<appSettings>
<add key="SqlConnection1.ConnectionString" value="data source= 10.4.3.58; database=ITPolicy; user id=sa;password=admin"/>
</appSettings>

namespace ITPolicy 
{
   public class Common
    {
        public Common()
        {
        }

        public int getInt(string sql)
        {
            SqlConnection con = OpenConnectionObject();
            con.Open();
            try
            {
                SqlCommand objcmd = new SqlCommand(sql, con);
                return (Convert.ToInt32(objcmd.ExecuteScalar()));
            }
            catch (Exception e)
            {
      HttpContext.Current.Response.Write("error" + e.Message + "during" + sql);
                return 0;
            }
            finally
            {
                con.Close();
            }
        }
  public DataSet ExecuteQuery(DataSet ds, string strSQL, string strDataTableName)
        {
            SqlConnection con = OpenConnectionObject();
            con.Open();
            try
            {
                SqlDataAdapter objDataAdapter = new SqlDataAdapter();
                SqlCommand objCmd = new SqlCommand(strSQL, con);
                objDataAdapter.SelectCommand = objCmd;
                if (ds.Tables.Contains(strDataTableName))
                    ds.Tables.Remove(strDataTableName);
                objDataAdapter.Fill(ds, strDataTableName);
                return ds;
            }
            catch (Exception e)
            {
 HttpContext.Current.Response.Write("" + e.Message + "");
                return null;
            }
            finally
            {
                con.Close();
            }
        }
        public SqlConnection OpenConnectionObject()
        {
            string strConn = System.Configuration.ConfigurationManager.AppSettings["SqlConnection1.ConnectionString"];
            if (strConn.ToString() == "")
            {
                return null;
            }
            SqlConnection con = new SqlConnection(strConn);
            return con;
        }
        public DataSet objDataSet = new DataSet();

        public int CloseConnectionObject(SqlConnection connobj)
        {
            try
            {
                connobj.Close();
                return 1;
            }
            catch (Exception e)
            {
  HttpContext.Current.Response.Write("" + e.Message +"");
                return 0;
            }
        }
        public DataSet ExecuteQuery(string strSQL, string strDataTableName)
        {
            SqlConnection con = OpenConnectionObject();
            con.Open();
            try
            {
                DataSet ds = null;
                ds = new DataSet();
                SqlCommand objcmd = new SqlCommand(strSQL, con);
                SqlDataAdapter objDataAdapter = new SqlDataAdapter(strSQL, con);
                objDataAdapter.Fill(ds, strDataTableName);
                return ds;
            }
            catch (Exception e)
            {
 HttpContext.Current.Response.Write("" + e.Message + "");
                return null;
            }
            finally
            {
                con.Close();
            }
        }
        public SqlDataReader ExcuteReader(string SQL)
        {
            SqlConnection con = OpenConnectionObject();
            con.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(SQL, con);
                SqlDataReader dr = cmd.ExecuteReader();
                return dr;
            }
            catch (Exception e)
            {
 HttpContext.Current.Response.Write("" + e.Message + "");
                return null;
            }
            /*finally
            {
                con.Close();
            }*/
        }
        public DataSet ExecuteQuery(string strSQL)
        {
            SqlConnection con = OpenConnectionObject();
            con.Open();
            try
            {
                DataSet ds = new DataSet();
                SqlCommand objcmd = new SqlCommand(strSQL, con);
                SqlDataAdapter objDataAdapter = new SqlDataAdapter();
                objDataAdapter.SelectCommand = objcmd;
                objDataAdapter.Fill(ds);
                return ds;
            }
            catch (Exception e)
            {
 HttpContext.Current.Response.Write("" + e.Message + "");
                return null;
            }
            finally
            {
                con.Close();
            }
        }       
        public string FormatDate(string strInputDate)
        {
            string strDate;
            strDate = strInputDate.Substring(0, 10);
            CultureInfo objCulture = new CultureInfo("en-GB");
            DateTime objTime = (DateTime)DateTime.Parse(strDate, objCulture);
            return objTime.ToString("dd-MMM-yyyy");
        }
    }
}