Programming.NET

Managing SQL Strings in C# ASP .NET Applications

modified | by

Introduction

Many ASP .NET business web applications involve the usage of a database in one form or another. Depending on the architecture of the web application, database calls may exist in the user interface, business tier, or data tier. Regardless of the layer, many developers include raw SQL query strings within their source code, for accessing the database. In fact, since the database classes accept a raw SQL string as a parameter when beginning a query or stored procedure call, it’s common for developers to include the SQL query string directly in the source code. While as simple and straight-forward as this technique is, it introduces a variety of maintenance problems, including requiring a recompile whenever an SQL string is tweaked, complicating the source code with long SQL strings, and string concatentation of parameters in the SQL query which is prone to errors.

In this article, we’ll describe an SQL String Factory class for accessing a database filled with Dragons, which allows you to clean up your source code by moving all of the raw SQL queries into the web.config. You’ll be able to quickly and easily access the SQL strings by calling a simple method, GetSql(). The method will utilize reflection to dramatically speed up the ease of accessing your SQL strings. You’ll also gain strong error checking on the format of your SQL queries and parameters. As a side bonus, the factory class will also help organize database connection strings and rid the problem of commenting different connection strings to change databases.

We’ve All Done This Before

Most developers that have written C# ASP .NET web applications, which access a database, have written code similar to the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
static void Main(string[] args)
{
    SqlConnection connection = new SqlConnection("myconnectionstring")
    connection.Open();

    SqlCommand command = new SqlCommand("SELECT * FROM DRAGONS WHERE DragonId = " + dragonId, connection);

    SqlDataReader reader = command.ExecuteReader();
    if (reader.Read())
    {
        ...
    }

    reader.Close();
    connection.Close();
}

The above code is very straight-forward, and simply accesses a database to execute a query to load a dragon by its identifier. It’s probably the same code you’ve used when beginning programming in C# ASP .NET. We’ve all included the connection string as a raw string in the code before, and we’ve all included SQL queries right in the code. Ignoring the fact that this code omits exception handling and “using” statements to properly manage the connection objects, there is another subtle problem that really sticks out. The problem is the raw connection string and SQL query. How can we improve this?

We’ve All Done This Before, Too

The first optimization most developers move on to, is moving the connection string out of the source code and into the web.config file. This resolves the problem of re-typing the same connection string all over in the source code, and allows us to simply access ConfigurationManager.ConnectionStrings[“MyConnection”], as follows:

web.config

1
2
3
4
5
6
7
8
9
10
<configuration>
    <connectionStrings>
        <!-- Development -->
        <add name="MyConnection" connectionString="Data Source=dev_database;User ID=username;Password=password;" />
        <!-- QA -->
        <!--<add name="MyConnection" connectionString="Data Source=qa_database;User ID=username;Password=password;" />-->
        <!-- Production -->
        <!--<add name="MyConnection" connectionString="Data Source=prod_database;User ID=username;Password=password;" />-->
    </connectionStrings>
</configuration>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
static void Main(string[] args)
{
    SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString)
    connection.Open();

    SqlCommand command = new SqlCommand("SELECT * FROM DRAGONS WHERE DragonId = " + dragonId, connection);

    SqlDataReader reader = command.ExecuteReader();
    if (reader.Read())
    {
        ...
    }

    reader.Close();
    connection.Close();
}

The above code takes a step forward in optimization by moving the connection string out of the source code and putting it in the web.config. This allows us to reference the connection string with the same code throughout our C# ASP .NET application, but it suffers from one distinct problem.

Notice that we’ve commented out the QA and Production connection strings in the web.config block. This is the common way that many developers utilize multiple connection strings in the web.config. They’ll provide the same name to each connection string, since the source code pulls using that name (ie., ConfigurationManager.ConnectionStrings[“MyConnection”]) and they’ll just uncomment the desired connection string and comment out the others. While no recompile is required, since we’re only modifying the web.config, you still find yourself commenting and uncommenting many times as you test against the various database. This can become tiresome and prone to error. Of course, the code above also still suffers from the issue of including raw SQL strings. Let’s see how we can fix this.

