using System;
using System.Data;
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 System.Data.SqlClient;
/// <summary>
/// Class contains all
Functions & Procedures, releated to execute Queries,SPs etc
/// </summary>
///
public class clsDatabase
{
#region Member variables
private static string _connString =
System.Configuration.ConfigurationManager.ConnectionStrings["ICTConstr"].ConnectionString;
#endregion
#region Constructor
public clsDatabase()
{
}
#endregion
//Return Connection String
private static string ConnString
{
get
{
return _connString;
}
}
//Return SqlConnection
private static
SqlConnection getConnection()
{
SqlConnection conn;
try
{
conn = new SqlConnection(ConnString);
}
catch
{
throw new Exception("SQL
Connection String is invalid.");
}
return conn;
}
//Execute Stored Procedure And Returns Dataview
public static
DataView GetSPDataView(string procedureName,
SqlCommand command)
{
SqlConnection connection = getConnection();
DataSet ds
= new DataSet();
SqlDataAdapter da = new
SqlDataAdapter();
try
{
command.CommandTimeout = 2000;
command.CommandText = procedureName;
command.Connection = connection;
//Mark As Stored Procedure
command.CommandType = CommandType.StoredProcedure;
da.SelectCommand = command;
da.Fill(ds);
}
catch (Exception
ex)
{
throw ex;
}
finally
{
command.Connection.Close();
command.Connection.Dispose();
command.Dispose();
if (connection.State == ConnectionState.Open)
connection.Close();
}
return ds.Tables[0].DefaultView;
}
//Execute Query And Returns Dataview
public static
DataView GetDataView(string sqlQuery)
{
SqlConnection connection = getConnection();
DataSet ds
= new DataSet();
SqlDataAdapter da = new
SqlDataAdapter();
DataView dv
= new DataView();
try
{
da = new SqlDataAdapter(sqlQuery, connection);
da.Fill(ds);
dv.Table = ds.Tables[0];
}
catch (Exception
ex)
{
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
return dv;
}
//Execute Stored Procedure And Returns Datatable
public static
DataTable GetDataTable(string procedureName,
SqlCommand command, string tableName)
{
SqlConnection connection = getConnection();
SqlDataAdapter da = new
SqlDataAdapter();
DataTable
dt = new DataTable();
try
{
command.CommandText = procedureName;
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
da.SelectCommand = command;
da.Fill(dt);
dt.TableName = tableName;
}
catch
(Exception ex)
{
throw ex;
}
finally
{
command.Connection.Close();
command.Connection.Dispose();
command.Dispose();
if (connection.State == ConnectionState.Open)
connection.Close();
}
return dt;
}
//Execute Query And Returns Datatable
public static
DataTable GetDataTable(string strSQL)
{
SqlConnection connection = getConnection();
SqlDataAdapter da = new
SqlDataAdapter(strSQL, connection);
DataTable
dt = new DataTable();
try
{
da.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
return dt;
}
public static
DataTable GetDataTable(SqlCommand objParaCmd)
{
SqlConnection connection = getConnection();
SqlDataAdapter objDA = new
SqlDataAdapter();
DataTable
objDtb = new DataTable();
SqlCommand
Cmd = new SqlCommand();
try
{
Cmd =
objParaCmd;
connection.Open();
objParaCmd.Connection = connection;
objDA.SelectCommand = Cmd;
objDA.Fill(objDtb);
return objDtb;
}
catch (Exception
Ex)
{
throw Ex;
}
finally
{
objParaCmd.Connection.Close();
objParaCmd.Connection.Dispose();
objParaCmd.Dispose();
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
//Execute Stored Procedure And Returns Datareader
public static SqlDataReader GetDataReader(string procedureName, SqlCommand command)
{
SqlConnection connection = getConnection();
SqlDataReader dr = null;
try
{
command.CommandText = procedureName;
command.Connection = connection;
//Mark As Stored Procedure
command.CommandType = CommandType.StoredProcedure;
connection.Open();
dr =
command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception
ex)
{
throw ex;
}
return dr;
}
//Execute Query AND Returns Datareader
public static SqlDataReader GetDataReader(string Query)
{
SqlConnection connection = getConnection();
SqlCommand
command=new SqlCommand ();
SqlDataReader dr = null;
try
{
command.CommandText = Query;
command.Connection = connection;
command.CommandType = CommandType.Text ;
connection.Open();
dr =
command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception
ex)
{
throw ex;
}
return dr;
}
//Execute SQL Query & Doesn't Return Anything
public static void ExecuteNonQuery(string
strSQL)
{
SqlConnection connection = getConnection();
connection.Open();
SqlCommand
command = new SqlCommand();
try
{
command.CommandType = CommandType.Text;
command.Connection = connection;
command.CommandText = strSQL;
command.ExecuteNonQuery();
}
catch (Exception
ex)
{
throw ex;
}
finally
{
command.Connection.Close();
command.Connection.Dispose();
command.Dispose();
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
public static int ExecuteNonQuery(string
procedureName, SqlCommand command)
{
SqlConnection connection = getConnection();
connection.Open();
try
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = procedureName;
command.Connection = connection;
command.ExecuteNonQuery();
return 1;
}
catch (Exception
ex)
{
throw ex;
}
finally
{
command.Connection.Close();
command.Connection.Dispose();
command.Dispose();
if
(connection.State == ConnectionState.Open) connection.Close();
}
}
//Execute SP & Return Nothing
public static int ExecuteNonQuerySP(string
procedureName, SqlCommand command)
{
SqlConnection
connection = getConnection();
connection.Open();
try
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = procedureName;
command.Connection = connection;
command.ExecuteNonQuery();
//return
Convert.ToInt32(command.Parameters["@nErrorCode"].Value);
return Convert.ToInt16(command.Parameters["@vMessage"].Value);
}
catch (Exception
ex)
{
throw ex;
}
finally
{
command.Connection.Close();
command.Connection.Dispose();
command.Dispose();
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
public static string[] ExecuteNonQuerySP_CG(string procedureName, SqlCommand command,int
iDiv)
{
string[] strReturn = new
string[2];
SqlConnection connection = getConnection();
connection.Open();
try
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = procedureName;
command.Parameters.Add("@iDivision", SqlDbType.SmallInt);
command.Parameters["@iDivision"].Value
= iDiv;//Convert.ToInt16(clsAppSession.SessionDivisionId);
command.Parameters.Add("@dtFinYearStart",
SqlDbType.SmallDateTime);
command.Parameters["@dtFinYearStart"].Value
= Convert.ToDateTime(clsAppSession.SessionFYrFromDate);
command.Parameters.Add("@dtFinYearEnd",
SqlDbType.SmallDateTime);
command.Parameters["@dtFinYearEnd"].Value
= Convert.ToDateTime(clsAppSession.SessionFYrToDate);
command.Parameters.Add("@vFlag",
SqlDbType.VarChar, 10);
command.Parameters["@vFlag"].Direction
= ParameterDirection.Output;
command.Parameters.Add("@vMessage",
SqlDbType.VarChar, 100);
command.Parameters["@vMessage"].Direction
= ParameterDirection.Output;
command.Connection = connection;
command.ExecuteNonQuery();
//return
Convert.ToInt32(command.Parameters["@nErrorCode"].Value);
strReturn[0] = command.Parameters["@vFlag"].Value.ToString();
strReturn[1] = command.Parameters["@vMessage"].Value.ToString();
return
strReturn;
}
catch (Exception
ex)
{
throw ex;
}
finally
{
command.Connection.Close();
command.Connection.Dispose();
command.Dispose();
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
//Procedure to Execute Scalar
public static object ExecuteScalar(string
strSQL)
{
SqlConnection connection = getConnection();
connection.Open();
SqlCommand
command = new SqlCommand();
try
{
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = strSQL;
return command.ExecuteScalar();
}
catch (Exception
ex)
{
throw ex;
}
finally
{
command.Connection.Close();
command.Connection.Dispose();
command.Dispose();
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
//Execute SQL Query & Return Single Value
public static object ReturnSingleValue(string
strSQL, Boolean blnNumericFlag)
{
object objRetVal;
string strRetVal = "";
SqlConnection connection = getConnection();
connection.Open();
SqlCommand
command = new SqlCommand();
try
{
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = strSQL;
objRetVal = command.ExecuteScalar();
if (blnNumericFlag == true)
{
strRetVal = Convert.ToString(objRetVal);
}
else
{
strRetVal = (string)(objRetVal);
}
return strRetVal;
}
catch (Exception
ex)
{
throw ex;
}
finally
{
command.Connection.Close();
command.Connection.Dispose();
command.Dispose();
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
//Execute Query And Returns DataSet
public static DataSet GetDataSet(string
procedureName, SqlCommand command)
{
SqlConnection connection = getConnection();
DataSet ds
= new DataSet();
SqlDataAdapter da = new
SqlDataAdapter();
try
{
command.CommandTimeout = 2000;
command.CommandText = procedureName;
command.Connection = connection;
//Mark As Stored Procedure
command.CommandType = CommandType.StoredProcedure;
da.SelectCommand = command;
da.Fill(ds);
}
catch (Exception
ex)
{
throw ex;
}
finally
{
command.Connection.Close();
command.Connection.Dispose();
command.Dispose();
if (connection.State == ConnectionState.Open)
connection.Close();
}
return ds;
}
}
No comments:
Post a Comment