|
|
Dotnet总结(2)--访问ms sql server 数据库基类 |
|
|
作者:未知 来源:月光软件站 加入时间:2005-5-13 月光软件站 |
using System; using System.Data; using System.Configuration; using System.Data.SqlClient; using System.Xml; using System.Xml.Serialization; using System.IO; using System.Text; using System.Collections;
namespace Common.lib.DBBase { public class DBManager { protected SqlConnection f_Connection; //数据库连接对象 private SqlTransaction f_Tran; private SqlException f_Exception; static public string f_ConnString; //数据库连接字符串 protected SqlDataReader p_DataReader; protected string p_strSql;
public string ConnString { get { return ConnString; } set { ConnString = value; } }
public DBManager() { // // TODO: 在此处添加构造函数逻辑 // f_Connection=new SqlConnection(f_ConnString); }
public DBManager(string strConn) { // // TODO: 在此处添加构造函数逻辑 // f_ConnString=strConn; f_Connection=new SqlConnection(f_ConnString); }
~DBManager() { f_Connection.Dispose(); }
/// <summary> /// 用于事务操作的事务对象,用BeginTran()开始事务,用CommitTran(DbTransaction)提交事务, RollbackTran(DbTransaction)回滚事务 /// </summary> public SqlTransaction DbTransaction { get { return f_Tran; } }
/// <summary> /// 返回错误异常 /// </summary> public SqlException DbException { get { return f_Exception; } } /// <summary> /// 执行无返回值的SQL语句 /// </summary> /// <param name="strsql">要执行的SQL语句</param> /// <returns>执行成功则返回受影响的行数,失败返回-1</returns> ///
protected string AddQuotes(string strTemp) { strTemp="\""+strTemp+"\""; return strTemp; }
protected string AddSingleQuotes(string strTemp) { strTemp="'"+strTemp+"'"; return strTemp; }
/// <summary> /// 给输入的不带事务的sql语句添加事务 /// </summary> /// <param name="strSqlWithOutTran">输入的sql语句</param> /// <returns></returns> protected string AddTransaction(string strSqlWithOutTran) { string strUp="begin tran \r"; strUp=strUp+strSqlWithOutTran+"\r"; strUp=strUp+"if @@error<>0 \r"; strUp=strUp+" rollback tran \r"; strUp=strUp+"else \r"; strUp=strUp+" commit tran \r"; return strUp; }
/// <summary> /// 获取系统时间 /// </summary> /// <returns>返回系统时间</returns> public DateTime GetServerTime() { string strsql="select GetDate()"; return DateTime.Parse(GetDataString(strsql)); } public int Execute(string strsql) { return Execute(strsql,(SqlTransaction)null); }
/// <summary> /// /// </summary> /// <param name="strSql"></param> /// <param name="p_Tran"></param> /// <returns></returns> public int Execute(string strSql, SqlTransaction p_Tran) { int i; if (p_Tran==null) { try { f_Exception=null; f_Connection.Open(); SqlCommand sqlcmd=new SqlCommand(strSql,f_Connection); i= sqlcmd.ExecuteNonQuery(); } catch (SqlException e) { f_Exception=e; i=0; } finally { f_Connection.Close(); } } else { try { f_Exception=null; SqlCommand sqlcmd = new SqlCommand(strSql,p_Tran.Connection,p_Tran); i= sqlcmd.ExecuteNonQuery(); } catch (SqlException e) { f_Exception=e; i=0; } } return i; }
/// <summary> /// 根据执行SQL的结果返回一个SqlDataReader /// </summary> /// <param name="strsql">要执行的SQL语句</param> /// <returns>成功则返回一个SqlDataReader,否则返回null</returns> public SqlDataReader GetDataReader(string strsql) { SqlDataReader sdr; SqlCommand sqlcmd; try { f_Connection.Open(); sqlcmd = new SqlCommand(strsql,f_Connection); sdr= sqlcmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); return sdr; } //失败,返回一个null空的SQLDATAREADER catch { //f_Exception=e; return null; } }
/// <summary> /// 返回单个结果字符串 /// </summary> /// <param name="strsql"></param> /// <returns></returns> public string GetDataString(string strsql) { SqlCommand sqlcmd; try { f_Connection.Open(); sqlcmd= new SqlCommand(strsql,f_Connection); string strTmp=sqlcmd.ExecuteScalar().ToString(); return strTmp; } catch { return null; } finally { f_Connection.Close(); } }
/// <summary> /// 根据执行SQL的结果返回一个DataTable /// </summary> /// <param name="strsql">要执行的SQL语句</param> /// <returns>成功则返回一个DataTable,否则返回null</returns> /// public DataTable GetDataTable(string strsql) { SqlCommand sqlcmd; SqlDataAdapter sda; DataTable dt;
try { f_Connection.Open(); sda=new SqlDataAdapter(); dt=new DataTable(); sqlcmd= new SqlCommand(strsql,f_Connection); sda.SelectCommand=sqlcmd; sda.Fill(dt); return dt; } catch { return null; } finally { f_Connection.Close(); } }
/// <summary> /// 根据执行SQL的结果返回一个DataTable /// </summary> /// <param name="strsql">要执行的SQL语句</param> /// <returns>成功则返回一个DataSet,否则返回null</returns> public DataSet GetDateSet(string strsql) { SqlCommand sqlcmd; SqlDataAdapter sda; DataSet ds;
try { f_Connection.Open(); sda=new SqlDataAdapter(); ds=new DataSet(); sqlcmd= new SqlCommand(strsql,f_Connection); sda.SelectCommand=sqlcmd; sda.Fill(ds); return ds; } catch { return null; } finally { f_Connection.Close(); }
}
/// <summary> /// 替换字符串中的单引号 /// </summary> /// <param name="str">要替换的字符串</param> /// <returns>返回替换后的字符串</returns> protected string RepString(string str) { if (str!="" && str.IndexOf("'")>0) { str=str.Replace("'","''"); } return str; }
/// <summary> /// 根据数据库字典编码得到字典名称 /// </summary> /// <param name="codeid">数据库字典编码</param> /// <returns>返回数据库字典名称</returns> public string GetCodeName(int codeid) { string strSql; string ReturnString; strSql="SELECT codename FROM syscode WHERE codeid='" + codeid.ToString() + "'"; SqlDataReader sdrTmp=GetDataReader(strSql); if (sdrTmp.Read()) { ReturnString=sdrTmp.GetString(0); } else { ReturnString=""; } sdrTmp.Close(); return ReturnString; }
/// <summary> /// 不带事务的添加操作 /// </summary> /// <param name="strTable">表名</param> /// <param name="arrStrField">字段名称列表</param> /// <param name="arrObj">字段值列表</param> /// <returns></returns> protected int DbAdd(string strTable,string[] arrStrField,object[] arrObj) { return DbAdd((SqlTransaction)null,strTable,arrStrField,arrObj); }
/// <summary> /// 带事务的添加操作 /// </summary> /// <param name="p_Tran">事务</param> /// <param name="strTable">表名</param> /// <param name="arrStrField">字段名称列表</param> /// <param name="arrObj">字段值列表</param> /// <returns></returns> protected int DbAdd(SqlTransaction p_Tran,string strTable,string[] arrStrField,object[] arrObj) { string l_str_Sql="INSERT INTO "+strTable+ " " +CreateInsertSql(arrStrField,arrObj); return this.Execute(l_str_Sql,p_Tran); }
public int DbAdd(string strTable,Hashtable hbIn) { return DbAdd((SqlTransaction)null,strTable,hbIn); } public int DbAdd(SqlTransaction p_Tran,string strTable,Hashtable hbIn) { string l_str_Sql="INSERT INTO "+strTable+ " " +CreateInsertSql(hbIn); return this.Execute(l_str_Sql,p_Tran); }
/// <summary> /// 不带事务的修改操作 /// </summary> /// <param name="strTable">表名</param> /// <param name="arrStrField">字段名称列表</param> /// <param name="arrObj">字段值列表</param> /// <param name="strIdField">关键字段名</param> /// <param name="strId">关键字段值</param> /// <returns></returns> protected int DbModify(string strTable,string[] arrStrField,object[] arrObj,string strWhere) { return DbModify((SqlTransaction)null,strTable,arrStrField,arrObj,strWhere); }
/// <summary> /// 带事务的修改操作 /// </summary> /// <param name="p_Tran">事务</param> /// <param name="strTable">表名</param> /// <param name="arrStrField">字段名称列表</param> /// <param name="arrObj">字段值列表</param> /// <param name="strIdField">关键字段名</param> /// <param name="strId">关键字段值</param> /// <returns></returns> protected int DbModify(SqlTransaction p_Tran,string strTable,string[] arrStrField,object[] arrObj,string strWhere) { string l_str_Sql="UPDATE "+strTable+" Set "+CreateUpdateSql(arrStrField,arrObj)+ " "+strWhere; return this.Execute(l_str_Sql,p_Tran); }
public int DbModify(string strTable,Hashtable hbIn,string strWhere) { return DbModify((SqlTransaction)null,strTable,hbIn,strWhere); } public int DbModify(SqlTransaction p_Tran,string strTable,Hashtable hbIn,string strWhere) { string l_str_Sql="UPDATE "+strTable+" SET "+CreateUpdateSql(hbIn)+ " "+strWhere; return this.Execute(l_str_Sql,p_Tran); } /// <summary> /// 不带事务的删除操作,直接提交 /// </summary> /// <param name="strTable">表名</param> /// <param name="strIdField">关键字段名</param> /// <param name="strId">关键字段值</param> /// <returns></returns> protected int DbDelete(string strTable,string strWhere) { return DbDelete((SqlTransaction)null,strTable,strWhere); } /// <summary> /// 带事务的删除操作 /// </summary> /// <param name="p_Tran">事务</param> /// <param name="strTable">表名</param> /// <param name="strIdField">关键字段名</param> /// <param name="strId">关键字段值</param> /// <returns></returns> protected int DbDelete(SqlTransaction p_Tran,string strTable,string strWhere) { string l_str_Sql="DELETE FROM "+strTable+" "+strWhere; return this.Execute(l_str_Sql); }
/// <summary> /// 获取自增长Id /// </summary> /// <param name="p_Tran"></param> /// <returns></returns> public int DbGetIdentity(SqlTransaction p_Tran) { try { string strSql="select @@identity "; SqlCommand sqlcmd = new SqlCommand(strSql,p_Tran.Connection,p_Tran); int i= int.Parse(sqlcmd.ExecuteScalar().ToString()); return i; } catch { return -1; } }
/// <summary> /// 开始事务,创建DbTransaction的关联连接 /// </summary> /// <returns></returns> public int BeginTran() { SqlConnection t_Connection=new SqlConnection(f_ConnString); t_Connection.Open(); f_Tran=t_Connection.BeginTransaction(); return 1; }
/// <summary> /// 提交事务,关闭相关连接 /// </summary> /// <param name="p_Tran"></param> /// <returns></returns> public int CommitTran(SqlTransaction p_Tran) { p_Tran.Commit(); if (p_Tran.Connection!=null) p_Tran.Connection.Close(); return 1; }
/// <summary> /// 回滚事务,关闭相关连接 /// </summary> /// <param name="p_Tran"></param> /// <returns></returns> public int RollbackTran(SqlTransaction p_Tran) { p_Tran.Rollback(); if (p_Tran.Connection!=null) p_Tran.Connection.Close(); return 1; } } }

|
|
相关文章:相关软件: |
|