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

• Download source files - 149 Kb

Introduction

The QASharp V.1.1 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 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.

Credits

SqlDBHandler.cs by ASP Enterprise Manager.

Functionality Supported

• Create new query
• Save existing query
• Open existing query
• Execute queries
• Multiple query output window
• Connect to different data sources
• Syntax highlighting of major keywords
• Added 'Show Errors' in Help menu

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>

• 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.

To select a different server: Click on File-> Connect. Enter the required parameters and click on Connect.

The main code changes from V1.0 to V1.1

/// <summary>
    /// Used to execute queries
/// </summary>

/// <param name="dataBaseName">the name of the database</param>
/// <param name="query">the query string to execute</param>
private void ExecuteQuery(string dataBaseName , string query)
{
    DataTable dt;
    if (ds == null) 
    {
        ds  = new DataSet("result");
    }
    if (query.Trim() == "")
        return;

    try
    {
        dt = dbh.ExecDT(query);
        if (dt != null)
        {
            ds.Tables.Add(dt);
            ((frmQueryWriter)this.ActiveMdiChild).RenderGrid (dt);
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

/// <summary> 
///   Creates dynamic grids in the output windows based on no. of 
///   queries
/// </summary>

/// <param name="dt">the datatable </param>

public void  RenderGrid(DataTable dt)
{
    if (buttonIndex == 0)   // 0 the query
    {
        this.dgResult.DataSource = dt;      
        buttonIndex++;
        return;
    }

    tabResult.Controls.Add(new TabPage("Result " 
        + buttonIndex.ToString()));

    tabResult.TabPages[tabResult.TabPages.Count-1].Name = 
        "tabPages"+ buttonIndex.ToString();

    CreateGridInTabControl(dt, "dgResult" + 
        buttonIndex.ToString());

    buttonIndex++;
}

/// <summary>
/// This code actually creates the grid control in the output 
/// window
/// </summary>

/// <param name="dt">the datatable </param>
/// <param name="gridName">the grid name</param>

public void CreateGridInTabControl(System.Data.DataTable dt, string gridName)
{
    System.Windows.Forms.DataGrid dg = new System.Windows.Forms.DataGrid ();
    dg.Name = gridName;
    dg.DataSource = dt;
    dg.Top = dgResult.Top;
    dg.Left = dgResult.Left;
    dg.Anchor = dgResult.Anchor;
    dg.Dock = dgResult.Dock;
    dg.Width = dgResult.Width;
    dg.Height = dgResult.Height;
 
       tabResult.TabPages[tabResult.TabPages.Count-1].Controls.Add(dg);
}

To connect to the database, the following code snippet is used:

/// <summary>
/// Connect to the datasource
/// </summary>
/// <param name="sender"></param>

/// <param name="e"></param>

private void btnConnect_Click(object sender, System.EventArgs e)
{
    this.Cursor = Cursors.WaitCursor;
    QASharp.DataBase db = new QASharp.DataBase();

    try
    {
        db.GetDatabases(txtServer.Text,
        txtUserID.Text,txtPassword.Text,txtDatabase.Text);

        Common.Common.SetDefaultDatabase(((frmQASharp)
          this.MdiParent).cmbDatabase, txtDatabase.Text);
        this.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error in connection. : " + ex.Message);
    }
    this.Cursor = Cursors.Default;
 }

To set the default database, the following code snippet is used:

public static void SetDefaultDatabase(ComboBox combo)
{
    string defaultDatabase;
    defaultDatabase = 
        System.Configuration.ConfigurationSettings.AppSettings.Get("defaultdatabase");
    combo.SelectedIndex = combo.FindString(defaultDatabase);
}

To display the error messages, the following code snippet is used:

private void ShowErrors()
{
    if (ErrorText.Trim() == "" ) 
    {
        MessageBox.Show("No Errors.");
    }
    else
    {
        MessageBox.Show(ErrorText);
    }
}

To save the existing query:

private void SaveFile()
{
    frmQueryWriter frmCurrentForm;
    frmCurrentForm = (frmQueryWriter)this.ActiveMdiChild;

    if (frmCurrentForm.Tag != null)
    {
        if (frmCurrentForm.Tag.ToString().Trim() != "") 
        {
            frmCurrentForm.rtbQueryPad.SaveFile(frmCurrentForm.Tag.ToString(),
               System.Windows.Forms.RichTextBoxStreamType.PlainText);
        }
    }
    else 
    {
        saveFD.ShowDialog();
        frmCurrentForm.rtbQueryPad.SaveFile(saveFD.FileName,
            System.Windows.Forms.RichTextBoxStreamType.PlainText);
        frmCurrentForm.Text = saveFD.FileName;
    }
}

TO-DO

• Efficient algorithm for syntax highlighting.
• A treeview of the left panel showing the number of servers connected and the related database and tables.

Revision history

• 1.0 - 12-23-2003 - Original article
• 1.1 - 02-22-2004
  • Multiple query output window.
  • Syntax highlighting.
  • Connect to different data source menu option.