QASharp V1.0 [Query Analyzer like tool for MSDE database]

• Download source files - 79.6 Kb

Introduction

The QASharp V.1.0 was developed for those using the MSDE (Microsoft Desktop Engine). This project is meant to provide a tool similar to Query Analyzer which can be used to execute queries, create tables and do all other common database activities. This is still in its first release and this is my first attempt at developing a utility entirely in C# and also my first article to Code Project. The code is not at all optimized. My purpose for the first release is to get a working tool.

All suggestions, apprehensions, criticisms are welcome.

Functionality Supported

• Create new query
• Save existing query
• Open existing query
• Execute queries

Configuration settings to be made

In order for the application to work, make the following changes in the app.config file in the keys listed below.

<?xml version="1.0" encoding="Windows-1252"?>
<configuration>
<appSettings>
    <add key="server" value="(local)" /> 
    <add key ="database" value="master" />
          <add key="uid" value="sa"/>
          <add key="pwd" value=""/> 
       </appSettings>
</configuration>

Keys	Values
Server	The server to connect to.
Database	The initial database to connect to.
Uid	User ID
Pwd	Password

Usage

• Select a database.
• Create a new document by clicking on New.
• Type in the query (for multiple queries, add a GO keyword after each query).
• Press F5 to execute query or click on the Execute icon on the toolbar.

The Code

// Function name :     ExecuteQuery
// Parameters    :     none
// Return        :     none
// Calls         :     ExecuteQuery(string databaseName, string Query)
 
/* Purpose       :     Executes the query user has typed in the query 
                       writer
*/
private void ExecuteQuery()
{
    dbh.ERR_MSG = "";
    ErrorText = "";
    if (ds != null) 
    {
        ds.Tables.Clear();
        ds = new DataSet();
        arrQuery = null;
    }
            
    AddToArray(((frmQueryWriter)this.ActiveMdiChild).rtbQueryPad.Text);
                                    
    foreach(string s1 in arrQuery)
    {
        ExecuteQuery(currentDatabase,s1);
        ErrorText += dbh.ERR_MSG + "\n";
    }
 
    if (ErrorText.Trim() == "")
    {
        sbMain.Panels[0].Text = "Query batch completed.";
                  
    }
    else
    {
        sbMain.Panels[0].Text = "Query batch completed with errors.";           
    }
 
}

 
// Function name :     ExecuteQuery
// Parameters    :     (in string databaseName, in string query)
// Return        :     none
 
/* Purpose       :     Executes each query called from ExecuteQuery() 
                       method
*/
 
private void ExecuteQuery(string dataBaseName , string query)
{
    DataTable dt;
    if (ds == null) 
    {
        ds  = new DataSet("result");
    }
    dt = dbh.ExecDT(query);
    if (dt != null)
        ds.Tables.Add(dt);
    ((frmQueryWriter)this.ActiveMdiChild).dgResult.DataSource = ds;
    dt.Dispose();
}
 
// Function name :     AddToArray
// Parameters    :     (in string QueryText)
// Return        :     none
 
/* Purpose       :     Splits the query based on “go” delimeter and 
                       stores it in array [arrQuery]
                       This [arrQuery] is used in ExecuteQuery()
*/
 
private void AddToArray(string QueryText)
{

    Regex r = new Regex("go",RegexOptions.IgnoreCase);
    arrQuery = r.Split(QueryText);
           
}

TODO

• Error Handling -> Both at code level i.e. try-catch block and, in case of any incorrect SQL statement, a provision is to be made to display a user friendly error message.
• Connect/Disconnect to multiple servers.

Credits

• SqlDBHandler.cs - by ASP.NET Enterprise Manager.

Revision History

• 12-23-2003: Original article