Session 8: Data Management (with Stored Procedures)

1
Session 8 Data Management (with Stored
Procedures)
2
Data Management (Using Stored Procedures)

• What Is a Stored Procedure?
• Why Use Stored Procedures?
• Calling Stored Procedures
• Using Parameters
• Input Parameters
• Output Parameters

3
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

StoredProcedure
Web Form
Client
Database
SQL Server
Web Server
4
Why Use Stored Procedures?

• Modular programming
• Distribution of work
• Database security
• Faster execution
• Reduces network traffic
• Provides flexibility

5
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.Select
Command.Connection conn daCategory.SelectCommand
.CommandText "ProductCategoryList" daCategory.Se
lectCommand.CommandType CommandType.StoredProced
ure
daCategory.Fill(ds, "Categories")
6
Example Calling a Stored Procedure
7
Using Parameters

• Identify the available parameters
• Input
• Output
• InputOutput
• ReturnValue
• Include parameters in the parameters collection
• or
• Include parameter values in the command string

8
Passing Input Parameters

• Create parameter, set direction and value, add to
  the Parameters collection
• Run stored procedure and store returned records

param New SqlParameter _ ("_at_Beginning_Date",
SQLDbType.DateTime) param.Direction
ParameterDirection.Input param.Value
CDate(txtStartDate.Text) da.SelectCommand.Paramete
rs.Add(param)
SqlParameter param new SqlParameter
("_at_Beginning_Date", SqlDbType.DateTime) param.Dir
ection ParameterDirection.Input param.Value
Convert.ToDateTime (txtStartDate.Text) da.Sele
ctCommand.Parameters.Add(param)
ds New DataSet() da.Fill(ds, "Products")
ds New DataSet() da.Fill(ds, "Products")
9
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("_at_ItemCount",
SQLDbType.Int) param.Direction
ParameterDirection.Output da.SelectCommand.Paramet
ers.Add(param)
ds new DataSet() da.Fill(ds)
iTotal da.Parameters("_at_ItemCount").Value
10
Example Passing Parameters