private static void GetDataByCriteriaTest(string criteria, object[] parameters, string predicate = "=")
{
TransactionWork transactionWork = null;
SqlParameter[] parameterCustomer = CreateUSP(criteria, parameters);
SqlParameter[] parameterReg = CreateUSP(criteria, parameters);
SqlParameter[] parameterInv = CreateUSP(criteria, parameters);
SqlParameter[] parameterInvBenefits = CreateUSP(criteria, parameters);
SqlParameter[] parameterAddr = CreateUSP(criteria, parameters);
try
{
using (transactionWork = (TransactionWork)TransactionFactory.Create())
{
transactionWork.Execute(_tables.CustomerDataTable, $"uspGetCustomerBy{criteria}",
parameterCustomer);
transactionWork.Execute(_tables.RegisterDataTable, $"uspGetRegisterBy{criteria}", parameterReg);
transactionWork.Execute(_tables.InvalidDataTable, $"uspGetInvalidBy{criteria}", parameterInv);
transactionWork.Execute(_tables.InvalidBenefitsDataTable, $"uspGetInvalidBenefitsBy{criteria}",
parameterInvBenefits);
transactionWork.Execute(_tables.AddressDataTable, $"uspGetAddressBy{criteria}", parameterAddr);
transactionWork.Commit();
}
WhereClasure(criteria, parameters, predicate);
}
catch (Exception)
{
transactionWork?.Rollback();
throw;
}
}
private static void GetDataByGlossary(string glossaryName, int id)
{
TransactionWork transactionWork = null;
string storageProcedureName = string.Format("uspGet{0}By{1}", "Customer", glossaryName);
SqlParameter parameter = new SqlParameter();
parameter.DbType = DbType.Int32;
parameter.ParameterName = "@ID";
parameter.Value = id;
string storageProcedureNameReg = string.Format("uspGet{0}By{1}", "Register", glossaryName);
SqlParameter parameterReg = new SqlParameter();
parameterReg.DbType = DbType.Int32;
parameterReg.ParameterName = "@ID";
parameterReg.Value = id;
string storageProcedureNameInv = string.Format("uspGet{0}By{1}", "Invalid", glossaryName);
SqlParameter parameterInv = new SqlParameter();
parameterInv.DbType = DbType.Int32;
parameterInv.ParameterName = "@ID";
parameterInv.Value = id;
string storageProcedureNameInvBenefits = string.Format("uspGet{0}By{1}", "InvalidBenefits", glossaryName);
SqlParameter parameterInvBenefits = new SqlParameter();
parameterInvBenefits.DbType = DbType.Int32;
parameterInvBenefits.ParameterName = "@ID";
parameterInvBenefits.Value = id;
string storageProcedureNameAddr = string.Format("uspGet{0}By{1}", "Address", glossaryName);
SqlParameter parameterAddr = new SqlParameter();
parameterAddr.DbType = DbType.Int32;
parameterAddr.ParameterName = "@ID";
parameterAddr.Value = id;
try
{
using (transactionWork = (TransactionWork)TransactionFactory.Create())
{
transactionWork.Execute(_tables.CustomerDataTable, storageProcedureName, parameter);
transactionWork.Execute(_tables.RegisterDataTable, storageProcedureNameReg, parameterReg);
transactionWork.Execute(_tables.InvalidDataTable, storageProcedureNameInv, parameterInv);
transactionWork.Execute(_tables.InvalidBenefitsDataTable, storageProcedureNameInvBenefits, parameterInvBenefits);
transactionWork.Execute(_tables.AddressDataTable, storageProcedureNameAddr, parameterAddr);
transactionWork.Commit();
}
WhereClasure(glossaryName, new object[] { id }, "=");
//whereClasure = "where " + glossaryName + "ID = @param";
//value = id;
//dbType = "@param int";
}
catch (Exception)
{
transactionWork?.Rollback();
throw;
}
}
private static void GetDataByCustomerID(int id)
{
TransactionWork transactionWork = null;
String storageProcedureName = "uspGetCustomers";
SqlParameter parameter = new SqlParameter();
parameter.DbType = DbType.Int32;
parameter.Size = 4;
parameter.ParameterName = "@CustomerID";
parameter.Value = id;
SqlParameter parameterReg = new SqlParameter();
parameterReg.DbType = DbType.Int32;
parameterReg.Size = 4;
parameterReg.ParameterName = "@CustomerID";
parameterReg.Value = id;
SqlParameter parameterInv = new SqlParameter();
parameterInv.DbType = DbType.Int32;
parameterInv.Size = 4;
parameterInv.ParameterName = "@CustomerID";
parameterInv.Value = id;
SqlParameter parameterAddr = new SqlParameter();
parameterAddr.DbType = DbType.Int32;
parameterAddr.Size = 4;
parameterAddr.ParameterName = "@CustomerID";
parameterAddr.Value = id;
try
{
using (transactionWork = (TransactionWork)TransactionFactory.Create())
{
transactionWork.Execute(_tables.CustomerDataTable, storageProcedureName, parameter);
transactionWork.Execute(_tables.RegisterDataTable, "uspGetRegisterByCustomerID", parameterReg);
transactionWork.Execute(_tables.InvalidDataTable, "uspGetInvalidByCustomerID", parameterInv);
transactionWork.Execute(_tables.AddressDataTable, "uspGetAddressByCustomerID", parameterAddr);
transactionWork.Commit();
}
whereClasure = "where vgc.CustomerID = @param";
value = id;
dbType = "@param int";
}
catch (Exception)
{
transactionWork?.Rollback();
throw;
}
}
public static void ExportToExcel()
{
DataTable table = new DataTable();
TransactionWork transactionWork = null;
SqlParameter[] parameters = new SqlParameter[3];
SqlParameter parameter = new SqlParameter();
parameter.DbType = DbType.String;
parameter.Size = 100;
parameter.ParameterName = "@whereClasure";
parameter.Value = whereClasure;
parameters[0] = parameter;
parameter = new SqlParameter();
parameter.DbType = DbType.String;
parameter.ParameterName = "@Param";
parameter.Size = 100;
if (value == null)
{
parameter.Value = string.Empty;
}
else
{
parameter.Value = value;
}
parameter.IsNullable = true;
parameters[1] = parameter;
parameter = new SqlParameter();
parameter.DbType = DbType.String;
parameter.Size = 30;
parameter.ParameterName = "@ParamType";
parameter.Value = dbType;
parameter.IsNullable = true;
parameters[2] = parameter;
try
{
using (transactionWork = (TransactionWork)TransactionFactory.Create())
{
transactionWork.Execute(table, "uspDynamicQuery", parameters);
transactionWork.Commit();
}
}
catch (Exception)
{
transactionWork?.Rollback();
throw;
}
FastExportingMethod.ExportToExcel(table,
Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "List"));
FastExportingMethod.GemExportToExcel(table,
Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "List2.xls"));
table.Dispose();
table = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
