在編寫有關(guān)數(shù)據(jù)庫方面的C#程序時，經(jīng)常需要知道數(shù)據(jù)庫的表中各字段的以下信息：
1. 用于OracleParameter(或SqlParameter,...)中的字段和屬性的數(shù)據(jù)庫特定的數(shù)據(jù)類型。
2. 其對應的.NET數(shù)據(jù)類型。
如下面的程序片斷所示：
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 = "執(zhí)行";
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 = "結(jié)構(gòu)";
chkStru.Location = new Point(80, 420);
chkStru.Size = new Size(60, 24);
chkStru.Anchor = (AnchorStyles.Bottom | AnchorStyles.Left);
lblTable = new Label();
lblTable.Text = "數(shù)據(jù)源";
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 = "數(shù)據(jù)庫查詢(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 = "表結(jié)構(gòu)";
dgOut.DataSource = RunQueryTableStruct(comm);
}
else dgOut.DataSource = RunQueryTableData(comm);
}
tbxMsg.Text = "運行 SQL 語句完畢(" + strType + ")";
if (rows >= 0) tbxMsg.Text = "受影響的行數(shù): " + 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("數(shù)據(jù)類型", typeof(string));
dt.Columns.Add("源數(shù)據(jù)類型", 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數(shù)據(jù)庫，使用Oracle.DataAccess.Client和System.Data.OracleClient還是有區(qū)別的，請參閱:
“Comparing the Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NET ”
例如，對于數(shù)據(jù)庫中的NUMBER類型，Oracle.DataAccess.Client對應的.NET類型可以是byte、short、int、long、decimal等類型，而System.Data.OracleClient一般都對應為decimal類型。
原文：http://www.cnblogs.com/skyivben/archive/2005/10/09/251212.html