Updated :- To compare performance benchmarks, Here is an interesting article. Data Access Performance Comparison in .NET
Recently i was working on a project where application performance was a top priority. I started looking into ORM frameworks and found that ORMs are great for most applications, but when performance is a factor it�s best to write your own data access. So that's when I decided to write my own data access. This article will explain some of the design that i have used in my project. I have used SqlDataReader�s strongly typed Get methods to do a null check and then write the values. Here is my base class in VB.NET.
1: Public MustInherit Class BaseDAL(Of T As Class)
3: Public fieldOrdinals As Dictionary(Of String, Integer) = Nothing
4: Private _Reader As IDataReader = Nothing
6: Public Overridable Sub GetFieldOrdinalsFromReader()
7: fieldOrdinals = New Dictionary(Of String, Integer)()
10: Public Overridable Function FillFromReader(ByVal sqlReader As IDataReader, ByRef instance As T)
11: If (IsNothing(fieldOrdinals)) Then
13: GetFieldOrdinalsFromReader()
18: Public Function ParseInteger(ByVal column As String) As Integer
19: Dim result As Integer = 0
22: Dim ordinal As Integer = 0
23: If (fieldOrdinals.Keys.Contains(column)) Then
24: ordinal = fieldOrdinals(column)
25: result = If(_Reader.IsDBNull(ordinal), Nothing, _Reader.GetInt32(ordinal))
27: Catch ex As Exception
34: Public Function ParseString(ByVal column As String) As String
35: Dim result As String = Nothing
38: Dim ordinal As Integer = 0
39: If (fieldOrdinals.Keys.Contains(column)) Then
40: ordinal = fieldOrdinals(column)
41: result = If(_Reader.IsDBNull(ordinal), Nothing, _Reader.GetString(ordinal))
43: Catch ex As Exception
50: Public Function ParseDate(ByVal column As String) As DateTime
51: Dim result As DateTime = Nothing
54: Dim ordinal As Integer = 0
55: If (fieldOrdinals.Keys.Contains(column)) Then
56: ordinal = fieldOrdinals(column)
57: result = If(_Reader.IsDBNull(ordinal), Nothing, _Reader.GetDateTime(ordinal))
59: Catch ex As Exception
66: Public Function ParseBoolean(ByVal column As String) As Boolean
67: Dim result As Boolean = False
70: Dim ordinal As Integer = 0
71: If (fieldOrdinals.Keys.Contains(column)) Then
72: ordinal = fieldOrdinals(column)
73: result = If(_Reader.IsDBNull(ordinal), False, _Reader.GetBoolean(ordinal))
75: Catch ex As Exception
82: Public Function SetFieldOrdinalsFromReader(ByVal column As String) As Boolean
83: Dim ordinal As Integer = -1
85: ordinal = _Reader.GetOrdinal(column)
86: fieldOrdinals.Add(column, ordinal)
88: ' Column not in reader, so don't track the field.
2: Inherits BaseDAL(Of Person)
4: Public Function GetAllPersons() As List(of Person)
5: dim myPersons as new List(of Person)
6: Dim defaultDB As Database = Helpers.GetDataBaseInstance()
7: Using cmdOrder As DbCommand = defaultDB.GetStoredProcCommand("SP_Name")
8: Using sqlReader As IDataReader = defaultDB.ExecuteReader(cmdOrder)
9: While sqlReader.Read()
10: Dim myPerson As New Person
11: myPersons.Add(FillFromReader(sqlReader, myPerson))
18: Public Overrides Function FillFromReader(ByVal sqlReader As IDataReader, ByRef myPerson As Person)
19: MyBase.FillFromReader(sqlReader, myOrder)
20: myPerson.PersonId = ParseInteger("PersonId")
21: myPerson.FullName = ParseString("FullName")
22: myPerson.Address = ParseString("Address")
23: myPerson.CreatedDate = ParseDate("CreatedDate")
27: Public Overrides Sub GetFieldOrdinalsFromReader()
28: MyBase.GetFieldOrdinalsFromReader()
29: SetFieldOrdinalsFromReader("PersonId")
30: SetFieldOrdinalsFromReader("FullName")
31: SetFieldOrdinalsFromReader("Address")
32: SetFieldOrdinalsFromReader("CreatedDate")
My base class has 2 methods SetFieldOrdinalsFromReader and FillFromReader that are used in a DAL class, SetFieldOrdinalsFromReader will assign database columns to fieldOrdinals which is a dictionary object and the FillFromReader will populate the person object with the help of helper methods that use SqlDataReader�s strongly typed Get methods. Here is my person class.
2: Private _PersonId As Integer =0
3: Public Property PersonId() As Integer
7: Set(ByVal value As Integer)
12: Private _FullName As String
13: Public Property FullName() As String
17: Set(ByVal value As String)
22: Private _Address As String
23: Public Property Address() As String
27: Set(ByVal value As String)
32: Private _CreatedDate As Date
33: Public Property CreatedDate() As Date
37: Set(ByVal value As Date)
Please note, the above code is written for queries to read data only, and if you would like to insert/update a bulk data, then you can read my last article here. Please feel free to leave any comments and feedback.
Subscribe
