How is wrong on function ExecuteNonQuery to work as Best Practise ?RSS

• • Reply
  ahmedbarbary

  Member

  32 Points

  303 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 ?

• • Reply
  bruce (sqlwo...

  All-Star

  56084 Points

  14565 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.

  bruce (sqlwork.com)