C#如何访问oracle？
跟别人的项目要对接，人家数据库用的oracle10g，有点小问题，问问大家，帮忙看看1.我从oracle网站上现下的odp.net visual tool，具体该下什么版本我不太清楚，就选了最新发布的，装上
2.visual studio中建个pc应用程序，添加引用，添加上去的是oracle.DataAccess
3.一般的连接、查询数据库，没有什么问题。
碰到的问题
问题1.执行一个存储过程GetNo
5个参数,2个输出
shisid input varchar2
sinhospitalno input varchar2
sbl input varchar2
sphotono output varchar2
istudyid output float
很奇怪的是，我执行存储过程时，按正常的，4个string，1个float类型添加参数，程序执行报错。
我用3个string，最后两个都用float，执行反而正常。
我用pldev去查看了下存储过程的参数，确实是4个varchar2，一个float。
问题2.参数化查询
要执行一个insert语句，我用拼接字符串的方式，执行是成功的，写parameter的话，执行却报错
string sql = "insert into HHRIS.R_BLREQUSITION (HISID)";
sql += " values (@HISID)";
OracleParameter[] parameters = new OracleParameter[]
{
new OracleParameter("@HISID", OracleDbType.Varchar2,“123”,ParameterDirection.Input),
}
command.CommandText=sql;
command.Parameters.AddRange(parameters);
int rowEffected=command.ExecuteNonQuery();
这样写参数化的话，就报错，提示miss expresstion.
string sql = "insert into HHRIS.R_BLREQUSITION (HISID) valuse ('123')"
command.CommandText=sql;
int rowEffected=command.ExecuteNonQuery();
这样执行就可以正常执行下去。
--------------------编程问答-------------------- 我记得参数要用:
:HISID --------------------编程问答--------------------
好的，感谢感谢，被一般的参数查询带@误导了 --------------------编程问答-------------------- 存储过程调用时，那个参数不匹配的问题，可有人遇到过？
odp.net与oracle有需要版本匹配的关系吗？ --------------------编程问答--------------------
namespace FG.DBUtility--------------------编程问答-------------------- = = 给你一个公司自己公司的ORACLE访问类~ --------------------编程问答-------------------- 具体实现看看这个:
{
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;
public abstract class OracleHelper
{
public static readonly string ConnectionStringInventoryDistributedTransaction = ConfigurationManager.AppSettings["OraConnString2"];
public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.AppSettings["OraConnString1"];
public static readonly string ConnectionStringMembership = ConfigurationManager.AppSettings["OraMembershipConnString"];
public static readonly string ConnectionStringOrderDistributedTransaction = ConfigurationManager.AppSettings["OraConnString3"];
public static readonly string ConnectionStringProfile = ConfigurationManager.AppSettings["OraProfileConnString"];
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
protected OracleHelper()
{
}
public static void CacheParameters(string cacheKey, params OracleParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}
public static int ExecuteNonQuery(string connectionString, string cmdText)
{
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(connectionString);
PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, null);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return num;
}
public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return num;
}
public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return num;
}
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
using (OracleConnection connection = new OracleConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int num = cmd.ExecuteNonQuery();
connection.Close();
cmd.Parameters.Clear();
return num;
}
}
public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleDataReader reader2;
OracleCommand cmd = new OracleCommand();
OracleConnection conn = new OracleConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
reader2 = reader;
}
catch
{
conn.Close();
throw;
}
return reader2;
}
public static object ExecuteScalar(OracleConnection connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connectionString, null, cmdType, cmdText, commandParameters);
object obj2 = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return obj2;
}
public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if ((transaction != null) && (transaction.Connection == null))
{
throw new ArgumentException("The transaction was rollbacked\tor commited, please\tprovide\tan open\ttransaction.", "transaction");
}
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
object obj2 = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return obj2;
}
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
using (OracleConnection connection = new OracleConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object obj2 = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return obj2;
}
}
public static bool ExecuteSqlTran(string conStr, List<CommandInfo> cmdList)
{
using (OracleConnection connection = new OracleConnection(conStr))
{
connection.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
OracleTransaction trans = connection.BeginTransaction();
cmd.Transaction = trans;
try
{
foreach (CommandInfo info in cmdList)
{
if (string.IsNullOrEmpty(info.CommandText))
{
continue;
}
PrepareCommand(cmd, connection, trans, CommandType.Text, info.CommandText, (OracleParameter[])info.Parameters);
if ((info.EffentNextType == EffentNextType.WhenHaveContine) || (info.EffentNextType == EffentNextType.WhenNoHaveContine))
{
if (info.CommandText.ToLower().IndexOf("count(") == -1)
{
trans.Rollback();
throw new Exception("Oracle:违背要求" + info.CommandText + "必须符合select count(..的格式");
}
object obj2 = cmd.ExecuteScalar();
bool flag = false;
if ((obj2 == null) && (obj2 == DBNull.Value))
{
flag = false;
}
flag = Convert.ToInt32(obj2) > 0;
if ((info.EffentNextType == EffentNextType.WhenHaveContine) && !flag)
{
trans.Rollback();
throw new Exception("Oracle:违背要求" + info.CommandText + "返回值必须大于0");
}
if ((info.EffentNextType == EffentNextType.WhenNoHaveContine) && flag)
{
trans.Rollback();
throw new Exception("Oracle:违背要求" + info.CommandText + "返回值必须等于0");
}
continue;
}
int num = cmd.ExecuteNonQuery();
if ((info.EffentNextType == EffentNextType.ExcuteEffectRows) && (num == 0))
{
trans.Rollback();
throw new Exception("Oracle:违背要求" + info.CommandText + "必须有影像行");
}
}
trans.Commit();
}
catch (OracleException exception)
{
trans.Rollback();
throw exception;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
return true;
}
}
}
http://zhidao.baidu.com/question/81881929.html
http://download.csdn.net/detail/ainizp/3851954
补充：.NET技术 , C#
