Prepared Statements in VB.NET

Question

I am new to prepared statements in vb.net and Microsoft SQL Server 2008. I can't really find any good sources for connecting to a database via connection string and executing prepared statements. Could someone show me an example or point me to a resource that might be useful?

Joel Coehoorn · Answer 1 · 2017-07-16T17:53:48.773

Here's some quick example code:

Using cn  As New SqlConnection("your connection string here"), _
      cmd AS New SqlCommand("SELECT * FROM Table WHERE ID= @ID", cn)

    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = 12345

    cn.Open()
    Using rdr As SqlDataREader = cmd.ExecuteReader()
        While rdr.Read()
            'Do something with the record
        End While
        rdr.Close()
    End Using
End Using

Of course you need to Import System.Data and System.Data.SqlClient.

score 5 · Accepted Answer · edited Sep 08 '11 at 16:29

5

Prepared statements are nothing but Parametrized SqlCommands enclosed in a Transaction.

For example, this is a Prepared Statement:

Using c As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
   c.Open()
using mytransaction = c.BeginTransaction()

   Dim command = New SqlCommand("INSERT INTO yourtable(image) values (@image)", c)
   ''# this is specific to the FileUploadControl but the idea is to get the
   ''#image in a byte array; however you do it, it doesn't matter
    Dim buffer(FileUpload1.PostedFile.ContentLength) As Byte
    FileUpload1.PostedFile.InputStream.Read(buffer, 0, buffer.Length)
    command.Parameters.AddWithValue("@image", buffer)
    command.ExecuteNonQuery()    
 mytransaction .Commit()
End Using
End Using

edited Sep 08 '11 at 16:29

Joel Coehoorn

399,467
113
570
794

answered Sep 08 '11 at 16:12

Icarus

63,293
14
100
115

3

Sorry but... I believe you're wrong? They don't require a transaction and have benefits in terms of allowing the server to re-use the execution plan. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare(v=vs.110).aspx – Basic Jan 18 '14 at 00:28
@Basic what you described are stored procedures. While all stored procedures are prepared statements not all prepared statements are stored procedures. – David Söderlund May 16 '16 at 12:34
@DavidSöderlund I'm aware of both (in fact, the docs I linked to discuss both). The point I was trying to make is that Icarus' answer doesn't seem to be accurate eg I can compile a prepared statement, allow the server to build an execution plan and then re-use it multiple times. This answer omits that pattern entirely. – Basic May 16 '16 at 19:07
Also, AddWithValue() has some serious drawbacks. Better to use Add() overload with explicit type information. – Joel Coehoorn Jul 16 '17 at 17:55

score -1 · Answer 3 · edited Nov 03 '19 at 02:30

Dim datOleReader As SqlDataReader
' Public con As New SqlConnection

Public Function CHECK_CONNECTION(ByVal CON As SqlConnection) As SqlConnection
    Try
        If CON.State = ConnectionState.Broken Or CON.State = ConnectionState.Closed Then
            CON.Open()
        End If
    Catch ex As Exception
        CON.Close()
        CON.Open()
    End Try

    Return CON
End Function

Public Function gGetMaxCode(ByVal strTable As String, ByVal strCode As String) As Long
    Dim cmdtext As New SqlCommand
    Try
        '  con = clsexe.CHECK_CONNECTION(con) #
        ' cmdtext.Connection = con #
        cmdtext.Connection = CHECK_CONNECTION(con) ' #
        cmdtext.CommandText = "SELECT ISNULL(MAX(" & strCode & "),0)+1 FROM  " & strTable
        datOleReader = cmdtext.ExecuteReader(CommandBehavior.SingleRow)
        datOleReader.Read()
        If datOleReader.IsDBNull(0) Then
            Return 1
        Else
            Return datOleReader.Item(0)
        End If

    Catch ex As Exception
        MsgBox(ex.Message.ToString)
    Finally
        datOleReader.Close()
    End Try
End Function
Public Function gGetMaxCode(ByVal strTable As String, ByVal strCode As String, ByVal trans As SqlTransaction) As Long
    Dim cmdtext As New SqlCommand
    Try
        '  con = clsexe.CHECK_CONNECTION(con) #
        ' cmdtext.Connection = con #
        cmdtext.Connection = CHECK_CONNECTION(con) ' #
        cmdtext.CommandText = "SELECT ISNULL(MAX(" & strCode & "),0)+1 FROM  " & strTable
        cmdtext.Transaction = trans
        datOleReader = cmdtext.ExecuteReader(CommandBehavior.SingleRow)
        datOleReader.Read()
        If datOleReader.IsDBNull(0) Then
            Return 1
        Else
            Return datOleReader.Item(0)
        End If

    Catch ex As Exception
        Throw ex
    Finally
        datOleReader.Close()
    End Try