Wouldn’t This Be Nice

The complete source code for the project can be downloaded here.

Let’s see what happens if we move out the connection string and SQL query strings from the source code into the web.config. What would the code look like?

web.config

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<configuration>
    <connectionStrings>
        <!-- Development -->
        <add name="DevConnection" connectionString="Data Source=dev_database;User ID=username;Password=password;" />
        <!-- QA -->
        <add name="QAConnection" connectionString="Data Source=qa_database;User ID=username;Password=password;" />
        <!-- Production -->
        <add name="ProdConnection" connectionString="Data Source=prod_database;User ID=username;Password=password;" />
    </connectionStrings>

    <SqlStringFactory defaultConnectionStringName="DevConnection">
        <SqlStrings>
            <add name="ConsoleApplication1.Main" sql="SELECT * FROM DRAGON" />
        </SqlStrings>
    </SqlStringFactory>
</configuration>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
static void Main(string[] args)
{
    SqlConnection connection = new SqlConnection(SqlFactory.ConnectionString)
    connection.Open();

    SqlCommand command = new SqlCommand(SqlFactory.GetSql(), connection);

    SqlDataReader reader = command.ExecuteReader();
    if (reader.Read())
    {
        ...
    }

    reader.Close();
    connection.Close();
}

The first item to notice in the above partial code example, is that we now have 3 connection strings, each with a unique name. None of the connection strings are commented out, allowing us to reference each one as needed. We’re also using a new class, SqlFactory, to access the active connection string (which is actually defined in the web.config in the SqlStringFactory section) and to access the SQL query. There is no need to even provide a key name when obtaining the SQL since SqlFactory is smart enough to key off of the class and method name. This certainly cleans up the code and provides a much more maintainable web application.

There are several benefits gained by moving the raw strings into the web.config. We can easily switch connection strings as needed without a recompile. We can easily tweak SQL queries without a recompile. We can also easily view all of the SQL queries in a single location.

In addition to storing SQL strings, for those who prefer stored procedures, the stored procedure method name can be stored in the SqlFactory block as well. Let’s move on to creating the SQL String Factory class.

Starting With the Web.Config

To begin the SQL String Factory class for managing raw SQL strings and connection strings, we’ll define the entries in the web.config first. We’re going to be using a .NET custom configuration section handler to define the SQL string entries, as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<configuration>
    <configSections>
        <section name="SqlStringFactory" type="ConsoleApplication1.SqlStringFactory.Configuration.SqlStringFactorySectionHandler, ConsoleApplication1.SqlStringFactory"/>
    </configSections>

    <connectionStrings>
        <!-- Development -->
        <add name="DevConnection" connectionString="Data Source=dev_database;User ID=username;Password=password;" />
        <!-- QA -->
        <add name="QAConnection" connectionString="Data Source=qa_database;User ID=username;Password=password;" />
        <!-- Production -->
        <add name="ProdConnection" connectionString="Data Source=prod_database;User ID=username;Password=password;" />
    </connectionStrings>

    <SqlStringFactory defaultConnectionStringName="DevConnection">
        <SqlStrings>
            <add name="DragonManager.GetDragons" sql="SELECT * FROM DRAGON" />
            <add name="DragonManager.GetDragonById" sql="SELECT * FROM DRAGON WHERE DragonId = {0}" />
            <add name="DragonManager.FindDragonByColorBreath" sql="SELECT * FROM DRAGON WHERE Color = '{0}' OR Breath = '{1}'" />
            <add name="DragonManager.GetDragonsWithProc" sql="MyStoredProcedureName" />
        </SqlStrings>
    </SqlStringFactory>
</configuration>

The first item to note above is our custom configuration section DLL defined as:

1
ConsoleApplication1.SqlStringFactory.Configuration.SqlStringFactorySectionHandler, ConsoleApplication1.SqlStringFactory

summarized as:

Namespace.Class, DLL

