User Database Query Tool

• Download Demo Project - 42.7 KB
• Download Source Files - 184.7 KB

Introduction

From time to time programmers come across users who, for whatever reason, seem to actually want to code T-SQL in order to find the information that they need. In an SQL Server environment, a common way to give them this capability is to install the SQL Query Analyzer product. Beginning with SQL Server 2005 however, Query Analyzer has been rolled in to the SQL Server Management Studio product which introduces a slew of possibilities that can put the enterprise data at risk.

This tool provides the users with their query capability, while minimizing the database exposure to those "accidental" issues.

Please note that this code does not contain any copyright because it cannot be copyrighted. The reason for this is that this code is an obvious, logical implementation utilizing the .NET 2.0 Framework to accomplish its task. Many programmers before me have created virtually identical tools (using a myriad of languages) to accomplish precisely the same thing, and there will be many after who never read this article who will create a similar tools as well.

Background

The "inspiration" for this tool came from observing users at one of my work sites who were originally given SQL Query Analyzer on the desktop to perform lookups from the enterprise database. Although I'm opposed to giving users a tool such as this when other ("better") options exist, this user community has a lot of history surrounding the decision to go with SQL Query Analyzer.

Unfortunately, the training they receive in relational databases comes from a book on T-SQL that they are given when they first arrive in the job position. Note that these are users, not IT folks of any kind. Most of them are terrified of learning SQL so they use a brief library of sample SQL code that was handed to them by one of the kinder IT folks.

If I can get on my soap box for a moment, I would like to point out that the supporting IT unit should be concentrating on providing the user community with the information that they need without resorting to the users writing their own programs. Ok, off the soap box.

Using the code

Once you dive into the code you'll see that it's really quite simple. An SQLConnection object handles the database connection, an SqlCommand object handles the command to be executed, and the execution of the command is handled through the SqlDataAdapter object.

The only bit of trickery was in allowing "batch" command execution such as:

USE databasename
GO

SELECT somedata
FROM   atable
WHERE  afield = aValueICareAbout

Since the word GO (case insensitive) is not an actual SQL language element, the database will throw an Exception which in turn causes the SqlCommand object to throw an Exception. My solution around this one was to break the input into individual commands that were separated with the GO command, then feed them to the database one at a time. The results are each collected in their own DataSet object, and all of the result DataSet objects are combined into the final return DataSet object, which is then sent on for display either by Microsoft Excel or a result window instance. Here's the code to split the commands:

/// Split a query batch into individual queries.
/// 
/// Query to process
/// Array of strings containing queries.
private string[] FragmentQuery(string Query)
{
    // Split the query into individual lines
    Regex r = new Regex("\r\n");
    string[] lines = r.Split(Query);

    // Count the number of GO's
    int gocnt = 0;
    foreach (string s in lines)
        if (s.Trim().ToLower() == "go")
            gocnt++;

    // Create an array of string of the appropriate size
    string[] ret = new string[gocnt + 1];
    gocnt = 0;

    // Construct the queries
    StringBuilder sb = new StringBuilder();
    for(int n = 0; n < lines.Length; n++)
    {
        string s = lines[n];
        if(s.Trim().ToLower() == "go")
        {
            if(sb.Length > 0)
            {
                ret[gocnt++] = sb.ToString();
                sb = new StringBuilder();
                s = string.Empty;
            }
        }
        if (s.Trim().Length > 0)
        {
            sb.Append(s);
            sb.Append("\r\n");
        }
    }
    // Catch the last one
    if (sb.Length > 0)
        ret[gocnt] = sb.ToString();

    return ret;
}

Once the commands have been split, the following code is used for execution:

/// Execute a database query.
/// 
/// Query to execute.
/// DataSet containing results
public DataSet ExecuteQuery(string Query)
{
    // Separate the queries
    string[] cmds = FragmentQuery(Query);

    // Get our return DataSet
    DataSet ret = new DataSet();

    // Get our execution DataSets
    DataSet[] exec = new DataSet[cmds.Length];
    for (int i = 0; i < exec.Length; i++)
        exec[i] = new DataSet();

    Exception e = null;
    using (SqlCommand cm = new SqlCommand())
    {
        // Set up the command
        cm.Connection = _Conn;
        cm.CommandType = CommandType.Text;
        SqlDataAdapter adap = new SqlDataAdapter(cm);

        // Open the connection
        try
        {
            if (_Conn.State != ConnectionState.Open)
                _Conn.Open();

            // Spin the commands
            for (int n = 0; n < cmds.Length; n++)
            {
                cm.CommandText = cmds[n];
                adap.Fill(exec[n]);
            }
        }
        catch (Exception ex)
        {
            e = ex;
        }
        finally
        {
            if (_Conn.State == ConnectionState.Open)
                _Conn.Close();
        }
    }
    // Re-throw the exception if we had one
    if (e != null)
        throw new Exception("Error during execute", e);

    // Package the results
    for (int n = 0; n < exec.Length; n++)
    {
        for (int j = 0; j < exec[n].Tables.Count; j++)
        {
            DataTable T = exec[n].Tables[j].Copy();
            T.TableName = string.Format("Query{0}-Result{1}", 
                (n + 1), (j + 1));
            ret.Tables.Add(T);
        }
    }

    // Return the results
    if (ret == null)
        return null;
    return ret.Copy();
}

