C#访问MySQL数据库的方法

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