通用的SQL和Oracle的存储过程执行方法

通用的SQL和Oracle的存储过程执行方法，代码中的ProcessDataBase.DBbind请用SqlDataAdapter.Fill()或者OracleDataAdapter.Fill()自行替换，ProcessDataBase.databasesource是判断数据库类型，大家也可以用其它方法替换。代码只是给大家作为参考，请勿原文Copy，谢谢。

 

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        internal ArrayList ExecProc(string procName, object[] parameters)
        {
            try
            {
                if (connectionType == ConnectionType.Ready)
                {
                    connectionType = ConnectionType.Run;
                    if (ProcessDataBase.databasesource.Trim().ToLower() == "sql")
                    {
                        sqlConnection.Open();
                    }
                    else if (ProcessDataBase.databasesource.Trim().ToLower() == "oracle")
                    {
                        oracleConnection.Open();
                    }
                }
                else
                {
                    errormsg = "Connection is not Ready";
                    ArrayList arrlist = new ArrayList();
                    arrlist.Add(9999);
                    return arrlist;
                }
                errormsg = "";
                if (ProcessDataBase.databasesource.Trim().ToLower() == "sql")
                {
                    ds = ProcessDataBase.DBbind("select syscolumns.name as colname,systypes.name as typename from  syscolumns left join systypes on syscolumns.xtype=systypes.xtype where id=(select id from sysobjects where name='" + procName + "')");
                    SqlCommand cmd = new SqlCommand(procName, sqlConnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < parameters.Length; i++)
                    {
                        cmd.Parameters.Add(new SqlParameter(ds.Tables[0].Rows[i]["colname"].ToString(), parameters[i]));
                    }

                    SqlParameter parmsr = new SqlParameter("returnValue", SqlDbType.Int);
                    parmsr.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(parmsr);
                    cmd.ExecuteNonQuery();
                    connectionType = ConnectionType.Close;
                    sqlConnection.Close();
                    System.Collections.ArrayList arrlist = new System.Collections.ArrayList();
                    arrlist.Add(cmd.Parameters["returnValue"].Value);
                    return arrlist;
                }
                else if (ProcessDataBase.databasesource.Trim().ToLower() == "oracle")
                {
                    ds = ProcessDataBase.DBbind("select argument_name,in_out,case when pls_type='VARCHAR2' then 'VARCHAR' when pls_type='INTEGER' then 'INT16' else pls_type end pls_type from ALL_ARGUMENTS where object_name='" + procName.ToUpper() + "' order by position");
                    string outname = "";
                    OracleCommand cmd = new OracleCommand(procName, oracleConnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        if (ds.Tables[0].Rows[i]["in_out"].ToString() != "OUT")
                        {
                            cmd.Parameters.Add(new OracleParameter(ds.Tables[0].Rows[i]["argument_name"].ToString(), parameters[i]));
                        }
                        else
                        {
                            outname = ds.Tables[0].Rows[i]["argument_name"].ToString();
                            foreach (OracleType st in (OracleType[])System.Enum.GetValues(typeof(OracleType)))
                            {
                                if (st.ToString().ToUpper() == ds.Tables[0].Rows[i]["pls_type"].ToString().ToUpper())
                                {
                                    cmd.Parameters.Add(new OracleParameter(outname, st));
                                    cmd.Parameters[outname].Direction = ParameterDirection.Output;
                                    break;
                                }
                            }
                        }
                    }
                    cmd.ExecuteNonQuery();
                    ArrayList arrlist = new ArrayList();
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        if (ds.Tables[0].Rows[i]["in_out"].ToString() != "OUT")
                        {
                            continue;
                        }
                        else
                        {
                            outname = ds.Tables[0].Rows[i]["argument_name"].ToString();
                            if (ds.Tables[0].Rows[i]["pls_type"].ToString().ToUpper() == "BLOB")
                            {
                                byte[] tmpblob = new byte[((OracleLob)cmd.Parameters[outname].Value).Length];
                                ((OracleLob)cmd.Parameters[outname].Value).Read(tmpblob, 0, Convert.ToInt32(((OracleLob)cmd.Parameters[outname].Value).Length));
                                arrlist.Add(tmpblob);
                            }
                            else
                            {
                                arrlist.Add(cmd.Parameters[outname].Value);
                            }
                           
                        }
                    }
                    connectionType = ConnectionType.Close;
                    oracleConnection.Close();
                    return arrlist;

                }
                else
                {
                    ArrayList arrlist = new ArrayList();
                    arrlist.Add(9999);
                    return arrlist;
                }

            }
            catch (Exception eee)
            {
                if (ProcessDataBase.databasesource.Trim().ToLower() == "sql")
                {
                    sqlConnection.Close();
                }
                else if (ProcessDataBase.databasesource.Trim().ToLower() == "oracle")
                {
                    oracleConnection.Close();
                }
                connectionType = ConnectionType.Close;
                errormsg = eee.Message;
                ArrayList arrlist = new ArrayList();
                arrlist.Add(errormsg);
                return arrlist;
            }
        }
    }