Converting a "standard" SQL statment application to parameters

[Sprint]

I read through http://www.vbdotnetforums.com/database-general-discussion/18562-ins-outs-parameterized-queries.html and http://www.vbdotnetforums.com/datab...scussions-about-parameterized-queries-pq.html already and maybe I'm dense but I can't figure it out. When it first started out (many years ago when I was just learning VB) I wrote a "DataAccess.vb" module with something like this (this is a subset but gets the point across):

Friend Function UpdateData(ByVal strSQL As String, Optional ByVal DocumentsDatabase As Boolean = False) As Integer
        ' Create our connection string
        Dim myConnString As SqlConnection
        If DocumentsDatabase = True Then
            myConnString = SQLDocumentsConnectionString()
        Else
            myConnString = SQLConnectionString()
        End If

        ' Create our command
        Dim myCommand As SqlCommand = New SqlCommand(strSQL, myConnString)
        Try
            ' Open the connection
            myCommand.Connection.Open()
            ' execute the SQL and return the result
            Return myCommand.ExecuteNonQuery()
        Catch ex As SqlException
            My.Application.Log.WriteEntry("An error was thrown by the UpdateData function.  Error Details:" & vbCrLf & ex.Message & vbCrLf & vbCrLf & "SQL Statement: " & strSQL)
            Debug.WriteLine("An error was thrown by the UpdateData function.  Error Details: " & vbCrLf & ex.Message & vbCrLf & vbCrLf & "SQL Statement: " & strSQL)
        Finally
            ' Close the connection
            myCommand.Connection.Close()
            myConnString = Nothing
            myCommand = Nothing
        End Try
    End Function

Then when I want to update data anywhere else in my application I do something like this:

If UpdateData("INSERT INTO tCompanyNotes ([CompanyID], [NoteTitle], [NoteDetails],[DateAdded],[AddedBy]) VALUES (" & mCompanyId & ",'" _
& StripSpacesAndQuotes(TitleTextBox.Text) & "','" & StripSpacesAndQuotes(DetailsTextBox.Text) & "','" & Date.Now.ToShortDateString & "','" & CurrentUserFullName & "')") > 0
 ' Stuff if successful else stuff for not sucessful

As you can see it's pretty hard to keep track of especially when I have updates to 30+ fields. Not only that when a user enters a single or double quot it throws a SQL error or as mentioned it could also be worse if the users had a idea on how to break a database. I actually wrote a sub that strips out the offending characters just in case and although it works it's not a good workaround.

Now I want to upgrade the program to parameters but I don't understand how. I guess I'm looking for something like what I have, a sub in a module for getting data and one for updating data and then a way to call them. Is there a easy way to do this or a example somewhere? Once I can get the example above done I'm sure I could replicate it across the other 80 - 100 queries I have. I have used a single parameterize query to add a file into SQL but it was off a example and I don't understand really how it does it, just that it works. In that example in the update function I have to declare the parameter with a myCommand.Parameter.Add(theParameter) and I guess I'm worried about having to repeat this code over and over while with the example above I wrote the update code once and can easily reuse it.

Hopefully this makes sense.

-Allan

 

[rcombs4]

here is a bit of code that i use a good bit. Basically the same thing you are doing but making a SqlDataAdapter public.

Public Class DB
    Public DA As New SqlDataAdapter

    Sub New(ByVal SQL As String, Optional ByVal DBType As GlobalVars.MyDBType = GlobalVars.MyDBType.Main_DB)
        DA = New SqlDataAdapter(SQL, GetDataConnection(DBType))
    End Sub

    Sub New()

    End Sub  

    Public Function execute() As DataSet
        Dim ds As New DataSet
        DA.SelectCommand.CommandTimeout = 100
        DA.Fill(ds)
        Return ds
    End Function


    Public Function executeScalar() As Object

        DA.SelectCommand.Connection.Open()
        DA.SelectCommand.CommandTimeout = 100
        executeScalar = DA.SelectCommand.ExecuteScalar()
        DA.SelectCommand.Connection.Close()

    End Function
