Workshop

for RuBoard

This workshop will help reinforce the concepts covered in today's lesson.

Quiz

1:	Why would you use stored procedures rather than dynamic or inline SQL?
A1:	If stored procedures are supported by your data store, you should consider using them because they can be used to implement a layer of abstraction for your applications, as well as increase both performance and security. However, you might want to use inline SQL if your application needs to supports multiple data stores.
2:	Which execute method might you use when executing a SQL statement that uses an aggregate function?
A2:	Typically, SELECT statements that use aggregate functions such as SUM , MIN , MAX , COUNT , and AVG can be easily executed using the ExecuteScalar method. This method returns the first column of the first row of the result set so that you needn't use a data reader.
3:	When would you use the ExecuteXmlReader method of the SqlCommand object?
A3:	This method returns an XmlReader from a command that uses the SQL Server 2000 FOR XML statement. This comes in handy if you want to return data from SQL Server as XML in order to send it to a trading partner or transform it for use on a Web site.
4:	In what situations would you use output parameters?
A4:	Output parameters are particularly effective when you need to return only one or a few values from a stored procedure. Using output parameters reduces the cost of execution on the server because a result set doesn't need to be created and makes the code you have to write simpler on the client.

Exercise

Q1:

Write a method and stored procedure that selects all orders based on a given date range and returns a SqlDataReader .

A1:

One possible solution might be the following: Stored Procedure: CREATE PROCEDURE usp_OrdersByDate @startdate smalldatetime, @enddate smalldatetime, @revenue money OUTPUT AS SELECT * FROM Orders WHERE OrderDate BETWEEN @startdate AND @enddate ORDER BY OrderDate GO Method: Public Function GetOrder(ByVal connect As SqlConnection, _ ByVal startDate As Date, ByVal endDate As Date) As SqlDataReader Dim com As New SqlCommand("usp_OrdersByDate", connect) Dim dr As SqlDataReader If connect.State = ConnectionState.Closed Then Throw New Exception("Must have an open connection") End If With com .CommandType = CommandType.StoredProcedure .Parameters.Add(New SqlParameter("@startdate", SqlDbType.SmallDateTime)) .Parameters.Add(New SqlParameter("@enddate", SqlDbType.SmallDateTime)) .Parameters("@startdate").Value = startDate .Parameters("@enddate").Value = endDate End With Try dr = com.ExecuteReader(CommandBehavior.CloseConnection) Return dr Catch e As SqlException Throw e End Try End Function

for RuBoard