1 / 11

Minder Chen, Ph.D.

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

salena
Download Presentation

Minder Chen, Ph.D.

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N 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>

More Related