Building a Data Access Framework (DAF)

Introduction

Imagine you need to architect a multitier, distributed .NET-based app with three logical layers—presentation and interface services, business logic with core functionalities, and data access, where all database stuff and messaging happens. For this type of application, layering is the key. If the lowest layer could be completely ignorant of the upper layers, the system would be nearly perfect.

When designing a layered system you must consider factors such as the ability to cascade changes (like new data schemas) through layers and the amount of overhead involved when data is moved from one layer to the next. Plus you need a business tier to execute your business logic, and you need a Data Access Framework (DAF) to provide Create/Read/Update/Delete (CRUD) functions to the rest of the system.

There are two primary ways to provide this functionality—by using a commercial Object-Relational Mapping (O-RM) tool or by rolling your own DAF. This post shows you the best way to go that is create your own.

DAF Design Issues

Normalized relational databases and objects generally don’t have one-to-one matchups between them. This means that someone has to write code that transmogrifies normalized rows and columns into business objects. This code is often called the Data Access Layer (DAL), which poses three challenges to the developer.

The first challenge of a DAL is that a relational database defines four DB operations: insert, update, delete, and select. In the object-oriented programming (OOP) world, the first thing developers can do to deal with these four behaviors is simplify them to two OOP behaviors: read and write. A select is the read, and update, insert, and delete are all consolidated into the write.

The second thing developers need to do is realize that business objects don’t need—and shouldn’t have—internal read and write baggage. Why? Because every business object would need redundant code for reading and writing. This code can be and should be factored out just as it’s done in DataSets and DataTables. Factoring out read/write shrinks the overall size of the code base.

Finally, the only real differences between any read and write are the SQL that performs the action, the inputs and outputs, and how these inputs and outputs are handled. Everything else about reading and writing is a connection, transaction, command, or reader, and the initiator—the behavior that sends the request to your database server. This means developers can factor out all of the latter behaviors and reduce the number of times they need to write the connect, transact, and command behavior.

Collectively, these three elements—consolidate behaviors to read and write, factor out persistence from business objects, and separate basic database operations from object-specific details—make up the collective data access solution this article provides.

 DAF Design Objectives

  • Reusable from one entity to the other and across different presentation layers.
  • Use Object-Oriented Features and adhere to design guidelines.
  • Be able to employ the best method(s) of accessing the database such as having a good stored procedure layer set up for CRUD (Create, Retrieve, Update, Delete) operations and using stored procedures makes a very clear delineation between areas of responsibility.
  • Abstract the .NET Framework Data Providers.
  • Be in control of all the details of your application.
  • Be compatible to the traditional multitier or n-tier application development approach.
  • Applications are freed from hard-coded dependencies on a particular data engine or logical model.
  • Mappings between the conceptual model and the storage-specific logical model can change without changing the application code.
  • Independent of the underlying database; Developers can work with a consistent application object model that can be mapped to various storage schemas, possibly implemented in different database management systems.
  • Multiple application models can be mapped to a single storage schema.

 Key Objects in the DAF

The DAF logically groups objects into those that can be implemented/inherited by any data provider objects and those objects that are data provider specific.

Common to all data provider objects found in the DAF.Common namespace are as follows:-

Interfaces

  • IData; has properties such as LoginID, LoginPassword, DatabaseName, UserID, DBPassword, Parameters, ReadData and SplitChar that are to be defined by any class that implements it.
  • IDbData; Inherits IDisposable and has methods such as Save, SaveData, Update, UpdateData, Delete, DeleteData, Load, Read, Execute and SetCommand.

 Classes

  • Data; implements IData Interface.
  • Parameter; defines two properties, Name and Value for the parameter array.

 Here are Provider Specific objects found in DAF.SQLDb namespace that inherit/implement the above interfaces. This post looks at SQL Server whose objects include:-

Interface

  • ISQLData; has properties such as ConString, Connection, Command, DataReader, ServerName and methods that include SaveCommand, UpdateCommand, DeleteCommand and SetConString.

 Enumeration

  • SQLConnectionMode; that helps to set connection mode to WindowsAuthentication or SQLServerAuthentication.

