Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to call a Stored Procedure in ASP.NET

Author: Alanna Kremer
Download Source Code : 1301_CallStoredProcedure.zip

Stored Procedures are SQL queries written in specific format in SQL Server. In this article, we will see how to call Stored Procedure in ASP.NET using ADO.NET.

 

Stored Procedures improve performance of your application as these are created and compiled in SQL Server. So instead of writing queries in your application, write Stored Procedures in SQL Server and call them from your application using ADO.NET code.

Articles on how to write Stored Procedure are available on DevASP.NET. You can refer to these articles to learn about how to write Stored Procedures.  In this article, I have written a simple Stored Procedure in NORTHWIND Database to get product name by product ID. The main focus of this article is to show how you can call this Stored Procedure in your application using ADO.NET. A Stored Procedure can be with parameters or without any parameter. You can return a single value or DataSet in Stored Procedure and this data can be retrieved using ADO.NET code. You can also use a Stored Procedure to insert, update or delete values in database.
 
Let’s start our example. You can download source code.
  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual C# or Visual Basic > ASP.NET Empty Web Site > Click Ok
  4. Website > Add New Item > Web Form > Click Add
  5. Add code below in your Web Form

    <asp:Label ID="Label1" runat="server" Text="Enter Product ID:"></asp:Label>
    <asp:TextBox ID="txtboxProductID" runat="server"></asp:TextBox>
    <br />
    <asp:Button ID="btnProductName" runat="server" Text="Get Product Name"
                onclick="btnProductName_Click" />
    <asp:Label ID="lblProductName" runat="server" ></asp:Label>

     
  6. Now let’s see our Stored Procedure for this example.
     
    CREATE PROCEDURE dbo.GetProductByID
        (
        @ProductID int
        )
    AS
        SELECT ProductName FROM Products WHERE ProductID = @ProductID
     
    It is very simple Stored Procedure with one parameter. “GetProductByID” is the name of the Stored Procedure. It has one parameter defined as “@ProductID”. At the end, I have written a SELECT statement to get product name from Products table in NORTHWIND database.
     
  7. Open code behind file and include following namespace.
     
    C#
     
    using System.Data;
    using System.Data.SqlClient;
     
    VB.NET
     
    Imports System.Data
    Imports System.Data.SqlClient
     
  8. Write below function in code behind file.

    C#
     
    private string GetProductName(int productID) {
        SqlConnection connection = new SqlConnection("Data Source=Local;Initial Catalog= NORTHWND;Integrated Security=True");
        SqlCommand command = new SqlCommand("GetProductByID", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@ProductID", SqlDbType.Int).Value = productID;
        connection.Open();
        command.ExecuteNonQuery();
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        connection.Close();
        string productName = dt.Rows[0]["ProductName"].ToString();
     
        return productName;
    }
     
    VB.NET
     
    Private Function GetProductName(ByVal productID As Integer) As String
        Dim connection As New SqlConnection("Data Source=Local;Initial Catalog=NORTHWND;Integrated Security=True")
        Dim command As New SqlCommand("GetProductByID", connection)
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.Add("@ProductID", SqlDbType.Int).Value = productID
        connection.Open()
        command.ExecuteNonQuery()
        Dim adapter As New SqlDataAdapter(command)
        Dim dt As New DataTable()
        adapter.Fill(dt)
        connection.Close()
        Dim productName As String = dt.Rows(0)("ProductName").ToString()
     
        Return productName
    End Function
     
    GetProductName() function takes one parameter and returns string value. Connect to database in SQL Server. Create a SqlCommand instance and pass the Stored Procedure name and connection string as parameters. Now mention CommandType as Stored Procedure. Open connection, call ExecuteNonQuery() method of command instance and get your data. Don’t forget to close the connection. I have closed it after filling DataTable because we can work with DataTable after closing connection to the Server.
     
  9. Now write code below in Button click event method in code behind file

    C#
     
    protected void btnProductName_Click(object sender, EventArgs e) {
        int Id = Convert.ToInt32(txtboxProductID.Text);
        lblProductName.Text = GetProductName(Id);
    }
     
    VB.NET
     
    Protected Sub btnProductName_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnProductName.Click
        Dim Id As Integer = Convert.ToInt32(txtboxProductID.Text)
        lblProductName.Text = GetProductName(Id)
    End Sub
     
    Here I am getting integer value from textbox and pass this value in GetProductName() function. This function, as we know, returns Product Name by calling Stored Procedure.
     
  10. Press F5 to see result in browser

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< Multiple database connection strings in web.config file

Disclaimer - Privacy
© 2002-2017 DevASP.net