using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Configuration;

namespace Aram_CRM_Project.DAL
{
    class DA
    {

    #region Private Variables
    private string Sql;
    private SqlCommand Command;
    private SqlDataAdapter DataAdapter;
    private SqlConnection Connection;
    private SqlTransaction sqlTran;
    #endregion
    // Constructor
    
    public DA()
    {
        Connection = new SqlConnection(Aram_CRM_Project.BLL.Entity.StaticVariable.DataSource);
    }


    // Open Connection
    public void OpenConnection()
    {
        try
        {
            Connection.ConnectionString = Aram_CRM_Project.BLL.Entity.StaticVariable.DataSource;
            if (Connection.State != ConnectionState.Open)
                Connection.Open();
        }
        catch (Exception exp)
        {
            MessageBox.Show(exp.Message);
        }
    }

    

    public int CheckOpenConnection()
    {
        try
        {
            Connection.ConnectionString = Aram_CRM_Project.BLL.Entity.StaticVariable.DataSource;
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
                return 1;
            }
            else
                return -2;
        }
        catch (Exception exp)
        {
            if (exp.Message.Contains("Cannot open database"))
            {
                return 0;
            }
            else if (exp.Message.Contains("An error has occurred while establishing a connection to the server"))
            {
                MessageBox.Show(exp.Message);
                return -1;
            }
            else
            {
                MessageBox.Show(exp.Message);
                return -2;
            }

        }
    }
    
    // Close Connection
    public void CloseConnection()
    {
        if (Connection.State == ConnectionState.Open)
            Connection.Close();
    }
    // Initial Command
    public void InitializeCommand()
    {
        try
        {
            Command = new SqlCommand(Sql, Connection);
            if (!Sql.ToUpper().StartsWith("INSERT") && !Sql.ToUpper().StartsWith("SELECT") && !Sql.ToUpper().StartsWith("UPDATE") && !Sql.ToUpper().StartsWith("DELETE") && !Sql.ToUpper().StartsWith("USE") && !Sql.ToUpper().StartsWith("ALTER") && !Sql.ToUpper().StartsWith("BACKUP"))
                Command.CommandType = CommandType.StoredProcedure;
        }
        catch (Exception exp)
        {
         //   Education.BusinessLogic.ErrorHandler.ConvertErrorToPersian(exp.Message);

        }

    }
    public void InitializeCommand(string paramName, object ParamValue)
    {
        try
        {
            Command = new SqlCommand(Sql, Connection);
            if (!Sql.ToUpper().StartsWith("INSERT") && !Sql.ToUpper().StartsWith("SELECT") && !Sql.ToUpper().StartsWith("UPDATE") && !Sql.ToUpper().StartsWith("DELETE"))
                Command.CommandType = CommandType.StoredProcedure;
            Command.Parameters.AddWithValue(paramName, ParamValue);
        }
        catch (Exception exp)
        {
           // Education.BusinessLogic.ErrorHandler.ConvertErrorToPersian(exp.Message);
        }


    }
    // AddParameter
    public void AddParameter(string Name, object Value)
    {
        try
        {
            Command.Parameters.AddWithValue(Name, Value);

        }
        catch (Exception exp)
        {
           // Education.BusinessLogic.ErrorHandler.ConvertErrorToPersian(exp.Message);
        }
    }
    // InitializeDataAdapter
    public void InitializeDataAdaptter()
    {
        try
        {
            DataAdapter = new SqlDataAdapter();
            DataAdapter.SelectCommand = Command;

        }
        catch (Exception exp)
        {
            //Education.BusinessLogic.ErrorHandler.ConvertErrorToPersian(exp.Message);
        }
    }
    public void InitializeTransaction()
    {
        Connection.Open();
        Command = Connection.CreateCommand();
        Command.CommandText = Sql;
        if (!Sql.ToUpper().StartsWith("INSERT") && !Sql.ToUpper().StartsWith("SELECT") && !Sql.ToUpper().StartsWith("UPDATE") && !Sql.ToUpper().StartsWith("DELETE"))
            Command.CommandType = CommandType.StoredProcedure;

        // must initial SQL and Parameters            
    }

    public void PerformTransaction()
    {

        SqlTransaction sqlTran = Connection.BeginTransaction();

        Command.Transaction = sqlTran;
        try
        {
            Command.ExecuteNonQuery();
            sqlTran.Commit();
        }
        catch (Exception ex)
        {
            //Education.BusinessLogic.ErrorHandler.ConvertErrorToPersian(ex.Message);
            sqlTran.Rollback();
        }
    }
    // property
    public string SQL
    {
        get
        {
            return Sql;
        }
        set
        {
            Sql = value;
        }
    }
    public SqlConnection ConnectionString
    {
        get
        {
            return Connection;
        }
        set
        {
            //if (System.Configuration.ConfigurationManager.ConnectionStrings[value.ToString()].ConnectionString != null)
            //{
                Connection = ConnectionString;
            //}
            //else
            //{
            //    //MessageBox.Show (

            //}
        }

    }
    //FillDataSet
    public void FillDataSet(DataSet ObjDataSet, String TableName)
    {
        try
        {
            //InitializeCommand();
            InitializeDataAdaptter();
            DataAdapter.Fill(ObjDataSet, TableName);
        }
        catch (Exception exp)
        {
            //Education.BusinessLogic.ErrorHandler.ConvertErrorToPersian(exp.Message);
        }
        finally
        {
            Command.Dispose();
            Command = null;
            DataAdapter.Dispose();
            DataAdapter = null;
        }
    }
    //FillDataTable
    public void FillDataTable(DataTable ObjDataTable)
    {
        try
        {
            //نمی توان چون نمی شود پارامتر پاس داد
            //InitializeCommand();
            InitializeDataAdaptter();
            DataAdapter.Fill(ObjDataTable);

        }
        catch (Exception exp)
        {
            MessageBox.Show(exp.Message);
        }
        finally
        {
            //Command.Dispose ();
            //Command=null;
            //DataAdapter.Dispose ();
            //DataAdapter =null;
        }

    }
    //ExecuteStoreProcedure
    public int ExecuteStoredProcedure()
    {
      // try
       // {
            OpenConnection();
            int temp = Command.ExecuteNonQuery();
            CloseConnection();
            return temp;
      //  }
      //  catch (Exception exp)
     //   {
            //Education.BusinessLogic.ErrorHandler.ConvertErrorToPersian(exp.Message);
          //  return -1;
     //   }
      //  finally
      //  {
            CloseConnection();
      // }
    }
    //Execute Non Query
    public object ExecuteScalar()
    {
        try
        {
            OpenConnection();
            return Command.ExecuteScalar ();
             
        }
        catch (Exception exp)
        {
            //Education.BusinessLogic.ErrorHandler.ConvertErrorToPersian(exp.Message);
            return -1;
        }
        finally
        {
            CloseConnection();
        }
    }

    public DataTable FillDataAdapter()
    {
        System.Data.DataTable dt = new DataTable();
        try
        {
            DataAdapter.Fill(dt);
            return dt;
        }
        catch (Exception exp)
        {
            return null;
        }
    }

    }
}
