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

namespace JTSSQLSERVER
{
    class CommonDataManager
    {
        SqlConnection _conn;
        SqlCommand _cmd;
        string _commandText;
        SqlDataAdapter _da;
        DataTable _dt;
        List<string> _commandTexts;

        public CommonDataManager()
        {
            Initialize(true);
        }

        public CommonDataManager(string query)
        {
            this._commandText = query;
            Initialize();
        }

        public int ParameterCount
        {
            get { return _cmd.Parameters.Count; }
        }

        public void AddWithValue(string name, object param)
        {
            if (_cmd != null)
            {
                _cmd.Parameters.AddWithValue(name, param);
            }
        }

        public void SetNullParameterValue(string name, SqlDbType tipe)
        {
            _cmd.Parameters[name].Value = DBNull.Value;
        }

        public void AddNullParameter(string name, SqlDbType tipe, int lenght)
        {
            _cmd.Parameters.Add(name, tipe, lenght);
        }

        public void ClearParameters()
        {
            if (_cmd.Parameters.Count > 0)
            {
                _cmd.Parameters.Clear();
            }
        }

        public DataTable GetDataTable()
        {
            _da.Fill(_dt);
            return _dt;
        }

        public int ExecuteNonQuery()
        {
            _conn.Open();
            int result = _cmd.ExecuteNonQuery();
            _conn.Close();
            return result;
        }

        public object ExecuteScalar()
        {
            _conn.Open();
            object result = _cmd.ExecuteScalar();
            _conn.Close();
            return result;
        }

        public void AddTransactionQuery(string transaction)
        {
            if (this._commandTexts != null)
            {
                this._commandTexts.Add(transaction);
            }
        }

        public void ClearTransactionQueries()
        {
            if (this._commandTexts.Count > 0)
            {
                this._commandTexts.Clear();
            }
        }


        public void Initialize()
        {
            this._conn = new SqlConnection(ConfigurationManager.ConnectionStrings["JTS"].ConnectionString);
            if (!string.IsNullOrEmpty(this._commandText))
            {
                this._cmd = new SqlCommand(this._commandText, this._conn);
                this._da = new SqlDataAdapter();
                this._dt = new DataTable();
                _da.SelectCommand = _cmd;
                this._commandTexts = new List<string>();
            }
        }

        public void Initialize(bool transaction)
        {
            if (transaction)
            {
                this._conn = new SqlConnection(ConfigurationManager.ConnectionStrings["JTS"].ConnectionString);

                this._cmd = new SqlCommand(this._commandText, this._conn);
                this._da = new SqlDataAdapter();
                this._dt = new DataTable();
                _da.SelectCommand = _cmd;
                this._commandTexts = new List<string>();
            }
        }

        public bool ExecuteTransaction()
        {
            using (_conn)
            {
                _conn.Open();
                SqlTransaction transaction = _conn.BeginTransaction();
                _cmd.Transaction = transaction;

                try
                {
                    foreach (string query in _commandTexts)
                    {
                        _cmd.CommandText = query;
                        _cmd.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch (Exception ex2)
                    {

                    }
                }
            }
            return false;
        }

        public void SetCommandText(string query)
        {
            this._commandText = query;
            _cmd.CommandText = this._commandText;
        }
    }
}