Minder chen ph d
Download
1 / 11

Dealing with SQL Server and Stored Procedures - PowerPoint PPT Presentation


  • 382 Views
  • Updated On :

Stored Procedures and ASP.NET. Minder Chen, Ph.D. Installing a Sample SQL Database. You may need to download SQL Server Express http://msdn2.microsoft.com/en-us/express/aa718378.aspx

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Dealing with SQL Server and Stored Procedures' - salena


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Minder chen ph d l.jpg

Stored Procedures and ASP.NET

Minder Chen, Ph.D.


Installing a sample sql database l.jpg
Installing a Sample SQL Database

  • You may need to download SQL Server Express

    • http://msdn2.microsoft.com/en-us/express/aa718378.aspx

  • Download SQL Server sample Northwind database at http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en


Config web l.jpg
Config.Web

<configuration>

<appSettings/>

<connectionStrings>

<add name="NORTHWNDConnectionString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

</connectionStrings>

</configuration>

Data Source=.\SQLEXPRESS;AttachDbFilename=D:\ASPNET2\SQLServerTutor\App_Data\NORTHWND.MDF;Integrated Security=True;User Instance=True


Categorylist aspx l.jpg
CategoryList.aspx

<%@ Page Language="VB" %>

<%@ Import Namespace="System.Data.SQLClient" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.DbType" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<script runat="server">

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

Dim conn As SqlConnection

Dim cmd As SqlCommand

Dim dr As SqlDataReader

conn = New SqlConnection()

‘ conn = New SqlConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _

' "Data Source=" & Server.MapPath("Northwind.mdb"))

If Not IsPostBack Then

Try

conn.ConnectionString = ConfigurationManager.ConnectionStrings("NORTHWNDConnectionString1").ConnectionString

conn.Open()

cmd = New SqlCommand("select * from categories", conn)

cmd.CommandType = CommandType.Text

dr = cmd.ExecuteReader()


Continued l.jpg
Continued…

Label1.Text &= "<table border=4><tr><th>ID</th><th>Name</th></tr>"

While dr.Read()

Label1.Text &= "<tr><td>" & dr("CategoryID") & _

"</td><td> <a href='ProductsBycategory.aspx?cid=" & _

dr("CategoryID") & "&cname=" & _

Server.UrlEncode(dr("CategoryName")) & "'>" & _

dr("CategoryName") & "</a> </td></tr>"

End While

Label1.Text &= "</table>"

Catch ex As Exception

Label1.Text = "Database error!" & "<br>" & ex.Message

Finally

conn.Close()

End Try

End If

End Sub

</script>


Continued6 l.jpg
Continued…

<html xmlns="http://www.w3.org/1999/xhtml" >

<head id="Head1" runat="server">

<title>Category List</title>

</head>

<body>

<H1>List of categories</H1>

<form id="form1" runat="server">

<div>

<asp:Label id="Label1" runat="server"></asp:Label>

</div>

</form>

</body>

</html>


Stored procedure template l.jpg
Stored Procedure Template

CREATE PROCEDURE dbo.StoredProcedure2

/*

(

@parameter1 int = 5,

@parameter2 datatype OUTPUT

)

*/

AS

/* SET NOCOUNT ON */

RETURN


Create a store procedure l.jpg
Create a Store Procedure

CREATE PROCEDURE dbo.ProductsByCategory

(

@CatID

)

AS

SET NOCOUNT ON

SELECT ProductID, ProductName, UnitPrice, CategoryID

FROM Products

WHERE (CategoryID = @CatID)

RETURN


Productsbycategory aspx l.jpg
ProductsByCategory.aspx

<%@ Page Language="VB" %>

<%@ Import Namespace="System.Data.SQLClient" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.DbType" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<script runat="server">

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

Dim conn As SqlConnection

Dim cmd As SqlCommand

Dim dr As SqlDataReader

LabelTitle.Text = “Products from “ & Request.QueryString("cname")

conn = New SqlConnection()

conn.ConnectionString = & _

ConfigurationManager.ConnectionStrings("NORTHWNDConnectionString1").ConnectionString


Continued10 l.jpg
Continued…

Try

cmd = New SqlCommand()

cmd.Connection = conn

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "ProductsByCategory"

cmd.Parameters.Clear()

cmd.Parameters.Add("@CatID", SqlDbType.Int).Value = Request.QueryString("cid")

conn.Open()

dr = cmd.ExecuteReader()

Label1.Text &= "<table border=4><tr><th>ID</th><th>Name</th><th>Price</th></tr>"

Do While dr.Read()

Label1.Text &= "<tr><td>" & dr("ProductID") & _

"</td><td> <a href='ProductDetail.aspx?pid=" & _

dr("ProductID") & "'>" & _

dr("ProductName") & "</a> </td><td align='right'>" & _

dr("UnitPrice") & "</td></tr>"

Loop


Continued11 l.jpg
Continued…

Catch ex As Exception

Label1.Text = "Database error!" & "<br>" & ex.Message

Finally

conn.Close()

conn = Nothing

Label1.Text &= "</table>"

End Try

End Sub

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head id="Head1" runat="server">

<title>Category List</title>

</head>

<body>

<H1>Products from <asp:Label ID="LabelTitle" runat="server" Text="Label"></asp:Label></H1>

<form id="form1" runat="server">

<div>

<asp:Label ID="Label1" runat="server"></asp:Label>

</div>

</form>

</body>

</html>


ad