.NET 系列教程2310B_11

What Is a Stored Procedure? Why Use Stored Procedures? Practice: Select the Correct Stored Procedure
What Is a Stored Procedure?
A common data procedures that can be called by many Web applications Programmatic access to a database Return records Return value Perform action
Web Form Client Web Server Stored Procedure
Database SQL Server
Why Use Stored Procedures?
Modular programming Distribution of work Database security Faster execution Reduces network traffic Provides flexibility
Practice: Select the Correct Stored Procedure
Students will: Given scenarios, decide what type of stored procedure needs to be used Time: 5 Minutes
Lesson: Calling Stored Procedures
Web. config
tempdb
Life Insurance
Life.aspx
Retirement
Retirement.aspx
Medical
Medical.aspx
Dental
Dental.aspx
Prospectus
Lab Web Application
Prospectus.aspx
Doctors
Calling Stored Procedures Demonstration: Calling a Stored Procedure Practice: Displaying Data from a Stored Procedure Using Parameters Passing Input Parameters Using Output Parameters Demonstration: Passing Parameters
Run the stored procedure and store returned records
daCategory.Fill(ds, "Categories") daCategory.Fill(ds, "Categories");
Demonstration: Calling a Stored Procedure
Module 11: Calling Stored Procedures with Microsoft
Overview
Overview of Stored Procedures Calling Stored Procedures
Lesson: Overview of Stored Procedures
Run stored procedure and store returned records
ds = new DataSet() da.Fill(ds) ds = new DataSet(); da.Fill(ds);
Read output parameters
iTotal = da.Parameters("@ItemCount").Value iTotal = da.Parameters("@ItemCount").Value;
Run stored procedure and store returned records
DataSet() ds = New DataSet(); "Products") da.Fill(ds, "Products");
Code Examples
Using Output Parameters
Create parameter, set direction, add to the Parameters collection
param = New SqlParameter("@ItemCount", SQLDbType.Int) param.Direction = ParameterDirection.Output da.SelectCommand.Parameters.Add(param) param = new SqlParameter("@ItemCount", SqlDbType.Int); param.Direction = ParameterDirection.Output; da.SelectCommand.Parameters.Add(param);
Using Parameters
Identify the available parameters Input Output InputOutput ReturnValue Include parameters in the parameters collection or Include parameter values in the command string
Doctors.aspx
User Control
namedate.ascx
XML Web Service
dentalService1.asmx
XML Files
Doctors
Dentists
�
Passing Input Parameters
Create parameter, set direction and value, add to the Parameters collection
SqlParameter param = new SqlParameter param = New SqlParameter _ ("@Beginning_Date", SqlDbType.DateTime); ("@Beginning_Date", SQLDbType.DateTime) param.Direction = ParameterDirection.Input; param.Direction = meterDirection.Input param.Value = Convert.ToDateTime param.Value = CDate(txtStartDate.Text) (txtStartDate.Text); da.SelectCommand.Parameters.Add(param) da.SelectCommand.Parameters.Add(param);
Logon Page
Login.aspx Coho Winery
Benefits Home Page
Default.aspx
Page Header Menu Component
Class1.vb or Class1.cs Header.ascx
ASPState
Registration
Register.aspx
Demonstration: Passing Parameters
Review
Overview of Stored Procedures Calling Stored Procedures
Lab 11: Calling Stored Procedures with Microsoft
Practice: Displaying Data from a Stored Procedure
Students will: Drag a stored procedure from Server Explorer onto an Web form Create a DataReader to read data From the stored procedure Bind a DataGrid to the DataReader Time: 15 Minutes
Calling Stored Procedures
Identify the stored procedure Set up the SelectCommand property of the DataAdapter
Dim daCategory As New SqlDataAdapter() daCategory.SelectCommand = New SqlCommand() daCategory.SelectCommand.Connection = conn mandText = "ProductCategoryList" mandType = CommandType.StoredProcedure SqlDataAdapter daCategory = new SqlDataAdapter(); daCategory.SelectCommand = new SqlCommand(); daCategory.SelectCommand.Connection = conn; mandText = "ProductCategoryList"; mandType = CommandType.StoredProcedure;