public static MySQLResult sql_query_local(string query)
{
{
var result = new MySQLResult {
count_rows = 0
};
// Выполняем запрос.
var dataSet = new DataSet();
//string connectionString = System.Configuration.ConfigurationSettings.AppSettings["MyConnection"];
string connectionString = ConnectionStringLocal;
using (var connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
var dbAdapter = new SqlDataAdapter(query, connection);
dbAdapter.Fill(dataSet);
connection.Close();
}
catch (Exception ex)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
// Заполняем данные.
if (dataSet.Tables.Count == 0)
{
return(result);
}
var table = dataSet.Tables[0];
// Собираем имена.
for (var columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++)
{
result.columns.Add(table.Columns[columnIndex].ColumnName);
}
// Собираем данные.
for (var rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++)
{
var row = table.Rows[rowIndex];
var newRow = new MySQLRow();
for (var columnIndex = 0; columnIndex < result.columns.Count; columnIndex++)
{
newRow.values.Add(row[columnIndex].ToString());
}
result.rows.Add(newRow);
result.count_rows++;
}
return(result);
}
}
/// <summary>
/// This is the actual query wrapper where you read from the database more than a singular value
/// </summary>
/// <param name="query">Query string</param>
/// <param name="parameters">Parameters in dictionary form</param>
/// <returns>Result of the Query, List of rows containing dictionarys representing each row</returns>
public Task <MySQLResult> QueryResult(string query, IDictionary <string, dynamic> parameters = null) => Task.Factory.StartNew(() =>
{
MySQLResult result = new MySQLResult();
System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
long connectionTime = 0, queryTime = 0, readTime = 0;
using (Connection db = new Connection(settings.ConnectionString))
{
timer.Start();
db.connection.Open();
connectionTime = timer.ElapsedMilliseconds;
using (MySqlCommand cmd = db.connection.CreateCommand())
{
cmd.CommandText = query;
cmd.AddParameters(parameters);
try
{
timer.Restart();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
queryTime = timer.ElapsedMilliseconds;
timer.Restart();
while (reader.Read())
{
result.Add(Enumerable.Range(0, reader.FieldCount).ToDictionary(reader.GetName,
i => (reader.IsDBNull(i)) ? null : reader.GetValue(i)));
}
if (settings.Debug)
{
query = cmd.Stringify();
}
}
readTime = timer.ElapsedMilliseconds;
}
catch (MySqlException mysqlEx)
{
PrintErrorInformation(mysqlEx);
}
// I don't think I want to catch the other exceptions. Just throw for now.
}
}
timer.Stop();
PrintDebugInformation(connectionTime, queryTime, readTime, query);
return(result);
}, CancellationToken.None, TaskCreationOptions.None, queryScheduler);
public Task <MySQLResult> QueryResult(string query, IDictionary <string, dynamic> parameters = null) => Task.Factory.StartNew(() => {
MySQLResult result = new MySQLResult();
System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
long connectionTime = 0, queryTime = 0, readTime = 0;
using (Connection db = new Connection(connectionString))
{
timer.Start();
db.connection.Open();
connectionTime = timer.ElapsedMilliseconds;
using (MySqlCommand cmd = db.connection.CreateCommand())
{
cmd.CommandText = query;
cmd.AddParameters(parameters);
timer.Restart();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
queryTime = timer.ElapsedMilliseconds;
timer.Restart();
while (reader.Read())
{
result.Add(Enumerable.Range(0, reader.FieldCount).ToDictionary(reader.GetName, reader.GetValue));
}
}
readTime = timer.ElapsedMilliseconds;
}
}
timer.Stop();
PrintDebugInformation(connectionTime, queryTime, readTime, query);
return(result);
}, CancellationToken.None, TaskCreationOptions.None, queryScheduler);
