session 8: data management (with stored procedures)
DESCRIPTION
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 Client SQL Server Web Form Stored Procedure Web Server DatabaseTRANSCRIPT
![Page 1: Session 8: Data Management (with Stored Procedures)](https://reader036.vdocuments.us/reader036/viewer/2022082501/5a4d1b697f8b9ab0599b2842/html5/thumbnails/1.jpg)
Session 8: Data Management
(with Stored Procedures)
![Page 2: Session 8: Data Management (with Stored Procedures)](https://reader036.vdocuments.us/reader036/viewer/2022082501/5a4d1b697f8b9ab0599b2842/html5/thumbnails/2.jpg)
Data Management (Using Stored Procedures)
What Is a Stored Procedure? Why Use Stored Procedures? Calling Stored Procedures Using Parameters
Input Parameters
Output Parameters
![Page 3: Session 8: Data Management (with Stored Procedures)](https://reader036.vdocuments.us/reader036/viewer/2022082501/5a4d1b697f8b9ab0599b2842/html5/thumbnails/3.jpg)
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
Client
SQL Server
Web Form
StoredProcedure
Web Server
Database
![Page 4: Session 8: Data Management (with Stored Procedures)](https://reader036.vdocuments.us/reader036/viewer/2022082501/5a4d1b697f8b9ab0599b2842/html5/thumbnails/4.jpg)
Why Use Stored Procedures?
Modular programming Distribution of work Database security Faster execution Reduces network traffic Provides flexibility
![Page 5: Session 8: Data Management (with Stored Procedures)](https://reader036.vdocuments.us/reader036/viewer/2022082501/5a4d1b697f8b9ab0599b2842/html5/thumbnails/5.jpg)
Calling Stored Procedures
Identify the stored procedure Set up the SelectCommand property of the DataAdapter
Run the stored procedure and store returned records
Dim daCategory As New SqlDataAdapter()daCategory.SelectCommand = New SqlCommand()daCategory.SelectCommand.Connection = conndaCategory.SelectCommand.CommandText = "ProductCategoryList"daCategory.SelectCommand.CommandType = CommandType.StoredProcedure
daCategory.Fill(ds, "Categories")
![Page 6: Session 8: Data Management (with Stored Procedures)](https://reader036.vdocuments.us/reader036/viewer/2022082501/5a4d1b697f8b9ab0599b2842/html5/thumbnails/6.jpg)
Example: Calling a Stored Procedure
![Page 7: Session 8: Data Management (with Stored Procedures)](https://reader036.vdocuments.us/reader036/viewer/2022082501/5a4d1b697f8b9ab0599b2842/html5/thumbnails/7.jpg)
Using Parameters
Identify the available parameters Input Output InputOutput ReturnValue
Include parameters in the parameters collection
or Include parameter values in the command string
![Page 8: Session 8: Data Management (with Stored Procedures)](https://reader036.vdocuments.us/reader036/viewer/2022082501/5a4d1b697f8b9ab0599b2842/html5/thumbnails/8.jpg)
Passing Input Parameters
Create parameter, set direction and value, add to the Parameters collection
Run stored procedure and store returned records
SqlParameter param = new SqlParameter ("@Beginning_Date", SqlDbType.DateTime);param.Direction = ParameterDirection.Input;param.Value = Convert.ToDateTime
(txtStartDate.Text);da.SelectCommand.Parameters.Add(param);
ds = New DataSet();da.Fill(ds, "Products");ds = New DataSet()da.Fill(ds, "Products")
param = New SqlParameter _ ("@Beginning_Date", SQLDbType.DateTime)param.Direction = ParameterDirection.Inputparam.Value = CDate(txtStartDate.Text)da.SelectCommand.Parameters.Add(param)
![Page 9: Session 8: Data Management (with Stored Procedures)](https://reader036.vdocuments.us/reader036/viewer/2022082501/5a4d1b697f8b9ab0599b2842/html5/thumbnails/9.jpg)
Using Output Parameters
Create parameter, set direction, add to the Parameters collection
Run stored procedure and store returned records
Read output parameters
param = New SqlParameter("@ItemCount", SQLDbType.Int)param.Direction = ParameterDirection.Outputda.SelectCommand.Parameters.Add(param)
ds = new DataSet()da.Fill(ds)
iTotal = da.Parameters("@ItemCount").Value
![Page 10: Session 8: Data Management (with Stored Procedures)](https://reader036.vdocuments.us/reader036/viewer/2022082501/5a4d1b697f8b9ab0599b2842/html5/thumbnails/10.jpg)
Example: Passing Parameters