IDC资讯中心
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;
}
}
}
