Accessing the database directly from within the CRM/BBIS via VB

I have seen and done a lot of work lately with the CRM and BBIS that deals directly with the database. I have also had the joys of refactoring other developer’s code, which we all know is a blast. Some of the major problems I have seen and encountered are inline SQL, which is a huge no-no, and the general lack of knowledge around the SqlClient classes.

Some basics:

1) NEVER use inline SQL, always parameterize your queries

Make sure that you don’t do something like what is below, because it’s…wrong. Look up inline SQL on google and read the first 10 results.

Dim cmdText As String = "select * from CONSTITUENT where ID = '" + _id + "'"

Make sure to parameterize like below, because then when you add parameter objects to the command object it will properly validate the parameter any illegal character. (sorry if that’s confusing)

Dim cmdText As String = "select * from CONSTITUENT where ID = @ID"

2) Know how to use the “using” statement and know what it does

The using statement is an extremely useful tool when it comes database work. Here is a link that will provide some basic info on the using statement, http://msdn.microsoft.com/en-us/library/yh598w02.aspx.

As you may know or learn from reading that the using statement provides an easy way to properly use IDisposable objects. DbConnection, DbCommand, & DbTransaction all implement IDisposable, so yay! We get to use “using” with it all and let it handle the correct disposal of all of our objects, even the connection!

Below is the code that I use for my database work, use it at your own discretion, change it, love it, or hate it. It does everything I need, and does it in a correct and elegant, if I say so myself, way. Some may argue with my use of DataTables instead of DataReaders. The main reason I use DataTables is that you can return the DataTable to the BI side of the development work without having to keep an open connection to the database. Whereas a DataReader requires an open connection to the database. See the problem? Have fun dissecting my code below.

Public Class Database Private _connectionString As String Private _cmdText As String Private _parms As List(Of SqlParameter)
        Private _cmdType As CommandType Public Sub New(ByVal connectionString As String, ByVal cmdText As String, Optional ByVal parms As List(Of SqlParameter) = Nothing, Optional ByVal cmdType As CommandType = CommandType.StoredProcedure)
            _connectionString = connectionString
            _cmdText = cmdText
            _parms = parms
            _cmdType = cmdType
        End Sub Public Function ExecuteDataTable() As DataTable Using connection As SqlConnection = CreateConnection()
                Using command As SqlCommand = CreateCommand(connection)
                    Using dataAdapter As SqlDataAdapter = New SqlDataAdapter(command)
                        Dim dataTable As New DataTable()
                        connection.Open()
                        dataAdapter.Fill(dataTable)
                        Return dataTable
                    End Using End Using End Using End Function Public Function ExecuteNonQuery()
            Try Using connection As SqlConnection = CreateConnection()
                    connection.Open()

                    Using transaction As SqlTransaction = connection.BeginTransaction()
                        Using command As SqlCommand = CreateCommand(connection, transaction)
                            command.ExecuteNonQuery()
                            transaction.Commit()
                            Return True End Using End Using End Using Catch ex As Exception 'no need to rollback the transaction 'dispose rolls back the transaction if the commit isnt explicitly called 'and dispose is called when the program leaves the transactions using block Throw ex
            End Try End Function Public Function ExecuteScalar() As Object Using connection As SqlConnection = CreateConnection()
                Using command As SqlCommand = CreateCommand(connection)
                    connection.Open()
                    Return command.ExecuteScalar()
                End Using End Using End Function Private Function CreateConnection()
            If Not String.IsNullOrWhiteSpace(_connectionString) Then Return New SqlConnection(_connectionString)
            Else Throw New Exception("Connection string is empty.")
            End If End Function Private Function CreateCommand(ByVal connection As SqlConnection, Optional ByVal transaction As SqlTransaction = Nothing) As SqlCommand If connection Is Nothing Then CreateConnection()
            End If Dim command As New SqlCommand(_cmdText, connection)

            If transaction IsNot Nothing Then command.Transaction = transaction
            End If command.CommandType = _cmdType

            If (_parms IsNot Nothing) AndAlso (0 < _parms.Count) Then AddParameters(command)
            End If Return command
        End Function Private Sub AddParameters(ByRef command As SqlCommand)
            If command IsNot Nothing Then For Each parm As SqlParameter In _parms
                    command.Parameters.Add(parm)
                Next End If End Sub Public Sub ClearParameters()
            Parameters = Nothing End Sub Public ReadOnly Property OutputParameterValue(ByVal parameterName As String) As Object Get If _parms IsNot Nothing AndAlso _parms.Count > 0 Then For i As Integer = 0 To _parms.Count - 1
                        If _parms(i).ParameterName.ToUpper = parameterName Then Return _parms(i).Value
                        End If Next End If Return Nothing End Get End Property Public WriteOnly Property Parameters As List(Of SqlParameter)
            Set(ByVal value As List(Of SqlParameter))
                _parms = value
            End Set End Property Public Property CommandText As String Get Return _cmdText
            End Get Set(ByVal value As String)
                _cmdText = value
            End Set End Property End Class

Happy coding…laugh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s