Classes

  • ParameterSQL; Inherits Parameter class and adds properties such as SQLDataType, ParDirection and Length.
  • DBConnect; Perhaps this is the most important class that is inherited by any object that accesses the database. It Inherits the Data class, Implements ISQLData Interface and defines a couple of properties such as ConString, Connection, Command, DataReader, ServerName and methods that include SetConString, Save, SaveData, SaveCommand, Update, UpdateData, Delete, DeleteData, DeleteCommand, SetCommand, Load, Execute, Read and Dispose.

Public MustInherit Class DBConnect : Inherits Data : Implements ISQLData

 

#Region ” Fields “

 

        Private Shared m_ConString As String

        Private _connection As New SqlConnection()

        Private _command As SqlCommand

        Private _dataReader As SqlDataReader

        Private Shared m_ServerName As String

 

#End Region

 

#Region ” Properties “

 

        Public Property ConString() As String Implements ISQLData.ConString

            Get

                Return m_ConString

            End Get

            Set(ByVal Value As String)

                m_ConString = Value

            End Set

        End Property

 

        Protected Property Connection() As SqlConnection Implements ISQLData.Connection

            Get

                Return _connection

            End Get

            Set(ByVal Value As SqlConnection)

                _connection = Value

            End Set

        End Property

 

        Protected Property Command() As SqlCommand Implements ISQLData.Command

            Get

                Return _command

            End Get

            Set(ByVal Value As SqlCommand)

                _command = Value

            End Set

        End Property

 

        Protected Property DataReader() As SqlDataReader Implements ISQLData.DataReader

            Get

                Return _dataReader

            End Get

            Set(ByVal Value As SqlDataReader)

                _dataReader = Value

            End Set

        End Property

 

        Public Property ServerName() As String Implements ISQLData.ServerName

            Get

                Return m_ServerName

            End Get

            Set(ByVal Value As String)

                m_ServerName = Value

            End Set

        End Property

 

#End Region

 

