分享
 
 
 

Dotnet总结(2)--访问ms sql server 数据库基类

王朝asp·作者佚名  2006-01-29
窄屏简体版  字體: |||超大  

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;

}

}

}

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有