开始Mysql(数据库操作通用类)4

asp.net连接mysql数据库有很多方式 这里找了比较简单的一种。接着前面的数据库。

先下载一个 MySql.Data.dll 自己写一个通用类基本上和MSSQL一样

 

using System;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;

/// <summary>
/// MysqlHelp
/// </summary>
public class MysqlHelp
{
    MySqlConnection con;
    /// <summary>
    /// 
    /// </summary>
    public MysqlHelp()
    {
        string sqlstring = System.Configuration.ConfigurationManager.AppSettings["sqlcon"];
        con = new MySqlConnection(sqlstring);
    }
    /// <summary>
    /// 数据连接
    /// </summary>
    /// <param name="ip"></param>
    /// <param name="database"></param>
    /// <param name="username"></param>
    /// <param name="pwd"></param>
    public MysqlHelp(string ip, string database, string username, string pwd)
    {
        string sql = "Server=" + ip + ";UserName=" + username + ";Password=" + pwd + ";Database=" + database + ";Port=3306;CharSet=utf8;Allow Zero Datetime=true";
        con = new MySqlConnection(sql);
    }
 
    #region SQL语句操作
    /// <summary>
    /// 执行SQL语句
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public int Runsql(string sql)
    {
        int i = -1;
        MySqlCommand com = new MySqlCommand(sql, con);
        con.Open();
        i = com.ExecuteNonQuery();
        con.Close();
        return i;
    }
    /// <summary>
    /// 执行带参数SQL语句
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parms"></param>
    /// <returns></returns>
    public int Runsql(string sql, IDataParameter[] parms)
    {
        int i = -1;
        MySqlCommand com = new MySqlCommand(sql,con);
        foreach (MySqlParameter par in parms)
        {
            com.Parameters.Add(par);
        }
        con.Open();
        i=com.ExecuteNonQuery();
        con.Close();
        return i;
    }

    /// <summary>
    /// 执行SQL语句获得DATATABLE
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public DataTable GetDatabysql(string sql)
    {
        DataSet ds = new DataSet();
        con.Open();
        MySqlDataAdapter da = new MySqlDataAdapter(sql, con);
        da.Fill(ds);
        con.Close();
        return ds.Tables[0];
    }
    /// <summary>
    /// 执行带参数SQL语句获得DATATABLE
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="parms"></param>
    /// <returns></returns>
    public DataTable GetDatabysql(string sql, IDataParameter[] parms)
    {
        DataSet ds = new DataSet();
        con.Open();
        MySqlCommand com = new MySqlCommand(sql,con);
        foreach (MySqlParameter par in parms)
        {
            com.Parameters.Add(par);
        }
        MySqlDataAdapter da = new MySqlDataAdapter(com);
        da.Fill(ds);
        con.Close();
        return ds.Tables[0];
    }
    #endregion

    #region 操作存储过程
    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="prcname"></param>
    /// <param name="parms"></param>
    /// <returns></returns>
    public int RunPrc(string prcname, IDataParameter[] parms)
    {
        int i = -1;
        MySqlCommand com = new MySqlCommand(prcname,con);
        com.CommandType = CommandType.StoredProcedure;
        foreach (MySqlParameter par in parms)
        {
            com.Parameters.Add(par);
        }
        con.Open();
        i=com.ExecuteNonQuery();
        con.Close();
        return i;
    }

    /// <summary>
    /// 执行存储过程获得数据集
    /// </summary>
    /// <param name="prcname"></param>
    /// <returns></returns>
    public DataTable GetDataByPrc(string prcname)
    {
        DataSet ds = new DataSet();
        MySqlCommand com = new MySqlCommand(prcname, con);
        com.CommandType = CommandType.StoredProcedure;
        con.Open();
        MySqlDataAdapter da = new MySqlDataAdapter(com);
        da.Fill(ds);
        con.Close();
        return ds.Tables[0];
    }
    /// <summary>
    /// 执行存储过程获得数据集(带参数)
    /// </summary>
    /// <param name="prcname"></param>
    /// <param name="parms"></param>
    /// <returns></returns>
    public DataTable GetDataByPrc(string prcname, IDataParameter[] parms)
    {
        DataSet ds = new DataSet();
        con.Open();
        MySqlCommand com = new MySqlCommand(prcname, con);
        com.CommandType = CommandType.StoredProcedure;
        foreach (MySqlParameter par in parms)
        {
            com.Parameters.Add(par);
        }
        MySqlDataAdapter da = new MySqlDataAdapter(com);
        da.Fill(ds);
        con.Close();
        return ds.Tables[0];
    }
    #endregion
}