开篇序:
现实中,我们经常遇到这样的人或事,前脚客客气气,后脚买完东西付完钱后另一个态度,前脚帮他一个忙,完事后装作不认识甚至背后捅刀,用人朝前,不用人朝后的完美诠释者,完全没必要生气,记住一句话,因果轮回,这种人有因不改必有果,成不了气候的,至少他教会了你要学会看人。
正文: ADO.NET 使用小记
/// <summary>
/// 主要用于数据的基础操作
/// </summary>
public class DataBase
{
private readonly MySqlCommand m_Cmd;
private readonly MySqlConnection m_Conn;
public static int DBlog_onoff=0;//0--记录DBLog,1--不记录
public static int LOGCount = 0;
public static void SetDBlogOnOff(int flag)
{
DBlog_onoff=flag;
}
/// <summary>
/// 创建数据库连接和SqlCommand实例
/// </summary>
public DataBase()
{
ReadConfig recon = new ReadConfig();
string strConn = recon.GetConnectionString();
//string strConn = ConnectionStringGet.ConnectionString;
try
{
m_Conn = new MySqlConnection(strConn);
m_Cmd = new MySqlCommand();
m_Cmd.Connection = m_Conn;
}
catch (Exception e)
{
#if DEBUG
Logger.LogText("DBUtility", "DataBase", e);
#endif
throw e;
}
}
public DataBase(string s)
{
string strConn = s;
try
{
m_Conn = new MySqlConnection(strConn);
m_Cmd = new MySqlCommand();
m_Cmd.Connection = m_Conn;
}
catch (Exception e)
{
#if DEBUG
Logger.LogText("DBUtility", "DataBase", e);
#endif
throw e;
}
}
public MySqlConnection Conn
{
get { return m_Conn; }
}
public MySqlCommand Cmd
{
get { return m_Cmd; }
}
/// <summary>
/// 通过Transact-SQL语句提交数据
/// </summary>
/// <param name="strSql"> Transact-SQL语句 </param>
/// <returns> 受影响的行数 </returns>
public int ExecDataBySql(string strSql)
{
int intReturnValue;
m_Cmd.CommandType = CommandType.Text;
m_Cmd.CommandText = strSql;
try
{
if (m_Conn.State == ConnectionState.Closed)
{
m_Conn.Open();
}
//DBLogLocal(strSql);
intReturnValue = m_Cmd.ExecuteNonQuery();
}
catch (Exception e)
{
#if DEBUG
Logger.LogText("DBUtility", "ExecDataBySql", e);
#endif
throw e;
}
finally
{
m_Conn.Close(); //连接关闭,但不释放掉该对象所占的内存单元
}
return intReturnValue;
}
static public int IsCancelFlag = 0;
static public void SetIsCancelFlag(int IsCancel)
{
IsCancelFlag = IsCancel;
}
/// <summary>
/// 多条Transact-SQL语句提交数据
/// </summary>
/// <param name="strSqls"> 使用List泛型封装多条SQL语句 </param>
/// <returns> bool值(提交是否成功) </returns>
public bool ExecDataBySqls(List<string> strSqls)
{
bool booIsSucceed;
if (m_Conn.State == ConnectionState.Closed)
{
m_Conn.Open();
}
MySqlTransaction sqlTran = m_Conn.BeginTransaction();
try
{
m_Cmd.Transaction = sqlTran;
foreach (string item in strSqls)
{
if (IsCancelFlag == 1)
{
sqlTran.Rollback();
booIsSucceed = false; //表示提交数据库失败!
return booIsSucceed;
}
m_Cmd.CommandType = CommandType.Text;
m_Cmd.CommandText = item;
//DBLogLocal(item);
m_Cmd.ExecuteNonQuery();
}
sqlTran.Commit();
booIsSucceed = true; //表示提交数据库成功
}
catch (Exception ex)
{
#if TRACE
Logger.LogText("ExecDataBySqls", m_Cmd.CommandText, ex);
#endif
Logger.WriteSQLCommandFile(strSqls, ".sqlcommand");
sqlTran.Rollback();
booIsSucceed = false; //表示提交数据库失败!
}
finally
{
m_Conn.Close();
strSqls.Clear();
}
return booIsSucceed;
}
static MySqlTransaction sqlTran=null;
public void ExecDataBySqlInit()
{
if (m_Conn.State == ConnectionState.Closed)
{
m_Conn.Open();
}
sqlTran = m_Conn.BeginTransaction();
}
public bool ExecDataBySqlCommit()
{
bool booIsSucceed;
try
{
sqlTran.Commit();
booIsSucceed = true; //表示提交数据库成功
sqlTran = null;
}
finally
{
m_Conn.Close();
//strSqls.Clear();
}
return booIsSucceed;
}
public bool ExecDataBySqlTransaction(string strSqls)
{
bool booIsSucceed;
try
{
m_Cmd.Transaction = sqlTran;
if (IsCancelFlag == 1)
{
sqlTran.Rollback();
booIsSucceed = false; //表示提交数据库失败!
return booIsSucceed;
}
m_Cmd.CommandType = CommandType.Text;
m_Cmd.CommandText = strSqls;
//DBLogLocal(item);
m_Cmd.ExecuteNonQuery();
// sqlTran.Commit();
booIsSucceed = true; //表示提交数据库成功
}
catch (Exception ex)
{
#if TRACE
Logger.LogText("ExecDataBySqls", m_Cmd.CommandText, ex);
#endif
//Logger.WriteSQLCommandFile(strSqls, ".sqlcommand");
sqlTran.Rollback();
booIsSucceed = false; //表示提交数据库失败!
}
return booIsSucceed;
}
/// <summary>
/// 通过Transact-SQL语句得到DataSet实例
/// </summary>
/// <param name="strSql"> Transact-SQL语句 </param>
/// <param name="strTable"> 相关的数据表 </param>
/// <returns> DataSet实例的引用 </returns>
public DataSet GetDataSet(string strSql, string strTable)
{
DataSet ds = null;
try
{
//DBLogLocal(strSql);
MySqlDataAdapter sda = new MySqlDataAdapter(strSql, m_Conn);
ds = new DataSet();
sda.Fill(ds, strTable);
}
catch (Exception e)
{
#if DEBUG
Logger.LogText("DBUtility", "GetDataSet", e);
#endif
throw e;
}
return ds;
}
/// <summary>
/// 通过Transact-SQL语句得到SqlDataReader实例
/// </summary>
/// <param name="strSql"> Transact-SQL语句 </param>
/// <returns> SqlDataReader实例的引用 </returns>
public MySqlDataReader GetDataReader(string strSql)
{
MySqlDataReader sdr;
m_Cmd.CommandType = CommandType.Text;
m_Cmd.CommandText = strSql;
try
{
if (m_Conn.State == ConnectionState.Closed)
{
m_Conn.Open();
}
//DBLogLocal(strSql);
sdr = m_Cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception e)
{
#if DEBUG
Logger.LogText("DBUtility", "GetDataReader", e);
#endif
throw e;
}
//sdr对象和m_Conn对象暂时不能关闭和释放掉,否则在调用时无法使用
//待使用完毕sdr,再关闭sdr对象(同时会自动关闭关联的m_Conn对象)
//m_Conn的关闭是指关闭连接通道,但连接对象依然存在
//m_Conn的释放掉是指销毁连接对象
return sdr;
}
/// <summary>
/// 重新封装ExecuteScalar方法,得到结果集中的第一行的第一列
/// </summary>
/// <param name="strSql"> Transact-SQL语句 </param>
/// <returns> 结果集中的第一行的第一列 </returns>
public object GetSingleObject(string strSql)
{
object obj = null;
m_Cmd.CommandType = CommandType.Text;
m_Cmd.CommandText = strSql;
try
{
if (m_Conn.State == ConnectionState.Closed)
{
m_Conn.Open();
}
//DBLogLocal(strSql);
obj = m_Cmd.ExecuteScalar();
}
catch (Exception e)
{
#if DEBUG
Logger.LogText("DBUtility", "GetSingleObject", e);
#endif
throw e; //向上一层抛出异常(上一层使用try{}catch{})或立刻中断(上一层未使用try{}catch{})
}
finally
{
m_Conn.Close();
}
return obj;
}
/// <summary>
/// 通过Transact-SQL语句,得到DataTable实例
/// </summary>
/// <param name="strSqlCode"> Transact-SQL语句 </param>
/// <param name="strTableName"> 数据表的名称 </param>
/// <returns> DataTable实例的引用 </returns>
public DataTable GetDataTable(string strSqlCode, string strTableName)
{
DataTable dt = null;
MySqlDataAdapter sda = null;
try
{
//DBLogLocal(strSqlCode);
sda = new MySqlDataAdapter(strSqlCode, m_Conn);
dt = new DataTable(strTableName);
sda.Fill(dt);
}
catch (Exception ex)
{
#if DEBUG
Logger.LogText("DBUtility", "GetDataTable", ex);
#endif
throw ex;
}
return dt; //dt.Rows.Count可能等于零
}
/// <summary>
/// 通过存储过程,得到DataTable实例
/// </summary>
/// <param name="strProcedureName"> 存储过程名称 </param>
/// <param name="inputParameters"> 存储过程的参数数组 </param>
/// <returns> DataTable实例的引用 </returns>
public DataTable GetDataTable(string strProcedureName, MySqlParameter[] inputParameters)
{
DataTable dt = new DataTable();
MySqlDataAdapter sda = null;
try
{
m_Cmd.CommandType = CommandType.StoredProcedure;
m_Cmd.CommandText = strProcedureName;
sda = new MySqlDataAdapter(m_Cmd);
m_Cmd.Parameters.Clear();
foreach (MySqlParameter param in inputParameters)
{
param.Direction = ParameterDirection.Input;
m_Cmd.Parameters.Add(param);
}
sda.Fill(dt);
}
catch (Exception ex)
{
#if DEBUG
Logger.LogText("DBUtility", "GetDataTable", ex);
#endif
throw ex;
}
return dt; //dt.Rows.Count可能等于零
}
/// <将一组数据添加到commond参数集合中>
/// 将一组数据添加到commond参数集合中(例如执行插入操作)
/// </summary>
/// <param name="sqlStr">SQL语句</param>
/// <param name="sq"> 插入的参数数组</param>
/// <returns>行数</returns>
public int ExecuteNonQuery(string sqlStr, params MySqlParameter[] sq)
{
using (m_Cmd)
{
try
{
m_Cmd.CommandType = CommandType.Text;
m_Cmd.CommandText = sqlStr;
if (sq != null)
{
m_Cmd.Parameters.Clear();
m_Cmd.Parameters.AddRange(sq);
}
//打开连接
if (m_Conn.State == ConnectionState.Closed)
{
m_Conn.Open();
}
//DBLogLocal(sqlStr);
return m_Cmd.ExecuteNonQuery();
}
catch (Exception f)
{
#if DEBUG
Logger.LogText("DBUtility", "ExecuteNonQuery", f);
#endif
m_Conn.Dispose();
throw f;
}
finally {
m_Conn.Close();
}
}
}
待优化:连接可用连接池,减少资源的浪费和数据库的负担。