DOTNET CSHARP ADO.NET ONLINE CLASSROOM IT SOFTWARE TRAINER DEVELOPER MANOHAR GOUD: ADODOTNET SQL STORED PROCEDURE MANOHAR GOUD

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. This article describes about how to call the stored procedures through Ado.net and how to handle the output parameters of the called stored procedures.

Initially create a object of SqlConnection class which is available in System.Data.SqlClient namespace. You has to provide the connection string as a parameter which includes the Data Source name, the database name and the authentication credentials. Open the connection using the Open() method.

            SqlConnection con = new SqlConnection("Data Source=  ; initial

 catalog= Northwind ; User Id=  ; Password=  '");

            con.open();

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

            CREATE PROCEDURE RegionUpdate (@RegionID INTEGER,
            @RegionDescription NCHAR(50)) AS
            SET NOCOUNT OFF
            UPDATE Region
            SET RegionDescription = @RegionDescription

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("RegionUpdate",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("@RegionID",SqlDbType.Int,0,"RegionID"));

            command.Parameters.Add(new SqlParameter("@RegionDescription",SqlDbType.NChar,50,"RegionDescription"));

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

            command.Parameters[0].Value=4;

            command.Parameters[1].Value="SouthEast";

Excecute the stored procedure using the ExecuteNonQuery method which returns the number of rows effected by the stored procedure.

             int i=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.

            ALTER PROCEDURE RegionFind(@RegionDescription NCHAR(50) OUTPUT,
            @RegionID INTEGER )AS

            SELECT @RegionDescription =RegionDescription from Region where

<A href="mailto:RegionID=@RegionID">RegionID=@RegionID</A>

The above stored procedure accepts regionID as input parameter and finds the RegionDescription for the RegionID input and results it as the output parameter.

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

Add the paremeters to the command1

            command1.Parameters.Add(new SqlParameter

("@RegionDescription",SqlDbType.NChar ,50,ParameterDirection.Output,false,0,50,

"RegionDescription",DataRowVersion.Default,null));
            command1.Parameters.Add(new SqlParameter("@RegionID" ,
            SqlDbType.Int,
            0 ,
            "RegionID" ));

Observe that the parameter RegionDescription is added with the ParameterDirection as Ouput.

specify the value for the input parameter RegionID.

            command1.Parameters["@RegionID"].Value = 4;

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 RegionDescription for the RegionID 4 using the value property of the parameter.

           command1.ExecuteNonQuery();
           string newRegionDescription =(string) command1.Parameters["@RegionDescription"].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.

Connection String

<add name="conString"

connectionString="Data Source=.\SQLEXPRESS;

database=Northwind;Integrated Security=true"/>

</connectionStrings>

Namespaces

You will need to import the following namespaces

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

VB.Net

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Insert Stored Procedures

Here is an example of Simple Insert Stored procedure which does not return anything. It just takes the values of the new record to be added as parameters and inserts the same.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[AddEmployee]

@FirstName varchar(50),

@LastName varchar(50),

@BirthDate datetime,

@City varchar(50),

@Country varchar(50)

BEGIN

SET NOCOUNT ON;

INSERT INTO Employees (FirstName, LastName, BirthDate, City, Country)

VALUES (@FirstName, @LastName, @BirthDate, @City, @Country)

END

And here is how I call it in the ASP.Net Web page. As you will notice I am passing the values to the parameters @FirstName, @LastName, @BirthDate, @City and @Country using the TextBoxes txtFirstName, txtLastName, txtBirthDate, txtCity and txtCountry respectively. Since the stored procedure does not return anything I am using the ExecuteNonQuery method of the SQL Command object

String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;

SqlConnection con = new SqlConnection(strConnString);

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "AddEmployee";

cmd.Parameters.Add("@FirstName",SqlDbType.VarChar).Value = txtFirstName.Text.Trim();

cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text.Trim();

cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime).Value = txtBirthDate.Text.Trim();

cmd.Parameters.Add("@City", SqlDbType.VarChar).Value =

txtCity.Text.Trim();

cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = txtCountry.Text.Trim();

cmd.Connection = con;

try

{

con.Open();

cmd.ExecuteNonQuery();

lblMessage.Text = "Record inserted successfully";

}

