public void TestTableCol()
{
TableCol tblcol = new TableCol();
Assert.AreEqual(1, TestUtils.RegexCount(tblcol.Top, "<*w:tr"));
Assert.AreEqual(2, TestUtils.RegexCount(tblcol.Middle, "<*w:tc>"));
Assert.AreEqual(1, TestUtils.RegexCount(tblcol.Middle,
"<w:t>[{]value[}]</w:t>")); // test placeholder
Assert.AreEqual(1, TestUtils.RegexCount(tblcol.Bottom, "</w:tr>"));
}
string GetEnumName(TableCol p_col)
{
if (!string.IsNullOrEmpty(p_col.Comments))
{
var match = Regex.Match(p_col.Comments, @"^#[^\s#]+");
if (match.Success)
{
return(match.Value.Trim('#'));
}
}
return("byte");
}
void AppendColumn(TableCol p_col, StringBuilder p_sb, bool p_isNew)
{
// 枚举类型特殊
bool isEnum = IsEnumCol(p_col);
string tpName = isEnum ? GetEnumName(p_col) : GetTypeName(p_col.Type);
p_sb.AppendLine();
AppendTabSpace(p_sb, 2);
p_sb.AppendLine("/// <summary>");
AppendTabSpace(p_sb, 2);
p_sb.Append("/// ");
if (isEnum)
{
p_sb.AppendLine(p_col.Comments.Substring(tpName.Length + 2));
}
else
{
p_sb.AppendLine(p_col.Comments);
}
AppendTabSpace(p_sb, 2);
p_sb.AppendLine("/// </summary>");
AppendTabSpace(p_sb, 2);
if (p_isNew)
{
p_sb.Append("new ");
}
p_sb.AppendLine($"public {tpName} {p_col.Name}");
AppendTabSpace(p_sb, 2);
p_sb.AppendLine("{");
AppendTabSpace(p_sb, 3);
p_sb.AppendLine($"get {{ return ({tpName})this[\"{p_col.Name}\"]; }}");
AppendTabSpace(p_sb, 3);
p_sb.AppendLine($"set {{ this[\"{p_col.Name}\"] = value; }}");
AppendTabSpace(p_sb, 2);
p_sb.AppendLine("}");
}
internal static TableSchema GetTableSchema(string p_tblName)
{
TableSchema schema = new TableSchema(p_tblName.ToLower());
foreach (var oc in AtModel.EachColumns(p_tblName.ToLower()))
{
TableCol col = new TableCol();
col.Name = oc.ColName;
col.Type = Table.GetColType(oc.DbType);
col.Length = oc.Length;
col.Nullable = oc.Nullable;
col.Comments = oc.Comments;
if (oc.IsPrimary)
{
schema.PrimaryKey.Add(col);
}
else
{
schema.Columns.Add(col);
}
}
return(schema);
}
/// <summary>
/// 加载表结构信息（已调整到最优）
/// </summary>
/// <returns>返回加载结果信息</returns>
public static string LoadSchema()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
var schema = new Dictionary <string, TableSchema>();
using (MySqlConnection conn = new MySqlConnection(MySqlAccess.DefaultConnStr))
{
conn.Open();
Database = conn.Database;
using (MySqlCommand cmd = conn.CreateCommand())
{
// 原来通过系统表information_schema.columns获取结构，为准确获取与c#的映射类型采用当前方式
// 所有表名
cmd.CommandText = $"SELECT table_name FROM information_schema.tables WHERE table_schema='{conn.Database}'";
List <string> tbls = new List <string>();
MySqlDataReader reader;
using (reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
// 表名小写
tbls.Add(reader.GetString(0).ToLower());
}
}
}
// 表结构
foreach (var tbl in tbls)
{
TableSchema tblCols = new TableSchema(tbl);
cmd.CommandText = $"SELECT * FROM {tbl} WHERE false";
ReadOnlyCollection <DbColumn> cols;
using (reader = cmd.ExecuteReader())
{
cols = reader.GetColumnSchema();
}
foreach (var colSchema in cols)
{
TableCol col = new TableCol();
col.Name = colSchema.ColumnName;
// 可为null的值类型
if (colSchema.AllowDBNull.HasValue && colSchema.AllowDBNull.Value && colSchema.DataType.IsValueType)
{
col.Type = typeof(Nullable <>).MakeGenericType(colSchema.DataType);
}
else
{
col.Type = colSchema.DataType;
}
// character_maximum_length
if (colSchema.ColumnSize.HasValue)
{
col.Length = colSchema.ColumnSize.Value;
}
if (colSchema.AllowDBNull.HasValue)
{
col.Nullable = colSchema.AllowDBNull.Value;
}
// 读取列结构
cmd.CommandText = $"SELECT column_default,column_comment FROM information_schema.columns WHERE table_schema='{conn.Database}' and table_name='{tbl}' and column_name='{colSchema.ColumnName}'";
using (reader = cmd.ExecuteReader())
{
if (reader.HasRows && reader.Read())
{
// 默认值
if (!reader.IsDBNull(0))
{
col.Default = reader.GetString(0);
}
// 字段注释
col.Comments = reader.GetString(1);
}
}
// 是否为主键
if (colSchema.IsKey.HasValue && colSchema.IsKey.Value)
{
tblCols.PrimaryKey.Add(col);
}
else
{
tblCols.Columns.Add(col);
}
}
schema[tbl] = tblCols;
}
// 取Db时间
cmd.CommandText = "select now()";
Kit.Now = (DateTime)cmd.ExecuteScalar();
}
}
stopwatch.Stop();
Schema = schema;
return($"加载 {Database} 的表结构用时 {stopwatch.ElapsedMilliseconds} 毫秒");
}
bool IsEnumCol(TableCol p_col)
{
return(p_col.Type == typeof(byte) &&
!string.IsNullOrEmpty(p_col.Comments) &&
Regex.IsMatch(p_col.Comments, @"^#[^\s#]+"));
}
