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;
}
}
}
