using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;

namespace DataBaseService
{
    public partial class DataBaseService : IDataBaseService
    {
        public static string ConnectionString = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["con"]);

        static SqlConnection GlobalConn()
        {
            SqlConnection sql = new SqlConnection(ConnectionString);
            return sql;
        }

        public  DataSet GetDataSet(string uspName, string[] paramArray, object[] valueArray)
        {
            SqlConnection con = null;
            DataSet dataSet;
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand;
            int i;
            dataSet = new DataSet();
            try
            {
                sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlDataAdapter.SelectCommand.Connection = con;

                sqlCommand = sqlDataAdapter.SelectCommand;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                i = 0;
                while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
                {
                    if (paramArray[i] != null)
                    {
                        sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
                    }
                    i++;
                }
                sqlDataAdapter.Fill(dataSet);

            }
            catch (Exception Ex)
            {
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return dataSet;
        }

        public DataSet GetDataSetWithOutParam(string spName)
        {
            SqlConnection con = null;
            DataSet dataSet;
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand;
            dataSet = new DataSet();
            try
            {
                sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlDataAdapter.SelectCommand.Connection = con;

                sqlCommand = sqlDataAdapter.SelectCommand;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = spName;
                sqlDataAdapter.Fill(dataSet);
            }
            catch (Exception Ex)
            {
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return dataSet;
        }

        public DataSet GetDataSetReturnWithParam(string uspName, string[] paramArray, object[] valueArray, string OutParamName, out Int32 OutParam)
        {
            SqlConnection con = null;
            DataSet dataSet;
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand;
            int i;
            dataSet = new DataSet();
            OutParam = 0;
            try
            {
                sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = new SqlCommand();
               
                con = GlobalConn();
                con.Open();

                sqlDataAdapter.SelectCommand.Connection = con;

                sqlCommand = sqlDataAdapter.SelectCommand;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                i = 0;
                while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
                {
                    if (paramArray[i] != null)
                    {
                        sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
                    }
                    i++;
                }
                //Set out parameter
                sqlCommand.Parameters.AddWithValue(OutParamName, OutParam);
                sqlCommand.Parameters[OutParamName].Direction = ParameterDirection.Output;
                sqlCommand.Parameters[OutParamName].DbType = System.Data.DbType.Int32;
                sqlDataAdapter.Fill(dataSet);
                if (sqlCommand.Parameters[OutParamName] != null)
                {
                    OutParam = Convert.ToInt32(sqlCommand.Parameters[OutParamName].Value); //sqlCommand.Parameters.Add(OutParamName, SqlDbType.Int)
                }
            }
            catch (Exception Ex)
            {
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return dataSet;
        }

        public DataSet GetDataSetWithReturnValue(string uspName, string[] paramArray, object[] valueArray, out Int32 OutParam)
        {
            SqlConnection con = null;
            DataSet dataSet;
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand;
            int i;
            dataSet = new DataSet();
            OutParam = 0;
            string OutParamName = "@ReturnValue";

            try
            {
                sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlDataAdapter.SelectCommand.Connection = con;

                sqlCommand = sqlDataAdapter.SelectCommand;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                i = 0;
                while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
                {
                    if (paramArray[i] != null)
                    {
                        sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
                    }
                    i++;
                }
                //Set out parameter
                sqlCommand.Parameters.AddWithValue(OutParamName, OutParam);
                sqlCommand.Parameters[OutParamName].Direction = ParameterDirection.ReturnValue;
                sqlCommand.Parameters[OutParamName].SqlDbType = SqlDbType.Int;
                sqlDataAdapter.Fill(dataSet);
                if (sqlCommand.Parameters[OutParamName] != null)
                {
                    OutParam = Convert.ToInt32(sqlCommand.Parameters[OutParamName].Value); //sqlCommand.Parameters.Add(OutParamName, SqlDbType.Int)
                }
            }
            catch (Exception Ex)
            {
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return dataSet;
        }

        public  DataTable GetDataTable(string uspName, string strParam, string strMain)
        {
            SqlConnection con = null;
            DataTable dataTable;
            SqlDataAdapter sqlDataAdapter;
            dataTable = new DataTable();
            try
            {
                sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlDataAdapter.SelectCommand.Connection = con;
                SqlCommand sqlCommand = sqlDataAdapter.SelectCommand;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                sqlCommand.Parameters.Add(strParam, SqlDbType.VarChar).Value = strMain;
                sqlDataAdapter.Fill(dataTable);
            }
            catch (Exception Ex)
            {
                //GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem in Getting Data.", Ex.Message);
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return dataTable;
        }

        public  void ExecuteSPReturn(string uspName)
        {
            SqlConnection con = null;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlCommand.Connection = con;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                sqlCommand.ExecuteNonQuery();
               
            }
            catch (Exception Ex)
            {
                //GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution.", Ex.Message);
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
        }

        public  object ExecuteSPReturnWithParam(string uspName, string[] ParamArr, object[] ValueArr, object OutParam)
        {
            SqlConnection con = null;
            string str;
            SqlCommand sqlCommand;
            int i;
            try
            {
                str = "";
                sqlCommand = new SqlCommand();
                con = GlobalConn();
               
                SqlConnection sqlCon = con;
                sqlCon.Open();
                sqlCommand.Connection = sqlCon;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                if ((ParamArr.Length.Equals(0)) || (ValueArr.Length.Equals(0)))
                {
                    return "";
                }
                if (!ParamArr.Length.Equals(ValueArr.Length))
                {
                    return "";
                }
                i = 0;
                while (i < ParamArr.Length)
                {
                    if (ParamArr[i] != null)
                    {
                        sqlCommand.Parameters.AddWithValue(ParamArr[i], ValueArr[i]);
                    }
                    str = string.Concat(str, ",", ValueArr[i].ToString());
                    i++;
                }
                sqlCommand.Parameters["@OutParam"].Direction = ParameterDirection.Output;
                sqlCommand.Parameters["@OutParam"].DbType = System.Data.DbType.Int64;
                sqlCommand.ExecuteNonQuery();
                OutParam = sqlCommand.Parameters.Add("@OutParam", SqlDbType.BigInt).Value;
                sqlCon.Close();
               
            }
            catch (Exception Ex)
            {
                //GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution.", Ex.Message);
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return OutParam;
        }

        public  long SetAddDataXML(string uspName, DataSet ds, long ID )
        {
            SqlConnection con = null;
            object obj;
            string str;
            SqlDataAdapter sqlDataAdapter;
            obj = new object();
            str = "";
            obj = 0;
            try
            {
                sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlDataAdapter.SelectCommand.Connection = con;
               
                SqlCommand sqlCommand = sqlDataAdapter.SelectCommand;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                MemoryStream memoryStream = new MemoryStream();
                ds.WriteXml(memoryStream);
                memoryStream.Position = 0;
                StreamReader streamReader = new StreamReader(memoryStream);
                str = streamReader.ReadToEnd();
                sqlCommand.Parameters.AddWithValue("@AddData", str);
                //sqlCommand.Parameters.AddWithValue("@MasterType", mstType);
                sqlCommand.Parameters["@Outparam"].Direction = ParameterDirection.Output;
                sqlCommand.ExecuteNonQuery();
                obj = sqlCommand.Parameters.Add("@Outparam", SqlDbType.BigInt).Value;

              


            }
            catch (Exception Ex)
            {
                //GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution. Unable to save data.", Ex.Message);
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return Convert.ToInt64(obj);
        }

        public  void ExecuteSQLQuery(string sStr)
        {
            SqlConnection con = null;
            try
            {
               
                SqlCommand sqlCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlCommand.Connection = con;
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = sStr;
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
                //GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution. Unable to do the operation.", Ex.Message);
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
        }


        public int ExecuteNonQueryWithReturn(string uspName, string[] paramArray, object[] valueArray)
        {
            SqlConnection sqlCon = null;

            try
            {

                int result = 0;
                SqlCommand sqlCommand = new SqlCommand();
                sqlCon = GlobalConn();
                sqlCon.Open();

                sqlCommand.Connection = sqlCon;

                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                int i = 0;
                while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
                {
                    if (paramArray[i] != null)
                    {
                        sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
                    }
                    i++;
                }
                SqlParameter myParm = sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
                sqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;
                sqlCommand.ExecuteNonQuery();
                result = (Int32)sqlCommand.Parameters["@ReturnValue"].Value;


                return result;

            }
            catch (Exception Ex)
            {

                throw new Exception(Ex.Message, Ex);

            }
            finally
            {
                sqlCon.Close();
            }
          
        }

        public int ExecuteNonQueryWithReturn(string uspName, string param, System.Data.SqlTypes.SqlXml value)
        {
            SqlConnection sqlCon = null;

            try
            {

                int result = 0;
                SqlCommand sqlCommand = new SqlCommand();
                sqlCon = GlobalConn();
                sqlCon.Open();

                sqlCommand.Connection = sqlCon;

                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
               
                sqlCommand.Parameters.Add(param, SqlDbType.Xml);
                sqlCommand.Parameters[param].Value = value.Value.Replace(" xmlns=\"http://tempuri.org/\"", "");
               
                SqlParameter myParm = sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
                sqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;

                sqlCommand.ExecuteNonQuery();
                result = (Int32)sqlCommand.Parameters["@ReturnValue"].Value;


                return result;

            }
            catch (Exception Ex)
            {

                throw new Exception(Ex.Message, Ex);

            }
            finally
            {
                sqlCon.Close();
            }

        }



        public int ExecuteNonQueryWithSaveXML(string uspName, string[] paramArray, object[] valueArray, string xmlparam, System.Data.SqlTypes.SqlXml value)
        {
            SqlConnection sqlCon = null;

            try
            {

                int result = 0;
                SqlCommand sqlCommand = new SqlCommand();
                sqlCon = GlobalConn();
                sqlCon.Open();

                sqlCommand.Connection = sqlCon;

                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                int i = 0;
                while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
                {
                    if (paramArray[i] != null)
                    {
                        sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
                    }
                    i++;
                }
              
                sqlCommand.Parameters.Add(xmlparam, SqlDbType.Xml);
                sqlCommand.Parameters[xmlparam].Value = value.Value.Replace(" xmlns=\"http://tempuri.org/\"", "");

                SqlParameter myParm = sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
                sqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;

                sqlCommand.ExecuteNonQuery();
                result = (Int32)sqlCommand.Parameters["@ReturnValue"].Value;


                return result;

            }
            catch (Exception Ex)
            {

                throw new Exception(Ex.Message, Ex);

            }
            finally
            {
                sqlCon.Close();
            }

        }




        public int ExecuteSPWithReturn(string uspName)
        {
            SqlConnection con = null;
            int result = 0;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlCommand.Connection = con;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
               
                sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
                sqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;

                sqlCommand.ExecuteNonQuery();
                result = (Int32)sqlCommand.Parameters["@ReturnValue"].Value;
            }
            catch (Exception Ex)
            {
                //GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution.", Ex.Message);
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        public int ExecuteNonQuery(string uspName, string[] paramArray, object[] valueArray)
        {
            SqlConnection sqlCon = null;
            try
            {
                int result=0;
                SqlCommand sqlCommand = new SqlCommand();
                 sqlCon= GlobalConn();
                sqlCon.Open();
                sqlCommand.Connection = sqlCon;
                   
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                int i = 0;
                while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
                {
                    if (paramArray[i] != null)
                    {
                        sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
                    }
                    i++;
                }
                result = sqlCommand.ExecuteNonQuery();
                sqlCon.Close();
              return result;
             
            }
            catch (Exception Ex)
            {
               
                throw new Exception(Ex.Message, Ex);
               
            }
            finally
            {
                sqlCon.Close();
            }
          
        }

        
    }
}