Using a set of structs for MySQL types is the right approach. I'll probably knock this out pretty quick so if anyone has any code for this, send it now.
Reggie > -----Original Message----- > From: Daniel Morgan [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 22, 2003 11:17 PM > To: Marlon Baculio > Cc: Mono-List; [EMAIL PROTECTED] > Subject: RE: [Mono] MySQL Managed Provider > > I would follow the model like System.Data.SqlTypes is for Microsoft SQL > Server types. Except, have a namespace Mono.Data.MySqlTypes which holds > types like the ones that MySQL has, such as, MySqlInt for int, and > MySqlDateTime for datetime. > > As far as Type coersion, the ODBC.net provider probably keeps the data as > a > string until you try to use it, such as, GetString or GetInt32 or > GetInt16. > > Have you tried using something like the ProviderFactory found in > Mono.Data? > > Here is some sample code: > > // test.cs > using System; > using System.Data; > > public class Test { > public static void Main(string[] args) { > IDbConnection conn = > ProviderFactory.CreateConnectionFromConfig("PubsConnStr"); > Console.WriteLine("Open Connection"); > conn.Open(); > IDbCommand cmd = conn.CreateCommand(); > cmd.CommandText = "select somecolumn from sometable"; > IDataReader reader = cmd.ExecuteReader(); > reader.Read(); > Console.WriteLine("SomeColumn: " + reader["somecolumn"].ToString()); > } > } > > Or you could use: > IDbConneciton conn = > ProviderFactory.Provider["Mono.Data.MySql"].CreateConnection(); > conn.ConnectionString = "Server=localhost;Database=test;User > ID=mysql;Password=mysecret"; > conn.Open(); > > I have provided a modified test.exe.config for you. > > <?xml version="1.0" encoding="utf-8" ?> > <configuration> > <configSections> > <sectionGroup name="mono.data"> > <section name="providers" > type="Mono.Data.ProviderSectionHandler,Mono.Data" /> > </sectionGroup> > </configSections> > <appSettings> > <add key="MySqlOdbc" > value="factory=System.Data.Odbc;DSN=myodbcdsn;UID=someuser;PWD=somepass" > /> > <add key="MonoMySql" > value="factory=Mono.Data.MySql;Server=localhost;Database=test;User > ID=mysql;Password=myscret" /> > <add key="MySqlNet" > value="factory=ByteFX.Data.MySQLClient;Server=localhost;Database=test;Us er > ID=mysql;Password=myscret" /> > </appSettings> > <mono.data> > <providers> > <provider > name="System.Data.Odbc" > connection="System.Data.Odbc.OdbcConnection" > adapter="System.Data.Odbc.OdbcDataAdapter" > command="System.Data.Odbc.OdbcCommand" > assembly="odbc" > description="ODBC (Mono)" /> > <provider name="Mono.Data.MySql" > connection="Mono.Data.MySql.MySqlConnection" > adapter="Mono.Data.MySql.MySqlDataAdapter" > command="Mono.Data.MySql.MySqlCommand" > assembly="Mono.Data.MySql" > description="MySQL (Mono)" /> > <provider name="ByteFX.Data.MySQLClient" > > connection="ByteFX.Data.MySQLClient.MySQLConnection" > adapter="ByteFX.Data.MySQLClient.MySQLDataAdapter" > command="ByteFX.Data.MySQLClient.MySQLCommand" > assembly="ByteFX.Data" > description="MySQL (ByteFX)" /> > </providers> > </mono.data> > </configuration> > > -----Original Message----- > From: Marlon Baculio [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 22, 2003 11:25 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: RE: [Mono] MySQL Managed Provider > > > Hi Daniel, > > All my code in the data layer is using the generic interfaces (such as > IDataReader) as opposed to provider-specific classes (except when > instantiating a concrete provider class, in which case, what I do is > create > an alias using the "using" C# keyword, so that my code just refers to the > (abstracted) alias then I just switch back-and-forth between ODBC.NET and > MySQLNet by changing the alias). > > Since my code has been coded around ODBC.NET, the problems I find are > compatibility issues, for example: > > 0. [enhancement] In connection string, support for "UID" in addition to > "user id" > > 1. [bugs] parsing value for MySQLFieldType.BYTE should use Byte.Parse() > and > not Int16.Parse() > > 2. ODBC.NET is more forgiving for type coercion. For example, you can call > IDataReader.GetString() against an integral type, or you can call > IDataReader.GetInt32() against a 16-bit column. > > 3. I'm also worried about how MySQLField is storing field values as > objects. > This will require a lot of boxing and unboxing when value is passed to the > client-code, specially if we want to support more relaxed type coercion > (although the cost of the boxing/unboxing would be negligible compared to > the overall SQL call, but I'm a performance freak so I tend to be paranoid > :). My recommendation is to create an abstract class for a field value, > then > derive classes for each type, for example: > > abstract class MySQLFieldValue > { > ... > public virtual byte GetByte(); > public virtual Int32 GetInt32(); > ... > }; > > class MySQLByteFieldValue : MySQLFieldValue > { > public MySQLByteFieldValue(byte[] data) > { > ... > } > public override byte GetByte() > { > return _value; > } > public override Int32 GetInt32() > { > // no boxing required, while being type-relaxed > return /*(Int32)*/ _value; > } > > private byte _value; > }; > > 4. I feel confident on using MySQLNet because of its performance and the > fact that I can fix the code myself being open source. It would be nice > though to support connection pooling. Maybe we can create a simple > ObjectPool that we can use in MySQLConnection.Open() and .Close(). > > > (I hope Reggie is reading this) I think what I'm going to do is continue > making the changes on my copy of the MySQLNet code and then send the > original and modified code to Reggie so he can do a diff and decide if my > changes deserve to be included. > > I've been frequenting the "MySQL forum section" at > http://www.asp.net/Forums/ShowForum.aspx?tabindex=1&ForumID=56 > Maybe we can get some help/idea from there. > > Thank you so much Daniel! I'm been following Mono for quite sometime now, > hoping that eventually I will use that to run my ASP.NET web site on > Linux. > > > Marlon Baculio > [EMAIL PROTECTED] > > > >From: "Daniel Morgan" <[EMAIL PROTECTED]> > >To: "Marlon Baculio" <[EMAIL PROTECTED]> > >CC: "Reggie Burnett" <[EMAIL PROTECTED]> > >Subject: RE: [Mono] MySQL Managed Provider > >Date: Wed, 22 Jan 2003 21:57:22 -0500 > > > >Hi Marlon, > > > >Reggie Burneet is the lead developer of MySQLNet. He would be glad to > have > >patches to the provider. Can you be more specific about the problems you > >are having. Can you provide simple test cases? > > > >Same thing with the provider in Mono - patches are welcome. > > > >Mono.Data.MySql is in maintenance mode and is mainly meant to work on > Mono. > >Try using MySQLNet which will become the choice for Mono in the future > >since > >it works on Mono and .NET, faster, > >and no dependencies on a client library. > > > >Have you been using the various interfaces in ADO.NET, such as, > >IDbConnection, IDbCommand, IDataReader, IDbDataAdapter, etc. ? > > > >IDbConnection dbcon = new MySqlConnection(); > >dbcon.ConnectionString = "Server=localhost;Database=test;User > >ID=mysql;Password=mysecret"; > >IDbCommand dbcmd = dbcon.CreateCommand(); > >dbcmd.CommandText = "select * from sometable"; > >IDataReader reader = dbcmd.ExecuteReader(); > > > >These interfaces provide a generic way to use different providers. Also, > >there is the ProviderFactory and DataTools found in Mono.Data.dll which > can > >be used to dynamically create connections, commands, parameters, and > >adapters via some configuration. > >Take a look at the test for it at mcs/class/Mono.Data/Test/test.cs > >which uses a .config file named test.exe.config > >for various app settings and providers. > > > >Daniel > > > >-----Original Message----- > >From: Marlon Baculio [mailto:[EMAIL PROTECTED]] > >Sent: Wednesday, January 22, 2003 1:09 AM > >To: [EMAIL PROTECTED] > >Subject: [Mono] MySQL Managed Provider > > > > > >Hi Daniel, > > > >(Please excuse me if this e-mail or this address is inappropriate.) > > > >I've been checking out MySQLNet from ByteFX. I've heard you are also > >writing > >a separate MySQL Managed Provider for Mono. > > > >I'm writing a web site that uses MySQL using ODBC.NET. I am not satisfied > >with the performance so I am looking for alternatives. MySQLNet from > ByteFX > >looks so promising but there's a few problems with it. My ultimate test > for > >ADO.NET compatibility (at least for my site) is to able to switch between > >ODBC.NET and MySQLNet (or any MySQL Provider) without a problem like > this: > > > >using DBConnection = Microsoft.Data.Odbc.OdbcConnection; > >using DBCommand = Microsoft.Data.Odbc.OdbcCommand; > > > >//using DBConnection = ByteFX.Data.MySQLClient.MySQLConnection; > >//using DBCommand = ByteFX.Data.MySQLClient.MySQLCommand; > > > > > >In fact, I've been making bug fixes/adjustments to my copy of the ByteFX > >code until I get a word from Reggie Burnette. > > > >If I don't get a response from him, I wish to ask you the following > >question: > > > >0. Any timeline or chance your provider will use pure C# to talk directly > >with the MySQL Server? > >1. Any immediate plans for connection pooling? > >2. Any chance I could contribute? (example: for ByteFX, I wish to make it > >more forgiving on type coercions like ODBC.NET, and less boxing/unboxing) > > > >So, basically, I'm torn as to which project I would use in my site :-) > > > >Thank you!!! > > > >Marlon Baculio > >[EMAIL PROTECTED] > > > > > > > >_________________________________________________________________ > >Add photos to your e-mail with MSN 8. Get 2 months FREE*. > >http://join.msn.com/?page=features/featuredemail > > > _________________________________________________________________ > Protect your PC - get McAfee.com VirusScan Online > http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 _______________________________________________ Mono-list maillist - [EMAIL PROTECTED] http://lists.ximian.com/mailman/listinfo/mono-list
