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數(shù)據(jù)庫的基本功能
///
/// 使用這個類前先確認(rèn)解決方案中添加了對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>
/// 默認(rèn)從web.config中讀出
/// </summary>
public MySQLHelper()
{
//
// TODO: 在此處添加構(gòu)造函數(shù)邏輯
//
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>
/// 用無參構(gòu)造后，以此函數(shù)建立連接
/// </summary>
/// <param name="ip">服務(wù)器地址</param>
/// <param name="dbName">數(shù)據(jù)庫名</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>
///構(gòu)造函數(shù)，以建立連接
/// </summary>
/// <param name="ip">服務(wù)器地址</param>
/// <param name="dbName">數(shù)據(jù)庫名</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)
{
//不成功設(shè)_conn為null
//在其他函數(shù)里將先判斷_conn
this._conn = null;
WriteIntoLog(mse.ToString());
return false;
}
//防止亂碼
MySQLCommand commn = new MySQLCommand("set names gb2312", this._conn);
commn.ExecuteNonQuery();
return true;
}
/// <summary>
/// 關(guān)閉鏈接
/// </summary>
public void CloseConnection()
{
try
{
this._conn.Close();
}
catch (MySQLException mse)
{
WriteIntoLog(mse.ToString());
}
}
/// <summary>
/// 執(zhí)行SQL語句,不需要Open,Close Connection
/// </summary>
/// <param name="sqlText">SQL語句</param>
/// <returns>受影響行數(shù),異常返回 -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>
/// 執(zhí)行sql語句,不需要Open,Close Connection
/// </summary>
/// <param name="sqlText">要執(zhí)行sql語句</param>
/// <returns>結(jié)果的第一行的第一列,異常返回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
/// 一般用于需要參數(shù)化執(zhí)行SqlCommand的場所
/// </summary>
/// <param name="sqlText">帶參數(shù)的sql語句</param>
/// <returns>MySQLCommand</returns>
public MySQLCommand GetCommand(string sqlText)
{
return new MySQLCommand(sqlText, this._conn);
}
/// <summary>
/// 執(zhí)行一個Command返回DataTable
/// 但數(shù)據(jù)庫中text的列不能顯示出來(GetTableFromReader不奏效)
/// 需要另外查詢
/// </summary>
/// <param name="cmd">要執(zhí)行的MySQLCommand</param>
/// <returns>結(jié)果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轉(zhuǎn)換到DataTable
/// </summary>
/// <param name="reader">現(xiàn)成的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對象添加對應(yīng)的列字段信息
for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
{
objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
}
objDataTable.BeginLoadData();
object[] objValues = new object[intFieldCount];
//逐行讀取SqlDataReader對象中每一行數(shù)據(jù)
//并把數(shù)據(jù)添加到對象dataTable中
while (reader.Read())
{
reader.GetValues(objValues);
objDataTable.LoadDataRow(objValues, true);
}
///關(guān)閉數(shù)據(jù)讀取器
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;
}
}
}
web.config中的內(nèi)容:
<appSettings>
<!--數(shù)據(jù)庫連接信息-->
<add key="server" value="127.0.0.1"/>
<add key="dbName" value="nameofDB"/>
<add key="user" value="root"/>
<add key="password" value="123456"/>
<!--數(shù)據(jù)庫異常日志路徑-->
<add key="logpath" value="D:\MyDocument\exception.txt"/>
</appSettings>
