C#对底层数据操作的通用类
2024-07-21 02:19:41
供稿：网友
using system;
using system.data;
using system.data.sqlclient;
using system.windows.forms;
namespace datalibrary
{
/// <summary>
/// 数据连接设置
/// </summary>
public class sqlconnect
{
static string sqlconnstr;
/// <summary>
/// 连接字符串
/// </summary>
public string connstring
{
set
{
sqlconnstr=value;
}
get
{
return sqlconnstr;
}
}
/// <summary>
/// 取得连接
/// </summary>
/// <returns></returns>
public sqlconnection getsqlconnection()
{
sqlconnection _connection=null;
if(sqlconnstr==null || sqlconnstr=="")
{
string systemdir=system.environment.systemdirectory;
disposalconductservice disposalconductservice1= new disposalconductservice(systemdir+"//cberp_dis.dll") ;
sqlconnstr = "server="+disposalconductservice1.getvalue("servername","(local)")
+";user id="+disposalconductservice1.getvalue("sqlaccount","sa")
+";password="+disposalconductservice1.getvalue("password","jiayu520")
+";database="+disposalconductservice1.getvalue("database","cberp");
}
try
{
_connection = new sqlconnection(sqlconnstr);
_connection.open();
}
catch(sqlexception se)
{
if(se.number==17 || se.number==18456)
{
if( messagebox.show("数据库访问参数设置不正确,是否对需要对其进行设置?","提示",messageboxbuttons.yesno,messageboxicon.question)==dialogresult.yes)
{
sqlconnstr="";
disposalconduct config = new disposalconduct();
config.showdialog();
_connection=getsqlconnection();
}
else
{
messagebox.show("建立数据库连接失败,程序不能继续运行!","错误",messageboxbuttons.ok,messageboxicon.stop);
application.exitthread();
application.exit();
}
}
else
{
messagebox.show(se.message,"提示信息",messageboxbuttons.ok,messageboxicon.information);
_connection.close();
}
}
return _connection;
}
}
/// <summary>
/// 数据配置
/// </summary>
public class datacatena : idisposable
{
private sqlconnection _connection;
/// <summary>
/// 数据配置
/// </summary>
public datacatena()
{
sqlconnect sqlconnect = new sqlconnect();
_connection = sqlconnect.getsqlconnection();
_connection.close();
}
/// <summary>
/// 数据配置
/// </summary>
/// <param name="connstr">连接字符串</param>
public datacatena(string connstr)
{
_connection = new sqlconnection(connstr);
}
/// <summary>
/// 取得dataview
/// </summary>
/// <param name="sqlstr">连接字符串</param>
/// <returns>数据视图</returns>
public dataview getdataview(string sqlstr)
{
dataview dv=null;
sqldataadapter mycommand = new sqldataadapter(sqlstr, _connection);
dataset ds = new dataset();
try
{
mycommand.fill(ds,"table");
dv=ds.tables["table"].defaultview;
}
catch(sqlexception exc)
{
debugbox db = new debugbox(sqlstr + "/r/n" + exc.message);
db.show();
}
return dv;
}
/// <summary>
/// 清理资源
/// </summary>
public void dispose()
{
dispose(true);
gc.suppressfinalize(true);
}
/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
/// <param name="disposing"></param>
protected virtual void dispose(bool disposing)
{
if (! disposing)
return;
if (this._connection != null)
{
this._connection.dispose();
}
}
/// <summary>
/// 取得数据适配器
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <returns>数据适配器</returns>
public sqldataadapter getsqlda(string sqlstr)
{
sqldataadapter mycommand = new sqldataadapter(sqlstr, _connection);
return mycommand;
}
/// <summary>
/// 取得数据表
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <returns>数据表</returns>
public datatable getdt(string sqlstr)
{
sqldataadapter mycommand = new sqldataadapter(sqlstr, _connection);
dataset ds = new dataset();
try
{
mycommand.fill(ds,"table");
}
catch(sqlexception exc)
{
debugbox db = new debugbox(sqlstr + "/r/n" + exc.tostring());
db.show();
}
return ds.tables["table"];
}
/// <summary>
/// 执行语句
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <returns></returns>
public object execwithvalue(string sqlstr)
{
object result=null;
_connection.open();
sqlcommand mycommand = new sqlcommand(sqlstr, _connection);
try
{
result=mycommand.executescalar();
}
catch (sqlexception exc)
{
debugbox db = new debugbox(sqlstr + "/r/n" + exc.tostring());
db.show();
//messagebox.show(exc.message);
}
_connection.close();
return result;
}
/// <summary>
/// 返回相应的行数
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <returns>行数</returns>
public int exec(string sqlstr)
{
int sucess=0;
sqlcommand mycommand = new sqlcommand(sqlstr, _connection);
_connection.open();
try
{
mycommand.executenonquery();
}
catch (sqlexception exc)
{
debugbox db = new debugbox(sqlstr + "/r/n" + exc.tostring());
db.show();
// messagebox.show(exc.message);
sucess=exc.number;
}
_connection.close();
return sucess;
}
}
/// <summary>
/// 单表操作
/// </summary>
public class singletable
{
private datacatena datacatena;
private sqldataadapter mydataadapter;
private dataset ds;
/// <summary>
/// 单表操作
/// </summary>
/// <param name="sqlstr"></param>
public singletable(string sqlstr)
{
datacatena = new datacatena();
mydataadapter = datacatena.getsqlda(sqlstr);
ds = new dataset();
mydataadapter.fill(ds, "table1");
}
/// <summary>
/// 更新一个表
/// </summary>
/// <param name="sqlstr"></param>
public void setupdatecommand(string sqlstr)
{
mydataadapter.updatecommand = new sqlcommand(sqlstr,mydataadapter.selectcommand.connection);
}
/// <summary>
/// 返回数据表
/// </summary>
/// <returns></returns>
public datatable getdatatable()
{
return ds.tables["table1"];
}
/// <summary>
/// 返回数据适配器
/// </summary>
/// <returns></returns>
public sqldataadapter getdataadapter()
{
return mydataadapter;
}
/// <summary>
/// 更新表
/// </summary>
public void update()
{
mydataadapter.update(ds, "table1");
}
}
/// <summary>
/// 自动更新
/// </summary>
public class autoupdateobj
{
private datacatena datacatena;
private sqldataadapter mydataadapter;
private sqlcommandbuilder cb;
private dataset ds;
/// <summary>
/// 自动更新
/// </summary>
/// <param name="sqlstr"></param>
public autoupdateobj(string sqlstr)
{
datacatena = new datacatena();
mydataadapter = datacatena.getsqlda(sqlstr);
cb = new sqlcommandbuilder(mydataadapter);
ds = new dataset();
mydataadapter.fill(ds, "table1");
}
/// <summary>
/// 更新sql语句
/// </summary>
/// <param name="sqlstr"></param>
public void setupdatecommand(string sqlstr)
{
mydataadapter.updatecommand = new sqlcommand(sqlstr,mydataadapter.selectcommand.connection);
}
/// <summary>
/// 设置select语句
/// </summary>
/// <param name="sqlstr"></param>
public void setselectcommand(string sqlstr)
{
mydataadapter.selectcommand = new sqlcommand(sqlstr,mydataadapter.selectcommand.connection);
cb.dataadapter = mydataadapter ;
ds.tables.clear();
}
/// <summary>
/// 取得数据表
/// </summary>
/// <returns></returns>
public datatable getdatatable()
{
ds.clear();
mydataadapter.fill(ds, "table1");
return ds.tables["table1"];
}
/// <summary>
/// 取得数据适配器
/// </summary>
/// <returns></returns>
public sqldataadapter getdataadapter()
{
return mydataadapter;
}
/// <summary>
/// 更新
/// </summary>
public void update()
{
try
{
mydataadapter.update(ds, "table1");
}
catch(sqlexception sqlex)
{
messagebox.show("出错原因："+sqlex.message,"出错",messageboxbuttons.ok,messageboxicon.error);
}
}
}
}
