Monday, 24 December 2012 13:55

stored procedures

Written by 
Rate this item
(0 votes)

stored procedures allow flexibility by offering capabilities like
conditional logic.a single procedure may execute a set of SQL
statements which are really complex.

how to create a stored procedure::
there is an easy way for creating stored procedures by Enterprise
Manager.follow the steps-
1.select the database on which the stored procedure will be created.
2.expand the database node,right click on stored procedure and select
new procedure.you will see the following code-
CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS

then change it like-CREATE PROCEDURE [dbo].[MyGetProductsProcedure]AS

after that you must specify the procedure body as shown below-
CREATE PROCEDURE [dbo].[MyGetProductsProcedure] AS
SELECT ProdID, ProdName FROM Products

now to check whether your procedure is syntactically correct or not,
click the button called check syntax.

so you have created the stored procedure,now follow the steps to
call it from a c#.net application-

a nice aspect of ADO.NET is that you can call a procedure in the same
way you call standard SQL statement.
1.create your new c#.net windows application
2.drag a DataGrid onto the form and modify it if neccessary
3.double clicking the form,create Form_Load event handler.add
using System.Data.SqlClient at top.then enter the codes below-
private void MyForm_Load(object sender, System.EventArgs e){
SqlConnection newconn = new SqlConnection("Data
Source=localhost;Database=DatabaseName;Integrated Security=SSPI");
SqlCommand newcom = new SqlCommand("MyGetProductsProcedure", newconn);
SqlDataAdapter newadapter = new SqlDataAdapter(newcom);
DataSet datas = new DataSet();
newadapter.Fill(datas, "Products");
this.myDataGrid1.DataSource = datas;
this.myDataGrid1.DataMember = "Products";}

so by the above example you see that calling a procedure is same as
using sql statements,but only that you define the procedure name instead
of the sql statement.you can treat the stored procedure in the same
way you would a normal sql statement call with advantages of procedure.

Conclusion::
Stored procedure gives programmers with too many features which are
not available in standard sql.the ado.net offers us to utilise stored
procedure in c#.net applications easily.

Read 1299 times
Super User

Email This email address is being protected from spambots. You need JavaScript enabled to view it.
Login to post comments