#Region ” Methods “

 

        ”’ <summary>

        ”’ Sets the MS SQL connection mode and connection string

        ”’ </summary>

        ”’ <param name=”_SQLConnectionMode”></param>

        ”’ <param name=”_ConString”></param>

        ”’ <remarks></remarks>

        Public Overloads Sub SetConString(ByVal _SQLConnectionMode As SQLConnectionMode, ByVal _ConString As String) _

                                Implements ISQLData.SetConString

 

            Dim conBuilder As New SqlConnectionStringBuilder()

 

            If String.IsNullOrEmpty(_ConString) Then

 

                Select Case True

 

                    Case _SQLConnectionMode = SQLConnectionMode.WindowsAuthentication

 

                        conBuilder.Clear()

                        conBuilder(“Server”) = Me.ServerName

                        conBuilder.InitialCatalog = Me.DatabaseName

                        conBuilder(“Integrated Security”) = “SSPI”

                        conBuilder.PersistSecurityInfo = False

                        ‘conBuilder.PacketSize = 4096

 

                        Me.ConString = conBuilder.ConnectionString()

 

                    Case _SQLConnectionMode = SQLConnectionMode.SQLServerAuthentication

 

                        With conBuilder

                            .Clear()

                            .DataSource = Me.ServerName

                            .InitialCatalog = Me.DatabaseName

                            .PersistSecurityInfo = True

                            .UserID = Me.UserID

                            .Password = Me.DBPassword

                        End With

 

                        Me.ConString = conBuilder.ConnectionString()

 

                End Select

 

            Else : Me.ConString = _ConString

 

            End If

 

        End Sub

 

        ”’ <summary>

        ”’ Sets the MS SQL connection mode

        ”’ </summary>

        ”’ <param name=”_SQLConnectionMode”></param>

        ”’ <remarks></remarks>

        Public Overloads Sub SetConString(ByVal _SQLConnectionMode As SQLConnectionMode) Implements ISQLData.SetConString

            Me.SetConString(_SQLConnectionMode, String.Empty)

        End Sub

 

        ”’ <summary>

        ”’ Saves the objects data and if successful true is returned otherwise false

        ”’ </summary>

        ”’ <returns></returns>

        ”’ <remarks></remarks>

        Public Overridable Function Save() As Boolean Implements ISQLData.Save

 

            Using Me.Connection

                Using Me.Command

 

                    Me.Connection.ConnectionString = Me.ConString

 

                    ‘ Clean up to ensure that you supply only the required paraments

                    Me.Parameters.Clear()

 

                    Dim arrParameters As ArrayList = SaveData()

 

                    For pos As Integer = 0 To arrParameters.Count – 1

                        With CType(arrParameters(pos), ParameterSQL)

                            Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                        End With

                    Next

 

                    Try

 

                        Me.Connection.Open()

                        Return Me.Command.ExecuteNonQuery > 0

 

                    Catch eX As Exception

                        Throw eX

 

                    Finally

                        If Me.Parameters IsNot Nothing Then Me.Parameters.Clear()

                        Me.Connection.Close()

                    End Try

 

                End Using

            End Using

 

        End Function

 

        Protected Overridable Function SaveData() As ArrayList Implements ISQLData.SaveData

            Return Nothing

        End Function

 

        Friend Overridable Function SaveCommand() As SqlCommand Implements ISQLData.SaveCommand

 

            ‘ Clean up to ensure that you supply only the required paraments

            Me.Parameters.Clear()

 

            Dim arrParameters As ArrayList = SaveData()

 

            For pos As Integer = 0 To arrParameters.Count – 1

                With CType(arrParameters(pos), ParameterSQL)

                    Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                End With

            Next

 

            If Not Me.Parameters Is Nothing Then Me.Parameters.Clear()

            Return Me.Command

 

        End Function

 

        ”’ <summary>

        ”’ Updates the object’s data and returns message telling the number of records updated

        ”’ </summary>

        ”’ <returns></returns>

        ”’ <remarks></remarks>

        Public Overridable Overloads Function Update() As String Implements ISQLData.Update

 

            Using Me.Connection

                Using Me.Command

 

                    Me.Connection.ConnectionString = Me.ConString

 

                    ‘ Clean up to ensure that you supply only the required paraments

                    Me.Parameters.Clear()

                    Dim arrParameters As ArrayList = UpdateData()

 

                    For pos As Integer = 0 To arrParameters.Count – 1

                        With CType(arrParameters(pos), ParameterSQL)

                            Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                        End With

                    Next

 

                    Try

 

                        Me.Connection.Open()

 

                        Dim updateMessage As String = Me.Command.ExecuteNonQuery().ToString()

                        Return “Record successfully updated!”

 

                    Catch eX As SqlException

                        Throw eX

 

                    Catch eX As Exception

                        Throw eX

 

                    Finally

                        If Me.Parameters IsNot Nothing Then Me.Parameters.Clear()

                        Me.Connection.Close()

                    End Try

 

                End Using

            End Using

 

        End Function

 

        Protected Overridable Function UpdateData() As ArrayList Implements ISQLData.UpdateData

            Return Nothing

        End Function

 

        Friend Overridable Function UpdateCommand() As SqlCommand Implements ISQLData.UpdateCommand

 

            ‘ Clean up to ensure that you supply only the required paraments

            Me.Parameters.Clear()

            Dim arrParameters As ArrayList = UpdateData()

 

            For pos As Integer = 0 To arrParameters.Count – 1

                With CType(arrParameters(pos), ParameterSQL)

                    Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                End With

            Next

 

            If Not Me.Parameters Is Nothing Then Me.Parameters.Clear()

            Return Me.Command

 

        End Function

 

        ”’ <summary>

        ”’ Deletes Object data and returns a message telling you the number of records deleted

        ”’ </summary>

        ”’ <returns></returns>

        ”’ <remarks></remarks>

        Public Overridable Overloads Function Delete() As String Implements ISQLData.Delete

 

            Using Me.Connection

                Using Me.Command

 

                    Me.Connection.ConnectionString = Me.ConString

 

                    ‘ Clean up to ensure that you supply only the required paraments

                    Me.Parameters.Clear()

                    Dim arrParameters As ArrayList = DeleteData()

 

                    For pos As Integer = 0 To arrParameters.Count – 1

                        With CType(arrParameters(pos), ParameterSQL)

                            Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                        End With

                    Next

 

                    Try

                        Me.Connection.Open()

 

                        Dim deleteMessage As String = Me.Command.ExecuteNonQuery().ToString()

                        Return “Record successfully deleted!”

 

                    Catch eX As SqlException

                        Throw eX

 

                    Catch eX As Exception

                        Throw eX

 

                    Finally

                        If Me.Parameters IsNot Nothing Then Me.Parameters.Clear()

                        Me.Connection.Close()

                    End Try

 

                End Using

            End Using

 

        End Function

 

        Protected Overridable Function DeleteData() As ArrayList Implements ISQLData.DeleteData

            Return Nothing

        End Function

 

        Friend Overridable Function DeleteCommand() As SqlCommand Implements ISQLData.DeleteCommand

 

            ‘ Clean up to ensure that you supply only the required paraments

            Me.Parameters.Clear()

            Dim arrParameters As ArrayList = DeleteData()

 

            For intPos As Integer = 0 To arrParameters.Count – 1

                With CType(arrParameters(intPos), ParameterSQL)

                    Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                End With

            Next

 

            If Not Me.Parameters Is Nothing Then Me.Parameters.Clear()

 

            Return Me.Command

 

        End Function

 

        Protected Overloads Sub SetCommand(ByVal _SPName As String, ByVal ctName As CommandType) Implements ISQLData.SetCommand

            Me.Command = New SqlCommand(_SPName, Me.Connection)

            Me.Command.CommandType = ctName

        End Sub

 

        Protected Overloads Sub SetCommand(ByVal _SPName As String) Implements ISQLData.SetCommand

            Me.Command = New SqlCommand(_SPName, Me.Connection)

            Me.Command.CommandType = CommandType.StoredProcedure

        End Sub

 

        ”’ <summary>

        ”’ Returns data from the database as a dataset

        ”’ </summary>

        ”’ <param name=”_SPName”></param>

        ”’ <param name=”_tables”></param>

        ”’ <param name=”_Parameters”></param>

        ”’ <param name=”ctName”></param>

        ”’ <param name=”commTimeout”></param>

        ”’ <returns></returns>

        ”’ <remarks></remarks>

        Protected Overloads Function Load(ByVal _SPName As String, ByVal _tables As String, ByVal _Parameters As ArrayList, _

                                ByVal ctName As CommandType, ByVal commTimeout As Integer) As DataSet Implements ISQLData.Load

 

            Using Me.Connection

 

                Me.Connection.ConnectionString = MyClass.ConString

 

                Dim tables() As String = _tables.Split(Me.SplitChar)

                Dim tableName As String = “Table”

 

                Using daSQL As SqlDataAdapter = New SqlDataAdapter(_SPName, Me.Connection)

                    Using dsSQL As DataSet = New DataSet(tables(tables.GetLowerBound(0)))

 

                        daSQL.SelectCommand.CommandType = ctName

                        daSQL.SelectCommand.CommandTimeout = commTimeout

                        If IsNothing(_Parameters) = False Then

                            For pos As Integer = 0 To _Parameters.Count – 1

                                With CType(_Parameters(pos), ParameterSQL)

                                    daSQL.SelectCommand.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                                End With

                            Next

                        End If

 

                        Try

 

                            dsSQL.EnforceConstraints = False

 

                            With daSQL

 

                                .MissingMappingAction = MissingMappingAction.Passthrough

                                .MissingSchemaAction = MissingSchemaAction.AddWithKey

 

                                For pos As Integer = tables.GetLowerBound(0) To tables.GetUpperBound(0)

                                    If pos = 0 Then

                                        .TableMappings.Add(tableName, tables(pos).Trim())

                                    Else : .TableMappings.Add(tableName & pos, tables(pos).Trim())

                                    End If

                                Next

 

                                .Fill(dsSQL)

 

                            End With

 

                            dsSQL.EnforceConstraints = True

 

                            Return dsSQL

 

                        Catch eX As SqlException

                            Throw eX

 

                        Catch eX As Exception

                            Throw eX

 

                        Finally

                            If Me.Parameters IsNot Nothing Then Me.Parameters.Clear()

                            Me.Connection.Close()

 

                        End Try

 

                    End Using

                End Using

            End Using

 

        End Function

 

        Protected Overloads Function Load(ByVal _SPName As String, ByVal _tables As String, ByVal _Parameters As ArrayList, _

                               ByVal ctName As CommandType) As DataSet Implements ISQLData.Load

            Return Me.Load(_SPName, _tables, _Parameters, ctName, 0)

        End Function

 

        Protected Overloads Function Load(ByVal _SPName As String, ByVal _tables As String, ByVal _Parameters As ArrayList) As DataSet Implements ISQLData.Load

            Return Me.Load(_SPName, _tables, _Parameters, CommandType.StoredProcedure, 0)

        End Function

 

        Protected Overloads Function Load(ByVal _SPName As String, ByVal _tables As String) As DataSet Implements ISQLData.Load

            Return Me.Load(_SPName, _tables, Nothing, CommandType.StoredProcedure, 0)

        End Function

 

        ”’ <summary>

        ”’ calling this helps to execute DB scripts returning no data such as abackup script

        ”’ </summary>

        ”’ <param name=”_SPName”></param>

        ”’ <param name=”_Parameters”></param>

        ”’ <param name=”ctName”></param>

        ”’ <param name=”commTimeout”></param>

        ”’ <remarks></remarks>

        Protected Overloads Sub Execute(ByVal _SPName As String, ByVal _Parameters As ArrayList, _

                                ByVal ctName As CommandType, ByVal commTimeout As Integer) Implements ISQLData.Execute

 

            Using Me.Connection

                Using Me.Command

 

                    Me.Connection.ConnectionString = MyClass.ConString

 

                    Me.Command = New SqlCommand(_SPName, Me.Connection)

                    Me.Command.CommandType = ctName

                    Me.Command.CommandTimeout = commTimeout

                    If IsNothing(_Parameters) = False Then

                        For pos As Integer = 0 To _Parameters.Count – 1

                            With CType(_Parameters(pos), ParameterSQL)

                                Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

                            End With

                        Next

                    End If

 

                    Try

 

                        Me.Connection.Open()

                        Me.Command.ExecuteNonQuery()

 

                    Catch eX As Exception

                        Throw eX

 

                    Finally

                        If Me.Parameters IsNot Nothing Then Me.Parameters.Clear()

                        Me.Connection.Close()

 

                    End Try

 

                End Using

            End Using

 

        End Sub

 

        Protected Overloads Sub Execute(ByVal _SPName As String, ByVal _Parameters As ArrayList, _

                         ByVal ctName As CommandType) Implements ISQLData.Execute

            Me.Execute(_SPName, _Parameters, ctName, 0)

        End Sub

 

        Protected Overloads Sub Execute(ByVal _SPName As String, ByVal _Parameters As ArrayList) Implements ISQLData.Execute

            Me.Execute(_SPName, _Parameters, CommandType.StoredProcedure, 0)

        End Sub

 

        Protected Overloads Sub Execute(ByVal _SPName As String) Implements ISQLData.Execute

            Me.Execute(_SPName, Nothing, CommandType.StoredProcedure, 0)

        End Sub

 

        ”’ <summary>

        ”’ Returns data from adata reader as ahashtable

        ”’ </summary>

        ”’ <param name=”_SPName”></param>

        ”’ <param name=”_Parameters”></param>

        ”’ <param name=”ctName”></param>

        ”’ <returns></returns>

        ”’ <remarks></remarks>

        Protected Overloads Function Read(ByVal _SPName As String, ByVal _Parameters As ArrayList, _

                                 ByVal ctName As CommandType) As Hashtable Implements ISQLData.Read

 

            Dim htReadData As New Hashtable()

 

            Using Me.Connection

                Using Me.Command

 

                    Me.Connection.ConnectionString = MyClass.ConString

 

                    Me.Command = New SqlCommand(_SPName, Me.Connection)

                    Me.Command.CommandType = ctName

 

                    If IsNothing(_Parameters) = False Then

 

                        For pos As Integer = 0 To _Parameters.Count – 1

                            With CType(_Parameters(pos), ParameterSQL)

 

                                Select Case True

                                    Case Not IsNothing(.Value)

                                        Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .Value))

 

                                    Case IsNothing(.Value) And IsNothing(.Length) OrElse IsNothing(.Value) And .Length = 0

                                        Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .SQLDataType)).Direction = .ParDirection

 

                                    Case Else

                                        Me.Command.Parameters.Add(New SqlParameter(“@” & .Name.ToString(), .SQLDataType, .Length)).Direction = .ParDirection

 

                                End Select

 

                            End With

                        Next

 

                        Try

 

                            Me.Connection.Open()

 

                            Me.Command.ExecuteNonQuery()

 

                            If IsNothing(_Parameters) = False Then

 

                                For pos As Integer = 0 To _Parameters.Count – 1

 

                                    With CType(_Parameters(pos), ParameterSQL)

 

                                        Select Case True

                                            Case .ParDirection = ParameterDirection.Output OrElse .ParDirection = ParameterDirection.InputOutput

                                                htReadData.Add(.Name.ToString(), Me.Command.Parameters(“@” & .Name.ToString()).Value)

                                        End Select

 

                                    End With

                                Next

                            End If

 

                            Return htReadData

 

                        Catch eX As SqlException

                            Throw eX

 

                        Catch eX As Exception

                            Throw eX

 

                        Finally

                            If Me.Parameters IsNot Nothing Then Me.Parameters.Clear()

                            If Me.DataReader IsNot Nothing Then Me.DataReader.Close()

                            Me.Connection.Close()

                        End Try

 

                    End If

                    Return Nothing

 

                End Using

            End Using

 

        End Function

 

        Protected Overloads Function Read(ByVal _SPName As String, ByVal _Parameters As ArrayList) As Hashtable Implements ISQLData.Read

            Return Me.Read(_SPName, _Parameters, CommandType.StoredProcedure)

        End Function

 

        Protected Overloads Function Read(ByVal _SPName As String) As Hashtable Implements ISQLData.Read

            Return Me.Read(_SPName, Nothing, CommandType.StoredProcedure)

        End Function

 