The custom configuration section block tells our code where to look in the web.config for the SQLStringFactory section and how to load it. Notice, we’ve defined all of our connection strings, each with a unique name. We then define the SqlStringFactory block. This block contains a parameter to point to the active connection string. This allows us to easily swap connection strings, at run-time, by simply changing this value to any of the available connection strings. We then define the list of raw SQL query strings. The name for each string follows the pattern: Class.Method. So “DragonManager.GetDragons” will be accessed from the DragonManager class and the method GetDragons. This allows the SqlFactory to use reflection to pull the correct SQL string.

Creating the Custom Configuration Section

The first required class for the ASP .NET custom configuration section is the SqlStringFactorySectionHandler, defined as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public sealed class SqlStringFactorySectionHandler : ConfigurationSection
{
    [ConfigurationProperty("defaultConnectionStringName")]
    public string DefaultConnectionStringName
    {
       get { return (string)base["defaultConnectionStringName"]; }
    }

    [ConfigurationProperty("secondaryConnectionStringName")]
    public string SecondaryConnectionStringName
    {
        get { return (string)base["secondaryConnectionStringName"]; }
    }

    [ConfigurationProperty("SqlStrings", IsDefaultCollection = false),
    ConfigurationCollection(typeof(SqlStringCollection), AddItemName = "add", ClearItemsName = "clear", RemoveItemName = "remove")]
    public SqlStringCollection SqlStrings
    {
        get
        {
            return (SqlStringCollection)this["SqlStrings"];
        }
    }
}

This class defines the main SqlStringFactory section of the web.config and allows us to load our SQL query strings. We then define a class to handle an individual SQL query element, as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
public sealed class SqlStringElement : ConfigurationElement
{
    [ConfigurationProperty("name", IsRequired = true)]
    public string Name
    {
       get
        {
            return (string)this["name"];
        }
        set
        {
            this["name"] = value;
        }
    }

    [ConfigurationProperty("sql", IsRequired = true)]
    public string Sql
    {
        get
        {
            return (string)this["sql"];
        }
        set
        {
            this["sql"] = value;
        }
    }
}

The above class handles the individual SQL query strings in our web.config, reading the name and Sql properties. Finally, we need a collection class to handle the set of SQL query strings, as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
public sealed class SqlStringCollection : ConfigurationElementCollection
{
    protected override ConfigurationElement CreateNewElement()
    {
       return new SqlStringElement();
    }

    protected override object GetElementKey(ConfigurationElement element)
    {
        return ((SqlStringElement)element).Name;
    }

    public SqlStringElement this[int index]
    {
        get
        {
        return (SqlStringElement)BaseGet(index);
        }
        set
        {
            if (BaseGet(index) != null)
            {
                BaseRemoveAt(index);
            }

            BaseAdd(index, value);
        }
    }

    public SqlStringElement this[string name]
    {
        get
        {
            return (SqlStringElement)BaseGet(name);
        }
        set
        {
            int index = 0;

            SqlStringElement element = (SqlStringElement)BaseGet(name);
            if (element != null)
            {
                index = BaseIndexOf(element);
                if (index > -1)
                {
                    BaseRemoveAt(index);
                }
            }

            BaseAdd(index, value);
        }
    }
}

The above class is a general collection class for holding the complete collection of SQL strings, defined in our web.config. With our custom configuration section classes completed, we can move on to the actual SQL String Factory class source code.

Getting to the Real Power with the SqlStringFactory

The SqlStringFactory class is the main class which allows us to seamlessly access the web.config, pull the active connection string, and pull SQL queries or stored procedure names - with or without a key name (via .NET reflection on the calling class and method).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
/// <summary>
/// Manages the primary database connection strings and SQL strings in the web.config.
/// Can be called without specifying a key, allowing reflection to select the proper SQL string, based on the calling class and function name.
/// </summary>
public static class SqlFactory {
    private static SqlStringFactorySectionHandler sqlStringFactoryConfiguration = (SqlStringFactorySectionHandler) ConfigurationManager.GetSection("SqlStringFactory");

    /// <summary>
    /// Default connection string used to connect to the primary database.
    /// </summary>
    public static string ConnectionString {
        get {
            return ConfigurationManager.ConnectionStrings[sqlStringFactoryConfiguration.DefaultConnectionStringName].ConnectionString;
        }
    }

