代码生成器原理及示例

大家好，又见面了，我是你们的朋友全栈君。

      在三层架构中Model、DAL(Data Access Layer)、BLL层有必要分开，其中有些代码可以由代码生成器生成。虽然网络已经有成熟的代码生成器，但是第三方代码生成器在实际应用场景中，生成的代码经常还需要在其基础上修改。修改其代码就不如修改代码生成器本身。所以掌握代码生成器的编写方法、原理还是很有必要的。

      下面通过一个例子简要介绍代码生成器编写过程，并给出一个具备基本功能的范例雏形。以抛砖引玉。

成果展示截图：

图 代码生成器界面截图

DAL结果截图

Model截图

后台代码展示如下：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;


namespace 我的代码生成器
{
    public partial class FormCodeGenerater : Form
    {
        public FormCodeGenerater()
        {
            InitializeComponent();
        }
     

        /// <summary>
        /// 使用ConfigurationManager读取App.config中的字符串。
        /// </summary>
        static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;

        #region 方法
        /// <summary>
        /// 获取数据库中所有的字段名称
        /// </summary>
        private List<string> GetFields(string _connectString, string _tableName)
        {
            List<string> tableNameList = new List<string>();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from " + _tableName;
                    DataSet ds = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.FillSchema(ds, SchemaType.Source);
                    adapter.Fill(ds);

                    DataTable table = ds.Tables[0];
                    foreach (DataColumn col in table.Columns)
                    {
                        string tmpString = col.ColumnName + "," + col.AllowDBNull + "," + col.DataType;
                        tableNameList.Add(tmpString);
                    }
                }
            }
            return tableNameList;
        }

        /// <summary>
        /// 获取数据库表名
        /// </summary>
        /// <param name="_connectString"></param>
        /// <returns></returns>
        private List<string> GetTableNames(string _connectString)
        {
            List<string> resultList = new List<string>();
            string databaseName = GetDataBaseNameFromConnectString(_connectString);//todo 取出数据库名称
            //string.take
            string sql = "SELECT Name FROM " + databaseName + "..SysObjects Where XType='U' ORDER BY Name";
            DataTable dt = ExecuteDataTable(_connectString, sql);
            //todo装载resultList
            foreach (DataRow row in dt.Rows)
            {
                string tmpString = row[0].ToString();
                resultList.Add(tmpString);
            }
            return resultList;
        }

        /// <summary>
        /// 从连接字符串读取出数据库名称
        /// </summary>
        /// <returns></returns>
        private string GetDataBaseNameFromConnectString(string _connectString)
        {
            try
            {
                string[] strArray = _connectString.Split(';');
                string resultStr = strArray[1].Substring(16);
                return resultStr;
            }
            catch { return string.Empty; }
        }

        /// <summary>
        /// 执行sql语句，返回datatable
        /// </summary>
        /// <param name="_connectString"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        private DataTable ExecuteDataTable(string _connectString, string sql)
        {
            using (SqlConnection conn = new SqlConnection(_connectString))
            {
                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);

                    DataTable table = ds.Tables[0];
                    return table;
                }
            }
        }

        /// <summary>
        ///  如果列允许为null，并且列在c#中的类型是不可空的（值类型）
        /// </summary>
        /// <param name="_column"></param>
        /// <returns></returns>
        private static string GetDataTypeName(DataColumn _column)
        {
            //如果列允许为null，并且列在c#中的类型是不可空的（值类型）
            if (_column.AllowDBNull && _column.DataType.IsValueType)
            {
                return _column.DataType + "?";
            }
            else
            {
                return _column.DataType.ToString();
            }
        }
        #endregion


        #region 事件
        private void FormCodeGenerater_Load(object sender, EventArgs e)
        {
            textBoxConnStr.Text = connStr;
        }

        private void buttonConnect_Click(object sender, EventArgs e)
        {
            try
            {
                //绑定Combobox
                comboBoxTables.Items.Clear();
                List<string> TableNameList = GetTableNames(connStr);
                comboBoxTables.Items.AddRange(TableNameList.ToArray());
                if (comboBoxTables.Items.Count > 0)
                    comboBoxTables.SelectedIndex = 0;
                buttonGenerateCode.Enabled = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void buttonGenerateCode_Click(object sender, EventArgs e)
        {
            string tableName = (string)comboBoxTables.SelectedItem;
            if (tableName == null)
            {
                MessageBox.Show("请选择要生成的表");
                return;
            }
            //大量字符串拼接，需要使用StringBuilder效率更高
            //Append()拼接
            //AppendLine()后面加一行

            CreateModelCode(connStr,tableName);
            CreateDALCode(connStr, tableName);
        }

        private void CreateModelCode(string _connStr,string _tableName)
        {
            DataTable table = ExecuteDataTable(_connStr, "select top 0 * from " + _tableName);
            StringBuilder sb = new StringBuilder();
            sb.Append("public class ").Append(_tableName).AppendLine("{");
            foreach (DataColumn col in table.Columns)
            {
                sb.Append("     public ").Append(GetDataTypeName(col)).Append(" ")
                    .Append(col.ColumnName).AppendLine("{get;set;}");             
            }
            sb.AppendLine("}");

            richTextBoxModelCode.Text = sb.ToString();
        }

        /// <summary>
        /// 创建DAL代码
        /// </summary>
        /// <param name="_tableName"></param>
        private void CreateDALCode(string _connStr, string _tableName)
        {
            string blanksString = "   ";
            DataTable table = ExecuteDataTable(_connStr, "select top 0 * from " + _tableName);
            StringBuilder sb = new StringBuilder();
            sb.Append("public class ").Append(_tableName).AppendLine("DAL").AppendLine("{");
 
            //ToModel开始
            sb.Append("   private ").Append(_tableName).AppendLine(" ToModel(DataRow row)").Append(blanksString + "{");
            sb.Append(blanksString + _tableName).AppendLine(" model=new " + _tableName + "();");
            foreach (DataColumn col in table.Columns)
            {
                //无论列是否为空，都进行判断DbNull的处理
                //model.Id=(Guid)SqlHelper.FromDbValue(row["Id"]);
                // \表示转义字符
                sb.Append(blanksString+"model.").Append(col.ColumnName).Append("=(").Append(GetDataTypeName(col)).Append(")SqlHelper.FromDbValue(row[\"").Append(col.ColumnName).AppendLine("\"]);");
            }
            sb.Append("return model;").AppendLine("}");

            //ToModel的结束
            //ListAll开始
            //public IEnumerable<Department> ListAll()
            sb.Append("public IEnumerable<").Append(_tableName).AppendLine("> ListAll()");
            sb.AppendLine("{");
            sb.Append(blanksString+"List<").Append(_tableName).Append("> list=new List<").Append(_tableName).AppendLine(">();");
            sb.Append(blanksString+"DataTable dt=SqlHelper.ExecuteDataTable(\"").Append("select * from "+_tableName).AppendLine("\");");
            sb.AppendLine("foreach(DataRow row in dt.Rows)");
            sb.Append("{");
            sb.Append(_tableName).Append(" model=ToModel(row);");
            sb.AppendLine("list.Add(model);}");
            sb.AppendLine("return list;");
            sb.AppendLine("}");
            //ListAll结束

            
            sb.AppendLine(blanksString + "public static object FromDbValue(object value)");
            sb.AppendLine(blanksString+"{");
            sb.Append(blanksString+"  if (value == DBNull.Value)");
            sb.AppendLine(blanksString+"  {return null;}");
            sb.Append("else");
            sb.AppendLine(blanksString + "  {return value;}");
            sb.AppendLine(blanksString+"}");

            //FromDbValue()结束



            sb.AppendLine("}");
            richTextBoxDALCode.Text = sb.ToString();
        }

        /// <summary>
        /// 以数组形式返回列名。
        /// </summary>
        /// <param name="_table"></param>
        /// <returns></returns>
        private static string[] GetParamColumnNames(DataTable _table)
        {
            string[] colNames = new string[_table.Columns.Count];
            for (int i = 0; i < colNames.Count(); i++)
            {
                 DataColumn dataCol=_table.Columns[i];
                 colNames[i] ="@"+ dataCol.ColumnName;
            }
            return colNames;
        }
        /// <summary>
        /// 以数组形式返回列名。
        /// </summary>
        /// <param name="_table"></param>
        /// <returns></returns>
        private static string[] GetColumnNames(DataTable _table)
        {
            string[] colNames = new string[_table.Columns.Count];
            for (int i = 0; i < colNames.Count(); i++)
            {
                DataColumn dataCol = _table.Columns[i];
                colNames[i] =  dataCol.ColumnName;
            }
            return colNames;
        }
  
        #endregion

    }
}

将连接字符串存储到App.Config中

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="dbConnStr" connectionString="Data Source=xxx.xxx.xx.xx,xxxx;Initial Catalog=LightningScienceAndTechnology;User ID=xx;Password=xxxxxxxxxx"/>
  </connectionStrings>
</configuration>

SqlHelper相关代码

 public class SqlHelper
    {

       static  string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;

        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 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 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);

                    SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    sqlAdapter.Fill(ds);
                    return ds.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;
            }
        }
    }

注意事项：

1 StringBuilder字符串拼接，在大量字符串拼接的情况下，效率比”+”要高。

2 在Model中考虑到可空类型(比如：int?)

3 app.Config的读取需要引用System.Configuration

做技术：