Building a Data Access Framework (DAF)

Kutegz — Mon, 24 May 2010 08:44:00 GMT

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

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 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

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

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 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

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

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 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

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

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

.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 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

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 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

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

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 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,

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)

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

Search results matching tags 'Data Access Layer', 'Data Access Framework', 'VB', and '.NET Framework'

Building a Data Access Framework (DAF)