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