请问大家有C#连接Mysql的类似于DataClass.cs的操作数据库类库文件吗？

[liguokun]

请问大家有C#连接Mysql的类似于DataClass.cs的操作数据库类库文件吗？
之前用C#操作SQL Server数据库开发了一个c/s程序，觉得里面的DataClass.cs文件很好用，现在想把数据库改成Mysql，大家用现成的操作Mysql的DataClass.cs文件吗？DataClass.cs操作SQL Server数据库 文件代码如下：


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace lgkznlTaobao.DBUtility
{
public class DataClass
{
#region 自定义变量

public static SqlConnection My_con; //定义一个SqlConnection类型的公共变量My_con，用于判断数据库是否连接成功

//家里SONY电脑换了固态硬盘后重新安装系统及vs2010和sql2008,登录SQL2008语句：
public static string M_str_sqlcon = "Data Source =WIN-5D4427PFSSU;Initial Catalog = myTB;User Id = lgk;Password =12345m";

#endregion

#region 建立数据库连接
/// <summary>
/// 建立数据库连接.
/// </summary>
/// <returns>返回SqlConnection对象</returns>
public SqlConnection getcon()
{
My_con = new SqlConnection(M_str_sqlcon); //用SqlConnection对象与指定的数据库相连接
My_con.Open(); //打开数据库连接
return My_con; //返回SqlConnection对象的信息
}
#endregion


#region 关闭数据库连接
/// <summary>
/// 关闭于数据库的连接
/// </summary>
public int con_close()
{
int n = 0;
if (My_con.State == ConnectionState.Open) //判断是否打开与数据库的连接
{
My_con.Close(); //关闭数据库的连接
My_con.Dispose(); //释放My_con变量的所有空间
n = 0;
}
else
n = 1;
return n;
}
#endregion

#region 创建DataSet对象
/// <summary>
/// 创建一个DataSet对象
/// </summary>
/// <param name="M_str_sqlstr">SQL语句</param>
/// <param name="M_str_table">表名</param>
/// <returns>返回DataSet对象</returns>
public DataSet getDataSet(string SQLstr, string tableName)
{
getcon(); //打开与数据库的连接
SqlDataAdapter SQLda = new SqlDataAdapter(SQLstr, My_con); //创建一个SqlDataAdapter对象，并获取指定数据表的信息
DataSet My_DataSet = new DataSet(); //创建DataSet对象
SQLda.Fill(My_DataSet, tableName); //通过SqlDataAdapter对象的的Fill()方法，将数据表信息添加到DataSet对象中
con_close(); //关闭数据库的连接
return My_DataSet; //返回DataSet对象的信息
}
#endregion

#region 执行SqlCommand命令
/// <summary>
/// 执行SqlCommand
/// </summary>
/// <param name="M_str_sqlstr">SQL语句</param>
public void getsqlcom(string SQLstr)
{
getcon(); //打开与数据库的连接
SqlCommand SQLcom = new SqlCommand(SQLstr, My_con); //创建一个SqlCommand对象，用于执行SQL语句
SQLcom.ExecuteNonQuery(); //执行SQL语句
SQLcom.Dispose(); //释放所有空间
con_close(); //调用con_close()方法，关闭与数据库的连接
}

public int getsqlcom1(string SQLstr)
{
getcon(); //打开与数据库的连接
SqlCommand SQLcom = new SqlCommand(SQLstr, My_con); //创建一个SqlCommand对象，用于执行SQL语句
return SQLcom.ExecuteNonQuery(); //执行SQL语句
SQLcom.Dispose(); //释放所有空间
con_close(); //调用con_close()方法，关闭与数据库的连接
}


#endregion


}
}

[mirrorspace]

C#连接MYSQL的实现本来就有.将sqlserver ado.net换成mysql的就行.其它套路一样的

[键盘敲出字]

MySql.Data.dll里面有个帮助类MySqlHelper

[娃都会打酱油了]

用的Dapper，基础的使用例子NotificationRepository基本都包括了，只是这里的事物用的是DTC，也就是TranscationScope，不是直接数据库的，但Dapper自身支持

[娃都会打酱油了]

