Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Stored Proc Schema based Saving (parameters)

, 1 Jul 2011
Rate this:
Please Sign up or sign in to vote.
This article explains a refactored code snippet on passing parameters for a stored proecedure via SP schema rather than hard coding the column/param names.

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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

zenwalker1985
Software Developer (Senior) Siemens
India India
A .net developer since 4+ years, wild, curious and adventurous nerd.
 
Loves Trekking/Hiking, animals and nature.
 
A FOSS/Linux maniac by default Wink | ;)
 
An MVP aspirant and loves blogging -> https://adventurouszen.wordpress.com/
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 1 Jul 2011
Article Copyright 2011 by zenwalker1985
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid