Programming in almost language

This is the site where you may share your knowledge and experience to eachother..

  • Categories

  • LinkedIn

  • Tweet Me

  • My footsteps

StoredProcedure in brief for Beginner

Posted by Praveen Kumar on February 26, 2008

Stored Procedures

In this article, we will examine what a stored procedure is and how to call one from SQL Server database.A stored procedure is a subroutine available to applications accessing a relational database system Stored procedures (sometimes called a sprocSP) are actually stored in the database data dictionary

Features

Pre-compilation of SQL statements

Execution on a database server

Simplification of data management

code reusability.

Security

Outline:

  • Why Use Stored Procedures?

  • Creating a Stored Procedure

  • Calling a Stored Procedure

  • Specifying Parameters

  • Data Retrieval

  • Inserting Data Using Parameters

Why Use Stored Procedures?

There are several advantages of using stored procedures instead of standard SQL.

First, stored procedures allow a lot more flexibility offering capabilities such as conditional logic.

Second, because stored procedures are stored within the DBMS, bandwidth and execution time are reduced. This is because a single stored procedure can execute a complex set of SQL statements.

Third, SQL Server pre-compiles stored procedures such that they execute optimally. Fourth, client developers are abstracted from complex designs. They would simply need to know the stored procedure’s name and the type of data it returns.

Creating a Stored Procedure

In SQLServer,

CREATE PROCEDURE [dbo].[GetProducts] AS
SELECT ProductID, ProductName FROM Products

In MySql with parameter or you can write without parameter,

CREATE PROCEDURE `showall`(id int)
begin select *from News where ProductID=id;
end
Now that we have created a stored procedure, we will examine how to call it from within a C# application.

Calling a Stored Procedure

A very nice aspect of ADO.NET is that it allows the developer to call a stored procedure in almost the exact same way as a standard SQL statement.

1. Create a new C# Windows Application project.

2. From the Toolbox, drag and drop a DataGrid onto the Form. Resize it as necessary.

3. Double-click on the Form to generate the Form_Load event handler. Before entering any code, add “using System.Data.SqlClient” or if you are using ODBC then write using System.Data.Odbc at the top of the file.

Enter the following code: (“using System.Data.SqlClient”)

private void Form1_Load(object sender, System.EventArgs e)
{
    SqlConnection conn = new SqlConnection(“Data
Source=localhost;Database=Northwind;Integrated Security=SSPI”);SqlCommand command = new SqlCommand(“GetProducts”, conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
    DataSet ds = new DataSet();
adapter.Fill(ds, “Products”);
  this.dataGrid1.DataSource = ds;
this.dataGrid1.DataMember = “Products”;
}

Enter the following code: (“using System.Data.Odbc)

private void Form1_Load(object sender, System.EventArgs e){
OdbcConnection con = new OdbcConnection(“DSN=storedproc”);
OdbcDataAdapter da;
OdbcCommand cmd;
DataSet ds;
OdbcDataReader dr;
ry
{
con.Open();
da = new OdbcDataAdapter(“Select *from News”, con);
ds = new DataSet();
cmd = new OdbcCommand(“call showall(1)”, con);
cmd.CommandType = CommandType.StoredProcedure;
dr = cmd.ExecuteReader();
ArrayList al = new ArrayList();
while (dr.Read())
{
al.Add(dr.GetValue(2));
}
dr.Close();
foreach (String item in al)
{
Page.Response.Write(item.ToString());
}
}
catch (Exception e1) { Page.Response.Write(e1); }
finally { con.Close(); }}

Specifying with Parameter

SqlConnection conn = new SqlConnection(“Data
Source=localhost;Database=Northwind;Integrated Security=SSPI”);
SqlCommand command = new SqlCommand(“GetProducts”, conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(“@CategoryID”, SqlDbType.Int).Value = ;
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, “Products”);
this.dataGrid1.DataSource = ds;
this.dataGrid1.DataMember = “Products”

same as you can use with Odbc object

Note that you must now specify the CommandType property of the SqlCommand object. The reason we did not do this in the first example was that it is not required if the stored procedure does not accept parameters. Of course, specifying the CommandType property even if it is not needed may improve readability. The next line actually combines two lines in one:

command.Parameters.Add(“@CategoryID”, SqlDbType.Int);
command.Parameters[“@CategoryID”].Value = 1

Data Retrieval

I have already described in up example how to retrieve data via Odbc object

here i am showing with SqlClient

Data Retrieval with stored procedures is the same (surprise!) as if using standard SQL. You can wrap a DataAdapter around the Command object or you can use a DataReader to fetch the data one row at a time. The previous examples have already illustrated how to use a DataAdapter and fill a DataSet. The following example shows usage of the DataReader:

SqlConnection conn = new SqlConnection(“Data
Source=localhost;Database=Northwind;Integrated Security=SSPI”);
SqlCommand command = new SqlCommand(“GetProducts”, conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(“@CategoryID”, SqlDbType.Int).Value = 1;
conn.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read()){
Console.WriteLine(reader[“ProductName”]);}
conn.Close();

Again, using either a DataAdapter or a DataReader against a query from a stored procedure is the same as specifying the SQL from within the code.

Inserting Data Using Parameters

Using other SQL statements such as INSERT, UPDATE or DELETE follow the same procedure. First, create a stored procedure that may or may not accept parameters, and then call the stored procedure from within the code supply the necessary values if parameters are needed. The following example illustrates how to insert a new user in a users table that has a username and password field.

CREATE PROCEDURE [dbo].[InsertUser] ( 
    @Username varchar(50),
@Password varchar(50)
) AS
INSERT INTO Users VALUES(@Username, @Password)
string username = … // get username from user
string password = … // get password from user
SqlConnection conn = new SqlConnection(“Data
Source=localhost;Database=MyDB;Integrated Security=SSPI”);
SqlCommand command = new SqlCommand(“InsertUser”, conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(“@Username”, SqlDbType.VarChar).Value = username;
command.Parameters.Add(“@Password”, SqlDbType.VarChar).Value = password;
conn.Open();
int rows = command.ExecuteNonQuery();
conn.Close();

First, we retrieve the username and password information from the user. This information may be entered onto a form, through a message dialog or through some other method. The point is, the user specifies the username and password and the applicaton inserts the data into the database. Also notice that we called the ExecuteNonQuery() method of the Connection object. We call this method to indicate that the stored procedure does not return results for a query but rather an integer indicating how many rows were affected by the executed statement. ExecuteNonQuery() is used for DML statements such as INSERT, UPDATE and DELETE. Note that we can test the value of rows to check if the stored procedure inserted the data successfully.

if (rows == 1)
{
MessageBox.Show(“Create new user SUCCESS!”);
}
else {
MessageBox.Show(“Create new user FAILED!”);
}

We check the value of rows to see if it is equal to one. Since our stored procedure only did one insert operation and if it is successful, the ExecuteNonQuery() method should return 1 to indicate the one row that was inserted. For other SQL statements, especially UPDATE and DELETE statements that affect more than one row, the stored procedure will return the number of rows affected by the statement.

DELETE FROM Products WHERE ProductID > 50

This will delete all products whose product ID is greater than 50 and will return the number of rows deleted.

Conclusion

Stored procedures offer developers a lot of flexibility with many features not available using standard SQL. ADO.NET allows us to use stored procedures in our applications seamlessly. The combination of these two allows us to create very powerful appliations rapidly.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: