数据库小工具(C#)

using (oracleconnection conn = new oracleconnection(pub.connstring))
{
conn.open();
oraclecommand comm = new oraclecommand(
"select trdate,txcode,drcrf,amount,balance,tellerno,txnote,zoneno,nodeno from detail "+
"where accno=:accno and currtype=:currtype order by accno,currtype,trdate,seqno", conn);
comm.parameters.add("accno", oracledbtype.int64).value = long.parse(acc.substring(4,13));
comm.parameters.add("currtype", oracledbtype.int16).value = curr;
using (oracledatareader r = comm.executereader())
{
for (cnt = 0; r.read(); cnt++)
{
datarow dr = dt.newrow();
dr["trdate"] = r.getdatetime(0);
dr["txcode"] = r.getint32(1);
dr["drcrf"] = icbcetc.getdrcrfstring(r.getint16(2));
dr["amount"] = r.getint64(3) / r;
dr["balance"] = r.getint64(4) / r;
dr["tellerno"] = r.getint32(5);
dr["txnote"] = r.getstring(6);
dr["zoneno"] = r.getint32(7);
dr["nodeno"] = r.getint32(8);
dr["txname"] = dbtrxcode.getnewname((int)dr["txcode"]);
dt.rows.add(dr);
}
}
}

　　为此，我编写了一个小工具，其应用示例如下：

　　这里是源程序(odp.net版)，需要下载“oracle data provider for .net”，其命名空间是: oracle.dataaccess.client。

usingsystem;
usingsystem.data;
usingsystem.text;
usingsystem.windows.forms;
usingsystem.drawing;
usingoracle.dataaccess.client;

namespaceskyiv.util.odpnet
{
classodpnetdlg:form
{
labellbltable;
textboxtbxconn;
textboxtbxsql;
textboxtbxmsg;
buttonbtnsubmit;
checkboxchkstru;
datagriddgout;
stringstrconn="datasource=ora-m38;userid=test;[email protected]";

publicodpnetdlg()
{
suspendlayout();

btnsubmit=newbutton();
btnsubmit.text="执行";
btnsubmit.location=newpoint(10,420);
btnsubmit.size=newsize(60,24);
btnsubmit.click+=neweventhandler(submit_click);
btnsubmit.anchor=(anchorstyles.bottom|anchorstyles.left);

chkstru=newcheckbox();
chkstru.text="结构";
chkstru.location=newpoint(80,420);
chkstru.size=newsize(60,24);
chkstru.anchor=(anchorstyles.bottom|anchorstyles.left);

lbltable=newlabel();
lbltable.text="数据源";
lbltable.location=newpoint(12,460);
lbltable.size=newsize(70,24);
lbltable.anchor=(anchorstyles.bottom|anchorstyles.left);

tbxconn=newtextbox();
tbxconn.text=strconn;
tbxconn.location=newpoint(83,456);
tbxconn.size=newsize(626,20);
tbxconn.anchor=(anchorstyles.bottom|anchorstyles.left|anchorstyles.right);

tbxsql=newtextbox();
tbxsql.text="select*/r/nfromv$version/r/n";
tbxsql.location=newpoint(10,10);
tbxsql.size=newsize(240,200);
tbxsql.multiline=true;
tbxsql.scrollbars=scrollbars.both;
tbxsql.acceptsreturn=true;
tbxsql.wordwrap=true;
tbxsql.anchor=(anchorstyles.top|anchorstyles.left);

tbxmsg=newtextbox();
tbxmsg.location=newpoint(10,220);
tbxmsg.size=newsize(240,190);
tbxmsg.multiline=true;
tbxmsg.scrollbars=scrollbars.both;
tbxmsg.acceptsreturn=true;
tbxmsg.wordwrap=true;
tbxmsg.anchor=(anchorstyles.top|anchorstyles.bottom|anchorstyles.left);

dgout=newdatagrid();
dgout.location=newpoint(260,10);
dgout.size=newsize(450,436);
dgout.captionvisible=false;
dgout.readonly=true;
dgout.anchor=(anchorstyles.top|anchorstyles.bottom|anchorstyles.left|anchorstyles.right);

controls.addrange(newcontrol[]{btnsubmit,chkstru,lbltable,tbxsql,tbxmsg,tbxconn,dgout});
text="数据库查询(odpnet)";
clientsize=newsize(720,490);
windowstate=formwindowstate.maximized;

resumelayout(false);
}

voiddisplayerror(exceptionex)
{
stringbuildersb=newstringbuilder();
while(ex!=null)
{
sb.append(">");
sb.append(ex.gettype());
sb.append(environment.newline);
oracleexceptione=exasoracleexception;
if(e!=null)
{
for(inti=0;i<e.errors.count;i++)sb.appendformat(
"index:{1}{0}message:{2}{0}datasource:{3}{0}source:{4}{0}number:{5}{0}procedure:{6}{0}",environment.newline,
i,e.errors[i].message,e.errors[i].datasource,e.errors[i].source,e.errors[i].number,e.errors[i].procedure
);
}
elsesb.append(ex.message);
sb.append(environment.newline);
ex=ex.innerexception;
}
tbxmsg.text=sb.tostring();
}

voidsubmit_click(objectsender,eventargse)
{
btnsubmit.enabled=false;
stringsql=tbxsql.text.trim();
if(sql.length==0)return;
try
{
introws=-2;
stringstrtype="查询";
using(oracleconnectionconn=neworacleconnection(tbxconn.text))
{
conn.open();
oraclecommandcomm=neworaclecommand(sql,conn);
if(!isquery(sql))
{
strtype="非查询";
rows=comm.executenonquery();
}
elseif(chkstru.checked)
{
strtype="表结构";
dgout.datasource=runquerytablestruct(comm);
}
elsedgout.datasource=runquerytabledata(comm);
}
tbxmsg.text="运行sql语句完毕("+strtype+")";
if(rows>=0)tbxmsg.text="受影响的行数:"+rows.tostring("n0");
}
catch(exceptionex)
{
displayerror(ex);
}
btnsubmit.enabled=true;
}

boolisquery(stringsql)
{
returnsql.substring(0,6).toupper()=="select";
}

privatedataviewrunquerytabledata(oraclecommandcomm)
{
oracledataadapterda=neworacledataadapter();
da.selectcommand=comm;
datasetds=newdataset();
da.fill(ds);
returnds.tables[0].defaultview;
}

privatedataviewrunquerytablestruct(oraclecommandcomm)
{
datatabledt=newdatatable();
dt.columns.add("#",typeof(int));
dt.columns.add("字段名",typeof(string));
dt.columns.add("数据类型",typeof(string));
dt.columns.ad, d("源数据类型",typeof(string));
dt.columns.add("大小",typeof(string));
dt.columns.add("备注",typeof(string));
using(oracledatareaderr=comm.executereader(commandbehavior.keyinfo))
{
datatabledt0=r.getschematable();
//returndt0.defaultview;
foreach(datarowdr0indt0.rows)
{
datarowdr=dt.newrow();
dr[0]=(int)dr0["columnordinal"];
dr[1]=(string)dr0["columnname"];
dr[2]=getbrieftype(dr0["datatype"]);
dr[3]=((oracledbtype)dr0["providertype"]).tostring();
dr[4]=string.format(
"({0},{1}){2}",getint16(dr0["numericprecision"]),getint16(dr0["numericscale"]),(int)dr0["columnsize"]
);
dr[5]=string.format(
"{0}{1}{2}{3}{4}{5}{6}{7}{8}{9}",
istrue(dr0["allowdbnull"])?"allowdbnull":"",
istrue(dr0["iskey"])?"key":"",
istrue(dr0["isunique"])?"unique":"",
istrue(dr0["islong"])?"long":"",
istrue(dr0["isreadonly"])?"readonly":"",
istrue(dr0["isrowid"])?"rowid":"",
istrue(dr0["isaliased"])?"aliased":"",
istrue(dr0["isbytesemantic"])?"bytesemantic":"",
istrue(dr0["isexpression"])?"expression":"",
istrue(dr0["ishidden"])?"hidden":""
);
dt.rows.add(dr);
}
}
returndt.defaultview;
}

boolistrue(objectobj)
{
if(obj==dbnull.value)returnfalse;
return(bool)obj;
}

shortgetint16(objectobj)
{
if(obj==dbnull.value)return-1;
elsereturn(short)obj;
}

stringgetbrieftype(objectobj)
{
strings=(objastype).tostring();
if(string.compareordinal(s,0,"system.",0,7)==0)s=s.substring(7);
returns;
}

staticvoidmain()
{
application.run(newodpnetdlg());
}
}
}

　　此外，该程序还有以下各种版本：
system.data.oracleclient;
system.data.sqlclient;
system.data.oledb;
system.data.odbc;
　　限于篇幅，这里不就贴出源程序了，各位可以自己在odp.net版本的基础上稍做修改就行了。
　　同样是oracle数据库，使用oracle.dataaccess.client和system.data.oracleclient还是有区别的，请参阅:
“comparing the microsoft .net framework 1.1 data provider for oracle and the oracle data provider for .net ”
　　例如，对于数据库中的number类型，oracle.dataaccess.client对应的.net类型可以是byte、short、int、long、decimal等类型，而system.data.oracleclient一般都对应为decimal类型。