using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
namespace Admin.shibashish
{
public class DataBaseUtility
{
public class common
{
//internal void LogWriter(string spString, string p)
//{
// throw new NotImplementedException();
//}
}
#region Constructor
public DataBaseUtility()
{
//your connectionstring which is written inside the web.config file
ConnString = ConfigurationManager.ConnectionStrings["cs"].ToString();
}
#endregion
#region Variables
SqlCommand command;
SqlDataAdapter dataAdapter;
SqlConnection Conn;
String ConnString;
common objCommon = new common();
#endregion
/// <summary>
/// openConnection
/// </summary>
public void openConnection()
{
if (Conn == null)
{
Conn = new SqlConnection(ConnString);
if (Conn.State == ConnectionState.Closed)
Conn.Open();
command = new SqlCommand();
command.Connection = Conn;
}
}
/// <summary>
/// closeConnection
/// </summary>
public void closeConnection()
{
if (Conn.State == ConnectionState.Open)
Conn.Close();
}
/// <summary>
/// disposeConnection
/// </summary>
public void disposeConnection()
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Closed)
{
Conn.Dispose();
Conn = null;
}
}
}
/// <summary>
/// Function to Fetch data from DataBase
/// </summary>
/// <param name="spString"></param>
/// <param name="auctionParam"></param>
/// <returns>Int</returns>
///
public int ExecuteNonQuery(string spString, params SqlParameter[] auctionParam)
{
try
{
openConnection();
SqlParameter param = new SqlParameter();
dataAdapter = new SqlDataAdapter();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = spString;
command.Parameters.Clear();
foreach (SqlParameter LoopVar_param in auctionParam)
{
command.Parameters.Add(LoopVar_param);
}
command.Parameters.Add("@ReturnValue", SqlDbType.Int);
command.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;
command.ExecuteNonQuery();
int outParam = ((int)command.Parameters["@ReturnValue"].Value);
if (outParam == 0)
errorMsg(spString);
return outParam;
}
catch
{
throw;
}
finally
{
command.Dispose();
closeConnection();
disposeConnection();
}
}
/// <summary>
/// Function to Fetch data from DataBase
/// </summary>
/// <param name="spString"></param>
/// <param name="auctionParam"></param>
/// <returns>SqlDataReader</returns>
///
public object ExecuteScalar(string spString, params SqlParameter[] auctionParam)
{
try
{
openConnection();
SqlParameter param = new SqlParameter();
dataAdapter = new SqlDataAdapter();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = spString;
command.Parameters.Clear();
foreach (SqlParameter LoopVar_param in auctionParam)
{
command.Parameters.Add(LoopVar_param);
}
object obj = command.ExecuteScalar();
return obj;
}
catch
{
throw;
}
finally
{
command.Dispose();
}
}
public SqlDataReader ExecuteReader(string spString, params SqlParameter[] auctionParam)
{
try
{
openConnection();
SqlParameter param = new SqlParameter();
dataAdapter = new SqlDataAdapter();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = spString;
command.Parameters.Clear();
foreach (SqlParameter LoopVar_param in auctionParam)
{
command.Parameters.Add(LoopVar_param);
}
return command.ExecuteReader();
}
catch
{
throw;
}
finally
{
command.Dispose();
}
}
/// <summary>
/// Function to Fetch data from DataBase
/// </summary>
/// <param name="spString"></param>
/// <param name="auctionParam"></param>
/// <returns>DataSet</returns>
///
public DataSet ExecuteDataSet(string spString, params SqlParameter[] auctionParam)
{
DataSet ds = new DataSet();
try
{
openConnection();
SqlParameter param = new SqlParameter();
dataAdapter = new SqlDataAdapter();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = spString;
command.Parameters.Clear();
dataAdapter.SelectCommand = command;
foreach (SqlParameter LoopVar_param in auctionParam)
{
command.Parameters.Add(LoopVar_param);
}
dataAdapter.Fill(ds);
return ds;
}
catch
{
throw;
}
finally
{
ds.Dispose();
closeConnection();
disposeConnection();
}
}
/// <summary>
/// Overload function if there are no parameters
/// </summary>
/// <param name="spString"> </param>
/// <returns>DataSet</returns>
///
public DataSet ExecuteDataSet(string spString)
{
DataSet ds = new DataSet();
try
{
openConnection();
SqlParameter param = new SqlParameter();
dataAdapter = new SqlDataAdapter();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = spString;
dataAdapter.SelectCommand = command;
dataAdapter.Fill(ds);
return ds;
}
catch
{
throw;
}
finally
{
ds.Dispose();
closeConnection();
disposeConnection();
}
}
/// <summary>
/// Function to Fetch data from DataBase
/// </summary>
/// <param name="spString"></param>
/// <param name="auctionParam"></param>
/// <returns>DataTable</returns>
///
public DataTable ExecuteDataTable(string spString, params SqlParameter[] auctionParam)
{
DataTable dt = new DataTable();
try
{
openConnection();
SqlParameter param = new SqlParameter();
dataAdapter = new SqlDataAdapter();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = spString;
command.Parameters.Clear();
dataAdapter.SelectCommand = command;
foreach (SqlParameter LoopVar_param in auctionParam)
{
command.Parameters.Add(LoopVar_param);
}
dataAdapter.Fill(dt);
return dt;
}
catch
{
throw;
}
finally
{
dt.Dispose();
closeConnection();
disposeConnection();
}
}
/// <summary>
/// Overload function if there are no parameters
/// </summary>
/// <param name="spString"> </param>
/// <returns>DataTable</returns>
///
public DataTable ExecuteDataTable(string spString)
{
DataTable dt = new DataTable();
try
{
openConnection();
SqlParameter param = new SqlParameter();
dataAdapter = new SqlDataAdapter();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = spString;
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dt);
return dt;
}
catch
{
throw;
}
finally
{
dt.Dispose();
closeConnection();
disposeConnection();
}
}
public void errorMsg(string spString)
{
common objcommn = new common();
//objCommon.LogWriter(spString, "Unexpected error occurred!");
}
}
}
No comments:
Post a Comment
Please don't spam, spam comments is not allowed here.