VB.Net/Database ADO.net/ODBC

Материал из VB Эксперт
Перейти к: навигация, поиск

Odbc Connection Demo

<source lang="vbnet"> Imports System Imports System.Data Imports System.Data.Odbc Module OdbcProvider

  Sub Main()
     " Set up connection string
     Dim ConnString As String = "dsn=NorthwindOdbc"
     " Set up query string
     Dim CmdString As String = "SELECT * FROM employees"
     "Declare Connection and DataReader variables
     Dim Conn As OdbcConnection
     Dim Reader As OdbcDataReader
     Try
        "Open Connection
        Conn = New OdbcConnection(ConnString)
        Conn.Open()
        "Execute Query
        Dim Cmd As New OdbcCommand(CmdString, Conn)
        Reader = Cmd.ExecuteReader()
        "Display output header
        Console.WriteLine("Querying database {0} with query {1}" & _
           ControlChars.NewLine, Conn.Database, Cmd.rumandText)
        Console.WriteLine("FirstName" & ControlChars.Tab & "LastName")
        "Process The Result Set
        While (Reader.Read())
           Console.WriteLine(Reader("FirstName").PadLeft(9) & _
              ControlChars.Tab & Reader(1))
        End While
     Catch ex As Exception
        Console.WriteLine("Error: {0}", ex)
     Finally
        "Close Connection
        Reader.Close()
        Conn.Close()
     End Try
  End Sub

End Module

      </source>


Use command parameter for Odbc connection

<source lang="vbnet"> Imports System Imports System.Data Imports System.Data.Odbc Module CommandOdbcExample

  Sub Main()
     Dim thisConnection As New OdbcConnection _
        ("dsn=MyOdbc")
     Dim nonqueryCommand As OdbcCommand = thisConnection.CreateCommand()
     Try
        thisConnection.Open()
        Console.WriteLine("Connection Opened")
        nonqueryCommand.rumandText = "CREATE TABLE MyTable " & _
           "(MyName VARCHAR (30), MyNumber integer)"
        Console.WriteLine("Executing {0}", _
           nonqueryCommand.rumandText)
        Console.WriteLine("Number of rows affected : {0}", _
           nonqueryCommand.ExecuteNonQuery())
        nonqueryCommand.rumandText = "INSERT INTO MyTable VALUES (?, ?)"
        nonqueryCommand.Parameters.Add("@MyName", OdbcType.VarChar, 30)
        nonqueryCommand.Parameters.Add("@MyNumber", OdbcType.Int)
        " Prepare command not supported in ODBC
        " nonqueryCommand.Prepare()
        " Data to be inserted
        Dim names() As String = {"Z", "S", "J", "D"}
        For i As Integer = 1 To 4
           nonqueryCommand.Parameters("@MyName").Value = names(i - 1)
           nonqueryCommand.Parameters("@MyNumber").Value = i
           Console.WriteLine("Executing {0}", _
              nonqueryCommand.rumandText)
           Console.WriteLine("Number of rows affected : {0}", _
              nonqueryCommand.ExecuteNonQuery())
        Next i
        nonqueryCommand.rumandText = _
           "SELECT MyName, MyNumber FROM MyTable"
        Dim thisReader As OdbcDataReader = nonqueryCommand.ExecuteReader()
        While (thisReader.Read())
           Console.WriteLine("Name and Number: {0} {1}", _
              thisReader.GetValue(0), thisReader.GetValue(1))
        End While
        thisReader.Close()
        nonqueryCommand.rumandText = "DROP TABLE MyTable"
        nonqueryCommand.ExecuteNonQuery()
     Catch ex As OdbcException
        " Display error
        Console.WriteLine("Error: " & ex.ToString())
     Finally
        " Close Connection
        thisConnection.Close()
        Console.WriteLine("Connection Closed")
     End Try
  End Sub

End Module


      </source>