A SQL helper class that allows executing a query or running a stored procedure, with or without dynamic parameters

A while back I wrote about my extension method to copy a non-generic collection to an array. A commenter pointed out that it isn’t really necessary. Oh well… here is some code that uses it… (If you use this code, you will need the extension method from the linked post, or else just do what it does instead of calling ToArray<DataColumn> in the code included.)

I use this all over the show, where I want to do a simple SQL query or run a stored procedure and get the results in a table. I like it because it makes it easy to do queries with any number of parameters, by using a collection initializer for a Dictionary type.

An example of usage is this:

        private void Example()
        {
            int id = 666;
            var queryHanlder = new QueryHandler(this.connectionString);

            var data = queryHanlder.OpenQuery(
                "SELECT Devil FROM Details WHERE ID = @ID", 
                new Dictionary<string, object> { {"@ID", id} });

            // Do something with data...
        }

And here’s the class… It contains the method called in the example above, overloaded to be called with or without parameters, and similar overloaded methods to call a stored procedure with or without parameters. I wrote this code years ago, but use it all the time, and it has crept into every project at work.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Demo
{
    public class QueryHandler
    {
        private string connectionString;

        public QueryHandler(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public DataTable OpenQuery(string query)
        {
            return OpenQuery(query, new Dictionary<string, object>());
        }

        public DataTable OpenQuery(string query, Dictionary<string, object> parameters, CommandType commandType = CommandType.Text)
        {
            using (var connection = new SqlConnection(this.connectionString))
            {
                using (var adapter = new SqlDataAdapter(query, connection))
                {
                    connection.Open();
                    try
                    {
                        var table = new DataTable();
                        adapter.SelectCommand.CommandType = commandType;

                        foreach (var kvp in parameters)
                            adapter.SelectCommand.Parameters.AddWithValue(kvp.Key, kvp.Value);

                        using (var reader = adapter.SelectCommand.ExecuteReader())
                        {
                            using (var schemaTable = reader.GetSchemaTable())
                            {
                                foreach (DataRow row in schemaTable.Rows)
                                    table.Columns.Add((string)row["ColumnName"], Type.GetType(row["DataType"].ToString()));
                            }

                            var columns = table.Columns.ToArray<DataColumn>();

                            while (reader.Read())
                                table.Rows.Add(columns.Select(c => reader[c.Ordinal]).ToArray());
                        }
                        return table;
                    }
                    finally { connection.Close(); }
                }
            }
        }

        public DataTable OpenStoredProcedure(string procName)
        {
            return OpenQuery(procName, new Dictionary<string, object>(), CommandType.StoredProcedure);
        }

        public DataTable OpenStoredProcedure(string procName, Dictionary<string, object> parameters)
        {
            return OpenQuery(procName, parameters, CommandType.StoredProcedure);
        }
    }
}
Advertisements

About Jerome

I am a senior C# developer in Johannesburg, South Africa. I am also a recovering addict, who spent nearly eight years using methamphetamine. I write on my recovery blog about my lessons learned and sometimes give advice to others who have made similar mistakes, often from my viewpoint as an atheist, and I also write some C# programming articles on my programming blog.
This entry was posted in Database, SQL and tagged , . Bookmark the permalink.

One Response to A SQL helper class that allows executing a query or running a stored procedure, with or without dynamic parameters

  1. Roger W says:

    Microsoft.ApplicationBlock.Data.SqlHelper

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s