Next


Query Processing

 Def: The process of retrieving the data from backend database into the frontend application is called as “Query Processing”.
 As a part of this “Query processing”, the query statement (select statement) will be written in the frontend application and it will be sent to the backend database. Then that statement will be executed at backend. Then the result data will be returned to the frontend application. That returned data will be received into the buffer.


API:

 Connection: Maintains the connection with database.
 Command: Sends a sql statement and executes it at backend.
 DataReader: Acts as a buffer. It holds the data that is received from database.

Implementation of Query Processing with “System.Data.SqlClient”:

 Import the API:
using System.Data.SqlClient;
 Prepare the Connection string: string cnstr = “data source=<name of the server>;user id=<user name>;password=<password>;initial catalog=<database name>”;
 Construct the “Connection” class object:
SqlConnection cn = new SqlConnection(cnstr);
 Prepare the sql statement:
string sqlstr = “select * from tablename”;
 Construct the “Command” class object:
SqlCommand cmd = new SqlCommand(sqlstr,cn);
 Construct the “DataReader” class object:
SqlDataReader dr;
 Open the connection:
cn.Open();
 Execute the command and receive the data into the buffer:
dr = cmd.ExecuteReader();
 Read the next row.
dr.Read();
 Display the value at specific column:
dr.GetValue(column index);
 Close the buffer:
dr.Close();
 Close the connection:
cn.Close();

Implementation of Query Processing with “System.Data.OleDb”:

 Import the API:
using System.Data.OleDb;
 Prepare the Connection string:
string cnstr = “string cnstr = “provider=<provider name>;data source=<name of the server>;user id=<user name>;password=<password>”;
 Construct the “Connection” class object: OleDbConnection cn = new OleDbConnection(cnstr);
 Prepare the sql statement:
string sqlstr = “select * from tablename”;
 Construct the “Command” class object:
OleDbCommand cmd = new OleDbCommand(sqlstr,cn);
 Construct the “DataReader” class object:
OleDbDataReader dr;
 Open the connection:
cn.Open();
 Execute the command and received the data into the buffer:
dr = cmd.ExecuteReader();
 Read the next row.
dr.Read();
 Display the value at specific column:
dr.GetValue(column index);
 Close the buffer:
dr.Close();
 Close the connection:
cn.Close();

Demo on Query Processing

Sql Server Code

using System.Data.SqlClient;
private void Form1_Load(object sender, EventArgs e)
{
string cnstr = "data source=.;user id=sa;password=123;initial catalog=sample";
SqlConnection cn = new SqlConnection(cnstr);
string sqlstr = "select * from students";
SqlCommand cmd = new SqlCommand(sqlstr, cn);
SqlDataReader dr;
cn.Open();
lblData.Text = "Database Connected.\n\n";
dr = cmd.ExecuteReader();
while (dr.Read() == true)
{
lblData.Text = lblData.Text + dr.GetValue(0) + ", " + dr.GetValue(1) + ", " + dr.GetValue(2) + "\n";
}
dr.Close();
cn.Close();
lblData.Text = lblData.Text + "\nDatabase Disconnected.";
}

Oracle Code

using System.Data.OleDb;
private void Form1_Load(object sender, EventArgs e)
{
string cnstr = "provider=oraoledb.oracle.1;data source=.;user id=scott;password=tiger";
OleDbConnection cn = new OleDbConnection(cnstr);
string sqlstr = "select * from students";
OleDbCommand cmd = new OleDbCommand(sqlstr, cn);
OleDbDataReader dr;
cn.Open();
lblData.Text = "Database Connected.\n\n";
dr = cmd.ExecuteReader();
while (dr.Read() == true)
{
lblData.Text = lblData.Text + dr.GetValue(0) + ", " + dr.GetValue(1) + ", " + dr.GetValue(2) + "\n";
}
dr.Close();
cn.Close();
lblData.Text = lblData.Text + "\nDatabase Disconnected.";
}

output

Demo on Query Processing (with UI)


Sql Server Code

using System.Data.SqlClient;
SqlDataReader dr;
SqlConnection cn;
private void Form1_Load(object sender, EventArgs e)
{
string cnstr = "data source=.;user id=sa;password=123;initial catalog=sample";
cn = new SqlConnection(cnstr);
string sqlstr = "select * from students";
SqlCommand cmd = new SqlCommand(sqlstr, cn);
cn.Open();
dr = cmd.ExecuteReader();
if (dr.Read() == true)
{
txtStudentID.Text = Convert.ToString(dr.GetValue(0));
txtName.Text = Convert.ToString(dr.GetValue(1));
txtMarks.Text = Convert.ToString(dr.GetValue(2));
}
else
MessageBox.Show("No records found.");
}
private void btnNext_Click(object sender, EventArgs e)
{
if (dr.Read() == true)
{
txtStudentID.Text = Convert.ToString(dr.GetValue(0));
txtName.Text = Convert.ToString(dr.GetValue(1));
txtMarks.Text = Convert.ToString(dr.GetValue(2));
}
else
MessageBox.Show("End of the records.");
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
dr.Close();
cn.Close();
}

