SQL Executer

Introduction

Installing Oracle or SQL Server to practice SQL Queries is not a good idea. They occupy a lot of space and also slow down the normal processing of the pc. The better option is to use MS Access. It comes with an MS Office package, which is usually easily available on every pc.

Designing User Interface

1. TextBox in Top Center for writing SQL queries
2. ListBox on Left for List of Tables
3. DataGridView for showing data from a particular table

Using the Code

Add the following to the project

using System.Data.OleDb;  
using System.IO;  

Define Variables for use in the code.

private OleDbConnection mycon;  
private DataSet ds;  
private OleDbDataAdapter da;  
private string sqlcmd = "SELECT * FROM Class";  
protected void Page_Load(object sender, EventArgs e)  

On Load, fill the list box from the tables present in the database.

void table()   {
     listBox1.Items.Clear();
     textBox1.Text = "";
     mycon = new OleDbConnection(
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Directory
                                                           .GetCurrentDirectory() + "\\zipcodes.mdb;Persist Security Info=True");
     mycon.Open();
     DataTable tables = mycon.GetOleDbSchemaTable(
      OleDbSchemaGuid.Tables, new object[]  { null, null, null, "TABLE"  });  
foreach (DataRow row in tables.Rows)  
listBox1.Items.Add(row[2]);
     mycon.Close();
     mycon.Dispose();
    
}

The above code gets the name of tables from the database and add them to ListBox.

Now we need to make SelectedIndexChange Event for the ListBox.

private void listBox1_SelectedIndexChanged_1(object sender, EventArgs e)  
{  
    try  
    {  
        string tname = listBox1.SelectedItem.ToString();  
        sqlcmd = "select * from " + tname;  
        execute();  
    }  
    catch (Exception ex)  
    {  
        Messagebox.Show(ex.Message);  
    }  
}  

Execute() function fills the DataGridView with the data extracted from the database as a result of sqlcmd.

void execute()   {
     try
       {
         mycon = new OleDbConnection(
          "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Directory
                                                               .GetCurrentDirectory() + "\\zipcodes.mdb;Persist Security Info=True");
         ds = new DataSet();
         ds.Clear();
         mycon.Open();
         da = new OleDbDataAdapter(sqlcmd, mycon);
         da.Fill(ds, "mydata");
        
    dataGridView1.DataSource = ds.Tables["mydata"];
         label3.Text = "Rows=" + ds.Tables["mydata"].Rows.Count.ToString();
         label4.Text = "Columns=" + ds.Tables["mydata"]
                                       .Columns.Count.ToString();
         mycon.Close();
         mycon.Dispose();
        
    }
     catch (OleDbException ole_execp)   {
       MessageBox.Show("Error in Command Execution");
      
  }
     catch (Exception ex)   {
       MessageBox.Show(ex.Message);
      
  }
     
}
  

Finally, write the Run Button Click Event to execute the user SQL query

private void button1_Click_1(object sender, EventArgs e)  
{  
    if ((textBox1.Text != "") && (textBox1.Text != "Write Command") && (textBox1.Text != "Some Mistake in Command"))  
    {  
        sqlcmd = textBox1.Text;  
        execute();  
        table();  
    }  
    else  
    {  
        textBox1.Text = "Write Command";  
    }  
}  

Summary

The article is written for beginners, the methods to communicate with the database are kept simple and complex, and an improved code version for dealing with the database can be viewed here.

Reference

• How to Easily Query a Database