End Function

   Public Function ExecuteQry(ByVal qry As String) As Boolean
    Dim cmdtext As New SqlCommand
    Try
        'con = cls.cnn #
        'cmdtext.Connection = con #
        cmdtext.Connection = CHECK_CONNECTION(con) ' #
        cmdtext.CommandText = qry
        cmdtext.CommandType = CommandType.Text
        cmdtext.ExecuteNonQuery()
        cmdtext.Dispose()
        cmdtext = Nothing
        ErrCode = True
    Catch ex As Exception
        'gErrMsg()
        ErrCode = False
        MsgBox(ex.Message.ToString)
    Finally
        'con.Close()
    End Try
End Function
Public Function ExecuteQry(ByVal qry As String, ByVal trans As SqlTransaction) As Boolean
    Dim cmdtext As New SqlCommand
    Try
        'con = cls.cnn #
        'cmdtext.Connection = con #
        cmdtext.Connection = CHECK_CONNECTION(con) ' #
        cmdtext.CommandText = qry
        cmdtext.CommandType = CommandType.Text
        cmdtext.Transaction = trans
        cmdtext.ExecuteNonQuery()
        cmdtext.Dispose()
        cmdtext = Nothing
    Catch ex As Exception
        Throw ex
        'gErrMsg()
        MsgBox(ex.Message.ToString)
    Finally
        'con.Close()
    End Try
End Function


Public Function ExecuteSelect(ByVal qry As String, ByVal trans As SqlTransaction) As SqlDataReader

    Dim cmdtext As New SqlCommand
    Try
        'con = cls.cnn #
        'cmdtext.Connection = con #
        cmdtext.Connection = CHECK_CONNECTION(con) ' #
        cmdtext.CommandText = qry
        cmdtext.CommandType = CommandType.Text
        cmdtext.Transaction = trans
        ExecuteSelect = cmdtext.ExecuteReader()
        Return ExecuteSelect
        cmdtext.Dispose()
        cmdtext = Nothing

    Catch ex As Exception
        Throw ex
        'MsgBox(ex.Message.ToString)
    Finally
        'clsexe.ExecuteSelect.Close()
        'con.Close()
    End Try
End Function


Public Function Executescalar(ByVal qry As String, ByVal trans As SqlTransaction)

    Dim cmdtext As New SqlCommand
    Try
        'con = cls.cnn #
        'cmdtext.Connection = con #
        cmdtext.Connection = CHECK_CONNECTION(con) ' #
        cmdtext.CommandText = qry
        cmdtext.CommandType = CommandType.Text
        cmdtext.Transaction = trans
        Executescalar = cmdtext.ExecuteScalar
        cmdtext.Dispose()
        cmdtext = Nothing

    Catch ex As Exception
        Throw ex
        ' MsgBox(ex.Message.ToString)
    Finally
        ' con.Close()
    End Try

End Function


Public Function ExecuteAdapter(ByVal qry As String, ByVal trans As SqlTransaction) As SqlDataAdapter
    Dim cmdtext As New SqlCommand
    Dim da As New SqlDataAdapter
    Try
        'con = cls.cnn #
        'cmdtext.Connection = con
        cmdtext.Connection = CHECK_CONNECTION(con) ' #
        cmdtext.CommandTimeout = 0
        cmdtext.CommandText = qry
        cmdtext.Transaction = trans
        cmdtext.CommandType = CommandType.Text
        da.SelectCommand = cmdtext
        Return da
        cmdtext.Dispose()
        cmdtext = Nothing
    Catch ex As Exception
        Throw ex
        'MsgBox(ex.Message.ToString)
    Finally
        ' con.Close()
    End Try
End Function


Public Function Executedataset(ByVal qry As String) As DataSet
    Dim cmdtext As New SqlCommand
    Dim ds As New DataSet
    Dim da As New SqlDataAdapter
    Try
        'con = cls.cnn #
        'cmdtext.Connection = con #
        cmdtext.Connection = CHECK_CONNECTION(con) ' #
        cmdtext.CommandText = qry
        cmdtext.CommandType = CommandType.Text
        da.SelectCommand = cmdtext
        da.Fill(ds)
        cmdtext.Dispose()
        cmdtext = Nothing
    Catch ex As Exception
        MsgBox(ex.Message.ToString)
    End Try
End Function

Public Function ExecuteProcedure(ByVal qry As String) As Boolean
    Dim cmdtext As New SqlCommand
    Try
        'con = cls.cnn #
        'cmdtext.Connection = con #
        cmdtext.Connection = CHECK_CONNECTION(con) ' #
        cmdtext.CommandText = qry
        cmdtext.CommandType = CommandType.StoredProcedure
        cmdtext.ExecuteNonQuery()
        cmdtext.Dispose()
        cmdtext = Nothing
    Catch ex As Exception
        MsgBox(ex.Message.ToString)
    End Try
End Function

Could you add some more detail here please? While I can appreciate the example, an explanation of some pieces would be nice. — JB King, Nov 23 '15 at 16:41

Prepared Statements in VB.NET

3 Answers3

Linked