public class operatedb
{
private static string constr;
public static oledbconnection cnn;
oledbdataadapter da;
oledbcommandbuilder cb;
oledbcommand cmd;
//構(gòu)造函數(shù)
#region initialize
public operatedb()
{
//
// todo: 在此處添加構(gòu)造函數(shù)邏輯
//
cnn=new oledbconnection();
da=new oledbdataadapter();
//不用oledbcommand對象更新到數(shù)據(jù)庫時，必須有下面一行
cb=new oledbcommandbuilder(da);
cmd=new oledbcommand();
}
#endregion initialize
//連接字符串
#region get&setconnectionstring
/// <summary>
/// 獲取連接字符串
/// </summary>
public string myconstr
{
get {return constr;}
set {constr = value;}
}
#endregion get&setconnectionstring
//獲得表的名稱
#region acquiretablenames
/// <summary>
/// 獲取數(shù)據(jù)庫中的表名集合
/// </summary>
/// <returns></returns>
public datatable tablescollection()
{
datatable tbl=new datatable();
try
{
cnn.connectionstring=constr;
cnn.open();
tbl = cnn.getoledbschematable(oledbschemaguid.tables,
new object[] {null, null, null, "table"});
}
catch(exception ce)
{
console.writeline("產(chǎn)生錯誤:/n{0}",ce.message);
}
finally
{
cnn.close();
}
return tbl;
}
#endregion acquiretablenames
//填充數(shù)據(jù)
#region filltable
/// <summary>
/// 填充datatable的查詢
/// </summary>
/// <param name="tblname">數(shù)據(jù)表(必須輸入數(shù)據(jù)庫中存在的名稱,也可以是視圖)</param>
/// <param name="sqlstr">sql語句</param>
/// <returns>記錄條數(shù)</returns>
public int select(datatable tblname,string sqlstr)
{
int i=0;
// try
// {
//
tblname.clear();
da.dispose();
if (cnn.connectionstring=="")
cnn.connectionstring=constr;
if (cnn.state!=connectionstate.open)
cnn.open();
// oledbcommand cmd=new oledbcommand("select * from "+tblname.tablename+" where "+sqlstr,cnn);
cmd.connection=cnn;
cmd.commandtype=commandtype.text;
cmd.commandtext="select * from "+tblname.tablename+" where "+sqlstr;
da.selectcommand=cmd;
i=da.fill(tblname);
//
//
// }
// catch(exception ce)
// {
// console.writeline("產(chǎn)生錯誤:/n{0}",ce.message);
// }
// finally
// {
//this.da.dispose();
cnn.close();
// }
return i;
}
#endregion filltable
//插入記錄
#region insert(use commandbuilder)
/// <summary>
/// 插入記錄(用oledbcommandbuilder)
/// </summary>
/// <param name="tblname">數(shù)據(jù)表</param>
/// <param name="newrow">與表中字段對應的新行</param>
/// <returns>影響的行數(shù)</returns>
public int insert(datatable tblname,datarow newrow)
{
cnn.open();
int i=0;
//
// try
// {
//如何判斷oledbdataadapter是否已經(jīng)dispose
//下面如果不生成新的oledbdataadapter、oledbcommandbuilder、oledbcommand，
//而用原來的全局da,cb,cmd,則在一次操作中只能更新一張表
oledbdataadapter dain=new oledbdataadapter();
oledbcommandbuilder cbin=new oledbcommandbuilder(dain);
oledbcommand cmdin=new oledbcommand("select * from "+tblname.tablename,cnn);
dain.selectcommand=cmdin;
// foreach (datatable dt in da.tablemappings)
// {
// if (dt.tablename!=tblname.tablename)
// dt.clear();
// }
tblname.rows.add(newrow);
i=dain.update(tblname);
//
// }
// catch(exception ce)
// {
// console.writeline("產(chǎn)生錯誤:/n{0}",ce.message);
// }
// finally
// {
// cnn.close();
// }
// cnn.close();
return i;
}
#endregion insert(use commandbuilder)
//插入記錄
#region insert(use insidetransaction,datatable[])
public string insert(datatable[] tbls,datarow[] newrows)
{
int[] num=new int[tbls.length];
int sum=0;
bool judge=false;
string str="";
if (tbls.length==newrows.length)
{
cnn.open();
oledbtransaction tran=cnn.begintransaction();
for (int i=0;i<tbls.length;i++)
{
// this.select(tbls[i],"1=1",tran);
da.insertcommand=insertcmd(tbls[i],"操作編號");
tbls[i].rows.add(newrows[i]);
da.insertcommand.transaction=tran;
try
{
num[i]=da.update(tbls[i]);
sum+=num[i];
}
catch
{
sum=-1;
}
if (num[i]==0)
judge=true;
}
if (judge)
{
tran.rollback();
str="更新失敗";
sum=0;
}
else
{
tran.commit();
str="更新成功";
}
}
cnn.close();
return str+",影響了 "+sum.tostring()+" 條記錄";
}
#endregion insert(use insidetransaction,datatable[])
//插入記錄
#region insert(use outsidetransaction)
/// <summary>
/// 填充datatable(用于事務(wù)處理)
/// </summary>
/// <param name="tblname">表</param>
/// <param name="sqlstr">sql語句</param>
/// <param name="trs">transaction對象</param>
/// <returns>行數(shù)</returns>
public int select(datatable tblname,string sqlstr,oledbtransaction trs)
{
int i=0;
// try
// {
//
tblname.clear();
da.dispose();
if (cnn.connectionstring=="")
cnn.connectionstring=constr;
if (cnn.state!=connectionstate.open)
cnn.open();
// oledbcommand cmd=new oledbcommand("select * from "+tblname.tablename+" where "+sqlstr,cnn);
cmd.connection=cnn;
cmd.commandtype=commandtype.text;
cmd.commandtext="select * from "+tblname.tablename+" where "+sqlstr;
da.selectcommand=cmd;
cmd.transaction=trs;
i=da.fill(tblname);
return i;
}
/// <summary>
/// 插入記錄(用oledbdataadapter.update方法及oledbtransaction)
/// </summary>
/// <param name="tblname">數(shù)據(jù)表</param>
/// <param name="newrow">新行</param>
/// <param name="trs">事務(wù)對象</param>
/// <returns></returns>
public int insert(datatable tblname,datarow newrow,oledbtransaction trs)
{
da.insertcommand=insertcmd(tblname,"noo");
int num=0;
try
{
tblname.rows.add(newrow);
da.insertcommand.transaction=trs;
num=da.update(tblname);
}
catch
{
}
return num;
}
#endregion insert(use outsidetransaction)
//構(gòu)造插入的command
#region insertcommand
/// <summary>
/// 構(gòu)造insertcommand
/// </summary>
/// <param name="dtl">數(shù)據(jù)表</param>
/// <param name="identitycol">identity列的名稱</param>
/// <returns></returns>
private static oledbcommand insertcmd(datatable dtl,string identitycol)
{
oledbcommand incmd=new oledbcommand();
incmd.connection=cnn;
string sqlstr="";
string strvalue="";
sqlstr = "insert " + dtl.tablename.tostring() + "(";
strvalue = ") values (";
for (int i=0;i<dtl.columns.count;i++)
{
//對于identity列無需賦值
if (dtl.columns[i].tostring() != identitycol)
{
sqlstr += "[" + dtl.columns[i].tostring() + "], ";
strvalue +="?,";
oledbparameter mypara = new oledbparameter();
mypara.parametername = "@" + dtl.columns[i].tostring();
mypara.oledbtype = getoledbtype(dtl.columns[i].datatype.tostring());
// mypara.direction = parameterdirection.input;
mypara.sourcecolumn = dtl.columns[i].tostring();
// mypara.sourceversion = datarowversion.current;
incmd.parameters.add(mypara);
}
}
sqlstr=sqlstr.substring(0,sqlstr.length-2);
strvalue=strvalue.substring(0,strvalue.length-1);
sqlstr += strvalue + ")";
incmd.commandtext = sqlstr;
return incmd;
}
#endregion insertcommand
//修改
#region update
/// <summary>
/// 修改記錄
/// </summary>
/// <param name="tblname">數(shù)據(jù)表</param>
/// <param name="strup">sql語句</param>
/// <returns>影響的行數(shù)</returns>
public int update(datatable tblname,string strup)
{
cnn.close();
return i;
}
#endregion update
//刪除
#region del(use commandbuilder)
/// <summary>
/// 刪除記錄
/// </summary>
/// <param name="tblname">數(shù)據(jù)表</param>
/// <param name="strdel">sql語句</param>
/// <returns>影響的行數(shù)</returns>
public int delete(datatable tblname,string strdel) //strdel是刪除條件
{
int rows=0;
//用oledbdataadapter.update方法自動更新必須在where中存在主鍵或唯一值
// try
// {
//
cnn.open();
rows=tblname.rows.count;
for (int i=0;i< tblname.rows.count;i++)
{
tblname.rows[i].delete();
}
//注意，如在da.update前面用了下面的acceptchanges方法，因為記錄被刪除--更新到數(shù)據(jù)庫失敗
//tblname.acceptchanges();
da.update(tblname);
//
// }
// catch(exception ce)
// {
// console.writeline("產(chǎn)生錯誤:/n{0}",ce.message);
// }
// finally
// {
cnn.close();
// }
///
//用oledbcommand直接更新
// try
// {
// string str="delete from "+tblname.tablename+" where "+strdel;
// cnn.open();
// oledbcommand cmdd=new oledbcommand(str,cnn);
// cmdd.commandtype=commandtype.text;
// rows=cmdd.executenonquery();
// }
//
// catch(exception ce)
// {
// console.writeline("產(chǎn)生錯誤:/n{0}",ce.message);
// }
// finally
// {
// cnn.close();
// }
return rows;
}
#endregion del(use commandbuilder)
//構(gòu)造刪除的command
#region delcommand(create oledbdataadapter.deletecommand)
public int delete(datatable tblname)
{
int rows=0;
da.deletecommand=delcmd(tblname);
for (int i=0;i< tblname.rows.count;i++)
{
tblname.rows[i].delete();
}
rows=da.update(tblname);
return rows;
}
private static oledbcommand delcmd(datatable dtl)
{
oledbcommand delcmd=new oledbcommand();
delcmd.connection=cnn;
string sqlstr="";
sqlstr = "delete from " + dtl.tablename.tostring() + " where ";
for (int i=0;i<dtl.columns.count;i++)
{
sqlstr += "([" + dtl.columns[i].tostring() + "] = ? or ? is null and ["+dtl.columns[i].tostring()+"] is null) and";
oledbparameter mypara = new oledbparameter();
mypara.parametername = "or1_" + dtl.columns[i].tostring();
mypara.oledbtype = getoledbtype(dtl.columns[i].datatype.tostring());
mypara.direction = parameterdirection.input;
mypara.sourcecolumn = dtl.columns[i].tostring();
mypara.sourceversion = datarowversion.original;
delcmd.parameters.add(mypara);
int j=delcmd.parameters.count;
bool b=dtl.columns[i].allowdbnull;
if (b)
{
oledbparameter mypara1 = new oledbparameter();
mypara1.parametername = "or2_" + dtl.columns[i].tostring();
mypara1.oledbtype = getoledbtype(dtl.columns[i].datatype.tostring());
mypara1.direction = parameterdirection.input;
mypara1.sourcecolumn = dtl.columns[i].tostring();
mypara1.sourceversion = datarowversion.original;
delcmd.parameters.add(mypara1);
j=delcmd.parameters.count;
}
}
sqlstr=sqlstr.substring(0,sqlstr.length-3);
delcmd.commandtext = sqlstr;
return delcmd;
}
#endregion delcommand(create oledbdataadapter.deletecommand)
#region amenddatabase
public void addcolumn(datatable tblname,string strup) //修改表的結(jié)構(gòu),更新到數(shù)據(jù)庫
{
cnn.open();
// oledbcommand cmds=new oledbcommand("select * from "+tblname.tablename,cnn);
// da.selectcommand=cmds;
// oledbcommandbuilder cb=new oledbcommandbuilder(da);
// datacolumn colitem = new datacolumn(strup,type.gettype("system.string"));
//
// tblname.columns.add(colitem);
//為什么上面的方法不行，只能直接用sql語句嗎?
da.fill(tblname);
da.update(tblname);
}
#endregion amenddatabase
//調(diào)用存儲過程
#region execproc(return datatable)
/// <summary>
/// 執(zhí)行存儲過程
/// </summary>
/// <param name="procname">存儲過程名字</param>
/// <param name="paravalue">參數(shù)的值</param>
/// <param name="paraname">參數(shù)名字</param>
/// <param name="paratype">參數(shù)的類型</param>
/// <returns></returns>
public datatable execproc(string procname,string[] paravalue,string[] paraname,string[] paratype)
{
oledbcommand cmdp=new oledbcommand();
cmdp.connection=cnn;
cmdp.commandtype=commandtype.storedprocedure;
cmdp.commandtext=procname;
for (int i=0;i<paraname.length;i++)
{
oledbparameter pt=new oledbparameter();
paraname[i]="@"+paraname[i];
//參數(shù)名字
//pt.parametername=paraname[i];
pt.sourcecolumn=paraname[i];
pt.oledbtype=getoledbtype(paratype[i]);
pt.value=paravalue[i];
cmdp.parameters.add(pt);
}
datatable dtl=new datatable();
cnn.open();
da.selectcommand=cmdp;
da.fill(dtl);
cnn.close();
return dtl;
}
/// <summary>
/// 設(shè)置oledbparameter對象的dbtype（把字符串變?yōu)橄鄳膐ledbtype類型）
/// </summary>
/// <param name="type">傳入?yún)?shù)的字符串</param>
/// <returns></returns>
private static oledbtype getoledbtype(string type)
{
// try
// {
// return (oledbtype)enum.parse(typeof(oledbtype), type, true);
// }
// catch
// {
// return oledbtype.varchar;
// }
switch (type)
{
case "date":
return oledbtype.dbdate;
break;
case "num":
return oledbtype.integer;
break;
default:
return oledbtype.varchar;
}
}
#endregion execproc(return datatable)
}
