如何在C#中调用数据库包函数

Question

我有这段代码；但是，当我尝试运行Invoke时，第一个命令将工作并返回正确的值，但是command2给出了一个在C#代码下面发布的错误。为什么C#不能看到环境保护局的方案，我需要做些什么来修正它呢？

如能对此提供任何帮助，将不胜感激。

我试图在PL/SQL中调用的函数的签名是

FUNCTION NotificationGetNextID return integer;

其他潜在的重要信息:我正在使用这个DLL访问数据库http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

//todo set all returns to void so that no one can see internal structure of db
internal static class Data
{
    private const String User = "xxx";
    private const String Pass = "xxx";
    private const String Source = "xxx";
    private const String DateFormat = "dd/MMM/yyyy";

    public static DatabasePackage DatabasePackageFactory(DatabasePackageType T)
    {
        switch (T)
        {
            case DatabasePackageType.EPA:
                return new EPA();
            default:
                return null;
        }

    }

    private class EPA : DatabasePackage
    {
        OracleConnection Conn;
        public EPA()
        {
            Conn = new OracleConnection();
            Conn.ConnectionString += "User Id=" + User + ";";
            Conn.ConnectionString += "Password=" + Pass + ";";
            Conn.ConnectionString += "Data Source=" + Source + ";";
            Conn.Open();
        }

        public object Invoke(String identifier, params String [] args)
        {
            //if you remove the commented out lines the code will not work, however now it works perfectly
            var Command = Conn.CreateCommand();
            //var Command2 = Conn.CreateCommand();

            //Command2.CommandType = CommandType.StoredProcedure;
            Command.CommandType = CommandType.Text;

            Command.CommandText = "select EPA.NotificationGetNextID from dual";// + identifier + parameters;
            //Command2.CommandText = "EPA.NotificationGetNextID";

            //var reader2 = ((Command2.ExecuteNonQuery()));
            var reader = ((Command.ExecuteReader()));

            reader.Read();
            return reader[0];
        }

        public void Dispose()
        {
            Conn.Close();
        }
    }
}

internal interface DatabasePackage : IDisposable
{
    object Invoke(String identifier, params String[] args);
}

internal enum DatabasePackageType
{
    EPA
}

错误：

Oracle.DataAccess.Client.OracleException: ORA-06550: line 1, column 7:
PLS-00221: 'NOTIFICATIONGETNEXTID' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
   at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
   at EPA.Data.EPA.Invoke(String identifier, String[] args)
   at EPA.ElectronicPriceAgreement.CompanyCreate()

它打算如何使用

  using (DatabasePackage dbp = Data.DatabasePackageFactory(DatabasePackageType.EPA))
            {
                return dbp.Invoke("NotificationGetNextId");
                //return dbp.Invoke("CompanyCreate","key","description","","","","");
                //return result;
            }

Answer 1

这不起作用的原因是因为我调用的函数不正确。为了得到渴望的结果，调用必须是这样的。

 public object Invoke(String identifier, params object[] args)
        {
            using (var Conn = new OracleConnection(ConnectionString))
            {
                using (var Command = new OracleCommand())
                {
                    Command.Connection = Conn;
                    Command.CommandText = "EPA."+ identifier;
                    Command.CommandType = CommandType.StoredProcedure;

                    using (var param = new OracleParameter())
                    {
                        param.OracleDbType = OracleDbType.Int32;
                        param.Direction = ParameterDirection.ReturnValue;
                        param.ParameterName = "return";
                        Command.Parameters.Add(param);
                    }
                    int nCount = 0;
                    foreach(object o in args)
                    {
                        using (var param = new OracleParameter())
                        {
                            param.OracleDbType = GetOracleDbType(o);
                            param.Direction = ParameterDirection.Input;
                            param.ParameterName = "arg" + nCount++;
                            param.Value = o;

                            if(param.OracleDbType != OracleDbType.Date)
                                Command.Parameters.Add(param);
                            else{
                                DateTime dt = (DateTime)o;
                                Command.Parameters.Add(dt.ToString(DateFormat),OracleDbType.Date).Value = dt;
                            }
                        }
                    }

                    Conn.Open();
                    Command.ExecuteNonQuery();
                    return Int32.Parse((Command.Parameters["return"].Value).ToString());
                }
            }
        }

首先，我需要关闭所有实现IDipsosable的对象。一开始我没做过的事。

其次，我需要将命令类型更改为StoredProcedure，并设置返回类型。Oracle关心顺序，因此应该按照函数接收它们的顺序来设置。回报应该是第一位的。

用于确定数据类型的函数是我从某个地方复制的，尽管我不记得从哪里抓取的，我很抱歉没有给最初的创建者适当的信任。

private OracleDbType GetOracleDbType(object o) 
        {
            if (o is string) 
                return OracleDbType.Varchar2;
            if (o is DateTime) 
                return OracleDbType.Date;
            if (o is Int64) 
                return OracleDbType.Int64;
            if (o is Int32) 
                return OracleDbType.Int32;
            if (o is Int16) 
                return OracleDbType.Int16;
            if (o is byte) 
                return OracleDbType.Byte;
            if (o is decimal) 
                return OracleDbType.Decimal;
            if (o is float) 
                return OracleDbType.Single;
            if (o is double) 
                return OracleDbType.Double;
            if (o is byte[]) 
                return OracleDbType.Blob;
            return OracleDbType.Varchar2;
        }