...

I use it by...

dim sql as new DB("SELECT * FROM Table WHERE Field1 = @Field1 AND Field2 = @Field2")
sql.da.selectcommand.parameters.addwithvalue("@Field1",txtField1.text)
sql.da.selectcommand.parameters.addwithvalue("@Field2",txtField2.text)

dim ds as dataset = sql.execute

 

[InertiaM]

Here's something I use. It isnt my most up-to-date version, but should give you the direction you are looking for.

To ease your transition into PQs, you can use a messagebox to show the effective query

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim WhatConnection As New SqlConnection("Connection string here")

        Dim sSQL As String = ""
        sSQL &= " INSERT INTO"
        sSQL &= "   tCompanyNotes"
        sSQL &= "   ("
        sSQL &= "     [CompanyID],"
        sSQL &= "     [NoteTitle],"
        sSQL &= "     [NoteDetails],"
        sSQL &= "     [DateAdded],"
        sSQL &= "     [AddedBy]"
        sSQL &= "   )"
        sSQL &= "   VALUES"
        sSQL &= "   ("
        sSQL &= "     @CompanyId,"
        sSQL &= "     @NoteTitle,"
        sSQL &= "     @NoteDetails,"
        sSQL &= "     @DateAdded,"
        sSQL &= "     @AddedBy"
        sSQL &= "   )"

        Dim ParametersToPass As New List(Of SqlParameter)
        AddParameter(ParametersToPass, "@CompanyID", mCompanyId, SqlDbType.VarChar, 30, False)
        AddParameter(ParametersToPass, "@NoteTitle", TitleTextBox.Text.replace(" ", ""), SqlDbType.VarChar, 100, False)
        AddParameter(ParametersToPass, "@NoteDetails", DetailsTextBox.Text.replace(" ", ""), SqlDbType.VarChar, 255, False)
        AddParameter(ParametersToPass, "@DateAdded", Date.Now, SqlDbType.SmallDateTime, , False)
        AddParameter(ParametersToPass, "@AddedBy", CurrentUserFullName, SqlDbType.VarChar, 30, False)

        Dim iRecordsAffected As Integer = 0
        Try
            iRecordsAffected = ExecuteNonQuery(WhatConnection, sSQL, ParametersToPass)
        Catch ex As Exception
            iRecordsAffected = 0
        End Try

        ParametersToPass.Clear()

    End Sub

    Public Overloads Function ExecuteNonQuery(ByRef whatSQLconnection As SqlConnection, ByVal sSQL As String, ByVal whatParameters As List(Of SqlParameter), Optional ByVal ShowEffectiveNonQuery As Boolean = False) As Integer
        Using cmdCommand As SqlCommand = New SqlCommand(sSQL)
            cmdCommand.Connection = whatSQLconnection
            cmdCommand.Parameters.Clear()
            For Each _Parameter As SqlParameter In whatParameters
                cmdCommand.Parameters.Add(_Parameter)
            Next

            Dim sEffectiveNonQuery As String = sSQL
            For Each _Parameter As SqlParameter In whatParameters
                If DBNull.Value.Equals(_Parameter.Value) = False AndAlso _Parameter.Value.ToString.Length > 0 Then
                    Select Case _Parameter.SqlDbType
                        Case SqlDbType.VarChar, SqlDbType.Char, SqlDbType.NVarChar, SqlDbType.NChar, SqlDbType.Text, SqlDbType.NText
                            sEffectiveNonQuery = sEffectiveNonQuery.Replace(_Parameter.ParameterName, "'" & _Parameter.Value.ToString & "'")
                        Case SqlDbType.DateTime, SqlDbType.SmallDateTime
                            sEffectiveNonQuery = sEffectiveNonQuery.Replace(_Parameter.ParameterName, "#" & _Parameter.Value.ToString & "#")
                        Case Else
                            sEffectiveNonQuery = sEffectiveNonQuery.Replace(_Parameter.ParameterName, _Parameter.Value.ToString)
                    End Select
                    Select Case _Parameter.DbType
                        Case DbType.String
                            sEffectiveNonQuery = sEffectiveNonQuery.Replace(_Parameter.ParameterName, "'" & _Parameter.Value.ToString & "'")
                        Case DbType.DateTime
                            sEffectiveNonQuery = sEffectiveNonQuery.Replace(_Parameter.ParameterName, "#" & _Parameter.Value.ToString & "#")
                        Case Else
                            sEffectiveNonQuery = sEffectiveNonQuery.Replace(_Parameter.ParameterName, _Parameter.Value.ToString)
                    End Select
                Else
                    sEffectiveNonQuery = sEffectiveNonQuery.Replace(_Parameter.ParameterName, "NULL")
                End If
            Next
            If ShowEffectiveNonQuery = True Then
                MessageBox.Show(sEffectiveNonQuery)
            End If

            Dim iRecordsAffected As Integer = 0
            Try
                iRecordsAffected = cmdCommand.ExecuteNonQuery()
            Catch ex As Exception
                iRecordsAffected = 0
            End Try

            cmdCommand.Parameters.Clear()

            Return iRecordsAffected
        End Using
    End Function

    Public Sub AddParameter(ByRef ParameterList As List(Of SqlParameter), ByVal ParameterName As String, ByVal Value As Object, ByVal SQLDbType As SqlDbType, Optional ByVal Size As Integer = 0, Optional ByVal IsNullable As Boolean = False)
        For Each _Parameter As SqlParameter In ParameterList
            If _Parameter.ParameterName.Trim.ToUpper = ParameterName.Trim.ToUpper Then
                ParameterList.Remove(_Parameter)
                Exit For
            End If
        Next
        Dim NewParameter As New SqlParameter
        With NewParameter
            .ParameterName = ParameterName
            .SqlDbType = SQLDbType
            .IsNullable = IsNullable
            If IsNullable = True Then
                If Value.ToString.Length = 0 Then
                    .Value = DBNull.Value
                Else
                    .Value = Value
                End If
            Else
                If Value.ToString.Length = 0 Then
                    Throw New Exception("Nullable value where IsNullable = false")
                Else
                    .Value = Value
                End If
            End If
        End With
        ParameterList.Add(NewParameter)
        NewParameter = Nothing
    End Sub

 

