using System;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls; 

/// <summary>
/// Summary description for ConnectionDB
/// </summary>
public class ConnectionDB
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnectionstring"].ConnectionString);
    public void ConnectinOpen()
    {
         if(con.State!=ConnectionState.Open)
              con.Open();
    }
    public bool checkUsernameExist(string usernamid)
    {
        string sql = string.Format("select count(*) from MEMBERS where Uname='{0}'",usernamid);
        ConnectinOpen();
        SqlCommand cmd = new  SqlCommand();
        
        cmd.Connection = con;
        cmd.CommandText = sql;
        object obj= cmd.ExecuteScalar();
        if (int.Parse(obj.ToString())!= 0)
        {
            ConnectinClose();
            return false;
        }
        else
            ConnectinClose();
            return true;

    }
    public bool Cahngepassword( string old,string newpwd,string mid)
    {
        string sql = string.Format("update MEMBERS set Pword='{0}' where MemberId='{1}' and Pword='{2}'", newpwd, mid, old);
        SqlCommand cmd = new  SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = sql;
        ConnectinOpen();
        if (cmd.ExecuteNonQuery() > 0)
        {
            ConnectinClose();
            return true;
        }
        else
        {
            ConnectinClose();
            return false;
        }
    }

    public void ConnectinClose()
    {
        if (con.State != ConnectionState.Closed) 
            con.Close();
    }
    public string GetLegId(string id)
    {
        if (con.State != ConnectionState.Open)
            con.Open();
        if (id.Substring(0, 1) == "B")
        {
            id = id.Substring(4);
        }
         SqlCommand cmd = new  SqlCommand("select LegId from MEMBERS where MemberId='" + id+"'", con);
         SqlDataReader reader = cmd.ExecuteReader();
        string temp = "";
        if (reader.Read())
        {
            temp = reader["LegId"].ToString();
        }
        reader.Close();
        if (con.State != ConnectionState.Closed)
            con.Close();
        return temp;
    }
    public bool Execute(string sql)
    {
        ConnectinOpen();
         SqlCommand cmd = new  SqlCommand(sql, con);
        if (cmd.ExecuteNonQuery() > 0)
        {
            ConnectinClose();
            return true;
        }
        else
        {
            ConnectinClose();
            return false;
        }
       
    }
    public bool ExecuteBatch(string sql)
    {
        SqlCommand cmd = new SqlCommand(sql, con);
        if (cmd.ExecuteNonQuery() > 0)
        {
            return true;
        }
        else
        {
            return false;
        }

    }
    public DataTable GetRecords(string sql)
    {
         SqlCommand cmd = new  SqlCommand(sql, con);
         SqlDataAdapter adap = new  SqlDataAdapter(sql, con);
        DataSet ds = new DataSet();
        adap.Fill(ds, "tab");
        return ds.Tables[0];
    }
    public DataSet GetDataSet(string strSql)
    {
        try
        {
            SqlDataAdapter adp = new SqlDataAdapter(strSql, con);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            return null;
        }
    }
    public string  Authenticateuser(string username, string password)
    {

        string sql = "select MemberId from MEMBERS where Uname='" + username + "' AND Pword='" + password+"'";
         SqlCommand cmd = new  SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = sql;
        ConnectinOpen();
        try
        {
            object obj = cmd.ExecuteScalar();

            if (obj == DBNull.Value)
            {
                ConnectinClose();
                return "0";
            }
            if (obj == null)
            {
                ConnectinClose();
                return "0";

            }
            ConnectinClose();
            return obj.ToString();

           

        }
        catch (Exception)
        {
            ConnectinClose();
            return "0";
        }


    }

    public string GetValue(string sql)
    {
         SqlCommand cmd = new  SqlCommand();
        cmd.CommandText = sql;
        cmd.Connection = con;
        ConnectinOpen();
        object obj = cmd.ExecuteScalar();
        ConnectinClose();
        if (obj == null)
            return "0";
        if (obj == DBNull.Value)
        {
            return "0";
        }
        return obj.ToString();
    }
    public object ExecuteScalar(string sql)
    {
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = sql;
        cmd.Connection = con;
        ConnectinOpen();
        object obj = cmd.ExecuteScalar();
        ConnectinClose();

        return obj;
    }
    /// <summary>
    /// This function return weekno,dayno,weeday(date) or 0 if calculation not now
    /// </summary>

    public bool SetAdminLogin(string uname, string pwd)
    {

         SqlCommand cmd = new  SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = string.Format("insert into Admin values('{0}','{1}')",uname,pwd);
        ConnectinOpen();
        int i = cmd.ExecuteNonQuery();
        ConnectinClose();
        if (i > 0)
        {
            return true;
        }
        return false;
    }
    public string Isadminlogin(string username, string pword)
    {
        string sql = "SELECT [Admin].[AdminUsername] FROM [Admin] WHERE [Admin].[AdminUsername]='" + username + "' AND [Admin].[AdminPassword]='" + pword + "'";
         SqlCommand cmd = new  SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = sql;
        ConnectinOpen();
         SqlDataReader reder = cmd.ExecuteReader();
        string str = "";
        if (reder.Read())
        {
            str = reder["AdminUsername"].ToString();
        }
        ConnectinClose();
        return str;
    }

}