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();
这样执行就可以正常执行下去。

解决方案 »

  1.   

    存储过程调用时，那个参数不匹配的问题，可有人遇到过？
    odp.net与oracle有需要版本匹配的关系吗？
      

  2.   

    namespace FG.DBUtility
    {
        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;
            }        
                    
        }
    }
      

  3.   

      = = 给你一个公司自己公司的ORACLE访问类~
      

  4.   

    具体实现看看这个:
    http://zhidao.baidu.com/question/81881929.html
    http://download.csdn.net/detail/ainizp/3851954