catch (Exception ex)

{

throw ex;

}

finally

{

con.Close();

con.Dispose();

}

VB.Net

Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString

Dim con As New SqlConnection(strConnString)

Dim cmd As New SqlCommand()

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "AddEmployee"

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text.Trim()

cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text.Trim()

cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime).Value = txtBirthDate.Text.Trim()

cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim()

cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = txtCountry.Text.Trim()

cmd.Connection = con

Try

con.Open()

cmd.ExecuteNonQuery()

lblMessage.Text = "Record inserted successfully"

Catch ex As Exception

Throw ex

Finally

con.Close()

con.Dispose()

End Try

Connection String

<add name="conString"

connectionString="Data Source=.\SQLEXPRESS;

database=Northwind;Integrated Security=true"/>

</connectionStrings>

Namespaces

You will need to import the following namespaces

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

VB.Net

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Select Stored Procedures

Here is a simple Select Stored Procedure that brings all the records from the Employees table

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[GetAllEmployeeDetails]

BEGIN

SET NOCOUNT ON;

SELECT FirstName, LastName, BirthDate, City, Country

FROM Employees

END

Now I’ll call the stored procedure using ADO.Net in my ASP.Net website and bind the results to a GridView. Since I need to fetch multiple rows I’ll be using ExecuteReader method of SQL Command object

String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;

SqlConnection con = new SqlConnection(strConnString);

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "GetAllEmployeeDetails";

cmd.Connection = con;

try

{

con.Open();

GridView1.EmptyDataText = "No Records Found";

GridView1.DataSource = cmd.ExecuteReader() ;

GridView1.DataBind();

}

catch (Exception ex)

{

throw ex;

}

finally

{

con.Close();

con.Dispose();

}

VB.Net

Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString

Dim con As New SqlConnection(strConnString)

Dim cmd As New SqlCommand()

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "GetAllEmployeeDetails"

cmd.Connection = con

Try

con.Open()

GridView1.EmptyDataText = "No Records Found"

GridView1.DataSource = cmd.ExecuteReader()

GridView1.DataBind()

Catch ex As Exception

Throw ex

Finally

con.Close()

con.Dispose()

End Try

Select Stored Procedures with Parameters

Here I will get filtered records based on Employee ID. In the stored procedure below I am passing Employee ID as parameter and based on the ID the records will be fetched.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[GetEmployeeDetailsByID]

@EmployeeID int = 0

BEGIN

SET NOCOUNT ON;

SELECT FirstName, LastName, BirthDate, City, Country

FROM Employees WHERE EmployeeID=@EmployeeID

END

Now in order to call the above stored procedure in an asp.net application using ADO.Net we will have to do it the following way. You will notice all remains the same except here I am passing @EmployeeID as parameter. Also you will notice txtID which is a TextBox in which user enters the EmployeeID of the Employee whose details he wants to view

String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;

SqlConnection con = new SqlConnection(strConnString);

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "GetEmployeeDetailsByID";

cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = txtID.Text.Trim();

cmd.Connection = con;

try

{

con.Open();

GridView1.EmptyDataText = "No Records Found";

GridView1.DataSource = cmd.ExecuteReader();

GridView1.DataBind();

}

catch (Exception ex)

{

throw ex;

}

finally

{

con.Close();

con.Dispose();

}

VB.Net

Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString

Dim con As New SqlConnection(strConnString)

Dim cmd As New SqlCommand()

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "GetEmployeeDetailsByID"

cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = txtID.Text.Trim()

cmd.Connection = con

Try

con.Open()

GridView1.EmptyDataText = "No Records Found"

GridView1.DataSource = cmd.ExecuteReader()

GridView1.DataBind()

Catch ex As Exception

Throw ex

Finally

con.Close()

con.Dispose()

End Try

Using stored procedures instead of writing SQL queries manually is a good thing to do in pretty much every application that involves SQL databases.

In C#, to execute a stored procedure, a developer needs to initialize an instance of SqlConnection, set the connection string, create an instance of SqlCommand, add the parameters, set the type of the command to StoredProcedure and use ExecuteNonQuery() to execute it. Using this every single time for every other stored procedure that needs to be called can involve more code that a developer probably wants to see in a function.

