473,388 Members | 996 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

turning 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

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.







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%">



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.Ge t("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("C onnectionString"))

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.
AMBER [MCSD.NET MCAD.NET] http://www.dedicatedsolutions.co.uk
Nov 17 '05 #1
0 2621

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: shamansc | last post by:
I am trying to use RDB stored procedures to return result sets. How do I implement this in RDB? For example to select all employees in a table select * from employees How would I return a...
2
by: Yves Touze | last post by:
Hi All, I'm trying to migrate from SQL Server 7.0 to SQL Server 2000. I've got some ASP page which call VB components that retrieve shaped recordsets from SQL Server using the MSDATASHAPE...
1
by: Top Gun | last post by:
In order to avoid multiple trips to the database, I would like to fill several tables in a DataSet with a single call to a stored procedure that will return resultsets for the appropriate tables. ...
18
by: Robin Lawrie | last post by:
Hi again, another problem! I've moved from an Access database to SQL server and am now having trouble inserting dates and times into seperate fields. I'm using ASP and the code below to get the...
5
by: gilles27 | last post by:
I've ready many of the posts on this and other newsgroups in which people describe working practices for source control of database scripts. We are looking to implement something similar in my...
6
by: Tor Heigre | last post by:
Hello While testing our code on DB2 we have encountered a difference in the behaviour of DB2Driver (com.ibm.db2.jcc.DB2Driver with driverType= 4) compared to the drivers offered by Oracle 9i and...
0
by: Amber | last post by:
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...
1
by: Robson Siqueira | last post by:
Folks, For designing WinApps, I do prefer to have the controls dragged and dropped into the screen, mainly for datagridview controls. For that end, I normally use the DataSet designer but...
2
by: rsreeni | last post by:
Hi, I would appreciate if any one provide any example code for accessing ResultSets( Cursor declared with Return ) from Java? Thanks, Sreeni
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.