Minder chen ph d
Download
1 / 11

Minder Chen, Ph.D. - PowerPoint PPT Presentation


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

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.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
  • Login