
Listing 1	VB.NET 
Execute Stored Procedures With SqlCommand	SqlClient lets you execute stored procedures through the SqlCommand object, which also helps you execute dynamic SQL through its CommandType property. Here you call the data access class NwindProducts from an ASP.NET application's UI. Note how you implement the AddCategory method here.


Option Strict On
Option Explicit On 

Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.IO
Imports System.Xml
Public Class NwindProducts
	Private _connect As String
	Public Sub New(ByVal connect As String)
		_connect = connect
	End Sub
	Public Function AddCategory( _
		ByVal name As String) As Integer
		' Verify the incoming parameters
		If name Is Nothing Then
			Throw New ArgumentNullException( _
				"name ", _
					"This method requires name")
		End If
		Return _AddCategory(name, Nothing)
	End Function
	Public Function AddCategory(_
		ByVal name As String, _
		ByVal description As String) As Integer
		If name Is Nothing _
			Or description Is Nothing Then
			Throw New ArgumentNullException( _
				 "name or description", _
				"Name and description are required")
		End If
		Return _AddCategory(name, description)
	End Function
	Private Function _AddCategory( _
		ByVal name As String, _
		ByVal description As String) As Integer
		' private implementation
		Dim cn As New SqlConnection(_connect)
		Dim cm As SqlCommand
		Try
			' Build the command
			cm = New SqlCommand("usp_AddCategory", _
				cn)cm.CommandType = _
				CommandType.StoredProcedure
			' Add the parameters
			Dim parm As New SqlParameter("@name", _
				SqlDbType.NVarChar)
			parm.Value = name
			cm.Parameters.Add(parm)
			If not description Is Nothing Then
				Dim parm2 As New _
					SqlParameter("@desc", _
					SqlDbType.NText)
				parm2.Value = description
				cm.Parameters.Add(parm2)
			End If
			Dim retparm As New _
				SqlParameter("RETURN", _
				SqlDbType.Int)
			retparm.Direction = _
				ParameterDirection.ReturnValue
			cm.Parameters.Add(retparm)
			' Execute the command
			cn.Open()
			cm.ExecuteNonQuery()
			Return CType(retparm.Value, Integer)
		Catch e As Exception
			Throw e
		Finally
			' Release connection
			cn.Close()
		End Try
	End Function
End Class

Listing 2	VB.NET
Get to the Point With ExecuteScalar
Okay, it's no faster than the ExecuteNonQuery method, but the ExecuteScalar method lets you capture single-row, single-column results easily. Instead of caching such a resultset in a DataSet object or opening a SqlDataReader, use the ExecuteScalar method to return the value as an object. You can then convert it to the appropriate type. In this example, you use the Decimal data type to return a currency value.

Public Function CustomerTotal( _
	ByVal customer As String) As Decimal
	Dim cn As New SqlConnection(_connect)
	Dim cm As SqlCommand
	If customer Is Nothing Then
		Throw New ArgumentNullException( _
			"customer", "Customer must contain ID")
	End If
	Try
		' Build the command
		cm = New SqlCommand("usp_GetCustTotal", cn)
		cm.CommandType = _
			CommandType.StoredProcedure
		' Add the parameters
		Dim parm As New SqlParameter( _
			"@customerID", SqlDbType.NChar)
		parm.Value = customer
		cm.Parameters.Add(parm)
		cn.Open()
		' Rounding because it's a monetary value
		Return Decimal.Round(CType( _
			cm.ExecuteScalar(), Decimal), 2)
	Catch e As Exception
		Throw e
	Finally
		cn.Close()
	End Try
End Function

Listing 3	SQL
Populate XML Objects With ExecuteXmlReader
SqlClient can populate System.Xml.XmlReader objects through the ExecuteXmlReader method. This works if the stored procedure uses the FOR XML statement to create an XML document from a SELECT statement. The usp_GetCatTotals stored procedure uses the FOR XML EXPLICIT statement to build an XML document while the GetCatTotals method retrieves the XML using the ExecuteXmlReader method. The method returns the open XmlReader that you can then iterate, using its methods and properties such as Read, ReadInnerXml, and Value.