/// <summary>
    /// base repository
    /// </summary>
    public class BaseRepository
    {
        #region Constants
        public static readonly int DefaultSqlCommandTimeout = 10;
        public static readonly int LongSqlCommandTimeout = 15;
        protected static readonly int TableAccountTradingStatementsSplitCount = 32;
        #endregion

        #region private fields
        private readonly string _connectionName;
        private readonly int _commandTimeout = 2;
        const int _sharding = 32;
        #endregion

        #region Ctor
        protected BaseRepository(string connectionName= "Default")
        {
            _connectionName = connectionName;
        }
        #endregion

        /// <summary>
        /// 异步的sqlconnection管理
        /// 参考 http://www.joesauve.com/async-dapper-and-async-sql-connection-management/
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="getData"></param>
        /// <returns></returns>
        protected async Task<T> WithConnection<T>(Func<IDbConnection, Task<T>> getData)
        {
            try
            {
                var connectionString = ConfigurationManager.ConnectionStrings[this._connectionName].ConnectionString;
                using (var connection = new MySqlConnection(connectionString))
                {
                    try
                    {
                        await connection.OpenAsync(); // Asynchronously open a connection to the database
                        return await getData(connection);
                        // Asynchronously execute getData, which has been passed in as a Func<IDBConnection, Task<T>>
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex);
                        throw;
                    }
                }
            }
            catch (TimeoutException ex)
            {
                throw new Exception($"{GetType().FullName}.WithConnection() experienced a SQL timeout", ex);
            }
            catch (SqlException ex)
            {
                throw new Exception($"{GetType().FullName}.WithConnection() experienced a SQL exception", ex);
            }
        }

        protected async Task<int> ExecuteAsync(string sql, DynamicParameters p)
        {
            return await WithConnection(
                async c =>
                {
                    var ret = await c.ExecuteAsync(sql, param: p, commandTimeout: _commandTimeout);
                    return ret;
                });
        }

        protected async Task<IEnumerable<T>> QueryAsync<T>(string sql, DynamicParameters p)
        {
            return await WithConnection(
                async c =>
                {
                    var ret = await c.QueryAsync<T>(sql, param: p, commandTimeout: _commandTimeout);
                    return ret;
                });
        }
    }

    public class NotificationRepository : BaseRepository
    {
        public async Task<bool> Add(NotificationModel model, bool trans = false)
        {
            return await WithConnection(
                async c =>
                {
                    if (trans)
                    {
                        ((DbConnection)c).EnlistTransaction(Transaction.Current);
                    }
                    var sql = @"INSERT INTO `asyncnotifications` 
(`JobFullName`,`JobData`,`ExecutedNumber`,`ExecutedUtcTime`,`NotifyStatus`,`CreationTime`) 
VALUES
(@JobFullName,@JobData,@ExecutedNumber,@ExecutedUtcTime,@NotifyStatus,@CreationTime);
SELECT CONVERT(LAST_INSERT_ID(), unsigned integer) AS ID";
                    var id = await c.ExecuteScalarAsync<long>(sql, model, commandTimeout: DefaultSqlCommandTimeout);
                    model.Id = id;
                    return id > 0;
                });
        }
        public async Task<bool> Update(NotificationModel model)
        {
            return await WithConnection(
                async c =>
                {//`JobFullName` = @JobFullName,`JobData`= @JobData,
                    var sql = @"update `asyncnotifications`
set `ExecutedNumber`=@ExecutedNumber,
`ExecutedUtcTime`=@ExecutedUtcTime,`NotifyStatus`= @NotifyStatus,`LastModificationTime`= @LastModificationTime
where `Id`=@id";
                    return await c.ExecuteAsync(sql, model, commandTimeout: DefaultSqlCommandTimeout) > 0;
                });
        }
        public async Task<IEnumerable<NotificationModel>> FindAllNotFinished()
        {
            return await this.WithConnection(async c =>
            {
                string sql = @"select `Id`,`JobFullName`,`JobData`,`ExecutedNumber`,`ExecutedUtcTime`,`NotifyStatus`,`CreationTime`,`LastModificationTime`
from `asyncnotifications`
where `NotifyStatus`=0";
                return await c.QueryAsync<NotificationModel>(sql).ConfigureAwait(false);
            });
        }
    }

[liguokun]

今天捣鼓了一下已经搞定了，如果大家想用VS2015+mysq的三层架构模式，需要的可以找我，下面贴出三层结构和一些代码供大家参考：


其中DBClass.cs的代码如下：
using System;
using System.Data;

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;


namespace test.mysql
{
public class DataClass
{
#region 建立MySql数据库连接
/// <summary>
/// 建立数据库连接.
/// </summary>
/// <returns>返回MySqlConnection对象</returns>
public MySqlConnection getmysqlcon()
{
//http://sosoft.cnblogs.com/
string M_str_sqlcon = "server=localhost;user id=root;password=root;database=stu"; //根据自己的设置
MySqlConnection myCon = new MySqlConnection(M_str_sqlcon);
return myCon;
}
#endregion

#region 执行MySqlCommand命令
/// <summary>
/// 执行MySqlCommand
/// </summary>
/// <param name="M_str_sqlstr">SQL语句</param>
public void getmysqlcom(string M_str_sqlstr)
{
MySqlConnection mysqlcon = this.getmysqlcon();
mysqlcon.Open();
MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
mysqlcom.ExecuteNonQuery();
mysqlcom.Dispose();
mysqlcon.Close();
mysqlcon.Dispose();
}
#endregion

#region 创建MySqlDataReader对象
/// <summary>
/// 创建一个MySqlDataReader对象
/// </summary>
/// <param name="M_str_sqlstr">SQL语句</param>
/// <returns>返回MySqlDataReader对象</returns>
public MySqlDataReader getmysqlread(string M_str_sqlstr)
{
MySqlConnection mysqlcon = this.getmysqlcon();
MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
mysqlcon.Open();
MySqlDataReader mysqlread = mysqlcom.ExecuteReader(CommandBehavior.CloseConnection);
return mysqlread;
}
#endregion


}
}


现贴出一部分DAL.cs里的部分代码 ：

//打开数据库
mysql.DataClass DBclass = new mysql.DataClass();


//查询所有商品
public DataTable getstu_all()
{
//DataSet ds_like = DBclass.getmysqlread("select * from stu_info");
//return ds_like;


DataTable data = new DataTable();
//da = new MySqlDataAdapter("SELECT * FROM " + tables.SelectedItem.ToString(), conn );
MySqlDataAdapter da=new MySqlDataAdapter("select * from stu_info",DBclass.getmysqlcon());
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);// 此处必须有，否则无法更新
da.Fill(data);
return data;

}

//添加商品
public bool Add(Model.goods model)
{
//DBclass.getmysqlcon();
string strsql = "insert into goods(GoodID,GoodName,GoodPrice)";
strsql += "values('" + model.GoodID + "','" + model.GoodName + "','" + model.GoodPrice + "')";

DBclass.getmysqlcom(strsql);

return true;

}


感谢大家！！

[liguokun]

特别是初学者想学习源码的，可以发邮件给我：[e-mail]