Home Other projects Utilities The adodb-mysql project
ipv6 ready
The adodb-mysql project PDF Print
Utilities
Wednesday, 19 January 2011 17:56

ADODB-MYSQL is a simplified ADODB interface library for MySQL on Mono/.NET. The library can be used to port MS ADODB project to an Mono environment.

Click here to switch to the SourceForge project site..

Contents of this page:

What is adodb-mysql?

What is this project bringing?

What are the differences between adodb-mysql and MS-adodb?

What happens behind the screen?

 

What is adodb-mysql?

The adodb-mysql software was created to be able to port an existing ASP.NET project (written in VB.NET) to the Mono platform. To be able to port the software to Mono I needed an alternative for the 'adodb' library of Microsoft. It would be a lot of work to convert all existing code to the new .NET libraries using DataSet's and DataReaders.

So if you are planning to port your existing code to Mono or want to be able to host your code on both MS-IIS and Mono, and you don't want to change all of your code, adodb-mysql might be for you!

An example of using adodb in a VB.NET project shows how much the code is like the DAO VB-code of MS-access:

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

 

What is this project bringing?

The download of this project supply project files created in MS Visual Studio. The C# sourcefiles can also be compiled using Mono. Using the adodb-mysql library you coud find an alternative for using the adodb library of Microsoft.

What are the differences between adodb-mysql and MS-adodb?

An important note is that not all of MS-adodb library has been implemented in adodb-mysql. The main reason for this is that not everything of MS-adodb was used in my projects. The following table shows what functionality is available at the moment:

Included in adodb-mysql Not included in adodb-mysql

ADODB.Connection

ADODB.Recordset (updatable and forward-only)

ADODB.Fields

ADODB.Field

ADODB.Command (executing non parameter queries)

ADODB.Stream

ADODB.Command (executing parameter queries)

not all properties of included classes are implemented.

 

 

What happens behind the screen?

The adodb-mysql library makes use of the .NET data connector published bij Mysql AB. This library is based on the .NET System.Data class and uses e.g. DataSets and DataReaders. To make the code efficiënt, and reduce complexibility, adodb-mysql re-uses database connections and automaticly clone connection when multiple recordsets are openened on the same connection. When a new ADODB connection is openened the class will open a Mysql connection in parallel using the .NET dataconnector. This is the standard connection.

The ADODB.Recordset class opens a DataReader of a DataSet. The DataReader is used for updatable record set. This type of record set allows inserts, modifications and reads forward or backwards. A disadvantage of this type op recordset is that it takes more memory and performs slower. For minimum memory usages and high-speed datareads, a forward only recordset should be opened.  This type op recordset makes use of an MySqlDataReader. Because a connection can have only one open DataReader at a time adodb-mysql will automaticly open a new connection based on the default connection. This connection will be closed upon closing the recordset. To open a changable recordset the OpenRecordset function needs to be called with the cursor type CursorTypeEnum.adOpenKeyset. The cursor type is specified by the third parameter. Other cursortypes will open a read-only and forward-only recordset, this is also the default.

The ADODB.Command class uses a MySqlCommand behind the screen. This class does NOT support parameters at the moment. If this a feature that is worth to be added, please let it know!

The ADODB.Connection class also supplies an Execute command which enables the execution of queries. In important difference though is that the ADODB.Connection.Execute command does not return a recordset. The command is meant to be used to execute a query that is not returning data.

For retreiving a single value the extra function ADODB.Connection.ExecuteScalar is added. This function is not present in th Microsofts adodb.

Last Updated on Wednesday, 19 January 2011 22:54
 
Copyright © 2014 Frequency Tuner. All Rights Reserved.