using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ExecuteProcByOracle
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Btn_LoadData_Click(object sender, EventArgs e)
        {
            // 存儲過程名稱
            string strProcName = "usp_yngr_getInfectionCard";
            // 存儲過程參數(shù)
            OracleParameter[] parameters = {
                                        new OracleParameter("V_BeginTime",OracleType.VarChar),
                                        new OracleParameter("V_EndTime",OracleType.VarChar),
                                        new OracleParameter("V_DateType",OracleType.Number),
                                        new OracleParameter("V_PtName",OracleType.VarChar),
                                        new OracleParameter("V_PtChartNo",OracleType.VarChar),
                                        new OracleParameter("V_DeptCode",OracleType.VarChar),
                                        new OracleParameter("V_CheckedStatus",OracleType.VarChar),
                                        // 返回值的類型是游標類型
                                        new OracleParameter("cur_out",OracleType.Cursor)
                                        };
            // 設(shè)置存儲過程參數(shù)數(shù)組的值和參數(shù)的類型
            parameters[0].Value = "2017-06-01";
            parameters[0].Direction = ParameterDirection.Input;
            parameters[1].Value = "2017-07-31";
            parameters[1].Direction = ParameterDirection.Input;
            parameters[2].Value = 1;
            parameters[2].Direction = ParameterDirection.Input;
            parameters[3].Value = "";
            parameters[3].Direction = ParameterDirection.Input;
            parameters[4].Value = "";
            parameters[4].Direction = ParameterDirection.Input;
            parameters[5].Value = "";
            parameters[5].Direction = ParameterDirection.Input;
            parameters[6].Value = "1";
            parameters[6].Direction = ParameterDirection.Input;
            parameters[7].Direction = ParameterDirection.Output;

            this.dgv_Demo.DataSource = LoadData(strProcName, parameters);
        }

        private DataTable LoadData(string strProcName, params OracleParameter[] parameters)
        {

            DataTable dt = new DataTable();
            string strConn = ConfigurationManager.ConnectionStrings["HealthHospInfection"].ConnectionString;
            using (OracleConnection conn = new OracleConnection(strConn))
            {
                try
                {

                    OracleCommand cmd = new OracleCommand();
                    cmd.CommandText = strProcName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    conn.Open();
                    if (parameters != null)
                    {
                        // 添加參數(shù)
                        cmd.Parameters.AddRange(parameters);
                    }

                    // 取數(shù)據(jù)
                    using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))
                    {

                        adapter.Fill(dt);
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("錯誤:" + ex.Message + "/r/n跟蹤:" + ex.StackTrace);
                }
                finally
                {
                    conn.Close();
                }
            }

            return dt;
        }
    }
}