界面
代码
【Form1】
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace 代码生成器
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
//for(int i=0;i<4;i++){
//}
string kongge1 = "\t";
string kongge2 = "\t\t";
string kongge3 = "\t\t\t";
string kongge4 = "\t\t\t\t";
StringBuilder SB = new StringBuilder();
#region 把数据库类型转化为.net类型
private static string ToNetType(string dataType)
{
switch (dataType)
{
case "int":
return "int";
break;
case "nvarchar":
case "varchar":
case "char":
case "nchar":
return "string";
case "bit":
return "bool";
case "datetime":
return "DateTime";
default:
return "object";
}
}
#endregion
#region 数据库连接操作
public DataTable ExecuteDataTable(string cmdText, params SqlParameter[] parameters)
//不能写成static
{
using (SqlConnection conn = new SqlConnection(txtConnStr.Text))
{
//WhetherCon(txtConnSr.Text);//待优化
/************此处写等待用户输入的代码********************/
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;
}
}
}
#endregion
#region "连接"按钮事件
private void btnConnect_Click(object sender, EventArgs e)
{
DataTable dt = ExecuteDataTable("select * from information_schema.tables");
foreach (DataRow row in dt.Rows)
{
string tablename = (string)row["TABLE_NAME"];
clbFile.Items.Add(tablename);
}
}
#endregion
#region "生成"按钮事件;生成文件选项
private void btnGo_Click(object sender, EventArgs e)
{
foreach (string tablename in clbFile.CheckedItems)
//遍历,获得用户所勾选的表的名字
{
if (checkBox1.Checked == true)/*★★*/
{
CreateModel(tablename);//生成model
SB.AppendLine("模型层生成完成\r\n");
}
if (checkBox2.Checked == true)
{
CreateDAL(tablename);//生成DAL
SB.AppendLine("数据访问层生成完成\r\n");
}
if (checkBox3.Checked == true)
{
CreateBLL(tablename);//生成BLL
SB.AppendLine("业务逻辑层生成完成\r\n");
}
txtShow.Text=SB.ToString();
}
}
#endregion
#region 生成Model
/// <summary>
/// 生成Model
/// </summary>
/// <param name="tablename"></param>
private void CreateModel(string tablename)
{
DataTable dtCols = ExecuteDataTable("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 System.Threading.Tasks;\r\n");
sb.AppendLine("namespace " + textBox1.Text.Trim()+ ".Model{");
sb.AppendLine(kongge1+"class "+tablename);
sb.AppendLine(kongge1+"{");
foreach(DataRow row in dtCols.Rows)
/*★参数中数据库类型和.net的数据类型之间的转换*/
//遍历每行,得到要用的参数,并赋给其它变量
{
string colName = (string)row["Column_Name"];
string dataType = (string)row["Data_Type"];
string netType = ToNetType(dataType);
sb.AppendLine(kongge2 + "public" + " " + netType + " " + colName + "{get;set;}");
//MessageBox.Show((string)row["Column_Name"] + (string)row["Data_Type"]);
}
sb.AppendLine(kongge1+"}");
sb.AppendLine( "}");
File.WriteAllText(textBox2.Text + @"\" + tablename + "Model.cs", sb.ToString());
}
#endregion
/*得到数据库表列名的第二种方式:*************************************/
//private static string[] GetColumnNames(DataTable table)
//{
// string[] colnames = new string[table.Columns.Count];
// for (int i = 0; i < table.Columns.Count; i++)
// {
// DataColumn dataCol = table.Columns[i];
// colnames[i] = dataCol.ColumnName;
// }
// return colnames;
//}
//private static string[] GetParamColumnNames(DataTable table){
// string[] colnames=new string[table.Columns.Count];
// for(int i=0;i<table.Columns.Count;i++){
// DataColumn dataCol=table.Columns[i];
// colnames[i]="@"+dataCol.ColumnName;
// }
// return colnames;
//}
/********************************************************************/
#region 提取方法:getTableFromDataBase()、 GetParametersFromTable()
private string[] getTableFromDataBase(string tablename)
{
//做复杂功能点的时候先对一些条件做一些假设,把问题逐步分解
//假设:主键名就是Id,并且是int类型、自动增长
DataTable dtCols = ExecuteDataTable("select * from information_schema.columns where Column_Name<>'Id' and table_name=@tablename", new SqlParameter("tablename", tablename));
string[] colnames = new string[dtCols.Rows.Count];
for (int i = 0; i < dtCols.Rows.Count; i++)//遍历数据表中的各行
{
DataRow row = dtCols.Rows[i];//得到一行
string colname = (string)row["Column_Name"];
//得到该行列为“Column_Name”信息并将其存于colname变量中
colnames[i] = colname;//▲colnames数组中存放的是数据表列名信息
}
return colnames;/*▲返回数组,可以直接返回数组名字*/
}
private string[] GetParametersFromTable(string tablename)//将列名数组变成DAL的sql语句的参数信息
{
DataTable dtCols = ExecuteDataTable("select * from information_schema.columns where Column_Name<>'Id' and table_name=@tablename", new SqlParameter("tablename", tablename));
string[] colnames = new string[dtCols.Rows.Count];
for (int i = 0; i < dtCols.Rows.Count; i++)
{
DataRow row = dtCols.Rows[i];
string colname = (string)row["Column_Name"];
colnames[i] = "@" + colname;
}
return colnames;
}
#endregion
#region 生成DAL:CreateDAL
/// <summary>
/// 生成DAL:CreateDAL
/// </summary>
/// <param name="tablename"></param>
private void CreateDAL(string tablename)
{
DataTable dtCols = ExecuteDataTable("select * from information_schema.columns where Column_Name<>'Id' and table_name=@tablename", new SqlParameter("tablename", tablename));
string[] parameters = GetParametersFromTable(tablename);
string[] colnames = getTableFromDataBase(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.AppendLine("using System.Linq;");
sb.AppendLine("using System.Text;");
sb.AppendLine("using System.Threading.Tasks;");
sb.AppendLine("using System.Windows.Forms;");
sb.AppendLine("using " + textBox1.Text.Trim() + ".Model;\r\n");
sb.AppendLine("namespace " + textBox1.Text.Trim() + ".DAL{");
sb.AppendLine(kongge1+"class "+tablename+"DAL{");
/************AddNew()方法***************/
sb.AppendLine(kongge2+"public int AddNew(" + tablename + " model){");
sb.Append(kongge3+"object obj = SqlHelper.ExecuteScalar(");
sb.Append("\"intsert into " + tablename + "(" + string.Join(",", colnames) + ") values (" + string.Join(",", parameters) + ");select @@identity\"");
/*@只能转义“\”,这里必须用“\”来转义*/
/*▲string.Join方法:第一个参数是分隔符;第二个参数参数字符串*/
/*▲拼:“(Age,Name) values(@Age,@Name)”形式的参数*/
//拼参数
foreach (string colname in colnames)
{
sb.Append(",new SqlParameter(\"" + colname + "\",model." + colname + ")");
}
sb.AppendLine(");");
sb.AppendLine(kongge3+"return Convert.ToInt32(obj);");
sb.AppendLine(kongge2+"}");
/************拼Delete()方法***************/
sb.AppendLine(kongge2+"public int Delete(int id){");
sb.Append(kongge3+"return SqlHelper.ExecuteNonQuery(");
sb.Append("\"delete from " + tablename + " where id=@id\",new SqlParameter(");
sb.AppendLine("\"id\",id));");//★字符串的拼接,这里有点麻烦要注意
sb.AppendLine(kongge2+"}");
/************拼Update()方法***************/
/*★拼:“Name=@Name,Age=@Age”形式的参数列表******/
sb.AppendLine(kongge2+"public int Update(" + tablename + " model){");
sb.Append(kongge3+"return SqlHelper.ExecuteNonQuery").Append("(\"update " + tablename + " set ");
foreach (string colname in colnames)
{
sb.Append(colname + "=@" + colname + ",");
}
sb.Append("where id=@id\"");
foreach (string colname in colnames)
{
sb.Append(",new SqlParameter(\"" + colname + "\",model." + colname + ")");
}
sb.AppendLine(");");
sb.AppendLine(kongge2+"}");
sb.AppendLine("\n");
/************拼Get()方法***************/
//▲where后面有bug,有个逗号不好处理
sb.AppendLine(kongge2+"public " + tablename + " Get(int id){");
sb.AppendLine(kongge3+"DataTable dt = SqlHelper.ExecuteDataTable(\"select * from " + tablename + " where id=@id\",new SqlParameter(\"id\",id));");
sb.AppendLine(kongge3+"if (dt.Rows.Count <= 0) {return null;}");
sb.AppendLine(kongge3+"else if(dt.Rows.Count==1){");
sb.AppendLine(kongge4+tablename + " model=new " + tablename + "();");
sb.AppendLine(kongge4+"DataRow row = dt.Rows[0];");
foreach (DataRow row in dtCols.Rows)
{
string colName = (string)row["Column_Name"];
string dataType = (string)row["Data_Type"];
string netType = ToNetType(dataType);
sb.AppendLine(kongge4+"model." + colName + "=(" + netType + ")row[\"" + colName + "\"];");
}
sb.AppendLine(kongge4+"return model;");
sb.AppendLine(kongge3+"}else{");
sb.AppendLine(kongge4+"throw new Exception(\"出现多条id值相同的数据\");");
sb.AppendLine(kongge3+"}");
sb.AppendLine(kongge2+"}");
sb.AppendLine(kongge1+"}");
sb.AppendLine("}");
//MessageBox.Show(sb.ToString());
File.WriteAllText(textBox2.Text + @"\" + tablename + "DAL.cs", sb.ToString());
}
#endregion
#region Default生成的BLL
private void CreateBLL(string 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.AppendLine("using System.Linq;");
sb.AppendLine("using System.Text;");
sb.AppendLine("using System.Threading.Tasks;");
sb.AppendLine("using " + tablename + ".DAL;");
sb.AppendLine("using " + tablename + ".Model;\r\n");
sb.AppendLine("namespace " + textBox1.Text.Trim()+ ".BLL;{");
sb.AppendLine(kongge1+"class " + tablename + "BLL{");
sb.AppendLine(kongge2+"pulic int AddNew(" + tablename + " model){");
sb.AppendLine(kongge3+"return new " + tablename + "DAL().AddNew(model);");
sb.AppendLine(kongge2+"}");
sb.AppendLine(kongge2+"pulic int Delete(int id){");//假设Id是主键且必须有的
sb.AppendLine(kongge3+"return new " + tablename + "DAL().Delete(id);");
sb.AppendLine(kongge2+"}");
sb.AppendLine(kongge2+"pulic int Update(" + tablename + " model){");
sb.AppendLine(kongge3+"return new " + tablename + "DAL().Update(model);");
sb.AppendLine(kongge2+"}");
sb.AppendLine(kongge2+"pulic int Get(int id){");
sb.AppendLine(kongge3+"return new " + tablename + "DAL().Get(model)");
sb.AppendLine(kongge2+"}");
sb.AppendLine(kongge1+"}");
sb.AppendLine("}");
File.WriteAllText(textBox2.Text + @"\" + tablename + "BLL.cs", sb.ToString());
}
#endregion
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
/*点击按钮弹出“保存对话框”***************************************/
private void btnRoutine_Click(object sender, EventArgs e)
{
//SaveFileDialog saveFileDialog = new SaveFileDialog();
//if (saveFileDialog.ShowDialog() == DialogResult.OK)
//{
// string localFilePath = saveFileDialog.FileName.ToString();//获得文件路径
// textBox2.Text = localFilePath;
// saveFileDialog.RestoreDirectory = true;//保存对话框是否记忆上次打开的目录
// // Directory.CreateDirectory(textBox2.Text);
//}
/*以上是保存对话框,下面是浏览对话框*/
FolderBrowserDialog folderBrowserDialog1 = new FolderBrowserDialog();
//使用文件对话框查找文件夹创建的路径
if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
{
string path = folderBrowserDialog1.SelectedPath; //获取用户选中路径
textBox2.Text = path; //显示路径
/*▲Bug:路径不能手写。因为先点击按钮执行该函数,后面改变TextBox与该函数无关*/
}
}
}
}
App.config
<?xml version="1.0" encoding="utf-8" ?> <configuration> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" /> </startup> <connectionStrings> <add name="connstr" connectionString="Data Source=.\;Initial Catalog=CallCenter;Integrated Security=True"/> </connectionStrings> </configuration