ASP.NET C# 访问Oracle数据库示例
初级入门的小例子。
首先确保引用了命名空间：
using System.Data;
using System.Data.OracleClient;
定义连接字符串如下：
private const string ConnectionString = "Data Source=mydatabase;user=name;password=pwd;";
1.使用OracleCommand执行无参数简单查询，直接写SQL语句或调用存储过程，使用OracleDataReader遍历显示数据，如下：
protected void GetData1()
{
OracleConnection conn = new OracleConnection(ConnectionString);
try
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
//cmd.CommandText = "select * from TB";
cmd.CommandText = "bowenpkg.getdata";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter parameters = new OracleParameter("refOut", OracleType.Cursor);
parameters.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parameters);
OracleDataReader odr = cmd.ExecuteReader();
while (odr.Read())
{
Response.Write(odr.GetOracleString(1).ToString() + "<br>");
}
odr.Close();
}
catch (Exception ee)
{
Response.Write(ee.Message);
}
finally
{
conn.Close();
}
}
2.使用OracleDataAdapter执行SQL语句，填充DataSet，遍历显示数据，如下：
protected void GetData2()
{
OracleConnection conn = new OracleConnection(ConnectionString);
OracleDataAdapter ad = new OracleDataAdapter("select * from TB", conn);
try
{
DataSet ds = new DataSet();
ad.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Response.Write(dr["code"].ToString() + "<br>");
}
}
catch (Exception ee)
{
Response.Write(ee.Message);
}
finally
{
conn.Close();
}
}
3.带参数的存储过程，如下：
protected void ViewButton_Click(object sender, EventArgs e)
{
OracleConnection conn = new OracleConnection(ConnectionString);
try
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "bowenpkg.getdatabyid";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter[] parameters = {new OracleParameter("rid",OracleType.Number),new OracleParameter("refOut", OracleType.Cursor)};
parameters[0].Direction = ParameterDirection.Input;
parameters[0].Value = DropDownList1.SelectedValue;
parameters[1].Direction = ParameterDirection.Output;
cmd.Parameters.Add(parameters[0]);
cmd.Parameters.Add(parameters[1]);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataTable dt=new DataTable();
da.Fill(dt);
Label1.Text = dt.Rows[0][1].ToString();
}
catch (Exception ee)
{
Response.Write(ee.Message);
}
finally
{
conn.Close();
}
}