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