public async Task <bool> LogAsync(LogLevel level, params string[] messages)
{
// DESIGN: Using OdbcConnection instead of SqlConnection as it is the standard
// RDBMS driver. SqlConnection is more performant, but it is specific to SQL Server
using (var connection = new OdbcConnection(ConnectionString))
{
var sql = string.Empty; // TODO: YOUR implementation code HERE
// SECURITY WARNING: Use of raw SQL command is vulnerable to SQL injection attacks.
// Recommended to use OdbcCommandBuilder instead
using (var command = new OdbcCommand(sql))
{
command.Connection = connection;
connection.Open();
var rowsAffected = await command.ExecuteNonQueryAsync().ConfigureAwait(false);
if (rowsAffected > 0)
{
return(true);
}
return(false);
}
}
}
public async Task <bool> AutorizeAsync(string token, Chat chat)
{
if (string.IsNullOrEmpty(token) || chat == null)
{
return(false);
}
try
{
var command = new OdbcCommand($"SELECT managerid, telegramchatid, token FROM telegram_managers where token='{token.Replace("\'", "\'\'")}';".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
var reader = await command.ExecuteReaderAsync();
if (!reader.Read())
{
return(false);
}
var updcommand = new OdbcCommand($"UPDATE telegram_managers SET telegramchatid ={chat.Id}, token=null where token='{token?.Replace("\'", "\'\'")}'; ".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
await updcommand.ExecuteNonQueryAsync();
return(true);
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "AutorizeAsync: " + e.Message));
#if DEBUG
throw;
#endif
return(false);
}
}
public async void InsertOrUpdateClient(User user, Contact contact = null)
{
if (user == null)
{
return;
}
try
{
InsertOrUpdateUser(user);
var test = new OdbcCommand($"select * from telegram_clients where telegramuserid={user.Id}", Connection);
var command = new OdbcCommand()
{
Connection = Connection,
CommandText =
(test.ExecuteReader().Read() && contact != null)
? $"UPDATE telegram_clients SET phone = '{contact?.PhoneNumber?.Replace("\'", "\'\'")}' WHERE telegramuserid = {user.Id};".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'")
: $"INSERT INTO telegram_clients (phone,telegramuserid) VALUES('{contact?.PhoneNumber?.Replace("\'", "\'\'")}', {user.Id});".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'")
};
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertOrUpdateClient: " + e.Message));
#if DEBUG
throw;
#endif
}
}
public async void InsertOrUpdateUser(User user)
{
if (user == null)
{
return;
}
try
{
var test = new OdbcCommand($"select * from telegram_users where id={user.Id};", Connection);
var command = new OdbcCommand()
{
Connection = Connection,
CommandText =
test.ExecuteReader().Read()
? $"UPDATE telegram_users SET first_name = '{user.FirstName?.Replace("\'", "\'\'")}', last_name = '{user.LastName?.Replace("\'", "\'\'")}', username = '******'", "\'\'")}' WHERE id = {user.Id};".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'")
: $"INSERT INTO telegram_users (id, first_name, last_name, username) VALUES ({user.Id}, '{user.FirstName?.Replace("\'", "\'\'")}', '{user.LastName?.Replace("\'", "\'\'")}', '{user.Username?.Replace("\'", "\'\'")}');".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'")
};
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, $"InsertOrUpdateUser: {e.Message}"));
#if DEBUG
throw;
#endif
}
}
// TODO: отсылка прикрепленных объектов и нормальная замена их в бд
public async Task <List <Message> > GetUnsendedMessages()
{
try
{
var messages = new DataTable();
messages.Load(await new OdbcCommand($"SELECT message_id, from_id, date, chat_id, text FROM telegram_messages where not issended;", Connection).ExecuteReaderAsync());
var command = new OdbcCommand("DELETE FROM telegram_messages WHERE message_id < 0;", Connection);
await command.ExecuteNonQueryAsync();
return(Enumerable.Select(messages.AsEnumerable(), row => new Message
{
MessageId = int.Parse(row[0].ToString()),
Chat = new Chat {
Id = int.Parse(row[3].ToString())
},
Text = row[4].ToString()
}).ToList());
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "GetUnsendedMessages: " + e.Message));
#if DEBUG
throw;
#endif
return(new List <Message>(0));
}
}
public async Task <string> Post([FromBody] Message message)
{
MyObject objReturn = new MyObject(message.Data);
var dbPath = Directory.GetParent(Environment.CurrentDirectory).FullName;
connectionString += (dbPath + "\\record.accdb");
using (OdbcConnection connection = new OdbcConnection(connectionString)) {
string addQuery = $"INSERT INTO recordData (Data) Values ('{message.Data}')";
try {
await connection.OpenAsync();
OdbcCommand command = new OdbcCommand(addQuery, connection);
await command.ExecuteNonQueryAsync();
connection.Close();
objReturn.State = "Success";
}
catch (Exception ex) {
objReturn.State = "Fail";
objReturn.Msg = ex.ToString();
}
var jsonSetting = new JsonSerializerSettings {
NullValueHandling = NullValueHandling.Ignore
};
return(JsonConvert.SerializeObject(objReturn, Formatting.Indented, jsonSetting));
}
}
/// <summary>
/// Executes save command
/// </summary>
/// <param name="executeStatement">execute statement</param>
/// <returns></returns>
public static async Task ExecuteNonQuaryAsync(string executeStatement)
{
using OdbcConnection con = new OdbcConnection(GlobalConstants.ConnectionString);
using OdbcCommand com = new OdbcCommand(executeStatement, con);
await con.OpenAsync();
await com.ExecuteNonQueryAsync();
}
/// <summary>
/// 异步执行一般sql语句
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public Task <int> ExecuteCmdAsync(string cmd)
{
using (OdbcConnection conn = OpenConnection())
{
OdbcCommand odbc = new OdbcCommand(cmd, conn);
Console.WriteLine("DBUtils.ExecuteCmdAsync cmd : " + cmd);
//Console.WriteLine("DBUtils.ExecuteCmdAsync cmd");
return(odbc.ExecuteNonQueryAsync());
}
}
private async Task InsertNewMessage()
{
string queryString = string.Format("INSERT INTO dbo.ServerMessages (Message, SenderId, ReceiverId) VALUES('{0}','{1}','{2}')", tbCompose.Text, "CSharp", "All");
OdbcCommand command = new OdbcCommand(queryString);
using (OdbcConnection connection = new OdbcConnection(CONNECTION_STRING))
{
command.Connection = connection;
connection.Open();
await command.ExecuteNonQueryAsync();
}
}
private async void InsertVenue(Message message)
{
if (message?.Venue == null)
{
return;
}
try
{
var command = new OdbcCommand($"INSERT INTO telegram_venues ( message_id, latitude, longitude, title, address, foursquare_id) VALUES ({message.MessageId}, {message.Venue.Location.Latitude}, {message.Venue.Location.Longitude}, '{message.Venue.Title?.Replace("\'", "\'\'")}', '{message.Venue.Address?.Replace("\'", "\'\'")}', '{message.Venue.FoursquareId?.Replace("\'", "\'\'")}');".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertVenue: " + e.Message));
#if DEBUG
throw;
#endif
}
}
private async void InsertSticker(Sticker sticker)
{
if (sticker == null)
{
return;
}
try
{
var command = new OdbcCommand($"INSERT INTO telegram_stickers ( file_id, width, height, file_size) VALUES ('{sticker.FileId?.Replace("\'", "\'\'")}', {sticker.Width}, {sticker.Height},{sticker.FileSize});".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertSticker: " + e.Message));
#if DEBUG
throw;
#endif
}
}
private async void InsertDocument(Document document)
{
if (document == null)
{
return;
}
try
{
var command = new OdbcCommand($"INSERT INTO telegram_documents (file_id, file_name, mime_type, file_size) VALUES ('{document.FileId?.Replace("\'", "\'\'")}', '{document.FileName?.Replace("\'", "\'\'")}', '{document.MimeType?.Replace("\'", "\'\'")}', {document.FileSize});".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertDocument: " + e.Message));
#if DEBUG
throw;
#endif
}
}
private async void InsertAudio(Audio audio)
{
if (audio == null)
{
return;
}
try
{
var command = new OdbcCommand($"INSERT INTO telegram_audios (file_id, duration, performer, title, mime_type, file_size) VALUES ('{audio.FileId?.Replace("\'", "\'\'")}', {audio.Duration}, '{audio.Performer?.Replace("\'", "\'\'")}', '{audio.Title?.Replace("\'", "\'\'")}', '{audio.MimeType?.Replace("\'", "\'\'")}', {audio.FileSize});".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertAudio: " + e.Message));
#if DEBUG
throw;
#endif
}
}
static public void updateCharacter(int exp, string player)
{
try
{
connDB.Open();
}
catch (OdbcException e)
{
Console.WriteLine(e.Message + "\n\n" + e.StackTrace);
}
data = new DataSet();
dbAdapter.SelectCommand = new OdbcCommand("SELECT * FROM users WHERE twitch_name='" + player + "';", connDB);
dbAdapter.Fill(data);
var newExp = exp + int.Parse(data.Tables[0].Rows[0]["experience"].ToString());
OdbcCommand update = new OdbcCommand("UPDATE users SET experience=" + newExp + "WHERE twitch_name='" + player + "';", connDB);
update.ExecuteNonQueryAsync().Wait();
connDB.Close();
}
public async void SetToken(int managerId, string token)
{
if (string.IsNullOrEmpty(token))
{
return;
}
try
{
var command = new OdbcCommand($"UPDATE telegram_managers SET telegramchatid = NULL, token ='{token?.Replace("\'", "\'\'")}' WHERE managerid={managerId}; ".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "SetToken: " + e.Message));
#if DEBUG
throw;
#endif
}
}
//TODO убрать превью из стикера
public async void InsertMessage(Message message, bool?isSended = null)
{
if (message == null)
{
return;
}
try
{
InsertAudio(message.Audio);
InsertDocument(message.Document);
InsertLocation(message);
InsertMessageEntities(message);
InsertPhotos(message);
InsertSticker(message.Sticker);
InsertVenue(message);
InsertVideo(message.Video);
InsertVoice(message.Voice);
if (message.ForwardFrom != null)
{
InsertOrUpdateUser(message.ForwardFrom);
}
if (message.ReplyToMessage != null)
{
InsertMessage(message.ReplyToMessage);
}
if (message.PinnedMessage != null)
{
InsertMessage(message.PinnedMessage);
}
var command = new OdbcCommand($"INSERT INTO telegram_messages (message_id, from_id, date, chat_id, text, forward_from, forward_date, reply_to_message_id, pinned_message_id, document_id, caption, audio_id, video_id, voice_id, sticker_id, issended) VALUES({message.MessageId}, {message.From?.Id.ToString() ?? "NULL"}, {message.Date.ToUnixTime()}, {message.Chat.Id}, '{message.Text?.Replace("\'", "\'\'")}', {message.ForwardFrom?.Id.ToString() ?? "NULL"}, {message.ForwardDate?.ToUnixTime().ToString() ?? "NULL"}, {message.ReplyToMessage?.MessageId.ToString() ?? "NULL"}, {message.PinnedMessage?.MessageId.ToString() ?? "NULL"}, '{message.Document?.FileId?.Replace("\'", "\'\'")}', '{message.Caption?.Replace("\'", "\'\'")}', '{message.Audio?.FileId?.Replace("\'", "\'\'")}', '{message.Video?.FileId?.Replace("\'", "\'\'")}', '{message.Voice?.FileId?.Replace("\'", "\'\'")}', '{message.Sticker?.Thumb.FileId?.Replace("\'", "\'\'")}', {isSended ?? true});".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertMessage: " + e.Message));
#if DEBUG
throw;
#endif
}
}
private async void InsertLocation(Message message)
{
if (message?.Location == null)
{
return;
}
try
{
var command = new OdbcCommand($"INSERT INTO telegram_locations (message_id, latitude, longitude) VALUES ({message.MessageId}, {message.Location.Latitude}, {message.Location.Longitude});", Connection);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertLocation: " + e.Message));
#if DEBUG
throw;
#endif
}
}
public async void InsertOrUpdateCurrentBot(User bot)
{
if (bot == null)
{
return;
}
try
{
await new OdbcCommand("DELETE FROM telegram_currentbot;", Connection).ExecuteNonQueryAsync();
var command = new OdbcCommand($"INSERT INTO telegram_currentbot (id) VALUES ({bot.Id});", Connection);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertOrUpdateCurrentBot: " + e.Message));
#if DEBUG
throw;
#endif
}
}
private async void InsertVoice(Voice voice)
{
if (voice == null)
{
return;
}
try
{
var command = new OdbcCommand($"INSERT INTO telegram_voices ( file_id, duration, mime_type, file_size) VALUES ( '{voice.FileId?.Replace("\'", "\'\'")}', {voice.Duration}, '{voice.MimeType?.Replace("\'", "\'\'")}', {voice.FileSize});".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertVoice: " + e.Message));
#if DEBUG
throw;
#endif
}
}
public async void InsertOrUpdateChat(Chat chat, bool?isClosed = null, bool?isDialogOpened = null)
{
if (chat == null)
{
return;
}
try
{
var test = new OdbcCommand($"select * from telegram_chats where id={chat.Id}", Connection);
bool isUpdate = test.ExecuteReader().Read();
var command = new OdbcCommand {
Connection = Connection
};
if (isUpdate)
{
command.CommandText =
$"UPDATE telegram_chats SET type = '{chat.Type}', title = '{chat.Title?.Replace("\'", "\'\'") }', username = '******'", "\'\'")}', first_name = '{chat.FirstName?.Replace("\'", "\'\'")}', last_name = '{chat.LastName?.Replace("\'", "\'\'")}'".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'");
if (isClosed.HasValue)
{
command.CommandText += $", is_closed = {isClosed}";
}
if (isDialogOpened.HasValue)
{
command.CommandText += $", is_dialog_opened = {isDialogOpened}";
}
command.CommandText += $" WHERE id={chat.Id};";
}
else
{
command.CommandText = $"INSERT INTO telegram_chats (id ,type ,title ,username ,first_name ,last_name ,is_closed, is_dialog_opened) VALUES ({chat.Id}, '{chat.Type}', '{chat.Title?.Replace("\'", "\'\'")}', '{chat.Username?.Replace("\'", "\'\'")}', '{chat.FirstName?.Replace("\'", "\'\'")}', '{chat.LastName?.Replace("\'", "\'\'")}', {isClosed ?? false}, {isDialogOpened ?? false});".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'");
}
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertOrUpdateChat: " + e.Message));
#if DEBUG
throw;
#endif
}
}
private async void InsertVideo(Video video)
{
if (video == null)
{
return;
}
var command = new OdbcCommand($"INSERT INTO telegram_videos ( file_id, width, height, duration, mime_type, file_size) VALUES ( '{video.FileId?.Replace("\'", "\'\'")}', {video.Width}, {video.Height}, {video.Duration}, '{video.MimeType?.Replace("\'", "\'\'")}', {video.FileSize});".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
try
{
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertVideo: " + e.Message));
#if DEBUG
throw;
#endif
}
}
/// <summary>
/// Verify if a given context is supported for current secure store
/// Tries to connect to the DB given by the context
/// </summary>
/// <returns></returns>
public async Task ValidateContextAsync(string context)
{
// verify context is able to be deserialized
SqlPassContext config = DeserializeContext(context);
// verify connection can be established
string connectionString = BuildConnectionString(config);
using (OdbcConnection connection = new OdbcConnection(connectionString))
using (OdbcCommandBuilder builder = new OdbcCommandBuilder())
{
await connection.OpenAsync();
string escapedTableName = builder.QuoteIdentifier(config.TableName, connection);
try
{
using (OdbcCommand command = new OdbcCommand($"SELECT TOP 1 * FROM {escapedTableName}", connection))
{
await command.ExecuteReaderAsync();
}
}
catch (OdbcException ex)
{
if (!ex.Message.Contains($"Invalid object name '{config.TableName}'"))
{
throw;
}
string queryStringCreateTable = $@"CREATE TABLE {escapedTableName} (
name varchar(96) NOT NULL PRIMARY KEY,
value varchar(max) NOT NULL)";
using (OdbcCommand command = new OdbcCommand(queryStringCreateTable, connection))
{
await command.ExecuteNonQueryAsync();
}
}
}
}
private async void InsertMessageEntities(Message message)
{
if (message?.Entities == null || message.Entities.Count == 0)
{
return;
}
foreach (var entity in message.Entities)
{
try
{
var command = new OdbcCommand($"INSERT INTO telegram_messageentities (message_id, type, \"offset\", length, url) VALUES ({message.MessageId},'{entity.Type}', {entity.Offset}, {entity.Length},'{entity.Url?.Replace("\'", "\'\'")}');".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertMessageEntities: " + e.Message));
#if DEBUG
throw;
#endif
}
}
}
private async void InsertPhotos(Message message)
{
if (message?.Photo == null || !message.Photo.Any())
{
return;
}
foreach (var photo in message.Photo)
{
try
{
var command = new OdbcCommand($"INSERT INTO telegram_photos (messageid, file_id, width, height, file_size) VALUES ({message.MessageId}, '{photo.FileId?.Replace("\'", "\'\'")}', {photo.Width}, {photo.Height},{photo.FileSize});".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertPhotos: " + e.Message));
#if DEBUG
throw;
#endif
}
}
}
private async void Button_Click_2(object sender, RoutedEventArgs e)
{
//użyć ExecuteReaderAsync
//przebudować generowanie raportu
try
{
OdbcCommand select = new OdbcCommand("SELECT * FROM " + ChamberTextBlock.Text + " ORDER BY DATE_TIME DESC ", OdbcConnect);
var result = await select.ExecuteNonQueryAsync();
OdbcDataReader odbcDataReader = select.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(odbcDataReader);
DataView.ItemsSource = dt.DefaultView;
odbcDataReader.Dispose();
odbcDataReader.Close();
}
catch (OdbcException ex)
{
zapiszLog("Błąd wykonania polecenia SELECT: " + ex.Message);
MessageBox.Show("Brak połączenia z bazą danych");
}
}
/// <summary>
/// Executes a query without a return. 27/11/15
/// </summary>
private static bool ExecuteNonQuery(string query)
{
if (connectionOpen())
{
// Creates a database command from the query and existing connection
OdbcCommand cmd = new OdbcCommand(query, connection);
try
{
cmd.ExecuteNonQueryAsync(); // Executes the command
return(true);
}
catch (OdbcException ex)
{
// Displays an error if something bad occurs while executing the command
error = ex.Message;
return(false);
}
}
else
{
return(false);
}
}
/// <summary>
/// Executes a non-result query
/// </summary>
/// <returns></returns>
private async Task <dynamic> ExecuteNonQuery()
{
try
{
using (OdbcConnection cn = new OdbcConnection(this.ConnectionString))
{
using (OdbcCommand cmd = new OdbcCommand(this.Query, cn))
{
cn.Open();
int result = await cmd.ExecuteNonQueryAsync();
return(result);
}
}
}
catch (Exception ex)
{
//ERROR[HY000][Elevate Software][DBISAM] DBISAM Engine Error # 9729 Duplicate key found in the index 'Primary' of the table 'CALLPNT'
Regex regex = new Regex(@"^(ERROR\W?\[[a-zA-Z0-9]+\]\W?[Elevate Software\]\W?\[DBISAM\])\W?([a-zA-Z ]+)(#\W?)(\d+)(.*)$");
Match m = regex.Match(ex.Message);
if (m.Success)
{
if (m.Groups[3].Value == "9729")
{
return(-1);
}
else
{
throw ex;
}
}
throw ex;
}
finally
{
}
}
public async void InsertOpportunity(User user, string description)
{
if (user == null || string.IsNullOrEmpty(description))
{
return;
}
try
{
var reader = await new OdbcCommand($"SELECT id FROM telegram_clients where telegramuserid={user.Id};", Connection).ExecuteReaderAsync();
if (!reader.Read())
{
return;
}
var command = new OdbcCommand($"INSERT INTO opportunities (clientid,description) VALUES({reader[0]}, '{description?.Replace("\'", "\'\'")}');".Replace("\'\'", "NULL").Replace("NULLNULL", "\'\'\'\'"), Connection);
await command.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Log.Add(new Log.LogMessage(Log.MessageType.ERROR, "InsertOpportunity: " + e.Message));
#if DEBUG
throw;
#endif
}
}
/// <summary>
/// Execute one or more SQL statements as a transaction. Roll back the transaction if any of them fail.
/// </summary>
public async Task Execute(
IUi ui,
int rowLimit,
string connectionString)
{
List <string> statements = SqlQuery.SplitIntoStatements(Query);
if (statements.Count == 0)
{
return;
}
var sw = new System.Diagnostics.Stopwatch();
var currentStatement = "";
try
{
using (var cn = new OdbcConnection(connectionString))
{
// START CONNECT
ui.SetStatusLabel(ExecuteStatus.Connecting.Description());
sw.Restart();
cn.Open();
Duration_Connect = sw.Elapsed;
// END CONNECT
using (var cmd = new OdbcCommand())
{
cmd.Connection = cn;
// START EXECUTE
ui.SetStatusLabel(ExecuteStatus.Executing.Description());
sw.Restart();
using (var t = cn.BeginTransaction())
{
cmd.Transaction = t; // Assign transaction object for a pending local transaction.
foreach (var statement in statements)
{
if (string.IsNullOrEmpty(statement))
{
continue;
}
currentStatement = statement.Trim();
cmd.CommandText = currentStatement;
var statementResult = new StatementResult();
if (!currentStatement.StartsWith("select", StringComparison.OrdinalIgnoreCase))
{
statementResult.RowsAffected = await cmd.ExecuteNonQueryAsync();
statementResult.Duration_Execute = sw.Elapsed;
// END EXECUTE
}
else
{
using (var reader = await cmd.ExecuteReaderAsync())
{
statementResult.Duration_Execute = sw.Elapsed;
// END EXECUTE
// BEGIN STREAM
ui.SetStatusLabel(ExecuteStatus.Streaming.Description());
sw.Restart();
statementResult.Data.Add(GetColumns(reader));
int readCount = 1;
while (readCount <= rowLimit && reader.Read())
{
statementResult.Data.Add(GetData(reader));
readCount += 1;
}
statementResult.Duration_Stream = sw.Elapsed;
// END STREAM
ui.SetStatusLabel("");
statementResult.RowsAffected = reader.RecordsAffected;
}
}
Results.Add(statementResult);
}
try
{
t.Commit();
}
catch
{
t.Rollback();
throw;
}
}
}
}
}
catch (ArgumentOutOfRangeException ex)
{
if (ex.Message == "Year, Month, and Day parameters describe an un-representable DateTime.")
{
// filemaker allows importing incorrect data into fields, so we need to catch these errors!
Error = ExecuteError.UnrepresentableDateTimeValue.Description();
}
else
{
Error = ex.Message;
}
Error += Environment.NewLine + Environment.NewLine + currentStatement;
}
catch (Exception ex)
{
Error = ex.Message + Environment.NewLine + Environment.NewLine + currentStatement;
}
}
public void TMCThread()
{
Console.WriteLine("TwitchMessageThread Start");
Active = true;
var consumer = new ConsumerBuilder <string, string>(config).Build();
var topicp = new TopicPartition(topic, 0);
consumer.Assign(topicp);
while (Active)
{
try
{
var consumeresult = consumer.Consume(canceltoken);
if (!consumeresult.IsPartitionEOF)
{
var input = consumeresult.Value;
var channel = input.Substring(0, input.IndexOf(" "));
var uname = input.Substring(input.IndexOf(" ")).Trim();
var message = uname.Substring(uname.IndexOf(" ")).Trim();
uname = uname.Substring(0, uname.IndexOf(" ")).Trim();
uname = uname.TrimStart(new char[] { '\0', ':' });
//Console.WriteLine(channel);
//Console.WriteLine(uname);
//Console.WriteLine(message);
try
{
dbAdapter.SelectCommand = new OdbcCommand("SELECT * FROM users WHERE twitch_name='" + uname + "';", connDB);
dbAdapter.Fill(data);
if (data.Tables[0].Rows.Count != 0)
{
mInput = new ModelInput();
mInput.Message = message;
mOutput = ConsumeModel.Predict(mInput);
string result = mOutput.Prediction;
if (data.Tables[0].Rows[0]["growing"].ToString().Equals("1"))
{
string attribute = "";
int value = 0;
if (result.Equals("Temper"))
{
attribute = "temper";
value = int.Parse(data.Tables[0].Rows[0]["temper"].ToString()) + 1;
}
else if (result.Equals("Cheer"))
{
attribute = "cheer";
value = int.Parse(data.Tables[0].Rows[0]["cheer"].ToString()) + 1;
}
else if (result.Equals("Curiousity"))
{
attribute = "curiosity";
value = int.Parse(data.Tables[0].Rows[0]["curiosity"].ToString()) + 1;
}
else if (result.Equals("Charisma"))
{
attribute = "charisma";
value = int.Parse(data.Tables[0].Rows[0]["charisma"].ToString()) + 1;
}
else if (result.Equals("Empathy"))
{
attribute = "empathy";
value = int.Parse(data.Tables[0].Rows[0]["empathy"].ToString()) + 1;
}
OdbcCommand comm = new OdbcCommand("UPDATE users SET " + attribute + "=" + value + " WHERE twitch_name='" + uname + "';", connDB);
comm.ExecuteNonQueryAsync().Wait();
if (int.Parse(data.Tables[0].Rows[0]["temper"].ToString()) + int.Parse(data.Tables[0].Rows[0]["cheer"].ToString()) + int.Parse(data.Tables[0].Rows[0]["curiosity"].ToString()) + int.Parse(data.Tables[0].Rows[0]["charisma"].ToString()) + int.Parse(data.Tables[0].Rows[0]["empathy"].ToString()) >= 50)
{
comm = new OdbcCommand("UPDATE users SET growing=false WHERE twitch_name='" + uname + "';", connDB);
comm.ExecuteNonQueryAsync().Wait();
}
}
}
else
{
commandMessageQueue.Enqueue("SCPBlacklist " + uname);
}
data.Reset();
}
catch (OdbcException e)
{
}
}
else
{
Thread.Yield();
}
}
catch (System.OperationCanceledException e)
{
Console.WriteLine(e.Message);
}
}
}