1、先看界面效果
根据默认的连接字符串查询出系统有多少表,在下拉框中加载出来,然后点击生成代码,生成数据模型类
2、后端代码实现
private void button1_Click(object sender, EventArgs e)
{
comTable.Items.Clear();
DataTable table = GetTable();
foreach (DataRow row in table.Rows)
{
comTable.Items.Add(row[0]);
}
}
private void Form1_Load(object sender, EventArgs e)
{
txtConn.Text = SqlHelper.connStr;//获取数据库的连接字符串
}
/// <summary>
/// 查询数据库下的所有表名
/// </summary>
/// <returns></returns>
private DataTable GetTable()
{
string sql = "Select TABLE_NAME FROM MVCCRUD.INFORMATION_SCHEMA.TABLES Where TABLE_TYPE='BASE TABLE'";
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(SqlHelper.connStr))
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
/*while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}*/
dt.Load(reader);
}
}
return dt;
}
private void btnCode_Click(object sender, EventArgs e)
{
string tableName = (string)comTable.SelectedItem;
if (tableName == null)
{
MessageBox.Show("请选择要生成的表");
return;
}
txtModeCode.Font = new Font("Consolas", 14);
CreateModelCode(tableName);
}
private void CreateModelCode(string tableName)
{
string sql = "select top 0 * from "+tableName;//查询表字段
DataTable dt = SqlHelper.ExecuteQuery(sql);
StringBuilder sb = new StringBuilder();
tableName = tableName.TrimStart('T').TrimStart('_'); //清除表名特定的字符
sb.Append("public class ").Append(tableName).AppendLine("{");
foreach (DataColumn columns in dt.Columns)//循环表的列
{
string columnsDataType = GetDataTypeName(columns);
sb.Append(" ").Append("public ").Append(columnsDataType).Append(" ").Append(columns).AppendLine(" { get; set; }");
}
sb.AppendLine("}");
txtModeCode.Text = sb.ToString();
}
/// <summary>
/// 进行可空类型的处理
/// </summary>
/// <param name="columns"></param>
/// <returns></returns>
private static string GetDataTypeName(DataColumn column)
{
//判断列是否是值类型,是否为空
string columnType = "";
if (column.AllowDBNull && column.DataType.IsValueType)//值类型为空
{
columnType = column.DataType.ToString().Replace("System.Int32", "int").Replace("System.Boolean","bool");
columnType += "?";
return columnType;
}
else if (column.AllowDBNull && (!column.DataType.IsValueType))//字符串类型为空
{
columnType = column.DataType.ToString().Replace("System.String", "string");
//columnType += "?"; string类型可以为null
return columnType;
}
else
{
columnType = column.DataType.ToString().Replace("System.Int32", "int").Replace("System.Boolean", "bool").Replace("System.String", "string");
return columnType;
}
}
这样就自动生成模型类的代码。