实例讲解:.NET如何访问MySQL数据库

80酷酷网    80kuku.com

  mysql|访问|数据|数据库

  .NET的数据库天然支持MSSQLServer,但是并非其他数据库不支持,而是微软基于自身利益需要,在支持、营销上推自己的数据库产品;但是作为平台战略,他并非排斥其他数据库,而是参考java体系提出了一套数据库访问规范,让各个第三方进行开发,提供特定的驱动。

  MySQL是免费的数据库,在成本上具有无可替代的优势,但是目前来讲,并没有提供。微软把MySQL当作ODBC数据库,可以按照ODBC.Net规范进行访问,具体参考

  http://www.microsoft.com/china/community/Columns/Luyan/6.mspx

  而实际上,针对ODBC。Net的需要配置DSN的麻烦,而是出现了一个开源的系统MySQLDriverCS,对MySQL的开发进行了封装,实现了.net环境下对于MySQL数据库系统的访问。

  http://sourceforge.net/projects/mysqldrivercs/

  通过阅读源代码,我们看到MySQLDriverCS的思路是利用C函数的底层库来操纵数据库的,通常提供对MySQL数据库的访问的数据库的C DLL是名为libmySQL.dll的驱动文件,MySQLDriverCS作为一个.net库进行封装C风格的驱动。

  具体如何进行呢?

  打开工程后,我们看到其中有一个比较特殊的.cs文件CPrototypes.cs:

