Click here to Skip to main content
15,867,488 members
Articles / Desktop Programming / MFC
Article

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

Rate me:
Please Sign up or sign in to vote.
4.67/5 (16 votes)
11 Oct 2004CPOL1 min read 125.2K   2.6K   53   30
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.

Sample screenshot

Sample screenshot

Introduction

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

All suggestions, apprehensions, criticisms are welcome.

Credits

SqlDBHandler.cs by http://www.aspenterprisemanager.com/.

Functionality Supported

  • Create new query.
  • Save existing query.
  • Open existing query.
  • Execute queries.
  • Multiple query output window.
  • Connect to different data source.
  • Flicker free Syntax highlighting of major keywords.
  • Added Show Errors in Help menu.
  • Object browser.
  • Data/Stored procedure editing functionality.
  • Printing 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
<?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>
KeysValues
ServerThe server to connect to
DatabaseThe initial database to connect to
UidUser ID
PwdPassword

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.
  • To select a different server:
    • Click on File-> Connect. Enter the required parameters, and click on Connect.

The main code changes from V1.2 to V1.3

C#
/// Syntax highlighting
private void rtbQueryPad_TextChanged(object sender, System.EventArgs e)
{
   /// Split the line into tokens.
   Regex r = new Regex("([ \\t{}();])");

   if (rtbQueryPad.Text.Trim() =="")
    return;

   /// Get current line

   int currentLine = rtbQueryPad.GetLineFromCharIndex(
    rtbQueryPad.GetCharIndexFromPosition(Cursor.Position));
   
   /// When large amount of text is copied, generates error
   
   try {
    string currentLineString = rtbQueryPad.Lines[currentLine];

    int startPos = 0;
    for (int i = 0; i < currentLine-1; i++)
    {
     startPos = startPos + rtbQueryPad.Lines[i].Length;
    }

    string [] tokens = r.Split(currentLineString);
    
    foreach (string token in tokens) 
      {
     if (token.Trim() != string.Empty) 
     {
      if (IsKeyWord(token))
       FindAndHighlight(token,Color.Blue,false,true,startPos);
      else
       FindAndHighlight(token,Color.Black,false,true,startPos);
     }
    }
   }
   catch (Exception ex)
   {
    Console.WriteLine(ex.Message);
   }
} 

private bool IsKeyWord(string input)
{
  for (int i = 0; i < keywords.Length; i++) 
  {
        if (input.Trim().ToLower() == keywords[i].ToLower())
    {
     return true;
    }
  }
  return false;
}

/// More functionality to be added later.
/// Code to print the query
protected void pdQuery_PrintPage (object sender, 
 System.Drawing.Printing.PrintPageEventArgs ev)
  {
   float linesPerPage = 0;
   float yPosition = 0;
   int count = 0;
   float leftMargin = ev.MarginBounds.Left;
   float topMargin = ev.MarginBounds.Top;
   string line = null;
   
   frmQueryWriter frmCurrentForm;
   frmCurrentForm = (frmQueryWriter)this.ActiveMdiChild;

   Font printFont = frmCurrentForm.rtbQueryPad.Font;
   SolidBrush myBrush = new SolidBrush(Color.Black);

   // Work out the number of lines per page, using the MarginBounds.
   linesPerPage = ev.MarginBounds.Height / printFont.GetHeight(ev.Graphics);

   // Iterate over the string using the StringReader, printing each line.
   while(count < linesPerPage && ((line=myReader.ReadLine()) != null)) 
   {
    // calculate the next line position based on 
    // the height of the font according to the printing device
    yPosition = topMargin + (count * printFont.GetHeight(ev.Graphics));

    // draw the next line in the rich edit control
    
    ev.Graphics.DrawString(line, printFont, myBrush, 
     leftMargin, yPosition, new StringFormat());
    count++;
   }

   // If there are more lines, print another page.
   if(line != null)
    ev.HasMorePages = true;
   else
    ev.HasMorePages = false;

   myBrush.Dispose();
      
  }

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

// Update the table with changed values.

C#
public void UpdateDT(DataTable dt)
{
   string sql;dt.TableName;                 
   try
   {
       if (m_conn.State == ConnectionState.Closed)
       {
           m_conn.Open ();
       }
       SqlDataAdapter da = new SqlDataAdapter(
        sql,m_conn.ConnectionString);
       string field="";
       string values="";
       string sql1 = "";
       foreach(DataRow dtr in  dt.Rows)
       { 
          if (dtr.RowState == DataRowState.Added)  
          {      
             sql1 = " insert into " + dt.TableName + " (";          
             field = "";                
             values = "";       
             foreach(DataColumn dc in dt.Columns)   
             {          
                if (dc.AutoIncrement == false)          
                {   
                    field += dc.ColumnName + ",";   
                    values +=  "'" +  dtr[dc.ColumnName] + "',";          
                }   
             }  
             field = field.Substring(0,field.Length-1);  
             values = values.Substring(0,values.Length-1); 
             sql1 += field  + ")" + "Values (" + values + ")" ;
          }
          else if (dtr.RowState == DataRowState.Modified)
          {
             sql1 = " update  " + dt.TableName + " set ";
             string stmt="";
             string where = " where ";

             foreach(DataColumn dc in dt.Columns)
             {
                field  = dc.ColumnName + " = ";
                values =  "'" +  
                 dtr[dc.ColumnName].ToString().Trim() + "' ";
                stmt += field +  values + ",";
                where += field + "'" +  
                dtr[dc.ColumnName,
                 DataRowVersion.Original].ToString().Trim() 
                     +   "' and " ;
             }

             stmt = stmt.Substring(0,stmt.Length-1);
             where = where.Substring(0,where.Length-5);
             sql1 += stmt + where;
          }

          else if (dtr.RowState == DataRowState.Deleted)
          {
             sql1 = " delete from " + dt.TableName ;
             string stmt="";
             string where = " where ";
             foreach(DataColumn dc in dt.Columns)
             {
                 field  = dc.ColumnName + " = ";
                 where += field + "'" +  
                 dtr[dc.ColumnName,DataRowVersion.Original].ToString().
                    Trim() + "' and " ;
             }
             where = where.Substring(0,where.Length-5);
             sql1 += stmt + where;
          }
          if (sql1.Trim() != string.Empty)
                ExecNonQuery(sql1);
      }

   }

   catch (Exception ex)
   {
       System.Diagnostics.Debug.WriteLine(ex.Message);
   }
}

