Stored Proc Schema based Saving (parameters)

• Download source - 151.43 KB

Introduction

This article explains a refactored code snippet on passing parameters for a stored procedure via SP schema rather than hard coding the column/param names viz @param1, @param2, etc. In this article, I shall try to show two code snippets with explanation; one with the traditional style and the other with the refactored method.

I got this idea when I was looking over a colleagues' code that he wrote to save data to DB via SP which takes around 10 or 12 parameters. An idea struck me as to why we have to hard code those magic strings. Here, I have assumed the column name of table and the param name of the SP are the same.

This sample application has a gridview which displays the specified table contents as well as accepts edit of cell values. Once edited, it can be saved off via a refactored Schema based method call or via normal code using explicit parameter name mentioned.

How Does the Code Work?

The code retrieves SQL table contents from the DB based on the input information given. It then allows the user to edit the values of the table contents. Once the values are edited, the user can proceed to save the information onto the DB table via Stored procedure.

The code has the following parts/sections:

SqlWorker

The SqlWorker class helps in connecting and retrieving the data from SQL based on the input values. This class also handles saving of data to DB table via Stored proc. Based on the build condition viz SchemaBased or NonSchemaBased related methods will be called in saving the data. The only difference that exists here is the way in which data is saved onto the table via stored procedure. By default, most stored procs do accept a lot of parameters whose values have to be saved. Here in this article, I have tried to refactor a part of code which I think could help out. First, I'll explain the tradition style of passing parameters to a Stored proc and then the refactored way.

using (SqlConnection conn = new SqlConnection(_connectionString))
             { 
                conn.Open();
                SqlCommand command = new SqlCommand(_spName, conn); 
                command.CommandType = CommandType.StoredProcedure;
                foreach (DataRow row in dtable.Rows) 
                { 
                    command.Parameters.Add(new SqlParameter_
			("@StudentAge",row["StudentAge"])); 
                    command.Parameters.Add(new SqlParameter_
			("@StudentName", row["StudentName"]));
                    command.Parameters.Add(new SqlParameter_
			("@StudentNumber", row["StudentNumber"]));
                    /* 
                    *
                    * 
                    * 
                    */ 
                }
                command.ExecuteNonQuery();

As per the code above, it's pretty obvious that most of the parameters passed are hard coded into the code. This for me could lead to a maintenance issue later on if the SP gets changed or param names get changed. What if the stored proc takes 20 parameters or some thing? (hypothetically). I think it's better to avoid the magic strings to be hard coded in the code. The other way to hard code the magic strings (column names) is via mapping in some other file, say resx? But again, the problem could come in mapping it to match the SP names. Again, we have to edit the resx file often and often or even XML for that sake. I think it gets pretty complex as well. Right?

Below, I use this SQL query to get the schema definition of the Stored Proc. I am only interested in the param names and its type defined in the stored proc.

select sys.parameters.name as ParamName,sys.types.name as ParamType from sys.objects,
                sys.parameters, sys.types ", "where sys.objects.name = '",_spName,"' _
				AND sys.objects.object_id
                = sys.parameters.object_id AND ", "sys.objects.type='p' _
				AND sys.parameters.system_type_id
                = sys.types.system_type_id order by sys.parameters.parameter_id asc 

As per the SQL query shown above, I am trying to get the Parameter names and types details from three different system tables. The details are spread out to three different system tables called sys.parameters, sys.objects, sys.types. The reason I am doing order by on parameter_id is because without this, I don't get in proper order from the query on column names. Which creates mapping between reading the table and this schema. Execute the query with the attached stored proc and DB file. You'll get the idea.

Next, I'll explain and show the refactored code which could solve in hard coding magic strings inside code/XML/resx, etc. before.

using (SqlConnection conn = new SqlConnection(_connectionString))
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand(_spName, conn);
               cmd.CommandType = CommandType.StoredProcedure;

               int columnIndex = 0;
               foreach (DataRow row in pupilTable.Rows)
               {
                   cmd.Parameters.Clear();

                   foreach (var column in row.ItemArray)
                   {
                       cmd.Parameters.Add(new SqlParameter_
           (spSchema.Rows[columnIndex][0].ToString(), column));
                       columnIndex++;
                   }
                   columnIndex = 0;
                   cmd.ExecuteNonQuery();
               }
           }

As per the above code, the table which was retrieved earlier (refer to the code) has the same structure of the table with the column names as of the SP schema definition.

So now I iterate on each Table's datarow retrieved (PupilTable) and then for each row, get all the column data and add parameters to command object from spSchema.Row[columnIndex][0], the 0 index has the Parameter names that belong to Stored proc.

I repeat this loop for all the rows in the pupil table which are needed to be saved.

Well folks, thanks for reading. Hope this helps. I'm open to ideas/improvements/learning.
Enjoy!