#region LICENSE/*     MySQLDriverCS: An C# driver for MySQL.     Copyright (c) 2002 Manuel Lucas Vi馻s Livschitz.      This file is part of MySQLDriverCS.     MySQLDriverCS is free software; you can redistribute it and/or modify    it under the terms of the GNU General Public License as published by    the Free Software Foundation; either version 2 of the License, or    (at your option) any later version.     MySQLDriverCS is distributed in the hope that it will be useful,    but WITHOUT ANY WARRANTY; without even the implied warranty of    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the    GNU General Public License for more details.     You should have received a copy of the GNU General Public License    along with MySQLDriverCS; if not, write to the Free Software    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA*/#endregionusing System;using System.Data;using System.Runtime.InteropServices;namespace MySQLDriverCS{     //[StructLayout(LayoutKind.Sequential)]     public class MYSQL_FIELD_FACTORY     {         static string version;         public static IMYSQL_FIELD GetInstance()         {               if (version==null)               {                   version = CPrototypes.GetClientInfo();              }              if (version.CompareTo("4.1.2-alpha")>=0)               {                   return new MYSQL_FIELD_VERSION_5();              }              else                    return new MYSQL_FIELD_VERSION_3();         }     }     public interface IMYSQL_FIELD     {         string Name{get;}         uint Type{get;}         long Max_Length     {get;}     }     ///<summary>     /// Field descriptor     ///</summary>     [StructLayout(LayoutKind.Sequential)]//"3.23.32", 4.0.1-alpha     internal class MYSQL_FIELD_VERSION_3: IMYSQL_FIELD      {         ///<summary>         /// Name of column         ///</summary>         public string name;                   ///<summary>         /// Table of column if column was a field         ///</summary>         public string table;                    //public string org_table;              /* Org table name if table was an alias */          //public string db;             /* Database for table */         ///<summary>         /// def         ///</summary>         public string def;                     ///<summary>         /// length         ///</summary>         public long length;               ///<summary>         /// max_length         ///</summary>         public long max_length;              ///<summary>         /// Div flags         ///</summary>         public uint flags;                  ///<summary>         /// Number of decimals in field          ///</summary>         public uint decimals;               ///<summary>         /// Type of field. Se mysql_com.h for types          ///</summary>         public uint type;           ///<summary>         /// Name         ///</summary>         public string Name         {              get{return name;}         }         ///<summary>         /// Type         ///</summary>         public uint Type         {              get{return type;}         }         ///<summary>         /// Max_Length         ///</summary>         public long Max_Length         {              get     {return max_length;}         }     }       ///<summary>     /// Field descriptor     ///</summary>     [StructLayout(LayoutKind.Sequential)]     internal class MYSQL_FIELD_VERSION_5: IMYSQL_FIELD      {         ///<summary>         /// Name of column          ///</summary>         public string name;               ///<summary>         /// Original column name, if an alias         ///</summary>         public string org_name;              ///<summary>         /// Table of column if column was a field         ///</summary>         public string table;               ///<summary>         /// Org table name if table was an alias         ///</summary>         public string org_table;              ///<summary>         /// Database for table         ///</summary>         public string db;                      ///<summary>         /// Catalog for table         ///</summary>          //public string catalog;                    ///<summary>         /// def         ///</summary>         public string def;                     ///<summary>         /// length         ///</summary>         public long length;               ///<summary>          /// max_length         ///</summary>         public long max_length;              ///<summary>         /// name_length         ///</summary>          //public uint name_length;         ///<summary>         /// org_name_length         ///</summary>         public uint org_name_length;         ///<summary>         /// table_length         ///</summary>         public uint table_length;         ///<summary>         /// org_table_length         ///</summary>         public uint org_table_length;         ///<summary>         /// db_length         ///</summary>         public uint db_length;         ///<summary>         /// catalog_length         ///</summary>         public uint catalog_length;         ///<summary>         /// def_length         ///</summary>         public uint def_length;         ///<summary>         /// Div flags         ///</summary>         public uint flags;                  ///<summary>         /// Number of decimals in field         ///</summary>         public uint decimals;               ///<summary>         /// Character set         ///</summary>         public uint charsetnr;              ///<summary>         /// Type of field. Se mysql_com.h for types         ///</summary>         public uint type;           ///<summary>         /// Name         ///</summary>         public string Name         {              get     {return name;}         }         ///<summary>         /// Type         ///</summary>         public uint Type         {              get     {return type;}         }         ///<summary>         /// Max_Length         ///</summary>         public long Max_Length         {              get     {return max_length;}         }     }       //[StructLayout(LayoutKind.Explicit)]     public enum enum_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      };      ///<summary>     /// C prototypes warpper for mysqllib.     ///</summary>     internal class CPrototypes     {          [ DllImport( "libmySQL.dll", EntryPoint="mysql_init" )]         unsafe public static extern void* mysql_init(void* must_be_null);          [ DllImport( "libmySQL.dll", EntryPoint="mysql_close" )]         unsafe public static extern void mysql_close(void* handle);                  // BEGIN ADDITION 2004-07-01 BY Alex Seewald         // Enables us to call mysql_option to activate compression and timeout          [ DllImport( "libmySQL.dll", EntryPoint="mysql_options" )]               unsafe public static extern void mysql_options(void* mysql, uint option, uint *value);         // END ADDITION 2004-07-01 By Alex Seewald            [ DllImport( "libmySQL.dll", EntryPoint="mysql_real_connect" )]         unsafe public static extern void* mysql_real_connect(void* mysql, string host, string user, string passwd, string db, uint port, string unix_socket, int client_flag);          [ DllImport( "libmySQL.dll", EntryPoint="mysql_query" )]         unsafe public static extern int mysql_query(void*mysql, string query);          [ DllImport( "libmySQL.dll", EntryPoint="mysql_store_result" )]         unsafe public static extern void *mysql_store_result(void *mysql);          [ DllImport( "libmySQL.dll", EntryPoint="mysql_free_result" )]         unsafe public static extern void mysql_free_result(void*result);          [ DllImport( "libmySQL.dll", EntryPoint="mysql_errno" )]         unsafe public static extern uint mysql_errno(void*mysql);          [ DllImport( "libmySQL.dll", EntryPoint="mysql_error" )]         unsafe public static extern string mysql_error(void*mysql);          [ DllImport( "libmySQL.dll", EntryPoint="mysql_field_count" )]         unsafe public static extern uint mysql_field_count(void*mysql);          [ DllImport( "libmySQL.dll", EntryPoint="mysql_affected_rows" )]         unsafe public static extern ulong mysql_affected_rows(void*mysql);          [ DllImport( "libmySQL.dll", EntryPoint="mysql_num_fields" )]         unsafe public static extern uint mysql_num_fields(void*result);          [ DllImport( "libmySQL.dll", EntryPoint="mysql_num_rows" )]         unsafe public static extern ulong mysql_num_rows(void *result);          [ DllImport( "libmySQL.dll", EntryPoint="mysql_fetch_field_direct" )]         unsafe public static extern IntPtr mysql_fetch_field_direct(void*result, uint fieldnr);           ///<returns>Returns a string that represents the client library version</returns>          [DllImport("libmySQL.dll",CharSet=System.Runtime.InteropServices.CharSet.Ansi,          EntryPoint="mysql_get_client_info", ExactSpelling=true)]         public static extern string GetClientInfo();           [ DllImport( "libmySQL.dll", EntryPoint="mysql_fetch_row" )]         unsafe public static extern IntPtr mysql_fetch_row(void*result);          [ DllImport( "libmySQL.dll", EntryPoint="mysql_select_db" )]         unsafe public static extern int mysql_select_db(void*mysql,string dbname);          [ DllImport( "libmySQL.dll", EntryPoint="mysql_fetch_lengths" )]         unsafe public static extern UInt32 *mysql_fetch_lengths(void*result);      }}

  基本上是将C风格的基础数据结构进行.net的重新定义,然后通过InteropServices进行访问。   具体如何利用这个库进行操作,可以参考其中的例子。

分享到
  • 微信分享
  • 新浪微博
  • QQ好友
  • QQ空间
点击: