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