CREATE PROCEDURE usp_GetCatTotals
@CatID int
AS
SELECT 1 As Tag, NULL as Parent,
	@CatID AS [OrderTot!1!CatID],
	NULL As [Product!2!ProductID], 
	NULL	As [Product!2!ProductName], 
	NULL As [Product!2!TotalUnits!element]
UNION
SELECT 2, 1,
	NULL,
	d.ProductID,
	d.ProductName,
	SUM(b.UnitPrice)
FROM Orders a JOIN [Order Details] b 
	ON a.OrderID = b.OrderID
JOIN Products d ON d.ProductID = b.ProductID
WHERE d.CategoryID = @CatID
GROUP BY d.ProductID, d.ProductName
FOR XML EXPLICIT
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
Public Function GetCatTotals( _
	ByVal catID As Integer) As XmlReader
	Dim cn As New SqlConnection(_connect)
	Dim cm As SqlCommand
	If catID = 0 Then
		Throw New ArgumentNullException("catID", _
			"CatID must be > 0")
	End If
	Try
		' Build the command
		cm = New SqlCommand("usp_GetCatTotals", cn)
		cm.CommandType = _
			CommandType.StoredProcedure
		' Add the parameters
		Dim parm As New SqlParameter("@catID", _
			SqlDbType.Int)
		parm.Value = catID
		cm.Parameters.Add(parm)
		cn.Open()
		Return cm.ExecuteXmlReader()
	Catch e As Exception
		Throw e
	Finally
		cn.Close()
	End Try
End Function

Listing 4	VB.NET
Update Cached Records With SqlDataAdapter
Take care when updating data from cached records. Fortunately, SqlDataAdapter eases this process by exposing the InsertCommand, UpdateCommand, and DeleteCommand properties that map to SqlCommand objects. If you populate these objects with commands that reference stored procedures, SqlDataAdapter will execute stored procedures when you call the Update method. For example, the SaveSuppliers method accepts a DataSet as a parameter, then updates the database. SaveSuppliers uses two SqlCommand objects: one for insert and update, the other for delete.



Public Function SaveSuppliers( _
	ByVal suppliersData As DataSet) As DataSet
	Dim cn As New SqlConnection(_connect)
	Dim da As New SqlDataAdapter()
	Dim cmSave As New _
		SqlCommand("usp_SaveSupplier", cn)
	Dim cmDel As New _
		SqlCommand("usp_DelSupplier", cn)
	' Don't do anything if no changes made 
	If Not suppliersData.HasChanges Then
		Return Nothing
	End If
	' Configure the Save command
	With cmSave
		.Parameters.Add(New _
			SqlParameter("@supplierID", _
			SqlDbType.Int))
		.Parameters(0).SourceColumn = "SupplierID"
		.Parameters.Add(New SqlParameter( _
			"@company", SqlDbType.NVarChar))
		.Parameters(1).SourceColumn = "CompanyName"
		.Parameters.Add(New SqlParameter( _
			"@contact", SqlDbType.NVarChar))
		.Parameters(2).SourceColumn = "ContactName"
		.Parameters.Add(New SqlParameter("@city", _
			SqlDbType.NVarChar))
		.Parameters(3).SourceColumn = "City"
		.CommandType = CommandType.StoredProcedure
		Dim retParm As New SqlParameter( _
			"RETURN", SqlDbType.Int)
		retParm.Direction = _
			ParameterDirection.ReturnValue
		retParm.SourceColumn = "SupplierID"
		.Parameters.Add(retParm)
	End With
	' Configure the Delete command
	cmDel.Parameters.Add(New _
		SqlParameter("@supplierID", SqlDbType.Int))
	cmDel.Parameters(0).SourceColumn = _
		"SupplierID"
	cmDel.CommandType = _
		CommandType.StoredProcedure
	' Set up the commands
	With da
		.InsertCommand = cmSave
		.UpdateCommand = cmSave
		.DeleteCommand = cmDel
	End With
	Try
		da.Update(suppliersData)
	Catch e As SqlException
		' An error occurred 
		Dim ds As New DataSet()
ds.Merge(suppliersData.Tables(0).GetChanges())
		Return ds
	End Try
End Function
