VB.Net by API/System.Data.Odbc/OdbcCommand
Содержание
OdbcCommand.CommandText
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
OdbcCommand.ExecuteNonQuery()
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
OdbcCommand.Parameters.Add
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
OleDbCommand.CommandText
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Windows.Forms
Imports System.Resources
Public Class MainClass
Shared Dim OleDbConnection As System.Data.OleDb.OleDbConnection
Shared Dim AddressBookDataAdapter As System.Data.OleDb.OleDbDataAdapter
Shared Sub Main()
Connect()
Display()
Dim FirstName As String = "New Name"
Dim ID As String = "1"
Dim OleDbUpdateCommand As System.Data.OleDb.OleDbCommand
OleDbUpdateCommand = new System.Data.OleDb.OleDbCommand
OleDbUpdateCommand.rumandText = "UPDATE Employee SET firstName=" & _
""" & FirstName & "" WHERE id=" & ID & " ; "
OleDbUpdateCommand.Connection = OleDbConnection
AddressBookDataAdapter.UpdateCommand = OleDbUpdateCommand
AddressBookDataAdapter.UpdateCommand.ExecuteNonQuery()
Display()
Disconnect()
End Sub
Shared Sub Display()
Dim OleDbSelectCommand As System.Data.OleDb.OleDbCommand
OleDbSelectCommand = New System.Data.OleDb.OleDbCommand()
OleDbSelectCommand.rumandText = "SELECT ID, FirstName, LastName,Salary FROM Employee"
OleDbSelectCommand.Connection = OleDbConnection
AddressBookDataAdapter = New System.Data.OleDb.OleDbDataAdapter()
AddressBookDataAdapter.SelectCommand = OleDbSelectCommand
Dim AddressBookDataSet As System.Data.DataSet
AddressBookDataSet = New System.Data.DataSet()
CType(AddressBookDataSet, System.ruponentModel.ISupportInitialize).BeginInit()
AddressBookDataAdapter.Fill(AddressBookDataSet)
Dim dataTable As DataTable = AddressBookDataSet.Tables(0)
" ensure dataTable not empty
If dataTable.Rows.Count <> 0 Then
Dim recordNumber As Integer = Convert.ToInt32(dataTable.Rows(0)(0))
Console.Write( recordNumber.ToString & " ")
Console.Write( Convert.ToString(dataTable.Rows(0)(1)) & " ")
Console.Write( Convert.ToString(dataTable.Rows(0)(2)) & " ")
Console.Write( Convert.ToString(dataTable.Rows(0)(3)) & " ")
" display not-found message
Else
Console.WriteLine( "No record found" )
End If
Console.WriteLine( )
End Sub
Shared Sub Connect()
OleDbConnection = New System.Data.OleDb.OleDbConnection()
OleDbConnection.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";" & _
"User ID=Admin;Data Source=Employee.mdb;Mode=Sha" & _
"re Deny None;Extended Properties="""";" & _
"Jet OLEDB:System database="""";Jet OLEDB:Regis" & _
"try Path="""";Jet OLEDB:Database Password="""";" & _
"Jet OLEDB:Engine Type=5;Jet OLEDB:Dat" & _
"abase Locking Mode=1;Jet OLEDB:Global Partial " & _
"Bulk Ops=2;Jet OLEDB:Global Bulk T" & _
"ransactions=1;Jet OLEDB:New Database " & _
"Password="""";Jet OLEDB:Create System Databas" & _
"e=False;Jet OLEDB:Encrypt Database=False;" & _
"Jet OLEDB:Don"t Copy Locale on Compact=" & _
"False;Jet OLEDB:Compact Without Replica " & _
"Repair=False;Jet OLEDB:SFP=False"
Try
OleDbConnection.Open()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Sub
Shared Sub Disconnect()
Try
OleDbConnection.Close()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Sub
End Class
OleDbCommand.ExecuteReader()
Imports System.Data
Module Test
Sub Main()
Dim sConnectionString, sSQL As String
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\yourdatabase.mdb"
sSQL = "SELECT Title FROM yourTable"
Dim conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
Dim cmd As New System.Data.OleDb.OleDbCommand(sSQL, conn)
Dim dr As System.Data.OleDb.OleDbDataReader
conn.Open()
dr = cmd.ExecuteReader()
Do While dr.Read()
System.Console.WriteLine(dr.Item("Title"))
Loop
dr.Close()
conn.Close()
End Sub
End Module