|
Imports ADODB
Module adodbtestvb
'Test application to demonstrate adodb-mysql using VB.Net code
'/// Test application to demonstrate adodb-mysql using C# code
'/// Needed for this code to work:
'/// Install mysql
'/// Create account 'test' with password 'test' and assign database privileges
'/// for database 'test' or assign global database privileges for the account 'test'
Sub Main()
Dim MyCon As New ADODB.Connection()
Dim strProvider As String="driver={MySQLODBC3.51Driver};server=localhost;UID=test;pwd=test;PORT=3306;OPTION=3;STMT=;"
MyCon.CursorLocation = ADODB.CursorLocationEnum.adUseClient
MyCon.ConnectionString = strProvider
MyCon.Open()
'Create database
MyCon.Execute("create database if not exists `test`;")
MyCon.DefaultDatabase = "test"
'(Re)Create table
MyCon.Execute("DROP TABLE IF EXISTS `testtable`;")
MyCon.Execute("CREATE TABLE `testtable` (`ID` int(11) NOT NULL auto_increment,`Name` varchar(255) default NULL,`Value` varchar(255) default NULL, PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;")
'Fill Table, CursorTypeEnum.adOpenKeyset needs to be set to get an updatable recordset
Dim MyRs As New ADODB.Recordset()
MyRs.Open("select ID, Name, Value from testtable", MyCon, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic)
MyRs.AddNew()
MyRs("Name").Value = "Platform"
MyRs("Value").Value = "Mono"
MyRs.Update()
With MyRs
.AddNew()
.Fields("Name").Value = "Libary"
.Fields("Value").Value = "ADODB"
.Update()
End With
MyRs.Close()
'Update value of record ID 1, CursorTypeEnum.adOpenKeyset needs to be set to get an updatable recordset
MyRs = New ADODB.Recordset()
MyRs.Open("select ID, Name, Value from testtable where id=1", MyCon, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic)
With MyRs
.Fields("Value").Value = "Multi"
.Update()
End With
MyRs.Close()
'Delete last record, CursorTypeEnum.adOpenKeyset needs to be set to get an updatable recordset
MyRs = New ADODB.Recordset()
MyRs.Open("select ID, Name, Value from testtable", MyCon, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic)
MyRs.MoveLast()
MyRs.Delete()
MyRs.Close()
'Forward read, Default a forward only datareader is used to ensure performance
MyRs = New ADODB.Recordset()
MyRs.Open("select ID, Name, Value from testtable", MyCon)
Do While (Not MyRs.EOF)
Console.WriteLine("ID: {0}, Name: {1}, Value: {2}", New Object() {MyRs(0).Value, MyRs(1).Value, MyRs(2).Value})
MyRs.MoveNext()
Loop
MyRs.Close()
MyCon.Close()
End Sub
End Module
|
Other projects 
