VP Lecture Slides November, 2011

Let me know of any issue while downloading the lecture slides.

Thanks,

Using Stored Procedures in ADO.Net

Stored Procedures are a set of sql commands which are compiled and are stored inside the database. Every time you execute a sql command, the command is parsed, optimization is done and then the command is executed. Parsing and optimization the command each time you run the query is very expensive. To solve this we have a set of commands collectively called as stored procedure, which are already parsed and optimized and are executed when ever we call them.

Initially create an object of SqlConnection class Open the connection using the Open() method

SqlConnection con = new SqlConnection(@"Data Source=LAB4-SYS12\SQLEXPRESS ; initial catalog= StudentDB ; Integrated Security =SSPI");
con.open();

Create the following stored procedure on the Student _info table in the StudenDB database which accepts two parameters and does not have any output parameters.

CREATE PROCEDURE StudentInfoUpdate (@id INTEGER,
@Address VARCHAR(50)) AS
SET NOCOUNT OFF
UPDATE Student_info
SET Address = @Address
WHERE ID = @ID

Create a SqlCommand object with the parameters as the name of the stored procedure that is to be executed and the connection object con to which the command is to be sent for execution.

SqlCommand command = new SqlCommand("StudentInfoUpdate",con);

Change the command objects CommandType property to stored procedure.

command.CommandType = CommandType.StoredProcedure;

Add the parameters to the command object using the Parameters collection and the SqlParameter class.

command.Parameters.Add(new SqlParameter("@id",SqlDbType.Int,0,"id"));
command.Parameters.Add(new SqlParameter("@Address",SqlDbType.VarChar,50,"Address"));

Specify the values of the parameters using the Value property of the parameters

command.Parameters[0].Value=1;
command.Parameters[1].Value="Khyber PakhtoonKhuwa";

Execute the stored procedure using the ExecuteNonQuery method which returns the number of rows affected by the stored procedure.

int result =command.ExecuteNonQuery();

Now let us see how to execute stored procedures which has output parameters and how to access the results using the output parameters.

Create the following stored procedure which has one output parameter.

CREATE PROCEDURE StudentCount (@Students int OUTPUT) AS
SELECT @Students = Count(*) from Student_info

The above stored procedure counts the student records and results it as the output parameter.

SqlCommand command1 = new SqlCommand("StudentCount",con);
command1.CommandType = CommandType.StoredProcedure;

Add the parameters to the command1
command1.Parameters.Add(new SqlParameter("@Students" ,SqlDbType.Int,0 ,"Students"));

Observe that the parameter Students is added with the ParameterDirection as Output.

Assign the UpdatedRowSource property of the SqlCommand object to UpdateRowSource.OutputParameters to indicate that data will be returned from this stored procedure via output parameters.

command1.UpdatedRowSource = UpdateRowSource.OutputParameters;

Call the stored procedure and access the Students using the value property of the parameter.

command1.ExecuteNonQuery();
int studentCount =(int) command1.Parameters["@Students"].Value;

Close the sql connection.

con.Close();

In the same way you can call the stored procedure that returns a set of rows by defining the parameters as appropriate and executing the command using ExecuteReader() that is used to traverse the records returned by the command.

Please leave your questions and comments in the comments section below.

Thank you.

ADO.Net Demo (CRUD Operations)

using System;
using System.Data;
using System.Data.SqlClient;
///

/// Demonstrates how to work with ADO.net objects
///

class AdoNetDemo
{
SqlConnection conn;
public AdoNetDemo()
{
// Instantiate the connection
conn = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
}
// call methods that demo SqlCommand capabilities
static void Main()
{
AdoNetDemo adoNetDemo = new AdoNetDemo();

Console.WriteLine();
Console.WriteLine("Categories Before Insert");
Console.WriteLine("------------------------");
// use ExecuteReader method
adoNetDemo.ReadData();
// use ExecuteNonQuery method for Insert
adoNetDemo.Insertdata();
Console.WriteLine();
Console.WriteLine("Categories After Insert");
Console.WriteLine("------------------------------");
adoNetDemo.ReadData();
// use ExecuteNonQuery method for Update
adoNetDemo.UpdateData();
Console.WriteLine();
Console.WriteLine("Categories After Update");
Console.WriteLine("------------------------------");
adoNetDemo.ReadData();
// use ExecuteNonQuery method for Delete
adoNetDemo.DeleteData();
Console.WriteLine();
Console.WriteLine("Categories After Delete");
Console.WriteLine("------------------------------");
adoNetDemo.ReadData();
// use ExecuteScalar method
int numberOfRecords = adoNetDemo.GetNumberOfRecords();
Console.WriteLine();
Console.WriteLine("Number of Records: {0}", numberOfRecords);
// Just to stop the screen
Console.ReadKey();
}
///

/// use ExecuteReader method
///

public void ReadData()
{
SqlDataReader rdr = null;
try
{
// Open the connection
conn.Open();
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
// 2. Call Execute reader to get query results
rdr = cmd.ExecuteReader();
// print the CategoryName of each record
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
finally
{
// close the reader
if (rdr != null)
{
rdr.Close();
}
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
///

/// use ExecuteNonQuery method for Insert
///

public void Insertdata()
{
try
{
// Open the connection
conn.Open();
// prepare command string
string insertString = @" insert into Categories (CategoryName, Description) values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand(insertString, conn);
// 2. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
///

/// use ExecuteNonQuery method for Update
///

public void UpdateData()
{
try
{
// Open the connection
conn.Open();
// prepare command string
string updateString = @"update Categories set CategoryName = 'Other' where CategoryName = 'Miscellaneous'";
// 1. Instantiate a new command with command text only
SqlCommand cmd = new SqlCommand(updateString);
// 2. Set the Connection property
cmd.Connection = conn;
// 3. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
///

/// use ExecuteNonQuery method for Delete
///

public void DeleteData()
{
try
{
// Open the connection
conn.Open();
// prepare command string
string deleteString = @"delete from Categories where CategoryName = 'Other'";
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand();
// 2. Set the CommandText property
cmd.CommandText = deleteString;
// 3. Set the Connection property
cmd.Connection = conn;
// 4. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
///

/// use ExecuteScalar method
///

/// number of records
public int GetNumberOfRecords()
{
int count = -1;
try
{
// Open the connection
conn.Open();
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
// 2. Call ExecuteScalar to send command
count = (int)cmd.ExecuteScalar();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
return count;
}
}

Eid Mubarak

I wish everyone in BSCS a very happy Eid Festival. May the joy and happiness of Eid be yours always.

Asim Israr

VP Lecture Slides - October 2011

Lecture Slides for the month of October can be downloaded from the following link.