Simple Query&Analyst tool for Access2003

DataBase Access :

using System;

using System.Data.OleDb;

using System.Data;

using System.Windows.Forms;

namespace AccessAnalyst

{

       /// <summary>

       ///Database Access

       /// </summary>

       public class DBAccess

       {

              /// <summary>

              /// Get Connection string

              /// </summary>

              /// <returns>connection string</returns>

              internal static string GetConnectionStr()

              {

                     string str = string.Empty;

                     str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ Application.StartupPath +"//library.mdb";

                     return str;

              }

 

              /// <summary>

              ///via execute SQL sentence get DataTable object

              /// </summary>

              /// <param name="sql">SQL sentences</param>

              /// <returns>Seek result </returns>

              public static DataTable GetDataTable( string sql )

              {

                     DataTable dt = new DataTable();

                     OleDbConnection conn = new OleDbConnection( GetConnectionStr() );

                     OleDbCommand odc = new OleDbCommand( sql, conn );

                     OleDbDataAdapter oda = new OleDbDataAdapter( odc);

                     try

                     {

                            odc.Connection.Open();

                            oda.Fill( dt );

                            return dt;

                     }

                     catch( Exception e )

                     {

                            MessageBox.Show( "an unknown error was encountered!" + e.Message, "Error" );

                            return null;

                     }

                     finally

                     {

                            odc.Connection.Close();

                            odc.Dispose();

                            oda.Dispose();

                     }

              }

       }

}

 

Graphical User Interface:

using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.Data;

using System.Threading;

namespace AccessAnalyst

{

       /// <summary>

       /// Query&Analyst for access 2003

       /// </summary>

       public class Form1 : System.Windows.Forms.Form

       {

              private System.Windows.Forms.Button button1;

              private System.Windows.Forms.RichTextBox rtbSql;

              private System.Windows.Forms.DataGrid dataGrid1;

              private System.Windows.Forms.DataGrid dataGrid2;

              private System.Windows.Forms.RichTextBox richTextBox1;

              private System.Windows.Forms.Button button2;

              private System.Windows.Forms.ProgressBar pb;

              private System.ComponentModel.IContainer components = null;

 

              public Form1()

              {

                     InitializeComponent();

              }

 

              /// <summary>

              /// clear the resource which was using

              /// </summary>

              protected override void Dispose( bool disposing )

              {

                     if( disposing )

                     {

                            if (components != null)

                            {

                                   components.Dispose();

                            }

                     }

                     base.Dispose( disposing );

              }

 

              #region Windows ´°ÌåÉè¼ÆÆ÷Éú³ÉµÄ´úÂë

              /// <summary>

              /// Éè¼ÆÆ÷Ö§³ÖËùÐèµÄ·½·¨ - ²»ÒªÊ¹ÓôúÂë±à¼­Æ÷ÐÞ¸Ä

              /// ´Ë·½·¨µÄÄÚÈÝ¡£

              /// </summary>

              private void InitializeComponent()

              {

                     System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(Form1));

                     this.button1 = new System.Windows.Forms.Button();

                     this.rtbSql = new System.Windows.Forms.RichTextBox();

                     this.dataGrid1 = new System.Windows.Forms.DataGrid();

                     this.dataGrid2 = new System.Windows.Forms.DataGrid();

                     this.richTextBox1 = new System.Windows.Forms.RichTextBox();

                     this.button2 = new System.Windows.Forms.Button();

                     this.pb = new System.Windows.Forms.ProgressBar();

                     ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();

                     ((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).BeginInit();

                     this.SuspendLayout();

                     //

                     // button1

                     //

                     this.button1.Location = new System.Drawing.Point(16, 120);

                     this.button1.Name = "button1";

                     this.button1.TabIndex = 0;

                     this.button1.Text = "Execute";

                     this.button1.Click += new System.EventHandler(this.button1_Click);

                     //

                     // rtbSql

                     //

                     this.rtbSql.Font = new System.Drawing.Font("Verdana", 7.5F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));