Therefore, I was working on a function that would unify all this code in one place, and whenever needed, it can be called in one line of code.

Suppose I have a database on my SQL Server called MySystem. There is also a table, called Users. Now, I also a have a stored procedure to simplify the process of adding new users to the system:

PROCEDURE [dbo].[AddUser] (@userID CHAR(36), @username CHAR(25), @password CHAR(25), @fullname CHAR(60), @role INT) 

 

AS

 

INSERT INTO Users 

 

VALUES (@userID, @username, @password, @role, @fullname)

Don’t get confused by such a long userID – a GUID is used as a user identifier in my case.
Here is my function that will execute stored procedures in C#:

enum ProcedureType 

{ 

UpdateOnly, 

WithReturn 

} 

SqlDataReader ExecuteProcedure(string connectionString, string procedureName, Dictionary<string,string> parameters,ProcedureType pType) 

{ 

SqlDataReader reader = null; 

SqlConnection sqlConn; 

SqlCommand sqlCmd; 

try

{ 

sqlConn = new SqlConnection(connectionString); 

sqlCmd = new SqlCommand(procedureName, sqlConn); 

sqlCmd.CommandType = CommandType.StoredProcedure; 

if (parameters != null) 

{ 

foreach (string parameter in parameters.Keys) 

{ 

sqlCmd.Parameters.AddWithValue(parameter, parameters[parameter]); 

} 

} 

if (pType == ProcedureType.UpdateOnly) 

{ 

sqlConn.Open(); 

sqlCmd.ExecuteNonQuery(); 

sqlConn.Close(); 

} 

else

{ 

sqlConn.Open(); 

reader = sqlCmd.ExecuteReader(); 

} 

} 

catch (Exception ex) 

{ 

System.Diagnostics.Debug.Print( 

string.Format("An exception occured when executing the {0} stored procedure. {1}", 

procedureName, ex.Message)); 

} 

 

return reader; 

}

Its parameters are pretty much self explanatory – it gets the connection string, the name of the stored procedure, the list of parameters to be passed to the procedure and the type of the procedure. UpdateOnly means that the stored procedure is executed and there is no value that needs to be read by the end user. WithReturn means that there are results returned (in case with SELECT statements, for example). If a value is not returned, SqlDataReader is null. Otherwise, it contains the response data. Mention the fact that the list of parameters is represented as Dictionary<string,string>. This is because parameters to the procedure are passed as value pairs – the parameter name and the value of the parameter.

Here is a piece of sample code that shows how to use the above mentioned function applied to my sample database (should be places in an event handler to be triggered):

string connectStr = @"server=DEN-PC\SQLEXPRESS;database=MySystem;uid=admin;pwd=pass"; 

Dictionary<string, string> parameters = new Dictionary<string, string>(); 

parameters.Add("@userID", "4d36e978-e325-11ce-bfc1-08002be10311"); 

parameters.Add("@username", "somename"); 

parameters.Add("@password", "passHash"); 

parameters.Add("@role", "1"); 

parameters.Add("@fullname", "Someone Pass"); 

SqlDataReader reader = ExecuteProcedure(connectStr, "AddUser", parameters, ProcedureType.WithReturn);

The SimpleStoredProcedure class contains three methods. I have followed the .NET naming convention for my methods since we are all familiar with them.

The SimpleStoredProcedure class with its constructor is as follows:

