[SQL] VB.NET에서 문 준비

VB.NET에서 문 준비

나는 vb.net 및 Microsoft SQL Server 2008에 정말 연결 문자열을 통해 데이터베이스에 연결하고 준비된 문을 실행하기위한 좋은 소스를 찾을 수있는 준비된 문에 새로운 오전. 누군가가 나에게 예를 표시하거나 내게는 도움이 될 수있는 자원을 가리 수 있을까요?

해결법

1. ==============================

   1.준비된 문은 트랜잭션 안에 아무것도하지만, 매개 변수가있는 SQL 명령 없습니다.

   준비된 문은 트랜잭션 안에 아무것도하지만, 매개 변수가있는 SQL 명령 없습니다.

   예를 들어,이 준비된 명령문은 다음과 같습니다

   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. ==============================

   2.다음은 몇 가지 간단한 예제 코드는 다음과 같습니다

   다음은 몇 가지 간단한 예제 코드는 다음과 같습니다

   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
   

   물론 당신은 가져 오기 System.Data과의 System.Data.SQLClient해야합니다.

3. ==============================

   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
   

from https://stackoverflow.com/questions/7351135/prepared-statements-in-vb-net by cc-by-sa and MIT license