public class operatedb
{
private static string constr;
public static oledbconnection cnn;
oledbdataadapter da;
oledbcommandbuilder cb;
oledbcommand cmd;
//构造函数
#region initialize
public operatedb()
{
//
// todo: 在此处添加构造函数逻辑
//
cnn=new oledbconnection();
da=new oledbdataadapter();
//不用oledbcommand对象更新到数据库时，必须有下面一行
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>
/// 获取数据库中的表名集合
/// </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("产生错误:/n{0}",ce.message);
}
finally
{
cnn.close();
}
return tbl;
}
#endregion acquiretablenames
//填充数据
#region filltable
/// <summary>
/// 填充datatable的查询
/// </summary>
/// <param name="tblname">数据表(必须输入数据库中存在的名称,也可以是视图)</param>
/// <param name="sqlstr">sql语句</param>
/// <returns>记录条数</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("产生错误:/n{0}",ce.message);
// }
// finally
// {
//this.da.dispose();
cnn.close();
// }
return i;
}
#endregion filltable
//插入记录
#region insert(use commandbuilder)
/// <summary>
/// 插入记录(用oledbcommandbuilder)
/// </summary>
/// <param name="tblname">数据表</param>
/// <param name="newrow">与表中字段对应的新行</param>
/// <returns>影响的行数</returns>
public int insert(datatable tblname,datarow newrow)
{
cnn.open();
int i=0;
//
// try
// {
//如何判断oledbdataadapter是否已经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("产生错误:/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(用于事务处理)
/// </summary>
/// <param name="tblname">表</param>
/// <param name="sqlstr">sql语句</param>
/// <param name="trs">transaction对象</param>
/// <returns>行数</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">数据表</param>
/// <param name="newrow">新行</param>
/// <param name="trs">事务对象</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)
//构造插入的command
#region insertcommand
/// <summary>
/// 构造insertcommand
/// </summary>
/// <param name="dtl">数据表</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">数据表</param>
/// <param name="strup">sql语句</param>
/// <returns>影响的行数</returns>
public int update(datatable tblname,string strup)
{
cnn.close();
return i;
}
#endregion update
//删除
#region del(use commandbuilder)
/// <summary>
/// 删除记录
/// </summary>
/// <param name="tblname">数据表</param>
/// <param name="strdel">sql语句</param>
/// <returns>影响的行数</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方法，因为记录被删除--更新到数据库失败
//tblname.acceptchanges();
da.update(tblname);
//
// }
// catch(exception ce)
// {
// console.writeline("产生错误:/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("产生错误:/n{0}",ce.message);
// }
// finally
// {
// cnn.close();
// }
return rows;
}
#endregion del(use commandbuilder)
//构造删除的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) //修改表的结构,更新到数据库
{
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
//调用存储过程
#region execproc(return datatable)
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procname">存储过程名字</param>
/// <param name="paravalue">参数的值</param>
/// <param name="paraname">参数名字</param>
/// <param name="paratype">参数的类型</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];
//参数名字
//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>
/// 设置oledbparameter对象的dbtype（把字符串变为相应的oledbtype类型）
/// </summary>
/// <param name="type">传入参数的字符串</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)
}
