stored procedure creation

Arun Kurmi

13y1.6k.NET

hi friends,
i know syntax of stored procedure
but what are the place were write it-sql server mgmt studio or server explorer in VS,or .cs file of code. and how to use it
please provide solution step by step

for ex. i have a syntax for stored procedure below. where write this and how to use this....
suppose i want store text of TextBox1 on btn1 click

Create procedure InsertProc(
@name as string)
as
insert into user_tbl(name)values(@name)
go

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Naveen Bisht13y ago
Accepted answer
Ste1- create method in for insert details
//userDetails is class have tow properties FNAME,LNAME

DataAcessLayer.cs
public void InsertUserDetails(UserDetails userInfo)
    {

        SqlConnection con = new SqlConnection("Data Source=DAFFODIL-96;Initial Catalog=Test;User ID=sa;Password=hrhk");
        con.Open();
SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText=" Youre Store procedure Name"
        cmd.Parameters.AddWithValue("@fname", userInfo.FNAME);//procedure paramters
        cmd.Parameters.AddWithValue("@lname", userInfo.LNAME);//parameters
        int result = cmd.ExecuteNonQuery();
    }

Step2- create code on button click

DataAcessLayer oDataAcessLayer=new DataAcessLayer()

            UserDetails oUserDetails = new UserDetails();
            oUserDetails.FNAME = txtFirstName.Text;
            oUserDetails.LNAME = txtLastName.Text;
            oDataAcessLayer.InsertUserDetails(oUserDetails);
            showdata();
            lblMessage.ForeColor = System.Drawing.Color.Green;
            lblMessage.Text = "Data inserted sucessfully";
0
Chandan Kr Madal13y ago
write this code under click event
SqlCommand com;
SqlTransaction tr;
SqlConnection con;

con = new SqlConnection("put your connetion string");
try
{
con.Open();
}
catch (Exception ee)
{
con.Close();
return;
}
tr = con.BeginTransaction();
com = new SqlCommand("storedProcedureName", con);
try
{
com.Transaction = tr;
com.CommandType = CommandType.StoredProcedure;

com.Parameters.Add(new SqlParameter("@storedProcedure_parameter", textbox.text));
com.ExecuteNonQuery();
tr.Commit();
}
catch (Exception ee)
{
tr.Rollback();
}
finally
{
tr.Dispose();
com.Dispose();
con.Close();
}
0