Reader Level:
ARTICLE

Query Builder

Posted by Neelam Iyer Articles | ADO.NET October 17, 2001
This application is that of a QueryBuilder in CSharp.This application is for Beta2 SDK version of DotNetFramework.
  • 0
  • 0
  • 47987
Download Files:
 

Description

This application is that of a QueryBuilder in CSharp.This application is for Beta2 SDK version of .NetFramework. I have used Oracle as the backend. For database connectivity and related operations, I have used System.Data.Odbc.dll file. For this application you'll have to download and install Oracle.NET from the Microsoft site. And yes you need the Microsoft.Net Framework installed on your machine(That goes without saying).The path of System.Data.Odbc.dll file may differ from the path of your application.If you wish you can copy the dll file in the folder in which you have your application.

How the application works:

The GUI is a Windows Form which has a Mainmenu,Buttons,TextBox ,ListBox and Statusbar.

You can  get connected to the database by clicking on the Connect button .You have to provide DSN name,Username and Password to connect to the database(assuming you are using Oracle as the database). You can select all the tables by clicking on the UserTables button.Only five tables are displayed at a time and you can click the NextRowSet button to move to the next five tables and so on. There is an execute button to execute any query.If the query is for selecting records, again only five records are displayed per page and you can click the NextRowSet button to move to the next five records and so on.

The disconnect button gets you disconnected from the database. Mainmenu consists of three Menuitems File,Edit and Status.You can use the different File menu items to perform file related operations.Edit Menuitems consist of cut, copy and paste options.Status menuitem controls the visibility of the statusbar.

There are two files queryanal.cs and connect.cs.

Compile the files by using the following command:

csc /r:System.Data.Odbc.dll queryanal.cs connect.cs

Souce code:

