关于SQLSERVER数据操作的常用类库
2024-07-21 02:23:14
供稿：网友
根据自己平时的经验写的针对sqlserver操作的通用类库
using system;
using system.data;
using system.data.sqlclient;
using system.web;
namespace whgw_lc
{
/// <summary>
/// data 的摘要说明。
/// </summary>
/// <summary>
/// 数据库相关操作类
/// 作者：darkangel
/// 时间：2004-9-24
/// </summary>
public class data
{
/// <summary>
///连接数据库字符串
/// </summary>
protected static string con="data source=10.68.19.203;initial catalog=whgw;persist security info=false;user id=sa;password=sc2000;workstation id=darkangel;packet size=4096";
/// <summary>
/// 数据库连接对象
/// </summary>
protected system.data .sqlclient.sqlconnection dataconnection;
/// <summary>
/// sqldataadapter对象
/// </summary>
protected system.data.sqlclient.sqldataadapter dataadapter;
/// <summary>
/// sqlcommand对象
/// </summary>
protected system.data.sqlclient.sqlcommand datacommand;
/// <summary>
/// sqlcommandbuilder对象
/// </summary>
protected system.data.sqlclient.sqlcommandbuilder datacombuilder;
/// <summary>
/// dataset对象
/// </summary>
protected system.data.dataset dataset;
/// <summary>
/// datarow对象
/// </summary>
protected system.data.datarow datarow;
/// <summary>
/// sqldatareader对象
/// </summary>
protected system.data.sqlclient.sqldatareader datareader;
//protected system.web.ui.page mypage;
/// <summary>
///构造函数
/// </summary>
public data()
{
//
// todo: 连接数据库参数
//
//con=
dataconnection=dataconn();
}
/// <summary>
/// 数据库连接参数属性
/// </summary>
public string con
{
get{return con;}
set{con= value;}
}
/// <summary>
/// 建立数据库连接
/// </summary>
/// <returns>返回数据库连接对象</returns>
public sqlconnection dataconn()
{
sqlconnection dataconnection=new sqlconnection(con);
return dataconnection;
}
/// <summary>
/// 查询结果，返回
/// </summary>
/// <param name="mysql">查询语句</param>
/// <returns>返回datareader</returns>
public sqldatareader dataquery(string sql)
{
dataconnection=dataconn();
dataconnection.open();
datareader=null;
datacommand=new sqlcommand(sql,dataconnection);
datareader=datacommand.executereader();
return datareader;
}
/// <summary>
/// 查询结果，返回dataset
/// </summary>
/// <param name="mysql">查询语句</param>
/// <param name="tabname">表名</param>
/// <returns>dataset</returns>
public virtual dataset datasetquery(string sql,string tablename)
{
dataconnection=dataconn();
dataconnection.open();
dataadapter=new sqldataadapter(sql,dataconnection);
dataset=new dataset();
dataadapter.fill(dataset,tablename);
return dataset;
}
public dataset datasetquery(dataset myds,string tablename,string sql)
{
dataconnection=dataconn();
dataconnection.open();
dataadapter=new sqldataadapter(sql,dataconnection);
dataadapter.fill(myds,tablename);
return dataset;
}
/// <summary>
/// 查询结果，返回dataset，带起始和结束
/// </summary>
/// <param name="mysql">查询语句</param>
/// <param name="tabname">表名</param>
/// <param name="starindex">起始行</param>
/// <param name="count">终止行</param>
/// <returns>返回dataset</returns>
public dataset datasetquery(string sql,string tablename,int starindex,int count)
{
dataconnection=dataconn();
dataconnection.open();
dataadapter=new sqldataadapter(sql,dataconnection);
dataset=new dataset();
dataadapter.fill(dataset,starindex,count,tablename);
return dataset;
}
/// <summary>
/// 修改数据库内容
/// </summary>
/// <param name="mysql">查询语句</param>
/// <returns>如果执行成功返回真，否者返回假</returns>
public bool editdata(string sql)
{
dataconnection=dataconn();
dataconnection.open();
datacommand=new sqlcommand(sql,dataconnection);
try
{
datacommand.executenonquery();
return true;
}
catch(exception ex)
{
system.console.write(ex.message.tostring());
return false;
}
dataconnection.close();
}
/// <summary>
/// 修改dataset内容,并更新数据库
/// </summary>
/// <param name="dataset">dataset对象</param>
/// <param name="myda">sqldataadapter容器</param>
/// <param name="tbname">表名</param>
/// <returns>返回dataset</returns>
public dataset editdata(dataset dataset,sqldataadapter myda,string tablename)
{
datacombuilder=new sqlcommandbuilder(myda);
myda.updatecommand=datacombuilder.getupdatecommand();
myda.deletecommand=datacombuilder.getdeletecommand();
myda.insertcommand=datacombuilder.getinsertcommand();
myda.update(dataset,tablename);
dataset.acceptchanges();
return dataset;
}
/// <summary>
/// 无参数存储过程的执行
/// </summary>
/// <param name="proname">存储过程名称</param>
/// <returns>是否执行成功</returns>
public bool dataprocedure(string procedurename)
{
dataconnection=dataconn();
dataconnection.open();
datacommand=new sqlcommand();
datacommand.commandtext=procedurename;
datacommand.connection=dataconnection;
datacommand.commandtype=system.data.commandtype.storedprocedure;
try
{
datacommand.executenonquery();
return true;
}
catch(exception ex)
{
system.console.write(ex.message.tostring());
return false;
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void closeconnection()
{
dataconnection.close();
}
}
}
