Design


The design intends to solve the following problems:

 

1. SQL Queries written in data access code is generally full with string concatenation which makes it difficult to read and un-maintainable as it grows

 

2. It gets even more difficult when your code supports multiple database server products and the queries are written in your data access code.

 

To solve these problems we need to do the following

 

1. Add a class called SQLHelper:

 

 

 

 

 

Imports System

Imports System.Collections

Imports System.Reflection

Imports System.Configuration

 

Friend Class SQLHelper

    Private Shared htQueries As Hashtable = New Hashtable

    Private Shared mExtension As String

 

    Private Const CURRENT_EXTENSION As String = “CURRENT_EXTENSION”

    Private Const DEFAULT_EXTENSION As String = “.sql.qry”

   

    Shared Sub New()

        If Not ConfigurationSettings.AppSettings.Item(CURRENT_EXTENSION) Is Nothing Then

            mExtension = ConfigurationSettings.AppSettings.Item(CURRENT_EXTENSION)

            Return

        End If

        mExtension = DEFAULT_EXTENSION

    End Sub

 

    Public Shared ReadOnly Property Extension() As String

        Get

            Return mExtension

        End Get

    End Property

 

    Public Shared Function GetQueryString(ByVal CurrentAssembly As [Assembly], ByVal FileName As String) As String

        Dim key As String = GetKey(CurrentAssembly, FileName)

        Return GetEmbeddedQuery(key)

    End Function

 

    Public Shared Function GetQueryString(ByVal FileName As String) As String

        Dim CurrentlyAssembly As [Assembly] = Reflection.Assembly.GetExecutingAssembly()

        Return GetQueryString(CurrentlyAssembly, FileName)

    End Function

 

    Public Shared Function GetQueryString(ByVal FileName As String, ByVal args() As String) As String

        Dim SQL As String = GetQueryString(FileName)

        Return String.Format(SQL, args)

    End Function

 

    Private Shared Function GetKey(ByVal CurrentAssembly As [Assembly], ByVal FileName As String) As String

        Dim TheNamespace As String = GetType(SQLHelper).Namespace

        If TheNamespace Is Nothing OrElse Trim(TheNamespace) = “” Then

            Return FileName

        Else

            Return TheNamespace & “.” & FileName

        End If

    End Function

 

    Public Shared Function GetEmbeddedQuery(ByVal FullPath As String) As String

        Dim CurrentlyAssembly As [Assembly] = Reflection.Assembly.GetExecutingAssembly()

        Return GetEmbeddedFileAsString(CurrentlyAssembly, FullPath)

    End Function

 

    Private Shared Function GetEmbeddedFileAsString(ByVal CurrentAssembly As [Assembly], ByVal key As String) As String

        SyncLock (htQueries)

            Dim SQL As String

            If htQueries.Item(key) Is Nothing Then

                Dim FullPath As String = key & mExtension

                SQL = GetEmbeddedResourceAsString(CurrentAssembly, FullPath)

                htQueries.Add(key, SQL)

            End If

        End SyncLock

        Return CType(htQueries.Item(key), String)

    End Function

 

    Public Shared Function GetEmbeddedResourceAsString(ByVal CurrentAssembly As [Assembly], ByVal FullPath As String) As String

        Dim ResultStream As System.IO.Stream = CurrentAssembly.GetManifestResourceStream(FullPath)

        If Not ResultStream Is Nothing Then

            Try

                Dim intStreamLen As Integer = CType(ResultStream.Length, Integer)

                Dim ByteArray(intStreamLen – 1) As Byte

                ResultStream.Read(ByteArray, 0, intStreamLen)

                Return System.Text.Encoding.UTF8.GetString(ByteArray)

            Catch e As Exception

                Throw New Exception(“Error reading resource”, e)

            Finally

                If Not ResultStream Is Nothing Then

                    ResultStream.Close()

                End If

            End Try

        Else

            Throw New Exception(“Resource not found”)

        End If

    End Function

 

End Class

 

 

The code written above contains the following:

1.    

mExtension: This member stores the default extension for the query files. The query files need to be stored in your data access project as embedded resources. You can keep multiple extensions for supporting multiple database products.
For ex. you have an extension “.sql.qry” for SQL Server/T-SQL “.ora.qry” for Oracle/PL-SQL

 

2.    

htQueries: This member is used to cache string from a query file once it is retrieved. This will prevent your code from calling expensive reflection code for retrieving each and every query.

 

3.    

GetQueryString(FileName as string): This is a method which takes the query file name as a parameter, retrieves the file from the assembly, reads the string and returns the query.

 

4.    

GetQueryString(FileName as string, args() as string): This method is meant to help you pass parameters in an easy, yet powerful way. Following example shows a sample query string and how to pass parameters to it.

Sample query string:

SELECT * FROM CUSTOMER WHERE FNAME LIKE ‘{0}’ AND LNAME LIKE ‘{1}’

Sample code to pass parameters or prepare queries:

 


Dim sParamArray(2) As String

sParamArray.SetValue(“Sukanya”, 0)

sParamArray.SetValue(“Singh”, 1)

SQLHelper.GetQueryString(“GetCustomerByName”,sParamArray)

 

 

To use the code shown above,

1.    Add text files to your data access project.

2.    Rename its extension to “.sql.qry” or whichever appropriate.

3.    Add your query to this file.

4.    Call the query from your data access code. No need to write long code from creating command and passing parameters.

 

Let me know if it works for you.

Thanks and have fun!!!

 

 

Light Bulb

I’ve been recently involved in design as many of the teams I’ve been working with are starting newer projects. As we get on to design we had a lot of mention of using design patterns especially GOF(Gang Of Four).

One of the common problems I’ve faced using GOF is that it really does not fit every scenario especially when it comes to object modeling. It also does not tell me which object should do what all functions/responsibilities. It is also easy for developers with relatively lesser experience to forget about basic design principles like Low Coupling, High Cohesion, Modular Design while concentrating too much on design patterns.  

Few years back I had a chance to work with an Architect, who trained us on some very basic principles of design and code. One such concept I learned was Information Expert Pattern

It has since then served as a guiding principle, whenever i get in to design. It states that responsibility should be assigned to the information expert—the class that has all the necessary information.

Information expert not only enables you to keep the design simple, it also makes it a lot more easier to write code for reuse if followed religiously throughout the design.

The details of this can be found in the book Applying UML and Patterns: An Introduction to Object-Oriented Analysis and Design and Iterative Development  by Craig Larman.

Some other references :

If you have used Information Expert before. Let me know your experiences.