[原创]封装使用MySQLDriverCS在ASP.NET中访问MySQL的类

悟静 2011-12-29

展开全文

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;

using MySQLDriverCS;
using System.IO;

/// <summary>
/// MySQLHelper 的摘要说明
/// 封装使用MySQLDriverCS在ASP.NET中访问MySQL数据库的基本功能
///
/// 使用这个类前先确认解决方案中添加了对MySQLDriverCS.dll的引用
/// </summary>

/*
* 使用示例：
    protected void bindGridView()
    {
        MySQLHelper hlp = new MySQLHelper();
        MySQLCommand cmd = hlp.GetCommand("select * from tablename");
        hlp.OpenConnection();
        DataTable dt = MySQLHelper.GetTableFromCommand(cmd);
        hlp.CloseConnection();

        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
*/

public class MySQLHelper
{
private MySQLConnection _conn;

    /// <summary>
    /// 默认从web.config中读出
    /// </summary>

    public MySQLHelper()
    {
        //
        // TODO: 在此处添加构造函数逻辑
        //
        MySQLConnectionString strConn = new MySQLConnectionString(
                ConfigurationManager.AppSettings["server"].ToString(),
                ConfigurationManager.AppSettings["dbName"].ToString(),
                ConfigurationManager.AppSettings["user"].ToString(),
                ConfigurationManager.AppSettings["password"].ToString()
                );
        this._conn = new MySQLConnection(strConn.AsString);
    }

    /// <summary>
    /// 用无参构造后，以此函数建立连接
    /// </summary>
    /// <param name="ip">服务器地址</param>
    /// <param name="dbName">数据库名</param>
    /// <param name="user">用户名</param>
    /// <param name="password">密码</param>

    public void Connect(string server, string dbName, string user, string password)
    {
        this._conn = new MySQLConnection(new MySQLConnectionString(server, dbName, user, password).AsString);
    }

    /// <summary>
    ///构造函数，以建立连接
    /// </summary>
    /// <param name="ip">服务器地址</param>
    /// <param name="dbName">数据库名</param>
    /// <param name="user">用户名</param>
    /// <param name="password">密码</param>

    public MySQLHelper(string sever, string dbName, string user, string password)
    {
        this.Connect(sever, dbName, user, password);
    }

    /// <summary>
    /// 打开连接,并解决乱码问题
    /// </summary>
    /// <returns>true:打开成功 false:打开失败</returns>

    public bool OpenConnection()
    {
        try
        {
            this._conn.Open();
        }
        catch (MySQLException mse)
        {
            //不成功设_conn为null
            //在其他函数里将先判断_conn
            this._conn = null;
            WriteIntoLog(mse.ToString());
            return false;
        }
        //防止乱码
        MySQLCommand commn = new MySQLCommand("set names gb2312", this._conn);
        commn.ExecuteNonQuery();
        return true;
    }

    /// <summary>
    /// 关闭链接
    /// </summary>

    public void CloseConnection()
    {
        try
        {
            this._conn.Close();
        }
        catch (MySQLException mse)
        {
            WriteIntoLog(mse.ToString());
        }
    }

    /// <summary>
    /// 执行SQL语句,不需要Open,Close Connection
    /// </summary>
    /// <param name="sqlText">SQL语句</param>
    /// <returns>受影响行数,异常返回 -1</returns>

    public int ExecuteNonQuery(string sqlText)
    {
        if (this._conn == null) return -1;
        int temp = -1;
        try
        {
            MySQLCommand cmd = new MySQLCommand(sqlText, this._conn);
            this._conn.Open();
            //防止乱码
            MySQLCommand commn = new MySQLCommand("set names gb2312", this._conn);
            commn.ExecuteNonQuery();
            temp = cmd.ExecuteNonQuery();
            this._conn.Close();
        }
        catch (MySQLException mse)
        {
            WriteIntoLog(mse.ToString());
        }
        return temp;
    }

    /// <summary>
    /// 执行sql语句,不需要Open,Close Connection
    /// </summary>
    /// <param name="sqlText">要执行sql语句</param>
    /// <returns>结果的第一行的第一列,异常返回null</returns>

    public object ExecuteScalar(string sqlText)
    {
        if (this._conn == null) return null;
        object temp = null;
        try
        {
            MySQLCommand cmd = new MySQLCommand(sqlText, this._conn);
            this._conn.Open();
            //防止乱码
            MySQLCommand commn = new MySQLCommand("set names gb2312", this._conn);
            commn.ExecuteNonQuery();
            temp = cmd.ExecuteScalar();
            this._conn.Close();
        }
        catch (MySQLException mse)
        {
            WriteIntoLog(mse.ToString());
        }
        return temp;
    }

    /// <summary>
    /// 返回一个MySQLCommand
    /// 一般用于需要参数化执行SqlCommand的场所
    /// </summary>
    /// <param name="sqlText">带参数的sql语句</param>
    /// <returns>MySQLCommand</returns>

    public MySQLCommand GetCommand(string sqlText)
    {
        return new MySQLCommand(sqlText, this._conn);
    }

    /// <summary>
    /// 执行一个Command返回DataTable
    /// 但数据库中text的列不能显示出来(GetTableFromReader不奏效)
    /// 需要另外查询
    /// </summary>
    /// <param name="cmd">要执行的MySQLCommand</param>
    /// <returns>结果DataTable</returns>

    public static DataTable GetTableFromCommand(MySQLCommand cmd)
    {
        DataTable dt;
        try
        {
            dt = GetTableFromReader(cmd.ExecuteReaderEx());
        }
        catch (MySQLException mse)
        {
            WriteIntoLog(mse.ToString());
            return null;
        }
        return dt;
    }

    /// <summary>
    /// 从MySQLDataReader转换到DataTable
    /// </summary>
    /// <param name="reader">现成的MySQLDataReader</param>
    /// <returns>DataTable</returns>

    public static DataTable GetTableFromReader(MySQLDataReader reader)
    {
        if (reader == null) return null;

DataTable objDataTable = new DataTable();
int intFieldCount = reader.FieldCount;

        //为DataTable对象添加对应的列字段信息
        for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
        {
            objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
        }

objDataTable.BeginLoadData();
object[] objValues = new object[intFieldCount];

        //逐行读取SqlDataReader对象中每一行数据
        //并把数据添加到对象dataTable中
        while (reader.Read())
        {
            reader.GetValues(objValues);
            objDataTable.LoadDataRow(objValues, true);
        }

        ///关闭数据读取器
        reader.Close();
        objDataTable.EndLoadData();
        return objDataTable;
    }

    /// <summary>
    /// 把异常信息写入日志
    /// </summary>
    /// <param name="strExp">要写入的异常信息</param>

    private static void WriteIntoLog(string strExp)
    {
        try
        {
            FileStream fs = new FileStream(
                ConfigurationManager.AppSettings["logpath"].ToString(),
                FileMode.Append);
            StreamWriter sw = new StreamWriter(fs);
            sw.WriteLine(DateTime.Now);
            sw.Write(strExp + "\r\n\r\n");
            sw.Flush();
            sw.Close();
        }
        catch (IOException exp)
        {
            return;
        }
    }
}