1. 用于OracleParameter(或SqlParameter,...)中的字段和属性的数据库特定的数据类型。
2. 其对应的.NET数据类型。
如下面的程序片断所示：
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;Password=p@ssw0rd";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类型。
