Imports System.Data.SqlClient Imports System.Xml Imports System.Configuration.ConfigurationSettings ' The SqlHelper class is intended to encapsulate high performance, scalable best practices for ' common uses of SqlClient. Public NotInheritable Class DbAccess Implements IDisposable Private conn As SqlConnection '######################################################################################################################### ' FRIEND METHODS '######################################################################################################################### ' Execute a SqlCommand (that returns a resultset) against the specified SqlConnection ' using the provided parameters. ' e.g.: ' Dim ds as Dataset = ExecuteDataset(CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)) ' Parameters: ' -commandText - the stored procedure name or T-SQL command ' -commandParameters - a collection of SqlParamters used to execute the command ' Returns: a dataset containing the resultset generated by the command Friend Overloads Function ExecuteDataset(ByVal commandText As String, ByVal commandParameters As Collection, ByVal TableName As String) As DataSet 'create a command and prepare it for execution Dim cmd As New SqlCommand() Dim ds As New DataSet() Dim da As SqlDataAdapter PrepareCommand(cmd, CType(Nothing, SqlTransaction), CommandType.StoredProcedure, commandText, commandParameters) 'create the DataAdapter & DataSet da = New SqlDataAdapter(cmd) 'fill the DataSet using default values for DataTable names, etc. da.Fill(ds, TableName) 'detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear() 'return the dataset Return ds End Function 'ExecuteDataset ' Execute a SqlCommand that has no return values. Used for create/update/delete functions. ' Parameters: ' -procName - the name of the stored procedure ' -params - a collection of SqlParamters used to execute the command Friend Sub ExecuteProc(ByVal procName As String, ByVal params As Collection) Dim cmd As New SqlCommand() PrepareCommand(cmd, CType(Nothing, SqlTransaction), CommandType.StoredProcedure, procName, params) cmd.ExecuteNonQuery() Me.Close() End Sub ' Creates an Sql parameter object Friend Function MakeParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal Size As Int32, ByVal Value As Object) As SqlParameter Dim param As SqlParameter If Size > 0 Then param = New SqlParameter(ParamName, DbType, Size) Else param = New SqlParameter(ParamName, DbType) End If param.Direction = ParameterDirection.Input If TypeOf Value Is DateTime Then If (Not Value Is Nothing AndAlso Convert.ToDateTime(Value).Year <> 1) Then param.Value = Value End If ElseIf TypeOf Value Is Int32 Then If CType(Value, Int32) <> 0 Then param.Value = Value End If Else If (Not Value Is Nothing AndAlso Value.ToString().Length > 0) Then param.Value = Value End If End If Return param End Function '######################################################################################################################### ' PRIVATE METHODS '######################################################################################################################### ' This method is used to attach array of SqlParameters to a SqlCommand. ' This method will assign a value of DbNull to any parameter with a direction of ' InputOutput and a value of null. ' This behavior will prevent default values from being used, but ' this will be the less common case than an intended pure output parameter (derived as InputOutput) ' where the user provided no input value. ' Parameters: ' -command - The command to which the parameters will be added ' -commandParameters - an array of SqlParameters tho be added to command Private Sub AttachParameters(ByVal command As SqlCommand, ByVal commandParameters As Collection) Dim p As SqlParameter For Each p In commandParameters 'check for derived output value with no value assigned If p.Direction = ParameterDirection.InputOutput And p.Value Is Nothing Then p.Value = Nothing End If command.Parameters.Add(p) Next p End Sub 'AttachParameters ' This method opens (if necessary) and assigns a connection, transaction, command type and parameters ' to the provided command. ' Parameters: ' -command - the SqlCommand to be prepared ' -transaction - a valid SqlTransaction, or 'null' ' -commandType - the CommandType (stored procedure, text, etc.) ' -commandText - the stored procedure name or T-SQL command ' -commandParameters - an array of SqlParameters to be associated with the command or 'null' if no parameters are required Private Sub PrepareCommand(ByVal command As SqlCommand, _ ByVal transaction As SqlTransaction, _ ByVal commandType As CommandType, _ ByVal commandText As String, _ ByVal commandParameters As Collection) 'if the provided connection is not open, we will open it Me.Open() 'associate the connection with the command command.Connection = conn 'set the command text (stored procedure name or SQL statement) command.CommandText = commandText 'if we were provided a transaction, assign it. If Not (transaction Is Nothing) Then command.Transaction = transaction End If 'set the command type command.CommandType = commandType 'attach the command parameters if they are provided If Not (commandParameters Is Nothing) Then AttachParameters(command, commandParameters) End If Return End Sub 'PrepareCommand Private Sub Open() ' Open the connection If conn Is Nothing Then conn = New SqlConnection(AppSettings("DatabaseConnString")) conn.Open() End If End Sub Friend Sub Close() ' Close the connection and cleanup the class with the Dispose() method Me.Dispose() End Sub Public Sub Dispose() Implements IDisposable.Dispose If Not conn Is Nothing Then conn.Close() conn.Dispose() conn = Nothing End If GC.SuppressFinalize(Me) End Sub End Class | 