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 write multiple queries in Stored Procedure and call it in ASP.NET

Author: Alanna Kremer
Download Source Code : 1371_MultipleQueriesInSP.zip

Multiple queries can be written in a SQL Server Stored Procedure and this stored procedure can be called in same manner as for single query stored procedure.

 

I will explain in this article that how we can write multiple queries in a stored procedure and how we can call this stored procedure in our ASP.NET code. I will use two queries in my stored procedure. One is to insert record in a table and other is to update a different table in the same database. For this purpose, I am using already created database and table for my previous article and I am also adding one more table to my database.
 
These are the information for my database and tables.
 
Database Name: Sample
First Table Name: Person
Table Column names: ID (Identity column, auto increment), LastName, FirstName, City, State
Second Table Name: Employee
Table Column names: ID (Identity column, auto increment), EmpName, Age, HireDate
 

Let’s start our example. You can also 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

    <table>
        <tr>
            <td>
                EmpName:
            </td>
            <td>
                <asp:TextBox ID="txtEmpName" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                Age:
            </td>
            <td>
                <asp:TextBox ID="txtAge" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                Last Name:
            </td>
            <td>
                <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
     
                First Name:
            </td>
            <td>
                <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                City:
            </td>
            <td>
                <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                State:
            </td>
            <td>
                <asp:TextBox ID="txtState" runat="server"></asp:TextBox>
            </td>
        </tr>
    </table>
    <asp:Button ID="btnInsert" runat="server" Text="Insert record and update table"
            onclick="btnInsert_Click" />
    <br />
    <asp:Label ID="lblMessage" runat="server" ></asp:Label>
     
  6.  Write a Stored Procedure with two queries

    CREATE PROCEDURE dbo.MultipleQueries
           (
           @EmpName nvarchar(50),
           @Age int,
           @HireDate datetime,
           @ID int,
           @LastName nvarchar(50),
           @FirstName nvarchar(50),
           @City nvarchar(30),
           @State nvarchar(10)
           )
    AS
    BEGIN
    BEGIN TRANSACTION
     
    BEGIN
           INSERT Employee
                  (
                  EmpName,
                  Age,
                  HireDate
                  )
           VALUES
                  (
                  @EmpName,
                  @Age,
                  @HireDate
                  )
    END
     
    BEGIN
            UPDATE Person
            SET
            LastName = @LastName,
            FirstName = @FirstName,
            City = @City,
            State = @State
            WHERE ID = @ID
    END
     
    IF(@@error = 0)
           COMMIT TRANSACTION
    ELSE
           ROLLBACK TRANSACTION
     
    END   
    RETURN
     
    Write multiple queries as a transaction. Check error, if there is no error then commit transaction otherwise rollback transaction. 

     
  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#
     
    public void CallStoredProcedure(string empName, int age, DateTime hiredate, int id, string lastname, string firstname, string city, string state)
    {
        string constring = "Data Source=Local;Initial Catalog=Sample;Integrated Security=True";
        SqlConnection conn = new SqlConnection(constring);
        SqlCommand cmd = new SqlCommand("MultipleQueries", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@EmpName", SqlDbType.NVarChar, 50).Value = empName;
        cmd.Parameters.Add("@Age", SqlDbType.Int).Value = age;
        cmd.Parameters.Add("@HireDate", SqlDbType.DateTime).Value = hiredate;
        cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = lastname;
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = firstname;
        cmd.Parameters.Add("@City", SqlDbType.NVarChar, 30).Value = city;
        cmd.Parameters.Add("@State", SqlDbType.NVarChar, 10).Value = state;
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
        finally
        {
            conn.Close();
        }
    }
     
    VB.NET
     
    Public Sub CallStoredProcedure(ByVal empName As String, ByVal age As Integer, ByVal hiredate As DateTime, ByVal id As Integer, ByVal lastname As String, ByVal firstname As String, ByVal city As String, ByVal state As String)
        Dim constring As String = "Data Source=Local;Initial Catalog=Sample;Integrated Security=True"
        Dim conn As New SqlConnection(constring)
        Dim cmd As New SqlCommand("MultipleQueries", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@EmpName", SqlDbType.NVarChar, 50).Value = empName
        cmd.Parameters.Add("@Age", SqlDbType.Int).Value = age
        cmd.Parameters.Add("@HireDate", SqlDbType.DateTime).Value = hiredate
        cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = lastname
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = firstname
        cmd.Parameters.Add("@City", SqlDbType.NVarChar, 30).Value = city
        cmd.Parameters.Add("@State", SqlDbType.NVarChar, 10).Value = state
        Try
            conn.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            lblMessage.Text = ex.Message
        Finally
            conn.Close()
        End Try
    End Sub
     
    Connect to your server and set connection. Create an instance of SqlCommand class and pass stored procedure name and connection object as parameters. Add all parameters mentioned in stored procedure for both queries. Open connection and call ExecuteNonQuery() method.
     
  9. Now write code below in btnInsert Click event

    C#
     
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        string empName = txtEmpName.Text;
        int age = Convert.ToInt32(txtAge.Text);
        string lastname = txtLastName.Text;
        string firstname = txtFirstName.Text;
        string city = txtCity.Text;
        string state = txtState.Text;
     
        CallStoredProcedure(empName, age, DateTime.Now, 4, lastname, firstname, city, state);
     
        lblMessage.Text = "Stored procedure executed successfully";
    }
     
    VB.NET
     
    Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnInsert.Click
        Dim empName As String = txtEmpName.Text
        Dim age As Integer = Convert.ToInt32(txtAge.Text)
        Dim lastname As String = txtLastName.Text
        Dim firstname As String = txtFirstName.Text
        Dim city As String = txtCity.Text
        Dim state As String = txtState.Text
     
        CallStoredProcedure(empName, age, DateTime.Now, 4, lastname, firstname, city, state)
     
        lblMessage.Text = "Stored procedure executed successfully"
    End Sub
     
    Get all TextBox values and call CallStoredProcedure() method by providing all parameters.
     
  10. Press F5, insert values and see result.

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to get last inserted record ID from SQL Server table in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net