using System;
using System.Data;
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;
using RMG;
///

/// Summary description for cls_connection
///

///
namespace connection_layer
{
public class cls_connection
{
SqlConnection con = new SqlConnection();
SqlDataAdapter ad=null;
SqlCommand cmd=null ;
int result = 0;

public cls_connection()
{
try
{

con.ConnectionString = ConfigurationManager.ConnectionStrings["Con_Str"].ConnectionString;
}
catch (Exception ex)
{
RMG.Functions.MsgBox(ex.Message);
}

// TODO: Add constructor logic here

}

// Function for open & close the connection
#region connection_check

public void open_connection()
{
try
{
if (con.State == ConnectionState.Closed)
con.Open();
}
catch(Exception ex)
{
RMG.Functions.MsgBox(ex.Message);
}
}

public void close_connection()
{
try
{
if (con.State == ConnectionState.Open)
con.Close();
}

catch(Exception ex)
{
RMG.Functions.MsgBox(ex.Message);
}
}
#endregion

// Function for fetch the data
#region data_functions

public DataSet select_data_ds(string str)
{

open_connection();
DataSet ds = new DataSet();
ad = new SqlDataAdapter(str, con);
ad.Fill(ds);
close_connection();
return ds;

}


public SqlDataReader select_data_dr(string str)
{

open_connection();
cmd = new SqlCommand(str, con);
SqlDataReader dtr = cmd.ExecuteReader();
// close_connection();
return dtr;

}

public int select_data_scalar_int(string str)
{

open_connection();
cmd = new SqlCommand(str, con);

if (cmd.ExecuteScalar() != DBNull.Value)
result = Convert.ToInt32(cmd.ExecuteScalar());
else
result = 0;

close_connection();
return result;

}

public double select_data_scalar_double(string str)
{
double result1;
open_connection();
cmd = new SqlCommand(str, con);
if (cmd.ExecuteScalar() != DBNull.Value)
result1 = Convert.ToDouble(cmd.ExecuteScalar());
else
result1 = 0;

close_connection();
return result1;

}

public long select_data_scalar_long(string str)
{
long result1;
open_connection();
cmd = new SqlCommand(str, con);
if (cmd.ExecuteScalar() != DBNull.Value)
result1 = Convert.ToInt64(cmd.ExecuteScalar());
else
result1 = 0;

close_connection();
return result1;

}

public string select_data_scalar_string(string str)
{
string result1;
open_connection();
cmd = new SqlCommand(str, con);
if (cmd.ExecuteScalar() != DBNull.Value)
result1 =Convert.ToString(cmd.ExecuteScalar());
else
result1 ="";

close_connection();
return result1;

}

public int insert_data(string str)
{

open_connection();
cmd = new SqlCommand(str, con);
int x= cmd.ExecuteNonQuery();
close_connection();
return x;
}

public int delete_data(string str)
{
open_connection();
cmd = new SqlCommand(str, con);
int x = cmd.ExecuteNonQuery();
close_connection();
return x;

}

public int update_data(string str)
{

open_connection();
cmd = new SqlCommand(str, con);
int x = cmd.ExecuteNonQuery();
close_connection();
return x;

}


public int insert_with_SP(string sp_name, SqlParameter[] param)
{

open_connection();
cmd = new SqlCommand(sp_name, con);
cmd.CommandType = CommandType.StoredProcedure;
if (param != null)
{
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
}

int x = cmd.ExecuteNonQuery();
close_connection();
return x;
}

//Insert with SP and return values

public DataSet insert_with_SP_ds(string sp_name, SqlParameter[] param)
{

open_connection();
cmd = new SqlCommand(sp_name, con);
cmd.CommandType = CommandType.StoredProcedure;
if (param != null)
{
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
}

DataSet ds = new DataSet();
ad = new SqlDataAdapter();

ad.SelectCommand = cmd;
ad.Fill(ds);
close_connection();
return ds;
}

public int update_with_sp(string sp_name, SqlParameter[] param)
{

open_connection();
cmd = new SqlCommand(sp_name, con);
cmd.CommandType = CommandType.StoredProcedure;
if (param != null)
{
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
}
result = cmd.ExecuteNonQuery();
close_connection();
return result ;

}




public DataSet select_with_SP(string sp_name, SqlParameter[] param)
{
open_connection();

cmd = new SqlCommand();
cmd.CommandText = sp_name;
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
if (param != null )
{
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
}

ad = new SqlDataAdapter();
ad.SelectCommand = cmd;

DataSet dsp = new DataSet();
ad.Fill(dsp);

close_connection();
return dsp;

}


public DataTable select_with_DataTable(string sp_name, SqlParameter[] param)
{
open_connection();

cmd = new SqlCommand();
cmd.CommandText = sp_name;
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
if (param != null)
{
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
}

ad = new SqlDataAdapter();
ad.SelectCommand = cmd;

DataTable dsp = new DataTable();
ad.Fill(dsp);

close_connection();
return dsp;

}
#endregion
}
}