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);
}