[Sprint]

Thanks for the examples. I'm still a bit confused and it looks like a lot more code then what I have but I'll try to replace a couple of mine and see how it goes.

-Allan

 

[jmcilhinney]

E.g.

''' <summary>
''' Creates a command associated with the current connection to execute an inline SQL statement.
''' </summary>
''' <param name="query">
''' The SQL code to execute, which is assigned to the command's <see cref="DbCommand.CommandText">CommandText</see> property.
''' </param>
''' <param name="parameterValues">
''' A dictionary containing parameter values keyed on parameter name, which are added to the command's <see cref="DbCommand.Parameters">Parameters</see> collection.
''' </param>
''' <returns>
''' A data source-specific <see cref="DbCommand" /> object.
''' </returns>
Public Overloads Function CreateCommand(ByVal query As String, _
                                        ByVal parameterValues As Dictionary(Of String, Object)) As DbCommand
    Dim command As DbCommand = Me.CreateCommand()

    'The query specified is an inline SQL statement.
    command.CommandText = query
    command.CommandType = CommandType.Text

    If parameterValues IsNot Nothing AndAlso parameterValues.Count > 0 Then
        With command.Parameters
            'Add the parameters.
            For Each parameterValue As KeyValuePair(Of String, Object) In parameterValues
                .Add(Me.CreateParameter(parameterValue.Key, _
                                        parameterValue.Value, _
                                        ParameterDirection.InputOutput))
            Next
        End With
    End If

    Return command
End Function

''' <summary>
''' Creates a command parameter.
''' </summary>
''' <returns>
''' A data source-specific <see cref="DbParameter" /> object.
''' </returns>
Public Function CreateParameter() As DbParameter
    Return Me.Factory.CreateParameter()
End Function

