' A base data class that makes it much easier for derived classes to run a ' stored procedure to retrieve data or execute a SQL command. (See usage ' examples at the bottom)Imports SystemImports System.DataImports System.Data.SqlClient' DbObject is the class from which all classes in the Data Services Tier' inherit. The core functionality of establishing a connection with the' database and executing simple stored procedures is also provided by' this base class.' ---Public MustInherit Class DbObject ' Private Variables '- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Protected myConnection As SqlConnection Private myConnectionString As String ' Constructors '- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ' A parameterized constructor, it allows us to take a connection string ' as a constructor argument, automatically instantiating a new connection ' --- Public Sub New(ByVal newConnectionString As String) myConnectionString = newConnectionString myConnection = New SqlConnection(myConnectionString) End Sub ' Methods '- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ' Runs a stored procedure, can only be called by those classes deriving ' from this base. It returns an integer indicating the return value of the ' stored procedure, and also returns the value of the RowsAffected aspect ' of the stored procedure that is returned by the ExecuteNonQuery method. Protected Overloads Function RunProcedure(ByVal storedProcName As String, _ ByVal parameters As IDataParameter(), ByRef rowsAffected As Integer) As _ Integer Dim result As Integer myConnection.Open() Dim command As SqlCommand = BuildIntCommand(storedProcName, parameters) rowsAffected = command.ExecuteNonQuery() result = CInt(command.Parameters("ReturnValue").Value) myConnection.Close() Return result End Function ' Will run a stored procedure, can only be called by those classes ' deriving from this base. It returns a SqlDataReader containing the ' result of the stored procedure. Protected Overloads Function RunProcedure(ByVal storedProcName As String, _ ByVal parameters As IDataParameter()) As SqlDataReader Dim returnReader As SqlDataReader myConnection.Open() Dim command As SqlCommand = BuildQueryCommand(storedProcName, _ parameters) command.CommandType = CommandType.StoredProcedure returnReader = command.ExecuteReader(CommandBehavior.CloseConnection) ' Connection will be closed automatically Return returnReader End Function ' Creates a DataSet by running the stored procedure and placing ' the results of the query/proc into the given tablename. Protected Overloads Function RunProcedure(ByVal storedProcName As String, _ ByVal parameters As IDataParameter(), ByVal tableName As String) As _ DataSet Dim dataSet As New DataSet() myConnection.Open() Dim sqlDA As New SqlDataAdapter() sqlDA.SelectCommand = BuildQueryCommand(storedProcName, parameters) sqlDA.Fill(dataSet, tableName) myConnection.Close() Return dataSet End Function ' Takes an -existing- dataset and fills the given table name ' with the results of the stored procedure. Protected Overloads Sub RunProcedure(ByVal storedProcName As String, _ ByVal parameters As IDataParameter(), ByVal dataSet As DataSet, _ ByVal tableName As String) myConnection.Open() Dim sqlDA As New SqlDataAdapter() sqlDA.SelectCommand = BuildIntCommand(storedProcName, parameters) sqlDA.Fill(dataSet, tableName) myConnection.Close() End Sub ' Properties '- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ' Protected property that exposes the connection string to inheriting ' classes. Read-Only. Protected ReadOnly Property ConnectionString() As String Get Return myConnectionString End Get End Property ' Private Code '- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ' Private routine allowed only by this base class, it automates the task ' of building a SqlCommand object designed to obtain a return value from ' the stored procedure. Private Function BuildIntCommand(ByVal storedProcName As String, _ ByVal parameters As IDataParameter()) As SqlCommand Dim command As SqlCommand = BuildQueryCommand(storedProcName, _ parameters) Dim parameter As New SqlParameter() With parameter .ParameterName = "ReturnValue" .DbType = SqlDbType.Int .Size = 4 .Direction = ParameterDirection.ReturnValue .IsNullable = False .Precision = 0 .Scale = 0 .SourceColumn = String.Empty .SourceVersion = DataRowVersion.Default .Value = Nothing End With command.Parameters.Add(parameter) Return command End Function ' Builds a SqlCommand designed to return a SqlDataReader, ' and not an actual integer value. Private Function BuildQueryCommand(ByVal storedProcName As String, _ ByVal parameters As IDataParameter()) As SqlCommand Dim command As New SqlCommand(storedProcName, myConnection) command.CommandType = CommandType.StoredProcedure Dim parameter As SqlParameter For Each parameter In parameters command.Parameters.Add(parameter) Next Return command End FunctionEnd Class' *** USAGE ***Public Class Categories Inherits Wrox.WebModules.Data.DbObject Public Sub New(ByVal newConnectionString As String) MyBase.New(newConnectionString) End Sub ' Return all the Categories Public Function GetCategories() As DataSet Return RunProcedure("sp_Forums_GetCategories", New IDataParameter() {}, _ "Categories") End Function ' Add a new record Public Function Add(ByVal categoryName As String, ByVal categoryImageUrl As _ String, ByVal categoryPosition As Integer) As Integer Dim rowsAffected As Integer ' Create the parameters Dim parameters As SqlParameter() = { New SqlParameter("@CategoryName", _ SqlDbType.VarChar, 100), New SqlParameter("@CategoryImageUrl", _ SqlDbType.VarChar, 100), New SqlParameter("@CategoryPosition", _ SqlDbType.Int, 4), New SqlParameter("@CategoryID", SqlDbType.Int, _ 4)} ' Set the values parameters(0).Value = categoryName.Trim() parameters(1).Value = categoryImageUrl.Trim() parameters(2).Value = categoryPosition parameters(3).Direction = ParameterDirection.Output ' Run the procedure RunProcedure("sp_Forums_InsertCategory", parameters, rowsAffected) Return CInt(parameters(3).Value) End FunctionEnd Class' This code is taken from Marco Bellinaso's and Kevin Hoffman's "ASP.NET ' Website Programming - VB.NET edition" (Wrox Press). You can read two entire ' sample chapters of the C# edition from our Book Bank:' Chapter 4: Mantaining the site: http://www.vb2themax.com/Htmldoc.asp?File=/' Books/AspnetWebsite/AspNetWebSite_04.htm' Chapter 11: Deploying the Site: http://www.vb2themax.com/HtmlDoc.asp?Table=' Books&ID=7800


The Best Mechanical Keyboards For Programmers: Where To Find Them
When it comes to programming, a good mechanical keyboard can make all the difference. Naturally, you would want one of the best mechanical keyboards for programmers. But with so many