Returning Resultsets Using Sql Server Stored Procedures...

Stored procedures are faster and more efficient than in-line SQL statements. In this article we will look at two SQL Server stored procedures; one using an input parameter and one not, and see how to call them from an ASP.Net page

By: John Kilgo

Date: March 26, 2003

Download the code.

Every modern database system has a stored procedure language. SQL Server is no different and has a relatively sophisticated and easy to use system. This article will not attempt to go into depth in explaining SQL Server stored procedure programming - there are whole books devoted to the subject. Instead we will provide a glimpse at how easy it is to perform some relatively simple tasks that we .Net programmers need to do every day.

In this example we will use use two stored procedures. The first will bring back a comlete list of all the author last names from the authors table in the Pubs database. The lastnames will be placed in a DropDownList. The second procedure will accept as an input parameter the author lastname selected in the DropDownList and return the entire row(s) of data for that author last name. This information will be place in a DataGrid.

First the .aspx page which contains a label telling the user to select a name from the dropdown, the DropDownList containing the last names, and a button to execute the sub-routine which will fill the DataGrid with the row of data for the selected last name. Following is the code for the .aspx page.

<%@ Page Language="vb" Src="SqlStoredProc.aspx.vb" Inherits="SqlStoredProc" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>SqlStoredProc</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name=vs_defaultClientScript content="JavaScript">
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:Label id="lblSelect" text="Select an author: " runat="server" />
<asp:DropDownList id="ddlAuthor" runat="server" />
<asp:Button id="btnGetAuthor" text="Find Authors" OnClick="btnGetAuthor_Click" runat="server" />
<p></p>
<asp:DataGrid ID="dtgAuthors" runat="server"
              AutoGenerateColumns=True
              HeaderStyle-BackColor="IndianRed"
              HeaderStyle-Font-Bold="True"
              HeaderStyle-Font-Name="Verdana"
              HeaderStyle-Font-Size="12px"
              HeaderStyle-ForeColor="White"
              ItemStyle-BackColor=Gainsboro
              ItemStyle-Font-Name="verdana"
              ItemStyle-Font-Size="12px"
              Width="75%">
</asp:DataGrid>
</form>
</body>
</html>

At this point we take a look at the stored procedures before finishing up with the code-behind page. The first stored procedure returns all of the last names from the authors table and is really quite simple. It is as follows:

CREATE PROCEDURE GetAuthorLastNames
AS
select distinct au_lname from authors;
GO

All we have done is create a procedure, give it a name, use the AS keyword, followed by our select statement. That is all that is needed. When ADO.Net executes that procedure name it will receive back a complete resultset just as it would have with an in-line SQL statement.

The next procedure is a little different in that it accepts an input parameter which will appear in the WHERE clause so that only specific rows are returned. That procedure is a follows.

CREATE PROCEDURE GetAuthorsByLastName(@au_lname varchar(20))
AS
select * from authors where au_lname=@au_lname;
GO

This time we have again created a procedure, with a name, but with an input parameter added parenthetically after the procedure name. Parameters are denoted by the @ sign. I used @au_lname (the same as the column name I am comparing to), but I could have used @xxx if I had wanted to. We then have our select statement but with a WHERE clause to bring back only rows where the author last name is equal to the last name we selected in our DropDownList. We will see how that works next in the code-behind file.

As usual, all the work gets done in the code-behind file. It is shown below. We fill the DropDownList in the Page_Load event. Notice the line strSql = "EXECUTE GetAuthorLastNames". Instead of an in-line SQL statement we simply are telling ADO to execute our first stored procedure. Four lines below that we set dataReader = objCmd.ExecuteReader and we have our result set. From there it is just a matter of setting the DropDownList's datasource to the DataReader.

It is in the btnGetAuthor_Click event that we respond to the button being clicked on the .aspx page after an author last name has been selected. The operative line in that event is:

strSQL = "EXECUTE GetAuthorsByLastName '" & ddlAuthor.SelectedItem.Text & "'"

Here our SQL is to execute our second stored procedure, but also passing in the input variable which is the author last name selected in the DropDownList. After that we again get our DataReader and then set the datasource of our DataGrid.

Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Public Class SqlStoredProc : Inherits System.Web.UI.Page

  Protected ddlAuthor As System.Web.UI.WebControls.DropDownList
  Protected dtgAuthors As System.Web.UI.WebControls.DataGrid
  Dim objConn As SqlConnection
  Dim objCmd As SqlCommand
  Dim dataReader As SqlDataReader
  Dim strSql As String

  Private Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
    If Not IsPostBack Then
      Dim objConn As SqlConnection
      Dim dataReader As SqlDataReader

      objConn = New SqlConnection(ConfigurationSettings.AppSettings.Get("ConnectionString"))
      strSql = "EXECUTE GetAuthorLastNames"
      objCmd = New SqlCommand(strSql, objConn)
      Try
        objConn.Open()
        dataReader = objCmd.ExecuteReader
        With ddlAuthor
          .DataSource = dataReader
          .DataTextField = "au_lname"
          .DataValueField = "au_lname"
          .DataBind()
        End With
      Catch
      Finally
        objConn.Close()
        objConn.Dispose()
      End Try
    End If
  End Sub

  Public Sub btnGetAuthor_Click(sender as Object, e As EventArgs)
    objConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

    strSQL = "EXECUTE GetAuthorsByLastName '" & ddlAuthor.SelectedItem.Text & "'"
    objCmd = New SqlCommand(strSQL, objConn)
    Try
      objConn.Open()
      dataReader = objCmd.ExecuteReader()
      'Bind to DataGrid
      dtgAuthors.DataSource = dataReader
      dtgAuthors.DataBind()
    Catch
    Finally
      objConn.Close()
      objConn.Dispose()
    End Try
  End Sub

End Class

I hope you have learned how easy it is to write simple SQL Server stored procedures to return data to your .Net programs. As you may guess, it is just as simple to do INSERTs, UPDATEs, and DELETEs as well.

You may download the code here.