#End Region

 

#Region ” Destructor “

 

        Public Overridable Sub Dispose() Implements ISQLData.Dispose

            GC.Collect()

        End Sub

 

#End Region

 

    End Class

Using the DAF

To use the DAF, define a class such as Customers that inherits DBConnect and overrides SaveData, UpdateData, DeleteData methods. You can include several other methods that call the Load method to retrieve data as a dataset using different select criterion such as GetCustomers Note that if your SP returns multiple tables from the DB, then you need to supply comma separated datatable names to load the returned DB data, or if you just want to execute a certain script, you can just call Execute method. You can also return only one value from the DB by calling the Read method that returns a hashtable.

public class Customers : DBConnect

    {

        #region Fields

 

        private string m_CustID;

        private string m_FirstName;

        private string m_LastName;

        private DateTime m_JoinDate;

        private string m_Email;

        private decimal m_CreditLimit;

 

       #endregion

 

        #region Properties

 

        public string CustID { get { return m_CustID; } set { m_CustID = value; } }

        public string FirstName { get { return m_FirstName; } set { m_FirstName = value; } }

        public string LastName { get { return m_LastName; } set { m_LastName = value; } }

        public DateTime JoinDate { get { return m_JoinDate; } set { m_JoinDate = value; } }

        public string Email { get { return m_Email; } set { m_Email = value; } }

        public decimal CreditLimit { get { return m_CreditLimit; } set { m_CreditLimit = value; } }

 

        #endregion

 

        #region Constructors

 

        public Customers() : base() { }

 

        public Customers(string serverName, string databaseName)

            : base()

        {

            this.ServerName = serverName;

            this.DatabaseName = databaseName;

        }

 

        #endregion

 

        #region Methods

 

        protected override ArrayList SaveData()

        {

            this.SetCommand(“uspInsertCustomers”);

 

            Parameters.Add(new ParameterSQL(“CustID”, this.CustID));

            Parameters.Add(new ParameterSQL(“FirstName”, this.FirstName));

            Parameters.Add(new ParameterSQL(“LastName”, this.LastName));

            Parameters.Add(new ParameterSQL(“JoinDate”, this.JoinDate));

            Parameters.Add(new ParameterSQL(“Email”, this.Email));

            Parameters.Add(new ParameterSQL(“CreditLimit”, this.CreditLimit));

 

            return Parameters;

 

        }

 

        protected override ArrayList UpdateData()

        {

            this.SetCommand(“uspUpdateCustomers”);

 

            Parameters.Add(new ParameterSQL(“CustID”, this.CustID));

            Parameters.Add(new ParameterSQL(“FirstName”, this.FirstName));

            Parameters.Add(new ParameterSQL(“LastName”, this.LastName));

            Parameters.Add(new ParameterSQL(“JoinDate”, this.JoinDate));

            Parameters.Add(new ParameterSQL(“Email”, this.Email));

            Parameters.Add(new ParameterSQL(“CreditLimit”, this.CreditLimit));

 

            return Parameters;

 

        }

 

        protected override ArrayList DeleteData()

        {

            string where;

            string errorPart;

 

            where = “CustID = ‘” + this.CustID + “‘”;

            errorPart = “Cust No: “ + this.CustID;

            this.SetCommand(“uspDeleteObject”);

 

            Parameters.Add(new ParameterSQL(“ObjectName”, “Customers”));

            Parameters.Add(new ParameterSQL(“Where”, where));

            Parameters.Add(new ParameterSQL(“ErrorPart”, errorPart));

 

            return Parameters;

 

        }

 

 

        public DataSet GetCustomers(string custID)

        {

            Parameters.Add(new ParameterSQL(“CustID”, custID));

 

            return this.Load(“uspGetCustomers”, “Customers”, Parameters);

 

        }

 

        #endregion

    }

 Finally create a form that instantiates the Customers object and calls the respective methods,

