|
首先看看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,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;
??????? }
? （编辑：北几岛）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
