昨天宿舍没电，终于有耐心花了两个小时完整写了一个简易的代码生成器，要去上课了，就随便说一下
1.运行环境 ： WIN7+.NET4.0+msSQLSEVER2008
2.生成代码模式： 三层中的两层 DAL+BLL 外加一个模型model
3.主要组成： 自己封装的一个SMO 数据库操作辅助类+耐心拼接字符串
4.缺陷：对SQL字符类型的转换只是列举了一部分，需要后期按照需要扩充
5.运行步骤：检索局域网内所有的SQL服务器-------》选择服务器----------》选择身份验证--------》加载数据库文件-------》--------》选择数据库名---------------》列遍历所选数据库显示所有表-------》自动生成连接字符串------》选择表文件-------》选择路径生成-------》生成代码--------》over!
6.所花时间：2小时
效果图：
代码如下： 由于博客园好像不支持500行以上的粘贴，我只能贴生成代码文件部分了，也是最简单的部分。
生成文件部分代码
#region model
/// <summary>
/// 生成Model
/// </summary>
/// <param name="TableName">被选中的表名</param>
/// <returns>model模型</returns>
private void CreateModel(string tableName, params string[] spacename)
{
string nameStr = "";
if (spacename.Length == 1)
{
nameStr = " Rohelm";
}
else
{
nameStr = spacename[0];
}
DataTable Tb_Info = ExecuteDataTable("select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tablename and Column_Name<>'id'", new SqlParameter("tablename", tableName));
//排除以"id"为主键的列,获取该表的所有信息
StringBuilder mod = new StringBuilder();
mod.AppendLine("namespace" + nameStr + ".Model");
mod.AppendLine("{");
mod.AppendLine(space3 + "class " + tableName);
mod.AppendLine(space3 + "{");
foreach (DataRow row in Tb_Info.Rows)
{
string col_name = row["column_name"].ToString();
string col_type = row["data_type"].ToString();
col_type = StrToCsharp(col_type);
mod.AppendLine(space6 + "public " + col_type + " " + col_name + "{get;set;}");
}
mod.AppendLine(space3 + "}");
mod.AppendLine("}");
string Fpath = Path.Combine(tb_path.Text, "Modal");
Directory.CreateDirectory(Fpath);
File.WriteAllText(tb_path.Text + @"\Modal\" + tableName + "model.cs", mod.ToString());
}
#endregion
#region DAL
/// <summary>
/// DAL
/// </summary>
/// <param name="TableName">表名</param>
private void CreateDAL(string tableName, params string[] spacename)
{
string nameStr = "";//命名空间
if (spacename.Length == 1)
{
nameStr = " Rohelm";
}
else
{
nameStr = spacename[0];
}
DataTable Tb_Info = ExecuteDataTable("select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tablename and Column_Name<>'id'", new SqlParameter("tablename", tableName));
string[] ColumnNames = new string[Tb_Info.Rows.Count];//保存所属表中的字段名
for (int i = 0; i < Tb_Info.Rows.Count; i++)
{
ColumnNames[i] = Tb_Info.Rows[i]["COLUMN_NAME"].ToString();////将循环获取的列名加入列名数组
}
string[] paramers = new string[Tb_Info.Rows.Count];//保存对应的参数
for (int i = 0; i < paramers.Length; i++)
{
paramers[i] = "@" + ColumnNames[i];
}
StringBuilder dal = new StringBuilder();
dal.AppendLine("using System;");
dal.AppendLine("using " + nameStr + ".Model;");
dal.AppendLine("using System.Data.SqlClient;");
dal.AppendLine("using System.Data;");
dal.AppendLine("using System.Collections.Generic;\n\n");
dal.AppendLine("namespace " + nameStr + ".DAL");
dal.AppendLine("{");
dal.AppendLine(space3 + "partial class " + tableName + "DAL");
dal.AppendLine(space3 + "{");
dal.AppendLine(space8 + "//添加操作");
dal.AppendLine(space8 + " public static int AddNew(" + tableName + " model)//返回新添加字段的主键");
dal.AppendLine(space8 + "{");
dal.AppendLine(space12 + " object obj= SQLHelper.ExecuteScalar(");
//需要获取表中的字段
dal.AppendLine("\"insert into " + tableName + "(" + string.Join(",", ColumnNames) + ") output inserted.id values(" + string.Join(",", paramers) + ")\"");
foreach (string colName in ColumnNames)
{
//,new SqlParameter("BillNo",model.BillNo)
dal.AppendLine(",new SqlParameter(\"" + colName + "\",model." + colName + ")");
}
dal.AppendLine(");");
dal.AppendLine(space12 + "return Convert.ToInt32(obj);\n");
dal.AppendLine(space8 + "}");
dal.AppendLine(space8 + "//删除操作");
dal.AppendLine(space8 + "public static int Delete(int id)");
dal.AppendLine(space8 + "{");
dal.AppendLine(space12 + "return SQLHelper.ExecuteNonQuery(\"delete from T_person where Id=@id\", new SqlParameter(\"id\", id));");
dal.AppendLine(space8 + "}");
dal.AppendLine(space8 + "//查询单个模型");
dal.AppendLine(space8 + "public static " + tableName + " Get" + tableName + "(int id)");
dal.AppendLine(space8 + "{");
dal.AppendLine(space12+ "DataTable dt=SQLHelper.ExecuteDataTable(\"select * from " + tableName + " where id=@id\",new SqlParameter(\"id\",id));");
dal.AppendLine(space12+"if (dt.Rows.Count <= 0)");
dal.AppendLine(space12+"{");
dal.AppendLine(space8+space8 + "return null;");
dal.AppendLine(space12 + "}");
dal.AppendLine(space12+" else if (dt.Rows.Count ==1)");
dal.AppendLine(space12+"{");
dal.AppendLine(space8+space8+ " DataRow row =dt.Rows[0];");
dal.AppendLine(space8+space8+tableName+" "+tableName.ToLower()+"=new "+ tableName.ToLower() + "();");
foreach (DataRow row in Tb_Info.Rows)
{
string col_name = row["column_name"].ToString();
string col_type = row["data_type"].ToString();
col_type = StrToCsharp(col_type);
dal.AppendLine(space8+space8+tableName.ToLower() + "." + col_name + "=(" + col_type + ")row[\"" + col_name + "\"];");
}
dal.AppendLine(space8+space8+"return person;");
dal.AppendLine(space12 + "}");
dal.AppendLine(space12 + "else");
dal.AppendLine(space12 + "{");
dal.AppendLine(space8+space8+"throw new Exception(\"未知数据错误！\");");
dal.AppendLine(space12 + "}");
dal.AppendLine(space8 + "}");
dal.AppendLine(space8 + "//遍历所有模型数据");
dal.AppendLine(space8 + "public static IEnumerable<" + tableName + "> GetAll" + tableName + "()");
dal.AppendLine(space8 + "{");
dal.AppendLine(space12+"DataTable dt = SQLHelper.ExecuteDataTable(\"select * from " + tableName + "\");");
dal.AppendLine(space12 + "List<" + tableName + "> list = new List<" + tableName + ">();");
dal.AppendLine(space12 + " foreach (DataRow row in dt.Rows)");
dal.AppendLine(space12 + "{");
dal.AppendLine(space8+space8+tableName+" "+tableName.ToLower() +"=new " + tableName.ToLower() + "();");
foreach (DataRow row in Tb_Info.Rows)
{
string col_name = row["column_name"].ToString();
string col_type = row["data_type"].ToString();
col_type = StrToCsharp(col_type);
dal.AppendLine(space8 + space8 + tableName.ToLower() + "." + col_name + "=(" + col_type + ")row[\"" + col_name + "\"];");
}
dal.AppendLine(space8 + space8 + "list.Add(" + tableName.ToLower() + ");");
dal.AppendLine(space12 + "}");
dal.AppendLine(space12 + " return list;");
dal.AppendLine(space8 + "}");
dal.AppendLine(space3 + "}");
dal.AppendLine("}");
string Fpath = Path.Combine(tb_path.Text, "DAL");
Directory.CreateDirectory(Fpath);
File.WriteAllText(tb_path.Text + @"\DAL\" + tableName + "DAL.cs", dal.ToString());
}
#endregion
#region BLL
private void CreateBLL(string tableName, params string[] spacename)
{
string nameStr = "";//命名空间
if (spacename.Length == 1)
{
nameStr = " Rohelm";
}
else
{
nameStr = spacename[0];
}
DataTable Tb_Info = ExecuteDataTable("select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tablename and Column_Name<>'id'", new SqlParameter("tablename", tableName));
string[] ColumnNames = new string[Tb_Info.Rows.Count];//保存所属表中的字段名
for (int i = 0; i < Tb_Info.Rows.Count; i++)
{
ColumnNames[i] = Tb_Info.Rows[i]["COLUMN_NAME"].ToString();////将循环获取的列名加入列名数组
}
string[] paramers = new string[Tb_Info.Rows.Count];//保存对应的参数
for (int i = 0; i < paramers.Length; i++)
{
paramers[i] = "@" + ColumnNames[i];
}
StringBuilder bll = new StringBuilder();
bll.AppendLine("using "+nameStr+".Model;");
bll.AppendLine("using "+nameStr+".DAL;");
bll.AppendLine("using System;");
bll.AppendLine("using System.Collections;");
bll.AppendLine("using System.Collections.Generic;\n\n");
bll.AppendLine("namespace 三层架构.BLL");
bll.AppendLine("{");
bll.AppendLine(space3 + "partial class "+tableName+"BLL");
bll.AppendLine(space3 + "{");
bll.AppendLine(space6 + "//增加字段");
bll.AppendLine(space6 + " public static int AddNew("+tableName+" model)//返回新添加字段的主键");
bll.AppendLine(space6 + "{");
bll.AppendLine(space8 + "return "+tableName+"DAL.AddNew(model);");
bll.AppendLine(space6 + "}");
bll.AppendLine(space6 + "//删除操作");
bll.AppendLine(space6 + "public static int Delete(int id)");
bll.AppendLine(space6 + "{");
bll.AppendLine(space8 + "return PersonDAL.Delete(id);");
bll.AppendLine(space6 + "}");
bll.AppendLine(space6 + "//更新数据");
bll.AppendLine(space6 + " public static int UpDate("+tableName+" model)");
bll.AppendLine(space6 + "{");
bll.AppendLine(space8 + "return "+tableName+".UpDate(model);");
bll.AppendLine(space6 + "}");
bll.AppendLine(space6 + " //查询一条数据");
bll.AppendLine(space6 + "public static "+tableName+" Get"+tableName+"(int id)");
bll.AppendLine(space6 + "{");
bll.AppendLine(space8 + "return PersonDAL.Get"+tableName+"(id);");
bll.AppendLine(space6 + "}");
bll.AppendLine(space6 + "//查询所有数据");
bll.AppendLine(space6 + "public static IEnumerable<"+tableName+"> GetAll"+tableName+"()");
bll.AppendLine(space6 + "{");
bll.AppendLine(space8 + "return "+tableName+"DAL.GetAll"+tableName+"();");
bll.AppendLine(space6 + "}");
bll.AppendLine(space3 + "}");
bll.AppendLine("}");
string Fpath = Path.Combine(tb_path.Text, "BLL");
Directory.CreateDirectory(Fpath);
File.WriteAllText(tb_path.Text + @"\BLL\" + tableName + "BLL.cs", bll.ToString());
}
#endregion