using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.OracleClient;

using System.Data;

using Test.Biz;

namespace Test.Dal

{

   partial class DalOracle

    {

       private static string CONNECTION_STRING = "Data Source=10.3.6.77/orcl;User ID=dbo_CRM;Password=123456;";

        private static OracleConnection conn = new OracleConnection(CONNECTION_STRING);

        internal static List<Biz.BizProduct> GetALL()

        {

            List<BizProduct> pros = new List<BizProduct>();

            OracleCommand cmd = new OracleCommand();

            cmd.CommandText="SELECT * FROM PRODUCTS";

            cmd.CommandType = CommandType.Text;

            cmd.Connection = conn;

            conn.Open();

                //using (OracleDataReader rdr = cmd.ExecuteReader())

                //{

                //  while (rdr.Read())

                //  {

                //      BizProduct pro = new BizProduct();

                //      pro.ProID = rdr.GetValue(0).ToString();

                //      pro.ProCode = rdr.GetValue(1).ToString();

                //      pro.ProName = rdr.GetValue(2).ToString();

                //      pro.ProLine = rdr.GetValue(3).ToString();

                //      pros.Add(pro);

                //  }

                //}

                //conn.Close();

                //return pros;

            //OracleDataAdapter da = new OracleDataAdapter(cmd);

            //DataTable tb = new DataTable();

            //DataSet ds = new DataSet();

            ////da.TableMappings.Add("Table", "Pro");

            //da.Fill(ds);

            //conn.Close();

            //if (ds.Tables["Pro"].Rows.Count != 0)

            //{

            //    for (int i = 0; i < ds.Tables["Pro"].Rows.Count; i++)

            //    {

            //        BizProduct pro = new BizProduct();

            //        pro.ProID = ds.Tables["Pro"].Rows[i][0].ToString();

            //        pro.ProCode = ds.Tables["Pro"].Rows[i][1].ToString();

            //        pro.ProName = ds.Tables["Pro"].Rows[i][2].ToString();

            //        pro.ProLine = ds.Tables["Pro"].Rows[i][3].ToString();

            //        pros.Add(pro);

            //    }

            //    return pros;

            //}

            //return null;

            OracleDataAdapter da = new OracleDataAdapter(cmd);

            DataTable tb = new DataTable();

            da.Fill(tb);

            conn.Close();

            if (tb.Rows.Count != 0)

            {

                for (int i = 0; i < tb.Rows.Count; i++)

                {

                    BizProduct pro = new BizProduct();

                    pro.ProID = tb.Rows[i][0].ToString();

                    pro.ProCode = tb.Rows[i][1].ToString();

                    pro.ProName = tb.Rows[i][2].ToString();

                    pro.ProLine = tb.Rows[i][3].ToString();

                    pros.Add(pro);

                }

                return pros;

            }

            return null;

        }

        internal static string Add(BizProduct bizProduct)

        {

            if(conn.State== ConnectionState.Open)

            {

                conn.Close();

            }

            try

            {

                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "Products_Add";

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("v_ProductID", OracleType.Char, 36).Direction = ParameterDirection.ReturnValue;

                cmd.Parameters.Add("v_ProductCode", OracleType.NVarChar, 50).Value =bizProduct.ProCode;

                cmd.Parameters.Add("v_ProductName", OracleType.NVarChar,255).Value = bizProduct.ProName;

                cmd.Parameters.Add("v_ProductLine", OracleType.NVarChar,50).Value = bizProduct.ProLine;

                cmd.Parameters.Add("v_Return", OracleType.Char, 36).Direction = ParameterDirection.ReturnValue;

                cmd.Connection = conn;

                conn.Open();

                int rowsAffected = cmd.ExecuteNonQuery();

                conn.Close();

                // output the result

                return cmd.Parameters["v_ProductID"].Value.ToString();

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        internal static string Update(BizProduct bizProduct)

        {

            if (conn.State == ConnectionState.Open)

            {

                conn.Close();

            }

            try

            {

                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "Products_Modify";

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("v_ProductID", OracleType.Char, 36).Value = bizProduct.ProID;

                cmd.Parameters.Add("v_ProductCode", OracleType.NVarChar, 50).Value = bizProduct.ProCode;

                cmd.Parameters.Add("v_ProductName", OracleType.NVarChar, 255).Value = bizProduct.ProName;

                cmd.Parameters.Add("v_ProductLine", OracleType.NVarChar, 50).Value = bizProduct.ProLine;

                cmd.Parameters.Add("v_Return", OracleType.Char, 36).Direction = ParameterDirection.ReturnValue;

                cmd.Connection = conn;

                conn.Open();

                int rowsAffected = cmd.ExecuteNonQuery();

                conn.Close();

                // output the result

                return rowsAffected.ToString();

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

    }

}