/// <summary>
/// 获取表的所有字段名及字段类型
/// </summary>
public List <Dictionary <string, string> > GetAllColumns(string tableName)
{
string connectionString = ConfigurationManager.ConnectionStrings["MySqlConnection"].ToString();
int start = connectionString.IndexOf("database=") + 9;
int end = connectionString.IndexOf("user id=");
string owner = connectionString.Substring(start, end - start).Replace(";", "").ToUpper();
MySqlHelper dbHelper = new MySqlHelper();
DataTable dt = dbHelper.Query(string.Format(@"
select *
from INFORMATION_SCHEMA.Columns
where table_name='{0}'
and table_schema='{1}'", tableName, owner));
List <Dictionary <string, string> > result = new List <Dictionary <string, string> >();
foreach (DataRow dr in dt.Rows)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
dic.Add("columns_name", dr["COLUMN_NAME"].ToString());
dic.Add("notnull", dr["IS_NULLABLE"].ToString() == "NO" ? "1" : "0");
dic.Add("comments", dr["COLUMN_COMMENT"].ToString());
string dataType = dr["COLUMN_TYPE"].ToString();
int pos = dataType.IndexOf("(");
if (pos != -1)
{
dataType = dataType.Substring(0, pos);
}
dic.Add("data_type", dataType);
dic.Add("data_scale", dr["CHARACTER_MAXIMUM_LENGTH"].ToString());
dic.Add("data_precision", dr["NUMERIC_SCALE"].ToString());
if (dr["COLUMN_KEY"].ToString() == "PRI")
{
dic.Add("constraint_type", "P");
}
else
{
dic.Add("constraint_type", "");
}
result.Add(dic);
}
return(result);
}
/// <summary>
/// 获取所有表信息
/// </summary>
public List <Dictionary <string, string> > GetAllTables()
{
string connectionString = ConfigurationManager.ConnectionStrings["MySqlConnection"].ToString();
int start = connectionString.IndexOf("database=") + 9;
int end = connectionString.IndexOf("user id=");
string owner = connectionString.Substring(start, end - start).Replace(";", "").ToUpper();
MySqlHelper dbHelper = new MySqlHelper();
DataTable dt = dbHelper.Query(string.Format(@"
SELECT TABLE_NAME as TABLE_NAME,TABLE_COMMENT as COMMENTS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{0}'", owner));
List <Dictionary <string, string> > result = new List <Dictionary <string, string> >();
foreach (DataRow dr in dt.Rows)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
dic.Add("table_name", dr["TABLE_NAME"].ToString());
dic.Add("comments", dr["COMMENTS"].ToString());
result.Add(dic);
}
return(result);
}
