Not sure why the create task overhead when you are using async operations. I’d probably create a base db class/interface class that managed connection settings, and make the methods extension method for the class/interface. also why do you pass a sqlconnection?
public class Database
{
public readonly string ConnectionString = "";
public Database(string connectionString)
{
this.ConnectionString = connectionString;
}
}
public static class DatabaseExtensions
{
public static async Task<int> ExecuteNonQueryAsync(this Database db, string sql, DbParameter[] @params = null, CommandType cmdType = CommandType.StoredProcedure)
{
using (var conn = new SqlConnection(db.ConnectionString))
{
var cmd = new SqlCommand(sql, conn);
cmd.CommandType = cmdType;
if (cmd.CommandTimeout < 360)
cmd.CommandTimeout = 360;
if (@params != null)
{
for (var i = 0; i < @params.Length; i++)
{
cmd.Parameters.Add(@params[i]);
}
}
await conn.OpenAsync();
return await cmd.ExecuteNonQueryAsync();
}
}
}
...
var db = new Database(connectionString);
var count = db.ExecuteNonQueryAsync(sql);
also if you want a simple ORM, I use dapper from the stackoverflow folks
Member
34 Points
316 Posts
How is wrong on function ExecuteNonQuery to work as Best Practise ?
Jun 13, 2020 08:25 AM|ahmedbarbary|LINK
I work on c# app I need to make function make insert data or update or delete dynamically so that I do function below for insert
or update or delete but I don't know what must added or remove from function below to make function work as best practice .
public static async Task<int> ExecuteNonQuery(string sql, SqlConnection sqlconnection, DbParameter[] @params = null, CommandType cmdType = CommandType.StoredProcedure) { int RecordsCount = 0; if (sql == "") return 0; await Task.Run(async () => { using (var con = new SqlConnection(GlobalVariables.con)) { using (var cmd = new SqlCommand() { Connection = con }) { if (cmd.CommandTimeout < 360) cmd.CommandTimeout = 360; cmd.CommandText = sql; cmd.CommandType = cmdType; cmd.Parameters.Clear(); if (@params != null) { for (int i = 0; i < @params.Length; i++) { cmd.Parameters.Add(@params[i]); } } try { await con.OpenAsync(); RecordsCount = (await cmd.ExecuteNonQueryAsync()); } catch (Exception ex) { throw new Exception(ex.Message); } } } }); return RecordsCount; }so I do function above for make insert or update or delete
what is remaining or wrong to be best practice ?
All-Star
57234 Points
15157 Posts
Re: How is wrong on function ExecuteNonQuery to work as Best Practise ?
Jun 13, 2020 11:11 PM|bruce (sqlwork.com)|LINK
Not sure why the create task overhead when you are using async operations. I’d probably create a base db class/interface class that managed connection settings, and make the methods extension method for the class/interface. also why do you pass a sqlconnection?
public class Database { public readonly string ConnectionString = ""; public Database(string connectionString) { this.ConnectionString = connectionString; } } public static class DatabaseExtensions { public static async Task<int> ExecuteNonQueryAsync(this Database db, string sql, DbParameter[] @params = null, CommandType cmdType = CommandType.StoredProcedure) { using (var conn = new SqlConnection(db.ConnectionString)) { var cmd = new SqlCommand(sql, conn); cmd.CommandType = cmdType; if (cmd.CommandTimeout < 360) cmd.CommandTimeout = 360; if (@params != null) { for (var i = 0; i < @params.Length; i++) { cmd.Parameters.Add(@params[i]); } } await conn.OpenAsync(); return await cmd.ExecuteNonQueryAsync(); } } } ... var db = new Database(connectionString); var count = db.ExecuteNonQueryAsync(sql);also if you want a simple ORM, I use dapper from the stackoverflow folks
https://dapper-tutorial.net
They have full async support.