/// <summary>
///
/// </summary>
/// <param name="SPName"></param>
/// <param name="SQLParams"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string SPName, ref SqlParameter[] SQLParams, string ConnString)
{
SqlCommand Command = new SqlCommand();
SqlDataReader Reader = null;
try
{
if (Command.Parameters.Count > 0)
{
Command.Parameters.Clear();
}
Command.CommandText = SPName;
HelpDeskConnection ac = new HelpDeskConnection();
Command.Connection = ac.GetConnection(ConnString);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.AddRange(SQLParams);
Reader = Command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
throw ex;
}
return(Reader);
}
/// <summary>
///
/// </summary>
/// <param name="SPName"></param>
/// <param name="SQLParams"></param>
/// <returns></returns>
public static object ExecuteScalar(string SPName, ref SqlParameter[] SQLParams, string ConnString)
{
SqlCommand Command = new SqlCommand();
object obj = null;
try
{
if (Command.Parameters.Count > 0)
{
Command.Parameters.Clear();
}
Command.CommandText = SPName;
HelpDeskConnection ac = new HelpDeskConnection();
Command.Connection = ac.GetConnection(ConnString);
Command.CommandType = CommandType.StoredProcedure;
if (SQLParams != null)
{
Command.Parameters.AddRange(SQLParams);
}
obj = Command.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (Command.Connection.State == ConnectionState.Open)
{
Command.Connection.Close();
}
}
return(obj);
}
/// <summary>
///
/// </summary>
/// <param name="Sql"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string Sql, string ConnString)
{
SqlCommand Command = new SqlCommand();
SqlDataAdapter Adapter = new SqlDataAdapter();
DataTable DataDetails = new DataTable();
try
{
Command.CommandText = Sql;
Command.CommandType = CommandType.Text;
HelpDeskConnection ac = new HelpDeskConnection();
Command.Connection = ac.GetConnection(ConnString);
Adapter.SelectCommand = Command;
Adapter.Fill(DataDetails);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (Command.Connection != null)
{
if (Command.Connection.State == ConnectionState.Open)
{
Command.Connection.Close();
}
}
}
return(DataDetails);
}
/// <summary>
/// this method retuns the datatable by executing stored procedure using connection string
/// </summary>
/// <param name="SPName"></param>
/// <param name="SQLParams"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string SPName, ref SqlParameter[] SQLParams, string ConnString)
{
SqlCommand Command = new SqlCommand();
SqlDataAdapter Adapter = new SqlDataAdapter();
DataTable DataDetails = new DataTable();
try
{
if (Command.Parameters.Count > 0)
{
Command.Parameters.Clear();
}
Command.CommandText = SPName;
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.AddRange(SQLParams);
HelpDeskConnection ac = new HelpDeskConnection();
Command.Connection = ac.GetConnection(ConnString);
Adapter.SelectCommand = Command;
Adapter.Fill(DataDetails);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (Command.Connection != null)
{
if (Command.Connection.State == ConnectionState.Open)
{
Command.Connection.Close();
}
}
}
return(DataDetails);
}
public static object ExecuteScaler(string spName, string ConnString)
{
SqlCommand Command = new SqlCommand();
object obj = null;
try
{
using (SqlConnection Conn = new SqlConnection(ConnectionString))
{
Command.CommandText = spName;
HelpDeskConnection ac = new HelpDeskConnection();
Command.Connection = ac.GetConnection(ConnString);
Command.CommandType = CommandType.StoredProcedure;
obj = Command.ExecuteScalar();
}
}
catch (Exception ex)
{
throw ex;
}
return(obj);
}
/// <summary>
///
/// </summary>
/// <param name="SPName"></param>
/// <param name="SQLParams"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string SPName, ref SqlParameter[] SQLParams, string ConnString)
{
SqlCommand Command = new SqlCommand();
int cnt = 0;
try
{
if (Command.Parameters.Count > 0)
{
Command.Parameters.Clear();
}
Command.CommandText = SPName;
HelpDeskConnection ac = new HelpDeskConnection();
Command.Connection = ac.GetConnection(ConnString);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.AddRange(SQLParams);
Command.CommandTimeout = 1800;
cnt = Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (Command.Connection != null)
{
if (Command.Connection.State == ConnectionState.Open)
{
Command.Connection.Close();
}
}
}
return(cnt);
}
/// <summary>
///
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, string ConnString)
{
SqlCommand Command = new SqlCommand();
int cnt = 0;
try
{
using (SqlConnection Conn = new SqlConnection(ConnString))
{
Conn.Open();
Command.CommandText = sql;
HelpDeskConnection ac = new HelpDeskConnection();
Command.Connection = Conn;
Command.CommandType = CommandType.Text;
cnt = Command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
return(cnt);
}