Prepared Statements in VB.NET

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?

回答1:

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

回答2:

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.

回答3:

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