Points of Interest

The program has the capability of keeping multiple query result windows open at one time. That's because each of the result windows renders a DataSet object within multiple DataGridView objects located each on their own tab, easily accomplished by:

// Spin through the tables
foreach (DataTable t in _DS.Tables)
{
    rescnt++;
    TabPage tp = new TabPage(t.TableName);
    DataGridView tpdg = new DataGridView();
    tpdg.AllowDrop = false;
    tpdg.AllowUserToAddRows = false;
    tpdg.AllowUserToDeleteRows = false;
    tp.Controls.Add(tpdg);
    tpdg.Dock = DockStyle.Fill;
    tpdg.DataSource = t;
    tpdg.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
    tabA.TabPages.Add(tp);
}

Which gives us:

One of the most common activities is to copy the results of a query into a Microsoft Excel spreadsheet. Rather than have the users do this, I utilize the Excel PIA (Primary Interop Assembly) from Microsoft for (in the case of my users) Microsoft Office 2003, which allows the program to write the output directly to an Excel workbook instead of the regular results window using this code:

/// Save the results in an Excel workbook.
/// 
/// DataSet to transform.
private void PlantItInExcel(DataSet DSource)
{
    Excel.Application oExcel;
    Excel._Workbook oWB;
    Excel._Worksheet oWS;
    Excel.Range oRange;

    try
    {
        // Start Excel and get application object
        oExcel = new Excel.Application();
        oExcel.Visible = true;

        // Get a new workbook
        oWB = (Excel._Workbook)
            (oExcel.Workbooks.Add(System.Reflection.Missing.Value));

        // Remove the default worksheets
        for (int n = oWB.Worksheets.Count; n > 1; n--)
            ((Excel.Worksheet)oWB.Sheets[n]).Delete();

        // Spin the tables
        bool FirstIn = true;
        for (int n = DSource.Tables.Count - 1; n > (-1);  n--)
        {
            // Pick up the table
            DataTable t = DSource.Tables[n];

            if (FirstIn)
            {
                oWS = (Excel._Worksheet)oWB.ActiveSheet;
                FirstIn = false;
            }
            else
            {
                oWS = (Excel._Worksheet)(oExcel.Worksheets.Add
                   (Missing.Value, Missing.Value, 
                    Missing.Value, Missing.Value));
            }
            oWS.Name = t.TableName;

            // Set the table headers from the column names
            int baserow = 1;
            for (int c = 0; c < t.Columns.Count; c++)
                oWS.Cells[baserow, (c + 1)] = t.Columns[c].ColumnName;

            // Assign the headers as bold
            oRange = oWS.get_Range(oWS.Cells
                [baserow, 1], oWS.Cells[baserow, t.Columns.Count]);
            oRange.Font.Bold = true;

            // Create a string array to hold the data rows
            string[,] tData = new string[t.Rows.Count, t.Columns.Count];

            baserow++;
            int row = 0;
            int col = 0;
            foreach (DataRow dr in t.Rows)
            {
                col = 0;
                foreach (DataColumn dc in t.Columns)
                {
                    tData[row, col] = (dr[col] == DBNull.Value) ? 
                        string.Empty : Convert.ToString(dr[col]);
                    col++;
                }
                row++;
            }
            oRange = oWS.get_Range(oWS.Cells[baserow, 1], 
                oWS.Cells[baserow + t.Rows.Count - 1, t.Columns.Count]);
            oRange.Value2 = tData;

            // Autofit the columns
            oRange.EntireColumn.AutoFit();
        }

        // Make sure Excel is visible and give the user control
        oExcel.Visible = true;
        oExcel.UserControl = true;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Which gives us:

I've also incorporated drag & drop for the query text box so that the user can cut and paste text from another application or drag a file directly to the textbox as well as execution of selected text from within the textbox. All drag & drop operations append to the text box contents rather than replace it.

Of course the user has the standard file operations (open, save, save as) from the file menu.

Because of the intended use of this tool, I've also included a list of SQL language elements that are not permitted:

/// The Bad Words list.
/// 
private const string _BadWords = "(\b|^)(create|alter|insert|truncate|
     delete|drop|update|grant|revoke|dbcc|exec|execute)(\b|$)";

This Regular Expression will locate the target word at the beginning of a line, end of a line, or anywhere in the line if it's bordered by a whitespace character. Readers familiar with SQL will instantly recognize the danger of having a untrained user issuing these commands. Any SQL scripts to be deployed with the tool can be placed in the Scripts folder. The download source contains only the AdventureWorks query example shown above.

Deployment of the tool

The download uses an App.config file for the primary database connection, and you should definitely change it. If you were to actually deploy this tool to users, you should have the connection hard-coded (or even further obfuscated) to avoid any "explorative" users from changing the connection string.

Error handling in the demo is very simplistic. Errors are presented in a dialog box that the user simply closes. Not a best practice for a production deployment, but on that note, see my comments above.

History

• v1.0 - addendum - Added compiled program as requested.
• v1.0 - Initial release.