Click here to Skip to main content
15,918,343 members
Articles / Database Development
Tip/Trick

SQL Executer to run SQL Queries

Rate me:
Please Sign up or sign in to vote.
1.00/5 (1 vote)
21 Feb 2011CPOL 16.2K   3   1
Installing Oracle or SQL Server just 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.
Introduction

Installing Oracle or SQL Server just 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 Microsoft Access. It comes with Microsoft Office package and normally it is 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 in the project

C#
using System.Data.OleDb;
using System.IO;

Define Variables for use in the code:
C#
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.
C#
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 database and add them to ListBox.

Now we need to make SelectedIndexChange Event for the ListBox:
C#
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 result of sqlcmd.
C#
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 user SQL query
C#
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";
    }
}


For more details,

View Article & Download Source Code Here[^].

License

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


Written By
Pakistan Pakistan
I am a learner.

Comments and Discussions

 
GeneralReason for my vote of 1 1. Access is not needed for this, th... Pin
FZelle21-Feb-11 23:35
FZelle21-Feb-11 23:35 
Reason for my vote of 1
1. Access is not needed for this, the Jet Engine does the Access access.
2. If you would know the basics of ADO.NET you would not create the Connection beforehand. The Adapter can use a connectionstring and then it opens and closes the Connection itself.
3. If you want to fill a datatable, do it, don't use a dataset if you don't need one.
4. After a new on DS/DT you don't need to clear it, it is not "dirty".
5. Because of 2, you only need a global variable for the connectionstring
6. There is a reason why a function can have parameters, not to missuse global variables, so use a parameter for Execute
7. We are not in VB6 anymore, get rid of those ToString() and use string.Format
8. Do not catch Exceptions in the dal ( even if it is located in your form ). Catch the exceptions where it makes sence, in the caller.

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.