[Description("Fill a DataSet and update the source data")] private void FillAndUpdateDataset()
{
string selectSQL = "SELECT Id, Name, Value FROM SampleData WHERE Id > 0";
string addSQL = "INSERT INTO Products (Name, Value) VALUES (@name, @value);";
string updateSQL = "UPDATE SampleData SET Name = @name, Value = @value WHERE Id = @id";
string deleteSQL = "DELETE FROM SampleData WHERE Id = @id";
// Fill a DataSet from the Products table using the simple approach
DataSet simpleDS = db.ExecuteDataSet(CommandType.Text, selectSQL);
//DisplayTableNames(simpleDS, "ExecuteDataSet")
simpleDS = null;
// Fill a DataSet from the Products table using the LoadDataSet method
// This allows you to specify the name(s) for the table(s) in the DataSet
DataSet loadedDS = new DataSet("ProductsDataSet");
db.LoadDataSet(CommandType.Text, selectSQL, loadedDS, new string[] { "Products" });
//DisplayTableNames(loadedDS, "LoadDataSet")
// Update some data in the rows of the DataSet table
DataTable dt = loadedDS.Tables["Products"];
dt.Rows[0].Delete();
object[] rowData = new object[] { -1, "A New Row", "Added to the table at " + DateTime.Now.ToShortTimeString() };
dt.Rows.Add(rowData);
rowData = dt.Rows[1].ItemArray;
rowData[2] = "A new description at " + DateTime.Now.ToShortTimeString();
dt.Rows[1].ItemArray = rowData;
//DisplayRowValues(dt)
// Create the commands to update the original table in the database
DbCommand insertCommand = db.GetSqlStringCommand(addSQL);
db.AddInParameter(insertCommand, "name", DbType.String, "Name", DataRowVersion.Current);
db.AddInParameter(insertCommand, "description", DbType.String, "Description", DataRowVersion.Current);
DbCommand updateCommand = db.GetSqlStringCommand(updateSQL);
db.AddInParameter(updateCommand, "name", DbType.String, "Name", DataRowVersion.Current);
db.AddInParameter(updateCommand, "description", DbType.String, "Description", DataRowVersion.Current);
db.AddInParameter(updateCommand, "id", DbType.String, "Id", DataRowVersion.Original);
DbCommand deleteCommand = db.GetSqlStringCommand(deleteSQL);
db.AddInParameter(deleteCommand, "id", DbType.Int32, "Id", DataRowVersion.Original);
// Apply the updates in the DataSet to the original table in the database
int rowsAffected = db.UpdateDataSet(loadedDS, "Products", insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard);
Console.WriteLine("Updated a total of {0} rows in the database.", rowsAffected);
}
public bool InsertUpdateDelete(Customer customer, Database db, DbTransaction transaction)
{
DbCommand commandInsert = db.GetStoredProcCommand("usp_CustomerGroupInsert");
db.AddInParameter(commandInsert, "@GroupId", DbType.Int32, customer.CustomerId);
db.AddInParameter(commandInsert, "@CompanyId", DbType.Int32, customer.CompanyId);
db.AddInParameter(commandInsert, "@CustomerName", DbType.String, "CustomerName", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@PassportNumber", DbType.String, "PassportNumber", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@Gender", DbType.String, "Gender", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@GuestTypeId", DbType.Int32, "GuestTypeId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@Phone", DbType.String, "Phone", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@StatusId", DbType.Int32, (int)HBM.Common.Enums.HBMStatus.Active);
db.AddInParameter(commandInsert, "@IsGroupCustomer", DbType.Boolean, customer.IsGroupCustomer);
db.AddInParameter(commandInsert, "@CreatedUser", DbType.Int32, "CreatedUser", DataRowVersion.Current);
DbCommand commandUpdate = db.GetStoredProcCommand("usp_CustomerGroupUpdate");
db.AddInParameter(commandUpdate, "@CustomerId", DbType.Int32, "CustomerId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@CustomerName", DbType.String, "CustomerName", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@PassportNumber", DbType.String, "PassportNumber", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Gender", DbType.String, "Gender", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@GuestTypeId", DbType.Int32, "GuestTypeId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Phone", DbType.String, "Phone", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@StatusId", DbType.Int32, (int)HBM.Common.Enums.HBMStatus.Active);
db.AddInParameter(commandUpdate, "@UpdatedUser", DbType.Int32, "UpdatedUser", DataRowVersion.Current);
DbCommand commandDelete = db.GetStoredProcCommand("usp_CustomerDelete");
db.AddInParameter(commandDelete, "@CustomerId", DbType.Int32, "CustomerId", DataRowVersion.Current);
db.UpdateDataSet(customer.DsGroupCustomers, customer.DsGroupCustomers.Tables[0].TableName, commandInsert, commandUpdate, commandDelete, transaction);
return true;
}
public bool InsertUpdateDelete(ReservationAdditionalService reservationAddtionalService, Database db, DbTransaction transaction)
{
DbCommand commandInsert = db.GetStoredProcCommand("usp_ReservationAdditionalServiceInsert");
db.AddInParameter(commandInsert, "@ReservationId", DbType.Int64,reservationAddtionalService.ReservationId);
db.AddInParameter(commandInsert, "@Note", DbType.String, "Note", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@Amount", DbType.Decimal, "Amount", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CreatedUser", DbType.Int32, "CreatedUser", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@StatusId", DbType.Int32, "StatusId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@AdditionalServiceId", DbType.Int32, "AdditionalServiceId", DataRowVersion.Current);
DbCommand commandUpdate = db.GetStoredProcCommand("usp_ReservationAdditionalServiceUpdate");
db.AddInParameter(commandUpdate, "@ReservationAdditionalServiceId", DbType.Int64, "ReservationAdditionalServiceId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Note", DbType.String, "Note", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Amount", DbType.String, "Amount", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@UpdatedUser", DbType.Int32, "UpdatedUser", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@StatusId", DbType.Int32, "StatusId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@AdditionalServiceId", DbType.Int32, "AdditionalServiceId", DataRowVersion.Current);
DbCommand commandDelete = db.GetStoredProcCommand("usp_ReservationAdditionalServiceDelete");
db.AddInParameter(commandDelete, "@ReservationAdditionalServiceId", DbType.Int64, "ReservationAdditionalServiceId", DataRowVersion.Current);
db.UpdateDataSet(reservationAddtionalService.ReservationAdditionalServiceList, reservationAddtionalService.ReservationAdditionalServiceList.Tables[0].TableName, commandInsert, commandUpdate, commandDelete, transaction);
return true;
}
public bool InsertUpdateDelete(ReservationPayments reservationPayments, Database db, DbTransaction transaction)
{
DbCommand commandInsert = db.GetStoredProcCommand("usp_ReservationPaymentInsert");
db.AddInParameter(commandInsert, "@ReservationId", DbType.Int64, reservationPayments.ReservationId);
db.AddInParameter(commandInsert, "@PaymentDate", DbType.DateTime, "PaymentDate", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@ReferenceNumber", DbType.String, "ReferenceNumber", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@Notes", DbType.String, "Notes", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@PaymentTypeId", DbType.Int32, "PaymentTypeId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CurrencyId", DbType.Int32, "CurrencyId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CreditCardTypeId", DbType.Int32, "CreditCardTypeId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CCNo", DbType.String, "CCNo", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CCExpirationDate", DbType.DateTime, "CCExpirationDate", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CCNameOnCard", DbType.String, "CCNameOnCard", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CreatedUser", DbType.Int32, "CreatedUser", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@StatusId", DbType.Int32, "StatusId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@Amount", DbType.Decimal, "Amount", DataRowVersion.Current);
DbCommand commandUpdate = db.GetStoredProcCommand("usp_ReservationPaymentUpdate");
db.AddInParameter(commandUpdate, "@ReservationPaymentId", DbType.Int64, "ReservationPaymentId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@PaymentDate", DbType.DateTime, "PaymentDate", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@ReferenceNumber", DbType.String, "ReferenceNumber", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Notes", DbType.String, "Notes", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@PaymentTypeId", DbType.Int32, "PaymentTypeId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@CurrencyId", DbType.Int32, "CurrencyId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@CreditCardTypeId", DbType.Int32, "CreditCardTypeId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@CCNo", DbType.String, "CCNo", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@CCExpirationDate", DbType.DateTime, "CCExpirationDate", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@CCNameOnCard", DbType.String, "CCNameOnCard", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@UpdatedUser", DbType.Int32, "UpdatedUser", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@StatusId", DbType.Int32, "StatusId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Amount", DbType.Decimal, "Amount", DataRowVersion.Current);
DbCommand commandDelete = db.GetStoredProcCommand("usp_ReservationPaymentDelete");
db.AddInParameter(commandDelete, "@ReservationPaymentId", DbType.Int64, "ReservationPaymentId", DataRowVersion.Current);
db.UpdateDataSet(reservationPayments.ReservationPaymentList, reservationPayments.ReservationPaymentList.Tables[0].TableName, commandInsert, commandUpdate, commandDelete, transaction);
return true;
}
private bool UpdateVoucherDetails(VoucherRecievable voucher, Database db, DbTransaction pTransaction)
{
bool result = false;
try
{
DbCommand insCmd = db.GetStoredProcCommand(Constant.SP_Voucher_Receivable_Insert_Voucher_Details);
db.AddInParameter(insCmd, "@iPaymentID", DbType.Int64, voucher.PaymentID);
db.AddInParameter(insCmd, "@iInvoiceId", DbType.Int32, "InvoiceId", DataRowVersion.Current);
db.AddInParameter(insCmd, "@mAmount", DbType.Currency, "Amount", DataRowVersion.Current);
db.AddInParameter(insCmd, "@iCreatedBy", DbType.Int32, voucher.CreatedBy);
//DbCommand updCmd = db.GetStoredProcCommand(Constant.SP_Voucher_Insert_Voucher_Details);
//db.AddInParameter(updCmd, "@iPaymentDetailID", DbType.Int64, "PaymentDetailID",DataRowVersion.Current);
//db.AddInParameter(updCmd, "@iInvoiceId", DbType.Int32, "InvoiceId", DataRowVersion.Current);
//db.AddInParameter(updCmd, "@mAmount", DbType.Currency, "Amount", DataRowVersion.Current);
//db.AddInParameter(updCmd, "@iModifiedBy", DbType.Currency, "ModifiedBy", DataRowVersion.Current);
int rowcount = 0;
rowcount = db.UpdateDataSet(voucher.DsPaymentDetails, voucher.DsPaymentDetails.Tables[0].TableName, insCmd, null, null, pTransaction);
result = true;
}
catch (System.Exception ex)
{
result = false;
throw ex;
}
return result;
}
/// <summary>
/// Updates cheque details, update individual cheques
/// </summary>
/// <param name="chqBook"></param>
/// <param name="db"></param>
/// <param name="transaction"></param>
/// <returns></returns>
private bool UpdateCheques(ChequeBook chqBook, Database db, DbTransaction transaction)
{
bool result = false;
try
{
DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_Cheque_InsertDetails);
db.AddInParameter(insCommand, "@iChqBookId", DbType.Int32, chqBook.ChqBookId);
db.AddInParameter(insCommand, "@tiChqStatusId", DbType.Int16, "ChqStatusId", DataRowVersion.Current);
db.AddInParameter(insCommand, "@biChequeNo", DbType.Int64, "ChequeNo", DataRowVersion.Current);
DbCommand updCommand = db.GetStoredProcCommand(Constant.SP_Cheque_UpdateDetails);
db.AddInParameter(updCommand, "@iChqId", DbType.Int32, "ChqId", DataRowVersion.Current);
db.AddInParameter(updCommand, "@mAmount", DbType.Currency, "Amount", DataRowVersion.Current);
db.AddInParameter(updCommand, "@sComment", DbType.String, "Comment", DataRowVersion.Current);
db.AddInParameter(updCommand, "@tiChqStatusId", DbType.Int16, "ChqStatusId", DataRowVersion.Current);
//db.AddInParameter(updCommand, "@dtWrittenDate", DbType.DateTime, "WrittenDate", DataRowVersion.Current);
//db.AddInParameter(updCommand, "@dtChqDate", DbType.DateTime, "ChqDate", DataRowVersion.Current);
db.AddInParameter(updCommand, "@iWrittenBy", DbType.Int32, "WrittenBy", DataRowVersion.Current);
db.AddInParameter(updCommand, "@iModifiedBy", DbType.Int32, "ModifiedBy", DataRowVersion.Current);
DbCommand delCommand = db.GetStoredProcCommand(Constant.SP_Cheque_DeleteDetails);
db.AddInParameter(delCommand, "@iChqId", DbType.Int32, "ChqId", DataRowVersion.Current);
db.UpdateDataSet(chqBook.DsCheques, chqBook.DsCheques.Tables[0].TableName, insCommand, updCommand, delCommand, transaction);
result = true;
}
catch (System.Exception ex)
{
throw ex;
}
return result;
}
public bool InsertUpdateDelete(DataSet dsComments, Database db, DbTransaction transaction)
{
DbCommand commandInsert = db.GetStoredProcCommand("usp_CommentInsert");
db.AddInParameter(commandInsert, "CommentId", DbType.Int32, "CommentId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "CommentText", DbType.String, "CommentText", DataRowVersion.Current);
db.AddInParameter(commandInsert, "RatingValue", DbType.Decimal, "RatingValue", DataRowVersion.Current);
db.AddInParameter(commandInsert, "ContextTypeId", DbType.Int32, "ContextTypeId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "CommentTypeId", DbType.Int32, "CommentTypeId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "FilePath", DbType.String, "FilePath", DataRowVersion.Current);
db.AddInParameter(commandInsert, "IsDeleted", DbType.String, "IsDeleted", DataRowVersion.Current);
db.AddInParameter(commandInsert, "CreatedBy", DbType.Guid, "CreatedBy", DataRowVersion.Current);
db.AddOutParameter(commandInsert, "CreatedDate", DbType.DateTime, 30);
DbCommand commandUpdate = db.GetStoredProcCommand("usp_CommentUpdate");
db.AddInParameter(commandUpdate, "CommentId", DbType.Int32, "CommentId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "CommentText", DbType.String, "CommentText", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "RatingValue", DbType.Decimal, "RatingValue", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "ContextTypeId", DbType.Int32, "ContextType", DataRowVersion.Current);
db.AddInParameter(commandInsert, "CommentTypeId", DbType.Int32, "CommentTypeId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "FilePath", DbType.String, "FilePath", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "IsDeleted", DbType.Boolean, "IsDeleted", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "UpdatedBy", DbType.Guid, "UpdatedBy", DataRowVersion.Current);
db.AddOutParameter(commandUpdate, "UpdatedDate", DbType.DateTime, 30);
DbCommand commandDelete = db.GetStoredProcCommand("usp_CommentDelete");
db.AddInParameter(commandDelete, "CommentId", DbType.Int32, "CommentId", DataRowVersion.Current);
db.AddInParameter(commandDelete, "UpdatedBy", DbType.Guid, "UpdatedBy", DataRowVersion.Current);
db.UpdateDataSet(dsComments, dsComments.Tables[0].TableName, commandInsert, commandUpdate, commandDelete, transaction);
return true;
}
private bool UpdateBrandCategories(Brand brand, Database db, DbTransaction transaction)
{
bool result = false;
try
{
DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_BrandCategories_InsBrandCategories);
db.AddInParameter(insCommand, "@iBrandId", DbType.Int32, brand.BrandId);
db.AddInParameter(insCommand, "@iCategoryId", DbType.Int32, "CategoryId", DataRowVersion.Current);
DbCommand DelCommand = db.GetStoredProcCommand(Constant.SP_BrandCategories_DelBrandCategories);
db.AddInParameter(DelCommand, "@iBrandId", DbType.Int32, brand.BrandId);
db.AddInParameter(DelCommand, "@iCategoryId", DbType.Int32, "CategoryId", DataRowVersion.Current);
db.UpdateDataSet(brand.DsCategories, brand.DsCategories.Tables[0].TableName, insCommand, null, DelCommand, transaction);
result = true;
}
catch (System.Exception ex)
{
throw ex;
}
return result;
}
private bool AddItemTransferInvoice(ItemTransfer itemTransfer, Database db, DbTransaction transaction)
{
bool result = false;
try
{
DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_Item_Transfer_Invoice_Insert);
db.AddInParameter(insCommand, "@iTransferId", DbType.Int32, itemTransfer.TransferId);
db.AddInParameter(insCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current);
db.AddInParameter(insCommand, "@iQuantity", DbType.Int32, "Quantity", DataRowVersion.Current);
db.AddInParameter(insCommand, "@iBranchFrom", DbType.Int32, itemTransfer.BranchFrom);
db.AddInParameter(insCommand, "@iBranchTo", DbType.Int32, itemTransfer.BranchTo);
db.UpdateDataSet(itemTransfer.DsTransferInvoiceItems, itemTransfer.DsTransferInvoiceItems.Tables[0].TableName, insCommand,
null, null, transaction);
result = true;
}
catch (System.Exception ex)
{
throw ex;
}
return result;
}
private bool UpdateVoucherDetails(Voucher voucher, Database db, DbTransaction pTransaction)
{
bool result = false;
try
{
DbCommand insCmd = db.GetStoredProcCommand(Constant.SP_Voucher_Insert_Voucher_Details);
db.AddInParameter(insCmd, "@sVoucherDetails", DbType.String, "VoucherDetails", DataRowVersion.Current);
db.AddInParameter(insCmd, "@iVoucherID", DbType.Int32, voucher.VoucherID);
db.AddInParameter(insCmd, "@iPOId", DbType.Int32, "POId", DataRowVersion.Current);
db.AddInParameter(insCmd, "@iGRNId", DbType.Int32, "GRNId", DataRowVersion.Current);
db.AddInParameter(insCmd, "@mAmount", DbType.Currency, "Amount", DataRowVersion.Current);
int rowcount = 0;
rowcount = db.UpdateDataSet(voucher.DsVoucherDetails, voucher.DsVoucherDetails.Tables[0].TableName, insCmd, null, null, pTransaction);
result = true;
}
catch (System.Exception ex)
{
result = false;
throw ex;
}
return result;
}
private bool UpdatePOItems(PO po, Database db, DbTransaction transaction)
{
bool result = false;
try
{
DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_PO_InsPOItems);
db.AddInParameter(insCommand, "@iPOId", DbType.Int32, po.POId);
db.AddInParameter(insCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current);
db.AddInParameter(insCommand, "@mPOItemCost", DbType.Currency, "POItemCost", DataRowVersion.Current);
db.AddInParameter(insCommand, "@iQty", DbType.Int32, "Qty", DataRowVersion.Current);
db.AddInParameter(insCommand, "@mLineCost", DbType.Currency, "LineCost", DataRowVersion.Current);
db.AddInParameter(insCommand, "@dDiscountPerUnit", DbType.Decimal, "DiscountPerUnit", DataRowVersion.Current);
DbCommand updCommand = db.GetStoredProcCommand(Constant.SP_PO_UpdPOItems);
db.AddInParameter(updCommand, "@iPOId", DbType.Int32, po.POId);
db.AddInParameter(updCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current);
db.AddInParameter(updCommand, "@mPOItemCost", DbType.Currency, "POItemCost", DataRowVersion.Current);
db.AddInParameter(updCommand, "@iQty", DbType.Int32, "Qty", DataRowVersion.Current);
db.AddInParameter(updCommand, "@mLineCost", DbType.Currency, "LineCost", DataRowVersion.Current);
db.AddInParameter(updCommand, "@dDiscountPerUnit", DbType.Decimal, "DiscountPerUnit", DataRowVersion.Current);
db.AddInParameter(updCommand, "@biPOItemId", DbType.Int64, "POItemId", DataRowVersion.Current);
DbCommand delCommand = db.GetStoredProcCommand(Constant.SP_PO_DelPOItems);
db.AddInParameter(delCommand, "@biPOItemId", DbType.Int64, "POItemId", DataRowVersion.Current);
db.UpdateDataSet(po.DsPOItems, po.DsPOItems.Tables[0].TableName, insCommand, updCommand, delCommand, transaction);
result = true;
}
catch (System.Exception ex)
{
ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool AddUser()");
throw ex;
}
return result;
}
private bool UpdateGRNItems(GRN grn, Database db, DbTransaction transaction)
{
bool result = false;
try
{
DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_GRN_InsertDetails);
db.AddInParameter(insCommand, "@iId", DbType.Int64, "Id",DataRowVersion.Current);
db.AddInParameter(insCommand, "@biGRNId", DbType.Int64, grn.GRNId);
db.AddInParameter(insCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current);
db.AddInParameter(insCommand, "@iReceivedQty", DbType.Int32, "ReceivedQty", DataRowVersion.Current);
db.AddInParameter(insCommand, "@mItemValue", DbType.Currency, "ItemValue", DataRowVersion.Current);
DbCommand delCommand = db.GetStoredProcCommand(Constant.SP_GRN_DeleteDetails);
//db.AddInParameter(insCommand, "@iId", DbType.Int64, "Id");not yet done
db.AddInParameter(delCommand, "@biGRNId", DbType.Int64, grn.GRNId);
db.AddInParameter(delCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current);
db.UpdateDataSet(grn.GRNItems, grn.GRNItems.Tables[0].TableName, insCommand, null, delCommand, transaction);
result = true;
}
catch (System.Exception ex)
{
throw ex;
}
return result;
}
private bool UpdatePRItems(PurchaseReturns pr, Database db, DbTransaction transaction)
{
bool result = false;
try
{
DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_PR_InsPRItems);
db.AddInParameter(insCommand, "@iPRId", DbType.Int32, pr.PRId);
db.AddInParameter(insCommand, "@iGRNDetailsId", DbType.Int64, "GRNDetailsId", DataRowVersion.Current);
db.AddInParameter(insCommand, "@iQty", DbType.Int32, "Qty", DataRowVersion.Current);
db.AddInParameter(insCommand, "@mUnitCost", DbType.Decimal, "UnitCost", DataRowVersion.Current);
DbCommand updCommand = db.GetStoredProcCommand(Constant.SP_PR_UpdPRItems);
db.AddInParameter(updCommand, "@iPRDetailId", DbType.Int32, "PRDetailId", DataRowVersion.Current);
db.AddInParameter(updCommand, "@mUnitCost", DbType.Currency, "UnitCost", DataRowVersion.Current);
db.AddInParameter(updCommand, "@iQty", DbType.Int32, "Qty", DataRowVersion.Current);
DbCommand delCommand = db.GetStoredProcCommand(Constant.SP_PR_DelPRItems);
db.AddInParameter(delCommand, "@biPRDetailId", DbType.Int64, "PRDetailId", DataRowVersion.Current);
db.UpdateDataSet(pr.DsReturnDetails, pr.DsReturnDetails.Tables[0].TableName, insCommand, updCommand, delCommand, transaction);
result = true;
}
catch (System.Exception ex)
{
ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "private bool UpdatePRItems(PurchaseReturns pr, Database db, DbTransaction transaction)");
throw ex;
}
return result;
}
/// <summary>
/// 以 <paramref name="insertCommand"/>、<paramref name="insertCommand"/>、<paramref name="insertCommand"/> 作为数据处理命令更新 <paramref name="dataSet"/> 中特定名称的表的数据，并返回所影响的行数。
/// </summary>
/// <param name="database">表示一个 <see cref="Microsoft.Practices.EnterpriseLibrary.Data.Database"/> 对象。</param>
/// <param name="dataSet">待更新数据的 <see cref="DataSet"/> 对象。</param>
/// <param name="tableName">指示 <paramref name="dataSet"/> 中待更新的数据表的名称。</param>
/// <param name="insertCommand">表示用于往数据表中插入数据的 <see cref="DbCommand"/> 对象。</param>
/// <param name="updateCommand">表示用于往数据表中修改数据的 <see cref="DbCommand"/> 对象。</param>
/// <param name="deleteCommand">表示用于往数据表中删除数据的 <see cref="DbCommand"/> 对象。</param>
/// <param name="updateBatchSize">该值启用或禁用批处理支持，并且指定可在一次批处理中执行的命令的数量。</param>
/// <param name="updateBehavior">一个 <see cref="UpdateBehavior"/> 值，用于控制执行 Update 操作时当出现错误时的事务处理机制。</param>
/// <returns>表示脚本命令执行受影响的行数。</returns>
public static int UpdateDataSet(Database database, DataSet dataSet, string tableName, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand, UpdateBehavior updateBehavior, int? updateBatchSize)
{
return database.UpdateDataSet(dataSet, tableName, insertCommand, updateCommand, deleteCommand, updateBehavior, updateBatchSize);
}
private bool UpdateInvoiceDetails(Invoice invoice, Database db, DbTransaction transaction)
{
bool result = true;
try
{
DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_Invoice_Add_InvoiceDetails);
db.AddInParameter(insCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current);
db.AddInParameter(insCommand, "@biQuantity", DbType.Int64, "Quantity", DataRowVersion.Current);
db.AddInParameter(insCommand, "@iInvoiceId", DbType.Int32, invoice.InvoiceId);
db.AddInParameter(insCommand, "@mPrice", DbType.Currency, "Price", DataRowVersion.Current);
db.AddInParameter(insCommand, "@tiGroupID", DbType.Int16, "GroupID", DataRowVersion.Current);
db.AddInParameter(insCommand, "@mTotalPrice", DbType.Currency, "TotalPrice", DataRowVersion.Current);
db.AddInParameter(insCommand, "@dDiscountPerUnit", DbType.Currency, "DiscountPerUnit", DataRowVersion.Current);
DbCommand updCommand = db.GetStoredProcCommand(Constant.SP_Invoice_Update_InvoiceDetails);
db.AddInParameter(updCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current);
db.AddInParameter(updCommand, "@biQuantity", DbType.Int64, "Quantity", DataRowVersion.Current);
db.AddInParameter(updCommand, "@iInvoiceId", DbType.Int32, invoice.InvoiceId);
db.AddInParameter(updCommand, "@mPrice", DbType.Currency, "Price", DataRowVersion.Current);
db.AddInParameter(updCommand, "@tiGroupID", DbType.Int16, "GroupID", DataRowVersion.Current);
db.AddInParameter(updCommand, "@mTotalPrice", DbType.Currency, "TotalPrice", DataRowVersion.Current);
DbCommand delCommand = db.GetStoredProcCommand(Constant.SP_Invoice_Delete_InvoiceDetails);
db.AddInParameter(delCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current);
db.AddInParameter(delCommand, "@iInvoiceId", DbType.Int32, invoice.InvoiceId);
db.AddInParameter(delCommand, "@tiGroupID", DbType.Int16, "GroupID", DataRowVersion.Current);
int rowcount = db.UpdateDataSet(invoice.DsInvoiceDetails, invoice.DsInvoiceDetails.Tables[0].TableName, insCommand, updCommand, delCommand,
transaction);
//Commented this because there can be instances where the line items are not edited
//if (rowcount > 0)
//{
// result = true;
//}
//else
//{
// result = false;
//}
}
catch (System.Exception ex)
{
result = false;
throw ex;
}
return result;
}
public bool InsertUpdateDelete(ReservationRoom reservationRoom, Database db, DbTransaction transaction)
{
DbCommand commandInsert = db.GetStoredProcCommand("usp_ReservationRoomInsert");
db.AddInParameter(commandInsert, "@ReservationId", DbType.Int64, reservationRoom.ReservationId);
db.AddInParameter(commandInsert, "@RoomId", DbType.Int32, "RoomId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@RatePlanId", DbType.Int32, "RatePlanId", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@Sharers", DbType.String, "Sharers", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CheckInDate", DbType.DateTime, "CheckInDate", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CheckOutDate", DbType.DateTime, "CheckOutDate", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@NumberOfAdults", DbType.Int32, "NumberOfAdults", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@NumberOfChildren", DbType.Int32, "NumberOfChildren", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@NumberOfInfant", DbType.Int32, "NumberOfInfant", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@Days", DbType.Decimal, "Days", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@Amount", DbType.Decimal, "Amount", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@Rate", DbType.Decimal, "Rate", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@CreatedUser", DbType.Decimal, "CreatedUser", DataRowVersion.Current);
db.AddInParameter(commandInsert, "@StatusId", DbType.Int32, "StatusId", DataRowVersion.Current);
DbCommand commandUpdate = db.GetStoredProcCommand("usp_ReservationRoomUpdate");
db.AddInParameter(commandUpdate, "@ReservationReservationId", DbType.Int32, "ReservationReservationId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@RoomId", DbType.Int32, "RoomId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@RatePlanId", DbType.Int32, "RatePlanId", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Sharers", DbType.String, "Sharers", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@CheckInDate", DbType.DateTime, "CheckInDate", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@CheckOutDate", DbType.DateTime, "CheckOutDate", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@NumberOfAdults", DbType.Int32, "NumberOfAdults", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@NumberOfChildren", DbType.Int32, "NumberOfChildren", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@NumberOfInfant", DbType.Int32, "NumberOfInfant", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Days", DbType.Decimal, "Days", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Amount", DbType.Decimal, "Amount", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@Rate", DbType.Decimal, "Rate", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@UpdatedUser", DbType.Int32, "UpdatedUser", DataRowVersion.Current);
db.AddInParameter(commandUpdate, "@StatusId", DbType.Int32, "StatusId", DataRowVersion.Current);
DbCommand commandDelete = db.GetStoredProcCommand("usp_ReservationRoomDelete");
db.AddInParameter(commandDelete, "@ReservationRoomId", DbType.Int32, "ReservationRoomId", DataRowVersion.Current);
db.UpdateDataSet(reservationRoom.ReservationRoomList, reservationRoom.ReservationRoomList.Tables[0].TableName, commandInsert, commandUpdate, commandDelete, transaction);
return true;
}
private bool UpdateGroupItems(Group group, Database db, DbTransaction transaction)
{
bool result = false;
try
{
DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_Groups_InsGroupItems);
db.AddInParameter(insCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current);
db.AddInParameter(insCommand, "@tiIGroupId", DbType.Int16, group.GroupId);
DbCommand DelCommand = db.GetStoredProcCommand(Constant.SP_BrandCategories_DelBrandCategories);
db.AddInParameter(DelCommand, "@iItemId", DbType.Int32, "ItemId", DataRowVersion.Current);
db.AddInParameter(DelCommand, "@tiIGroupId", DbType.Int16, group.GroupId);
db.UpdateDataSet(group.GroupItems, group.GroupItems.Tables[0].TableName, insCommand, null, DelCommand, transaction);
result = true;
}
catch (System.Exception ex)
{
throw ex;
}
return result;
}
private bool UpdateGetPassDetails(GetPass getPass, Database db, DbTransaction transaction)
{
bool result = false;
try
{
DbCommand insCommand = db.GetStoredProcCommand(Constant.SP_GetPas_Add_Details);
db.AddInParameter(insCommand, "@biGPId", DbType.Int64, getPass.GPId);
db.AddInParameter(insCommand, "@biInvDetID", DbType.Int64, "InvDetID", DataRowVersion.Current);
db.AddInParameter(insCommand, "@biQty", DbType.Int64, "Qty", DataRowVersion.Current);
DbCommand delCommand = db.GetStoredProcCommand(Constant.SP_GetPass_Delete_GetPassDetails);
db.AddInParameter(delCommand, "@biGPId", DbType.Int64, getPass.GPId);
db.AddInParameter(delCommand, "@biInvDetID", DbType.Int64, "InvDetID", DataRowVersion.Current);
db.AddInParameter(delCommand, "@biQty", DbType.Int64, "Qty", DataRowVersion.Current);
db.UpdateDataSet(getPass.DsGatePassDetails, getPass.DsGatePassDetails.Tables[0].TableName, insCommand, null, delCommand,
transaction);
result = true;
}
catch (System.Exception ex)
{
result = false;
throw ex;
}
return result;
}