Customers Registration Form

 Code under Save button is as follows

private void btnSave_Click(object sender, EventArgs e)

        {

            try

            {

                using (DAFSampleData.Customers oCustomers = new DAFSampleData.Customers())

                {

 

                        oCustomers.CustID = this.txtCustID.Text;

                        oCustomers.FirstName = this.txtFirstName.Text;

                        oCustomers.LastName = this.txtLastName.Text;

                        oCustomers.JoinDate = this.dtpJoinDate.Value;

                        oCustomers.Email = this.txtEmail.Text;

                        oCustomers.CreditLimit = Convert.ToDecimal(this.txtCreditLimit.Text);

 

                        ////////////////////////////////////////////////////////////////////////////////////////////////////

                        if (oCustomers.Save()) {MessageBox.Show(“Cust ID: “ + oCustomers.CustID + ” successfully saved!”);}

                        ////////////////////////////////////////////////////////////////////////////////////////////////////

 

                }

 

                //////////////////////////

                this.ResetControls();

                //////////////////////////

 

            }

 

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

 

        }

Code under Load button is as follows

private void btnLoad_Click(object sender, EventArgs e)

        {

            DataTable customers = new DataTable();

 

            try

            {

                DAFSampleData.Customers oCustomers = new DAFSampleData.Customers();

                customers = oCustomers.GetCustomers(this.txtCustID.Text).Tables["Customers"];

 

                /////////////////////////////////////////////////////////////////////////////////////////////////////

                this.ResetControls();

                /////////////////////////////////////////////////////////////////////////////////////////////////////

 

                EnumerableRowCollection<DataRow> cust = customers.AsEnumerable();

 

                /////////////////////////////////////////////////////////////////////////////////////////////////////

 

                this.txtCustID.Text = (from c in cust select c.Field<String>(“CustID”)).First();

                this.txtFirstName.Text = (from c in cust select c.Field<String>(“FirstName”)).First();

                this.txtLastName.Text = (from c in cust select c.Field<String>(“LastName”)).First();

                this.dtpJoinDate.Value = (from c in cust select c.Field<DateTime>(“JoinDate”)).First();

                this.txtEmail.Text = (from c in cust select c.Field<String>(“Email”)).First();

                this.txtCreditLimit.Text = (from c in cust select c.Field<Decimal>(“CreditLimit”)).First().ToString();

 

                //////////////////////////////////////////////////////////////////////////////////////////////////////             

 

            }

 

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

 

        }

