C#访问MySQL数据库的方法

(1)首先需要下载C#访问MySQL数据库的ADO.NET驱动程序

下载地址为：

http://dev.mysql.com/downloads/connector/net/6.0.html

我下载的版本为： mysql-connector-net-6.3.8.msi

下载地址如下url:

(2)安装mysql-connector-net

然后直接在Windows操作系统安装 mysql-connector-net-6.3.8.msi

默认是安装在C盘：

C:\Program Files\MySQL\MySQL Connector Net 6.3.8\Assemblies

v2.0

v4.0

安装完后我选择的是v2.0版本的

然后在应用工程中引用组件MySQL.Data.dll

(3)封装数据库访问组件DbConnectionMySQL

/// <summary> /// MySQL数据库 /// 版本 mysql-connector-net-6.3.8.msi /// vp:hsg /// create date:2012-02-28 /// </summary> [Serializable] public class DbConnectionMySQL : DbConnectionWrapper { public DbConnectionMySQL(string pConnectionString) : base(pConnectionString) { this.m_dbconn = new MySqlConnection(pConnectionString); this.m_DbConnState = DbConnState.Free; } //-- public override DbDataAdapter GetDbDataAdapter() { return new MySqlDataAdapter(); } public override DbDataAdapter GetDbDataAdapter(DbCommand dbCommand) { return new MySqlDataAdapter(dbCommand as MySqlCommand); } public override DbCommand GetDbCommand() { return new MySqlCommand(); } public override DbConnection GetDbConnection() { return new MySqlConnection(); } public override DbCommandBuilder GetDbCommandBuilder() { return new MySqlCommandBuilder(); } public override DataProviderType GetCurrentDataProviderType() { return DataProviderType.Sql; } public override bool IsExistsTable(string TableName, string UserName) { #region information bool rbc = false; //TABLES表中去查询 table_name string dSql = "select * from TABLES where table_name='" + TableName + "'"; DataSet ds = this.ExecuteDataSet(dSql); if (ds != null) { if (ds.Tables[0].Rows.Count > 0) { rbc = true; } else { rbc = false; } } else { rbc = false; } return rbc; #endregion } public override bool IsExistsField(string FieldName, string TableName) { #region information bool rbc = false; string dSql = ""; dSql = "select * from " + TableName + " where 1<>1"; DataSet ds = this.ExecuteDataSet(dSql); if (ds != null) { DataTable dt = ds.Tables[0]; for (int j = 0; j < dt.Columns.Count; j++) { if (dt.Columns[j].ColumnName.ToString().ToUpper() == FieldName.ToString().ToUpper()) { rbc = true; goto Return_End; } } dt.Dispose(); dt = null; } ds.Dispose(); ds = null; Return_End: return rbc; #endregion } public override char ParameterChar { get { return ':'; //SQLite的参数符号为: } } public override DbParameter CreateParameter(string name, object value) { return new MySqlParameter(name, value); } public override DbParameter CreateParameter(string name) { DbParameter dbp = new MySqlParameter(); dbp.ParameterName = name; return dbp; } public override DbParameter CreateParameter(string name, DbType dbtype, object value) { DbParameter dbp = new MySqlParameter(); dbp.ParameterName = name; dbp.Value = value; dbp.DbType = dbtype; return dbp; } public override DbParameter CreateParameter(string name, DbType dbtype, int size, object value) { DbParameter dbp = new MySqlParameter(); dbp.ParameterName = name; dbp.Value = value; dbp.DbType = dbtype; dbp.Size = size; return dbp; } }

(4)客户端访问测试开发实例

public void TestCShape_MySQL() { string constr = "server=localhost;User Id=root;password=root;Database=xp_users"; DbConnectionWrapper dbw = new DbConnectionMySQL(constr); bool rbc=dbw.TestConnection(); this.Context.Response.Write(rbc); string x = ""; //删除语句 x = "delete from xp_users"; if (dbw.ExecuteQuery(x) > 0) { this.Context.Response.Write("删除语句成功!下面是SQL语句<br>" + x); } //插入语句 x = "insert into xp_users(gid,uid,uname,sex,email,pwd) values('"; x += "1','hsg77','何XXX',1,'hsg77@163.com','1')"; if (dbw.ExecuteQuery(x) > 0) { this.Context.Response.Write("插入语句成功!下面是SQL语句<br>"+x); } //查询语句 DataTable dt = dbw.ExecuteDataTable("select * from xp_users"); if (dt != null && dt.Rows.Count > 0) { this.Context.Response.Write("<br>用户数:"+dt.Rows.Count); } if (dt != null) { dt.Dispose(); dt = null; } dbw.Dispose(); dbw = null; }

----the---end---

create date:2012-02-28