using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;
using System.Collections;
using System.Windows.Forms;
namespace SinosysHardware.DataAccess
{
    class HelperDB
    {
        public SqlCeConnection GetConnection()
        {

            string constr = @"Data Source=\Program Files\HardwareDistributor.sdf";
            SqlCeConnection Con = new SqlCeConnection(constr);
            return Con;
        }

        public DataSet GetDataSet(string Sql, string tablename)
        {
            SqlCeConnection Con = this.GetConnection();
            SqlCeCommand Cmd = new SqlCeCommand(Sql, Con);

            SqlCeDataAdapter Sda = new SqlCeDataAdapter(Cmd);
            DataSet Ds = new DataSet();
            try
            {
                Sda.Fill(Ds, tablename);
                return Ds;
            }
            catch (SqlCeException ex)
            {
                if (Con.State != ConnectionState.Closed)
                {
                    Con.Close();
                }
                MessageBox.Show(ex.Message);
            }
            return null;
        }


        public DataSet GetDataSet(string Sql, string tablename,params SqlCeParameter[] paras)
        {
            SqlCeConnection Con = this.GetConnection();
            SqlCeCommand Cmd = new SqlCeCommand(Sql, Con);
            foreach (SqlCeParameter p in paras)
            {
                Cmd.Parameters.Add(p);
            }
            SqlCeDataAdapter Sda = new SqlCeDataAdapter(Cmd);
            DataSet Ds = new DataSet();
            try
            {
                Sda.Fill(Ds, tablename);
                return Ds;
            }
            catch (SqlCeException ex)
            {
                if (Con.State != ConnectionState.Closed)
                {
                    Con.Close();
                }
                MessageBox.Show(ex.Message);
            }
            return null;
        }

        public int ExcuteSql(string Sql, SqlCeParameter[] paras)
        {
            int flag = 0;
            SqlCeConnection Con = this.GetConnection();
            SqlCeCommand Cmd = new SqlCeCommand(Sql, Con);
            foreach (SqlCeParameter p in paras)
            {
                Cmd.Parameters.Add(p);
            }
            try
            {
                Con.Open();
                flag = Cmd.ExecuteNonQuery();

            }
            catch (SqlCeException ex)
            {

                MessageBox.Show(ex.Message);
            }
            finally
            {
                Con.Close();
            }
            return flag;

        }

        public object ExcuteScale(string Sql, params SqlCeParameter[] paras)
        {
            object flag = null;
            SqlCeConnection Con = this.GetConnection();
            SqlCeCommand Cmd = new SqlCeCommand(Sql, Con);
            if (paras != null)
            {
                foreach (SqlCeParameter p in paras)
                {
                    Cmd.Parameters.Add(p);
                }
            }
            try
            {
                Con.Open();
                flag = Cmd.ExecuteScalar();

            }
            catch (SqlCeException ex)
            {

                MessageBox.Show(ex.Message);
            }
            finally
            {
                Con.Close();
            }
            return flag;

        }
        public object ExcuteScaleID(string[] Sql, params SqlCeParameter[] paras)
        {
            object flag = null;
            SqlCeConnection Con = this.GetConnection();
            SqlCeCommand Cmd = new SqlCeCommand();
            Cmd.Connection = Con;
            if (paras != null)
            {
                foreach (SqlCeParameter p in paras)
                {
                    Cmd.Parameters.Add(p);
                }
            }
            try
            {
                Con.Open();
                Cmd.CommandText = Sql[0];
                Cmd.ExecuteNonQuery();
                Cmd.CommandText = Sql[1];
                flag = Cmd.ExecuteScalar();

            }
            catch (SqlCeException ex)
            {

                MessageBox.Show(ex.Message);
            }
            finally
            {
                Con.Close();
            }
            return flag;

        }     
    }
}
