GOAL

Dynamic SQL command generation with proper use of sql parameterization to protect against sql injection and mal-formated queries.

I came up with what seems an efficient and clean way to dynamically build actual sql queries from the parameter list that is passed through the default SqlClient objects. Since sql parameterization is an absolute must in the world of safe sql through .NET I thought it would be a good idea to concentrate solely on the parameters when attempting to build a sql query in a function. This also allows for easy manipulation of dynamic arguments being sent through to build a dynamic query.

The solution I came up with was to use a single string as an argument that was a JSON serialized associative array (Hashtable) that contained field names and field values as the key->value pair to be later parsed through while building the parameter collection.

This solution could easily be adapted to use an ArrayList, string[], SqlParameter[], or many other dynamic way to send in field name -> field value pairs. The JSON string made to most sense in this case.

This relies on having LitJson present for using as a reference during compilation.

Here is the simple class with the saveRow method:

// csc.exe /t:library /out:sqlExample.dll /r:LitJson.dll com.overset.sqlExample.cs
namespace com.overset {

	using System;
	using System.Data;
	using System.Data.SqlClient;
	using LitJson;

	public class sqlExample {
		
		/* provide your sqlclient connection string here */
		public string __connStr = "Initial Catalog=<DBNAME>;Data Source=<DBNAME>;User ID=<DBUSER>;password=<DBPASS>;"
	
		public sqlExample () { /* ctor */ }

		/// <summary>
		///		Function to save a row depending on the arguments supplied in a single JSON string
		/// </summary>
		/// <param name="fieldsObject">(string) '{\'field1\':\'value1\', ...}'</param>
		/// <returns>(bool) success</returns>
		public bool saveRow (string fieldsObject) {

			/* open the DB connection */
			SqlConnection sConn = new SqlConnection(__connStr);
			sConn.Open();

			if ( fieldsObject.Length == 0 )
				throw(new Exception("argument fieldsObject must be a valid associative array serialized as a JSON string with at least 1 valid field in it"));
			
			/* we are expecting a json mapped string with all the fields information */
			Hashtable fields = JsonMapper.ToObject<Hashtable> ( fieldsObject );

			SqlCommand sC = new SqlCommand();
			string fieldList = "";
			string valueList = "";
			int numRows;

			/* build individual parameters to add to the parameter collection (IList) */
			if ( fields.ContainsKey("field1") )
				sC.Parameters.Add("@field1", SqlDbType.VarChar, 15).Value = 
					fields["field1"].ToString().Substring(0, Math.Min(fields["field1"].ToString().Length, 15));

			if ( fields.ContainsKey("field2") )
				sC.Parameters.Add("@field2", SqlDbType.VarChar, 20).Value = 
					fields["field2"].ToString().Substring(0, Math.Min(fields["field2"].ToString().Length, 20));

			if ( fields.ContainsKey("field3") )
				sC.Parameters.Add("@field3", SqlDbType.VarChar, 100).Value = 
					fields["field3"].ToString().Substring(0, Math.Min(fields["field3"].ToString().Length, 100));

			if ( fields.ContainsKey("field4") )
				sC.Parameters.AddWithValue("@field4", fields["field4"].ToString().Trim());

			/* detect wether to build an INSERT or UPDATE sqlCommand.CommandText */
			if ( fields.ContainsKey("primary_key") && fields["primary_key"].ToString().Length > 0 && Convert.ToDouble(fields["primary_key"].ToString()) > 0 ) {

				/* build the query off the parameter list */
				for (int pInd=0; pInd < sC.Parameters.Count; pInd++) {
					fieldList += sC.Parameters[pInd].ParameterName.Substring(1) + "=" + sC.Parameters[pInd].ParameterName + ( ( pInd < (sC.Parameters.Count - 1) ) ? ", " : "" );
				}

				/* add the primary_key to the where clause AFTER building the query */
				sC.Parameters.AddWithValue("@primary_key", fields["primary_key"].ToString());

				/* create the UPDATE sql command - append debug string for mssql profiler for testing on busy DEV DBs */
				sC.CommandText = @"UPDATE table SET " + fieldList + " WHERE primary_key=@primary_key /* DEBUG */";

				/* force the connection to use the UPDATE sqlCommand we have just built */
				sComm.Connection = sConn;

				/*	execute query with numrows - will return a higher number depending on triggers - >0 is a success, 0 no records found, -1 db error */
				numRows = sComm.ExecuteNonQuery();

			} else {

				/* add the primary_key BEFORE we build the field and value strings */
				sC.Parameters.AddWithValue("@primary_key", fields["primary_key"].ToString());

				/* build the query off the parameter list */
				for (int pInd=0; pInd < sC.Parameters.Count; pInd++) {
					fieldList += sC.Parameters[pInd].ParameterName.Substring(1) + ( ( pInd < (sC.Parameters.Count - 1) ) ? ", " : "" );
					valueList += sC.Parameters[pInd].ParameterName + ( ( pInd < (sC.Parameters.Count - 1) ) ? ", " : "" );
				}

				/* create the INSERT sql command - append debug string for mssql profiler for testing on busy DEV DBs */
				sC.CommandText = @"INSERT INTO table (" + fieldList + ") VALUES (" + valueList + ") /* DEBUG */";

				/* force the connection to use the INSERT sqlCommand we have just built */
				sComm.Connection = sConn;

				/*	execute query with numrows - will return a higher number depending on triggers - >0 is a success, 0 no records found, -1 db error */
				numRows = sComm.ExecuteNonQuery();
		
			}

			/* forcefully close the sqlConnection */
			sConn.Close();

			/* return false if the UPDATE/INSERT did not return # of affected rows - otherwise return true */
			if ( numRows <= 0 )
				return false;
			else
				return true;

		}

	}

}

This builds a query for table in the MSSQL environment that has the following fields:

• primary_key numeric(15,0)
• field1 varchar(15)
• field2 varchar(20)
• field3 varchar(100)
• field4 text

There are some other tidbits in there such as:

• adding a debug comment to the actual sqlCommand.CommandText which helps greatly when using Profiler on a high-traffic mssql development server.
• proper field value truncation while building the parameter list through Math.Min(x, y)