    #region Reflected Functions

    /// <summary>
    /// Returns the SQL string with the key matching the calling class and function name.
    /// </summary>
    /// <returns>SQL string</returns>
    public static string GetSql() {
        // Get the calling method via the stack trace.
        StackTrace stackTrace = new StackTrace();
        MethodBase reflectedMethod = stackTrace.GetFrame(1).GetMethod();

        // Get the class and method name via the reflected method.
        string className = reflectedMethod.DeclaringType.Name;
        string method = reflectedMethod.Name;

        return GetSql(className + "." + method);
    }

    /// <summary>
    /// Returns the SQL string with the key matching the calling class and function name. Passes in parameters.
    /// </summary>
    /// <param name="parameterList">List of parameter strings</param>
    /// <returns>SQL string</returns>
    public static string GetSql(List < string > parameterList) {
        // Get the calling method via the stack trace.
        StackTrace stackTrace = new StackTrace();
        MethodBase reflectedMethod = stackTrace.GetFrame(1).GetMethod();

        // Get the class and method name via the reflected method.
        string className = reflectedMethod.DeclaringType.Name;
        string method = reflectedMethod.Name;

        return GetSql(className + "." + method, parameterList);
    }

    #endregion

    /// <summary>
    /// Returns the SQL string associated with a specific key.
    /// </summary>
    /// <param name="className">Class name</param>
    /// <param name="method">Function name</param>
    /// <returns>SQL string</returns>
    public static string GetSql(string name) {
        return GetSql(name, new List < string > ());
    }

    /// <summary>
    /// Returns the SQL string associated with a specific key. Passes in parameters.
    /// </summary>
    /// <param name="name">Class name</param>
    /// <param name="parameterList">List of string parameters</param>
    /// <returns>SQL string</returns>
    public static string GetSql(string name, List < string > parameterList) {
        if (name.Length == 0) {
            throw new Exception("Empty Name found in SqlStringFactory.");
        }

        return InsertParameters(sqlStringFactoryConfiguration.SqlStrings[name].Sql, parameterList, name);
    }

    /// <summary>
    /// Replaces each parameter in the SQL string with a parameter item. Ie., {0} {1} {2}.
    /// If not enough parameters are passed in, an exception is thrown.
    /// If too many parameters are passed in, an exception is thrown.
    /// </summary>
    /// <param name="sql">SQL string, optionally including parameters {0} {1} {2}, etc</param>
    /// <param name="parameterList">List of string parameters</param>
    /// <param name="name">Name (for error logging)</param>
    /// <returns>SQL string</returns>
    private static string InsertParameters(string sql, List < string > parameterList, string name) {
        int parameterIndex = 0;

        foreach(string parameter in parameterList) {
            string parameterKey = "{" + parameterIndex + "}";

            if (sql.IndexOf(parameterKey) == -1) {
                throw new Exception("Too many parameters passed into GetSql. Name: " + name + ", SQL: " + sql + ", Parameter Count: " + parameterList.Count);
            }

            // Check for SQL comment characters.
            if (parameter.IndexOf("--") > -1) {
                throw new Exception("Invalid parameter: " + parameter);
            }

            // Replace the occurance of {0} with parameterList[0].
            // Replace single quotes to prevent SQL Injection
            sql = sql.Replace(parameterKey, parameter.Replace("'", "''"));

            parameterIndex++;
        }

        if (sql.IndexOf("{") != -1) {
            throw new Exception("Too few parameters passed into GetSql. Name: " + name + ", SQL: " + sql + ", Parameter Count: " + parameterList.Count);
        }

        return sql;
    }
}

The first item to note in the above code is the ConnectionString property. This property allows us to easily access the active connection string from our client code. There is no longer a need to use the ConfigurationManager.ConnectionStrings function from the client. We can simply call SqlFactory.ConnectionString. To change the active connection string, we simply modify the web.config property on the SqlStringFactory block. The change is automatically handled throughout the application.

