asp.net mysql打包_[原创]封装使用MySQLDriverCS在ASP.NET中访问MySQL的类

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using MySQLDriverCS;

using System.IO;

///

/// MySQLHelper 的摘要说明

/// 封装使用MySQLDriverCS在ASP.NET中访问MySQL数据库的基本功能

///

/// 使用这个类前先确认解决方案中添加了对MySQLDriverCS.dll的引用

///

/*

* 使用示例：

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;

///

/// 默认从web.config中读出

///

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);

}

///

/// 用无参构造后，以此函数建立连接

///

/// 服务器地址

/// 数据库名

/// 用户名

/// 密码

public void Connect(string server, string dbName, string user, string password)

{

this._conn = new MySQLConnection(new MySQLConnectionString(server, dbName, user, password).AsString);

}

///

///构造函数，以建立连接

///

/// 服务器地址

/// 数据库名

/// 用户名

/// 密码

public MySQLHelper(string sever, string dbName, string user, string password)

{

this.Connect(sever, dbName, user, password);

}

///

/// 打开连接,并解决乱码问题

///

/// true:打开成功 false:打开失败

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;

}

///

/// 关闭链接

///

public void CloseConnection()

{

try

{

this._conn.Close();

}

catch (MySQLException mse)

{

WriteIntoLog(mse.ToString());

}

}

///

/// 执行SQL语句,不需要Open,Close Connection

///

/// SQL语句

/// 受影响行数,异常返回 -1

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;

}

///

/// 执行sql语句,不需要Open,Close Connection

///

/// 要执行sql语句

/// 结果的第一行的第一列,异常返回null

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;

}

///

/// 返回一个MySQLCommand

/// 一般用于需要参数化执行SqlCommand的场所

///

/// 带参数的sql语句

/// MySQLCommand

public MySQLCommand GetCommand(string sqlText)

{

return new MySQLCommand(sqlText, this._conn);

}

///

/// 执行一个Command返回DataTable

/// 但数据库中text的列不能显示出来(GetTableFromReader不奏效)

/// 需要另外查询

///

/// 要执行的MySQLCommand

/// 结果DataTable

public static DataTable GetTableFromCommand(MySQLCommand cmd)

{

DataTable dt;

try

{

dt = GetTableFromReader(cmd.ExecuteReaderEx());

}

catch (MySQLException mse)

{

WriteIntoLog(mse.ToString());

return null;

}

return dt;

}

///

/// 从MySQLDataReader转换到DataTable

///

/// 现成的MySQLDataReader

/// DataTable

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;

}

///

/// 把异常信息写入日志

///

/// 要写入的异常信息

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;

}

}

}

web.config中的内容: