您当前位置：首页 > 互联网 > 自己编写的DAL三层代码生成器

自己编写的DAL三层代码生成器

来源：程序员人生发布时间：2014-09-13 08:00:00 阅读次数：2913次

（1）创建自己的解决方案目录结构如下：

（2）编写代码：

（要使用数据库建议创建任意数据库即可）

创建配置文件App.config代码如下：

<?xml version="1.0"?> <configuration> <connectionStrings> <add name="connstr" connectionString="Data Source=.; Initial Catalog=HRMSYSDB;User ID=hrmsa;Password=你的数据库密码"/> </connectionStrings> <appSettings> <add key="passwordSalt" value="love?P3@9"/> <add key="aaa" value="333"/> </appSettings> </configuration>

MainWindow.xaml代码如下：（在MainWindow.xaml下把Grid里边的代码换一下就好了）

MainWindow.xaml.cs代码如下：

namespace MyCodeGen //这里要把命名空间改成自己的也就是自己生成的只需粘贴<span style="font-family: Arial, Helvetica, sans-serif;">public partial class MainWindow : Window{ 下边的内容即可</span> { /// <summary> /// MainWindow.xaml 的交互逻辑 /// </summary> public partial class MainWindow : Window { public MainWindow() { InitializeComponent(); } private DataTable ExecuteDataTable(string sql) { using (SqlConnection conn = new SqlConnection(txtConnStr.Text)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { //只获得表的架构信息（列信息） cmd.CommandText = sql; DataSet ds = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.FillSchema(ds, SchemaType.Source);//获得表信息必须要写 adapter.Fill(ds); return ds.Tables[0]; } } } private void btnConnect_Click(object sender, RoutedEventArgs e) { DataTable table; try { table = ExecuteDataTable(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"); } catch (SqlException sqlex) { MessageBox.Show("连接数据库出错！错误消息：" + sqlex.Message); return; } string[] tables = new string[table.Rows.Count]; for (int i = 0; i < table.Rows.Count; i++) { DataRow row = table.Rows[i]; tables[i] = (string)row["TABLE_NAME"]; } cmbTables.ItemsSource = tables; cmbTables.IsEnabled = true; btnGenerateCode.IsEnabled = true; //把连接字符串记录到文件中，避免用户每次都需要输入连接字符串 //将连接字符串保存起来 string configFile = GetConfigFilePath(); File.WriteAllText(configFile, txtConnStr.Text); //除非真的有捕获异常的需要，否则不要try...catch } //为例避免每次都输入链接数据库字符串将保存的代码封装到函数中 private static string GetConfigFilePath() { string currenctDir = AppDomain.CurrentDomain.BaseDirectory; string configFile = System.IO.Path.Combine(currenctDir, "connstr.txt"); return configFile; } private void Window_Loaded(object sender, RoutedEventArgs e) { //在每次加载的时候都要加载保存在文件夹内的连接字符串 string configFile = GetConfigFilePath(); txtConnStr.Text = File.ReadAllText(configFile); } private void btnGenerateCode_Click(object sender, RoutedEventArgs e) { string tablename = (string)cmbTables.SelectedItem; if (tablename == null) { MessageBox.Show("请选择要生成的表"); return; } CreateModelCode(tablename); CreateDALCode(tablename); } private void CreateModelCode(string tablename) { DataTable table = ExecuteDataTable("select top 0 * from " + tablename); //在大量的字符串拼接的时候使用stringbuilder（） StringBuilder sb = new StringBuilder(); sb.Append("public class ").AppendLine(tablename).AppendLine("{"); foreach (DataColumn column in table.Columns) { string columnDataType = GetDataTypeName(column); //判断类型是否可空 sb.Append(" ").Append("public ").Append(columnDataType).Append(" ") .Append(column.ColumnName).AppendLine("{get;set;}"); } sb.AppendLine("}"); txtModelCode.Text = sb.ToString(); } //进行可空类型的处理 private static string GetDataTypeName(DataColumn column) { //如果列允许为null，并且列在C#中的类型是不可为空的（值类型ValueType） if (column.AllowDBNull && column.DataType.IsValueType) { return column.DataType + "?"; } else { return column.DataType.ToString(); } } //下边是生成DALcode部分 private void CreateDALCode(string tablename) { DataTable table = ExecuteDataTable("select top 0 * from " + tablename); StringBuilder sb = new StringBuilder(); sb.Append("public class ").Append(tablename).AppendLine("DAL").AppendLine("{"); //tomodel开始 sb.Append(" ").Append("private ").Append(tablename) .AppendLine(" ToModel(DataRow row)").Append(" ").AppendLine("{"); sb.Append(" ").Append(tablename).AppendLine(" model = new " + tablename + "();"); foreach (DataColumn column in table.Columns) { //无论列是否允许为空，都进行判断DbNull的处理（省事） //model.Id = (Guid)SqlHelper.FromDbValue(row["Id"]); sb.Append(" ").Append("model.").Append(column.ColumnName).Append("=(") .Append(GetDataTypeName(column)).Append(")SqlHelper.FromDbValue(row["") .Append(column.ColumnName).AppendLine(""]);"); } sb.Append(" ").AppendLine("return model;"); sb.AppendLine("}"); //tomodel的结束 //listall开始 sb.Append("public IEnumerable<").Append(table) .AppendLine("> ListAll()").AppendLine("{"); sb.Append(" ").Append("List<").Append(tablename).Append("> list=new List<") .Append(tablename).AppendLine(">();"); sb.Append("DataTable dt = SqlHelper.ExecuteDataTable("") .Append("select * from " + tablename).AppendLine("");"); sb.AppendLine("foreach (DataRow row in dt.Rows)"); sb.Append(tablename).AppendLine(" model=ToModel(row);"); sb.AppendLine("list.Add(model);}"); sb.AppendLine("}"); //生成器要求列名必须是Id，类型必须是Guid //Insert开始 //public void Insert(Operator op) sb.Append("public void Insert(") .Append(tablename).AppendLine(" model){"); //SqlHelper.ExecuteNonQuery(@"insert into T_Operator( sb.Append("SqlHelper.ExecuteNonQuery(@"") .Append("insert into ").Append(tablename).AppendLine("("); string[] colNames = GetColumnNames(table); sb.AppendLine(string.Join(",", colNames)); string[] colParamNames = GetParamColumnNames(table); sb.Append("values(").AppendLine(string.Join(",", colParamNames)); sb.AppendLine("}"); //Insert结束 sb.AppendLine("}"); txtDALCode.Text = sb.ToString(); } //以数组形式返回列名 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; } } }

SqlHelper.cs代码如下：（命名空间里边的代码）

namespace MyCodeGen { static class SqlHelper { //app.config文件的继承： public static readonly string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString; //别忘了添加引用 System.configuration 然后在解析到步骤：右键点击引用--添加引用--.NET--找到System.configuration 确定即可 public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(parameters); return cmd.ExecuteNonQuery(); } } } public static object ExecuteScalar(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(parameters); return cmd.ExecuteScalar(); } } } public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(parameters); DataSet dataset = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dataset); return dataset.Tables[0]; } } } public static object FromDbValue(object value) { if (value == DBNull.Value) { return null; } else { return value; } } public static object ToDbValue(object value) { if (value == null) { return DBNull.Value; } else { return value; } } } }

好了全部代码都好了！点击运行效果如下：