Minimizing code repeatability when calling stored procedures

Question

I use a specific method body to call stored procedures with the following code example:

public void StoredProcedureThatIsBeingcalled(int variable_1, int variable_2, out DataSet ds) { using (SqlConnection con = new SqlConnection(DatabaseConnectionString)) { ds = new DataSet("DsToGoOut"); using (SqlCommand cmd = new SqlCommand("StoredProcedureThatIsBeingcalled", DbConn.objConn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@variable_1", variable_1)); cmd.Parameters.Add(new SqlParameter("@variable_2", variable_2)); try { con.Open(); SqlDataAdapter objDataAdapter = new SqlDataAdapter(); objDataAdapter.SelectCommand = cmd; objDataAdapter.Fill(ds); con.Close(); } catch (Exception ex) { //sql_log_err } } } } 

What is bothering me? I have most of the above code repeating over and over in my cs file for every other procedure that I call.

Obviously, I can clear it and call one function with the name of the procedure as a variable, but how can I give it according to a different number of parameters (with different data types - int, string bool - never anything) different procedures that I use?

I may have several different functions with a different number of parameters (0-10), but I think there is a better way to do this?

Answer 1

Update

I know this is a very old question (and in fact, I came across it when looking for another old answer that I gave someone else to close as a duplicate), but I recently released a git hub project that answers this need . It minimizes code repetition when using ADO.Net, encapsulating Connection, Command, Parameters and DataAdapters.
If you want to try, I would be happy to know what you think about it.

First version

You can use a helper class to encapsulate sql parameters and create a single method for processing all data set populations as follows:

Helper Class:

 private class SqlParamDefinition { public SqlParamDefinition(string name, SqlDbType dbType, object value) { this.Name = name; this.DbType = dbType; this.Value = value; } public string Name { get; } public SqlDbType DbType { get; } public object Value { get; } } 

Run the method (based on the method you posted):

 public DataSet ExecuteSelectProcedure(string procedeureName, params SqlParamDefinition[] parameters) { var ds = new DataSet(); using (var con = new SqlConnection(DatabaseConnectionString)) { using (var cmd = new SqlCommand(procedeureName, DbConn.objConn)) { cmd.CommandType = CommandType.StoredProcedure; for(int i = 0; i < parameters.Length; i++) { var param = parameters[i]; cmd.Parameters.Add(new SqlParameter(param.Name, param.DbType).Value = param.Value); } try { con.Open(); var objDataAdapter = new SqlDataAdapter(); objDataAdapter.SelectCommand = cmd; objDataAdapter.Fill(ds); con.Close(); } catch (Exception ex) { //sql_log_err } } } return ds; } 

Call example:

 var parameters = new SqlParamDefinition[] { new SqlParamDefinition("@Param1", SqlDbType.VarChar, "value1"), new SqlParamDefinition("@Param2", SqlDbType.VarChar, "value2"), new SqlParamDefinition("@Param3", SqlDbType.Int, 123), }; var ds = ExecuteSelectProcedure("Strong procedure name", parameters); 

Answer 2

I had this problem; I called stored procedures in several databases. You can save stored procedure data, for example. name, input parameters, output parameters, etc. in the database tables, and then use the factory method to populate the object (clsStoredProcedure in the example below). The code will look something like this (I have not tested the code):

 public void StoredProcedureThatIsBeingcalled(clsStoredProcedure objStoredProcedure) { using (SqlConnection con = new SqlConnection(objStoredProcedure.ConnectionString)) { ds = new DataSet("DsToGoOut"); using (SqlCommand cmd = new SqlCommand(objStoredProcedure.Name, DbConn.objConn)) { cmd.CommandType = CommandType.StoredProcedure; foreach (Parameter p in clsStoredProcedure.Parameters) { cmd.Parameters.Add(new SqlParameter(p.name, p.value)); } try { con.Open(); SqlDataAdapter objDataAdapter = new SqlDataAdapter(); objDataAdapter.SelectCommand = cmd; objDataAdapter.Fill(ds); con.Close(); } catch (Exception ex) { //sql_log_err } } } } 

If you connect to Oracle databases and SQL databases, you can use dbConnection, dbCommand, etc. to connect to databases.

Answer 3

You can create a method that takes string and Dictionary<string,object> as parameters. Now you can create your team according to the text of the commands and the dictionary of parameters. In addition, you can extend this method and use it for select, insert, etc. queries.

Example:

 private void ExecCommand(string commandText, Dictionary<string, object> param) { using (SqlConnection con = new SqlConnection(DatabaseConnectionString)) { ds = new DataSet("DsToGoOut"); using (SqlCommand cmd = new SqlCommand(commandText, DbConn.objConn)) { cmd.CommandType = CommandType.StoredProcedure; //*************************************** // New method cmd = AddParametersToCommand(cmd, param); //*************************************** try { con.Open(); SqlDataAdapter objDataAdapter = new SqlDataAdapter(); objDataAdapter.SelectCommand = cmd; objDataAdapter.Fill(ds); con.Close(); } catch (Exception ex) { //sql_log_err } } } } 

And AddParametersToCommand :

 private SQLCommand AddParametersToCommand(SqlCommand command, Dictionary<string, object> parameters) { if (parameters == null || command == null) { return; } SQLCommand tempCommand = command; foreach (var param in parameters) { var parameter = tempCommand.CreateParameter(); parameter.ParameterName = param.Key; parameter.Value = param.Value ?? DBNull.Value; tempCommand.Parameters.Add(parameter); } return tempCommand; } 

And use it like:

 Dictionary<string, object> parameters = new Dictionary<string, object>(); parameters.Add("@variable_1", variable_1); parameters.Add("@variable_2", variable_2); ExecCommand("StoredProcedureThatIsBeingcalled", parameters); 

Answer 4

So,

You can use dapper to execute stored procedures. https://github.com/StackExchange/dapper-dot-net

You can declare a DTO model that will be displayed using what you selected in your stored procedure.

 public class DogDto { public int? Age { get; set; } public int Id { get; set; } public string Name { get; set; } public float? Weight { get; set; } public int IgnoredProperty { get { return 1; } } } // _databaseConnectionString is your database connection string using (var conn = new SqlConnection(_databaseConnectionString)){ var dog = cnn.Query<DogDto>("schema.spGetDog", new {Id = 120}, commandType: CommandType.StoredProcedure).SingleOrDefault(); } // and let assume we have schema.spGetDog stored procedure already declared in our database // and be aware that the 2nd parameter after the stored procedure name are the stored procedure parameters 

The stored procedure must specify the columns Age, Id, Name, Weight (mapping is done using the property name). I don’t know if you can change this content, because if you have the same columns / properties in the / dto database that use stored procedures, the development process will be faster.

And just for the record, according to the documentation: "Dapper has no specific implementation details for a specific database, it works with all .NET ADO providers, including SQLite, SQL CE, Firebird, Oracle, MySQL, PostgreSQL and SQL Server."

And that’s all you need to do.