风舞残阳 2008-4-2 13:44
.NET如何访问MySQL数据库
.NET的[wiki]数据库[/wiki]天然支持MSSQLServer，但是并非其他数据库不支持，而是[wiki]微软[/wiki]基于自身利益需要，在支持、营销上推自己的数据库产品;但是作为平台战略，他并非排斥其他数据库，而是参考java体系提出了一套数据库访问规范，让各个第三方进行开发，提供特定的驱动。
MySQL是免费的数据库，在[wiki]成本[/wiki]上具有无可替代的优势，但是目前来讲，并没有提供。微软把MySQL当作ODBC数据库，可以按照ODBC.Net规范进行访问，具体参考
http://www.microsoft.com/china/community/Columns/Luyan/6.mspx
而实际上，针对ODBC。Net的需要配置DSN的麻烦，而是出现了一个[wiki]开源[/wiki]的[wiki]系统[/wiki]MySQLDriverCS，对MySQL的开发进行了封装，实现了.net[wiki]环境[/wiki]下对于MySQL数据库系统的访问。
http://sourceforge.net/projects/mysqldrivercs/
通过阅读[wiki]源代码[/wiki]，我们看到MySQLDriverCS的思路是利用C函数的底层库来操纵数据库的，通常提供对MySQL数据库的访问的数据库的CDLL是名为libmySQL.dll的驱动文件，MySQLDriverCS作为一个.net库进行封装C风格的驱动。
具体如何进行呢?
打开工程后，我们看到其中有一个比较特殊的.cs文件CProto[wiki]type[/wiki]s.cs：
以下是引用片段：
#regionLICENSE
/*
MySQLDriverCS:AnC#driverforMySQL.
Copyright(c)2002ManuelLucasVi馻sLivschitz.
ThisfileispartofMySQLDriverCS.
MySQLDriverCSisfreesoftware;youcanredistributeitand/ormodify
itunderthetermsoftheGNUGeneralPublicLicenseaspublishedby
theFreeSoftwareFoundation;eitherversion2oftheLicense,or
(atyouroption)anylaterversion.
MySQLDriverCSisdistributedinthehopethatitwillbeuseful,
butWITHOUTANYWARRANTY;withouteventheimpliedwarrantyof
MERCHANTABILITYorFITNESSFORAPARTICULARPUR[wiki]POS[/wiki]E.Seethe
GNUGeneralPublicLicenseformoredetails.
YoushouldhavereceivedacopyoftheGNUGeneralPublicLicense
alongwithMySQLDriverCS;ifnot,writetotheFreeSoftware
Foundation,Inc.,59TemplePlace,Suite330,Boston,MA02111-1307USA
*/
#endregion
usingSystem;
usingSystem.Data;
usingSystem.Runtime.InteropServices;
namespaceMySQLDriverCS
{
//[StructLayout(LayoutKind.Sequential)]
publicclassMYSQL_FIELD_FACTORY
{
staticstringversion;
publicstaticIMYSQL_FIELDGetInstance()
{
if(version==null)
{
version=CPrototypes.GetClientInfo();
}
if(version.CompareTo("4.1.2-alpha")%26gt;=0)
{
returnnewMYSQL_FIELD_VERSION_5();
}
else
returnnewMYSQL_FIELD_VERSION_3();
}
}
publicinterfaceIMYSQL_FIELD
{
stringName{get;}
uintType{get;}
longMax_Length {get;}
}
///%26lt;summary%26gt;
///Fielddescriptor
///%26lt;/summary%26gt;
[StructLayout(LayoutKind.Sequential)]//"3.23.32",4.0.1-alpha
internalclassMYSQL_FIELD_VERSION_3:IMYSQL_FIELD
{
///%26lt;summary%26gt;
///Nameofcolumn
///%26lt;/summary%26gt;
publicstringname;
///%26lt;summary%26gt;
///Tableofcolumnifcolumnwasafield
///%26lt;/summary%26gt;
publicstringtable;
//publicstringorg_table; /*Orgtablenameiftablewasanalias*/
//publicstringdb; /*Databasefortable*/
///%26lt;summary%26gt;
///def
///%26lt;/summary%26gt;
publicstringdef;
///%26lt;summary%26gt;
///length
///%26lt;/summary%26gt;
publiclonglength;
///%26lt;summary%26gt;
///max_length
///%26lt;/summary%26gt;
publiclongmax_length;
///%26lt;summary%26gt;
///Divflags
///%26lt;/summary%26gt;
publicuintflags;
///%26lt;summary%26gt;
///Numberofdecimalsinfield
///%26lt;/summary%26gt;
publicuintdecimals;
///%26lt;summary%26gt;
///Typeoffield.Semysql_com.hfortypes
///%26lt;/summary%26gt;
publicuinttype;
///%26lt;summary%26gt;
///Name
///%26lt;/summary%26gt;
publicstringName
{
get{returnname;}
}
///%26lt;summary%26gt;
///Type
///%26lt;/summary%26gt;
publicuintType
{
get{returntype;}
}
///%26lt;summary%26gt;
///Max_Length
///%26lt;/summary%26gt;
publiclongMax_Length
{
get {returnmax_length;}
}
}
///%26lt;summary%26gt;
///Fielddescriptor
///%26lt;/summary%26gt;
[StructLayout(LayoutKind.Sequential)]
internalclassMYSQL_FIELD_VERSION_5:IMYSQL_FIELD
{
///%26lt;summary%26gt;
///Nameofcolumn
///%26lt;/summary%26gt;
publicstringname;
///%26lt;summary%26gt;
///Originalcolumnname,ifanalias
///%26lt;/summary%26gt;
publicstringorg_name;
///%26lt;summary%26gt;
///Tableofcolumnifcolumnwasafield
///%26lt;/summary%26gt;
publicstringtable;
///%26lt;summary%26gt;
///Orgtablenameiftablewasanalias
///%26lt;/summary%26gt;
publicstringorg_table;
///%26lt;summary%26gt;
///Databasefortable
///%26lt;/summary%26gt;
publicstringdb;
///%26lt;summary%26gt;
///Catalogfortable
///%26lt;/summary%26gt;
//publicstringcatalog;
///%26lt;summary%26gt;
///def
///%26lt;/summary%26gt;
publicstringdef;
///%26lt;summary%26gt;
///length
///%26lt;/summary%26gt;
publiclonglength;
///%26lt;summary%26gt;
///max_length
///%26lt;/summary%26gt;
publiclongmax_length;
///%26lt;summary%26gt;
///name_length
///%26lt;/summary%26gt;
//publicuintname_length;
///%26lt;summary%26gt;
///org_name_length
///%26lt;/summary%26gt;
publicuintorg_name_length;
///%26lt;summary%26gt;
///table_length
///%26lt;/summary%26gt;
publicuinttable_length;
///%26lt;summary%26gt;
///org_table_length
///%26lt;/summary%26gt;
publicuintorg_table_length;
///%26lt;summary%26gt;
///db_length
///%26lt;/summary%26gt;
publicuintdb_length;
///%26lt;summary%26gt;
///catalog_length
///%26lt;/summary%26gt;
publicuintcatalog_length;
///%26lt;summary%26gt;
///def_length
///%26lt;/summary%26gt;
publicuintdef_length;
///%26lt;summary%26gt;
///Divflags
///%26lt;/summary%26gt;
publicuintflags;
///%26lt;summary%26gt;
///Numberofdecimalsinfield
///%26lt;/summary%26gt;
publicuintdecimals;
///%26lt;summary%26gt;
///Characterset
///%26lt;/summary%26gt;
publicuintcharsetnr;
///%26lt;summary%26gt;
///Typeoffield.Semysql_com.hfortypes
///%26lt;/summary%26gt;
publicuinttype;
///%26lt;summary%26gt;
///Name
///%26lt;/summary%26gt;
publicstringName
{
get {returnname;}
}
///%26lt;summary%26gt;
///Type
///%26lt;/summary%26gt;
publicuintType
{
get {returntype;}
}
///%26lt;summary%26gt;
///Max_Length
///%26lt;/summary%26gt;
publiclongMax_Length
{
get {returnmax_length;}
}
}
//[StructLayout(LayoutKind.Explicit)]
publicenumenum_field_types
{
FIELD_TYPE_DECIMAL,FIELD_TYPE_TINY,
FIELD_TYPE_SHORT,FIELD_TYPE_LONG,
FIELD_TYPE_FLOAT,FIELD_TYPE_DOUBLE,
FIELD_TYPE_NULL, FIELD_TYPE_TIMESTAMP,
FIELD_TYPE_LONGLONG,FIELD_TYPE_INT24,
FIELD_TYPE_DATE, FIELD_TYPE_TIME,
FIELD_TYPE_DATETIME,FIELD_TYPE_YEAR,
FIELD_TYPE_NEWDATE,
FIELD_TYPE_ENUM=247,
FIELD_TYPE_SET=248,
FIELD_TYPE_TINY_BLOB=249,
FIELD_TYPE_MEDIUM_BLOB=250,
FIELD_TYPE_LONG_BLOB=251,
FIELD_TYPE_BLOB=252,
FIELD_TYPE_VAR_STRING=253,
FIELD_TYPE_STRING=254,
FIELD_TYPE_GEOMETRY=255
};
///%26lt;summary%26gt;
///Cprototypeswarpperformysqllib.
///%26lt;/summary%26gt;
internalclassCPrototypes
{
[DllImport("libmySQL.dll",EntryPoint="mysql_init")]
unsafepublicstaticexternvoid*mysql_init(void*must_be_null);
[DllImport("libmySQL.dll",EntryPoint="mysql_close")]
unsafepublicstaticexternvoidmysql_close(void*handle);
//BEGINADDITION2004-07-01BYAlexSeewald
//Enablesustocallmysql_optiontoactivatecompressionandtimeout
[DllImport("libmySQL.dll",EntryPoint="mysql_options")]
unsafepublicstaticexternvoidmysql_options(void*mysql,uintoption,uint*value);
//ENDADDITION2004-07-01ByAlexSeewald
[DllImport("libmySQL.dll",EntryPoint="mysql_real_connect")]
unsafepublicstaticexternvoid*mysql_real_connect(void*mysql,stringhost,stringuser,stringpasswd,stringdb,uintport,stringunix_socket,intclient_flag);
[DllImport("libmySQL.dll",EntryPoint="mysql_query")]
unsafepublicstaticexternintmysql_query(void*mysql,stringquery);
[DllImport("libmySQL.dll",EntryPoint="mysql_store_result")]
unsafepublicstaticexternvoid*mysql_store_result(void*mysql);
[DllImport("libmySQL.dll",EntryPoint="mysql_free_result")]
unsafepublicstaticexternvoidmysql_free_result(void*result);
[DllImport("libmySQL.dll",EntryPoint="mysql_errno")]
unsafepublicstaticexternuintmysql_errno(void*mysql);
[DllImport("libmySQL.dll",EntryPoint="mysql_error")]
unsafepublicstaticexternstringmysql_error(void*mysql);
[DllImport("libmySQL.dll",EntryPoint="mysql_field_count")]
unsafepublicstaticexternuintmysql_field_count(void*mysql);
[DllImport("libmySQL.dll",EntryPoint="mysql_affected_rows")]
unsafepublicstaticexternulongmysql_affected_rows(void*mysql);
[DllImport("libmySQL.dll",EntryPoint="mysql_num_fields")]
unsafepublicstaticexternuintmysql_num_fields(void*result);
[DllImport("libmySQL.dll",EntryPoint="mysql_num_rows")]
unsafepublicstaticexternulongmysql_num_rows(void*result);
[DllImport("libmySQL.dll",EntryPoint="mysql_fetch_field_direct")]
unsafepublicstaticexternIntPtrmysql_fetch_field_direct(void*result,uintfieldnr);
///%26lt;returns%26gt;Returnsastringthatrepresentstheclientlibraryversion%26lt;/returns%26gt;
[DllImport("libmySQL.dll",CharSet=System.Runtime.InteropServices.CharSet.Ansi,
EntryPoint="mysql_get_client_info",ExactSpelling=true)]
publicstaticexternstringGetClientInfo();
[DllImport("libmySQL.dll",EntryPoint="mysql_fetch_row")]
unsafepublicstaticexternIntPtrmysql_fetch_row(void*result);
[DllImport("libmySQL.dll",EntryPoint="mysql_select_db")]
unsafepublicstaticexternintmysql_select_db(void*mysql,stringdbname);
[DllImport("libmySQL.dll",EntryPoint="mysql_fetch_lengths")]
unsafepublicstaticexternUInt32*mysql_fetch_lengths(void*result);
}
} 基本上是将C风格的基础数据结构进行.net的重新定义，然后通过InteropServices进行访问。
具体如何利用这个库进行操作，可以参考其中的例子。
