1.添加MySql.Data.dll引用
2.SQLHelper类:
private string strConn;
private MySqlConnection sqlConn;
private MySqlCommand sqlComm;
private string errMsg = null;
private int errNumber = 0;
#region Member
public string ErrorMessage
{
get { return errMsg; }
}
public int ErrorNumber
{
get { return errNumber; }
}
#endregion
public SqlHelper()
{
strConn = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
}
//public SqlHelper(string dbName)
//{
// strConn = ConfigurationManager.ConnectionStrings[dbName].ConnectionString;
//}
/// <summary>
/// query db. get recordSet. Must close.
/// </summary>
/// <param name="commandText">T-SQL command string</param>
///
/// <returns>SqlDataReader Or Null</returns>
public MySqlDataReader ExecuteReader(string commandText)
{
MySqlDataReader sqlDR = null;
try
{
sqlConn = new MySqlConnection(strConn);
sqlConn.Open();
sqlComm = new MySqlCommand(commandText, sqlConn);
sqlDR = sqlComm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (MySqlException e)
{
errMsg = e.Message;
errNumber = e.Number;
if (sqlConn.State == ConnectionState.Open)
sqlConn.Close();
}
return sqlDR;
}
/// <summary>
/// Opreation db. include: insert and update and delete.
/// </summary>
/// <param name="commandText">T-SQL command string</param>
/// <returns>True or False</returns>
public bool ExecuteNonQuery(string commandText)
{
bool result = true;
try
{
sqlConn = new MySqlConnection(strConn);
sqlConn.Open();
sqlComm = new MySqlCommand(commandText, sqlConn);
sqlComm.ExecuteNonQuery();
}
catch (MySqlException e)
{
result = false;
errMsg = e.Message;
errNumber = e.Number;
}
if (sqlConn.State == ConnectionState.Open)
sqlConn.Close();
return result;
}
/// <summary>
/// Get a DataSet
/// </summary>
/// <param name="commandText">T-SQL command string</param>
/// <returns>DataSet type</returns>
public DataSet ExecuteDataSet(string commandText)
{
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter(commandText, new MySqlConnection(strConn));
da.Fill(ds);
return ds;
}
/// <summary>
/// Get a DataTable.
/// </summary>
/// <param name="commandText">T-SQL command string</param>
/// <returns>DataTable type</returns>
public DataTable ExecuteDataTable(string commandText)
{
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(commandText, new MySqlConnection(strConn));
da.Fill(dt);
return dt;
}
/// <summary>
/// Execute a transacton.NonQuery.
/// </summary>
/// <param name="commandTextList">T-SQL command string set</param>
/// <returns>True or False</returns>
public bool ExecuteTransaction(List<string> commandTextList)
{
bool result = true;
sqlConn = new MySqlConnection(strConn);
MySqlTransaction sqlTrans = null;
try
{
sqlConn.Open();
sqlTrans = sqlConn.BeginTransaction();
sqlComm = new MySqlCommand();
sqlComm.Connection = sqlConn;
sqlComm.Transaction = sqlTrans;
sqlComm.CommandType = CommandType.Text;
foreach (string commandText in commandTextList)
{
sqlComm.CommandText = commandText;
sqlComm.ExecuteNonQuery();
}
sqlTrans.Commit();
}
catch (MySqlException e)
{
sqlTrans.Rollback();
errMsg = e.Message;
errNumber = e.Number;
result = false;
}
if (sqlConn.State == ConnectionState.Open)
sqlConn.Close();
return result;
}
/// <summary>
/// Execute a StroreProcedure.
/// </summary>
/// <param name="storeProcedureName">StroeProcedure Name</param>
/// <param name="paraList">Dictionary type</param>
/// <returns>True or False. if execute failed, ErrorNumber=@@error and ErrorMessag is NULL.</returns>
public bool ExecuteProcedure(string storeProcedureName, Dictionary<string, string> paraList)
{
bool result = true;
try
{
sqlConn = new MySqlConnection(strConn);
sqlConn.Open();
sqlComm = new MySqlCommand(storeProcedureName, sqlConn);
sqlComm.CommandType = CommandType.StoredProcedure;
foreach (KeyValuePair<string, string> kvp in paraList)
{
sqlComm.Parameters.Add(new MySqlParameter(kvp.Key, kvp.Value));
}
int spReturnValue = (int)sqlComm.ExecuteScalar();
if (spReturnValue != 0)
{
errNumber = spReturnValue;
errMsg = null;
result = false;
}
}
catch (MySqlException e)
{
errMsg = e.Message;
errNumber = e.Number;
result = false;
}
if (sqlConn.State == ConnectionState.Open)
sqlConn.Close();
return result;
}