C# .NET - Create stored procedure from asp.net

Asked By Dom Afonso on 17-Jun-12 01:05 PM
How to create sql stored procedure from asp.net?
Lalit M replied to Dom Afonso on 17-Jun-12 01:15 PM
Check this link
--------
http://www.c-sharpcorner.com/UploadFile/gtomar/storedprocedure12052007003126AM/storedprocedure.aspx
Ravi Maurya replied to Dom Afonso on 17-Jun-12 01:20 PM
Hi,

Here is the peace of code which will insert a single value into the database using store procedure.

Controls:

<asp:Label ID="Label1" runat="server" Visible="false"></asp:Label><br />
Add Category: <asp:TextBox ID="txtCategory" runat="server"></asp:TextBox><br />
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />

Stored Procedure:

CREATE PROCEDURE [dbo].[AddCategory]
  @Category nvarchar(20)
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
 
  INSERT INTO CATEGORIES (Category) VALUES (@Category)
END

Code:

protected void Button1_Click(object sender, EventArgs e)
{
string Category = txtCategory.Text;
string connectionString = Utils.GetConnString();
      
using (SqlConnection conn = new SqlConnection(connectionString))
  {
// 1.  create a command object identifying the stored procedure      
   using (SqlCommand cmd = new SqlCommand("AddCategory", conn))
  {
// 2. set the command object so it knows to execute a stored procedure
  cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
  cmd.Parameters.AddWithValue("@Category", Category);
  conn.Open();
// execute the command
  cmd.ExecuteNonQuery();
  }
  }
}

Hope this might help you.
dipa ahuja replied to Dom Afonso on 17-Jun-12 05:01 PM
To Create Procedure Go to solution explorer expand database and from the stored Procedure , right click on it and choose new stored procedure
 
ALTER PROCEDURE dbo.Insertion
 (
   @f1 varchar,
   @f2 varchar,
)
    
AS
    /* SET NOCOUNT ON */
insert into table2
(
   field1,
   field2
)
 values
(
   @f1,
   @f2
)
RETURN
 
 
protected void Button1_Click(object sender, EventArgs e)
{
  string conn = "ConnectionString";
 
  SqlConnection sqlcon = new SqlConnection(conn);
 
  sqlcon.Open();
 
  SqlCommand comm = new SqlCommand("Insertion", sqlcon);
  comm.CommandType = CommandType.StoredProcedure;
 
  comm.Parameters.AddWithValue("f1", int.Parse(txtid.Text.ToString()));
  comm.Parameters.AddWithValue("f2", Txtname.Text.ToString());
 
  comm.ExecuteNonQuery();
}
 
 
hope this will help you!!
 
 
 
 
Jitendra Faye replied to Dom Afonso on 18-Jun-12 01:57 AM
If you want to Use stored procedure  then you have to follow 2 steps-

Step 1. First you have to create stored procedure using SQL Server

ex- Here i m creating one stored proceduer for selecting all record from table
 
CREATE PROCEDURE SelectEmployees ( @EmployeeID int )
 
AS 
 
SELECT [EmployeeID], [LastName], [FirstName], [Title], [HireDate], [Address] FROM [Employees]  WHERE [EmployeeID] = @EmployeeID
GO  

After creating this stored procedure just execute in SQL Server.

Step 2. Call this stored procedure Using Caommand object in ASP.Net.

ex- For that follow this code


 private void button1_Click(object sender, EventArgs e)
      {
        SqlConnection cn = new SqlConnection("Data Source=10.0.2.8;Initial Catalog=JitendraDB;User ID=sa;password=change_123");
        cn.Open();
        SqlCommand cmd = new SqlCommand("selectData", cn);
       
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@EmployeeID ",1001);
      
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        
        GridView1.DataSource=ds;
        GridView1.DataBind()l

}

After excution of this code you can get data from dataset ds.

USE THIS CODE AND LET ME KNOW.