One Data Access Layer: (vb.net, w/explanation)
Author: Terry Voss
Assistant: Minglong Wu, reviewer/critic/tester

x
I will have a detailed code example that I am currently using for SQL Server 2000 that you may want to use or easily modify for Oracle or any provider like OleDb, available, but I will be focusing mostly on the different ways of going about keeping this process simple and maintainable and easy to use from different types of classes and why I made the choices I have. The main goal is making a DAL that anyone can maintain.
 
From the requirements list, the DataProvider variation will be handled by different subclasses of one AbstractDataProvider class. You copy my SqlProvider class in sqlprovider.vb to a file called oracleprovider.vb and edit away for an hour at most and you have your new concrete data provider subclass.  To handle CommandType variation we will use a DALRequest class with default settings to our favorite. I will be calling the output variation consumers to contrast with the usage of provider. To handle the variation of consumers, I will vary the input signature of overloaded method called Execute of the SqlProvider subclass. To handle the variation of Commands I will overload Execute and vary the code from overload to overload very slightly so it is easy to understand and change.
 

Below is diagram of Class Design showing all the main files in this download, with client-tier on bottom, business-tier as customer, and rest is DAL.

Now if I could just get a generator to compile so that the classes are ready to use this DAL, that would be a great savings of coding time, (maybe a next article?).
 
Let's look at each class individually to see what we can learn about the whole simple process starting with the front end. I am not delivering any Stored Procedures with this because you can test all of this with just a few tweaks to Northwind's SPs or place some text commands very easily with this once you understand the structure. It might look complex, but once you see the design things fall easily into place. First in dal.aspx there is a datagrid and a textbox and 3 buttons: getorders, delete one record, and insert a customer.
 
Looking at the code for these button in the codebehind: dal.aspx.vb,,, we see that we are merely instantiating a class from the middle tier and calling one of its methods. This is all I allow in this tier's responses to events. Never any data provider specific code. Some may say that a datareader is specific, or dataset, but they may be made as abstract as you want with wrapper classes as we will see was necessary with the datareader class. You will see that datareader is not the sqldatareader, but a custom wrapper class. I feel that a dataset class is not specific to sql or oracle or oledb, so I did not choose to make a wrapper as I could have for the dataset or datatable. The main thing to notice here is that if I want to receive back a datareader for datagrid binding, then I simply input to the middle-tier method, an new and empty datareader object. I could here, input only a type of datareader and and an instantiation of nothing and get my DAL to work fine and have been lighter weight in what I passed, but inputing the non null instantiation that is empty saves me code in my sqlProvider class as you will see so I decided on this course.  If I want to receive a dataset back, I simply input a new dataset object, etc  This works for any custom class that you want to define also which is very easy to do, but this only works for consumers that you have handled in the DAL. So, it is a 1 or 2 word change of code to get any type of consumer object back for binding or whatever use.

Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic.ControlChars

Public Enum ssenumSqlDataTypes 

 Public Class Parameter

 Public Class SQLProvider : Inherits AbstractProvider

 Public Class Parameter
  Public DataType As SqlDbType '//--- The datatype of the parameter 
  Public Direction As ParameterDirection '//--- The direction of the parameter 
  Public ParameterName As String '//--- The Name of the parameter 
  Public Size As Integer '//--- The size in bytes of the parameter 
  Public Value As String '//--- The value of the parameter 
  Sub New(ByVal sParameterName As String, ByVal lDataType As SqlDbType, ByVal iSize As Integer, _
    ByVal sValue As String, ByVal iDirection As Integer)
    ParameterName = sParameterName
    DataType = lDataType
    Size = iSize
    Value = sValue
    Direction = iDirection
  End Sub
End Class

Private m_oParmList As ArrayList = New ArrayList() ' holds parameters for a stored procedure

Public Overloads Overrides Sub ClearAllParameters()
  m_oParmList.Clear()
End Sub

Public Overloads Overrides Sub AddParameter(ByVal sParameterName As String, _
  ByVal lSqlType As ssenumSqlDataTypes, ByVal iSize As Integer, ByVal sValue As String, ByVal iDirection As Integer)
  Dim eDataType As SqlDbType
  Dim oParam As Parameter = Nothing
  Select Case lSqlType
  Case ssenumSqlDataTypes.ssSDT_String
    eDataType = SqlDbType.VarChar
  Case ssenumSqlDataTypes.ssSDT_Integer
    eDataType = SqlDbType.Int
  Case ssenumSqlDataTypes.ssSDT_DateTime
    eDataType = SqlDbType.DateTime
  Case ssenumSqlDataTypes.ssSDT_Bit
    eDataType = SqlDbType.Bit
  Case ssenumSqlDataTypes.ssSDT_Decimal
    eDataType = SqlDbType.Decimal
  Case ssenumSqlDataTypes.ssSDT_Money
    eDataType = SqlDbType.Money
  End Select
  oParam = New Parameter(sParameterName, eDataType, iSize, sValue, iDirection)
  m_oParmList.Add(oParam)
