通過存儲過程傳回結果集

前台頁面放置兩個Datagrid：gridMain和gridMain2，通過一個存儲過程傳回兩個結果集，綁定到Datagrid中

1.Oracle傳回結果集

（1）調用方法綁定Datagrid

ExecProcMgr manager = new ExecProcMgr();
manager.ZQDZ_UNDOREAD(gridMain, gridMain2);
           

（2）放置方法的類

引用System.Data.OracleClient.dll

using System.Collections;
using System.Data.OracleClient;
           

public class ExecProcMgr
    {
        private string _zbbh;
        private string _zbmc;
        private string _dwbh;
        private string _smsj;

        public string ZBBH
        {
            get { return _zbbh; }
            set { _zbbh = value; }
        }

        public string ZBMC
        {
            get { return _zbmc; }
            set { _zbmc = value; }
        }

        public string DWBH
        {
            get { return _dwbh; }
            set { _dwbh = value; }
        }

        public string SMSJ
        {
            get { return _smsj; }
            set { _smsj = value; }
        }

        public void ZQDZ_UNDOREAD(DataGrid dataGrid1, DataGrid dataGrid2)
		{
            //string constr = "Provider=MSDAORA.1;Data Source=zqorcl;Persist Security Info=True;User ID=lc029999;Password=htscwyj;Unicode=True";
            string constr = "Data Source=zqorcl;User ID=lc;Password=aaaaaa";
            OracleConnection con = new OracleConnection();
            con.ConnectionString = constr; 
            con.Open();
            
            //OracleTransaction trans = con.BeginTransaction();
            OracleCommand cmd = con.CreateCommand();
            //cmd.Transaction = trans;

            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "ZQDZ_test";//存儲過程名稱

            //存儲過程參數設定
            System.Data.OracleClient.OracleParameter p;
            p = new OracleParameter("userid", System.Data.OracleClient.OracleType.VarChar, 20);
            cmd.Parameters.Add(p);
            p.Direction = System.Data.ParameterDirection.Input;
            p.Value = "zzx";

            p = new OracleParameter("re_cursor1", System.Data.OracleClient.OracleType.Cursor);
            cmd.Parameters.Add(p);
            p.Direction = System.Data.ParameterDirection.Output;//設定為Output

            p = new OracleParameter("re_cursor2", System.Data.OracleClient.OracleType.Cursor);
            cmd.Parameters.Add(p);
            p.Direction = System.Data.ParameterDirection.Output;//設定為Output

            ArrayList list1 = new ArrayList();
            ArrayList list2 = new ArrayList();
            IDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ExecProcMgr execProc = new ExecProcMgr();
                    execProc.ZBBH = Convert.ToString(reader.GetValue(0));
                    execProc.ZBMC = Convert.ToString(reader.GetValue(1));
                    execProc.DWBH = Convert.ToString(reader.GetValue(2));
                    execProc.SMSJ = Convert.ToString(reader.GetValue(3));

                    list1.Add(execProc);
                }
                reader.NextResult();
                while (reader.Read())
                {
                    ExecProcMgr execProc = new ExecProcMgr();
                    execProc.ZBBH = Convert.ToString(reader.GetValue(0));
                    execProc.ZBMC = Convert.ToString(reader.GetValue(1));
                    execProc.DWBH = Convert.ToString(reader.GetValue(2));
                    execProc.SMSJ = Convert.ToString(reader.GetValue(3));

                    list2.Add(execProc);
                }

                dataGrid1.DataSource = list1;
                dataGrid1.DataBind();
                dataGrid2.DataSource = list2;
                dataGrid2.DataBind();
                //trans.Commit();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            
          
		}

    }
           

(3)存儲過程ZQDZ_test

CREATE OR REPLACE Procedure ZQDZ_test
(
userid varchar2,
re_cursor1 OUT sys_refcursor,
re_cursor2 OUT sys_refcursor
)
is
vSql varchar2(3000);
begin
   	vSql:='SELECT distinct ZWPZK_PZBH,ZWPZFL_FLBH,ZWPZFL_KMBH,ZWPZK_PZRQ FROM ZWPZK2011,ZWPZFL2011 
     WHERE ZWPZK_DWBH=ZWPZFL_DWBH AND	ZWPZK_PZNM=ZWPZFL_PZNM AND ZWPZFL_DWBH=''010101'''; 
    open re_cursor1 for vSql;
    
    vSql:='SELECT distinct ZWPZK_PZBH,ZWPZFL_FLBH,ZWPZFL_KMBH,ZWPZK_PZRQ FROM ZWPZK2011,ZWPZFL2011 
     WHERE ZWPZK_DWBH=ZWPZFL_DWBH AND	ZWPZK_PZNM=ZWPZFL_PZNM AND ZWPZFL_DWBH=''010101'' AND ZWPZFL_FLNM=''1'''; 
    open re_cursor2 for vSql;

end ZQDZ_test;
           

2.SQL Server傳回結果集

（1）

public void ZQDZ_SqlServer(DataGrid dataGrid1, DataGrid dataGrid2)
        {
            string constr = "Data Source=(local); Initial Catalog=Text;User ID=sa;Password=sa; Persist Security Info=False";
            SqlConnection con = new SqlConnection();
            con.ConnectionString = constr;
            con.Open();

            //OracleTransaction trans = con.BeginTransaction();
            SqlCommand cmd = con.CreateCommand();
            //cmd.Transaction = trans;

            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "ZQDZ_test";

            SqlParameter p;
            p = new SqlParameter("@userid", System.Data.SqlDbType.VarChar, 20);
            cmd.Parameters.Add(p);
            p.Direction = System.Data.ParameterDirection.Input;
            p.Value = "zzx";

            ArrayList list1 = new ArrayList();
            ArrayList list2 = new ArrayList();
            IDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ExecProcMgr execProc = new ExecProcMgr();
                    execProc.ZBBH = Convert.ToString(reader.GetValue(0));
                    execProc.ZBMC = Convert.ToString(reader.GetValue(1));
                    execProc.DWBH = Convert.ToString(reader.GetValue(2));
                    execProc.SMSJ = Convert.ToString(reader.GetValue(3));

                    list1.Add(execProc);
                }
                reader.NextResult();
                while (reader.Read())
                {
                    ExecProcMgr execProc = new ExecProcMgr();
                    execProc.ZBBH = Convert.ToString(reader.GetValue(0));
                    execProc.ZBMC = Convert.ToString(reader.GetValue(1));
                    execProc.DWBH = Convert.ToString(reader.GetValue(2));
                    execProc.SMSJ = Convert.ToString(reader.GetValue(3));

                    list2.Add(execProc);
                }

                dataGrid1.DataSource = list1;
                dataGrid1.DataBind();
                dataGrid2.DataSource = list2;
                dataGrid2.DataBind();
                //trans.Commit();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                if (reader != null)
                    reader.Close();
            }


        }
           

（2）存儲過程ZQDZ_test

create  procedure ZQDZ_test
(
  @userid varchar(20)
)
as 
begin
  declare @vSql varchar(1000) 
  set @vSql=' select age,name,salary,department from employees'  
  exec(@vSql)
  set @vSql=' select popid,popname,FlId,fconectid from tresj'  
  exec(@vSql)
end