''' <summary>
''' Creates a command parameter.
''' </summary>
''' <param name="name">
''' The name of the parameter.
''' </param>
''' <returns>
''' A data source-specific <see cref="DbParameter" /> object.
''' </returns>
Public Function CreateParameter(ByVal name As String) As DbParameter
    Dim parameter As DbParameter = Me.CreateParameter()

    parameter.ParameterName = name
    parameter.SourceColumn = Me.GetParameterSourceColumn(name)

    Return parameter
End Function

''' <summary>
''' Creates a command parameter.
''' </summary>
''' <param name="name">
''' The name of the parameter.
''' </param>
''' <param name="value">
''' The parameter's value.
''' </param>
''' <returns>
''' A data source-specific <see cref="DbParameter" /> object.
''' </returns>
Public Function CreateParameter(ByVal name As String, _
                                ByVal value As Object) As DbParameter
    Dim parameter As DbParameter = Me.CreateParameter(name)

    parameter.Value = value

    Return parameter
End Function

''' <summary>
''' Creates a command parameter.
''' </summary>
''' <param name="name">
''' The name of the parameter.
''' </param>
''' <param name="value">
''' The parameter's value.
''' </param>
''' <param name="direction">
''' The direction(s) in which the parameter can pass data.
''' </param>
''' <returns>
''' A data source-specific <see cref="DbParameter" /> object.
''' </returns>
Public Function CreateParameter(ByVal name As String, _
                                ByVal value As Object, _
                                ByVal direction As ParameterDirection) As DbParameter
    Dim parameter As DbParameter = Me.CreateParameter(name, value)

    parameter.Direction = direction

    Return parameter
End Function

Me.Factory is an instance of the DbProviderFactory class, which allows the same code to generate objects for any ADO.NET providers, but you could just create a SqlParameter directly if you wanted.

 

[Sprint]

ok, I'll try some of these out today and see how it goes. Probably build a test app just for this and once I got it down I'll start converting the big program over. Thanks for the suggestions and examples.

-Allan

 

[Sprint]

Ok...I'm still not fully understanding the examples so I hacked at it and came up with my own. It uses the parameters but not sure if it's as clean as it could be. Does work though:

Imports System.Data.SqlClient

Module DatabaseAccess
    Friend Function ExecuteSQLCommand(ByVal strSQL As String, ByVal parameterValues As Dictionary(Of String, Object), Optional ByVal DocumentsDatabase As Boolean = False) As Integer
        Dim myCommand As SqlCommand = New SqlCommand(strSQL)

        ' Assign our connection string
        If DocumentsDatabase = True Then
            myCommand.Connection = SQLDocumentsConnectionString()
        Else
            myCommand.Connection = SQLConnectionString()
        End If

        myCommand.CommandType = CommandType.Text
        For Each kvp As KeyValuePair(Of String, Object) In parameterValues
            myCommand.Parameters.AddWithValue(kvp.Key, kvp.Value)
        Next kvp

        Try
            ' Open the connection
            myCommand.Connection.Open()
            ' execute the SQL and return the result
            Return myCommand.ExecuteNonQuery()
        Catch ex As SqlException
            My.Application.Log.WriteEntry("An error was thrown by the ExecuteSQLCommand function.  Error Details:" & vbCrLf & ex.Message & vbCrLf & vbCrLf & "SQL Statement: " & strSQL)
            Debug.WriteLine("An error was thrown by the ExecuteSQLCommand function.  Error Details: " & vbCrLf & ex.Message & vbCrLf & vbCrLf & "SQL Statement: " & strSQL)
        Finally
            ' Close the connection
            myCommand.Connection.Close()
            myCommand = Nothing
        End Try
    End Function

    Private Function SQLConnectionString() As SqlConnection
        Return New SqlConnection("Persist Security Info=False;Integrated Security=true;Initial Catalog=" & My.Settings.MainDatabaseName & ";server=" & My.Settings.SQLServerName & "")
    End Function

    Private Function SQLDocumentsConnectionString() As SqlConnection
        Return New SqlConnection("Persist Security Info=False;Integrated Security=true;Initial Catalog=" & My.Settings.DocumentsDatabaseName & ";server=" & My.Settings.SQLServerName & "")
    End Function
