Generic Method to Create and Execute Parameterized SQL Query in .NET 4.0

Now days, developers think and talk in terms of best practice and performance. When it comes to a debate of inline v/s parametrized SQL queries, Parametrized queries were preferred over inline queries, in terms of performance as they do avoid parsing, compile etc., by caching query plans each time the query is executed. It also helps in preventing SQL Injection.

In case of parametrized queries, it requires us to define the SQL parameters and then add them to parameter object and use along with data retrieval methods like ExecuteReader and DML Query methods like ExecuteNonQuery. Each time, depending on the number and type of parameters, we have to define the parameters and add them to parameter object for each of the method call which is tedious and cumbersome process to adapt.

Let us take an example query that retrieves the name of a student based on student ID and class.

var QueryString = "SELECT applicantname, emailId, overAllPercentage FROM  applicant  WHERE 
                   applicantid=`" + applicantid +"' and Status='" + Status + "' ";

In case of three tiered architecture, use of inline queries makes sense because you just need to write the inline query in logic tier and pass it to Data tier for retrieval and hence a few Generic data retrieval and manipulation methods were needed to Support the data processing. In general, I can write one method for update/insert/delete actions, as all these uses ExecuteNonQuery () method for Data manipulation. But inline queries are susceptible to SQL injection.

We do prefer parametrized queries because of fewer string concatenations and a more generic query form is presented to db, so it’s likely already hashed and stored as a pre-compiled execution plan and more importantly it will protect from SQL injection.

But as in inline query, itâ€™s difficult to use parametrized query as a part of the standard generic methods for data retrieval or manipulation. This is because, you never know the type of parameters, number of parameters that you are sending from logic tier to data tier. An Alternate approach for this, is to create the SQL parameter object in logic tier and pass that parameter object into data tier; however it doesn’t give clear separation between logic n data tier. In this article, we discuss about the generic methods that can be used with a clear separation between logic and data tier using LINQ.

This article focuses on writing the Generic method using parametrized query.

Let us say the logic tier needs a query to retrieve the applicant name, email id and his percentage from the applicant table depending on applicantID and Status.

      var appid =Convert.ToInt32 (txtname.Text);
      var Status=txtStatus.text.ToString();
      var query = "SELECT applicantName,emailID,overAllPercentage FROM applicant WHERE 
              applicantID=@0 and Status=@1";

      var result = new DbFunction().ExecuteQuery(query, appid,Status);

In the above code we use indexing of the parameters by specifying @0 and @1,that corresponds to the parameters passed in sequence at method call say appid at position correspond to @0 and Status at position correspond to @1.
The logic tier calls a data Retrieval method â€œExecuteQueryâ€ of data tier using parametrized query.
We just need to pass the query string and the set of parameters to the calling method which is a very simple approach to use. The ExecuteQuery () method returns IEnumerable type with which the logic tier continue its its data processing by browsing through the return values.

The generic method definition is given below:

        static SqlParameter ToConvertSqlParams(SqlCommand command,string name,object value)
        {
            var p = command.CreateParameter();
            p.ParameterName = name;
            p.Value = value;
            return p;
        }

        public IEnumerable ExecuteQuery(string commandtext, params Object[] args)
        {
        SqlConnection cnPubs = new SqlConnection(connectionstrng); 
            
       using (var connection=cnpubs)
            { 
              using (var command=new SqlCommand(commandtext,connection))
              { 
                    //use index,values to convert parameters passed  to sqlparameters         
var Parameters=args.Select((value,index)=>ToConvertSqlParams(command,index.ToString(),value));

                  command.Parameters.AddRange(Parameters.ToArray());
                  connection.Open();
                 //Execute the query 
                  using (var reader=command.ExecuteReader())
                  {
                      foreach (IDataRecord record in reader)
                          yield return record; 
                  }
                  connection.Close();
              }           
            }       
        }

The parameters are received as params object type and then later converted into SQL params by calling a separate method called ToConvertSqlParams() and added to command object to invoke ExecuteReder method.

This way we can use the generic method using LINQ to ease human effort and to increase the efficiency of code.