/// <summary> /// 获取操作model /// </summary> /// <returns></returns> public async Task <List <ActionCharacteristicsModel> > GetOperateModels() { using (MySql.Data.MySqlClient.MySqlConnection conn = MySqlHelper.GetConnection()) { return((await conn.QueryAsync <ActionCharacteristicsModel>("select * from t_utility_operate")).AsList()); } }
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); } }
private async Task <IEnumerable <dynamic> > GetDbUsersAsync() { using (var connection = new MySql.Data.MySqlClient.MySqlConnection("Server=127.0.0.1;Database=demo;Uid=root;Pwd=admin;SslMode=None;")) { return(await connection.QueryAsync("SELECT * FROM user")); } }
/// <summary> /// 创建Sql /// </summary> /// <param name="dateSql">时间sql</param> /// <param name="merchantGuid">商户GUID</param> /// <param name="merchantName">商户名称</param> /// <param name="startTime">开始时间</param> /// <param name="endTime">结束时间</param> /// <returns></returns> public async Task <List <ProjectSoldReportRankingAsyncResponseDto> > ProjectSoldReportRankingAsync(ProjectSoldReportRankingAsyncRequestDto requestDto) { string sqlstring = $@"SELECT a.merchant_guid, b.product_guid, b.product_name, sum( b.product_count ) AS SoldCount FROM t_mall_order a, t_mall_order_detail b WHERE ( ( a.payment_date >= @StartTime AND a.payment_date <= @EndTime ) OR ( a.creation_date >= @StartTime AND a.creation_date <= @EndTime ) ) AND a.merchant_guid = @MerchantGuid AND a.order_status in ('Received', 'Completed', 'Shipped' ) AND a.order_guid = b.order_guid GROUP BY b.product_guid, b.product_name ORDER BY SoldCount DESC LIMIT 10 "; using (MySql.Data.MySqlClient.MySqlConnection conn = MySqlHelper.GetConnection()) { return((await conn.QueryAsync <ProjectSoldReportRankingAsyncResponseDto>(sqlstring, new { requestDto.MerchantGuid, requestDto.StartTime, requestDto.EndTime }))?.AsList()); } }
private static async Task <string> GetAnyGridFromSqlQuery(string query) { string json = string.Empty; var dd = new DynamicData(); var rows = new List <AnyModel>(); //Send Query To Database using (var conn = new MySql.Data.MySqlClient.MySqlConnection(DbHelper.Conn())) { await conn.OpenAsync(); rows = (await conn.QueryAsync <AnyModel>(query)).ToList(); int count = 0; dd.COLUMNS.Add(new Column("Id")); dd.COLUMNS.Add(new Column("Title")); dd.COLUMNS.Add(new Column("City")); dd.COLUMNS.Add(new Column("Singer")); dd.COLUMNS.Add(new Column("Nationality")); dd.COLUMNS.Add(new Column("Year")); foreach (var v in rows) { var d = new List <string>(); d.Add(v.Id.ToString()); d.Add(v.Title); d.Add(v.City); d.Add(v.Singer); d.Add(v.Nationality); d.Add(v.Year.ToString()); dd.DATA.Add(d); count++; } json = JsonConvert.SerializeObject(dd); } return(json); }
public async static Task <IEnumerable <int> > NoOfLaps(BoatsTidy boat, Calendar cal) { using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal())) { return(await connection.QueryAsync <int>("select max(racelap) from races where name = @name and " + "summary = @summary", new { name = boat.Name, summary = cal.Summary })); } }
public async static Task <IEnumerable <int> > NoOfLaps(Calendar cal) { using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal())) { return(await connection.QueryAsync <int>("select max(racelap) from races where " + "summary = @summary and eventStart = @eventStart", new { summary = cal.Summary, eventStart = cal.DateTime })); } }
private static async Task <ICollection <string> > GetListFromSqlQuery(string query) { var dropDownList = new List <string>(); //Send Query To Database using (var conn = new MySql.Data.MySqlClient.MySqlConnection(DbHelper.Conn())) { await conn.OpenAsync(); dropDownList = (await conn.QueryAsync <string>(query)).ToList(); } return(dropDownList); }
private static async Task <List <AnyModel> > Create_CoinInterfaceView(string query) { var rows = new List <AnyModel>(); //Send Query To Database using (var conn = new MySql.Data.MySqlClient.MySqlConnection(DbHelper.Conn())) { await conn.OpenAsync(); rows = (await conn.QueryAsync <AnyModel>(query)).ToList(); } return(rows); }
/// <summary> /// 创建Sql /// </summary> /// <param name="dateSql">时间sql</param> /// <param name="merchantGuid">商户GUID</param> /// <param name="merchantName">商户名称</param> /// <param name="startTime">开始时间</param> /// <param name="endTime">结束时间</param> /// <returns></returns> private async Task <List <MerchantFlowingReportResponseDto> > SelectFlowingReportAsync(string dateSql, string merchantGuid, string merchantName, DateTime startTime, DateTime endTime) { string sqlstring = $@"SELECT m.merchant_guid AS MerchantGuid, m.merchant_name AS MerchantName, m.date AS Date, mfs.product_count AS ProductCount, mfs.amount AS Amount, mfs.order_count AS OrderCount, ccs.consumption_count AS ConsumptionCount FROM ({dateSql}) AS m LEFT JOIN( SELECT o.merchant_guid, DATE_FORMAT( o.payment_date, '%Y-%m-%d' ) AS date, SUM( o.product_count ) AS product_count, COUNT( o.order_guid ) AS order_count, Sum( o.paid_amount ) AS amount FROM t_mall_order AS o WHERE ( o.payment_date >=@startTime AND o.payment_date <=@endTime ) AND o.order_status IN ( 'Received', 'Completed', 'Shipped' ) AND o.merchant_guid = @merchantGuid GROUP BY o.merchant_guid, DATE_FORMAT( o.payment_date, '%Y-%m-%d' ) ) AS mfs ON mfs.merchant_guid=m.merchant_guid and mfs.date=m.date LEFT JOIN( SELECT cc.merchant_guid, DATE_FORMAT(cc.consumption_end_date, '%Y-%m-%d') AS date, COUNT(cc.consumption_guid) AS consumption_count FROM t_consumer_consumption cc WHERE cc.merchant_guid=@merchantGuid AND cc.consumption_status in ('Completed' ) AND cc.consumption_end_date >= @startTime AND cc.consumption_end_date <= @endTime GROUP BY cc.merchant_guid,date ) AS ccs ON ccs.merchant_guid=m.merchant_guid and ccs.date=m.date ORDER BY m.date ";// 'Booked','Arrive', using (MySql.Data.MySqlClient.MySqlConnection conn = MySqlHelper.GetConnection()) { return((await conn.QueryAsync <MerchantFlowingReportResponseDto>(sqlstring, new { merchantGuid, merchantName, startTime, endTime }))?.AsList()); } }
public async static Task <IEnumerable <BoatsTidy> > GetRacersReel(Calendar cal) { using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal())) { return(await connection.QueryAsync <BoatsTidy>("select name, boat, boatNumber, crew, py, notes from signonlists where" + "summary = @summary and dateTime = @dateTime and sailingClub = @sailingClub" , new { summary = cal.Summary, dateTime = cal.DateTime, sailingClub = "Whitefriars Sailing Club" })); } }
/* * /// <summary> * /// Gets the list of distinct boats which have been sailed before. * /// </summary> * /// <returns>Array of all boats used before.</returns> * public static String[] GetBoats() * { * * using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal())) * { * * // Query. * return connection.Query<string>("call returnboats").Distinct().ToArray(); * * } * * }*/ /// <summary> /// Adds a new boat/person /// </summary> /// <param name="boat">Boat data to add.</param> public async static Task <IEnumerable <dynamic> > SetNewFullBoat(Boats boat) { using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal())) { //Appends together the query, stops SQL injection. // Query. return(await connection.QueryAsync("insert into fulllist value (@name, @boatName, " + "@boatNumber, @py, @sailingClub)", new { name = boat.Name, boatName = boat.BoatName, boatNumber = boat.BoatNumber, py = boat.Py, sailingClub = "Whitefriars Sailing Club" })); } }
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(); } }
public async Task <IEnumerable <DealerDTO> > GetDealers() { using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString)) { connection.Open(); var result = await connection.QueryAsync <DealerDTO>( @"select * from DTH.tbl_dealers "); if (result.AsList().Count == 0) { throw new KeyNotFoundException(); } return(result); } }
public async Task <CustomerDTO> GetCustomersAsync() { using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString)) { connection.Open(); var result = await connection.QueryAsync <dynamic>( @"select * from DTH.tblCustomer "); if (result.AsList().Count == 0) { throw new KeyNotFoundException(); } return(MapCustomerItems(result)); } }
public async Task <IEnumerable <Master_StockDTO> > GetMaster_StockItems() { using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString)) { connection.Open(); var result = await connection.QueryAsync <Master_StockDTO>( @"SELECT Idbase, stock_name, if(stock_type = 1001, 'Serial', 'NonSerial') as stock_type, stock_unit, stock_cost, stock_detail FROM DTH.tbl_master_stock"); if (result.AsList().Count == 0) { throw new KeyNotFoundException(); } return(result); } }
public async Task <dynamic> GetProjectsByUserId(int userId) { using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString)) { connection.Open(); var result = await connection.QueryAsync <dynamic>( @"select *from project where userid = @userId" , new { userId } ); if (result.AsList().Count == 0) { throw new KeyNotFoundException(); } return(result); } }
public async Task <List <T> > GetAsync <T>(string connection, string sql, object parameters) { var result = new List <T>(); //var test = new MySql.Data.MySqlClient() using (var c = new MySqlData.MySqlClient.MySqlConnection(connection)) { _log.LogTrace <MySqlQuery>($"SELECT: {sql}. Parameters: {JsonConvert.SerializeObject(parameters)}"); await c.OpenAsync(); var query = await c.QueryAsync <T>(sql, parameters); result = query.ToList(); await c.CloseAsync(); } return(result.ToList()); }
public async Task <Master_StockDTO> GetMaster_StockItem(Int64 id) { using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString)) { connection.Open(); var result = await connection.QueryAsync <Master_StockDTO>( @"select * from DTH.tbl_master_stock WHERE Idbase=@id" , new { id } ); if (result.AsList().Count == 0) { throw new KeyNotFoundException(); } return(result.FirstOrDefault()); } }
public async Task <DealerDTO> GetDealer(int id) { using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString)) { connection.Open(); var result = await connection.QueryAsync <DealerDTO>( @"select * from DTH.tbl_dealers WHERE Idbase=@id" , new { id } ); if (result.AsList().Count == 0) { throw new KeyNotFoundException(); } return(result.FirstOrDefault()); } }
public async Task <Customer> GetCustomerAsync(int id) { using (var connection = new MySql.Data.MySqlClient.MySqlConnection(_connectionString)) { connection.Open(); var result = await connection.QueryAsync <dynamic>( @"select * from DTH.tblCustomer WHERE o.Idbase=@id" , new { id } ); if (result.AsList().Count == 0) { throw new KeyNotFoundException(); } return((Customer)result); } }
/// <summary> /// 获取积分规则Model /// </summary> /// <param name="userType">用户类型</param> /// <param name="actionCharacteristics">行为特征枚举</param> /// <returns></returns> public async Task <List <GetActionRulesResponseDto> > GetScoreRulesModel(UserType userType, ActionCharacteristicsEnum actionCharacteristics = ActionCharacteristicsEnum.Action) { string sqlstring = $@"select sr.rules_guid AS RulesGuid, ac.action_characteristics_code AS ActionCharacteristicsCode, ac.action_characteristics_name AS ActionCharacteristicsName, ac.action_characteristics_type AS ActionCharacteristicsType from t_utility_score_rules as sr inner join t_utility_user_action as ua on sr.user_action_guid=ua.user_action_guid inner join t_utility_action_characteristics as ac on ac.action_characteristics_guid=ua.action_guid where ac.action_characteristics_type=@action_characteristics_type and ua.user_type_guid=@user_type_guid"; DynamicParameters parameters = new DynamicParameters(); parameters.Add("action_characteristics_type", actionCharacteristics.ToString(), System.Data.DbType.String); parameters.Add("user_type_guid", userType.ToString(), System.Data.DbType.String); using (MySql.Data.MySqlClient.MySqlConnection conn = MySqlHelper.GetConnection()) { return((await conn.QueryAsync <GetActionRulesResponseDto>(sqlstring, parameters)).AsList()); } }