MySQL Metadata

http://www.devart.com/dotconnect/mysql/docs/MetaData.html

In this overload first parameter is name of a collection, and second parameter is the array of restrictions to be applied when querying information. These restrictions specify which subset of the collection will be returned. The restrictions can include, for example, the database name (in this case, only collection elements belonging to this database will be returned) or the mask for the name of collection elements (only the elements satisfying this mask will be returned). The quantity and description of restrictions allowed for each metadata collection are represented in the table here. Their number can also be obtained from the return of the GetSchema() method. If the second parameter is null/Nothing, it is ignored.
Instead of specifying the metadata collection name as a string constant, you may use members of System.Data.DbMetaDataCollectionNames and Devart.Data.MySql.MySqlMetadataCollectionNames as the first GetSchema argument values. The members of these classes are the string fields, each field stores the corresponding metadata collection name. It is recommended to use these fields rather than manually input the collection names manually as the string constants because in case of using these fields, you will find misspellings at compile-time, and intellisense will show you all the available metadata collection names.
GetSchema Method Reference
The following table provides detailed information on metadata collections that can be retrieved using the GetSchema method, and restrictions that can be applied for them. If the restriction name is not specified, a default value is used. For example, the default values for the database, table, and collection element name restrictions are "mysql", "user", and "%" accordingly. Note that the Database property of the connection string does not affect the GetSchema method, and the database restriction should be specified any time you are interested in some database other than "mysql". Some collections may be not supported in older server versions. If you try to get metadata for unsupported collection you will get exception with message "Collection not defined".

 

 

