您當前位置：首頁 > 互聯(lián)網(wǎng) > 自己編寫的DAL三層代碼生成器

自己編寫的DAL三層代碼生成器

來源：程序員人生發(fā)布時間：2014-09-13 08:00:00 閱讀次數(shù)：3066次

（1）創(chuàng)建自己的解決方案目錄結(jié)構(gòu)如下：

（2）編寫代碼：

（要使用數(shù)據(jù)庫建議創(chuàng)建任意數(shù)據(jù)庫即可）

創(chuàng)建配置文件App.config代碼如下：

<?xml version="1.0"?> <configuration> <connectionStrings> <add name="connstr" connectionString="Data Source=.; Initial Catalog=HRMSYSDB;User ID=hrmsa;Password=你的數(shù)據(jù)庫密碼"/> </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{ 下邊的內(nèi)容即可</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()) { //只獲得表的架構(gòu)信息（列信息） 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("連接數(shù)據(jù)庫出錯！錯誤消息：" + 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 } //為例避免每次都輸入鏈接數(shù)據(jù)庫字符串將保存的代碼封裝到函數(shù)中 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) { //在每次加載的時候都要加載保存在文件夾內(nèi)的連接字符串 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的結(jié)束 //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結(jié)束 sb.AppendLine("}"); txtDALCode.Text = sb.ToString(); } //以數(shù)組形式返回列名 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; } //以數(shù)組形式返回@列名 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; } } } }

好了全部代碼都好了！點擊運行效果如下：