using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;
/// <summary>
/// Summary description for DataUtility
/// </summary>
public class DataUtility
{
# region All Module level variables
SqlDataAdapter Da;
SqlConnection Con;
SqlCommand Cmd;
DataSet ds;
# endregion
public DataUtility()
{
//
// TODO: Add constructor logic here
//
}
private void OpenConnection()
{
if (Con == null)
{
Con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["PIS_Billing_Connection"].ToString());
}
// lets open the connection
if (Con.State == ConnectionState.Closed)
{
Con.Open();
}
Cmd = new SqlCommand();
//Set active connection with the command object
Cmd.Connection = Con;
}
private void CloseConnection()
{
if (Con.State == ConnectionState.Open)
{
Con.Close();
}
}
/// <summary>
/// This is to release the memory from the connection object
/// </summary>
private void DisposeConnection()
{
if (Con != null)
{
Con.Dispose();
Con = null;
}
}
public int ExecuteSql(String Sql)
{
OpenConnection();
//int Result;
//Set command object properties
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = Sql;
Cmd.CommandTimeout = 3000;
// Now call the method
int Result = Cmd.ExecuteNonQuery();
CloseConnection();
DisposeConnection();
return Result;
}
public bool IsExist(string sql)
{
OpenConnection();
//now set command object properties
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = sql;
Cmd.CommandTimeout = 2000;
//Execute Command object method
int Result = (int)Cmd.ExecuteScalar();
CloseConnection();
DisposeConnection();
if (Result > 0)
{
return true;
}
else
{
return false;
}
}
public SqlDataReader GetDataReader(String Sql)
{
OpenConnection();
SqlDataReader dReader;
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = Sql;
Cmd.CommandTimeout = 1000;
dReader = Cmd.ExecuteReader();
//CommandBehavior.CloseConnection
return dReader;
}
public DataTable GetDataTable(String sql)
{
DataTable dt = new DataTable();
try
{
OpenConnection();
Da = new SqlDataAdapter();
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = sql;
Cmd.CommandTimeout = 2000;
Da.SelectCommand = Cmd;
//ds = new DataSet();
Da.Fill(dt);
//dt = ds.Tables["table"];
CloseConnection();
return dt;
}
catch
{
return dt;
}
}
public DataTable returnDataTable(string sql)
{
//Con.Open();
ds = new DataSet();
Da = new SqlDataAdapter(sql, Con);
Da.Fill(ds, "datatable");
DataTable dt = new DataTable();
dt = ds.Tables["datatable"];
Con.Close();
return (dt);
}
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;
/// <summary>
/// Summary description for DataUtility
/// </summary>
public class DataUtility
{
# region All Module level variables
SqlDataAdapter Da;
SqlConnection Con;
SqlCommand Cmd;
DataSet ds;
# endregion
public DataUtility()
{
//
// TODO: Add constructor logic here
//
}
private void OpenConnection()
{
if (Con == null)
{
Con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["PIS_Billing_Connection"].ToString());
}
// lets open the connection
if (Con.State == ConnectionState.Closed)
{
Con.Open();
}
Cmd = new SqlCommand();
//Set active connection with the command object
Cmd.Connection = Con;
}
private void CloseConnection()
{
if (Con.State == ConnectionState.Open)
{
Con.Close();
}
}
/// <summary>
/// This is to release the memory from the connection object
/// </summary>
private void DisposeConnection()
{
if (Con != null)
{
Con.Dispose();
Con = null;
}
}
public int ExecuteSql(String Sql)
{
OpenConnection();
//int Result;
//Set command object properties
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = Sql;
Cmd.CommandTimeout = 3000;
// Now call the method
int Result = Cmd.ExecuteNonQuery();
CloseConnection();
DisposeConnection();
return Result;
}
public bool IsExist(string sql)
{
OpenConnection();
//now set command object properties
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = sql;
Cmd.CommandTimeout = 2000;
//Execute Command object method
int Result = (int)Cmd.ExecuteScalar();
CloseConnection();
DisposeConnection();
if (Result > 0)
{
return true;
}
else
{
return false;
}
}
public SqlDataReader GetDataReader(String Sql)
{
OpenConnection();
SqlDataReader dReader;
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = Sql;
Cmd.CommandTimeout = 1000;
dReader = Cmd.ExecuteReader();
//CommandBehavior.CloseConnection
return dReader;
}
public DataTable GetDataTable(String sql)
{
DataTable dt = new DataTable();
try
{
OpenConnection();
Da = new SqlDataAdapter();
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = sql;
Cmd.CommandTimeout = 2000;
Da.SelectCommand = Cmd;
//ds = new DataSet();
Da.Fill(dt);
//dt = ds.Tables["table"];
CloseConnection();
return dt;
}
catch
{
return dt;
}
}
public DataTable returnDataTable(string sql)
{
//Con.Open();
ds = new DataSet();
Da = new SqlDataAdapter(sql, Con);
Da.Fill(ds, "datatable");
DataTable dt = new DataTable();
dt = ds.Tables["datatable"];
Con.Close();
return (dt);
}
}
No comments:
Post a Comment