Using of stored procedures and ad hoc queries.

Introduction:
Well in the previous article you were introduced on how to use dataset. Now we will be seeing about how to work with stored procedures and ad Hoc queries.

Stored procedures and ad Hoc queries are the two way of accessing the database and are important while working with DataBase. Usually stored procedure are preferred over the ad hoc queries as it is easy to hack the database information in ad hoc queries where as it is not possible in stored procedure as you use parameter to access the query. Let’s see how to access the data source using stored procedure.

Stored procedure is a parameterized method let’s look at an example to see how to write a stored procedure and how it is used in .NET.

Store Procedure:
Create Procedure SelectAuthorInfo
@au_Id Varchar(50)
AS
Select * from Authors where au_id =@au_Id
GO

This is a simple stored procedure; instead of this Insert, Update or Delete queries can be used in the stored procedure. In the above example the Pubs database is used. This is an in-build data base in MS SQL server.

You will now see how to use the stored procedure in .NET application. As the procedure is the select statement it returns some records.

VB Code:

Dim dsetMydata As New DataSet
Dim connString As String = "server=.; database=pubs; UID=sa; PWD=;"
Dim myConn As New SqlConnection(connString)
Dim strQuery As String = "SelectAuthorInfo"
Dim myCommand As New SqlCommand(strQuery, myConn)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176"
Dim myAdapter As New SqlDataAdapter(myCommand)
myAdapter.Fill(dsetMydata)
DataGrid1.DataSource = dsetMydata.Tables(0)

C# Code:

DataSet dsetMydata = new DataSet();
string connString = "server=.; database=pubs; UID=sa; PWD=;";
string strQuery = "SelectAuthorInfo";
SqlConnection myConn = new SqlConnection();
SqlCommand myCommand = new SqlCommand(strQuery,myConn);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add(new SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176";
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
myAdapter.Fill(dsetMydata);
DataGrid1.DataSource = dsetMydata.Tables(0);

The above code gets the records for the au_id = “172-32-1176” this is done by using stored procedure. The Stored procedure has a parameter called @au_id, you are passing the value for the parameter from code behind

VB Code:

myCommand.Parameters.Add(New SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176"

C# Code:

myCommand.Parameters.Add(new SqlParameter("@au_Id", SqlDbType.VarChar)).Value = "172-32-1176";


You have to note that the command object is used to pass the parameter for the SQL stored procedure. For passing parameter you have to use SqlParameter member as shown in code. The above code executes the stored procedure and returns a record for that au_id