Mikel
初来咋到，小试了手写代码生成器，望大牛们指点，下面是成型效果图
需求：
1、采用ORM（对象映射关系）模式；
2、主要解决提供现有表结构，生成Model、DAL层；
不多说了，下面进入正题
/// <summary> /// 读取文件 /// </summary> /// <param name="fileName">文件名</param> /// <returns>返回文件信息</returns> private static string GetConfigFilePath(string fileName) { string currenctDir = AppDomain.CurrentDomain.BaseDirectory;//存放路径 string configFile = System.IO.Path.Combine(currenctDir, fileName); return configFile; }读取文件 |
窗体加载时，读取默认路径连接数据库文件信息
void MainWindow_Loaded(object sender, RoutedEventArgs e) { string strcon = File.ReadAllText(GetConfigFilePath("connstr.txt"), Encoding.Default);//字符串以默认标准格式读取 string[] str = strcon.Split(';'); for (int i = 0; i < str.Count(); i++) { switch (i) { case 0: txtDataSource.Text = str[i].Split('=')[1];//数据库地址 break; case 1: txtDatabase.Text = str[i].Split('=')[1];//数据库名称 break; case 2: txtUserid.Text = str[i].Split('=')[1];//用户 break; case 3: txtPassword.Password = str[i].Split('=')[1];//密码 break; default: break; } } }窗体加载 |
connstr.txt 文件信息
data source=10.10.198.111;database=systemconfig;user id=sa;password=saprivate void btnConnect_Click(object sender, RoutedEventArgs e) { try { //查询所有表名称 DataTable table = ExcuteDataTable(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"); tables = new List<string>(); for (int i = 0; i < table.Rows.Count; i++) { DataRow row = table.Rows[i]; tables.Add(row["TABLE_NAME"].ToString()); } if (tables != null && tables.Count > 0) { cmbTables.ItemsSource = TablesDesc(tables); cmbTables.IsEnabled = true; btnGenerateCode.IsEnabled = true; cmbTables.SelectedIndex = 0; } } catch (Exception exc) { MessageBox.Show("连接失败" + exc.Message); return; } string configFile = GetConfigFilePath("connstr.txt"); File.WriteAllText(configFile, strconn);//创建一个新的文件，如果存在则覆盖 }连接数据库private void btnGenerateCode_Click(object sender, RoutedEventArgs e) { string tableName = cmbTables.SelectedItem.ToString(); if (ckbTable.IsChecked == true)//判断是否指定表生成 { tableName = txtTableName.Text; if (string.IsNullOrEmpty(tableName)) { MessageBox.Show("请输入表名！"); return; } else { if (!tables.Contains(tableName)) { MessageBox.Show("您输入表名的不存在！"); return; } } } CreatModelCode(tableName);//生成Model文件 CreatDALCode(tableName);//生成DAL文件 }生成代码private void CreatModelCode(string tablename) { //根据表列名创建Model层属性 DataTable table = ExcuteDataTable("select top 0 * from " + tablename); string Retable = tablename; if (tablename.Contains("_")) { Retable = tablename.Split('_')[1]; } StringBuilder sb = new StringBuilder(); sb.AppendLine("using System;"); sb.AppendLine("using System.Collections.Generic;"); sb.AppendLine("using System.Text;"); sb.Append("public class ").AppendLine(Retable + "Dto").AppendLine("{"); foreach (DataColumn item in table.Columns) { sb.Append("public ").Append(RemoveSystem(GetDataType(item))). Append(" ").Append(item.ColumnName).AppendLine(" {get;set;}"); } sb.Append("}"); txtModelCode.Text = sb.ToString(); string configFile = GetConfigFilePath(Retable + "Dto.cs");//创建ModelDto文件 File.WriteAllText(configFile, sb.ToString()); }创建Mode层代码private DataTable ExcuteDataTable(string sql) { strconn = @"data source=" + txtDataSource.Text + ";database=" + txtDatabase.Text + ";user id=" + txtUserid.Text + ";password=" + txtPassword.Password; using (SqlConnection cnn = new SqlConnection(strconn))//连接数据库 { cnn.Open(); using (SqlCommand cmd = cnn.CreateCommand()) { cmd.CommandText = sql;//执行sql DataSet dataset = new DataSet(); SqlDataAdapter dapter = new SqlDataAdapter(cmd); dapter.FillSchema(dataset, SchemaType.Source); dapter.Fill(dataset);//将dataset添加到SqlDataAdapter容器中 return dataset.Tables[0]; } } }查询表信息/// <summary> /// 判断表中列是否为空处理，范围属性类型 /// </summary> /// <param name="column"></param> /// <returns></returns> private static string GetDataType(DataColumn column) { if (column.AllowDBNull && column.DataType.IsValueType) { return column.DataType + "?";//表字段为空，类属性中添加？ } else { return column.DataType.ToString(); } }根据表中各列字段类型返回model属性类型/// <summary> /// 剔除列类型中包含system.字符串 /// </summary> /// <param name="str"></param> /// <returns></returns> private static string RemoveSystem(string str) { if (str.Contains("System.")) { return str.Replace("System.", ""); } else { return str; } }剔除列类型中包含system.字符串private void CreatDALCode(string tablename) { DataTable table = ExcuteDataTable("select top 0 * from " + tablename); string Retable = GetTableName(tablename); StringBuilder sb = new StringBuilder(); sb.AppendLine("using System;"); sb.AppendLine("using System.Collections.Generic;"); sb.AppendLine("using System.Data;"); sb.AppendLine("using System.Data.SqlClient;"); sb.Append("public class ").AppendLine(Retable + "DAL").AppendLine("{"); //ToModel sb.Append("private static ").Append(Retable + "Dto ").AppendLine("ToModel(DataRow row)").AppendLine("{"); sb.Append(Retable + "Dto " + "dto").Append("=new ").AppendLine(Retable + "Dto();"); foreach (DataColumn column in table.Columns) { sb.Append("dto.").Append(column.ColumnName).Append("=(") .Append(RemoveSystem(GetDataType(column))).Append(")SqlHelper.FromDbValue(row[\"") .Append(column.ColumnName).AppendLine("\"]);"); } sb.AppendLine("return dto;"); sb.AppendLine("}"); //查询所有 sb.Append("public static List<").AppendLine(Retable + "Dto> ListALL()").AppendLine("{"); sb.Append("List<").AppendLine(Retable + "Dto> lst=new List<" + Retable + "Dto>();"); sb.Append("DataTable table = SqlHelper.ExecuteDataTable(\"select * from ").AppendLine(tablename + "\");"); sb.AppendLine("for (int i = 0; i < table.Rows.Count; i++)"); sb.AppendLine("{"); sb.AppendLine(Retable + "Dto dto = ToModel(table.Rows[i]);"); sb.AppendLine("lst.Add(dto);"); sb.AppendLine("}"); sb.AppendLine("return lst;"); sb.AppendLine("}"); //根据ID获取数据 sb.Append("public static ").Append(Retable + "Dto ").AppendLine("GetById(string id)"); sb.AppendLine("{"); sb.AppendLine("DataTable table = SqlHelper.ExecuteDataTable(\"select * from " + tablename + " where id=@id\", new SqlParameter(\"@id\",id));"); sb.AppendLine(Retable + "Dto dto=ToModel(table.Rows[0]);"); sb.AppendLine("return dto;"); sb.AppendLine("}"); //删除 sb.AppendLine("public static void Delete(string id)"); sb.AppendLine("{"); sb.AppendLine("SqlHelper.ExecuteNonQuery(\"delete from " + tablename + " where id=@id\", new SqlParameter(\"@id\",id));"); sb.AppendLine("}"); //停用（软删除） sb.AppendLine("public static void UnUser(string id)"); sb.AppendLine("{"); sb.AppendLine("SqlHelper.ExecuteNonQuery(\"update " + tablename + "set status=1 where id=@id\",new SqlParameter(\"@id\",id));"); sb.AppendLine("}"); //启用 sb.AppendLine("public static void User(string id)"); sb.AppendLine("{"); sb.AppendLine("SqlHelper.ExecuteNonQuery(\"update " + tablename + "set status=0 where id=@id\",new SqlParameter(\"@id\",id));"); sb.AppendLine("}"); //新增 sb.AppendLine("public static void Insert(" + Retable + "Dto dto)"); sb.AppendLine("{"); sb.AppendLine("SqlHelper.ExecuteNonQuery(\"insert into " + tablename + "(" + GetCoulmns(tablename) + ") values (" + GetValues(tablename) + ")\"," + GetSqlParameter(tablename, true) + ");"); sb.AppendLine("}"); //编辑 sb.AppendLine("public static void Update(" + Retable + "Dto dto)"); sb.AppendLine("{"); sb.AppendLine("SqlHelper.ExecuteNonQuery(\"update " + tablename + "set" + GetUpdateValues(tablename) + " where id=@id\"," + GetSqlParameter(tablename, false) + ");"); sb.AppendLine("}"); sb.AppendLine("}"); txtDALCode.Text = sb.ToString(); string configFile = GetConfigFilePath(Retable + "DAL.cs"); File.WriteAllText(configFile, sb.ToString()); }生成数据处理层代码private string GetUpdateValues(string tablename) { DataTable table = ExcuteDataTable("select top 0 * from " + tablename); string Retable = GetTableName(tablename); List<string> strs = new List<string>(); for (int i = 0; i < table.Columns.Count; i++) { if (table.Columns[i].ColumnName.ToLower() != "id" && table.Columns[i].ColumnName.ToLower() != "status")//id、status字段不做更新 { strs.Add(table.Columns[i].ColumnName + "=@" + table.Columns[i].ColumnName); } } return string.Join(",", strs); }编辑操作，更新列值private string GetCoulmns(string tablename) { DataTable table = ExcuteDataTable("select top 0 * from " + tablename); string Retable = GetTableName(tablename); List<string> strs = new List<string>(); for (int i = 0; i < table.Columns.Count; i++) { strs.Add(table.Columns[i].ColumnName); } return string.Join(",", strs); }获取表中所以列拼接private string GetValues(string tablename) { DataTable table = ExcuteDataTable("select top 0 * from " + tablename); string Retable = GetTableName(tablename); List<string> strs = new List<string>(); for (int i = 0; i < table.Columns.Count; i++) { if (table.Columns[i].ColumnName == "id") { strs.Add("newid()"); } else if (table.Columns[i].ColumnName.ToLower() == "status") { strs.Add("0"); } else { strs.Add("@" + table.Columns[i].ColumnName); } } return string.Join(",", strs); }获取更新或插入的列值private string GetSqlParameter(string tablename, bool isInsert) { DataTable table = ExcuteDataTable("select top 0 * from " + tablename); string Retable = GetTableName(tablename); List<string> strs = new List<string>(); for (int i = 0; i < table.Columns.Count; i++) { if (isInsert) { if (table.Columns[i].ColumnName.ToLower() != "id" && table.Columns[i].ColumnName.ToLower() != "status") { strs.Add("new SqlParameter(\"@" + table.Columns[i].ColumnName + "\", dto." + table.Columns[i].ColumnName + ")"); } } else { if (table.Columns[i].ColumnName.ToLower() != "status") { strs.Add("new SqlParameter(\"@" + table.Columns[i].ColumnName + "\", dto." + table.Columns[i].ColumnName + ")"); } } } return string.Join(",", strs); }获取SqlParameter参数集 |
其他忽略部分，作为优化用
/// <summary> /// 表名排序 /// </summary> /// <param name="s"></param> private List<string> TablesDesc(List<string> lsttable) { var query = from s in lsttable orderby s ascending select s; List<string> tables = new List<string>(); foreach (string item in query) { tables.Add(item); } return tables; } /// <summary> /// 获取表名后缀 /// </summary> /// <param name="tableName"></param> /// <returns></returns> private string GetTableName(string tableName) { if (!tableName.Contains("_")) { return tableName; } return tableName.Split('_')[1]; } |