The other methods in the class are actually helper methods for loading SQL queries or stored procedure names. The core idea is to load a query based on a name key. However, by specifying the SQL query name keys in the format of Class.Method, we can automatically pull queries from the calling class, without specifying a key! The power behind this is that we can define SQL queries or stored procedure names in the web.config, and never have to worry about specifying the different key names throughout our source code. We just simply call SqlFactory.GetSql().

The Secret Sauce

How does the automatic SQL name key detection work? Taking a look inside the GetSql() method, we find the following code:

1
2
3
4
5
6
7
8
9
// Get the calling method via the stack trace.
StackTrace stackTrace = new StackTrace();
MethodBase reflectedMethod = stackTrace.GetFrame(1).GetMethod();

// Get the class and method name via the reflected method.
string className = reflectedMethod.DeclaringType.Name;
string method = reflectedMethod.Name;

return GetSql(className + "." + method);

The class is using the StackTrace and .NET reflection to get the name of the calling class and method. The most recently called class and method will be exactly one frame behind. With this information, we can build the className + “.” + method to serve as the name key into the web.config and return the resulting SQL query. We then send the key name to the method which accepts a manually defined name key GetSql(name).

The remainder of the class is basic in structure. The methods simply read from the web.config custom configuration section to pull the SQL query associated with the name:

1
sqlStringFactoryConfiguration.SqlStrings[name].Sql

Since we’ve already formed the name key, based upon the calling class and method name, pulling from the custom configuration section is simple.

SqlStringFactory Handles Parameters in SQL Too

The other interesting part to the SqlStringFactory class is the insertion of parameters within the SQL queries. Without parameters, our SQL would be fairly limited. SqlStringFactory takes care of this by allowing you to define parameters in the format {0} {1} {2} within the web.config SQL query strings. The class will automatically insert the values into the parameter placeholders in the order that they are received.

1
<add name="DragonManager.FindDragonByColorBreath" sql="SELECT * FROM DRAGON WHERE Color = '{0}' OR Breath = '{1}'" />

Of course, if you are storing stored procedures in the SqlFactory block, you won’t be using parameters in the query itself. Parameters would be added to the SqlCommand object instead. However, storing the stored procedure name within the web.config still provides benefit, in that you can easily swap stored procedures at run-time to alter application behavior.

Strong Error Checking on SQL Queries, Of Course

The real beauty behind the insertion of parameters and automatic reflection in the SqlStringFactory class, is the error checking included. Each method in the class performs strict error checking, verifying that the SQL query exists with the key name specified. If it’s not found, then you’ve probably defined the wrong query key name in the web.config for the matching class name. The class also verifies the number of parameters match those supplied. If they differ, you’ve either supplied too many or not enough parameters to the calling class, you’ve defined the wrong number of parameters in the web.config SQL query, or you’ve left out a single quote or other typo in the query string. You certainly don’t gain this ability with raw SQL strings in your source code!

Adding a Business Tier

To really benefit from the SqlStringFactory class, you’ll generally want individual database queries in their own methods. This allows for a unique key name for the query in the web.config. For methods which require multiple queries, you can still use the SqlStringFactory and manually provide the key name. An example business tier class, utilizing the SqlStringFactory would be as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
// Notice there is no SQL in this file because it's in the app.config!
public class DragonManager
{
    // Test method for SqlFactory.
    public static string GetDragons()
    {
        //
        // Setup SQLConnection and SQLCommand
        // ..

        return SqlFactory.GetSql();
    }

    // Test method for SqlFactory.
    public static string GetDragonById(int id)
    {
        List<string> parameterList = new List<string>();
        parameterList.Add(id.ToString());

        //
        // Setup SQLConnection and SQLCommand
        // ..

        return SqlFactory.GetSql(parameterList);
    }

    // Test method for SqlFactory.
    public static string FindDragonByColorBreath(string color, string breath)
    {
        List<string> parameterList = new List<string>();
        parameterList.Add(color);
        parameterList.Add(breath);

        //
        // Setup SQLConnection and SQLCommand
        // ..

        return SqlFactory.GetSql(parameterList);
    }
}

