private async Task <IDbConnection> OpenDbConnectionAsync() { var conn = new MySql.Data.MySqlClient.MySqlConnection(_eventBusMySQLOptions.ConnectionString); await conn.OpenAsync(); return(conn); }
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); }
/// <summary> /// Gets a fresh, open and ready-to-use connection wrapper /// </summary> public async Task <MySqlConnection> GetConnection() { var connection = new MySqlData.MySqlClient.MySqlConnection(_connectionString); await connection.OpenAsync(); var currentTransaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); return(new MySqlConnection(connection, currentTransaction)); }
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); }
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); }
public async Task ExecuteAsync(string connection, string sql, object parameters) { using (var c = new MySqlData.MySqlClient.MySqlConnection(connection)) { _log.LogTrace <MySqlQuery>($"EXECUTE: {sql}. Parameters: {JsonConvert.SerializeObject(parameters)}"); await c.OpenAsync(); await c.ExecuteAsync(sql, parameters); await c.CloseAsync(); } }
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline. public void Configure(IApplicationBuilder app, IWebHostEnvironment env) { if (env.IsDevelopment()) { app.UseDeveloperExceptionPage(); } else { app.UseExceptionHandler("/Home/Error"); // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts. app.UseHsts(); } app.UseHttpsRedirection(); app.UseStaticFiles(); app.UseRouting(); app.UseAuthorization(); app.UseEndpoints(endpoints => { /* Часть кода из шаблона * endpoints.MapControllerRoute( * name: "default", * pattern: "{controller=Home}/{action=Index}/{id?}"); */ endpoints.MapGet("/", async context => { try { using (var connection = new MySql.Data.MySqlClient.MySqlConnection("server=127.0.0.1;uid=root;pwd=root;database=dbo")) { await connection.OpenAsync(); var command = connection.CreateCommand(); command.CommandText = "select * from article"; var reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { await context.Response.WriteAsync(reader["id"].ToString() + " " + reader["title"] + " " + reader["body"]); } } } catch (Exception e) { await context.Response.WriteAsync(e.ToString()); } }); }); }
public async Task <T> ExecuteScalarAsync <T>(string connection, string sql, object parameters) { T result = default; using (var c = new MySqlData.MySqlClient.MySqlConnection(connection)) { _log.LogTrace <MySqlQuery>($"EXECUTE: {sql}. Parameters: {JsonConvert.SerializeObject(parameters)}"); await c.OpenAsync(); result = await c.ExecuteScalarAsync <T>(sql, parameters); await c.CloseAsync(); } return(result); }
public async Task <IActionResult> TestAsync() { IList <string> data = new List <string>(); using (var connection = new MySql.Data.MySqlClient.MySqlConnection("server=127.0.0.1;uid=root;pwd=root;database=dbo")) { await connection.OpenAsync(); var command = connection.CreateCommand(); command.CommandText = "select * from article"; var reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { data.Add(reader["id"].ToString() + " " + reader["title"] + " " + reader["body"]); } } return(View(data)); }
public async Task <HealthCheckResult> Run() { HealthCheckStatus status; string error; try { bool hasResult = false; using (var conn = new MySql.Data.MySqlClient.MySqlConnection(_connectionString)) { using (var cmd = new MySql.Data.MySqlClient.MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = "SELECT NOW();"; cmd.CommandType = System.Data.CommandType.Text; cmd.CommandTimeout = _connectTimeout; await conn.OpenAsync(); var reader = await cmd.ExecuteReaderAsync(); while (await reader.ReadAsync()) { hasResult |= true; } conn.Close(); } } status = hasResult ? HealthCheckStatus.Pass : HealthCheckStatus.Fail; error = null; } catch (Exception exception) { status = HealthCheckStatus.Fail; error = exception.Message; } return(new HealthCheckResult(nameof(MySqlConnectionHealthCheck), _description, error, status.ToString())); }
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 <EntityCollection <T> > GetRecords_Async <T>(Base.RequestFilters filters = null, Base.DataOrder Order = null, int PageSize = 0, int Page = 0) where T : class, IDataEntity { var currentType = typeof(T); var records = new EntityCollection <T>(); //first set the source where we get the data //if there is not any Table attribute we set the source as the type name DataInterface.Attribbutes.Table TableAttribute = (DataInterface.Attribbutes.Table)(from attr in currentType.GetCustomAttributes(true) where attr.GetType() == typeof(DataInterface.Attribbutes.Table) select attr).FirstOrDefault(); using (MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Source)) { await connection.OpenAsync(); try { StringBuilder Query = new StringBuilder(); if (TableAttribute == null) { Query.Append($"Select * from {currentType.Name } where 1=1 "); } else { Query.Append($"Select * from {TableAttribute.Name} where 1=1 "); } MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand(); BuildWhereStaTement(filters, Query, com); if (Order != null) { Query.Append($" order by {Order.Field} {Order.Order}"); } if (Page != 0) { Query.Append($" OFFSET (({Page} - 1) * {PageSize }) ROWS FETCH NEXT {PageSize} ROWS ONLY; "); BuildCountPagingQuery(filters, PageSize, currentType, records, TableAttribute, connection); } com.Connection = connection; com.CommandText = Query.ToString(); var result = await com.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection); if (result.HasRows) { FillresultsNew(currentType, records, result); } result.Close(); } catch (Exception ex) { if (connection.State == System.Data.ConnectionState.Open) { connection.Close(); } throw; } } if (Page != 0) { records.Pager.CurrentPage = Page; } else { records.Pager.TotalRecords = records.Count; } records.Pager.PageRecords = records.Count; return(records); }