public class SimpleStoredProcedure 
{
private string myConnectionString;
private SqlConnection Connection;
private SqlCommand comm;
private SqlParameter samparam;

public SimpleStoredProcedure(string ConnectionString)
{
myConnectionString=ConnectionString;
Connection = new SqlConnection(myConnectionString);
comm = new SqlCommand();

samparam = new SqlParameter();
}

The first method is called ExecuteSPReader which functions in the same way as the general ExecuteReader method in .NET.

public DataSet ExecuteSPReader
    (string StoredProcedure,string tableName, params DictionaryEntry[] ParamName)
{
comm = new SqlCommand(StoredProcedure);
comm.CommandType = CommandType.StoredProcedure;

foreach (DictionaryEntry paramV in ParamName)
{
comm.Parameters.AddWithValue(paramV.Key.ToString(), paramV.Value);
}

SqlDataAdapter resultDA = new SqlDataAdapter();
resultDA.SelectCommand = comm;
resultDA.SelectCommand.Connection = Connection;

Connection.Open();
try{
DataSet resultDS = new DataSet();
resultDA.Fill(resultDS, tableName);
}
finally{
Connection.Close();
}
return resultDS;
}

This method accepts three parameters:

The stored procedure to call on
Name of the table to get data from
A list of the stored procedure parameters as DictionaryEntry.

There are two other methods defined which do not need to be described. They are:

ExecuteScalarSP
ExecuteNonQuerySP

Usage

To use this solution, you need to import the SimpleStoredProcedure class and use its method that best fits your need.

Let's say that we have an SQL Stored Procedure defined as follows:

ALTER PROCEDURE dbo.varifyGroupPermission 
(
@groupNumber int
)
AS
SELECT permission FROM PERMISSION_MATRIX WHERE groupNumber=@groupNumber

We want to have a list of permissions when running the above SQL procedure and use it in our program.

First we create an object called SP for example. Then list the variables we have in our SQL procedure above, give the variables a value. As you can see, I have defined a DictionaryEntry group and I do not have to worry about the type of this variable. Call the ExecuteSPReader method with the appropriate parameters and have its results in a DataSet. Now we can use its data from the DataSet.

SimpleStoredProcedure SP = new SimpleStoredProcedure();

DictionaryEntry group;
group.Key = "@groupNumber";
group.Value = 2;
DataSet PermissionDS = SP.ExecuteSPReader
    ("varifyGroupPermission", "PERMISSION_MATRIX", group);
DataTableReader PermissionReader = PermissionDS.CreateDataReader();

while (PermissionReader.Read())
{
Permission = PermissionReader.GetString(0);
}

--------------------

 
'Create the following table in sql server
CREATE TABLE abc(
	EmpCode numeric(18, 0) NOT NULL,
	FName nvarchar(50),
	LName nvarchar(50)
) 
 

 

 

'Create the following PROCEDURE  sql server

 
     Create PROCEDURE procemp
        @Empcode numeric,
        @FName nvarchar(100),
        @LName nvarchar(100),
        
        AS
        BEGIN()
        Insert into abc(Empcode,FName, LName) values (@Empcode,@FName, @LName) 
        End
        
'Write the following code in front end in your aspx page.

 
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@Empcode", 101)
        cmd.Parameters.AddWithValue("@FName", "Sachin")
        cmd.Parameters.AddWithValue("@LName", "Sharma")
        cmd.Connection = con
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        cmd.ExecuteNonQuery()
 

'C#

 

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empcode", 101);
cmd.Parameters.AddWithValue("@FName", "Sachin");
cmd.Parameters.AddWithValue("@LName", "Sharma");
cmd.Connection = con;
if (con.State == ConnectionState.Closed) {
	con.Open();
}
cmd.ExecuteNonQuery();

------------------

store procedure defination
--------------------------------------------

create procedure sp_Test
(
    @Id int, @Name varchar(50)
)
Insert into table1(Id, FullName) values(@Id,@Name)

C# code for call store procedure and insert data in database.(perform it a button click event)
--------------------------------------

try
  {
     sqlConnection = new SqlConnection(dbConnectionString);
     SqlCommand command = new SqlCommand("sp_Test", sqlConnection);
     command.CommandType = CommandType.StoredProcedure;
     command.Parameters.Add("@Id", SqlDbType.VarChar).Value = txtId.Text;
     command.Parameters.Add("@Name", SqlDbType.DateTime).Value = txtName.Text;
     sqlConnection.Open();
     return command.ExecuteNonQuery();
     sqlConnection.Close();
  }
catch (SqlException ex)
  {
     Console.WriteLine("SQL Error" + ex.Message.ToString());
     return 0;
  }

DOTNET CSHARP ADO.NET ONLINE CLASSROOM IT SOFTWARE TRAINER DEVELOPER MANOHAR GOUD

Wednesday, 2 October 2013

ADODOTNET SQL STORED PROCEDURE MANOHAR GOUD

Usage

No comments:

Post a Comment