Notice the above business tier example should the usage of the SqlFactory with both parameterless SQL queries and queries which take parameters. The SQL strings are checked for parameter errors by the SqlFactory and the actual raw string is returned. Of course in the example above, you would insert your database connection and command code. The same could be done with stored procedures as well, by pulling the stored procedure method name from the SqlFactory.

Putting It All Together

With the SqlFactory class completed and a business tier created, we can gain access to connection strings and raw SQL queries via the SqlFactory with the following client code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
static void Main(string[] args)
{
    // Get the database connection string.
    string connectionString = SqlFactory.ConnectionString;
    Console.WriteLine(connectionString);

    // Get the SQL for GetDragons.
    Console.WriteLine(DragonManager.GetDragons());

    // Get the SQL for GetDragonById.
    Console.WriteLine(DragonManager.GetDragonById(10));

    // Get the SQL for FindDragon.
    Console.WriteLine(DragonManager.FindDragonByColorBreath("White", "Cold"));

    Console.ReadKey();
}

Output

1
2
3
4
Data Source=dev_database;User ID=username;Password=password;
SELECT * FROM DRAGON
SELECT * FROM DRAGON WHERE DragonId = 10
SELECT * FROM DRAGON WHERE Color = 'White' OR Breath = 'Cold'

In the above code, we’re simply calling the business tier class, which accesses the database and uses the parameters that we provide. The connection string and raw SQL queries come directory from the SqlFactory class (and thus, the web.config), without specifying them in the C# ASP .NET source code. Notice, the output contains our complete SQL queries, including parameters, allowing us to easily manage SQL strings in the web.config.

Hacking, Cracking, and SQL Injecting

An article about managing SQL strings and stored procedure names wouldn’t be complete without a brief disclosure regarding security. The SqlFactory class is a tool for managing SQL strings and stored procedure names in the web.config. It does not execute SQL code, and therefore doesn’t include SQL injection checks or other validity checks on the strings. While you could certainly add this ability, security checks may be more appropriate in your core data layer or within your ORM classes. However, if you wish to add SQL injection checking to the SqlFactory class, the ideal location would be within the InsertParameters function, as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
private static string InsertParameters(string sql, List<string> parameterList, string name)
{
    int parameterIndex = 0;

    foreach (string parameter in parameterList)
    {
        string parameterKey = "{" + parameterIndex + "}";

        if (sql.IndexOf(parameterKey) == -1)
        {
            throw new Exception("Too many parameters passed into GetSql. Name: " + name + ", SQL: " + sql + ", Parameter Count: " + parameterList.Count);
        }

        // ** Check for SQL comment characters.
        if (parameter.IndexOf("--") > -1)
        {
            throw new Exception("Invalid parameter: " + parameter);
        }

        // Replace the occurance of {0} with parameterList[0].
        // ** Replaced single quotes to prevent SQL Injection
        sql = sql.Replace(parameterKey, parameter.Replace("'", "''"));

        parameterIndex++;
    }

    if (sql.IndexOf("{") != -1)
    {
        throw new Exception("Too few parameters passed into GetSql. Name: " + name + ", SQL: " + sql + ", Parameter Count: " + parameterList.Count);
    }

    return sql;
}

Note in the above revised InsertParameters() function, we include a Replace(“‘“, “‘’”) statement where the parameter string is inserted. This properly codes the single quotes to help prevent SQL injection attacks. Further validation would include checking of special characters, encodings, using stored procedures, using parameterized queries, etc. Wherever you decide to place your security checks, just be sure they exist.

Conclusion

As software scales in size and complexity, raw SQL string queries and stored procedure method names can litter the source code and hinder maintainability of the C# ASP .NET web application. By organizing SQL queries in an SqlFactory, we can store SQL strings in the easily configurable web.config file, allowing for simple run-time tweaking of queries, stored procedure names, application functionality changes, and even handing off of application behavior responsiblity to higher level business team members, via web.config management. By removing raw SQL strings from C# ASP .NET source code, we help maintain the list of application queries, helping to provide for a longer-living and maintainable C# ASP .NET web application.

About the Author

This article was written by Kory Becker, software developer and architect, skilled in a range of technologies, including web application development, machine learning, artificial intelligence, and data science.

Share