VB.Net/Database ADO.net/ODBC
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>