//数据库操作
public static DataTable GetDataSet(string connStr, string cmdText,
params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
return dt;
}
}
}
//连接数据库
private void btnConnect_Click(object sender, EventArgs e)
{
clbTables.Items.Clear();
DataTable dt = GetDataSet(txtConnStr.Text,
"SELECT * FROM INFORMATION_SCHEMA.TABLES");
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
clbTables.Items.Add(row["TABLE_NAME"]);
}
}
else
{
ShowMessage("对不起,您选择的数据库暂无数据表!");
}
}
///
/// 把数据库类型转换为C#类型,模块化的开发方式
///
///
///
private static Type ToNetType(string dataType)
{
switch (dataType)
{
case "int":
return typeof(int);
case "nvarchar":
case "varchar":
case "char":
case "nchar":
case "text":
return typeof(string);
case "bit":
return typeof(bool);
case "datetime":
return typeof(DateTime);
case "uniqueidentifier":
return typeof(Guid);
default:
return typeof(Object);
}
}
//生成对应的层
private void btnGo_Click(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtpath.Text))
{
if (checkModel.Checked || checkDAL.Checked || checkBLL.Checked)
{
GeneratorArgs args = new GeneratorArgs();
args.ConnectionString = txtConnStr.Text;
args.OutputDir = txtpath.Text;
args.RootNamespace = txtnamespace.Text;
foreach (string tablename in clbTables.CheckedItems)
{
if (checkModel.Checked)
{
CreateModel(tablename, args);
}
if (checkDAL.Checked)
{
CreateDAL(tablename, args);
}
if (checkBLL.Checked)
{
CreateBLL(tablename, args);
}
}
ShowMessage("生成成功!请到 " + txtpath.Text + " 目录中查看..");
}
else
{
ShowMessage("生成失败:您还未选择要生成的层!");
}
}
else
{
ShowMessage("生成失败:您还未选择要输出的路径!");
}
}
//创建模型层
private void CreateModel(string tablename,GeneratorArgs args)
{
//查询数据库中所有的列
DataTable dtCols = GetDataSet(args.ConnectionString, "select * from INFORMATION_SCHEMA.COLUMNS whereTABLE_NAME=@tablename",
new SqlParameter("tablename", tablename));
//生成Model
StringBuilder sb = new StringBuilder();
sb.AppendLine("using System;");
sb.AppendLine("using System.Text;");
sb.AppendLine("using System.Data;");
sb.AppendLine("using System.Data.SqlClient;");
sb.AppendLine("using System.Collections.Generic;");
sb.AppendLine("");
sb.AppendLine("namespace " + args.RootNamespace + ".Model");
sb.AppendLine("{");
sb.AppendLine("public class " + tablename);
sb.AppendLine("{");
foreach (DataRow row in dtCols.Rows)
{
string colName = (string)row["Column_Name"];
string dataType = (string)row["Data_Type"];
Type dotnetType = ToNetType(dataType);
string netTypeName;
if (dotnetType.IsValueType)
{
netTypeName = dotnetType.ToString() + "?";
}
else
{
netTypeName = dotnetType.ToString();
}
sb.AppendLine("public " + netTypeName + " " + colName + " { get; set; }");
//MessageBox.Show((string)row["Column_Name"]+(string)row["Data_Type"]);
}
sb.AppendLine("}");
sb.AppendLine("}");
//在路径下建立Model文件夹
// string modelDir = Path.Combine(args.OutputDir, "Model1");
// string modelFile =Path.Combine(modelDir, tablename + ".cs");
// Directory.CreateDirectory(modelDir);
string modelFile = Path.Combine(args.OutputDir, tablename + ".cs");
File.WriteAllText(modelFile, sb.ToString());
MessageBox.Show(sb.ToString());
// File.WriteAllText(@"c:\" + tablename + ".cs", sb.ToString());
}
private static string[] GetCols(DataTable dtCols)
{
List list = new List();
foreach (DataRow row in dtCols.Rows)
{
string colName = Convert.ToString(row["Column_Name"]);
list.Add(colName);
}
return list.ToArray();
}
private static string[] GetColsWithoutId(DataTable dtCols)
{
List list = new List();
list.AddRange(GetCols(dtCols));
foreach (string colname in list.ToArray())
{
if (colname.Equals("id",
StringComparison.CurrentCultureIgnoreCase))
{
list.Remove(colname);
}
}
return list.ToArray();
}
private void CreateDAL(string tablename, GeneratorArgs args)
{
//获得所有列名
//做复杂功能点的时候先对一些条件做一些假设,把复杂问题简单化
//假设主键名就是Id,并且是int类型并自动增长
DataTable dtCols = GetDataSet(args.ConnectionString, "select * from INFORMATION_SCHEMA.COLUMNS whereTABLE_NAME=@tablename and Column_Name<>'Id'",
new SqlParameter("tablename", tablename));
StringBuilder sb = new StringBuilder();
sb.AppendLine("using System;");
sb.AppendLine("using System.Collections.Generic;");
sb.AppendLine("using System.Linq;");
sb.AppendLine("using System.Text;");
sb.AppendLine("using " + args.RootNamespace + ".Model;");
sb.AppendLine("using System.Data.SqlClient;");
sb.AppendLine("using System.Data;");
sb.AppendLine("");
sb.AppendLine("namespace " + args.RootNamespace + ".DAL");
sb.AppendLine("{");
sb.AppendLine("public partial class " + tablename + "DAL");
sb.AppendLine("{");
{
sb.AppendLine("public int AddNew(" + tablename + " model)");
sb.AppendLine("{");
string[] cols = GetColsWithoutId(dtCols);
string[] colParams = (from col in cols
select "@" + col).ToArray();
sb.AppendLine("string sql = \"insert into " + tablename +
"(" + string.Join(",", cols) +
") output inserted.id values(" +
string.Join(",", colParams) + ")\";");
sb.AppendLine("int id = (int)DBHelper.ExecuteCommand(sql");
foreach (string col in cols)
{
sb.AppendLine(",new SqlParameter(\"" + col + "\", model." + col
+ "==null?(object)DBNull.Value:model." + col + ")");
}
sb.AppendLine(");");
sb.AppendLine("return id;");
sb.AppendLine("}");
sb.AppendLine("");
}
{
sb.AppendLine("public int Update(" + tablename + " model)");
sb.AppendLine("{");
string[] cols = GetColsWithoutId(dtCols);
string[] colParams = (from col in cols
select col + "=@" + col).ToArray();
sb.AppendLine("string sql = \"update " + tablename + " set " +
string.Join(",", colParams) + " where id=@id\";");
sb.AppendLine("int rows = DBHelper.ExecuteCommand(sql");
foreach (string col in GetCols(dtCols))
{
sb.AppendLine(",new SqlParameter(\"" + col + "\", model." + col
+ "==null?(object)DBNull.Value:model." + col + ")");
}
sb.AppendLine(");");
sb.AppendLine("return rows;");
sb.AppendLine("}");
sb.AppendLine("");
}
{
sb.AppendLine("public bool Delete(int id)");
sb.AppendLine("{");
sb.AppendLine("int rows = DBHelper.ExecuteCommand(\"delete from" +
" " + tablename + " where id=@id\",");
sb.AppendLine("new SqlParameter(\"id\",id));");
sb.AppendLine("return rows > 0;");
sb.AppendLine("}");
sb.AppendLine("");
}
{
sb.AppendLine("private static " + tablename +
" ToModel(DataRow row)");
sb.AppendLine("{");
sb.AppendLine(tablename + " model = new "
+ tablename + "();");
foreach (DataRow row in dtCols.Rows)
{
string colName = Convert.ToString(row["Column_Name"]);
string dataType = Convert.ToString(row["Data_Type"]);
Type netType = ToNetType(dataType);
string netTypeName;
//处理可空数据类型
if (netType.IsValueType)
{
netTypeName = netType.ToString() + "?";
}
else
{
netTypeName = netType.ToString();
}
sb.AppendLine("model." + colName + " = row.IsNull(\"" + colName + "\")?null:(" +
netTypeName + ")row[\"" + colName + "\"];");
}
sb.AppendLine("return model;");
sb.AppendLine("}");
sb.AppendLine("");
}
{
sb.AppendLine("public " + tablename + " Get(int id)");
sb.AppendLine("{");
//sb.AppendLine("SqlDataReader reader = DBHelper.GetReader(\"select * from "
// + tablename + " where id=@id\",");
//sb.AppendLine("new SqlParameter(\"id\",id));");
//sb.AppendLine("if (reader.Read())");
//sb.AppendLine(tablename + " model = ToModel(row);");
//sb.AppendLine("else{");
//sb.AppendLine("reader.Close();");
//sb.AppendLine("}");
sb.AppendLine("DataTable dt = DBHelper.GetDataSet(\"select * from "
+ tablename + " where id=@id\",");
sb.AppendLine("new SqlParameter(\"id\",id));");
sb.AppendLine("if (dt.Rows.Count > 1)");
sb.AppendLine("{throw new Exception(\"more than 1 row was found\");}");
sb.AppendLine("if (dt.Rows.Count <= 0){return null;}");
sb.AppendLine("DataRow row = dt.Rows[0];");
sb.AppendLine(tablename + " model = ToModel(row);");
sb.AppendLine("return model;");
sb.AppendLine("}");
sb.AppendLine("");
}
{
sb.AppendLine("public IEnumerable<" + tablename
+ "> ListAll(){");
sb.AppendLine("List<" + tablename + "> list = new List<" +
tablename + ">();");
sb.AppendLine("DataTable dt = DBHelper.GetDataSet(\"select * from " +
tablename + "\");");
sb.AppendLine("foreach (DataRow row in dt.Rows){");
sb.AppendLine("list.Add(ToModel(row));}");
sb.AppendLine("return list;}");
}
sb.AppendLine("}");
sb.AppendLine("}");
//string dalDir = Path.Combine(args.OutputDir, "DAL");
//string dalFile =
// Path.Combine(dalDir, "DAL" + tablename + ".cs");
//Directory.CreateDirectory(dalDir);
string dalFile = Path.Combine(args.OutputDir, tablename + "Service.cs");
File.WriteAllText(dalFile, sb.ToString());
MessageBox.Show(sb.ToString());
}
public static void CreateBLL(string tablename, GeneratorArgs args)
{
DataTable dtCols = GetDataSet(args.ConnectionString,
"SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@tablename",
new SqlParameter("tablename", tablename));
StringBuilder sb = new StringBuilder();
sb.AppendLine("using System;");
sb.AppendLine("using System.Collections.Generic;");
sb.AppendLine("using System.Linq;");
sb.AppendLine("using System.Text;");
sb.AppendLine("using " + args.RootNamespace + ".Model;");
sb.AppendLine("using " + args.RootNamespace + ".DAL;");
sb.AppendLine("");
sb.AppendLine("namespace " + args.RootNamespace + ".BLL");
sb.AppendLine("{");
sb.AppendLine("partial class " + tablename + "BLL{");
sb.AppendLine("public int AddNew(" + tablename + " model){");
sb.AppendLine("return new " + tablename + "DAL().AddNew(model);");
sb.AppendLine("}");
sb.AppendLine("");
sb.AppendLine("public bool Delete(int id){");
sb.AppendLine("return new " + tablename + "DAL().Delete(id);");
sb.AppendLine("}");
sb.AppendLine("");
sb.AppendLine("public int Update(" + tablename + " model)");
sb.AppendLine("{");
sb.AppendLine("return new " + tablename + "DAL().Update(model);");
sb.AppendLine("}");
sb.AppendLine("");
sb.AppendLine("public " + tablename + " Get(int id)");
sb.AppendLine("{");
sb.AppendLine("return new " + tablename + "DAL().Get(id);");
sb.AppendLine("}");
sb.AppendLine("");
//IEnumerable 只读属性
sb.AppendLine("public IEnumerable<" + tablename + "> ListAll()");
sb.AppendLine("{");
sb.AppendLine("return new " + tablename + "DAL().ListAll();");
sb.AppendLine("}");
sb.AppendLine("}");
sb.AppendLine("}");
sb.AppendLine("");
//string bllDir = Path.Combine(args.OutputDir, "BLL");
//string bllFile =
// Path.Combine(bllDir, "BLL" + tablename + ".cs");
//Directory.CreateDirectory(bllDir);
string bllFile = Path.Combine(args.OutputDir, tablename + "Manager.cs");
File.WriteAllText(bllFile, sb.ToString());
MessageBox.Show(sb.ToString());
}
private void btnbrowse_Click(object sender, EventArgs e)
{
folderDialog = new FolderBrowserDialog();
if (folderDialog.ShowDialog() == DialogResult.OK)
{
txtpath.Text = folderDialog.SelectedPath;
}
}
private void ShowMessage(string msg)
{
txtstatus.AppendText(msg + "\r\n");
}
private void Form1_Load(object sender, EventArgs e)
{
ShowMessage("欢迎使用三层代码生成器!");
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
Settings.Default.Save();
}
}