Revision History

  • 06-Oct-2004
    • Added windows integrated security while logging in.
    • Improved flicker-free Syntax highlighting.
    • Added support for printing.
    • Bug fixes.
  • 05-Aug-2004
    • Object Browser.
    • Data / Stored Procedure Editor.
    • Database Tree Control.
  • 1.1 22nd-Feb-2004
    • Multiple query output window.
    • Syntax highlighting.
    • Connect to different data source menu option.
  • License

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


    Written By
    Founder Algorisys Technologies Pvt. Ltd.
    India India
    Co Founder at Algorisys Technologies Pvt. Ltd.

    http://algorisys.com/
    https://teachyourselfcoding.com/ (free early access)
    https://www.youtube.com/user/tekacademylabs/

    Comments and Discussions

     
    QuestionI need some help Pin
    Member 204359621-Sep-08 21:52
    Member 204359621-Sep-08 21:52 
    General.QASharp Development stopped !!! Pin
    Rajesh Pillai3-Jan-06 3:44
    Rajesh Pillai3-Jan-06 3:44 
    Questionhandle udf's ? Pin
    Anonymous28-Jun-05 12:18
    Anonymous28-Jun-05 12:18 
    QuestionStill no update? Pin
    andrew|14-Mar-05 16:15
    andrew|14-Mar-05 16:15 
    AnswerRe: Still no update? Pin
    Rajesh Pillai14-Mar-05 23:32
    Rajesh Pillai14-Mar-05 23:32 
    GeneralCheck &quot;SQLBuddy&quot; project Pin
    BlackTigerAP24-Oct-04 21:02
    BlackTigerAP24-Oct-04 21:02 
    QuestionDo you know something about 'Spliter'? Pin
    BlackTigerAP23-Oct-04 12:14
    BlackTigerAP23-Oct-04 12:14 
    AnswerRe: Do you know something about 'Spliter'? Pin
    Rajesh Pillai23-Oct-04 21:41
    Rajesh Pillai23-Oct-04 21:41 
    GeneralRe: Do you know something about 'Spliter'? Pin
    BlackTigerAP24-Oct-04 20:59
    BlackTigerAP24-Oct-04 20:59 
    GeneralRe: Do you know something about 'Spliter'? Pin
    Rajesh Pillai24-Oct-04 21:06
    Rajesh Pillai24-Oct-04 21:06 
    GeneralSlowest DB app! Sorry but it's true... Pin
    BlackTigerAP23-Oct-04 12:13
    BlackTigerAP23-Oct-04 12:13 
    GeneralRe: Slowest DB app! Sorry but it's true... Pin
    Rajesh Pillai23-Oct-04 21:38
    Rajesh Pillai23-Oct-04 21:38 
    GeneralNext release!!! Pin
    Rajesh Pillai11-Oct-04 23:05
    Rajesh Pillai11-Oct-04 23:05 
    In the next release there will be a lot of refactoring and functionality additions and code optimizations.



    Enjoy Coding,
    Rajesh Pillai

    GeneralRe: Next release!!! Pin
    Tom McAnnally12-Oct-04 16:34
    Tom McAnnally12-Oct-04 16:34 
    GeneralRe: Next release!!! Pin
    Rajesh Pillai12-Oct-04 18:21
    Rajesh Pillai12-Oct-04 18:21 
    GeneralSyntax Highlighting Pin
    labreuer12-Aug-04 3:03
    labreuer12-Aug-04 3:03 
    GeneralRe: Syntax Highlighting Pin
    Rajesh Pillai12-Aug-04 4:22
    Rajesh Pillai12-Aug-04 4:22 
    GeneralRe: Syntax Highlighting Pin
    Finn Kjær Rasmussen13-Sep-04 23:50
    Finn Kjær Rasmussen13-Sep-04 23:50 
    GeneralRe: Syntax Highlighting Pin
    Rajesh Pillai13-Sep-04 23:56
    Rajesh Pillai13-Sep-04 23:56 
    GeneralSuggestions Pin
    Daaron11-Aug-04 13:06
    Daaron11-Aug-04 13:06 
    GeneralRe: Suggestions Pin
    Rajesh Pillai11-Aug-04 19:06
    Rajesh Pillai11-Aug-04 19:06 
    GeneralRe: Suggestions Pin
    Daaron12-Aug-04 4:23
    Daaron12-Aug-04 4:23 
    GeneralRe: Suggestions Pin
    Rajesh Pillai12-Aug-04 4:27
    Rajesh Pillai12-Aug-04 4:27 
    GeneralHint... Pin
    Thomas Latuske5-Aug-04 9:42
    Thomas Latuske5-Aug-04 9:42 
    GeneralRe: Hint... Pin
    Rajesh Pillai5-Aug-04 19:37
    Rajesh Pillai5-Aug-04 19:37 

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

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