using System;
using System.Data.SqlClient;
using System.Data;
namespace DataAccess
{
public class DBConnection
{
string sqlConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
SqlDataReader rdr = null;
SqlConnection con = null;
SqlCommand cmd = null;
public DataTable ReadData(string qry)
{
try
{
DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = qry;
cmd.CommandType = CommandType.Text;
con.Open();
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(rdr);
return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
if(rdr != null)
rdr.Close();
//con.Close();
}
}
public bool CallSP(string spName, SqlParameter[] param)
{
con = new SqlConnection(sqlConnectionString);
bool toReturn;
try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
con.Open();
cmd.ExecuteNonQuery();
toReturn = true;
}
catch (Exception ex)
{
toReturn = false;
}
finally
{
con.Close();
}
return toReturn;
}
public DataTable CallSP(string spName, SqlParameter[] param, bool isReturn)
{
DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);
try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
con.Open();
rdr = cmd.ExecuteReader();
dt.Load(rdr);
return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
con.Close();
}
}
public DataTable CallSP(string spName, bool isReturn)
{
DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);
try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
rdr = cmd.ExecuteReader();
dt.Load(rdr);
return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
con.Close();
}
}
}
}