/// <summary>
/// Execute procedure
/// </summary>
/// <param name="commandName">procedure name</param>
/// <param name="paramCollection">collatin of paramter</param>
/// <returns></returns>
public DataSet ExecuteProcedure(string commandName, List <SqlParameter> paramCollection)
{
DataSet ds = new DataSet();
try
{
connection = new SqlConnection(connectionstring);
command = new SqlCommand(commandName, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
foreach (SqlParameter item in paramCollection)
{
command.Parameters.Add(item);
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = command;
da.Fill(ds);
}
catch (Exception ex)
{
LibLogging.WriteErrorToDB("DBHelper", "ExecuteProcedure", ex);
}
finally
{
connection.Close();
}
return(ds);
}
public int ExecuteNonQuery(string commandName, List <SqlParameter> paramCollection)
{
int rowAffected = 0;
try
{
connection = new SqlConnection(connectionstring);
connection.Open();
command = new SqlCommand(commandName, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
foreach (SqlParameter item in paramCollection)
{
command.Parameters.Add(item);
}
rowAffected = command.ExecuteNonQuery();
}
catch (Exception ex)
{
LibLogging.WriteErrorToDB("DBHelper", "ExecuteNonQuery", ex);
}
finally
{
connection.Close();
}
return(rowAffected);
}
public List <SelectListItem> GetClientList()
{
_clientRepository = new ClientRepository();
List <SelectListItem> clientDCList = new List <SelectListItem>();
List <HOAListDataContract> clientObjDCList = new List <HOAListDataContract>();
try
{
DataSet ds = new DataSet();
ds = _clientRepository.GetClientList();
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataTable item in ds.Tables)
{
string tableName = item.Rows != null && item.Rows.Count > 0 ? item.Rows[0][0].ToString() : string.Empty;
clientObjDCList = GenericConversionHelper.DataTableToList <HOAListDataContract>(item);
}
}
if (clientObjDCList != null && clientObjDCList.Count > 0)
{
clientDCList = clientObjDCList.Select(x => new SelectListItem {
Text = x.Client_Name, Value = x.ClientID
}).ToList();
}
}
catch (Exception ex)
{
LibLogging.WriteErrorToDB("DropDownManager", "GetClientList", ex);
}
return(clientDCList);
}
/// <summary>
/// Method to get the DataTable from the procedure.
/// </summary>
/// <param name="oSql"></param>
/// <param name="sqlParam"></param>
/// <returns></returns>
public DataTable ExecuteDataTable(string oSql, params SqlParameter[] sqlParam)
{
DataSet oDS = null;
DataTable oDT = null;
try
{
connection = new SqlConnection(connectionstring);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = oSql;
if ((sqlParam != null))
{
foreach (SqlParameter param in sqlParam)
{
cmd.Parameters.Add(param);
}
}
SqlDataAdapter oDA = new SqlDataAdapter(cmd);
oDS = new DataSet();
oDA.Fill(oDS);
if (oDS != null && oDS.Tables.Count > 0)
{
oDT = new DataTable();
oDT = oDS.Tables[0];
}
}
catch (Exception ex)
{
LibLogging.WriteErrorToDB("DBHelper", "ExecuteDataTable", ex);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(oDT);
}
public ActionResult ExpenseAddItem(int?id)
{
ExpenseDataContract expenseDC = new ExpenseDataContract();
_dropDownManager = new DropDownManager();
try
{
expenseDC.ClientList = _dropDownManager.GetClientList();
expenseDC.ProductList = _dropDownManager.GetProductList();
}
catch (Exception ex)
{
var trace = new StackTrace(ex, true);
var frame = trace.GetFrame(0);
var line = frame.GetFileLineNumber();
LibLogging.WriteErrorToDB("ExpenseAddItem", "LineNumber" + line.ToString() + "-" + ex.Message, ex);
}
return(View(expenseDC));
}
public ActionResult ExpenseAddItem(ExpenseDataContract expenseDC)
{
_expenseManager = new ExpenseManager();
int result = 0;
try
{
if (expenseDC != null)
{
result = _expenseManager.ManageExpenseItem(expenseDC);
}
}
catch (Exception ex)
{
var trace = new StackTrace(ex, true);
var frame = trace.GetFrame(0);
var line = frame.GetFileLineNumber();
LibLogging.WriteErrorToDB("ExpenseAddItem", "LineNumber" + line.ToString() + "-" + ex.Message, ex);
}
return(RedirectToAction("ExpenseAddItem", "Expense"));
}
