locked
How is wrong on function ExecuteNonQuery to work as Best Practise ? RRS feed

  • Question

  • User696604810 posted

    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 ?

    Saturday, June 13, 2020 8:25 AM

All replies

  • User-474980206 posted

    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.

    Saturday, June 13, 2020 11:11 PM