End Module

Then on my form after checking for input and what not:

    Private Sub AddRecordToDatabaseButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddRecordToDatabaseButton.Click

        Dim myParamList As New Dictionary(Of String, Object)
        myParamList.Add("@SerialNumber", SerialNumberTextBox.Text)
        myParamList.Add("@DeviceType", TypeOfDeviceTextBox.Text)
        myParamList.Add("@Manufacture", ManufactureTextBox.Text)
        myParamList.Add("@Description", DescriptionTextBox.Text)
        myParamList.Add("@Value", ValueTextBox.Text)
        myParamList.Add("@OrderDate", OrderDateTextBox.Text)
        myParamList.Add("@OperatingSystem", OperatingSystemTextBox.Text)
        myParamList.Add("@Speed", SpeedTextBox.Text)
        myParamList.Add("@Memory", MemoryTextBox.Text)
        myParamList.Add("@DHCP", DHCPCheckBox.Checked)
        myParamList.Add("@IPAddress", IPAddressTextBox.Text)
        myParamList.Add("@Notes", NotesTextBox.Text)
        myParamList.Add("@DateModified", DateModifiedTextBox.Text)
        myParamList.Add("@ModifiedBy", CurrentUserTextBox.Text)

        If ExecuteSQLCommand("INSERT INTO tDevices (SerialNumber, DeviceType, Manufacture, Description, Value, OrderDate, OperatingSystem, Speed, Memory, " & _
                            "DHCP, IPAddress, Notes, DateModified, ModifiedBy) " & _
                        "VALUES (@SerialNumber, @DeviceType, @Manufacture, @Description, @Value, @OrderDate, @OperatingSystem, @Speed, @Memory, " & _
                            "@DHCP, @IPAddress, @Notes, @DateModified, @ModifiedBy)", myParamList, False) = 1 Then
            ' msgboxes for testing only
            MsgBox("Record added!")
        Else
            MsgBox("Record could not be added!")
        End If
    End Sub

This close to right?

 

[InertiaM]

This close to right?

It's certainly close to right. My only comment would be that by doing it your way, you arent passing the parameter type. If you are happy with this, then yes, it will do

One note - watch out for FailBoy » SqlParameter is already contained by another SqlParameterCollection

 

[Sprint]

I added the myCommand.Parameters.Clear() before adding the new parameters to both functions. And yes I think I'm ok with not defining the types as I will throw a error if something doesn't match and it's still a vast improvement on what I have. That and I cant think of a easy way to do it using the framework above (which will kinda "fit in" with what I have already the easiest)

 

[cjard]

I read through http://www.vbdotnetforums.com/database-general-discussion/18562-ins-outs-parameterized-queries.html and http://www.vbdotnetforums.com/datab...scussions-about-parameterized-queries-pq.html already and maybe I'm dense but I can't figure it out.

Parameters are placeholders in SQL code, just like variables are placeholders in VB code

If you can understand this:

strSQL = "SELECT * FROM person WHERE lastname = ##LAST##"
strSQL = strSQL.Replace("##LAST##", lastnametextbox.text)


Then youre capable of understanding the notion of a placeholder

If you understanding why this doesnt work:

Dim i as Integer = "hello"

Then you get the notion of data types in variables


If you understand why VB won't compile this:

Dim s As String = "The speech mark character " goes either side of speech"
(3 speech marks)

But you understand that this string contains a speech mark:

Dim s As String = "The speech mark character "" goes either side of speech"
(4 speech marks)

And doesnt cause VB to fall over every time it is used, then you understand that a variable in VB can contain a value that would cause a problem if you wrote it straight into the code, but as a string of characters in memory it's just fine. The ompiler turns "" into a single " character when compiling, and understand that you don't want the string to end at that point


Now, all a parameter in SQL is is a placeholder in the code, that accepts a value of a certain type, and can contain characters that would otherwise break the code