Collection Name	Number of restrictions	Remarks
Arguments	2	Returns list of arguments for stored procedures and functions on the server. Restrict it by database name and procedure name.
Columns	3	Returns list of columns, their type and some extra information. GetSchema("Columns") returns list of columns in the table "user" in database "mysql". Restricted by database name, GetSchema returns columns of table "user" if it can be found in the database. The second restriction is name of a table that GetSchema method should search in. At last, you can set column name pattern as described in "Tables" collection.
Databases	1	Lists all databases on the server. When restricted by name mask, returns all databases that match the mask.
DatasourceInformation	0	Returns information about the data source.
DataTypes	0	Returns information about data types supported by the data source.
ForeignKeys	3	Returns list of foreign keys in the database. The first restriction for this collection is name of a database. The second restriction is table name mask. The third restriction is the key pattern.
ForeignKeyColumns	2	Returns list of columns used by foreign keys in the database. Restrict it with database name and table name.
Functions	2	Returns list of stored functions on the server. Restrict it by database name and function name.
IndexColumns	4	Returns list of indexed columns in the database, their type and some extra information. Restrict it with database name, table name, index name pattern, and column name pattern.
Indexes	3	Returns list of indexes and their details. The first restriction is database name The second restriction is table name The last restriction is the index name pattern.
MetaDataCollections	0	Returns this list. Same as using GetSchema() method without parameters.
PrimaryKeys	2	Returns list of columns that participate in primary keys. The first restriction for this collection is name of a database. The second restriction is table name. The last restriction is the key name pattern.
Procedures	2	Returns list of stored procedures on the server. Restrict it by database name and procedure name.
ReservedWords	0	Lists all reserved words used in the server.
Restrictions	0	Returns list of possible restrictions and their default values for the metadata collections.
Tables	2	GetSchema("Tables") returns list of tables in "mysql" database. The first restriction for this collection is name of a database. If specified, the method returns all tables within the database. The second restriction is table name mask. You can use wildcards '%' (any number of characters) and '_' (one character) to retrieve names of tables that match the mask.
Triggers	2	Returns list of triggers in a database. The first restriction for this collection is database name pattern. The second restriction is trigger name pattern.
UDFs	1	Returns information about user-defined functions on the server. You can specify name of the function as restriction.
UniqueKeys	2	Returns list of columns that participate in unique keys. The first restriction for this collection is name of a database. The second restriction is table name. The last restriction is the key name pattern.
UserPrivileges	1	Lists all users and their privileges on the server. When restricted by username, returns information about specific user.
Users	1	Lists all users on the server. When restricted by username, returns information about specific user.
ViewColumns	3	Returns list of columns used by views in the database. Restrict it with database name, table name and column name.
Views	2	GetSchema("Views") returns list of views in "mysql" database. The first restriction for this collection is name of a database. The second restriction is view name mask.

 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using MySql.Data.MySqlClient; //5.0 读取MySql 4.1 using MySql.Data; //using MySql.Data.Entity; //using MySql.Data.Common; using MySql.Data.Types;     /*     MetaDataCollections DataSourceInformation DataTypes Restrictions ReservedWords Databases Tables Columns Users Foreign Keys IndexColumns Indexes  *  */ namespace MysqlBig {     /// <summary>     /// 20150330 Tables Columns Views     /// </summary>     public partial class GeneratorForm : Form     {         string connectionString = "server=192.168.20.165;database=attend;user=root;password=涂聚文;charset='utf-8';pooling=true;Port=3306;Allow Zero Datetime=true;";         /// <summary>         ///         /// </summary>         /// <returns></returns>         DataTable setDatat()         {             DataTable dt = new DataTable();             dt.Columns.Add("id", typeof(int));             dt.Columns.Add("name", typeof(string));             //dt.Rows.Add(1, "Procedures");             //dt.Rows.Add(2, "DataTypes");             //dt.Rows.Add(3, "Foreign Keys");             //dt.Rows.Add(4, "Databases");             //dt.Rows.Add(5, "dbo");             //dt.Rows.Add(6, "Arguments");             //dt.Rows.Add(7, "Collection Name");             //dt.Rows.Add(8, "DatasourceInformation");             //dt.Rows.Add(9, "MetaDataCollections");             //dt.Rows.Add(10, "ForeignKeyColumns");             //dt.Rows.Add(11, "Functions");             //dt.Rows.Add(12, "IndexColumns");             //dt.Rows.Add(13, "Indexes");             //dt.Rows.Add(14, "PrimaryKeys");             //dt.Rows.Add(15, "ReservedWords");             //dt.Rows.Add(16, "Restrictions");             //dt.Rows.Add(17, "Triggers");             //dt.Rows.Add(18, "UDFs");             //dt.Rows.Add(19, "UniqueKeys");             //dt.Rows.Add(20, "UserPrivileges");             //dt.Rows.Add(21, "Users");             //dt.Rows.Add(22, "ViewColumns");               //DataTable dt = connection.GetSchema("Tables", strRestricted);             dt.Rows.Add(23, "Tables");             dt.Rows.Add(24, "Columns");//表的列的详细，有主键TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE,COLUMN_KEY,EXTRA,PRIVILEGES,COLUMN_COMMENT             dt.Rows.Add(25, "Views");             dt.Rows.Add(26, "Indexes");//表的列             dt.Rows.Add(27, "IndexColumns");//主键               return dt;         }           /// <summary>         ///         /// </summary>         public GeneratorForm()         {             InitializeComponent();         }             /// <summary>         ///         /// </summary>         /// <param name="sender"></param>         /// <param name="e"></param>         private void GeneratorForm_Load(object sender, EventArgs e)         {             this.txtConnection.Text = connectionString;             this.comboBox1.DataSource = setDatat();             this.comboBox1.DisplayMember = "name";             this.comboBox1.ValueMember = "id";           }         /// <summary>         ///         /// </summary>         /// <param name="sender"></param>         /// <param name="e"></param>         private void button1_Click(object sender, EventArgs e)         {             bool bIsPrimary = false; ;             DataTable oSchemaIndexes;             DataTable oSchemaIndexColumns;             string strIndexName=string.Empty;             string strColumnName = string.Empty;             string strTable = "attendrecord"; //"             String[] strRestricted = new String[4] { null, null, strTable, null };                try             {                 using (MySqlConnection connection = new MySqlConnection(connectionString))                 {                     connection.Open();                     //DataTable dt = connection.GetSchema(this.comboBox1.Text.Trim());                     DataTable dt = connection.GetSchema(this.comboBox1.Text.Trim(), strRestricted);                     this.dataGridView1.DataSource = dt;                     this.textBox1.Text = GetColumnNames(dt);                              // DATABASE: Get the schemas needed.                     oSchemaIndexes = connection.GetSchema("Indexes", strRestricted);                     oSchemaIndexColumns = connection.GetSchema("IndexColumns", strRestricted);                       // Get the index name for the primary key.                     foreach (DataRow oRow in oSchemaIndexes.Rows)                     {                         // If we have a primary key, then we found what we want.                         strIndexName = oRow["INDEX_NAME"].ToString();                         bIsPrimary = (Boolean)oRow["PRIMARY"];                         if (true == bIsPrimary)                             break;                     }                       // If no primary index, bail.                     if (false == bIsPrimary)                     {                         //return null;                       }                     else                     {                         // Get the corresponding column name.                         foreach (DataRow oRow in oSchemaIndexColumns.Rows)                         {                             // Get the column name.                             if (strIndexName == (String)oRow["INDEX_NAME"])                             {                                 strColumnName = (String)oRow["COLUMN_NAME"];                                 break;                             }                         }                     }                     this.textBox1.Text = strColumnName;                   }             }             catch (MySqlException ex)             {                 ex.Message.ToString();             }             }           /// <summary>         ///         /// </summary>         /// <param name="table"></param>         /// <returns></returns>         public static string GetColumnNames(System.Data.DataTable table)         {             if (table != null)             {                 List<string> lstColumn = new List<string>();                   foreach (System.Data.DataColumn col in table.Columns)                 {                     lstColumn.Add(col.ColumnName);                 }                   return String.Join(",", lstColumn.ToArray());             }               return string.Empty;             //foreach (DataRow row in table.Rows)             //{             //    foreach (DataColumn column in table.Columns)             //    {             //        ColumnName = column.ColumnName;             //        ColumnData = row[column].ToString();             //    }             //}         }         /// <summary>         ///         /// </summary>         /// <param name="strTable"></param>         /// <returns></returns>         public String GetPrimaryKey(String strTable)         {             MySqlConnection connection = new MySqlConnection(connectionString);             try             {                 String strPrimaryKey = null;                 String[] strRestricted = new String[4] { null, null, strTable, null };                 DataTable oSchema = null;                     // Make sure that there is a connection.                 if (ConnectionState.Open != connection.State)                     connection.Open();                   // DATABASE: Get the schema                 oSchema = connection.GetSchema("Tables", strRestricted);                   // Extract the information related to the primary column, in the format "{System.Data.DataColumn[0]}"                 DataColumn[] oPrimaryKeys = oSchema.PrimaryKey;                   // Extract: Column Names                 foreach (DataRow oRow in oSchema.Rows)                 {                     // Get the column name.                     String strColumnName = oRow["COLUMN_NAME"].ToString();                 }                   return strPrimaryKey;             }               catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }               return null;         }     } }

　　数据类型:

bit	System.UInt64	16
blob	System.Byte[]	252
tinyblob	System.Byte[]	249
mediumblob	System.Byte[]	250
longblob	System.Byte[]	251
datae	System.DateTime	10
datetime	System.DateTime	12
timestamp	System.DateTime	7
time	System.String	11
char	System.String	254
varchar	System.String	253
set	System.String	248
enum	System.String	247
double	System.Double	5
float	System.Single	4
tinyint	System.SByte	1
smallint	System.Int16	2
int	System.Int32	3
year	System.Int32	13
mediumint	System.Int32	9
bgeint	System.Int64	8
decimal	System.Decimal	246
tiny int	System.Byte	501
smallint	System.UInt16	502
mediumint	System.UInt32	509
int	System.UInt32	503
begint	System.UInt64	508