C# 访问Oracle数据库示例

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(); } }