Essentially: Remember that SQL is a programming language. The SQL you write in your VB program is COMPILED and understood by the database server every time it is used. Because it is a programming language it can contain variables

When it first started out (many years ago when I was just learning VB) I wrote a "DataAccess.vb" module with something like this (this is a subset but gets the point across):

Dump all your old knowleedge and code. The world has moved on.


To aid your understanding I'll rewrite your query using parameters in SQL server syntax (not access, not oracle):

Dim cmd as New SqlCommand
cmd.CommandText = "INSERT INTO tCompanyNotes ([CompanyID], [NoteTitle], [NoteDetails],[DateAdded],[AddedBy]) VALUES (@com, @tit,
@det, @dat, @usr)

cmd.Parameters.AddWithValue("@com", mCompanyId)
cmd.Parameters.AddWithValue("@tit", TitleTextBox.Text)
cmd.Parameters.AddWithValue("@det", DetailsTextBox.Text) 
cmd.Parameters.AddWithValue("@dat", Date.Now) 'note your DATE column in db should be a DATE not a string!
cmd.Parameters.AddWithValue("@usr", CurrentUserFullName)

Not only that when a user enters a single or double quot it throws a SQL error or as mentioned it could also be worse if the users had a idea on how to break a database.

Guaranteed impossible with the above code; they really will end up just searching for or entering the quotes as text into the db, as it should be

Now I want to upgrade the program to parameters but I don't understand how.

Now that I showed you above; seriously: forget it all - writing db access code is stupid donkey work and you have better things to be doing with your life. You use a visual tool to lay out forms, set button borders, edges, sizes, texts, layouts, clicks, locations..

Read the DW3 link in my signature, section Creating a Simple Data App

Get VS to write the db code for you. Got a database with 100 tables? Want to write the select, insert, update and delete queries in a parameterized way for them in about 30 seconds? Make a dataset, and tell the wizard to add all the tables. It will write the queries, wire them up and so forth.

Want to insert a line:
Dim myTA as new NotesTableAdapter
myTA.Insert("comapny name here", "note title here", "note text here", "date here", "user here")

Want to show a gui to the user and let them do it?
Set the data sources window to details
Drag the parent table icon to the form.
Text boxes appear, bound to a datatable of local data cache
Make a new record in the local cache:
myBindingSource.AddNew()

Let the user edit the note
Save it:

myTA.Update(theDataTable)

Done

No pain. If you want, give me an access db with the notes table in it and I'll send you an app that reads and writes the notes table and you can look at the code. I'll build it in about 3 minutes using the designers in VS. It really is that easy and the code is much better than anything I could write by hand in 3 hours

 

[cjard]

(The crazy thing is youre spending days here writing more or less the same code [but not as good] as visual studio will write for you in seconds.. you can inspect this code if you want, but doing so would be like opening the car bonnet/hood and looking at the engine; just appreciate that it's there, and it works, then leave it alone. you don't need to build a new engine every time you buy another car)

 

[Sprint]

I've tried using VB to do the code and I find it's easier to do it myself, especially now that I'm converting it all to parameters. It just seemed too cumbersome to change once it was added and if I pulls lots of information at once it seemed slower then hand written code. It's only taking a few minutes per query to update my old style SQL to the parameters so I'm going to continue doing so (and for future projects).

The only other change I made from the code I posted is in my data update function is to check for nothings:

        For Each kvp As KeyValuePair(Of String, Object) In parameterValues
            If kvp.Value Is Nothing Then
                myCommand.Parameters.AddWithValue(kvp.Key, DBNull.Value)
            Else
                myCommand.Parameters.AddWithValue(kvp.Key, kvp.Value)
            End If
        Next kvp

I have a lot of date fields and having them null is perfectly fine but I would get a "Parameter missing" error as I would assign a parameter to a DateTime field of a custom controls library I'm using (which would have a nothing value if a date wasn't selected). This gets around that and also makes sure I do store nulls instead of nothings. All in all with the examples posted it makes sense and isn't that hard.