.net 访问mysql_实例讲解：.NET如何访问MySQL数据库

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

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

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

通过阅读源代码，我们看到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

*/

#endregion

using 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;}

}

///

/// Field descriptor

///

[StructLayout(LayoutKind.Sequential)]//"3.23.32", 4.0.1-alpha

internal class MYSQL_FIELD_VERSION_3: IMYSQL_FIELD

{

///

/// Name of column

///

public string name;

///

/// Table of column if column was a field

///

public string table;

//public string org_table;              /* Org table name if table was an alias */

//public string db;             /* Database for table */

///

/// def

///

public string def;

///

/// length

///

public long length;

///

/// max_length

///

public long max_length;

///

/// Div flags

///

public uint flags;

///

/// Number of decimals in field

///

public uint decimals;

///

/// Type of field. Se mysql_com.h for types

///

public uint type;

///

/// Name

///

public string Name

{

get{return name;}

}

///

/// Type

///

public uint Type

{

get{return type;}

}

///

/// Max_Length

///

public long Max_Length

{

get     {return max_length;}

}

}

///

/// Field descriptor

///

[StructLayout(LayoutKind.Sequential)]

internal class MYSQL_FIELD_VERSION_5: IMYSQL_FIELD

{

///

/// Name of column

///

public string name;

///

/// Original column name, if an alias

///

public string org_name;

///

/// Table of column if column was a field

///

public string table;

///

/// Org table name if table was an alias

///

public string org_table;

///

/// Database for table

///

public string db;

///

/// Catalog for table

///

//public string catalog;

///

/// def

///

public string def;

///

/// length

///

public long length;

///

/// max_length

///

public long max_length;

///

/// name_length

///

//public uint name_length;

///

/// org_name_length

///

public uint org_name_length;

///

/// table_length

///

public uint table_length;

///

/// org_table_length

///

public uint org_table_length;

///

/// db_length

///

public uint db_length;

///

/// catalog_length

///

public uint catalog_length;

///

/// def_length

///

public uint def_length;

///

/// Div flags

///

public uint flags;

///

/// Number of decimals in field

///

public uint decimals;

///

/// Character set

///

public uint charsetnr;

///

/// Type of field. Se mysql_com.h for types

///

public uint type;

///

/// Name

///

public string Name

{

get     {return name;}

}

///

/// Type

///

public uint Type

{

get     {return type;}

}

///

/// Max_Length

///

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

};

///

/// C prototypes warpper for mysqllib.

///

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 a string that represents the client library version

[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进行访问。

具体如何利用这个库进行操作，可以参考其中的例子。