Dynamic SQL Generator

• Download demo - 28.18 KB
• Download source code - 56.96 KB

Summary

Dynamic Generator is a simple Windows application to generate dynamic SQL Stored Procedures on the fly, easily and error free; just enter the server name with authentication, select the database, point to the table to work with, press ‘Generate’, and it’s done.

Introduction

Very often, when I’m asked to develop a data driven application or a CMS, I’m asked to implement an “Advanced Search” feature; this can be done using Dynamic SQL.

Although a more efficient way will be using the COALESCE function, dynamic SQL is the solution for doing “All words”, “Any word”, and “Exact Match” string searches.

Writing these statements can be tiresome, note that (unlike usual Stored Procedures) you may need more than one variable for the same field, .i.e., if it’s a range field (date or money), you will need two variables, from and to; if it’s a string variable (varchar, nvarchar, etc.), you will need five variables: four words the user entered, and a flag to check which search type was selected (“All words”, “Any word”, or “Exact Match”).

How the Code Works

Dictionary<string, string> dict_Fields is a dictionary holding the field name as a key, and the field type as value. When the user selects a table, we have to get all the fields within it.

private void lstbx_Tables_SelectedIndexChanged(object sender, EventArgs e)
{
    if (lstbx_Tables.SelectedIndex < 0)
        return;
    dict_Fields = new Dictionary<string, string>();
    cmb_Fields.Items.Clear();
    using (SqlConnection conn = new SqlConnection(connstring))
    {
        conn.Open();
        // get fields' names and type of selected table
        string strCommand = @"SELECT COLUMN_NAME, DATA_TYPE FROM " + 
                            @"INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '" + 
                            lstbx_Tables.SelectedItem.ToString() + "'";
        SqlCommand cmd = new SqlCommand(strCommand, conn);
       SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            //add to dict_Fields dictionary and fill the "Orderby" combox
            dict_Fields.Add(dr.GetString(0), dr.GetString(1));
            cmb_Fields.Items.Add(dr.GetString(0));
        }
         dr.Close();
        conn.Close();
    }
    cmb_Fields.SelectedIndex = 0;
}

Now all we have to do is to iterate through this dictionary, checking the field type.

Initializing Variables

If it’s a string field, we will append an integer variable, with the field’s name plus the word “Type”, and four nvarchar variables with the field’s name plus the variable index, with size 15 characters (all initialized to NULL).

Example: If we have a field called CategoryName. Then, we have the following variables created:

@CategoryNameType int = NULL,  -- 0 for AND search, 1 for OR search, 2 for Exact search
@CategoryName1 nvarchar(15) = NULL,
@CategoryName2 nvarchar(15) = NULL,
@CategoryName3 nvarchar(15) = NULL,
@CategoryName4 nvarchar(15) = NULL

If it’s a range field (money, date-time etc.), we will append two variables of the same type, from and to. E.g.,: if we have a field called HireDate, we have the following variables created:

@HireDateFrom datetime = NULL,
@HireDateTo datetime = NULL

Procedure’s Body

If it’s a string field, append an ‘if’ statement to check the search type, then four other statements for the variables we just created.

if @CategoryNameType = 0 -- All words Search
begin
if @CategoryName1 is not null
SELECT @sql = @sql + N' and CategoryName LIKE @CategoryName1$' 

if @CategoryName2 is not null
SELECT @sql = @sql + N' and CategoryName LIKE @CategoryName2$' 

if @CategoryName3 is not null
SELECT @sql = @sql + N' and CategoryName LIKE @CategoryName3$' 

if @CategoryName4 is not null
SELECT @sql = @sql + N' and CategoryName LIKE @CategoryName4$' 

The same goes for ‘Any word’ and ‘Exact match’.

If it’s a range field, append two if statements. Any other field will append just one statement.

That’s it, happy search.

History

• 10^th November, 2009: Initial post
• 19^th November, 2009: Updated article
• 13^th June, 2010: Updated article

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