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:
http://dev.mysql.com/downloads/mirror.php?id=405442
(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