                     this.rtbSql.Location = new System.Drawing.Point(16, 16);

                     this.rtbSql.Name = "rtbSql";

                     this.rtbSql.Size = new System.Drawing.Size(240, 96);

                     this.rtbSql.TabIndex = 1;

                     this.rtbSql.Text = "";

                     //

                     // dataGrid1

                     //

                     this.dataGrid1.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left)

                            | System.Windows.Forms.AnchorStyles.Right)));

                     this.dataGrid1.DataMember = "";

                     this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;

                     this.dataGrid1.Location = new System.Drawing.Point(264, 16);

                     this.dataGrid1.Name = "dataGrid1";

                     this.dataGrid1.Size = new System.Drawing.Size(528, 280);

                     this.dataGrid1.TabIndex = 2;

                     this.dataGrid1.CurrentCellChanged += new System.EventHandler(this.dataGrid1_CurrentCellChanged);

                     //

                     // dataGrid2

                     //

                     this.dataGrid2.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)

                            | System.Windows.Forms.AnchorStyles.Left)

                            | System.Windows.Forms.AnchorStyles.Right)));

                     this.dataGrid2.DataMember = "";

                     this.dataGrid2.HeaderForeColor = System.Drawing.SystemColors.ControlText;

                     this.dataGrid2.Location = new System.Drawing.Point(264, 304);

                     this.dataGrid2.Name = "dataGrid2";

                     this.dataGrid2.Size = new System.Drawing.Size(528, 264);

                     this.dataGrid2.TabIndex = 3;

                     this.dataGrid2.CurrentCellChanged += new System.EventHandler(this.dataGrid1_CurrentCellChanged);

                     //

                     // richTextBox1

                     //

                     this.richTextBox1.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));

                     this.richTextBox1.Location = new System.Drawing.Point(16, 304);

                     this.richTextBox1.Name = "richTextBox1";

                     this.richTextBox1.Size = new System.Drawing.Size(232, 96);

                     this.richTextBox1.TabIndex = 5;

                     this.richTextBox1.Text = "";

                     //

                     // button2

                     //

                     this.button2.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));

                     this.button2.Location = new System.Drawing.Point(16, 408);

                     this.button2.Name = "button2";

                     this.button2.TabIndex = 4;

                     this.button2.Text = "Execute";

                     this.button2.Click += new System.EventHandler(this.button2_Click);

                     //

                     // pb

                     //

                     this.pb.Location = new System.Drawing.Point(16, 160);

                     this.pb.Maximum = 1000000;

                     this.pb.Name = "pb";

                     this.pb.Size = new System.Drawing.Size(232, 16);

                     this.pb.Step = 1;

                     this.pb.TabIndex = 6;

                     this.pb.Visible = false;

                     //

                     // Form1

                     //

                     this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);

                     this.ClientSize = new System.Drawing.Size(800, 574);

                     this.Controls.Add(this.pb);

                     this.Controls.Add(this.richTextBox1);

                     this.Controls.Add(this.button2);

                     this.Controls.Add(this.dataGrid2);

                     this.Controls.Add(this.dataGrid1);

                     this.Controls.Add(this.rtbSql);

                     this.Controls.Add(this.button1);

                     this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));

                     this.Name = "Form1";

                     this.Text = "AccessAnalyst";

                     ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();

                     ((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).EndInit();

                     this.ResumeLayout(false);

 

              }

              #endregion

 

              /// <summary>

              /// use system style

              /// </summary>

              [STAThread]

              static void Main()

              {

                     Application.EnableVisualStyles();

                     Application.DoEvents();

                     Application.Run(new Form1());

              }

 

              private void button1_Click(object sender, System.EventArgs e)

              {

                     string str = rtbSql.Text;

                     DataTable dt = DBAccess.GetDataTable( str );

                     if( dt != null )

                     {

                            InitializeDataGrid( dt, this.dataGrid1 );

                     }

              }

 

              /// <summary>

              /// Initialize the specified DataGrid object

              /// </summary>

              /// <param name="dt">the DataSource which will be specified to DataGrid</param>

              /// <param name="dg">DataGrid</param>

              private void InitializeDataGrid( DataTable dt, DataGrid dg )

              {

                     dg.TableStyles.Clear();

                     dg.TableStyles.Add( new DataGridTableStyle() );

                     dg.TableStyles[0].GridColumnStyles.Clear();

                     for( int i = 0; i < dt.Columns.Count; i ++ )

                     {

                            DataGridTextBoxColumn dgbc = new DataGridTextBoxColumn();

                            dgbc.MappingName = dt.Columns[ i ].ToString();

                            dgbc.Width = 125;

                            dgbc.NullText = string.Empty;

                            dgbc.HeaderText = dgbc.MappingName;

 

                            dg.TableStyles[0].GridColumnStyles.Add( dgbc );

                     }

                     dg.DataSource = dt;

                     ModifyGrid( dg );

              }

 

              private void button2_Click(object sender, System.EventArgs e)

              {

                     string str = this.richTextBox1.Text;

                     ParseString( str );

                     DataTable dt = DBAccess.GetDataTable( str );

                     if( dt != null )

                     {

                            InitializeDataGrid( dt, this.dataGrid2 );

                     }

              }

 

              /// <summary>

              /// Delete the textBox style of DataGrid

              /// </summary>

              /// <param name="dg">DataGrid object</param>

              private void ModifyGrid( DataGrid dg )

              {

                     DataGridTextBoxColumn dgbc = null;

                     foreach( DataGridColumnStyle dgcs  in dg.TableStyles[ 0 ].GridColumnStyles )

                     {

                            dgbc = dgcs as DataGridTextBoxColumn;

                            dgbc.TextBox.Parent.Controls.Remove( dgbc.TextBox );

                     }

              }

 

              private void dataGrid1_CurrentCellChanged(object sender, System.EventArgs e)

              {

                     DataGrid dg = sender as DataGrid;

                     dg.Select( dg.CurrentRowIndex );

              }

 

              /// <summary>

              ///Parse string, judge safety

              /// </summary>

              /// <param name="str">specified string</param>

              private void ParseString( string str )

              {

                     string mark = string.Empty;

                     str = str.Trim();

                     int i = str.Split( new char[]{' '} ).Length;

                     foreach( string a in str.Split( new char[]{' '} ) )

                     {

                           

                           

                            if( a== string.Empty )

                            {

                                   i --;

                            }

                            else

                            {

                                   if( ParseKeyword( a, "DELETE" ) )

                                   {

                                          mark += "/n include delete command";

                                   }

                                   else if( ParseKeyword( a, "insert" ) )

                                   {

                                          mark += "/n include insert command";

                                   }

                            }

                     }

                     MessageBox.Show( i.ToString() + "words" + mark );

              }

 

              /// <summary>

              /// Parse keywords

              /// </summary>

              /// <param name="sourceStr">string</param>

              /// <param name="keyword">keywords</param>

              /// <returns>result</returns>

              private bool ParseKeyword( string sourceStr, string keyword )

              {

                     bool b = false;

                     if( sourceStr.Length != keyword.Length )

                     {

                            return b;

                     }

                     int i = 0;

                     for(; i < sourceStr.Length; i ++ )

                     {

                            char c = sourceStr[ i ];

                            if( c != keyword[ i ] && char.ToLower(c ) != keyword[ i ] && char.ToUpper( c ) != keyword[ i ] )

                            {

                                   b = false;

                                   break;

                            }

                            if( c == keyword[ i ] || char.ToLower(c ) == keyword[ i ] || char.ToUpper( c ) == keyword[ i ] )

                            {

                                   b = true;

                                   continue;

                            }

                     }

                     return b;

              }

 

       }

}