Next


Advanced Query Processing

 Query Processing is of two types:
1. Standard Query Processing:
 Implemented with “DataReader” buffer.
2. Advanced Query Processing
 Implemented with “DataSet”, “DataTable”, “DataRow” buffers.
 To overcome the limitations of “Standard query processing”, ADO.NET introduces “Advanced query processing”.

API:

 Connection: Maintains the connection with database.
 DataAdapter: Sends a sql statement and executes it at backend.
 DataSet: Holds the data that is received from database, after execution of DataAdapter. It can hold multiple table’s data at-a-time. In other words, it contains an array of tables.
 DataTable: Holds a single table, from the DataSet.
 DataRow: Holds a single row, from the DataTable.

Limitations of Standard Query Processing / Advantages of Advanced Query Processing:

 The “DataReader” buffer supports record travelling only once in it’s lifetime. But the “DataTable” buffer supports record travelling any no. of times in its life time.
 The “DataReader” buffer supports only forward record travelling. It doesn’t supports backward or random record travels. But “DataTable” supports forward, backward and random record travels.
 “DataReader” supports sequential records travels only. It doesn’t supports to pick-up a record directly with it’s index. But “DataTable” supports to pick-up a record directly with it’s index.
 “DataReader” supports only column indexing. “DataTable” supports both column and row indexing.
 “DataReader” data can’t be assigned to “DataGridView” control. But the “DataTable” data can be assigned to the grid control.

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

 Import the API:
using System.Data.SqlClient;
using System.Data;
 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 “DataAdapter” class object:
SqlDataAdapter adp = new SqlDataAdapter(sqlstr,cn);
 Construct the “DataSet” class object:
DataSet ds = new DataSet();
 Construct the “DataTable” class object:
DataTable dt;
 Construct the “DataRow” class object:
DataRow drow;
 Open the connection:
cn.Open();
 Execute the adapter and receive the data into DataSet:
adp.Fill(ds);
 Assign the particular table from “DataSet” into “DataTable” object:
dt = ds.Tables[table index];
 Get the total no. of rows in the table:
dt.Rows.Count;
 Get the total no. of columns in the table:
dt.Columns.Count;
 Assign the particular row from “DataTable” into “DataRow” object:
drow = dt.Rows[row index];
 Get the particular value in the “DataRow”:
 drow[column index];
(or)
drow[“column name”];
 Close the connection:
cn.Close();

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

 Import the API:
using System.Data.OleDb;
using System.Data;
 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 “DataAdapter” class object:
OleDbDataAdapter adp = new OleDbDataAdapter(sqlstr,cn);
 Construct the “DataSet” class object:
DataSet ds = new DataSet();
 Construct the “DataTable” class object:
DataTable dt;
 Construct the “DataRow” class object:
DataRow drow;
 Open the connection:
cn.Open();
 Execute the adapter and receive the data into DataSet:
adp.Fill(ds);
 Assign the particular table from “DataSet” into “DataTable” object:
dt = ds.Tables[table index];  Get the total no. of rows in the table:
dt.Rows.Count;
 Get the total no. of columns in the table:
dt.Columns.Count;
 Assign the “DataTable” data into “DataGridView” control:
dataGridView1.DataSource = dt;
 Assign the particular row from “DataTable” into “DataRow” object:
drow = dt.Rows[row index];
 Get the particular value in the “DataRow”:
drow[column index];
(or)
drow[“column name”];
 Close the connection:
cn.Close();

Demo on Advanced Query Processing (DataGridView)


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";
SqlDataAdapter adp = new SqlDataAdapter(sqlstr, cn);
DataSet ds = new DataSet();
DataTable dt;
cn.Open();
adp.Fill(ds);
dt = ds.Tables[0];
gridMyData.DataSource = dt;
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";
OleDbDataAdapter adp = new OleDbDataAdapter(sqlstr, cn);
DataSet ds = new DataSet();
DataTable dt;
cn.Open();
adp.Fill(ds);
dt = ds.Tables[0];
gridMyData.DataSource = dt;
cn.Close();
}

Demo on Advanced Query Processing (Flexible Record Travels)


Sql Server Code

using System.Data.SqlClient;

DataTable dt;
int RowIndex;
private void Display()
{
DataRow drow;
drow = dt.Rows[RowIndex];
txtStudentID.Text = Convert.ToString(drow[0]); txtName.Text = Convert.ToString(drow[1]);
txtMarks.Text = Convert.ToString(drow[2]);
}
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";
SqlDataAdapter adp = new SqlDataAdapter(sqlstr, cn); DataSet ds = new DataSet();
cn.Open();
adp.Fill(ds);
dt = ds.Tables[0]; RowIndex = 0;
Display();
cn.Close();
}
private void btnFirst_Click(object sender, EventArgs e)
{
RowIndex = 0;
Display();
}
private void btnPrevious_Click(object sender, EventArgs e)
{
RowIndex--;
if (RowIndex < 0)
{
RowIndex = 0;
MessageBox.Show("Already at first record.");
}
Display();
}
private void btnNext_Click(object sender, EventArgs e)
{
RowIndex++;
if (RowIndex == dt.Rows.Count)
{
RowIndex = dt.Rows.Count - 1;
MessageBox.Show("Already at last record.");
}
Display();
}
private void btnLast_Click(object sender, EventArgs e)
{
RowIndex = dt.Rows.Count - 1;
Display();
}

Oracle Code

using System.Data.OleDb;

DataTable dt;
int RowIndex;
private void Display()
{
DataRow drow;
drow = dt.Rows[RowIndex];
txtStudentID.Text = Convert.ToString(drow[0]);
txtName.Text = Convert.ToString(drow[1]);
txtMarks.Text = Convert.ToString(drow[2]);
}
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";
OleDbDataAdapter adp = new OleDbDataAdapter(sqlstr, cn);
DataSet ds = new DataSet();
cn.Open();
adp.Fill(ds);
dt = ds.Tables[0];
RowIndex = 0;
Display();
cn.Close();
}
private void btnFirst_Click(object sender, EventArgs e)
{
RowIndex = 0;
Display();
}
private void btnPrevious_Click(object sender, EventArgs e)
{
RowIndex--;
if (RowIndex < 0)
{
RowIndex = 0;
MessageBox.Show("Already at first record.");
}
Display();
}
private void btnNext_Click(object sender, EventArgs e)
{
RowIndex++;
if (RowIndex == dt.Rows.Count)
{
RowIndex = dt.Rows.Count - 1;
MessageBox.Show("Already at last record.");
}
Display();
}
private void btnLast_Click(object sender, EventArgs e)
{
RowIndex = dt.Rows.Count - 1;
Display();
}
private void btnFirst_Click(object sender, EventArgs e)
{
RowIndex = 0;
Display();
}
private void btnPrevious_Click(object sender, EventArgs e)
{
RowIndex--;
if (RowIndex < 0)
{
RowIndex = 0;
MessageBox.Show("Already at first record.");
}
Display();
}
private void btnNext_Click(object sender, EventArgs e)
{
RowIndex++;
if (RowIndex == dt.Rows.Count)
{
RowIndex = dt.Rows.Count - 1;
MessageBox.Show("Already at last record.");
}
Display();
}
private void btnLast_Click(object sender, EventArgs e)
{
RowIndex = dt.Rows.Count - 1;
Display();
}