Code under Update button is as follows

private void btnUpdate_Click(object sender, EventArgs e)

        {

            try

            {

                using (DAFSampleData.Customers oCustomers = new DAFSampleData.Customers())

                {

 

                    oCustomers.CustID = this.txtCustID.Text;

                    oCustomers.FirstName = this.txtFirstName.Text;

                    oCustomers.LastName = this.txtLastName.Text;

                    oCustomers.JoinDate = this.dtpJoinDate.Value;

                    oCustomers.Email = this.txtEmail.Text;

                    oCustomers.CreditLimit = Convert.ToDecimal(this.txtCreditLimit.Text);

 

                    /////////////////////////////////////////////////////////////////////

                    string updateMSG = oCustomers.Update();

                    MessageBox.Show(updateMSG);

 

                    /////////////////////////////////////////////////////////////////////

 

                }

            }

 

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

Code under Delete button is as follows

private void btnDelete_Click(object sender, EventArgs e)

        {

            try

            {

                using (DAFSampleData.Customers oCustomers = new DAFSampleData.Customers())

                {

 

                    oCustomers.CustID = this.txtCustID.Text;

 

                    /////////////////////////////////////////////////////////////////////

                    string deleteMSG = oCustomers.Delete();

                    MessageBox.Show(deleteMSG);

 

                    /////////////////////////////////////////////////////////////////////

                    this.ResetControls();                  

                    /////////////////////////////////////////////////////////////////////

 

                }

            }

 

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

Finally run the SP that are also provided for in the Stored Procedure folder. No need to write them manually you can use tool such as Code Generation; Generate Stored Procedures Automatically from my earlier post.

Create Customers Table Script 

create table Customers
(CustID varchar(10) not null constraint pkCustID primary key,
FirstName varchar(20),
LastName varchar(20),
JoinDate smalldatetime,
Email varchar(20),
CreditLimit money
)
go

Insert Into Customers SP

create proc uspInsertCustomers(
@CustID varchar(10),
@FirstName varchar(20),
@LastName varchar(20),
@JoinDate smalldatetime,
@Email varchar(20),
@CreditLimit money
) as

declare @ErrorMSG varchar(200)

if exists(select CustID from Customers where CustID = @CustID)
 begin
  set @ErrorMSG = ‘The record with %s: %s, you are trying to enter already exists’
  raiserror(@ErrorMSG, 16, 1, ‘Cust ID’, @CustID)
  return 1
 end

begin
insert into Customers
(CustID, FirstName, LastName, JoinDate, Email, CreditLimit)
values
(@CustID, @FirstName, @LastName, @JoinDate, @Email, @CreditLimit)
return 0
end
go

Update Customers SP

create proc uspUpdateCustomers(
@CustID varchar(10),
@FirstName varchar(20),
@LastName varchar(20),
@JoinDate smalldatetime,
@Email varchar(20),
@CreditLimit money
) as

declare @ErrorMSG varchar(200)

if not exists(select CustID from Customers where CustID = @CustID)
 begin
  set @ErrorMSG = ‘The record with %s: %s, you are trying to enter does not exist in the registered %s’
  raiserror(@ErrorMSG, 16, 1, ‘Cust ID’, @CustID, ‘Customers’)
  return 1
 end

begin
update Customers set
FirstName = @FirstName, LastName = @LastName, JoinDate = @JoinDate, Email = @Email, CreditLimit = @CreditLimit
where CustID = @CustID
return 0
end
go

Get Customers SP

create proc uspGetCustomers(
@CustID varchar(10)
) as

declare @ErrorMSG varchar(200)

if not exists(select CustID from Customers where CustID = @CustID)
 begin
  set @ErrorMSG = ‘The record with %s: %s, you are trying to enter does not exist in the registered %s’
  raiserror(@ErrorMSG, 16, 1, ‘Cust ID’, @CustID, ‘Customers’)
  return 1
 end
else
begin
 select CustID, FirstName, LastName, JoinDate, Email, CreditLimit
 from Customers
 where CustID = @CustID
return 0
end
go

Delete SP

 Create one delete stored procedure that can be used to delete data from different tables

Conclusion

If you follow some basic rules and commit them to habit, writing data-access code will be faster, easier, and more reusable, save you trips to the server, and allow you to keep your data separate.

Note

  • Remember to download the complete code from the attached file (Data Access Framework.zip), that  shows usage in both VB and C#
  • This post contains a stripped down version of code that is an extract from my framework that I have used for over five years now. One application that uses it can be downloaded from http://www.clinicmaster.net/Downloads.htm
  • Follow me on twitter (@Kutegz) to get information for more posts.

 Next Post (s)

  •  Code Generation; Generate VB/C# Code Automatically

 

 Wilson Kutegeka | Microsoft MVP – VB | C#
Developer | Promoter | ClinicMaster Software

Cel: +256 772 609113 | Web:
www.clinicmaster.net

follow me on twitter find me on facebook

 

One thought on “Building a Data Access Framework (DAF)”

  1. hey thanks for great tutorials where is the link to the zip file for this tutorial i would like to download it and see how its used thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>