Next


Non-Query Processing

 Def: The process of modifying the table’s data or modifying structure is called as “Non-Query Processing”.
 Here, no data will be retrieved from database.
 So, you don’t require to maintain any buffer.

API:

 Connection: Maintains the connection with database.
 Command: Sends a sql statement and executes it at backend.

Implementation of Non-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 = “insert / delete / update / create table / drop table / alter table”;
 Construct the “Command” class object:
SqlCommand cmd = new SqlCommand(sqlstr,cn);
 Open the connection:
cn.Open();
 Execute the command and receive the no. of rows affected:
int n = cmd.ExecuteNonQuery();
 Close the connection:
cn.Close();

Implementation of Non-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 = “insert / delete / update / create table / drop table / alter table”;
 Construct the “Command” class object: OleDbCommand cmd = new OleDbCommand(sqlstr,cn);
 Open the connection:
cn.Open();
 Execute the command and receive the no. of rows affected:
int n = cmd.ExecuteNonQuery();
 Close the connection:
cn.Close();

Demo on Non-Query Processing (Insertion)

Demo on Non-Query Processing (Insertion)

Sql Server Code

using System.Data.SqlClient;
private void btnInsert_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 Sname = txtName.Text;
int marks = Convert.ToInt32(txtMarks.Text);
string sqlstr = "insert into students values(" + StuID + ", ' " + Sname + " ' , " + marks + ")";
SqlCommand cmd = new SqlCommand(sqlstr, cn);
cn.Open();
int n = cmd.ExecuteNonQuery();
if (n == 1)
MessageBox.Show("Successfully Inserted.");
else
MessageBox.Show("Insertion failed.");
cn.Close();
}

Oracle Code

using System.Data.OleDb;
private void btnInsert_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 Sname = txtName.Text;
int marks = Convert.ToInt32(txtMarks.Text);
string sqlstr = "insert into students values(" + StuID + ",'" + Sname + "'," + marks + ")";
OleDbCommand cmd = new OleDbCommand(sqlstr, cn);
cn.Open();
int n = cmd.ExecuteNonQuery();
if (n == 1)
MessageBox.Show("Successfully Inserted.");
else
MessageBox.Show("Insertion failed.");
cn.Close();
}

Demo on Non-Query Processing (Deletion)

Demo on Non-Query Processing (Deletion)

Sql Server Code

using System.Data.SqlClient;
private void btnDelete_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 = "delete from students where studentid=" + StuID;
SqlCommand cmd = new SqlCommand(sqlstr, cn);
cn.Open();
int n = cmd.ExecuteNonQuery();
if (n == 1)
MessageBox.Show("Successfully Deleted.");
else
MessageBox.Show("Deletion failed.");
cn.Close();
}

Oracle Code

using System.Data.OleDb;
private void btnDelete_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 = "delete from students where studentid=" + StuID;
OleDbCommand cmd = new OleDbCommand(sqlstr, cn);
cn.Open();
int n = cmd.ExecuteNonQuery();
if (n == 1)
MessageBox.Show("Successfully Deleted.");
else
MessageBox.Show("Deletion failed.");
cn.Close();
}

Demo on Non-Query Processing (Updation)

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(txtOldStudentID.Text);
string sqlstr = "select * from students where studentid=" + StuID;
SqlCommand cmd = new SqlCommand(sqlstr, cn);
SqlDataReader dr;
cn.Open();
dr = cmd.ExecuteReader();
if (dr.Read() == true)
{
txtNewStudentID.Text = Convert.ToString(dr.GetValue(0));
txtName.Text = Convert.ToString(dr.GetValue(1));
txtMarks.Text = Convert.ToString(dr.GetValue(2));
}
else
MessageBox.Show("Invalid Student ID. Try again!");
dr.Close();
cn.Close();
}
private void btnUpdate_Click(object sender, EventArgs e)
{
string cnstr = "data source=.;user id=sa;password=123;initial catalog=sample";
SqlConnection cn = new SqlConnection(cnstr);
int OldStuID = Convert.ToInt32(txtOldStudentID.Text);
int NewStuID = Convert.ToInt32(txtNewStudentID.Text);
string Sname = txtName.Text;
int marks = Convert.ToInt32(txtMarks.Text);
string sqlstr = "update students set studentid=" + NewStuID + ",name= ' " + Sname + " ' ,marks=" + marks + " where studentid=" + OldStuID;
SqlCommand cmd = new SqlCommand(sqlstr, cn);
cn.Open();
int n = cmd.ExecuteNonQuery();
if (n == 1)
MessageBox.Show("Successfully Updated.");
else
MessageBox.Show("Updation failed.");
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(txtOldStudentID.Text);
string sqlstr = "select * from students where studentid=" + StuID;
OleDbCommand cmd = new OleDbCommand(sqlstr, cn);
OleDbDataReader dr;
cn.Open();
dr = cmd.ExecuteReader();
if (dr.Read() == true)
{
txtNewStudentID.Text = Convert.ToString(dr.GetValue(0));
txtName.Text = Convert.ToString(dr.GetValue(1));
txtMarks.Text = Convert.ToString(dr.GetValue(2));
}
else
MessageBox.Show("Invalid Student ID. Try again!");
}
private void btnUpdate_Click(object sender, EventArgs e)
{
string cnstr = "provider=oraoledb.oracle.1;data source=.;user id=scott;password=tiger";
OleDbConnection cn = new OleDbConnection(cnstr);
int OldStuID = Convert.ToInt32(txtOldStudentID.Text);
int NewStuID = Convert.ToInt32(txtNewStudentID.Text);
string Sname = txtName.Text;
int marks = Convert.ToInt32(txtMarks.Text);
string sqlstr = "update students set studentid=" + NewStuID + ",name='" + Sname + "',marks=" + marks + " where studentid=" + OldStuID;
OleDbCommand cmd = new OleDbCommand(sqlstr, cn);
cn.Open();
int n = cmd.ExecuteNonQuery();
if (n == 1)
MessageBox.Show("Successfully Updated.");
else
MessageBox.Show("Updation failed.");
cn.Close();
}

Note: Try to implement “Table creation”, “Table deletion” and “Table alteration”.