Minder Chen, Ph.D..


59 views
Uploaded on:
Category: Travel / Places
Description
SQL Server and ASP.NET - 2. Minder Chen, 2007. Introducing a Sample SQL Database. You might ... Download SQL Server test Northwind database at http://www.microsoft. ...
Transcripts
Slide 1

Put away Procedures and ASP.NET Minder Chen, Ph.D.

Slide 2

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 test Northwind database at http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Slide 3

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

Slide 4

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()

Slide 5

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>

Slide 6

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>

Slide 7

Stored Procedure Template CREATE PROCEDURE dbo.StoredProcedure2 /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */AS /* SET NOCOUNT ON */ RETURN

Slide 8

Create a Store Procedure CREATE PROCEDURE dbo.ProductsByCategory ( @CatID ) AS SET NOCOUNT ON SELECT ProductID, ProductName, UnitPrice, CategoryID FROM Products WHERE (CategoryID = @CatID) RETURN

Slide 9

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 = "Items from " & Request.QueryString("cname") conn = New SqlConnection() conn.ConnectionString = & _ ConfigurationManager.ConnectionStrings("NORTHWNDConnectionString1").ConnectionString

Slide 10

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

Slide 11

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>

Recommended
View more...