//queryanal.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.Odbc;
using System.IO;
using System.Drawing.Printing;
public class queryanalyser: Form
{
int lowerlimit=1;
int upperlimit=5;
int increment=5;
int dispint;
Panel p1,p2;
ListView lv;
RichTextBox t1;
ToolBar tbar;
ToolBarButton b1,b2,b3,b4,b5;
StatusBar statusBar;
private System.ComponentModel.Container components;
OdbcConnection connection;
string fileName="";
string flag="";
PrintDialog printDialog1;
PageSetupDialog pageSetupDialog1;
MenuItem mistatus;
public queryanalyser()
{
InitializeComponent();
}
public static void Main()
{
queryanalyser qf=new queryanalyser();
qf.FormBorderStyle=FormBorderStyle.Fixed3D;
Application.Run(qf);
}
public void InitializeComponent()
{
//initialising components..
this.components = new System.ComponentModel.Container ();
p1=new Panel();
p1.Location=new Point(0,0);
p1.Size=new Size(800,200);
p2=new Panel();
p2.Location=new Point(0,200);
p2.Size=new Size(800,320);
this.t1=new RichTextBox();
t1.Location=new Point(0,40);
t1.Size=new Size(700,160);
t1.Multiline=true;
t1.ScrollBars = RichTextBoxScrollBars.ForcedVertical;
t1.ScrollBars= RichTextBoxScrollBars.ForcedHorizontal;
t1.WordWrap = true;
this.b1 = new System.Windows.Forms.ToolBarButton ();
b1.Text = "Connect";
this.b2 = new System.Windows.Forms.ToolBarButton ();
b2.Text = "Disconnect";
b2.Enabled=false;
this.b3 = new System.Windows.Forms.ToolBarButton ();
b3.Text = "User Tables";
b3.Enabled=false;
this.b4 = new System.Windows.Forms.ToolBarButton ();
b4.Text = "Execute";
b4.Enabled=false;
this.b5 = new System.Windows.Forms.ToolBarButton ();
b5.Text = "Next Row Set";
b5.Enabled=false;
this.tbar = new System.Windows.Forms.ToolBar ();
tbar.ButtonClick += new ToolBarButtonClickEventHandler(b1_Click);
tbar.Location=new Point(0,0);
tbar.ButtonSize = new System.Drawing.Size (130,35);
tbar.Size = new System.Drawing.Size (650, 40);
tbar.TabIndex = 2;
tbar.DropDownArrows = true;
this.tbar.Buttons.AddRange(new System.Windows.Forms.ToolBarButton[]
{this.b1, this.b2, this.b3, this.b4, this.b5});
lv = new ListView() ;
lv.View = View.Details ;
lv.GridLines = false ;
lv.FullRowSelect = true ;
lv.MultiSelect = false ;
lv.Width = 800 ;
lv.Height=320;
MainMenu mainMenu = new MainMenu();
this.Menu = mainMenu;
ContextMenu label1ContextMenu = new ContextMenu();
Label label1 = new Label();
label1.ContextMenu = label1ContextMenu;
MenuItem miFile = mainMenu.MenuItems.Add("&File");
MenuItem mnew=new MenuItem("New", new EventHandler(this.FileNew_Clicked),Shortcut.CtrlN);
miFile.MenuItems.Add(mnew);
mnew.Select+=new EventHandler(mnew_Click);
miFile.MenuItems.Add("-"); // Gives us a seperator
MenuItem mopen=new MenuItem("Open", new EventHandler(this.FileOpen_Clicked),Shortcut.CtrlO);
miFile.MenuItems.Add(mopen);
mopen.Select+=new EventHandler(mopen_Click);
miFile.MenuItems.Add("-");
MenuItem msave=new MenuItem("Save", new EventHandler(this.FileSave_Clicked),Shortcut.CtrlS);
miFile.MenuItems.Add(msave);
msave.Select+=new EventHandler(msave_Click);
miFile.MenuItems.Add("-");
MenuItem msaveas=new MenuItem("SaveAs", new EventHandler(this.FileSaveAs_Clicked), Shortcut.CtrlA);
miFile.MenuItems.Add(msaveas);
msaveas.Select+=new EventHandler(msaveas_Click);
miFile.MenuItems.Add("-");
MenuItem mpagesetup=new MenuItem("PageSetUp", new EventHandler(this.FilePageSetUp_Clicked));
miFile.MenuItems.Add(mpagesetup);
mpagesetup.Select+=new EventHandler(mpagesetup_Click);
miFile.MenuItems.Add("-"); // Gives us a seperator
MenuItem mprint=new MenuItem("Print", new EventHandler(this.FilePrint_Clicked),Shortcut.CtrlP);
miFile.MenuItems.Add(mprint);
mprint.Select+=new EventHandler(mprint_Click);
miFile.MenuItems.Add("-");
MenuItem mexit=new MenuItem("Exit", new EventHandler(this.FileExit_Clicked));
miFile.MenuItems.Add(mexit);
mexit.Select+=new EventHandler(mexit_Click);
miFile = mainMenu.MenuItems.Add("&Edit");
MenuItem mcut=new MenuItem("Cut", new EventHandler(this.FileCut_Clicked),Shortcut.CtrlX);
miFile.MenuItems.Add(mcut);
mcut.Select+=new EventHandler(mcut_Click);
miFile.MenuItems.Add("-");
MenuItem mcopy=new MenuItem("Copy", new EventHandler(this.FileCopy_Clicked),Shortcut.CtrlC);
miFile.MenuItems.Add(mcopy);
mcopy.Select+=new EventHandler(mcopy_Click);
miFile.MenuItems.Add("-");
MenuItem mpaste=new MenuItem("Paste", new EventHandler(this.FilePaste_Clicked), Shortcut.CtrlV);
miFile.MenuItems.Add(mpaste);
mpaste.Select+=new EventHandler(mpaste_Click);
miFile = mainMenu.MenuItems.Add("&View");
mistatus=new MenuItem("Status Bar", new EventHandler(this.StatusBar_Clicked));
mistatus.Checked=true;
miFile.MenuItems.Add(mistatus);
statusBar=new StatusBar();
statusBar.Size = new System.Drawing.Size(800,30);
statusBar.Location = new System.Drawing.Point(0, 500);
statusBar.BackColor = System.Drawing.Color.Black;
statusBar.Text = "Ready";
p1.Controls.Add(tbar);
p1.Controls.Add(t1);
p2.Controls.Add(lv);
this.Controls.Add(p1);
this.Controls.Add(p2);
this.Controls.Add(statusBar);
this.Size=new Size(800,600);
}
protected void b1_Click(object sender,ToolBarButtonClickEventArgs e)
{
if(e.Button==b1)
{
//Calling the Connection method of the connect.cs file for establishing connection to the database..
connect c=new connect();
c.ShowDialog();
connection=c.Connection();
statusBar.Text="Connection established";
b1.Enabled=false;
b2.Enabled=true;
b3.Enabled=true;
b4.Enabled=true;
b5.Enabled=false;
}
if(e.Button==b2)
{
//Closing the connection...
connection.Close();
b1.Enabled=true;
b2.Enabled=false;
b3.Enabled=false;
b4.Enabled=false;
b5.Enabled=false;
t1.Text="";
lv.Clear();
lowerlimit=1;
upperlimit=5;
dispint=0;
}
if(e.Button==b3)
{
//Setting the lower limit and upperlimit for no of records to be displayed per page...
//Retrieving all the tables present in the database....
lowerlimit=1;
upperlimit=5;
lv.Clear();
flag="usertables";
try
{
string query="select * from (select a.* ,rownum rnum from (select * from tab) a where rownum<="+upperlimit+" ) where rnum>="+lowerlimit;
OdbcCommand myCommand= new OdbcCommand(query, connection) ;
myCommand.Connection.Open() ;
OdbcDataReader myReader = myCommand.ExecuteReader CommandBehavior.CloseConnection) ;
int count=myReader.FieldCount-1;
lv.Columns.Add("Row No.", 100, HorizontalAlignment.Center) ;
for(int i=0;i<count;i++)
{
lv.Columns.Add(myReader.GetName(i), 100, HorizontalAlignment.Center) ;
}
dispint=0;
while(myReader.Read())
{
if (!myReader.IsDBNull(0))
lv.Items.Add((dispint+1).ToString()) ;
for (int i = 0; i < count; i++)
{
if (!myReader.IsDBNull(i))
lv.Items[dispint].SubItems.Add(myReader.GetValue(i).ToString()) ;
}
dispint++ ;
}
myReader.Close();
b5.Enabled=true;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
if(e.Button==b4)
{
lowerlimit=1;
upperlimit=5;
flag="execute";
try
{
string a=t1.Text;
//For executing only the select query...
if(a.IndexOf("select")!=-1)
{
lv.Clear();
string query="select * from (select a.* ,rownum rnum from (" + t1.Text +") a where rownum<="+upperlimit+" ) where rnum>="+lowerlimit;
OdbcCommand myCommand= new OdbcCommand(query, connection) ;
myCommand.Connection.Open() ;
OdbcDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection) ;
int count=myReader.FieldCount - 1;
lv.Columns.Add("Row No.", 100, HorizontalAlignment.Center) ;
for(int i=0;i<count;i++)
{
lv.Columns.Add(myReader.GetName(i), 100, HorizontalAlignment.Center) ;
}
dispint=0;
while(myReader.Read())
{
if (!myReader.IsDBNull(0))
lv.Items.Add((dispint+1).ToString()) ;
for (int i = 0; i < count; i++)
{
if (!myReader.IsDBNull(i))
lv.Items[dispint].SubItems.Add(myReader.GetValue(i).ToString()) ;
}
dispint++ ;
}
myReader.Close();
b5.Enabled=true;
}
//For executing any query like insert,create etc...
else
{
string nonquery= t1.Text ;
OdbcCommand mynonqueryCommand= new OdbcCommand(nonquery, connection) ;
mynonqueryCommand.Connection.Open() ;
mynonqueryCommand.ExecuteNonQuery() ;
mynonqueryCommand.Connection.Close();
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
if(e.Button==b5)
{
lv.Clear();
try
{
lowerlimit=upperlimit+1;
upperlimit=upperlimit+increment;
//For displaying the records of the select query five records per page...
//When the last record is displayed the NextRowSet button is disabled...
if(flag=="execute")
{
string abc=t1.Text;
string xyz=abc.Substring(abc.IndexOf("from"));
string querycount="select count(*) " + xyz;
OdbcCommand countcommand1= new OdbcCommand(querycount, connection) ;
countcommand1.Connection.Open() ;
OdbcDataReader countreader1 = countcommand1.ExecuteReader CommandBehavior.CloseConnection) ;
countreader1.Read() ;
int counttext = countreader1.GetInt32(0) ;
countreader1.Close() ;
countcommand1.Connection.Close() ;
if(counttext<=upperlimit)
{
upperlimit=counttext;
b5.Enabled=false;
}
string query="select * from (select a.* ,rownum rnum from (" + t1.Text +") a where rownum<="+upperlimit+") where rnum>="+lowerlimit+"";
OdbcCommand myCommand= new OdbcCommand(query, connection) ;
myCommand.Connection.Open() ;
OdbcDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection) ;
int count=myReader.FieldCount - 1 ;
lv.Columns.Add("Row No.", 100, HorizontalAlignment.Center) ;
for(int i=0;i<count;i++)
{
lv.Columns.Add(myReader.GetName(i), 100, HorizontalAlignment.Center) ;
}
dispint=0;
int rowid = lowerlimit ;
while(myReader.Read())
{
if (!myReader.IsDBNull(0))
lv.Items.Add((rowid++).ToString()) ;
for (int i = 0; i < count; i++)
{
if (!myReader.IsDBNull(i))
lv.Items[dispint].SubItems.Add(myReader.GetValue(i).ToString()) ;
}
dispint++ ;
}
myReader.Close();
}
//For displaying all the tables present in the database five per page....
//When the last table is displayed the NextRowSet button is disabled...
if(flag=="usertables")
{
string query1count="select count(*) from tab";
OdbcCommand countcommand= new OdbcCommand(query1count, connection) ;
countcommand.Connection.Open() ;
OdbcDataReader countreader = countcommand.ExecuteReader(CommandBehavior.CloseConnection) ;
countreader.Read() ;
int counttab = countreader.GetInt32(0) ;
countreader.Close() ;
countcommand.Connection.Close() ;
if(counttab<=upperlimit)
{
upperlimit=counttab;
b5.Enabled=false;
}
string query1="select * from (select a.* ,rownum rnum from (select * from tab) a where rownum<="+upperlimit+") where rnum>="+lowerlimit+"";
OdbcCommand myCommand1= new OdbcCommand(query1, connection) ;
myCommand1.Connection.Open() ;
OdbcDataReader myReader1 =
myCommand1.ExecuteReader(CommandBehavior.CloseConnection) ;
int count1=myReader1.FieldCount - 1 ;
lv.Columns.Add("Row No.", 100, HorizontalAlignment.Center) ;
for(int i=0;i<count1;i++)
{
lv.Columns.Add(myReader1.GetName(i), 100, HorizontalAlignment.Center) ;
}
dispint=0;
int rowid1 = lowerlimit ;
while(myReader1.Read())
{
if (!myReader1.IsDBNull(0))
lv.Items.Add((rowid1++).ToString()) ;
for (int i = 0; i < count1; i++)
{
if (!myReader1.IsDBNull(i))
lv.Items[dispint].SubItems.Add(myReader1.GetValue(i).ToString()) ;
}
dispint++ ;
}
myReader1.Close();
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
//Creating a new file...
private void FileNew_Clicked(object sender, System.EventArgs e)
{
t1.Text=null;
}
//Text on the statusbar changes when the mouse comes over the menuitem...
private void mnew_Click(object sender, System.EventArgs e)
{
statusBar.Text="Create a new file";
}
//Opening an existing file...
private void FileOpen_Clicked(object sender, System.EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
//openFileDialog.Filter = "Text files (*.txt)|*.txt|All files (*.*)|*.*" ;
openFileDialog.Filter = "Sql files (*.sql)|*.sql|All files (*.*)|*.*" ;
if(openFileDialog.ShowDialog() == DialogResult.OK)
{
try
{
t1.LoadFile(openFileDialog.FileName,RichTextBoxStreamType.PlainText);
fileName=openFileDialog.FileName;
}
catch
{
MessageBox.Show ("Could not open the file");
}
}
}
private void mopen_Click(object sender, System.EventArgs e)
{
statusBar.Text="Open an existing file";
}
//Saving the file...
private void FileSave_Clicked(object sender, System.EventArgs e)
{
SaveFileDialog fdlg = new SaveFileDialog();
if (fileName=="")
{
FileSaveAs_Clicked(sender, e);
}
else
{
try
{
t1.SaveFile(fileName,RichTextBoxStreamType.PlainText);
}
catch
{
MessageBox.Show ("Could not save the file");
}
}
}
private void msave_Click(object sender, System.EventArgs e)
{
statusBar.Text="Save the current document to a file";
}
//Saving the file...
private void FileSaveAs_Clicked(object sender, System.EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Text files (*.txt)|*.txt|All files (*.*)|*.*" ;
if(saveFileDialog.ShowDialog() == DialogResult.OK)
{
try
{
t1.SaveFile(saveFileDialog.FileName,RichTextBoxStreamType.PlainText);
fileName=saveFileDialog.FileName;
}
catch
{
MessageBox.Show ("Could not save the file");
}
}
}
private void msaveas_Click(object sender, System.EventArgs e)
{
statusBar.Text="Save the current document to a new file";
}
//Quiting the application...
private void FileExit_Clicked(object sender, System.EventArgs e)
{
this.Close();
}
private void mexit_Click(object sender, System.EventArgs e)
{
statusBar.Text="Quit the application";
}
private void FileCut_Clicked(object sender, System.EventArgs e)
{
t1.Cut();
}
private void mcut_Click(object sender, System.EventArgs e)
{
statusBar.Text="Cut the selection and puts it to the clipboard";
}
private void FileCopy_Clicked(object sender, System.EventArgs e)
{
t1.Copy();
}
private void mcopy_Click(object sender, System.EventArgs e)
{
statusBar.Text="Copy the selection to the clipboard";
}
private void FilePaste_Clicked(object sender, System.EventArgs e)
{
t1.Paste();
}
private void mpaste_Click(object sender, System.EventArgs e)
{
statusBar.Text="Insert clipboard contents";
}
//Printing a file..
protected void FilePrint_Clicked(object sender, System.EventArgs e)
{
printDialog1=new PrintDialog();
printDialog1.AllowPrintToFile =true;
PrintDocument docPrn = new PrintDocument();
docPrn.DocumentName=t1.Text;
printDialog1.Document=docPrn;
if(printDialog1.ShowDialog()==DialogResult.OK)
{
try
docPrn.Print();
}
catch
{
MessageBox.Show ("Error While Printing", "Print Error");
}
}
}
private void mprint_Click(object sender, System.EventArgs e)
{
statusBar.Text="Print the current document";
}
//Printer options...
protected void FilePageSetUp_Clicked(object sender, System.EventArgs e)
{
PrintDocument docPrn = new PrintDocument();
docPrn.DocumentName=t1.Text;
pageSetupDialog1=new PageSetupDialog();
pageSetupDialog1.Document=docPrn;
pageSetupDialog1.ShowDialog();
}
private void mpagesetup_Click(object sender, System.EventArgs e)
{
statusBar.Text="Change the printer options";
}
//Making the statusbar visible and invisible...
protected void StatusBar_Clicked(object sender, System.EventArgs e)
{
statusBar.Visible = !statusBar.Visible ;
mistatus.Checked=!mistatus.Checked;
}
}

//connect.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.Odbc;
using System.IO;
using Microsoft.Win32 ;
using System.Drawing.Printing;
public class connect: Form
{
TextBox t1,t2,t3,t4;
Label l1,l2,l3,l4;
Button b1,b2;
private ErrorProvider ep ;
private System.ComponentModel.Container components;
OdbcConnection myConnection;
public connect()
{
InitializeComponent();
}
/*public static void Main()
{
connect c=new connect();
c.FormBorderStyle=FormBorderStyle.Fixed3D;
Application.Run(c);
}*/
public void InitializeComponent()
{
ep = new ErrorProvider() ;
this.components = new System.ComponentModel.Container ();
this.l1=new Label();
l1.Location=new Point(25,20);
l1.Size=new Size(30,20);
l1.Text="DSN";
this.t1=new TextBox();
t1.Location=new Point(85,20);
t1.Size=new Size(120,20);
this.l2=new Label();
l2.Location=new Point(25,50);
l2.Size=new Size(60,20);
l2.Text="Username";
this.t2=new TextBox();
t2.Location=new Point(85,50);
t2.Size=new Size(120,20);
this.l3=new Label();
l3.Location=new Point(25,80);
l3.Size=new Size(60,20);
l3.Text="Password";
this.t3=new TextBox();
t3.Location=new Point(85,80);
t3.Size=new Size(120,20);
t3.PasswordChar = '*';
this.l4=new Label();
l4.Location=new Point(25,110);
l4.Size=new Size(60,20);
l4.Text="Service";
this.t4=new TextBox();
t4.Location=new Point(85,110);
t4.Size=new Size(120,20);
this.b1=new Button();
b1.Location=new Point(70,150);
b1.Size=new Size(60,30);
b1.Text="Connect";
b1.Click += new System.EventHandler(b1_Click);
b1.BackColor = System.Drawing.Color.Pink ;
b1.FlatStyle = System.Windows.Forms.FlatStyle.Flat;
this.b2=new Button();
b2.Location=new Point(155,150);
b2.Size=new Size(60,30);
b2.Text="Close";
b2.Click += new System.EventHandler(b2_Click);
b2.BackColor = System.Drawing.Color.Pink;
b2.FlatStyle = System.Windows.Forms.FlatStyle.Flat;
this.Size=new Size(300,300);
this.MaximizeBox = false;
this.Controls.Add(t1);
this.Controls.Add(t2);
this.Controls.Add(t3);
this.Controls.Add(t4);
this.Controls.Add(l1);
this.Controls.Add(l2);
this.Controls.Add(l3);
this.Controls.Add(l4);
this.Controls.Add(b1);
this.Controls.Add(b2);
}
protected void b1_Click(object sender, EventArgs e)
{
try
{
string connection =
"Provider=MSDAORA;FileDSN="+t1.Text+";UID="+t2.Text+";PWD="+t3.Text+";SID="+t4.Text+"";
string query = "select * from tab" ;
myConnection = new OdbcConnection(connection) ;
System.Console.Write("Connection established");
OdbcCommand myCommand = new OdbcCommand(query,myConnection);
myCommand = new OdbcCommand(query, myConnection) ;
myCommand.Connection.Open() ;
OdbcDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection) ;
while (myReader.Read())
{
string count = myReader.GetString(0) ;
Console.WriteLine("{0}",count);
}
myReader.Close() ;
myCommand.Connection.Close() ;
this.Hide();
}
catch(Exception ee)
{
if(ee.Message.IndexOf("invalid username/password")!=-1)
{
MessageBox.Show("Invalid Username/Password");
}
if(ee.Message.IndexOf("Invalid file dsn")!=-1)
{
MessageBox.Show("Invalid File DSN");
}
}
}
protected void b2_Click(object sender, EventArgs e)
{
Application.Exit();
}
public OdbcConnection Connection()
{
return myConnection;
}
}

COMMENT USING

Trending up