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


public class SqlHelp
{
    public static string mainConnectionString;
    public static SqlConnection cn;
    static SqlHelp()
    {
        mainConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
        cn = new SqlConnection(mainConnectionString);
    }
    
    private void OpenConnection()
    {
        if (cn.State != ConnectionState.Open)
        {
            cn.Open();
        }
    }
    private void CloseConnection()
    {
        if (cn.State == ConnectionState.Open)
        {
            cn.Close();
        }
    }
    /*Get properties  of a class*/
    public static string GetParameterName<T>(T item)
    {
        if (item == null)
            return string.Empty;

        return typeof(T).GetProperties()[0].Name;
    }
    /*create Parameters of SqlCommand from List given in "Paramerts"*/
    private void CreateParameters(ref SqlCommand cmd, params string[] Parameters)
    {
        if (Parameters.Length > 0)
        {
            foreach (var item in Parameters)
            {
                string param = GetParameterName(item);
                cmd.Parameters.AddWithValue("@" + param, item);
            }
        }

    }
    /*create Parameters of SqlCommand from class name*/
    private void CreateParameters<T>(object K, ref SqlCommand cmd)
    {
        foreach (var prop in typeof(T).GetProperties())
        {
            string VariableName = prop.Name;
            var value = Convert.ToString(K.GetType().GetProperty(VariableName).GetValue(K, null));
            if (value != null && value != "")
            {
                cmd.Parameters.AddWithValue("@" + VariableName, value);
            }
        }
    }
    /*create Insert Query in table from List of "Parameters"*/
    private string InsertQuery(string TableName, params string[] Parameters)
    {
        string query = "";
        if (Parameters.Length > 0)
        {
            StringBuilder parameter = new StringBuilder();
            StringBuilder values = new StringBuilder();
            for (int i = 0; i < Parameters.Length; i = i + 2)
            {
                string param = Parameters[i].ToString();
                parameter.Append(param + ",");
                values.Append("@" + Parameters[i + 1].ToString() + ",");
            }
            string paraName = parameter.ToString();
            string paravalue = values.ToString();
            query = string.Format("insert into {0} ({1}) values ({2})", TableName, paraName.Substring(1, paraName.Length - 1), paravalue.Substring(1, paravalue.Length - 1));
        }
        return query;
    }
    /*create Insert Query in table from Class*/
    private string InsertQuery<T>(object K, string TableName)
    {
        string query = "";

        StringBuilder parameter = new StringBuilder();
        StringBuilder values = new StringBuilder();
        foreach (var prop in typeof(T).GetProperties())
        {
            string VariableName = prop.Name;
            var value = Convert.ToString(K.GetType().GetProperty(VariableName).GetValue(K, null));
            if (value != null && value != "")
            {
                parameter.Append(VariableName + ",");
                values.Append("@" + VariableName.ToString() + ",");
            }
        }

        string paraName = parameter.ToString();
        string paravalue = values.ToString();
        query = string.Format("insert into {0} ({1}) values ({2})", TableName, paraName.Substring(1, paraName.Length - 1), paravalue.Substring(1, paravalue.Length - 1));

        return query;
    }
    /*Create Update query for a table from List of "Parametrs". provide "ColumnName and ColumnValue for where condition "*/
    private string UpdateQuery(string TableName, string ColumnName, string ColumnValue, params string[] Parameters)
    {
        string query = "";
        if (Parameters.Length > 0)
        {
            StringBuilder parameter = new StringBuilder();
            for (int i = 0; i < Parameters.Length; i = i + 2)
            {
                string param = Parameters[i].ToString();
                parameter.Append(Parameters[i + 1].ToString() + "= @" + param + ",");
            }
            string paraName = parameter.ToString();
            query = string.Format("update {0} set {1} where {2}={3}", TableName, paraName.Substring(1, paraName.Length - 1), ColumnName, ColumnValue);
        }
        return query;
    }
    /*Create Update query for a table from class. provide "ColumnName and ColumnValue for where condition "*/
    private string UpdateQuery<T>(object K, string TableName, string ColumnName, string ColumnValue)
    {
        string query = "";

        StringBuilder parameter = new StringBuilder();
        foreach (var prop in typeof(T).GetProperties())
        {
            string VariableName = prop.Name;
            var value = Convert.ToString(K.GetType().GetProperty(VariableName).GetValue(K, null));
            if (value != null && value != "")
            {
                parameter.Append(VariableName + ",");
                parameter.Append(VariableName + "= @" + VariableName + ","); ;
            }
        }
        string paraName = parameter.ToString();
        query = string.Format("update {0} set {1} where {2}={3}", TableName, paraName.Substring(1, paraName.Length - 1), ColumnName, ColumnValue);

        return query;
    }
    /*Insert Data in table. provide Table Name and Parameters. Parameter name should be same as on table column name*/
    public void InsertData(string TableName, params string[] Parameters)
    {

        SqlCommand cmd = new SqlCommand(InsertQuery(TableName, Parameters), cn);
        CreateParameters(ref cmd, Parameters);
        try
        {
            OpenConnection();
            int i = cmd.ExecuteNonQuery();
            CloseConnection();
        }
        catch
        {

        }
    }
    /*Insert Data in table. provide Table Name and Class name. Parameter name should be same on Class as on table column name*/
    public void InsertData<T>(object K, string TableName)
    {
        SqlCommand cmd = new SqlCommand(InsertQuery<T>(K, TableName), cn);
        CreateParameters<T>(K, ref cmd);
        try
        {
            OpenConnection();
            int i = cmd.ExecuteNonQuery();
            CloseConnection();
        }
        catch
        {

        }
    }
    /*Update Data in table. provide Table Name and Parameters. provide "ColumnName and ColumnValue for where condition.
     * Parameter name should be same as on table column name*/
    public void UpdateData(string TableName, string ColumnName, string ColumnValue, params string[] Parameters)
    {

        SqlCommand cmd = new SqlCommand(UpdateQuery(TableName, ColumnName, ColumnValue, Parameters), cn);
        CreateParameters(ref cmd, Parameters);
        try
        {
            OpenConnection();
            int i = cmd.ExecuteNonQuery();
            CloseConnection();
        }
        catch (Exception ex)
        {

        }
    }
    public void UpdateData<T>(object K, string TableName, string ColumnName, string ColumnValue)
    {
        SqlCommand cmd = new SqlCommand(UpdateQuery<T>(K, TableName,ColumnName,ColumnValue), cn);
        CreateParameters<T>(K, ref cmd);
        try
        {
            OpenConnection();
            int i = cmd.ExecuteNonQuery();
            CloseConnection();
        }
        catch
        {

        }
    }
}
