Working with SQL Database - part 2 [Stored Procedures]

Using Stored Procedures:

A Stored Procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity (information is entered in a consistent manner), and improving productivity (statements in a stored procedure only need to be written one time).

Syntax:

CREATE PROCEDURE [Procedure_Name]

(

[@Param1] [type],

[@Param2] [type]......

)

AS

BEGIN

[Your SQL Stataments]

END

GO

Example:

CREATE PROCEDURE setData

(

@Name VARCHAR(30),

@EMAIL VARCHAR(30),

@MOBILE VARCHAR(30)

)

AS

BEGIN

INSERT INTO testdata(name,email,mobile)

VALUES(@Name,@EMAIL,@MOBILE);

END

Implementation:

cmd = new SqlCommand("

setData

",con); //cmd- Command, con- Connection Variables.

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = txtName.Text;

cmd.Parameters.Add("@Email", SqlDbType.

VarChar

).Value = txtEmail.Text;

cmd.Parameters.Add("@Mobile", SqlDbType.BigInt).Value = txtMobile.Text;

cmd.ExecuteNonQuery();

The above statements when executed inserts the records in to the database table.