public async Task <List <Training> > GetTrainingByUser(string createdBy) { await connectionProvider.EstablishConnection(); var trainings = connectionProvider.Query <Training>("SELECT id,name,description,datetime,createdBy FROM training where createdBy=@createdBy", new { @createdBy = createdBy }); connectionProvider.Close(); return(trainings.ToList()); }
public static async Task <IEnumerable <dynamic> > GetViewColumns(string ViewName, string ViewKey) { IEnumerable <dynamic> columns = null; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { string UserId = ApiService.UserId; try { // get columns connection.Open(); int recCnt = await connection.ExecuteScalarAsync <int>(qryViewMaster.selectColDefCnt, new { ViewName, UserId }); if (recCnt > 0) { columns = await connection.QueryAsync(qryViewMaster.selectViewColumns, new { ViewName, UserId }); } else { columns = await connection.QueryAsync(qryViewMaster.selectDefaultColumns, new { ViewName }); } } catch (Exception) { throw; } finally { connection.Close(); } } return(columns); }
public static bool SaveGinDetail(whgi2 ginDetail) { int afRecCnt = 0; string storeProcName; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); int ginDetailCnt = connection.ExecuteScalar <int>(qryGoodsIssueNote.selectGinDetailCount, new { TrxNo = ginDetail.TrxNo, LineItemNo = ginDetail.LineItemNo }); if (ginDetailCnt > 0) { storeProcName = qryGoodsIssueNote.updateGinDetail; } else { storeProcName = qryGoodsIssueNote.insertGinDetail; } var param = connection.GetStoreProcParams(storeProcName, ginDetail); afRecCnt = connection.Execute(storeProcName, param, null, null, CommandType.StoredProcedure); } catch (Exception) { throw; } finally { connection.Close(); } } return(afRecCnt > 0 ? true : false); }
public static bool DeleteGin(int TrxNo, int Type) { int afRecCnt = 0; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); // delete gin afRecCnt = connection.Execute(qryGoodsIssueNote.deleteGin, new { TrxNo, UpdateBy = ApiService.UserId, Type }, null, null, CommandType.StoredProcedure); } catch (Exception) { throw; } finally { connection.Close(); } } return(afRecCnt > 0 ? true : false); }
public static int SaveBarCodeItemDetail(whbi2 itemDetail) { using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); itemDetail.LineItemNo = connection.ExecuteScalar <int>(qryBarCodeItem.SelectNewLineItemNo, new { TrxNo = itemDetail.TrxNo, TablePrefix = itemDetail.TablePrefix }); itemDetail.WorkStation = ApiService.HostName; itemDetail.CreateBy = ApiService.UserId; itemDetail.CreateDateTime = ApiService.ClientDate; connection.Execute(qryBarCodeItem.InsertBarcodeItemDetail, itemDetail); } catch (Exception) { throw; } finally { connection.Close(); } } return(itemDetail.LineItemNo); }
public static int DeleteGrnDetail(int TrxNo, int LineItemNo) { int afRecCnt = 0; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); // delete grn detail afRecCnt += connection.Execute(qryGoodsReceiptNote.deleteGrnDetail, new { TrxNo, LineItemNo, UpdateBy = ApiService.UserId }, null, null, CommandType.StoredProcedure); } catch (Exception) { throw; } finally { connection.Close(); } } var myGrn = GetGrn(TrxNo); InventoryHelper.DeleteInvByBatch(myGrn.GoodsReceiptNoteNo, LineItemNo, myGrn.WarehouseCode); return(afRecCnt); }
public static int savePickList(whpl1 pickList) { int trxNo = 0; string storeProcName; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { if (pickList.TrxNo > 0) { // update storeProcName = qryPickList.updatePickList; } else { // insert storeProcName = qryPickList.insertPickList; } connection.Open(); var param = connection.GetStoreProcParams(storeProcName, pickList); trxNo = connection.ExecuteScalar <int>(storeProcName, param, null, null, System.Data.CommandType.StoredProcedure); } catch (Exception) { throw; } finally { connection.Close(); } } return(trxNo); }
/// <summary> /// The dispose. /// </summary> /// <param name="disposing"> /// The disposing. /// </param> protected virtual void Dispose(bool disposing) { if (!WasDisposed && disposing && _connection != null) { ConnectionProvider.Close(_connection); } WasDisposed = true; }
public static int SaveGrnDetails(List <whgr2> GrnDetails) { int afRecCnt = 0; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); // insert warehouse details using (IDbTransaction transactionScope = connection.BeginTransaction(IsolationLevel.Serializable)) { try { foreach (var grnDetail in GrnDetails) { int grnDetailCnt = connection.ExecuteScalar <int>(qryGoodsReceiptNote.selectGrnDetailCount, new { TrxNo = grnDetail.TrxNo, LineItemNo = grnDetail.LineItemNo }, transactionScope, null, CommandType.Text); string storeProcName; if (grnDetailCnt <= 0) { // set insert store procedure storeProcName = qryGoodsReceiptNote.insertGrnDetail; } else { // set update store procedure storeProcName = qryGoodsReceiptNote.updateGrnDetail; } var param = connection.GetStoreProcParams(storeProcName, grnDetail, transactionScope); afRecCnt += connection.Execute(storeProcName, param, transactionScope, null, CommandType.StoredProcedure); } transactionScope.Commit(); } catch (Exception) { transactionScope.Rollback(); throw; } } } catch (Exception) { throw; } finally { connection.Close(); } } return(afRecCnt); }
public static bool SavePoDetails(IEnumerable <whpo2> poDetails) { int afRecCnt = 0; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); using (IDbTransaction tranScope = connection.BeginTransaction(IsolationLevel.Serializable)) { try { foreach (var poDetail in poDetails) { int poDetailCnt = connection.ExecuteScalar <int>(qryPurchaseOrder.selectPoDetailCount, new { TrxNo = poDetail.TrxNo, LineItemNo = poDetail.LineItemNo }, tranScope); string storeProcName; if (poDetailCnt <= 0) { // set insert store procedure storeProcName = qryPurchaseOrder.insertPoDetail; } else { // set update store procedure storeProcName = qryPurchaseOrder.updatePoDetail; } var param = connection.GetStoreProcParams(storeProcName, poDetail, tranScope); afRecCnt += connection.Execute(storeProcName, param, tranScope, null, CommandType.StoredProcedure); } tranScope.Commit(); } catch (Exception) { tranScope.Rollback(); throw; } } } catch (Exception) { throw; } finally { connection.Close(); } } return(afRecCnt > 0 ? true : false); }
public static bool SaveToInv(int TrxNo) { bool isDone = false; // configure automapper var config = new MapperConfiguration(cfg => cfg.CreateMap <whgi2, whiv1>() .ForMember(dest => dest.BatchLineItemNo, opt => opt.MapFrom(src => src.LineItemNo)) .ForMember(dest => dest.TrxNo, opt => opt.Ignore()) ); var mapper = config.CreateMapper(); // get gin, gindetails whgi1 gin = GetGin(TrxNo); IEnumerable <whgi2> ginDetails = GetGinDetails(TrxNo); using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); foreach (var ginDetail in ginDetails) { // set minus values ginDetail.Length = -1 * ginDetail.Length; ginDetail.Width = -1 * ginDetail.Width; ginDetail.Height = -1 * ginDetail.Height; ginDetail.Weight = -1 * ginDetail.Weight; ginDetail.Volume = -1 * ginDetail.Volume; ginDetail.SpaceArea = -1 * ginDetail.SpaceArea; ginDetail.Qty = -1 * ginDetail.Qty; // map gin to inv whiv1 myInv = mapper.Map <whiv1>(ginDetail); myInv.BatchNo = gin.GoodsIssueNoteNo; myInv.WarehouseCode = gin.WarehouseCode; myInv.WorkStation = ApiService.HostName; myInv.CreateBy = ApiService.UserId; myInv.CreateDateTime = ApiService.ClientDate; myInv.UpdateBy = ApiService.UserId; myInv.UpdateDateTime = ApiService.ClientDate; // save to inventory isDone = InventoryHelper.SaveInv(myInv); } } catch (Exception) { throw; } finally { connection.Close(); } } return(isDone); }
public static whpo2 GetPoDetail(int TrxNo, int LineItemNo) { whpo2 poDetail = null; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); poDetail = connection.QuerySingleOrDefault <whpo2>(qryPurchaseOrder.selectPoDetail, new { TrxNo, LineItemNo }); } catch (Exception) { throw; } finally { connection.Close(); } } return(poDetail); }
public static IEnumerable <whpo2> GetPoDetails(int TrxNo) { IEnumerable <whpo2> myPoDetails = null; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); myPoDetails = connection.Query <whpo2>(qryPurchaseOrder.selectPoDetails, new { TrxNo }); } catch (Exception) { throw; } finally { connection.Close(); } } return(myPoDetails); }
public static string GetNewPoNo(DateTime PoDate) { string newPoNo; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); newPoNo = connection.ExecuteScalar <string>(qryPurchaseOrder.selectNewPoNo, new { PoDate }); } catch (Exception) { throw; } finally { connection.Close(); } } return(newPoNo); }
public static dynamic GetPutAwayItem(string WarehouseCode, string SupplierCode) { dynamic myPutAwayItems = null; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); myPutAwayItems = connection.Query(qryGoodsReceiptNote.selectPutAwayItem, new { WarehouseCode, SupplierCode }); } catch (Exception) { throw; } finally { connection.Close(); } } return(myPutAwayItems); }
public static whgi1 GetGin(int TrxNo) { whgi1 myGin = null; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); myGin = connection.QuerySingleOrDefault <whgi1>(qryGoodsIssueNote.selectGin, new { TrxNo }); } catch (Exception) { throw; } finally { connection.Close(); } } return(myGin); }
public static string GetNewGinNo(DateTime IssueDate) { string newGinNo; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); newGinNo = connection.ExecuteScalar <string>(qryGoodsIssueNote.selectNewGinNo, new { IssueDate }); } catch (Exception) { throw; } finally { connection.Close(); } } return(newGinNo); }
public static whpo1 GetPo(int TrxNo) { whpo1 myPo = null; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); myPo = connection.QuerySingleOrDefault <whpo1>(qryPurchaseOrder.selectPo, new { TrxNo }); } catch (Exception) { throw; } finally { connection.Close(); } } return(myPo); }
/// <summary> /// The dispose. /// </summary> /// <param name="disposing"> /// The disposing. /// </param> protected virtual void Dispose(bool disposing) { if (!_disposed && disposing) { if (_connection != null) { ConnectionProvider.Close(_connection); } if (Current == this) { Current = null; } } _disposed = true; }
public static whpl2 getPickListDetail(int TrxNo, int LineItemNo) { whpl2 pickListDetail; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); pickListDetail = connection.QuerySingle <whpl2>(qryPickList.selectPickListDetail, new { TrxNo, LineItemNo }); } catch (Exception) { throw; } finally { connection.Close(); } } return(pickListDetail); }
public static string getNewPickNo(DateTime PickDate) { string myNewPickNo; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); myNewPickNo = connection.ExecuteScalar <string>(qryPickList.selectNewPickNo, new { PickDate }); } catch (Exception) { throw; } finally { connection.Close(); } } return(myNewPickNo); }
public static whpl1 getPickList(int TrxNo) { whpl1 pickList; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); pickList = connection.QuerySingleOrDefault <whpl1>(qryPickList.selectPickList, new { TrxNo }); } catch (Exception) { throw; } finally { connection.Close(); } } return(pickList); }
public static IEnumerable <whpl2> getPickListDetails(int TrxNo) { IEnumerable <whpl2> pickListDetails; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); pickListDetails = connection.Query <whpl2>(qryPickList.selectPickListDetails, new { TrxNo }); } catch (Exception) { throw; } finally { connection.Close(); } } return(pickListDetails); }
public static bool DeleteGinDetail(int TrxNo, int LineItemNo) { int afRecCnt = 0; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); afRecCnt = connection.Execute(qryGoodsIssueNote.deleteGinDetail, new { TrxNo, LineItemNo }); } catch (Exception) { throw; } finally { connection.Close(); } } return(afRecCnt > 0 ? true : false); }
public static IEnumerable <whgi2> GetGinDetails(int TrxNo) { IEnumerable <whgi2> ginDetails = null; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); ginDetails = connection.Query <whgi2>(qryGoodsIssueNote.selectGinDetails, new { TrxNo }); } catch (Exception) { throw; } finally { connection.Close(); } } return(ginDetails); }
public static whgi2 GetGinDetail(int TrxNo, int LineItemNo) { whgi2 ginDetail = null; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); ginDetail = connection.QuerySingleOrDefault <whgi2>(qryGoodsIssueNote.selectGinDetail, new { TrxNo, LineItemNo }); } catch (Exception) { throw; } finally { connection.Close(); } } return(ginDetail); }
public static List <whgr2> GetGrnDetails(int TrxNo) { List <whgr2> myGrnDetails = null; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); myGrnDetails = connection.Query <whgr2>(qryGoodsReceiptNote.selectGrnDetails, new { TrxNo }).ToList(); } catch (Exception) { throw; } finally { connection.Close(); } } return(myGrnDetails); }
public static bool DeleteBarcodeItemDetail(int TrxNo, string TablePrefix, int LineItemNo) { int afRecCnt = 0; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); afRecCnt = connection.Execute(qryBarCodeItem.DeleteBarcodeItemDetail, new { TrxNo, TablePrefix, LineItemNo }); } catch (Exception) { throw; } finally { connection.Close(); } } return(afRecCnt > 0 ? true : false); }
public static int SaveGin(whgi1 gin) { int trxNo = 0; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { connection.Open(); string storeProcName; if (gin.TrxNo <= 0) { // set audit values gin.WorkStation = ApiService.HostName; gin.CreateBy = ApiService.UserId; gin.CreateDateTime = ApiService.ClientDate; gin.UpdateBy = ApiService.UserId; gin.UpdateDateTime = ApiService.ClientDate; // set insert store procedure storeProcName = qryGoodsIssueNote.insertGin; } else { // set audit values gin.WorkStation = ApiService.HostName; gin.UpdateBy = ApiService.UserId; gin.UpdateDateTime = ApiService.ClientDate; // set update store procedure storeProcName = qryGoodsIssueNote.updateGin; } var myParam = connection.GetStoreProcParams(storeProcName, gin); trxNo = connection.ExecuteScalar <int>(storeProcName, myParam, null, null, CommandType.StoredProcedure); } catch (Exception) { throw; } finally { connection.Close(); } } return(trxNo); }
public static int SavePo(whpo1 po) { int trxNo = 0; using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection()) { try { string storeProcName; if (po.TrxNo <= 0) { // set audit values po.WorkStation = ApiService.HostName; po.CreateBy = ApiService.UserId; po.CreateDateTime = ApiService.ClientDate; po.UpdateBy = ApiService.UserId; po.UpdateDateTime = ApiService.ClientDate; // set insert store procedure storeProcName = qryPurchaseOrder.insertPo; } else { // set audit values po.WorkStation = ApiService.HostName; po.UpdateBy = ApiService.UserId; po.UpdateDateTime = ApiService.ClientDate; // set update store procedure storeProcName = qryPurchaseOrder.updatePo; } connection.Open(); var param = connection.GetStoreProcParams(storeProcName, po); trxNo = connection.ExecuteScalar <int>(storeProcName, param, null, null, CommandType.StoredProcedure); } catch (Exception) { throw; } finally { connection.Close(); } } return(trxNo); }
public void ServerDies() { TcpListener listener = new TcpListener(Dns.Resolve("localhost").AddressList[0], 6667); listener.Start(); ConnectionProvider provider = new ConnectionProvider("localhost", 6667); TcpClient client = listener.AcceptTcpClient(); try { client.Close(); provider.SendMessage("hello"); provider.SendMessage("world"); Assert.Fail(); } catch(ConnectionProviderException exception) { Assert.AreEqual("Error writting message to server.", exception.Message); } finally { listener.Stop(); provider.Close(); } }