using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.OracleClient;

using System.Data;

using Test.Biz;

using System.Data.SqlClient;

namespace Test.Dal

{

   partial class DaAccessDB

    {

        //Oracle server ------------------------------------------

        private static string CONNECTION_STRING = "DATA SOURCE=10.3.6.77/orcl;PASSWORD=123456;PERSIST SECURITY INFO=True;USER ID=uthv;";

        private static OracleConnection conn = new OracleConnection(CONNECTION_STRING);

        ////SQL server ------------------------------------------

        //private static string ConnectString = "Data Source=localhost;Initial Catalog=TestDB;Persist Security Info=True;User ID=sa;Password=1";

        //private static SqlConnection conn = new SqlConnection(ConnectString);

        internal static List<Biz.BizProduct> GetALL()

        {

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

            if (conn.State == ConnectionState.Closed)

            {

                conn.Open();

            }

            //Oracle server ------------------------------------------

            OracleCommand cmd = new OracleCommand();

            cmd.CommandText = "SELECT * FROM PRODUCTS";

            cmd.CommandType = CommandType.Text;

            cmd.Connection = conn;

            OracleDataAdapter da = new OracleDataAdapter(cmd);

            //end Oracle server ------------------------------------------

            ////SQL server ------------------------------------------

            //SqlCommand cmd = new SqlCommand();

            //cmd.CommandText = "select * from PRODUCTS";

            //cmd.CommandType = CommandType.Text;

            //cmd.Connection = conn;

            //SqlDataAdapter da = new SqlDataAdapter(cmd);

            //end SQL server ------------------------------------------

            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.Closed)

            {

                conn.Open();

            }

            //Oracle server ------------------------------------------

            OracleCommand cmd = new OracleCommand();

            cmd.CommandText = "PRODUCT_ADD";

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("v_PROID", OracleType.Char, 36).Direction = ParameterDirection.Output;

            cmd.Parameters.Add("v_PROCODE", OracleType.Char, 200).Value = bizProduct.ProCode;

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

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

            cmd.Parameters.Add("v_RETURN", OracleType.Number).Direction = ParameterDirection.ReturnValue;

            cmd.Connection = conn;

            int rowsAffected = cmd.ExecuteNonQuery();

            conn.Close();

            // output the result

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

            //end Oracle server ------------------------------------------

            ////SQL server ------------------------------------------

            //SqlCommand cmd = new SqlCommand();

            //cmd.CommandText = "dbo.PRODUCT_ADD";

            //cmd.CommandType = CommandType.StoredProcedure;

            //cmd.Parameters.Add("@ProID", SqlDbType.Char, 36).Direction = ParameterDirection.Output;

            //cmd.Parameters.Add("@ProCode", SqlDbType.NVarChar).Value = bizProduct.ProCode;

            //cmd.Parameters.Add("@Proname", SqlDbType.NVarChar).Value = bizProduct.ProName;

            //cmd.Parameters.Add("@ProLine", SqlDbType.NVarChar).Value = bizProduct.ProLine;

            //cmd.Connection = conn;

            //int rowsAffected = cmd.ExecuteNonQuery();

            //conn.Close();

            //// output the result

            //return cmd.Parameters["@ProID"].Value.ToString();

        }

        internal static string Update(BizProduct bizProduct)

        {

            if (conn.State == ConnectionState.Closed)

            {

                conn.Open();

            }

            //Oracle server ------------------------------------------

            OracleCommand cmd = new OracleCommand();

            cmd.CommandText = "PRODUCT_UPDATE";

            cmd.CommandType = CommandType.StoredProcedure;

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

            cmd.Parameters.Add("v_PROCODE", OracleType.Char, 200).Value = bizProduct.ProCode;

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

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

            cmd.Parameters.Add("v_RETURN", OracleType.Number).Direction = ParameterDirection.ReturnValue;

            cmd.Connection = conn;

            //cmd.ExecuteNonQuery();

            cmd.ExecuteNonQuery();

            conn.Close();

            // output the result

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

            //End Oracle server ------------------------------------------

            //SQL server ------------------------------------------

            //SqlCommand cmd = new SqlCommand();

            //cmd.CommandText = "dbo.PRODUCT_UPDATE";

            //cmd.CommandType = CommandType.StoredProcedure;

            //cmd.Parameters.Add("@ProID", SqlDbType.Char, 36).Value = bizProduct.ProID;

            //cmd.Parameters.Add("@ProCode", SqlDbType.NVarChar).Value = bizProduct.ProCode;

            //cmd.Parameters.Add("@Proname", SqlDbType.NVarChar).Value = bizProduct.ProName;

            //cmd.Parameters.Add("@ProLine", SqlDbType.NVarChar).Value = bizProduct.ProLine;

            //cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

            //cmd.Connection = conn;

            ////cmd.ExecuteNonQuery();

            //cmd.ExecuteNonQuery();

            //conn.Close();

            //// output the result

            //return cmd.Parameters["@Return"].Value.ToString();

        }

        internal static string Delete(BizProduct bizProduct)

        {

            if (conn.State == ConnectionState.Closed)

            {

                conn.Open();

            }

            //Oracle server ------------------------------------------

            OracleCommand cmd = new OracleCommand();

            cmd.CommandText = "PRODUCT_DEL";

            cmd.CommandType = CommandType.StoredProcedure;

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

            cmd.Parameters.Add("v_RETURN", OracleType.Number).Direction = ParameterDirection.ReturnValue;

            cmd.Connection = conn;

            //cmd.ExecuteNonQuery();

            cmd.ExecuteNonQuery();

            conn.Close();

            // output the result

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

            //end Oracle server ------------------------------------------

            ////SQL server ------------------------------------------

            //SqlCommand cmd = new SqlCommand();

            //cmd.CommandText = "dbo.PRODUCT_DEL";

            //cmd.CommandType = CommandType.StoredProcedure;

            //cmd.Parameters.Add("@ProID", SqlDbType.Char, 36).Value = bizProduct.ProID;

            //cmd.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

            //cmd.Connection = conn;

            ////cmd.ExecuteNonQuery();

            //cmd.ExecuteNonQuery();

            //conn.Close();

            //// output the result

            //return cmd.Parameters["@Return"].Value.ToString();

            ////End SQL server ------------------------------------------

        }

    }

}