context: using MySql.Data.MySqlClient;
  using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Data.Common;
  using System.Text;
  using System.Text.RegularExpressions;
  namespace Cdev.Frameworks.SchemaExplorer
  {
  	public class MySqlSchemaProvider : IDbSchemaProvider
  	{
          private string _connectionString = string.Empty;
  		public string Name
  		{
  			get
  			{
                  return "MySqlSchemaProvider";
  			}
  		}
  		public string Description
  		{
  			get
  			{
                  return "MySqlSchemaProvider";
  			}
  		}
  		public string ConnectionString
  		{
  			get
  			{
  				return this._connectionString;
  			}
  		}
  		public bool EditorAvailable
  		{
  			get
  			{
  				return true;
  			}
  		}
  
   
  		public string GetDatabaseName(string connectionString)
  		{
              Regex regex = new Regex("(Database|Catalog)\\W*=\\W*(?<database>[^;]*)", RegexOptions.IgnoreCase);
  			Match match = regex.Match(connectionString);
  			string result;
  			if (match.Success)
  			{
  				result = match.Groups["database"].ToString();
  			}
  			else
  			{
  				result = connectionString;
  			}
  			return result;
  		}
  		public string GetViewText(string connectionString, ViewSchema view)
  		{
  			StringBuilder stringBuilder = new StringBuilder();
  			string commandText = string.Format("SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", view.Database.Name, view.Name);
  			using (DbConnection dbConnection = MySqlSchemaProvider.CreateConnection(connectionString))
  			{
  				dbConnection.Open();
  				DbCommand dbCommand = dbConnection.CreateCommand();
  				dbCommand.CommandText = commandText;
  				dbCommand.Connection = dbConnection;
  				using (IDataReader dataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection))
  				{
  					while (dataReader.Read())
  					{
  						stringBuilder.Append(dataReader.GetString(0));
  					}
  					if (!dataReader.IsClosed)
  					{
  						dataReader.Close();
  					}
  				}
  				if (dbConnection.State != ConnectionState.Closed)
  				{
  					dbConnection.Close();
  				}
  			}
  			return stringBuilder.ToString();
  		}
  		public TableSchema[] GetTables(string connectionString, DatabaseSchema database)
  		{
  			string commandText = string.Format("SELECT TABLE_NAME, '' OWNER, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{0}' AND TABLE_TYPE = 'BASE TABLE' ORDER BY 1", database.Name);
  			List<TableSchema> list = new List<TableSchema>();
  			using (DbConnection dbConnection = MySqlSchemaProvider.CreateConnection(connectionString))
  			{
  				dbConnection.Open();
  				DbCommand dbCommand = dbConnection.CreateCommand();
  				dbCommand.CommandText = commandText;
  				dbCommand.Connection = dbConnection;
  				using (IDataReader dataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection))
  				{
  					while (dataReader.Read())
  					{
  						DateTime dateCreated = (!dataReader.IsDBNull(2)) ? dataReader.GetDateTime(2) : DateTime.MinValue;
  						list.Add(new TableSchema(database, dataReader.GetString(0), dataReader.GetString(1), dateCreated));
  					}
  					if (!dataReader.IsClosed)
  					{
  						dataReader.Close();
  					}
  				}
  				if (dbConnection.State != ConnectionState.Closed)
  				{
  					dbConnection.Close();
  				}
  			}
  			return list.ToArray();
  		}
  		public ViewSchema[] GetViews(string connectionString, DatabaseSchema database)
  		{
  			string commandText = string.Format("SELECT TABLE_NAME, '' OWNER, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{0}' AND TABLE_TYPE = 'VIEW' ORDER BY 1", database.Name);
  			List<ViewSchema> list = new List<ViewSchema>();
  			using (DbConnection dbConnection = MySqlSchemaProvider.CreateConnection(connectionString))
  			{
  				dbConnection.Open();
  				DbCommand dbCommand = dbConnection.CreateCommand();
  				dbCommand.CommandText = commandText;
  				dbCommand.Connection = dbConnection;
  				using (IDataReader dataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection))
  				{
  					while (dataReader.Read())
  					{
  						DateTime dateCreated = (!dataReader.IsDBNull(2)) ? dataReader.GetDateTime(2) : DateTime.MinValue;
  						list.Add(new ViewSchema(database, dataReader.GetString(0), dataReader.GetString(1), dateCreated));
  					}
  					if (!dataReader.IsClosed)
  					{
  						dataReader.Close();
  					}
  				}
  				if (dbConnection.State != ConnectionState.Closed)
  				{
  					dbConnection.Close();
  				}
  			}
  			return list.ToArray();
  		}
  		public CommandSchema[] GetCommands(string connectionString, DatabaseSchema database)
  		{
  			string commandText = string.Format("SELECT ROUTINE_NAME, '' OWNER, CREATED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '{0}' AND ROUTINE_TYPE = 'PROCEDURE' ORDER BY 1", database.Name);
  			List<CommandSchema> list = new List<CommandSchema>();
  			using (DbConnection dbConnection = MySqlSchemaProvider.CreateConnection(connectionString))
  			{
  				dbConnection.Open();
  				DbCommand dbCommand = dbConnection.CreateCommand();
  				dbCommand.CommandText = commandText;
  				dbCommand.Connection = dbConnection;
  				using (IDataReader dataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection))
  				{
  					while (dataReader.Read())
  					{
  						list.Add(new CommandSchema(database, dataReader.GetString(0), dataReader.GetString(1), dataReader.GetDateTime(2)));
  					}
  					if (!dataReader.IsClosed)
  					{
  						dataReader.Close();
  					}
  				}
  				if (dbConnection.State != ConnectionState.Closed)
  				{
  					dbConnection.Close();
  				}
  			}
  			return list.ToArray();
  		}
  		public DataTable GetTableData(string connectionString, TableSchema table)
  		{
  			string commandText = string.Format("SELECT * FROM {0}", table.Name);
  			DataSet dataSet = new DataSet();
  			using (DbConnection dbConnection = MySqlSchemaProvider.CreateConnection(connectionString))
  			{
  				dbConnection.Open();
  				DbCommand dbCommand = dbConnection.CreateCommand();
  				dbCommand.CommandText = commandText;
  				dbCommand.Connection = dbConnection;
  				dataSet = this.ConvertDataReaderToDataSet(dbCommand.ExecuteReader());
  				if (dbConnection.State != ConnectionState.Closed)
  				{
  					dbConnection.Close();
  				}
  			}
  			DataTable result;
  			if (dataSet.Tables.Count > 0)
  			{
  				result = dataSet.Tables[0];
  			}
  			else
  			{
  				result = new DataTable(table.Name);
  			}
  			return result;
  		}
  		public DataTable GetViewData(string connectionString, ViewSchema view)
  		{
  			string commandText = string.Format("SELECT * FROM {0}", view.Name);
  			DataSet dataSet = new DataSet();
  			using (DbConnection dbConnection = MySqlSchemaProvider.CreateConnection(connectionString))
  			{
  				dbConnection.Open();
  				DbCommand dbCommand = dbConnection.CreateCommand();
  				dbCommand.CommandText = commandText;
  				dbCommand.Connection = dbConnection;
  				dataSet = this.ConvertDataReaderToDataSet(dbCommand.ExecuteReader());
  				if (dbConnection.State != ConnectionState.Closed)
  				{
  					dbConnection.Close();
  				}
  			}
  			DataTable result;
  			if (dataSet.Tables.Count > 0)
  			{
  				result = dataSet.Tables[0];
  			}
  			else
  			{
  				result = new DataTable(view.Name);
  			}
  			return result;
  		}
  		public ColumnSchema[] GetTableColumns(string connectionString, TableSchema table)
  		{
              string commandText = string.Format("SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, CASE IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END IS_NULLABLE, COLUMN_TYPE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' ORDER BY ORDINAL_POSITION", table.Database.Name, table.Name);
  			List<ColumnSchema> list = new List<ColumnSchema>();
  			using (DbConnection dbConnection = MySqlSchemaProvider.CreateConnection(connectionString))
  			{
  				dbConnection.Open();
  				DbCommand dbCommand = dbConnection.CreateCommand();
  				dbCommand.CommandText = commandText;
  				dbCommand.Connection = dbConnection;
  				using (IDataReader dataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection))
  				{
  					while (dataReader.Read())
  					{
  						string @string = dataReader.GetString(0);
  						string string2 = dataReader.GetString(1);
  						long num = (!dataReader.IsDBNull(2)) ? dataReader.GetInt64(2) : 0L;
  						byte precision = (byte)((!dataReader.IsDBNull(3)) ? dataReader.GetInt32(3) : 0);
  						int scale = (!dataReader.IsDBNull(4)) ? dataReader.GetInt32(4) : 0;
  						bool allowDBNull = !dataReader.IsDBNull(5) && dataReader.GetBoolean(5);
  						string string3 = dataReader.GetString(6);
                          string description=dataReader.GetString(7);
  						int size = (num < 2147483647L) ? ((int)num) : 2147483647;
  						bool isUnsigned = string3.IndexOf("unsigned") > -1;
  						DbType dbType = MySqlSchemaProvider.GetDbType(string2, isUnsigned);
                          list.Add(new ColumnSchema(table, @string, dbType, string2, size, precision, scale, allowDBNull) { Description = description });
  					}
  					if (!dataReader.IsClosed)
  					{
  						dataReader.Close();
  					}
  				}
  				if (dbConnection.State != ConnectionState.Closed)
  				{
  					dbConnection.Close();
  				}
  			}
  			return list.ToArray();
  		}
  		public ViewColumnSchema[] GetViewColumns(string connectionString, ViewSchema view)
  		{
  			string commandText = string.Format("SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, CASE IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END IS_NULLABLE, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'ORDER BY ORDINAL_POSITION", view.Database.Name, view.Name);
  			List<ViewColumnSchema> list = new List<ViewColumnSchema>();
  			using (DbConnection dbConnection = MySqlSchemaProvider.CreateConnection(connectionString))
  			{
  				dbConnection.Open();
  				DbCommand dbCommand = dbConnection.CreateCommand();
  				dbCommand.CommandText = commandText;
  				dbCommand.Connection = dbConnection;
  				using (IDataReader dataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection))
  				{
  					while (dataReader.Read())
  					{
  						string @string = dataReader.GetString(0);
  						string string2 = dataReader.GetString(1);
  						long num = (!dataReader.IsDBNull(2)) ? dataReader.GetInt64(2) : 0L;
  						byte precision = (byte)((!dataReader.IsDBNull(3)) ? dataReader.GetInt32(3) : 0);
  						int scale = (!dataReader.IsDBNull(4)) ? dataReader.GetInt32(4) : 0;
  						bool allowDBNull = !dataReader.IsDBNull(5) && dataReader.GetBoolean(5);
  						string string3 = dataReader.GetString(6);
  						int size = (num < 2147483647L) ? ((int)num) : 2147483647;
  						bool isUnsigned = string3.IndexOf("unsigned") > -1;
  						DbType dbType = MySqlSchemaProvider.GetDbType(string2, isUnsigned);
  						list.Add(new ViewColumnSchema(view, @string, dbType, string2, size, precision, scale, allowDBNull));
  					}
  					if (!dataReader.IsClosed)
  					{
  						dataReader.Close();
  					}
  				}
  				if (dbConnection.State != ConnectionState.Closed)
  				{
  					dbConnection.Close();
  				}
  			}
  			return list.ToArray();
  		}
  		public PrimaryKeySchema GetTablePrimaryKey(string connectionString, TableSchema table)
  		{
  			string commandText = string.Format("SELECT t1.CONSTRAINT_NAME, t1.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2  ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA  AND t2.TABLE_NAME = t1.TABLE_NAME  AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME WHERE t1.TABLE_SCHEMA = '{0}' AND t1.TABLE_NAME = '{1}' AND t2.CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY t1.ORDINAL_POSITION", table.Database.Name, table.Name);
  			DataSet dataSet = new DataSet();
  			using (DbConnection dbConnection = MySqlSchemaProvider.CreateConnection(connectionString))
  			{
  				dbConnection.ConnectionString = connectionString;
  				dbConnection.Open();
  				DbCommand dbCommand = dbConnection.CreateCommand();
  				dbCommand.CommandText = commandText;
  				dbCommand.Connection = dbConnection;
  				dataSet = this.ConvertDataReaderToDataSet(dbCommand.ExecuteReader());
  				if (dbConnection.State != ConnectionState.Closed)
  				{
  					dbConnection.Close();
  				}
  			}
  			PrimaryKeySchema result;
  			if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0)
  			{
  				string name = dataSet.Tables[0].Rows[0]["CONSTRAINT_NAME"].ToString();
  				string[] array = new string[dataSet.Tables[0].Rows.Count];
  				for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
  				{
  					array[i] = dataSet.Tables[0].Rows[i]["COLUMN_NAME"].ToString();
  				}
  				result = new PrimaryKeySchema(table, name, array);
  			}
  			else
  			{
  				result = null;
  			}
  			return result;
  		}
  		public TableKeySchema[] GetTableKeys(string connectionString, TableSchema table)
  		{
  			List<TableKeySchema> list = new List<TableKeySchema>();
  			list.AddRange(this.GetMyTableKeys(connectionString, table));
  			list.AddRange(this.GetOthersTableKeys(connectionString, table));
  			return list.ToArray();
  		}
  		private List<TableKeySchema> GetMyTableKeys(string connectionString, SchemaObjectBase table)
  		{
  			string commandText = string.Format("SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1 WHERE t1.TABLE_SCHEMA = '{0}' AND t1.TABLE_NAME = '{1}'  AND CONSTRAINT_TYPE = 'FOREIGN KEY'", table.Database.Name, table.Name);
  			string commandText2 = string.Format("SELECT t1.CONSTRAINT_NAME, t1.COLUMN_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT,  t1.REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2  ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA  AND t2.TABLE_NAME = t1.TABLE_NAME  AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME WHERE t1.TABLE_SCHEMA = '{0}' AND t1.TABLE_NAME = '{1}'  AND t2.CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY t1.CONSTRAINT_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT", table.Database.Name, table.Name);
  			DataSet dataSet;
  			using (DbConnection dbConnection = MySqlSchemaProvider.CreateConnection(connectionString))
  			{
  				dbConnection.Open();
  				using (DbCommand dbCommand = dbConnection.CreateCommand())
  				{
  					dbCommand.CommandText = commandText;
  					dbCommand.Connection = dbConnection;
  					dataSet = this.ConvertDataReaderToDataSet(dbCommand.ExecuteReader());
  				}
  				if (dbConnection.State != ConnectionState.Closed)
  				{
  					dbConnection.Close();
  				}
  			}
  			using (DbConnection dbConnection2 = MySqlSchemaProvider.CreateConnection(connectionString))
  			{
  				dbConnection2.Open();
  				using (DbCommand dbCommand2 = dbConnection2.CreateCommand())
  				{
  					dbCommand2.CommandText = commandText2;
  					dbCommand2.Connection = dbConnection2;
  					dataSet.Tables.Add(this.ConvertDataReaderToDataTable(dbCommand2.ExecuteReader()));
  				}
  				if (dbConnection2.State != ConnectionState.Closed)
  				{
  					dbConnection2.Close();
  				}
  			}
  			List<TableKeySchema> list = new List<TableKeySchema>();
  			if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0)
  			{
  				dataSet.Relations.Add("Contraint_to_Keys", dataSet.Tables[0].Columns["CONSTRAINT_NAME"], dataSet.Tables[1].Columns["CONSTRAINT_NAME"]);
  
gt: 				foreach (DataRow dataRow in dataSet.Tables[0].Rows)