Verificarea constrangerilor dintr‐o baza de date MySql 

 

 

 

 

 

 

 

 

 

 

Student: Ababei Andrei 

Profesor : Mancas Adrian 

Aplicatia ruleaza pe platforma WinForms.NET si ofera posibilitatea verificarii integritatii tablelelor si coloanele verificand urmatoarele : 

●Fiecare tabel trebuie sa aiba o cheie primara ●Fiecare cheie primara sa aiba autoincrement ●Verifica ca fiecare camp folosit pe JOIN, WHERE, GROUP 

BY si ORDER BY sa fie indexate ●Campurile ce corespund paternului “tabel_id”, si creaza 

chei straine pe ele 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1. Conexiunea la baza de date public class MySqlConnector : IDisposable     {         private MySqlConnection _connection;         private MySqlConnectionString _connectionString;           public MySqlConnectionString ConnectionString         {             get             {                 return _connectionString;             }             set             {                 _connectionString = value;                 Initialize();             }         }           public MySqlConnection Connection => _connection;           public MySqlConnector(MySqlConnectionString connectionString)         {             _connectionString = connectionString;               try             {                 Initialize();             }             catch             {                 // ignored             }         }           private void Initialize()         {             _connection?.Dispose();               if(ConnectionString == null)                 throw new ArgumentNullException(nameof(ConnectionString));               _connection = new MySqlConnection(ConnectionString.ToString());         }           public IEnumerable<T> ExecuteQuery<T>(string query) where T : new () 

        {             if (_connection == null)                 throw new Exception("Connection is null");               try             {                 Func<CustomAttributeData, bool> mcap = ca => ca.AttributeType == typeof (MySqlColumnAttribute);                 var mlp = typeof(T).GetProperties()                                    .Where(p => p.CustomAttributes.Any(mcap))                                    .ToList();                    _connection.Open();                    MySqlCommand mcmd = new MySqlCommand(query, _connection);                 MySqlDataReader mdr = mcmd.ExecuteReader();                    while (mdr.Read())                 {                     T mitem = new T();                       foreach (PropertyInfo mpi in mlp)                     {                         var mca = mpi.GetCustomAttributes(typeof (MySqlColumnAttribute), true).FirstOrDefault() as MySqlColumnAttribute;                           if(mca == null)                             continue;                           mpi.SetValue(mitem, mdr[mca.Name]);                     }                       yield return mitem;                 }                   //close Data Reader                 mdr.Close();             }             finally             {                 _connection.Close();             }         }           public async Task<IEnumerable<T>> ExecuteQueryAsync<T>(string query) where T : new()         {             if (_connection == null)                 throw new Exception("Connection is null");   

            try             {                 Func<CustomAttributeData, bool> mcap = ca => ca.AttributeType == typeof (MySqlColumnAttribute);                 var mlp = typeof (T).GetProperties()                                     .Where(p => p.CustomAttributes.Any(mcap))                                     .ToList();                   await _connection.OpenAsync();                   MySqlCommand mcmd = new MySqlCommand(query, _connection);                 var mdr = await mcmd.ExecuteReaderAsync();                   IList<T> ml = new List<T>();                   while (await mdr.ReadAsync())                 {                     T mitem = new T();                       foreach (PropertyInfo mpi in mlp)                     {                         var mca = mpi.GetCustomAttributes(typeof (MySqlColumnAttribute), true).FirstOrDefault() as MySqlColumnAttribute;                           if (mca == null)                             continue;                           try                         {                             var mi = mdr[mca.Name];                             mpi.SetValue(mitem, mi != DBNull.Value ? mi : null);                         }                         catch (Exception mex)                         {                             Console.WriteLine(mex);                         }                     }                       ml.Add(mitem);                 }                   mdr.Close();                   return ml;             }             catch(Exception mex)             {                 Console.Write(mex.Message);                 return null;             } 

            finally             {                 await _connection.CloseAsync();             }         }         public bool Ping()         {             if (_connection == null)                 return false;             try             {                 _connection.Open();                 return _connection.Ping();             }             finally             {                 _connection.Close();               }         }           #region Implementation of IDisposable           public void Dispose()         {             if(_connection == null)                 return;               if(_connection.State != ConnectionState.Closed)                 _connection.Close();               _connection.Dispose();         }           #endregion           public async Task ExecuteNonQueryAsync(string query)         {             if (_connection == null)                 throw new Exception("Connection is null");               try             {                 await _connection.OpenAsync();                   MySqlCommand mcmd = new MySqlCommand(query, _connection);                 var mdr = await mcmd.ExecuteNonQueryAsync();                   mcmd.Dispose();             }             finally 

            {                 await _connection.CloseAsync();             }         }     } 

 

 

Clasa de Operatiuni pentru baza de date public static partial class DbHelper {     public static IEnumerable<MySqlDatabase> GetDatabases(MySqlConnectionString mySqlConnection)     {         string myConnectionString = $"SERVER={mySqlConnection.Server};" +                                     $"UID='{mySqlConnection.User}';" +                                     $"PASSWORD='{mySqlConnection.Pass}';";           MySqlConnection mc = new MySqlConnection(myConnectionString);         MySqlCommand mcmd = mc.CreateCommand();         mcmd.CommandText = "SHOW DATABASES;";         mc.Open();         MySqlDataReader mr = mcmd.ExecuteReader();         while (mr.Read())         {             for (int i = 0; i < mr.FieldCount; i++)                 yield return new MySqlDatabase(mr.GetValue(i).ToString());         }         mc.Close();     }       public static async Task<IEnumerable<MySqlDatabase>> GetDatabasesAsync(MySqlConnectionString mySqlConnection)     {         string myConnectionString = $"SERVER={mySqlConnection.Server};" +                                     $"UID='{mySqlConnection.User}';" +                                     $"PASSWORD='{mySqlConnection.Pass}';";           MySqlConnection mc = new MySqlConnection(myConnectionString);         MySqlCommand mcmd = mc.CreateCommand();         mcmd.CommandText = "SHOW DATABASES;";         await mc.OpenAsync();         DbDataReader mr = await mcmd.ExecuteReaderAsync();           IList<MySqlDatabase> ml = new List<MySqlDatabase>();   

        while (await mr.ReadAsync())         {             for (int i = 0; i < mr.FieldCount; i++)                 ml.Add(new MySqlDatabase((await mr.GetFieldValueAsync<object>(i)).ToString()));         }         await mc.CloseAsync();         return ml;       }       public static MySqlTestConnectionResult TestConnection(MySqlConnectionString mySqlConnection)     {         var mr = new MySqlTestConnectionResult();           try         {             if (!mySqlConnection.HaveMandatoryFields)             {                 mr.Result = MySqlTestConnectionResultStatus.BadStringConnectionFormated;                 mr.Exception = new Exception("Connection string is not corectly formated");                 mr.Message = mr.Exception.Message;             }             using (var mt = new MySqlConnector(mySqlConnection))                 mr.Result = mt.Ping()                                  ? MySqlTestConnectionResultStatus.Ok                                  : MySqlTestConnectionResultStatus.Unknow;         }         catch (MySqlException mex)         {             mr.Message = mex.Message;             mr.Exception = mex;             switch (mex.Number)             {                 case 0:                     mr.Result = MySqlTestConnectionResultStatus.ServerNotFound;                     break;                   case 1045:                     mr.Result = MySqlTestConnectionResultStatus.InvalidUserOrPassword;                     break;               }         }           return mr;     } 

      public static IEnumerable<MySqlTable> GetTables(MySqlConnectionString connectionString)     {         return GetTables(new MySqlConnector(connectionString));     }       public static IEnumerable<MySqlTable> GetTables(MySqlConnector connector)     {         return GetTables(connector?.Connection);     }       public static IEnumerable<MySqlTable> GetTables(MySqlConnection connection)     {         MySqlConnection mc = connection;           if (mc == null)             yield break;           try         {             MySqlCommand mcmd = mc.CreateCommand();             mcmd.CommandText = "SHOW TABLE STATUS;";             mc.Open();             MySqlDataReader mr = mcmd.ExecuteReader();             while (mr.Read())             {                 MySqlTableEngine me;                 Enum.TryParse(mr.GetValue(1)?.ToString() ?? string.Empty, true, out me);                 yield return new MySqlTable(mr.GetValue(0).ToString())                 {                     Engine = me                 };             }         }         finally         {             mc.Close();         }     }       public static async Task<IEnumerable<MySqlTable>> GetTablesAsync(MySqlConnectionString connectionString)     {         return await GetTablesAsync(new MySqlConnector(connectionString));     }       public static async Task<IEnumerable<MySqlTable>> GetTablesAsync(MySqlConnector connector) 

    {         return await GetTablesAsync(connector?.Connection);     }       public static async Task<IEnumerable<MySqlTable>> GetTablesAsync(MySqlConnection connection)     {         MySqlConnection mc = connection;         if (mc == null)             return Enumerable.Empty<MySqlTable>();           try         {             MySqlCommand mcmd = mc.CreateCommand();             mcmd.CommandText = "SHOW TABLE STATUS;";             await mc.OpenAsync();             var mr = await mcmd.ExecuteReaderAsync();             IList<MySqlTable> ml = new List<MySqlTable>();             while (await mr.ReadAsync())             {                 MySqlTableEngine me;                 Enum.TryParse((await mr.GetFieldValueAsync<object>(1))?.ToString() ?? string.Empty, true, out me);                 ml.Add(new MySqlTable((await mr.GetFieldValueAsync<object>(0))?.ToString())                 {                     Engine = me                 });             }               return ml;         }         finally         {             await mc.CloseAsync();         }     }       public static IEnumerable<MySqlView> GetViews(MySqlConnectionString connectionString)     {         return GetViews(new MySqlConnector(connectionString));     }       public static IEnumerable<MySqlView> GetViews(MySqlConnector connector)     {         return GetViews(connector?.Connection);     }       public static IEnumerable<MySqlView> GetViews(MySqlConnection connection) 

    {         MySqlConnection mc = connection;           if(mc == null)             yield break;           try         {             MySqlCommand mcmd = mc.CreateCommand();             mcmd.CommandText = $"SHOW FULL TABLES IN {connection.Database} WHERE TABLE_TYPE LIKE 'VIEW';";             mc.Open();             MySqlDataReader mr = mcmd.ExecuteReader();             while (mr.Read())                 yield return new MySqlView(mr.GetValue(0).ToString());         }         finally         {             mc.Close();         }     }       public static async Task<IEnumerable<MySqlView>> GetViewsAsync(MySqlConnectionString connectionString)     {         return await GetViewsAsync(new MySqlConnector(connectionString));     }       public static async Task<IEnumerable<MySqlView>> GetViewsAsync(MySqlConnector connector)     {         return await GetViewsAsync(connector?.Connection);     }       public static async Task<IEnumerable<MySqlView>> GetViewsAsync(MySqlConnection connection)     {         MySqlConnection mc = connection;         if (mc == null)             return Enumerable.Empty<MySqlView>();           try         {             MySqlCommand mcmd = mc.CreateCommand();             mcmd.CommandText = $"SHOW FULL TABLES IN {connection.Database} WHERE TABLE_TYPE LIKE 'VIEW';";             await mc.OpenAsync();             var mr = await mcmd.ExecuteReaderAsync();             IList<MySqlView> ml = new List<MySqlView>();             while (await mr.ReadAsync()) 

                ml.Add(new MySqlView((await mr.GetFieldValueAsync<object>(0))?.ToString()));               return ml;         }         finally         {             await mc.CloseAsync();         }     }       public static async Task<MysqlCheckDatabaseResult> CheckDatabase(MySqlConnectionString connectionString,  IEnumerable<MySqlTable> tables,                                                                      CheckDatabaseFlags flags,                                                                      Action<string> phaseAction,                                                                      Action<int> procWork)     {         MysqlCheckDatabaseResult mres = new MysqlCheckDatabaseResult();         IList<MysqlCheckTableResult> mtr = new List<MysqlCheckTableResult>();           MySqlConnector mcon = new  MySqlConnector(connectionString);           IEnumerable<MySqlTable> mtables = tables as IList<MySqlTable> ?? tables.ToList();         int mi = 0, mct = mtables.Count();           IList<MySqlView> mviews = null;           if (flags.HasFlag(CheckDatabaseFlags.Indexes))         {             mviews = (await GetViewsAsync(mcon)).ToList();             foreach (var mv in mviews)                 await mv.SetViewDefinitonAsync(mcon);         }           foreach (MySqlTable mtable in mtables)         {             procWork?.Invoke((int)(100f / mct * mi++));               mtr.Add(await GetTableResults(mcon, mtable, flags, phaseAction, mviews, mtables));         }           procWork?.Invoke(100);         phaseAction?.Invoke("Analyze is complete!"); 

        mres.Results = mtr;         return mres;     }       private static async Task<MysqlCheckTableResult> GetTableResults(MySqlConnector connector,                                                                       MySqlTable table,                                                                       CheckDatabaseFlags flags,                                                                      Action<string> phaseAction,  IEnumerable<MySqlView> views,  IEnumerable<MySqlTable> tables)     {         return new MysqlCheckTableResult         {             Table = table,             Result = await GetTableResult(connector, table, flags , phaseAction, views, tables)         };     }       private static async Task<IDictionary<CheckDatabaseFlags, IEnumerable<MysqlCheckResult>>> GetTableResult(MySqlConnector connector,                                                                          MySqlTable table,  CheckDatabaseFlags flags,                                                                          Action<string> phaseAction,   IEnumerable<MySqlView> mviews,   IEnumerable<MySqlTable> tables)     {         IDictionary<CheckDatabaseFlags, IEnumerable<MysqlCheckResult>> mres = new Dictionary<CheckDatabaseFlags, IEnumerable<MysqlCheckResult>>();           var mcolumns = (await table.GetColumnsAsync(connector)).ToList();         bool mhavePrimary = true;           if (flags.HasFlag(CheckDatabaseFlags.PrimaryKey) && !mcolumns.Any(c => c.IsPrimary))         {             mhavePrimary = false;               mres.Add(CheckDatabaseFlags.PrimaryKey, GetTableResultForPrimaryKey(table, mcolumns)); 

              phaseAction?.Invoke($"[{table.Name}] > Check primary key");         }         if (flags.HasFlag(CheckDatabaseFlags.AutoIncremet) && mhavePrimary && !(mcolumns.FirstOrDefault(c => c.IsPrimary)?.AutoIncrement ?? false))         {             mres.Add(CheckDatabaseFlags.PrimaryKey, GetTableResultForAutoIncrement(table, mcolumns));               phaseAction?.Invoke($"[{table.Name}] > Check auto incremet");         }         if(flags.HasFlag(CheckDatabaseFlags.Indexes))             mres.Add(CheckDatabaseFlags.Indexes, await GetTableResultForIndexes(table, mcolumns, connector, phaseAction, mviews));         if(flags.HasFlag(CheckDatabaseFlags.ForeignKeys))             mres.Add(CheckDatabaseFlags.ForeignKeys, await GetTableResultForForeignKeys(mcolumns, connector, tables, phaseAction));         return mres;     }       private static MysqlCheckResult GetMysqlCheckResult(string problem, string description = "", string query = "", WarningLevel wlevel = WarningLevel.None)     {         return new MysqlCheckResult         {             Problem = problem,             Description = description,             SolveQuery = query,             WarningLevel = wlevel         };     } } 

 

 

 

 

 

 

 

Clasa cu functii utile pentru UI public enum FieldError     {         Empty,         Format     }       public static class UiHelper     {         public static void ShowFieldError(string field, FieldError fieldError = FieldError.Empty, Form parentForm = null)         {             string mts = GetFieldErrorKey(fieldError);               ShowError(string.Format(Utils.Translate(mts), field), parentForm);         }           public static string GetFieldErrorKey(FieldError fieldError)         {             string mts = string.Empty;             if (fieldError == FieldError.Empty)                 mts = "error_field_cannot_be_empty_f";             else if (fieldError == FieldError.Empty)                 mts = "error_field_format_f";             return mts;         }           public static void ShowError(Exception exception, Form parentForm = null)         {             ShowError(exception?.Message, parentForm);         }           public static void ShowError(string text, Form parentForm = null)         {             MessageBox.Show(parentForm,                             text,                             Settings.Default.ApplicationName,                             MessageBoxButtons.OK,                             MessageBoxIcon.Error);         }           public static void ShowInfo(string text, Form parentForm = null)         {             MessageBox.Show(parentForm,                             text,                             Settings.Default.ApplicationName,                             MessageBoxButtons.OK,                             MessageBoxIcon.Information); 

        }     } 

 

Fereastra de creare a conexiunii 

 

Fereastra de selectare a tabelelor pentru analiza 

 

 

 

 

 

 

 

 

 

 

Fereastra de selecare a operatiunilor 

 

 

 

 

 

 

 

 

 

Selectarea operatiunilor de executie