DBUtility




using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
namespace Gridview
{
public class Dbutility
{
public Dbutility()
{
//
// TODO: Add constructor logic here
//
}
public SqlConnection con;
int flag = 0;
string cnnString;
public int sqlconnection()
{
cnnString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnect"].ToString();
con = new SqlConnection(cnnString);
flag = 1;
return flag;
}
public void ConnectDb()
{
try
{
if (con.State != ConnectionState.Open)
con.Open();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void DisconnectDb()
{
try
{
con.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public DataTable getdatatable(SqlCommand com)
{
try
{
sqlconnection();
com.Connection = con;
com.CommandTimeout = 200000000;
SqlDataAdapter da = new SqlDataAdapter(com);
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();
return dt;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
con.Close();
}
}
public DataTable getdatatableWithTransaction(SqlCommand com, SqlConnection db, SqlTransaction tran)
{
try
{
sqlconnection();
com.Connection = db;
com.CommandTimeout = 200000000;
SqlDataAdapter da = new SqlDataAdapter(com);
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();
return dt;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
con.Close();
}
}
public int ex_nonquery(SqlCommand com)
{
try
{
sqlconnection();
com.Connection = con;
com.CommandTimeout = 200000000;
con.Open();
int result = com.ExecuteNonQuery();
return result;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
con.Close();
}
}
public string ex_scaler(SqlCommand com)
{
try
{
sqlconnection();
com.Connection = con;
com.CommandTimeout = 200000000;
con.Open();
string result = com.ExecuteScalar().ToString();
return result;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
con.Close();
}
}
public DataSet getdatasetwithLimit(SqlCommand com, string startPoint, string endPoint, string srctable)
{
try
{
sqlconnection();
com.Connection = con;
com.CommandTimeout = 200000000;
com.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
con.Close();
return ds;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
con.Close();
}
}
public DataSet getdataset(SqlCommand com)
{
try
{
sqlconnection();
com.Connection = con;
com.CommandTimeout = 200000000;
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
return ds;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
con.Close();
}
}
}
}