Friday, January 28, 2011

STORED PROCEDURE

Following steps may helpfull for you to write the stored procedure:
  Q : Need a stored procedure which will help you to insert data in a database table.
( here database name is "anil"  and  table name is "emp". which consists the fields  eno,ename)

stored procedure formate:
USE  [database name]
CREATE PROCEDURE  [procedurename]
@fieldname1 datatype= _ ,
AS
BEGAN
"query"
END

Example 1:
Open query analyser in the sql server and write the following
USE  anil
CREATE PROCEDURE STP_EMP
@ENO
varbinary(MAX) = null,
@ENAME varchar(50) = null
 AS
BEGIN
 INSERT INTO emp(eno,ename) VALUES(@ENO,@ENAME)
END
Exicute the above procedure you will get the "Command exicuted Successfully msg".

Now Open the Default.aspx.cs page and write the following code:

SqlConnection con = new SqlConnection("Data Source=ADMIN-PC;Initial Catalog=anil;Integrated Security=True");

con.Open();
SqlCommand cmd = new SqlCommand("STP_EMP",con);// Call the procedure Name.
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ENO",SqlDbType.Int).Value = TextBox1.Text;(or) 1;
cmd.Parameters.Add("@ENAME", SqlDbType.VarChar).Value = TextBox2.Text; (or )name ;
cmd.ExecuteNonQuery();
con.Close();

No comments:

Post a Comment