/// <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); }