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

//using System.Linq;
using System.Data.SqlClient;
using System.IO;

namespace DAL
{
    class SqlDBHelper
    {
        
        //const string CONNECTION_STRING = "Provider=SQLOLEDB.1;Password=sa123;Persist Security Info=True;User ID=sa;Initial Catalog=Test_Inventory;Data Source=192.168.21.33";
        public static string CONNECTION_STRING = Convert.ToString(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
        //const string CONNECTION_STRING = "Provider=SQLOLEDB.1;Password=Global12;Persist Security Info=True;User ID=sa;Initial Catalog=QualityAnalysis;Data Source=SANKAR01L\\MSSQL";
        
        //internal static DataTable ExecuteSelectCommand(string CommandName, CommandType cmdType)
        public static DataTable ExecuteSelectCommand(string commandName, CommandType cmdType)
        {
            if (commandName == null) throw new ArgumentNullException("commandName");
            DataTable table = null;
            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = cmdType;
                    cmd.CommandText = commandName;
                    try
                    {
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }

                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            table = new DataTable();
                            da.Fill(table);
                        }
                    }
                    catch
                    {
                        //throw;
                    }
                }
            }

            return table;
        }
        // This function will be used to execute R(CRUD) operation of parameterized commands
        internal static DataTable ExecuteParamerizedSelectCommand(string commandName, CommandType cmdType, SqlParameter[] param)
        {
            DataTable table = new DataTable();

            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = cmdType;
                    cmd.CommandText = commandName;
                    cmd.Parameters.AddRange(param);

                    try
                    {
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }

                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(table);
                        }
                    }
                    catch
                    {
                        throw;
                    }
                }
            }

            return table;
        }
        internal static bool ExecuteNonQuery(string commandName, CommandType cmdType, SqlParameter[] pars)
        {
            int result = 0;

            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = cmdType;
                    cmd.CommandText = commandName;
                    cmd.Parameters.AddRange(pars);

                    try
                    {
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }

                        result = cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        throw;
                    }
                }
            }

            return (result > 0);
        }
        internal static bool BackUpdata(string commandName, CommandType cmdType)
        {
            int result = 0;

            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                string backupDIR = "C:\\Backup";
                if (!System.IO.Directory.Exists(backupDIR))
                {
                    System.IO.Directory.CreateDirectory(backupDIR);
                }
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand("backup database AIPMS to disk='" + backupDIR + "\\" + DateTime.Now.ToString("ddMMyyyy_HHmmss") + ".Bak'", con);
                    cmd.ExecuteNonQuery();
                    con.Close();
                    result = 1;
                }
                catch (Exception ex)
                {
                    result = 0;
                }
            }

            return (result > 0);
        }
        internal static string findUniqueTemporaryTableName()
        {
            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                con.Open();
                string name = "afpTempBackup";
                int counter = 0;
                string sql = "";
                SqlCommand _mycommand = new SqlCommand();
                _mycommand.Connection = con;
                while (true)
                {
                    ++counter;
                    sql = String.Format("SELECT OBJECT_ID('tempdb..##{0}') as xyz", name + counter.ToString());
                    _mycommand.CommandText = sql;
                    if (_mycommand.ExecuteScalar().ToString() == "")
                    {
                        return name + counter.ToString();
                        break;
                    }
                }
                con.Close();
                return name;
            }
        }
        internal static void BackUpdata1(string commandName, CommandType cmdType)
        {
            string _dbname = "AIPMS";
            string path = "C:\\Backup";
            if (!System.IO.Directory.Exists(path))
            {
                System.IO.Directory.CreateDirectory(path);
            }
            string AremoteTempPath = path;
            string AlocalPath = path;
            
            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                con.Open();
                try
                {
                    if (con == null)
                        return;
                    SqlCommand _command = new SqlCommand();
                    _command.Connection = con;
                    // nice filename on local side, so we know when backup was done
                    string fileName = _dbname + DateTime.Now.Year.ToString() + "-" +
                        DateTime.Now.Month.ToString() + "-" +
                        DateTime.Now.Day.ToString() + "-" +
                            DateTime.Now.Millisecond.ToString() + ".bak";
                    // we invoke this method to ensure we didnt mess up with other programs
                    string temporaryTableName = findUniqueTemporaryTableName();
                    string fileName1 = commandName + DateTime.Now.Year.ToString() + "-" +
                        DateTime.Now.Month.ToString() + "-" +
                        DateTime.Now.Day.ToString();
                    string _sql;

                    _sql = String.Format("BACKUP DATABASE {0} TO DISK = N'{1}\\{2}.bak' " +
                           "WITH FORMAT, COPY_ONLY, INIT, NAME = N'{0} - Full Database " +
                           "Backup', SKIP ", _dbname, AremoteTempPath, fileName1);
                    _command.CommandText = _sql;
                    _command.ExecuteNonQuery();
                    _sql = String.Format("IF OBJECT_ID('tempdb..##{0}') IS " +
                           "NOT NULL DROP TABLE ##{0}", temporaryTableName);
                    _command.CommandText = _sql;
                    _command.ExecuteNonQuery();
                    _sql = String.Format("CREATE TABLE ##{0} (bck VARBINARY(MAX))",
                                         temporaryTableName);
                    _command.CommandText = _sql;
                    _command.ExecuteNonQuery();
                    _sql = String.Format("INSERT INTO ##{0} SELECT bck.* FROM " +
                           "OPENROWSET(BULK '{1}\\{2}.bak',SINGLE_BLOB) bck",
                           temporaryTableName, AremoteTempPath, _dbname);
                    _command.CommandText = _sql;
                    _command.ExecuteNonQuery();
                    _sql = String.Format("SELECT bck FROM ##{0}", temporaryTableName);
                    SqlDataAdapter da = new SqlDataAdapter(_sql, con);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    DataRow dr = ds.Tables[0].Rows[0];
                    byte[] backupFromServer = new byte[0];
                    backupFromServer = (byte[])dr["bck"];
                    int aSize = new int();
                    aSize = backupFromServer.GetUpperBound(0) + 1;

                    FileStream fs = new FileStream(String.Format("{0}\\{1}",
                                    AlocalPath, fileName), FileMode.OpenOrCreate,
                                    FileAccess.Write);
                    fs.Write(backupFromServer, 0, aSize);
                    fs.Close();

                    _sql = String.Format("DROP TABLE ##{0}", temporaryTableName);
                    _command.CommandText = _sql;
                    _command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                   
                    throw;
                }
                con.Close();
            }
        }
        internal static Guid ExecuteNonQuerywithID(string commandName, CommandType cmdType, SqlParameter[] pars)
        {
            Guid result;

            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = cmdType;
                    cmd.CommandText = commandName;
                    cmd.Parameters.AddRange(pars);

                    try
                    {
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }

                        result = (Guid)cmd.ExecuteScalar();
                    }
                    catch
                    {
                        throw;
                    }
                }
            }

            return result ;
        }
        internal static string GetAutoValue(string Prefix, Guid User, string Suffix, string Digits,string AutoTemplate)
        {
            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                    SqlCommand cmd;
                    cmd = new SqlCommand("mstAutoGenerateNoGet", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Prefix", SqlDbType.VarChar, 50);
                    cmd.Parameters.Add("@Suffix", SqlDbType.VarChar, 50);
                    cmd.Parameters.Add("@Digits", SqlDbType.VarChar, 50);
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.UniqueIdentifier, 50);
                    cmd.Parameters.Add("@AutoTemplate", SqlDbType.VarChar, 50);
                    cmd.Parameters.Add("@ReturnID", SqlDbType.VarChar);
                    cmd.Parameters[0].Value = Prefix;
                    cmd.Parameters[1].Value = Suffix;
                    cmd.Parameters[2].Value = Digits;
                    cmd.Parameters[3].Value = User;
                    cmd.Parameters[4].Value = AutoTemplate;
                    cmd.Parameters[5].Direction = ParameterDirection.ReturnValue;
                    if (con.State == System.Data.ConnectionState.Closed)
                    { con.Open(); }
                    cmd.ExecuteNonQuery();

                    string Ret = Convert.ToString(cmd.Parameters[5].Value);
                    if (con.State == System.Data.ConnectionState.Open)
                    { con.Close(); }
                    return Ret;
            }
        }
        internal static string GetAutoValueYear(string Prefix, Guid User, string Suffix, string Digits, string AutoTemplate, string Yeartxt)
        {
            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                SqlCommand cmd;
                cmd = new SqlCommand("mstAutoGenerateNoGetYear", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Prefix", SqlDbType.VarChar, 50);
                cmd.Parameters.Add("@Suffix", SqlDbType.VarChar, 50);
                cmd.Parameters.Add("@Digits", SqlDbType.VarChar, 50);
                cmd.Parameters.Add("@CreatedBy", SqlDbType.UniqueIdentifier, 50);
                cmd.Parameters.Add("@AutoTemplate", SqlDbType.VarChar, 50);
                cmd.Parameters.Add("@Yeartxt", SqlDbType.VarChar, 50);
                cmd.Parameters.Add("@ReturnID", SqlDbType.VarChar);
                cmd.Parameters[0].Value = Prefix;
                cmd.Parameters[1].Value = Suffix;
                cmd.Parameters[2].Value = Digits;
                cmd.Parameters[3].Value = User;
                cmd.Parameters[4].Value = AutoTemplate;
                cmd.Parameters[5].Value = Yeartxt;
                cmd.Parameters[6].Direction = ParameterDirection.ReturnValue;
                if (con.State == System.Data.ConnectionState.Closed)
                { con.Open(); }
                cmd.ExecuteNonQuery();

                string Ret = Convert.ToString(cmd.Parameters[6].Value);
                if (con.State == System.Data.ConnectionState.Open)
                { con.Close(); }
                return Ret;
            }
        }
        internal static string ReturnString(string qry)
        {
            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                SqlCommand cmd;
                if (con.State == System.Data.ConnectionState.Closed)
                { con.Open(); }
                cmd = new SqlCommand(qry, con);
                object RetValue = cmd.ExecuteScalar();
                if (con.State == System.Data.ConnectionState.Open)
                { con.Close(); }
                return Convert.ToString(RetValue);
            }
        }
        internal static void InsertAutoValue(string Prefix, string Suffix, Guid User, string AutoNo, string AutoTemplate)
        {
            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                SqlCommand cmd;
                cmd = new SqlCommand("mstAutoGenerateNoInsert", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Prefix", SqlDbType.VarChar, 50);
                cmd.Parameters.Add("@Suffix", SqlDbType.VarChar, 50);
                cmd.Parameters.Add("@CreatedBy", SqlDbType.UniqueIdentifier, 50);
                cmd.Parameters.Add("@AutoNo", SqlDbType.VarChar, 20);
                cmd.Parameters.Add("@AutoTemplate", SqlDbType.VarChar, 20);

                cmd.Parameters[0].Value = Prefix;
                cmd.Parameters[1].Value = Suffix;
                cmd.Parameters[2].Value = User;
                cmd.Parameters[3].Value = AutoNo;
                cmd.Parameters[4].Value = AutoTemplate;

                if (con.State == System.Data.ConnectionState.Closed)
                { con.Open(); }
                cmd.ExecuteNonQuery();
                if (con.State == System.Data.ConnectionState.Open)
                { con.Close(); }
            }
        }
        internal static void InsertAutoValueYear(string Prefix, string Suffix, Guid User, string AutoNo, string AutoTemplate, string Yeartxt)
        {
            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                SqlCommand cmd;
                cmd = new SqlCommand("mstAutoGenerateNoYearInsert", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Prefix", SqlDbType.VarChar, 50);
                cmd.Parameters.Add("@Suffix", SqlDbType.VarChar, 50);
                cmd.Parameters.Add("@CreatedBy", SqlDbType.UniqueIdentifier, 50);
                cmd.Parameters.Add("@AutoNo", SqlDbType.VarChar, 20);
                cmd.Parameters.Add("@AutoTemplate", SqlDbType.VarChar, 20);
                cmd.Parameters.Add("@Yeartxt", SqlDbType.VarChar, 20);

                cmd.Parameters[0].Value = Prefix;
                cmd.Parameters[1].Value = Suffix;
                cmd.Parameters[2].Value = User;
                cmd.Parameters[3].Value = AutoNo;
                cmd.Parameters[4].Value = AutoTemplate;
                cmd.Parameters[5].Value = Yeartxt;

                if (con.State == System.Data.ConnectionState.Closed)
                { con.Open(); }
                cmd.ExecuteNonQuery();
                if (con.State == System.Data.ConnectionState.Open)
                { con.Close(); }
            }
        }
        internal static DataSet ReturnDataSet(string qry)
        {
            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                if (con.State == System.Data.ConnectionState.Closed)
                { con.Open(); }
                SqlDataAdapter da = new SqlDataAdapter(qry, con);
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (con.State == System.Data.ConnectionState.Open)
                { con.Close(); }
                return (ds);
            }
        }
        internal static void ExecuteQuery(string qry)
        {
            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                if (con.State == System.Data.ConnectionState.Closed)
                { con.Open(); }
                SqlCommand cmd = new SqlCommand(qry, con);
                cmd.ExecuteNonQuery();
                if (con.State == System.Data.ConnectionState.Open)
                { con.Close(); }
            }
        }
        
    }
}