/// <summary>
/// 内部使用-执行最终的查询
/// </summary>
/// <typeparam name="TParamter"></typeparam>
/// <param name="conn"></param>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private async Task <DataTable> SqlQuery <TParamter>(DbConnection conn, string sql, TParamter parameters = null, DbTransaction tran = null) where TParamter : class
{
try
{
DbCommand cmd = conn.CreateCommand(sql, parameters);
DataSet ds = new DataSet();
//暂时写死，后续根据连接情况设置多数据库连接
MySqlCommand mysqlCommand = cmd as MySqlCommand;
MySqlDataAdapter adapter = new MySqlDataAdapter(mysqlCommand);
if (tran != null)
{
await adapter.FillAsync(ds);
return(ds.Tables[0]);
}
using (conn)
{
await adapter.FillAsync(ds);
return(ds.Tables[0]);
}
}
catch (Exception ex)
{
throw new AttrSqlException(ex.Message);
}
}
public async Task <DataTable> FetchAsync(string query, CancellationToken cancellationToken, params object[] args)
{
var myTable = new DataTable();
if (cnn.State.ToString() == "Closed")
{
cnn.Open();
}
using (var cmd = new MySqlCommand(query, cnn))
{
//int paramNo = 0;
//foreach (object arg in args)
//{
ProcessParameters(cmd, args);
//MySqlParameter parameter = new MySqlParameter("@" + paramNo.ToString(), arg.GetType());
//parameter.Value = arg;
//cmd.Parameters.Add(parameter);
//paramNo++;
//}
using (var myDataAdp = new MySqlDataAdapter(cmd))
{
using (var myCmdBld = new MySqlCommandBuilder(myDataAdp))
{
await myDataAdp.FillAsync(myTable, cancellationToken);
}
}
}
return(myTable);
}
async void GetUserInfo(string log)
{
initializeDB();
String query1 = "select u.login, u.name, u.surname, u.mail, g.genderrus, u.city, p.userposition ,u.user_created,u.favourganre,u.avatar From users u Inner Join pos_user p On u.positionsID = p.id Inner Join gender g On u.genderID = g.id where login = '******'";
MySqlCommand comm = new MySqlCommand(query1, dbCon);
await dbCon.OpenAsync();
comm.ExecuteNonQuery();
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(comm);
DataTable dt = new DataTable();
await dataAdapter.FillAsync(dt);
var data = dt.Select();
login = log;
name = data[0].ItemArray[1] as string;
surname = (data[0].ItemArray[2]) as string;
mail = (data[0].ItemArray[3]) as string;
gender = (data[0].ItemArray[4]) as string;
city = (data[0].ItemArray[5]) as string;
position = (data[0].ItemArray[6]) as string;
regdate = (data[0].ItemArray[7].ToString());
favourganre = (data[0].ItemArray[8]) as string;
avatar = (data[0].ItemArray[9]) as string;
await dbCon.CloseAsync();
}
/// <summary>
/// Выполняет запрос и возвращает данные в виде таблицы <see cref="DataTable"/>.
/// </summary>
/// <param name="query">SQL запрос.</param>
/// <param name="retryOnError">Повторять выполнение запроса при возникновении ошибки.</param>
/// <param name="timeOut">Таймаут выполнения SQL запроса.</param>
/// <returns>Таблица с данными или null в случае ошибки выполнения запроса.</returns>
public async Task <DataTable> SelectAsync(string query, int?timeOut, bool?retryOnError)
{
DataTable result = null;
QueryContext context = new QueryContext()
{
Query = query,
Retry = retryOnError.HasValue ? retryOnError.Value : RetryOnError,
CommandTimeOut = timeOut.HasValue ? timeOut.Value : DefaultTimeOut
};
result = await _ExecuteAsync(context, async (connection, commandTimeOut) =>
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection))
{
//Если значение таймаута больше или равно нулю - то это значение берем из текущего вызова функции
adapter.SelectCommand.CommandTimeout = commandTimeOut;
result = new DataTable();
await adapter.FillAsync(result);
return(result);
}
});
return(result);
}
private async void Button_Click(object sender, RoutedEventArgs e)
{
string connectionString = "server=localhost;user id=enot;persistsecurityinfo=True;database=enotdb;allowuservariables=True;Password=ctccblecz"; //строка подключения
string sql = "SELECT ID , IDMU , CONCAT (NAME, \' \' , surname) as ФИО from Notaries"; // sql-запрос
using (MySqlConnection connection = new MySqlConnection(connectionString)) //создаем объект подключения к mysql
{
// Создаем объект DataAdapter
using (MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection)) //создаем адаптер из подключения mysql для заполнения кэша dataset
{
// Создаем объект DataSet
using (DataSet ds = new DataSet()) //создаем объект - кэш для хранения данных из БД
{
await adapter.FillAsync(ds); // метод адаптера заполняет кэш
// Отображаем данные
//ds.Tables[0].Columns[0].ColumnName; //имя первого столбца в таблице №1 в коллекции ds.Tables
//ds.Tables[0].Rows[0].ItemArray[0]; // значение первой ячейки первой строки
h1.ItemsSource = ds.Tables[0].DefaultView; //читаем из кэша таблицу и запысываем ее в datagrid
}
}
}
}
public static async Task SelectBroadcastText(string searchTerm, BroadcastTextData data)
{
try
{
data.Clear();
using (MySqlCommand query = mySqlConnection.CreateCommand())
{
string fieldNames = DetermineFieldNames(data);
query.CommandText = "SELECT " + fieldNames + " FROM " + data.SelectTableName + " WHERE MaleText LIKE '%" + searchTerm + "%' OR FemaleText LIKE '%" + searchTerm + "%' LIMIT 100";
using (MySqlDataAdapter dataAdp = new MySqlDataAdapter(query))
{
await dataAdp.FillAsync(data);
foreach (DataColumn c in data.Columns)
c.ReadOnly = true;
return;
}
}
}
catch (MySqlException e)
{
MessageBox.Show("Database Error!\nError: " + e.Message);
}
return;
}
public static async Task <DataRowCollection> QueryAsync(string query)
{
try
{
using (DataTable retl = new DataTable())
{
using (MySqlConnection nc = new MySqlConnection(cstring))
{
using (MySqlCommand cmd = new MySqlCommand(query, nc))
{
nc.Open();
using (var w = new MySqlDataAdapter(cmd))
{
await w.FillAsync(retl);
}
return(retl.Rows);
}
}
}
}
catch (Exception e)
{
lastError = e.ToString();
return(null);
}
}
public async void Pretraga(string pretrazi)
{
try
{
using (MySqlConnection mysql = new MySqlConnection(Login.constring))
{
DataSet ds = new DataSet();
mysql.Open();
//string query = "SELECT * FROM stanje_skladista WHERE "+comboBox1.Text+" LIKE '%"+pretrazi+"%'" ;
var query = QueryBuilder(pretrazi, comboBox1.Text, textBox4.Text, textBox4.Enabled, textBox2.Text, textBox2.Enabled, textBox6.Text, textBox6.Enabled, textBox3.Text, textBox3.Enabled, textBox7.Text, textBox7.Enabled, checkBox6.Checked);
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(query, mysql);
await mySqlDataAdapter.FillAsync(ds);
PostaviIzvjestaj(ds);
dataGridView1.DataSource = ds.Tables[0];
dataGridView1.Columns["MPC"].DefaultCellStyle.Format = "C";
dataGridView1.Columns["MPC_Popust"].DefaultCellStyle.Format = "C";
dataGridView1.Columns["min_MPC"].DefaultCellStyle.Format = "C";
}
}
catch { };
IzracunajPodnozje();
}
/// <summary>
/// 執行SQL指令碼
/// </summary>
/// <returns></returns>
public async Task <DataTable> RunAsync(string commandText)
{
if (!OpenConn())
{
return(null);
}
DataTable fillData = new DataTable();
cmd.CommandText = commandText;
try
{
await adapter.FillAsync(fillData);
ErrorMessage = "";
}
catch (Exception ex)
{
CloseConn();
ErrorMessage = ex.Message;
fillData = null;
}
finally
{
cmd.Parameters.Clear();
}
return(fillData);
}
public Task <DataSet> GetDataSetAsync(string sSQL, List <MySqlParameter> parameters)
{
return(Task.Run(() =>
{
try
{
cn.Open();
cmd = new MySqlCommand(sSQL, cn);
if (parameters != null)
{
cmd.Parameters.AddRange(parameters.ToArray <MySqlParameter>());
}
adptr = new MySqlDataAdapter(cmd);
ds = new DataSet();
adptr.FillAsync(ds);
cn.Close();
return ds;
}
catch (Exception e)
{
cn.Close();
MessageBox.Show("" + e.Message);
return null;
}
}));
}
/// <summary>
/// Fetchs all the data from the SQL Request.
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
public static async void FetchAllAsync(string sql, Dictionary <string, string> parameters = null, Action <DataTable> callback = null)
{
if (con == null || con.State == ConnectionState.Closed)
{
return;
}
if (parameters != null)
{
foreach (KeyValuePair <string, string> replacement in parameters)
{
sql.Replace(replacement.Key, replacement.Value);
}
}
using (MySqlCommand cmd = new MySqlCommand(sql, con))
{
try
{
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataTable data = new DataTable();
await adapter.FillAsync(data);
callback?.Invoke(data);
}
catch (MySqlException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(ex.Message.ToString());
Console.ResetColor();
}
}
}
public async Task <DataTable> QuerySync(string query, List <MySqlParameter> parameters)
{
// Sanitize
query = query.Replace("[[account]]", Config.DatabaseAccountDb);
query = query.Replace("[[player]]", Config.DatabasePlayerDb);
query = query.Replace("[[log]]", Config.DatabaseLogDb);
DataTable result = new DataTable();
try
{
MySqlCommand cmd = new MySqlCommand(query, await Server.DB.GetSuitableConnection());
foreach (MySqlParameter param in parameters)
{
cmd.Parameters.Add(param);
}
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
await da.FillAsync(result);
return(result);
}
}
catch (MySqlException ex)
{
Logger.Syserr($"{ex.Message}");
}
return(null);
}
public async Task <DataTable> ExecuteProcedureForSelect(string spName, MySqlParameter[] param,
string optional)
{
using (_dtTable = new DataTable())
{
using (_connection = OpenConnection())
{
using (var cmd = new MySqlCommand(spName, _connection)
{
CommandType = CommandType.StoredProcedure
})
{
foreach (var p in param)
{
cmd.Parameters.AddWithValue(p.ParameterName, p.Value);
}
using (_sqlDataAdapter = new MySqlDataAdapter(cmd))
{
await _sqlDataAdapter.FillAsync(_dtTable);
return(_dtTable);
}
}
}
}
}
public async Task <DataSet> ExecuteProcedureForSelect(string spName, MySqlParameter[] param)
{
using (_dataSet = new DataSet())
{
using (_connection = OpenConnection())
{
using (var cmd = new MySqlCommand
{
Connection = _connection,
CommandType = CommandType.StoredProcedure,
CommandText = spName
})
{
if (param != null)
{
foreach (var p in param)
{
cmd.Parameters.Add(p);
}
}
using (var da = new MySqlDataAdapter(cmd))
{
await da.FillAsync(_dataSet);
_connection.Close();
return(_dataSet);
}
}
}
}
}
//runs a query and fills in a grid
public async Task Select(DataGrid dg, string q, params object[] vals)
{
//select query for sql
DataTable dataTable = new DataTable();
try
{
MySqlCommand cmd = new MySqlCommand(q, connection);
for (int i = 0; i < vals.Length; i++)
{
cmd.Parameters.AddWithValue($"@val{i}", vals[i]);
}
if (await OpenConnection() == true)
{
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
await da.FillAsync(dataTable);
}
dg.ItemsSource = dataTable.DefaultView;
dg.DataContext = dataTable.DefaultView;
}
}
catch (Exception ex)
{
MessageBox.Show(string.Format("An error occurred {0}", ex.Message), "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
await this.CloseConnection();
}
}
/* Connect to the database specified in the connection string, execute the
* provided query, and return a datatable containing the results. Return null
* if any steps fail. */
private async Task <DataTable> GetDataTableAsync(string connect, string select)
{
if (connect == null)
{
return(null);
}
try
{
using (var connection = new MySqlConnection(connect))
using (var adapter = new MySqlDataAdapter(select, connection))
{
var table = new DataTable();
await adapter.FillAsync(table);
return(table);
}
}
catch // ( Exception e )
{
// MessageBox.Show( e.Message );
return(null);
}
}
public override async Task <DirectLoadResult> LoadAsync(string command)
{
command = this.OnBeforeCommandOverride(this.ConstructDatabaseNameAndScheme(command));
DirectLoadResult result = new DirectLoadResult();
using (var sqlConnection = new MySqlConnection(this.ConnectionString))
{
try
{
using (var sqlCommand = new MySqlCommand(command, sqlConnection))
using (var sqlAdapter = new MySqlDataAdapter(sqlCommand))
{
await sqlConnection.OpenAsync();
result.DataTable = new DataTable();
await sqlAdapter.FillAsync(result.DataTable);
}
}
catch (Exception e)
{
result.Exception = e;
this.OnException(DirectDatabaseExceptionType.OnLoadAsync, command, e);
}
finally
{
await sqlConnection.CloseAsync();
}
}
return(result);
}
public async Task <DataSet> ExecuteQueryAsync(string commandText, Dictionary <string, object> parameters)
{
logger.LogInformation($"{nameof(MySqlQueryManager)}.{nameof(ExecuteQueryAsync)} : Started");
var dataset = new DataSet();
try
{
await connection.OpenAsync();
}
catch (Exception ex)
{
throw new ServiceException("ReturnDataSet_Common.Error check(ExecuteDataset); connection.Open()", ex);
}
using (var transaction = connection.BeginTransaction())
{
var command = connection.CreateCommand();
command.CommandText = commandText;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Transaction = transaction;
if (parameters != null && parameters.Count() > 0)
{
foreach (var parameter in parameters)
{
command.Parameters.Add(CreateParameter(parameter.Key, parameter.Value));
}
}
try
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(command))
{
logger.LogInformation($"{nameof(MySqlQueryManager)}.{nameof(ExecuteQueryAsync)} : Fill DataSet");
await adapter.FillAsync(dataset);
await transaction.CommitAsync();
logger.LogInformation($"{nameof(MySqlQueryManager)}.{nameof(ExecuteQueryAsync)} : Commit transaction.");
}
}
catch (Exception ex)
{
await transaction.RollbackAsync();
logger.LogInformation($"{nameof(MySqlQueryManager)}.{nameof(ExecuteQueryAsync)} : Rollback transaction.");
logger.LogError(ex, $"{nameof(MySqlQueryManager)}.{nameof(ExecuteQueryAsync)} : Exception");
throw new ServiceException("ReturnDataSet_Common.Error check(ExecuteDataset)", ex);
}
finally
{
await connection.CloseAsync();
}
}
return(dataset);
}
public async Task <DataTable> GetDataTableAsync(MySqlConnection conn, string sql, MySqlParameter[] parameter = null, int timeout = 180)
{
var ds = new DataTable();
try
{
var cmd = new MySqlCommand(sql, conn)
{
CommandTimeout = timeout
};
if (parameter != null)
{
foreach (var tmp in parameter)
{
cmd.Parameters.Add(tmp);
}
}
conn.Open();
var adapter = new MySqlDataAdapter(cmd);
await adapter.FillAsync(ds);
}
catch (System.Exception ex)
{
throw new RepositoryException(ex.Message, ex);
}
finally
{
conn?.Close();
}
return(ds);
}
/// <summary>
/// Selects data specified by the parameters from world database with custom WHERE statement.
/// <para>
/// Example for whereStatement: 'entry=12345 AND groupid=0'
/// </para>
/// </summary>
public static async Task SelectMySqlData(NodeData data, string whereStatement)
{
try
{
data.Clear();
using (MySqlCommand query = mySqlConnection.CreateCommand())
{
string fieldNames = DetermineFieldNames(data);
query.CommandText = "SELECT " + fieldNames + " FROM " + data.SelectTableName + " WHERE " + whereStatement + " LIMIT 100";
using (MySqlDataAdapter dataAdp = new MySqlDataAdapter(query))
{
await dataAdp.FillAsync(data);
foreach (DataColumn c in data.Columns)
c.ReadOnly = true;
return;
}
}
}
catch (MySqlException e)
{
MessageBox.Show("Database Error!\nError: " + e.Message);
}
return;
}
/// <summary>
/// Store Procedure Kullanarak Verileri DataTable Tipinde Almak İçin Kullanılır.
/// Gönderdilen object adı ile procedure adı aynı olmak zorundadır.
/// Procedure alması gereken parametreler gönderilen objenin içinde olmalıdır.
/// Test Edildi!
/// </summary>
public async Task <DataTable> GetDataWithStoredProcedure(object data)
{
var result = new DataTable();
string query = "CALL " + data.GetType().Name + "(";
IList <PropertyInfo> properties = new List <PropertyInfo>(data.GetType().GetProperties());
foreach (PropertyInfo item in properties)
{
Type type = item.GetValue(data, null).GetType();
if (type == typeof(string))
{
query += "'" + item.GetValue(data, null) + "',";
}
else
{
query += item.GetValue(data, null) + ",";
}
}
query = query.TrimEnd(',') + ")";
using (var con = new MySqlConnection(this.connectionString))
{
using (var adapter = new MySqlDataAdapter(query, con))
{
try
{
await adapter.FillAsync(result);
}
catch (Exception) { }
}
}
return(result);
}
/// <summary>
/// Pega as informaçoes de todos plugins e guarda na propriedade <see cref="DataTable"/>
/// </summary>
/// <param name="dataGrid">se true, adiciona tabela na propriedade <see cref="DataTable"/>. senao, adiciona informaçoes de um determinado plugin na propriedade <see cref="TooltipInfo"/>.</param>
/// <param name="itemProcurarID">se dataGrid for false, deve informar o codigo do plugin para ser informado. senao, <see cref="Nullable"/></param>
/// <returns>Retorna o resultado da tarefa em bool.</returns>
public async Task <bool> InformacoesAsync(bool dataGrid, string itemProcurarID)
{
using (MySqlConnection con = new MySqlConnection(await PegarConexaoMySQL.ConexaoAsync()))
{
try
{
await con.OpenAsync();
using (MySqlCommand get = new MySqlCommand(CmdText2, con))
{
if (dataGrid)
{
using (MySqlDataAdapter mySqlData = new MySqlDataAdapter())
{
mySqlData.SelectCommand = get;
DataTable dataTable = new DataTable();
_ = await mySqlData.FillAsync(dataTable);
DataTable = dataTable;
NomeColunas();
using (MySqlCommand num = new MySqlCommand(CmdText3, con))
{
MetodosConstantes.EnviarMenssagem(@var.Parse((await num.ExecuteScalarAsync()).ToString()) + " Plugins encontrados!");
}
return(true);
}
}
else
{
TooltipInfo = new List <string>();
get.CommandText = $"{CmdText2} where ID = @ID";
_ = get.Parameters.Add(new MySqlParameter("@ID", itemProcurarID));
using (MySqlDataReader a = (MySqlDataReader)await get.ExecuteReaderAsync())
{
if (await a.ReadAsync())
{
for (byte i = 1; i <= 4; i++)
{
TooltipInfo.Add(a.GetString(i));
}
}
return(true);
}
}
}
}
catch (Exception e)
{
MetodosConstantes.MostrarExceptions(e);
return(false);
}
}
}
private async Task <DataTable> ExecuteDataTable(string sqlCommand, MySqlConnection conn)
{
var adapter = new MySqlDataAdapter(sqlCommand, conn);
var dataset = new DataSet();
await adapter.FillAsync(dataset);
return(dataset.Tables[0]);
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="sqltext"></param>
/// <returns></returns>
public static async Task <DataSet> ExecuteDataset(string sqltext)
{
using (MySqlConnection conn = new MySqlConnection(conf))
{
conn.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(sqltext, conf);
DataSet ds = new DataSet();
await adapter.FillAsync(ds);
return(ds);
}
}
private async Task <DataSet> FillData()
{
DataCenter dc = new DataCenter();
dsCheckExpDate ds = new dsCheckExpDate();
MySqlConnection cn = dc.ConnOpen(); // getConnection
string sql = string.Format(@"SELECT drg.drugName, rcd.lotNo, stc.TotalRemain, rcd.expDate from receivedatadetails AS rcd INNER JOIN drug AS drg ON rcd.drug_id = drg.drug_id INNER JOIN stockcard AS stc ON rcd.drug_id = stc.drug_id where rcd.expDate between CURDATE() AND CURDATE() + 180 AND stc.TotalRemain > 0 GROUP BY rcd.lotNo ORDER BY rcd.expDate ASC");
MySqlDataAdapter da = new MySqlDataAdapter(sql, cn);
await da.FillAsync(ds, ds.Tables[0].TableName);
return(ds);
}
public static void RefreshTableWithNewCommand(string cmd)
{
if (!IsConnected())
{
// make handler on not connecting
return;
}
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd, conn);
Table.Clear();
adapter.FillAsync(Table);
}
private async void PostaviRacunaAsync(string index_racuna, string referenca_na_godinu)
{
using (MySqlConnection mysqlc = new MySqlConnection(Login.constring))
{
await mysqlc.OpenAsync();
string query = "SELECT index_stavke,sifra,naziv,kolicina,MPC_Popust,MPC_Prodano FROM racuni_stavke" + referenca_na_godinu + " WHERE id_racun = " + index_racuna;
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(query, mysqlc);
DataSet dataSet = new DataSet();
await mySqlDataAdapter.FillAsync(dataSet);
dataGridView1.DataSource = dataSet.Tables[0];
}
}
public static void FillTable()
{
if (!IsConnected())
{
return;
}
var query = "SELECT id, nama, golongan, jabatan," +
"departemen, gaji, tunjangan, DATE_FORMAT(tgl_lahir, '%d-%m-%Y') as tgl_lahir," +
"jenis_kelamin, alamat, no_rek, no_npwp, no_bpjs, lokasi FROM tbl_karyawan";
MySqlDataAdapter selectAllKaryawan = new MySqlDataAdapter(query, conn);
selectAllKaryawan.FillAsync(Table);
}
protected async override Task <object> Execute(MySqlCommand command)
{
string name = string.Empty;
DataTable dataTable = new DataTable();
using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command))
{
await dataAdapter.FillAsync(dataTable);
}
if (dataTable.Rows.Count != 0)
{
name = dataTable.Rows[0].Field <string>("TitlesName");
}
return(name);
}
/// <summary>
/// 返回dataset 传入sqlparameter
/// </summary>
/// <param name="sqltext"></param>
/// <param name="param"></param>
/// <returns></returns>
public static async Task <DataSet> ExecuteDataset(string sqltext, MySqlParameter[] param)
{
using (MySqlConnection conn = new MySqlConnection(conf))
{
conn.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(sqltext, conn);
//adapter.SelectCommand.Connection = conn;
adapter.SelectCommand.CommandType = CommandType.Text;
// adapter.SelectCommand.CommandText = sqltext;
adapter.SelectCommand.Parameters.AddRange(param);
DataSet ds = new DataSet();
await adapter.FillAsync(ds);
return(ds);
}
}
