Click here to Skip to main content
Click here to Skip to main content

Dynamic SQL Generator

, 14 Jun 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Generate dynamic SQL Stored Procedures for advanced search quickly and easily
Screenshot.jpg

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

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

License

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

Share

About the Author

Omar Gameel Salem
Software Developer
Australia Australia
Enthusiastic programmer/researcher, passionate to learn new technologies, interested in problem solving, data structures, algorithms AI, machine learning and nlp.
 
Amateur guitarist/ keyboardist, squash player.
 
If you have a question\suggestion about one of my articles, or you want an algorithm implemented in C#, feel free to contact me.
Follow on   LinkedIn

Comments and Discussions

 
QuestionPlease contact me PinmemberIT WebWare12-Jan-12 14:34 
GeneralMy vote of 1 Pinmemberonpnttrk11-Nov-09 3:36 
GeneralRe: My vote of 1 PinmemberOmarGamil11-Nov-09 5:05 
GeneralMy vote of 1 PinmemberNikola Ilijev10-Nov-09 19:49 
GeneralRe: My vote of 1 PinmemberFernandoUY5-Feb-12 12:29 
GeneralMy vote of 2 PinmemberThirster4210-Nov-09 7:38 
GeneralRe: My vote of 2 PinmemberOmarGamil10-Nov-09 12:31 
GeneralMy vote of 1 [modified] PinmemberNadya_Nos10-Nov-09 5:48 
GeneralRe: My vote of 1 PinmemberOmarGamil10-Nov-09 7:16 
GeneralRe: My vote of 1 PinmemberThirster4210-Nov-09 7:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.1411022.1 | Last Updated 14 Jun 2010
Article Copyright 2009 by Omar Gameel Salem
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid