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