Oracle Code

using System.Data.OleDb;
OleDbDataReader dr;
OleDbConnection cn;
private void Form1_Load(object sender, EventArgs e)
{
string cnstr = "provider=oraoledb.oracle.1;data source=.;user id=scott;password=tiger";
cn = new OleDbConnection(cnstr);
string sqlstr = "select * from students";
OleDbCommand cmd = new OleDbCommand(sqlstr, cn);
cn.Open();
dr = cmd.ExecuteReader();
if (dr.Read() == true)
{
txtStudentID.Text = Convert.ToString(dr.GetValue(0));
txtName.Text = Convert.ToString(dr.GetValue(1));
txtMarks.Text = Convert.ToString(dr.GetValue(2));
}
else
MessageBox.Show("No records found.");
}
private void btnNext_Click(object sender, EventArgs e)
{
if (dr.Read() == true)
{
txtStudentID.Text = Convert.ToString(dr.GetValue(0));
txtName.Text = Convert.ToString(dr.GetValue(1));
txtMarks.Text = Convert.ToString(dr.GetValue(2));
}
else
MessageBox.Show("End of the records.");
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
dr.Close();
cn.Close();
}

Demo on Conditional Query Processing

Sql Server Code

using System.Data.SqlClient;
private void btnOpen_Click(object sender, EventArgs e)
{
string cnstr = "data source=.;user id=sa;password=123;initial catalog=sample";
SqlConnection cn = new SqlConnection(cnstr);
int StuID = Convert.ToInt32(txtStudentID.Text);
string sqlstr = "select name,marks from students where studentid=" + StuID;
SqlCommand cmd = new SqlCommand(sqlstr, cn);
SqlDataReader dr;
cn.Open();
dr = cmd.ExecuteReader();
if (dr.Read() == true)
{
txtName.Text = Convert.ToString(dr.GetValue(0));
txtMarks.Text = Convert.ToString(dr.GetValue(1));
}
else
MessageBox.Show("Invalid Student ID. Try again!");
dr.Close();
cn.Close();
}

Oracle Code

using System.Data.OleDb;
private void btnOpen_Click(object sender, EventArgs e)
{
string cnstr = "provider=oraoledb.oracle.1;data source=.;user id=scott;password=tiger";
OleDbConnection cn = new OleDbConnection(cnstr);
int StuID = Convert.ToInt32(txtStudentID.Text);
string sqlstr = "select name,marks from students where studentid=" + StuID;
OleDbCommand cmd = new OleDbCommand(sqlstr, cn);
OleDbDataReader dr;
cn.Open();
dr = cmd.ExecuteReader();
if (dr.Read() == true)
{
txtName.Text = Convert.ToString(dr.GetValue(0));
txtMarks.Text = Convert.ToString(dr.GetValue(1));
}
else
MessageBox.Show("Invalid Student ID. Try again!");
dr.Close();
cn.Close();
}

Demo on Presenting the Column Data in a Combo Box

Sql Server Code

using System.Data.SqlClient;
private void Form1_Load(object sender, EventArgs e)
{
string cnstr = "data source=.;user id=sa;password=123;initial catalog=sample";
SqlConnection cn = new SqlConnection(cnstr);
string sqlstr = "select * from students";
SqlCommand cmd = new SqlCommand(sqlstr, cn);
SqlDataReader dr;
cn.Open();
dr = cmd.ExecuteReader();
while (dr.Read() == true)
{
cmbStudentID.Items.Add(dr.GetValue(0));
}
dr.Close();
cn.Close();
}

Oracle Code

using System.Data.OleDb;
private void Form1_Load(object sender, EventArgs e)
{
string cnstr = "provider=oraoledb.oracle.1;data source=.;user id=scott;password=tiger";
OleDbConnection cn = new OleDbConnection(cnstr);
string sqlstr = "select * from students";
OleDbCommand cmd = new OleDbCommand(sqlstr, cn);
OleDbDataReader dr;
cn.Open();
dr = cmd.ExecuteReader();
while (dr.Read() == true)
{
cmbStudentID.Items.Add(dr.GetValue(0));
}
dr.Close();
cn.Close();
}