End Sub

Private Function ConvertParameterToSqlParameter(ByVal oP As Parameter) As SqlParameter
  Dim oSqlParameter As SqlParameter = New SqlParameter(oP.ParameterName, oP.DataType, oP.Size)
  With oSqlParameter
    .Value = oP.Value
    .Direction = oP.Direction
  End With
  Return oSqlParameter
End Function

Imports System.IO
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic.ControlChars

Public Enum ssenumSqlDataTypes ssSDT_Bit
ssSDT_DateTime
ssSDT_Decimal
ssSDT_Integer
ssSDT_Money
ssSDT_String
End Enum  Public Class Parameter

 Public Class SQLProvider : Inherits AbstractProvider

Public Shadows Direction As New Direction()
Public Shadows Connection As New Connection()
Private connectionString As String
Private m_oParmList As ArrayList = New ArrayList() ' holds parameters for a stored procedure

Sub New()
connectionString = XmlSetting.Read("appsettings", DalRequest.Role)
Me.Connection.ConSql = New SqlConnection(connectionString)
End Sub

Public Overloads Overrides Sub Execute() ' handles update and delete commands which return nothing here

Public Overloads Overrides Function Execute(ByVal consumer As Integer) As Integer

Public Overloads Overrides Function Execute(ByVal consumer As DataReader) As DataReader

Public Overloads Overrides Function Execute(ByVal consumer As DataSet) As DataSet

Public Overloads Overrides Function Execute(ByVal consumer As DataTable) As DataTable

Public Overloads Overrides Function Execute(ByVal consumer As DataRow) As DataRow

Public Overloads Overrides Function Execute(ByVal consumer As Order) As Order

Public Sub LogError(ByVal e As SqlException, ByVal Command As String)

Public Overloads Overrides Sub ClearAllParameters()

Public Overloads Overrides Sub AddParameter(ByVal sParameterName As String, ByVal lSqlType As ssenumSqlDataTypes, _
  ByVal iSize As Integer, ByVal sValue As String, ByVal iDirection As Integer)

Private Function ConvertParameterToSqlParameter(ByVal oP As Parameter) As SqlParameter

End Class

Public Overloads Overrides Function Execute(ByVal consumer As DataSet) As DataSet
  Dim oCmd As SqlCommand = New SqlCommand()
  Dim oEnumerator As IEnumerator = m_oParmList.GetEnumerator()
  Try
    Me.Connection.ConSql.Open()
    With oCmd
      .Connection = Me.Connection.ConSql
      .CommandText = DalRequest.Command
      .CommandType = DalRequest.CommandType
    End With
    If DalRequest.CommandType = CommandType.StoredProcedure Then
      Do While (oEnumerator.MoveNext())
        Dim oP As Parameter = oEnumerator.Current
        oCmd.Parameters.Add(ConvertParameterToSqlParameter(oP))
      Loop
    End If
    With New SqlDataAdapter()
      .SelectCommand = oCmd
      .Fill(consumer)
    End With
    Catch e As SqlException
      Me.LogError(e, oCmd.CommandText)
      Throw e
    Finally
      Me.Connection.ConSql.Close()
    End Try
    Return consumer
End Function

Public Overloads Overrides Function Execute(ByVal consumer As Order) As Order
  Dim oCmd As SqlCommand = New SqlCommand()
  Dim oEnumerator As IEnumerator = m_oParmList.GetEnumerator()
  Dim dt As DataTable
  Try
    Me.Connection.ConSql.Open()
    With oCmd
      .Connection = Me.Connection.ConSql
      .CommandText = DalRequest.Command
      .CommandType = DalRequest.CommandType
    End With
    If DalRequest.CommandType = CommandType.StoredProcedure Then
      Do While (oEnumerator.MoveNext())
        Dim oP As Parameter = oEnumerator.Current
        oCmd.Parameters.Add(ConvertParameterToSqlParameter(oP))
      Loop
    End If
    With New SqlDataAdapter()
      .SelectCommand = oCmd
      .Fill(dt)
    End With
    Catch e As SqlException
      Me.LogError(e, oCmd.CommandText)
      Throw e
    Finally
      Me.Connection.ConSql.Close()
    End Try
  consumer.OrderDate = dt.Rows(0)("OrderDate")
  Return consumer
End Function

 











c:\inetpub\wwwroot\dal\



c:\inetpub\wwwroot\dal\



Data Source=vsnet\dev1;Initial Catalog=Northwind;User ID=ext;Password=earth



Data Source=vsnet\dev1;Initial Catalog=Northwind;User ID=int;Password=wind



Data Source=vsnet\dev1;Initial Catalog=Northwind;User ID=su;Password=and



Data Source=vsnet\dev1;Initial Catalog=Northwind;User ID=admin;Password=fire







 

dim connectionString as string = xmlsetting.read("appsettings", "internal")

xmlsetting.write("appsetting", "Internal", "safe")