Minder Chen, Ph.D. - PowerPoint PPT Presentation

minder chen ph d l.
Skip this Video
Loading SlideShow in 5 Seconds..
Minder Chen, Ph.D. PowerPoint Presentation
Minder Chen, Ph.D.

play fullscreen
1 / 11
Download Presentation
gray-christian
Views
Download Presentation

Minder Chen, Ph.D.

Presentation Transcript

  1. Stored Procedures and ASP.NET Minder Chen, Ph.D.

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

  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

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

  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>

  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>

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

  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

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

  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

  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>