public async Task <IEnumerable <Stock_TransactionDTO> > GetStock_Tran_list() { using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString)) { connection.Open(); var sql = $"select tns.*,src.*, dest.*, stck.*,sr_itm.* from DTH.tbl_stock_trans tns " + "Inner join DTH.tbl_dealers src on tns.trans_source_dealer_id = src.Idbase " + "Inner join DTH.tbl_dealers dest on tns.trans_dest_dealer_id = dest.Idbase " + "Inner join DTH.tbl_master_stock stck on tns.trans_item_stock_id = stck.Idbase " + "Inner join DTH.tbl_master_serialitem sr_itm on tns.trans_item_ser_id = sr_itm.Idbase"; var items = await connection.QueryAsync <Stock_TransactionDTO>( sql, new[] { typeof(Stock_TransactionDTO), typeof(DealerDTO), typeof(DealerDTO), typeof(Master_StockDTO), typeof(master_serialitemDTO) } , obj => { Stock_TransactionDTO _dto = obj[0] as Stock_TransactionDTO; _dto.trans_source_dealer = obj[1] as DealerDTO; _dto.trans_dest_dealer = obj[2] as DealerDTO; _dto.trans_item_stock = obj[3] as Master_StockDTO; _dto.trans_item_ser = obj[4] as master_serialitemDTO; return(_dto); }, splitOn : "Idbase" ); return(items); } }
public async Task <Stock_TransactionDTO> GetStock_Tran(Int64 id) { using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString)) { connection.Open(); var sql = $"select tns.*,src.*, dest.*, stck.*,sr_itm.* from DTH.tbl_stock_trans tns " + "Inner join DTH.tbl_dealers src on tns.trans_source_dealer_id = src.Idbase " + "Inner join DTH.tbl_dealers dest on tns.trans_dest_dealer_id = dest.Idbase " + "Inner join DTH.tbl_master_stock stck on tns.trans_item_stock_id = stck.Idbase " + "Inner join DTH.tbl_master_serialitem sr_itm on tns.trans_item_ser_id = sr_itm.Idbase WHERE tns.Idbase={id}"; //Func<Stock_TransactionDTO, DealerDTO, DealerDTO> p = (oi, i, v) => // { // oi = oi; oi.trans_source_dealer = i; oi.trans_dest_dealer = v; // return oi; // }; var items = await connection.QueryAsync <Stock_TransactionDTO>( sql, new[] { typeof(Stock_TransactionDTO), typeof(DealerDTO), typeof(DealerDTO), typeof(Master_StockDTO), typeof(master_serialitemDTO) } , obj => { Stock_TransactionDTO _dto = obj[0] as Stock_TransactionDTO; _dto.trans_source_dealer = obj[1] as DealerDTO; _dto.trans_dest_dealer = obj[2] as DealerDTO; _dto.trans_item_stock = obj[3] as Master_StockDTO; _dto.trans_item_ser = obj[4] as master_serialitemDTO; return(_dto); }, splitOn : "Idbase" ); return(items.FirstOrDefault()); //var result = await connection.QueryAsync<Stock_TransactionDTO>( // @"select * from DTH.tbl_stock_trans // WHERE Idbase=@id" // , new { id } // ); //if (result.AsList().Count == 0) // throw new KeyNotFoundException(); //return result.FirstOrDefault(); } }