Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- using MySql.Data.MySqlClient;
- using System;
- using System.Collections.Generic;
- using System.ComponentModel;
- using System.Data;
- using System.Linq;
- using System.Text;
- using System.Threading;
- using System.Threading.Tasks;
- using System.Windows;
- namespace MySQL_DB_Listen_And_Notify_Demo
- {
- public class DbManager : ObservableObject, IDisposable
- {
- bool disposed = false;
- private MySqlConnection _conn;
- private CancellationTokenSource _cancel = new CancellationTokenSource();
- private Task _openAndPollTableChangeTask;
- private bool _isConnected;
- public bool IsConnected
- {
- get { return _isConnected; }
- set { SetField(ref _isConnected, value, "IsConnected"); }
- }
- private DataView _users;
- public DataView Users
- {
- get { return _users; }
- set
- {
- //SetField(ref _users, value, "Users");
- // class ref is the same when we update...
- // call the property change manually each time the value is set!
- _users = value;
- OnPropertyChanged("Users");
- }
- }
- public DataSet DBDataSet = new DataSet();
- public MySql.Data.MySqlClient.MySqlDataAdapter UsersDataAdapter;
- public DbManager(string connectionString)
- {
- _conn = new MySqlConnection(connectionString);
- _openAndPollTableChangeTask = Task.Factory.StartNew(async() =>
- {
- string users_checksum = "";
- string current_checksum = "";
- while (true)
- {
- if (_cancel.IsCancellationRequested) return;
- try
- {
- _conn.Open();
- IsConnected = _conn.State == System.Data.ConnectionState.Open;
- InitDB();
- MySql.Data.MySqlClient.MySqlCommand cmd = new MySqlCommand("CHECKSUM TABLE `users`", _conn);
- MySql.Data.MySqlClient.MySqlDataReader reader;
- while (true)
- {
- if (_cancel.IsCancellationRequested) return;
- if (!_isConnected) throw new Exception("Database disconnection detected...");
- using (reader = cmd.ExecuteReader())
- {
- var t = reader.ReadAsync();
- await t;
- if (t.Result)
- {
- current_checksum = reader.GetString(1);
- }
- }
- if (users_checksum != current_checksum)
- {
- await UsersDataAdapter.FillAsync(DBDataSet, "users");
- Users = DBDataSet.Tables["users"].DefaultView;
- users_checksum = current_checksum;
- }
- // wait 5sec or until cancel token is raised
- try { Task.Delay(5000, _cancel.Token).Wait(_cancel.Token); } catch { }
- }
- }
- catch (Exception ex)
- {
- var exxx = ex.Message;
- IsConnected = false;
- try { _conn.Close(); } catch { }
- }
- // retry in 5sec or quit on cancel token
- try { Task.Delay(5000, _cancel.Token).Wait(_cancel.Token); } catch { }
- }
- }, _cancel.Token, TaskCreationOptions.LongRunning, TaskScheduler.Default);
- }
- ~DbManager()
- {
- Dispose();
- }
- public void Dispose()
- {
- Dispose(true);
- GC.SuppressFinalize(this);
- }
- protected virtual void Dispose(bool disposing)
- {
- if (disposed)
- {
- return;
- }
- if (disposing)
- {
- try { _cancel.Cancel(); } catch { }
- try { _conn.Close(); } catch { }
- try { _conn.Dispose(); } catch { }
- }
- disposed = true;
- }
- public static async Task TestDb(string connectionString)
- {
- using (var conn = new MySqlConnection(connectionString))
- {
- await conn.OpenAsync();
- }
- }
- public static void ControlBlink(System.Windows.Controls.Control control, int delay, System.Windows.Media.Brush color1, System.Windows.Media.Brush color2, CancellationToken cancel)
- {
- Task.Run(async () =>
- {
- while (!cancel.IsCancellationRequested)
- {
- await Task.Delay(delay, cancel);
- if (cancel.IsCancellationRequested) return;
- App.Current.Dispatcher.Invoke(() => { control.Background = control.Background == color1 ? color2 : color1; });
- }
- });
- }
- public static string GetDatabaseConnectionString()
- {
- return string.Format("Server={0:s};Port={1:0};Database={2:s};Uid={3:s};Pwd={4:s};",
- Properties.Settings.Default.SqlServerHostname,
- Properties.Settings.Default.SqlServerPort,
- Properties.Settings.Default.SqlServerDbName,
- Properties.Settings.Default.SqlServerUserName,
- System.Text.ASCIIEncoding.UTF32.GetString(System.Convert.FromBase64String(Properties.Settings.Default.SqlServerUserPassword)));
- }
- //private async Task<System.Data.DataSet> GetUsers()
- //{
- // using (MySqlConnection conn = new MySqlConnection(GetDatabaseConnectionString()))
- // {
- // using (MySqlDataAdapter adapter = new MySqlDataAdapter())
- // {
- // string query = "SELECT * FROM `users` ORDER BY `name`";
- // adapter.SelectCommand = new MySqlCommand(query, conn);
- // System.Data.DataSet ds = new System.Data.DataSet();
- // await adapter.FillAsync(ds);
- // return ds;
- // }
- // }
- //}
- private void InitDB()
- {
- UsersDataAdapter = new MySqlDataAdapter();
- UsersDataAdapter.SelectCommand = new MySqlCommand("SELECT `id`,`name`,`birthdate` FROM `users`", _conn);
- UsersDataAdapter.InsertCommand = new MySqlCommand("INSERT INTO `users` (`name`, `birthdate`) VALUES (@name, @birthdate)", _conn);
- UsersDataAdapter.UpdateCommand = new MySqlCommand("UPDATE `users` SET `name` = @name, `birthdate` = @birthdate WHERE `id` = @id", _conn);
- UsersDataAdapter.DeleteCommand = new MySqlCommand("DELETE `users` WHERE `id` = @id", _conn);
- // activate primary key
- UsersDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement