一些类库的研究-sqlhelper
首先看看sqlhelper,就是Ado.net的辅助数据库操作类,传的是sql语句。
1.常量定义
private SqlConnection objSqlConnection = new SqlConnection();
private SqlCommand objSqlcommand = new SqlCommand();
private SqlDataAdapter objSDA = new SqlDataAdapter();
private static object objLock = new object();
public static string sqlConnString = string.Empty;
2.构造函数
public SqlHelper()
{
sqlConnString=ConfigValue.SqlConnection;
}
3.打开连接
private void OpenConn()
{
objSqlConnection.ConnectionString = SqlConnString;
try
{
objSqlConnection.Open();
}
catch (Exception Err)
{
throw new Exception(Err.Message);
}
}
4.关闭连接
private void CloseConn()
{
objSqlConnection.Close();
}
5.返回dataReader
public SqlDataReader GetSqlRD(string strSqlCommand)
{
SqlDataReader objRD = null;
ock (objLock)
{
try
{
OpenConn();
objSqlcommand = new SqlCommand(strSqlCommand, objSqlConnection);
objRD = objSqlcommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception Err)
{
//寫入出錯Log檔
throw new Exception(Err.Message);
CloseConn();
}
finally
{
CloseConn();
}
}
return objRD;
}
6.返回dataset
public DataSet getDataSet(string strSqlCommand)
{
DataSet objDS = null;
lock (objLock)
{
try
{
OpenConn();
objDS = new DataSet();
objSqlcommand = new SqlCommand(strSqlCommand, objSqlConnection);
SqlDataAdapter objDA = new SqlDataAdapter(objSqlcommand);
objDA.Fill(objDS);
}
catch (Exception Err)
{
//寫入Log檔
throw new Exception(Err.Message);
}
finally
{
CloseConn();
}
}
return objDS;
}
7.返回dataTable
public DataTable getDataTable(string strSqlCommand, string tableName)
{
DataTable objDT = null;
lock (objLock)
{
try
{
OpenConn();
objDT = new DataTable(tableName);
objSqlcommand = new SqlCommand(strSqlCommand, objSqlConnection);
SqlDataAdapter objDA = new SqlDataAdapter(objSqlcommand);
objDA.Fill(objDT);
}
catch (SqlException Err)
{
}
finally
{
CloseConn();
}
}
return objDT;
}
8.返回受影响的行数
public int ExecuteSqlCommand(string strSqlCommand)
{
int count = 0;
lock (objLock)
{
SqlTransaction sqlTran = null;
try
{
OpenConn();
sqlTran = objSqlConnection.BeginTransaction();
objSqlcommand = new SqlCommand(strSqlCommand, objSqlConnection, sqlTran);
count += objSqlcommand.ExecuteNonQuery();
sqlTran.Commit();
}
catch (Exception Err)
{
//寫入Log檔
sqlTran.Rollback();
throw new Exception(Err.Message);
}
finally
{
CloseConn();
}
}
return count;
}
9.检查dataset中是否有资料
public bool chkDataSet(DataSet objDS)
{
if (objDS.Tables[0].Rows.Count == 0)
{
return false;
}
else
{
return true;
}
}
10.返回DataAdapter
public SqlDataAdapter getDataAdapter(string strSqlCommand)
{
lock (objLock)
{
try
{
OpenConn();
objSqlcommand = new SqlCommand(strSqlCommand, objSqlConnection);
objSDA.SelectCommand = objSqlcommand;
}
catch (Exception Err)
{
//寫入出錯Log檔
throw new Exception(Err.Message);
}
finally
{
CloseConn();
}
}
return objSDA;
}
11.返回第一行第一列
public string getExecuteSqlCommandFrist(string strSqlCommand, params SqlParameter[] paras)
{
string strReturn = string.Empty;
lock (objLock)
{
SqlTransaction sqlTran = null;
try
{
OpenConn();
sqlTran = objSqlConnection.BeginTransaction();
objSqlcommand = new SqlCommand(strSqlCommand, objSqlConnection, sqlTran);
if (paras != null && paras.Length > 0)
{
for (int i = 0; i < paras.Length; i++)
{
objSqlcommand.Parameters.Add(paras[i].ParameterName, paras[i].Value);
}
}
strReturn = (string)objSqlcommand.ExecuteScalar();
sqlTran.Commit();
}
catch (Exception Err)
{
//寫入Log檔
sqlTran.Rollback();
throw new Exception(Err.Message);
}
finally
{
CloseConn();
}
}
return strReturn;
}