在编写有关数据库方面的C#程序时，经常需要知道数据库的表中各字段的以下信息：
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。
using System;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Drawing;
using Oracle.DataAccess.Client;
namespace Skyiv.Util.Odpnet
{
class OdpnetDlg : Form
{
Label lblTable;
TextBox tbxConn;
TextBox tbxSql;
TextBox tbxMsg;
Button btnSubmit;
CheckBox chkStru;
DataGrid dgOut;
string strConn = "Data Source=ora-m38;User ID=test;Password=p@ssw0rd";
public OdpnetDlg()
{
SuspendLayout();
btnSubmit = new Button();
btnSubmit.Text = "执行";
btnSubmit.Location = new Point(10, 420);
btnSubmit.Size = new Size(60, 24);
btnSubmit.Click += new EventHandler(Submit_Click);
btnSubmit.Anchor = (AnchorStyles.Bottom | AnchorStyles.Left);
chkStru = new CheckBox();
chkStru.Text = "结构";
chkStru.Location = new Point(80, 420);
chkStru.Size = new Size(60, 24);
chkStru.Anchor = (AnchorStyles.Bottom | AnchorStyles.Left);
lblTable = new Label();
lblTable.Text = "数据源";
lblTable.Location = new Point(12, 460);
lblTable.Size = new Size(70, 24);
lblTable.Anchor = (AnchorStyles.Bottom | AnchorStyles.Left);
tbxConn = new TextBox();
tbxConn.Text = strConn;
tbxConn.Location = new Point(83, 456);
tbxConn.Size = new Size(626, 20);
tbxConn.Anchor = (AnchorStyles.Bottom | AnchorStyles.Left | AnchorStyles.Right);
tbxSql = new TextBox();
tbxSql.Text = "select *\r\nfrom v$version\r\n";
tbxSql.Location = new Point(10, 10);
tbxSql.Size = new Size(240, 200);
tbxSql.Multiline = true;
tbxSql.ScrollBars = ScrollBars.Both;
tbxSql.AcceptsReturn = true;
tbxSql.WordWrap = true;
tbxSql.Anchor = (AnchorStyles.Top | AnchorStyles.Left);
tbxMsg = new TextBox();
tbxMsg.Location = new Point(10, 220);
tbxMsg.Size = new Size(240, 190);
tbxMsg.Multiline = true;
tbxMsg.ScrollBars = ScrollBars.Both;
tbxMsg.AcceptsReturn = true;
tbxMsg.WordWrap = true;
tbxMsg.Anchor = (AnchorStyles.Top | AnchorStyles.Bottom | AnchorStyles.Left);
dgOut = new DataGrid();
dgOut.Location = new Point(260, 10);
dgOut.Size = new Size(450, 436);
dgOut.CaptionVisible = false;
dgOut.ReadOnly = true;
dgOut.Anchor = (AnchorStyles.Top | AnchorStyles.Bottom | AnchorStyles.Left | AnchorStyles.Right);
Controls.AddRange(new Control[]{btnSubmit, chkStru, lblTable, tbxSql, tbxMsg, tbxConn, dgOut});
Text = "数据库查询(ODPNET)";
ClientSize = new Size(720, 490);
WindowState = FormWindowState.Maximized;
ResumeLayout(false);
}
void DisplayError(Exception ex)
{
StringBuilder sb = new StringBuilder();
while (ex != null)
{
sb.Append("> ");
sb.Append(ex.GetType());
sb.Append(Environment.NewLine);
OracleException e = ex as OracleException;
if (e != null)
{
for (int i = 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
);
}
else sb.Append(ex.Message);
sb.Append(Environment.NewLine);
ex = ex.InnerException;
}
tbxMsg.Text = sb.ToString();
}
void Submit_Click(object sender, EventArgs e)
{
btnSubmit.Enabled = false;
string sql = tbxSql.Text.Trim();
if (sql.Length == 0) return;
try
{
int rows = -2;
string strType = "查询";
using (OracleConnection conn = new OracleConnection(tbxConn.Text))
{
conn.Open();
OracleCommand comm = new OracleCommand(sql, conn);
if (!isQuery(sql))
{
strType = "非查询";
rows = comm.ExecuteNonQuery();
}
else if (chkStru.Checked)
{
strType = "表结构";
dgOut.DataSource = RunQueryTableStruct(comm);
}
else dgOut.DataSource = RunQueryTableData(comm);
}
tbxMsg.Text = "运行 SQL 语句完毕(" + strType + ")";
if (rows >= 0) tbxMsg.Text = "受影响的行数: " + rows.ToString("N0");
}
catch (Exception ex)
{
DisplayError(ex);
}
btnSubmit.Enabled = true;
}
bool isQuery(string sql)
{
return sql.Substring(0, 6).ToUpper() == "SELECT";
}
private DataView RunQueryTableData(OracleCommand comm)
{
OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = comm;
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0].DefaultView;
}
private DataView RunQueryTableStruct(OracleCommand comm)
{
DataTable dt = new DataTable();
dt.Columns.Add("#", typeof(int));
dt.Columns.Add("字段名", typeof(string));
dt.Columns.Add("数据类型", typeof(string));
dt.Columns.Add("源数据类型", typeof(string));
dt.Columns.Add("大小", typeof(string));
dt.Columns.Add("备注", typeof(string));
using (OracleDataReader r = comm.ExecuteReader(CommandBehavior.KeyInfo))
{
DataTable dt0 = r.GetSchemaTable();
//return dt0.DefaultView;
foreach (DataRow dr0 in dt0.Rows)
{
DataRow dr = 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);
}
}
return dt.DefaultView;
}
bool isTrue(object obj)
{
if (obj == DBNull.Value) return false;
return (bool)obj;
}
short GetInt16(object obj)
{
if (obj == DBNull.Value) return -1;
else return (short)obj;
}
string GetBriefType(object obj)
{
string s = (obj as Type).ToString();
if (string.CompareOrdinal(s, 0, "System.", 0, 7) == 0) s = s.Substring(7);
return s;
}
static void Main()
{
Application.Run(new OdpnetDlg());
}
}
}
此外，该程序还有以下各种版本：
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类型。