懒人小工具1：winform自动生成Model,Insert,Select,Delete以及导出Excel的方法

   懒人小工具2：T4自动生成Model,Insert,Select,Delete以及导出Excel的方法

   github地址：https://github.com/Jimmey-Jiang/JWorkHelper

在开发的过程中，我们为了节约时间，往往会将大量重复机械的代码封装，考虑代码的复用性，这样我们可以节约很多时间来做别的事情。最近跳槽到一节webform开发的公司，主要是开发自己公司用的ERP。开始因为一些诱惑进来的，现在感觉其实有些后悔放弃了很好的学习ABP以及新知识点像一些很新颖的前端框架进这个公司。但现在跳槽也不是一个明智之举，不好意思扯远了，既来之则安之。最近写了个小工具。主要针对webform开发过程中一些重复的代码处理。

github地址：https://github.com/Jimmey-Jiang/JWorkHelper
       其实没有什么特别大的技术难度，其实webform开发貌似也没有特别大的技术难度。

一、   首先新建一个类库WorkHelper。

然后新建类Program.cs，这里是作为主程序入口。

 using System;
 using System.Windows.Forms;

 namespace DevLogHelper
 {
     static class Program
     {
         /// <summary>
         /// 应用程序的主入口点。
         /// </summary>
         [STAThread]
         static void Main()
         {
             Application.EnableVisualStyles();
             Application.SetCompatibleTextRenderingDefault(false);
             Application.Run(new BaseSqlBuilder());
         }

     }
 }

 

为Main()方法加上STAThread标签。每次只能启动一个窗口。
      new 一个BaseSqlBuilder实例，BaseSqlBuilder实例是什么呢，就是我们今天的主题了。

二、winform界面设计

其实界面特别简单的。就是几个label、多选框、容器。
看下代码：

 using System;
 using System.Resources;
 using System.Text;
 using System.Windows.Forms;
 using DevLogHelper.Resources;

 namespace DevLogHelper
 {
     public partial class BaseSqlBuilder : Form
     {
         readonly ResourceManager _rm = new ResourceManager(typeof(ResourceDevCode));
         public BaseSqlBuilder()
         {
             InitializeComponent();
         }

         private void BaseSqlBuilder_Load(object sender, EventArgs e)
         {

         }
         /// <summary>
         /// 生成
         /// </summary>
         /// <param name="sender"></param>
         /// <param name="e"></param>
         private void btnCreate_Click(object sender, EventArgs e)
         {
             string msg = _rm.GetString("BaseSqlTip");
             try
             {
                 BaseSql.BaseSql sq = new BaseSql.BaseSql();
                 StringBuilder str = sq.BuilderCode(txtInput.Text, cbIsModel, txt_TableName.Text, ckb_Model.Checked, ckb_Insert.Checked,ckb_Update.Checked,ckb_Select.Checked,ckb_Delete.Checked,ckbExcel.Checked);
                 txtResult.Text = str.ToString();
                 Clipboard.SetDataObject(str.ToString());
             }
             catch (Exception ex)
             {
                 msg = ex.Message;
             }
             labTip.Text = msg;
         }

     }
 }

 

窗体BaseSqlBuilder继承自Form不必多说。ResourceManager是什么呢，ResourceManager就是一个资源文件，用于处理消息，或者路径什么。后面补上图。
新建一个BaseSql用于处理sql。把页面上的控件消息传递过去。封装一个数据的形式。

三、 资源文件以及解决方案结构。

跟踪BuilderCode到BaseSql类。

四、BaseSql类。

首先是对控件传递过来值得检查，参数是否为空等。

            string Table = inputSql[].ToString();
            if (string.IsNullOrWhiteSpace(inputCode))
            {
                inputCode = "select *from " + Table;
            }
            StringBuilder returnstr = new StringBuilder();
            StringBuilder strBuilder = new StringBuilder();
            DataSet ds = SqlHelper.Query(inputCode);
            DataRow dr = null;
            DataTable dt = new DataTable();

这里如果传递过来的sql语句为空，我们会根据表名自动生成查询SQL。然后根据ado.net 链接数据库生成DataSet 、DataRow 、DataTable 等。当然这里得有SqlHelper。SqlHelper如果需要可以去我源码上下载，稍后会放上源码。主要是链接数据库。执行SQL了。

五、实体

   #region 封装实体Model

    #region 封装实体Model

             strBuilder.AppendLine(@"
                public class Model
             {
                ");
             for (int i = ; i < dr.Table.Columns.Count; i++)
             {
                 if (i == )
                 {
                     Id = dr.Table.Columns[].ToString();//一般情况第一个字段是主键，当然如果第一个字段不是主键，那就需要修改了
                 }
                 string Type = dr.Table.Columns[i].DataType.ToString();
                 switch (Type)
                 {
                     case "System.String":
                         strBuilder.AppendLine("       private string " + "_" + dr.Table.Columns[i] + ";");
                         strBuilder.AppendLine("       public string " + dr.Table.Columns[i] + "");
                         strBuilder.AppendLine("         {");
                         strBuilder.AppendLine("            get { return " + "_" + dr.Table.Columns[i] + "; }");
                         strBuilder.AppendLine("            set { " + "_" + dr.Table.Columns[i] + " = value; }");
                         strBuilder.AppendLine("          }");
                         break;
                     case "System.Int":
                         strBuilder.AppendLine("       private Int " + "_" + dr.Table.Columns[i] + ";");
                         strBuilder.AppendLine("       public Int " + dr.Table.Columns[i] + "");
                         strBuilder.AppendLine("         {");
                         strBuilder.AppendLine("            get { return " + "_" + dr.Table.Columns[i] + "; }");
                         strBuilder.AppendLine("             set { " + "_" + dr.Table.Columns[i] + " = value; }");
                         strBuilder.AppendLine("         }");
                         break;
                     case "System.Int32":
                         strBuilder.AppendLine("       private Int " + "_" + dr.Table.Columns[i] + ";");
                         strBuilder.AppendLine("       public Int " + dr.Table.Columns[i] + "");
                         strBuilder.AppendLine("         {");
                         strBuilder.AppendLine("            get { return " + "_" + dr.Table.Columns[i] + "; }");
                         strBuilder.AppendLine("             set { " + "_" + dr.Table.Columns[i] + " = value; }");
                         strBuilder.AppendLine("         }");
                         break;
                     case "System.DateTime":
                         strBuilder.AppendLine("       private System.DateTime " + "_" + dr.Table.Columns[i] + ";");
                         strBuilder.AppendLine("       public System.DateTime " + dr.Table.Columns[i] + "");
                         strBuilder.AppendLine("          {");
                         strBuilder.AppendLine("             get { return " + "_" + dr.Table.Columns[i] + "; }");
                         strBuilder.AppendLine("             set { " + "_" + dr.Table.Columns[i] + " = value; }");
                         strBuilder.AppendLine("          }");
                         break;
                     case "System.Decimal":
                         strBuilder.AppendLine("       private System.Decimal " + "_" + dr.Table.Columns[i] + ";");
                         strBuilder.AppendLine("       public System.Decimal " + dr.Table.Columns[i] + "");
                         strBuilder.AppendLine("        {");
                         strBuilder.AppendLine("            get { return " + "_" + dr.Table.Columns[i] + "; }");
                         strBuilder.AppendLine("            set { " + "_" + dr.Table.Columns[i] + " = value; }");
                         strBuilder.AppendLine("         }");
                         break;
                     default:
                         strBuilder.AppendLine("       private string " + "_" + dr.Table.Columns[i] + ";");
                         strBuilder.AppendLine("       public string " + dr.Table.Columns[i] + "");
                         strBuilder.AppendLine("        {");
                         strBuilder.AppendLine("             get { return " + "_" + dr.Table.Columns[i] + "; }");
                         strBuilder.AppendLine("             set { " + "_" + dr.Table.Columns[i] + " = value; }");
                         strBuilder.AppendLine("         }");
                         break;
                 }
             }
             strBuilder.AppendLine(@"
               }
                ");

             #endregion

Model部分

首先我们是生成Model。有了Model才有下面的增删改查的方法。
        其实也很简单，dr.Table.Columns[i].DataType.ToString(),我们就是根据DataRow循环table列，判断字段类型，然后根据对应的字段类型封装成model.单选框默认勾选的，这里先去掉勾选，输入表名，点击生成，然后和数据库表对应，看一下效果。

Model 是最实用的，就算以后我们在用mvc或者ABP等其他框架底层用codefirst等orm框架的时候，我们也可以用这种方式生成Model.

六、生成insert方法

 public bool InsertAgreement_YNSHigh_Authorize(Model model)
         {

             string strSql = @"
                INSERT Agreement_YNSHigh_Authorize(
                  AYA_Code,
                  AYHA_Code,
                  Ctg_ID,
                  HospitalCode,
                  HospitalName,
                  HospitalGrad,
                  HospitalRegionID,
                  DevelopmentLimitTime,
                  EffectiveTime,
                  MarketingManagementFee,
                  BidPrice,
                  DeliveryCode,
                  DeliveryName,
                  AgreeAdjuestType,
                  ChangeContent,
                  Status,
                  DeliveryPrice,
                  BasePrice,
                  CategoryCommodityName,
                  CategorySystemName,
                  CategoryCommName,
                  CategorySpec,
                  Formulation,
                  BusinessModel)
                  VALUES (
                     @AYA_Code,
                     @AYHA_Code,
                     @Ctg_ID,
                     @HospitalCode,
                     @HospitalName,
                     @HospitalGrad,
                     @HospitalRegionID,
                     @DevelopmentLimitTime,
                     @EffectiveTime,
                     @MarketingManagementFee,
                     @BidPrice,
                     @DeliveryCode,
                     @DeliveryName,
                     @AgreeAdjuestType,
                     @ChangeContent,
                     @Status,
                     @DeliveryPrice,
                     @BasePrice,
                     @CategoryCommodityName,
                     @CategorySystemName,
                     @CategoryCommName,
                     @CategorySpec,
                     @Formulation,
                    @BusinessModel)

                      ";
             SqlParameter[] parameters = new SqlParameter[]
                                                   {
                       new SqlParameter("@AYA_Code", SqlDbType.NVarChar, ),
                      new SqlParameter("@AYHA_Code", SqlDbType.NVarChar, ),
                      new SqlParameter("@Ctg_ID", SqlDbType.NVarChar, ),
                      new SqlParameter("@HospitalCode", SqlDbType.NVarChar, ),
                      new SqlParameter("@HospitalName", SqlDbType.NVarChar, ),
                      new SqlParameter("@HospitalGrad", SqlDbType.NVarChar, ),
                      new SqlParameter("@HospitalRegionID", SqlDbType.NVarChar, ),
                      new SqlParameter("@DevelopmentLimitTime", SqlDbType.DateTime),
                      new SqlParameter("@EffectiveTime", SqlDbType.DateTime),
                       new SqlParameter("@MarketingManagementFee", SqlDbType.Decimal),
                       new SqlParameter("@BidPrice", SqlDbType.Decimal),
                      new SqlParameter("@DeliveryCode", SqlDbType.NVarChar, ),
                      new SqlParameter("@DeliveryName", SqlDbType.NVarChar, ),
                      new SqlParameter("@AgreeAdjuestType", SqlDbType.NVarChar, ),
                      new SqlParameter("@ChangeContent", SqlDbType.NVarChar, ),
                      new SqlParameter("@Status", SqlDbType.NVarChar, ),
                       new SqlParameter("@DeliveryPrice", SqlDbType.Decimal),
                       new SqlParameter("@BasePrice", SqlDbType.Decimal),
                      new SqlParameter("@CategoryCommodityName", SqlDbType.NVarChar, ),
                      new SqlParameter("@CategorySystemName", SqlDbType.NVarChar, ),
                      new SqlParameter("@CategoryCommName", SqlDbType.NVarChar, ),
                      new SqlParameter("@CategorySpec", SqlDbType.NVarChar, ),
                      new SqlParameter("@Formulation", SqlDbType.NVarChar, ),
                      new SqlParameter("@BusinessModel", SqlDbType.NVarChar, ),
                             };
             parameters[].Value = model.AYA_Code;
             parameters[].Value = model.AYHA_Code;
             parameters[].Value = model.Ctg_ID;
             parameters[].Value = model.HospitalCode;
             parameters[].Value = model.HospitalName;
             parameters[].Value = model.HospitalGrad;
             parameters[].Value = model.HospitalRegionID;
             parameters[].Value = model.DevelopmentLimitTime;
             parameters[].Value = model.EffectiveTime;
             parameters[].Value = model.MarketingManagementFee;
             parameters[].Value = model.BidPrice;
             parameters[].Value = model.DeliveryCode;
             parameters[].Value = model.DeliveryName;
             parameters[].Value = model.AgreeAdjuestType;
             parameters[].Value = model.ChangeContent;
             parameters[].Value = model.Status;
             parameters[].Value = model.DeliveryPrice;
             parameters[].Value = model.BasePrice;
             parameters[].Value = model.CategoryCommodityName;
             parameters[].Value = model.CategorySystemName;
             parameters[].Value = model.CategoryCommName;
             parameters[].Value = model.CategorySpec;
             parameters[].Value = model.Formulation;
             parameters[].Value = model.BusinessModel;

             using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
             {
                 conn.Open();
                 using (SqlTransaction trans = conn.BeginTransaction())
                 {
                     try
                     {
                         int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
                         if (i > )
                         {
                             trans.Commit();
                             return i > ;
                         }
                         else
                         {
                             trans.Rollback();
                             return false;
                         }
                     }
                     catch (System.Exception e)
                     {
                         return false;
                         trans.Rollback();
                         throw e;
                     }
                 }
             }

         }

平时我们在新增数据的时候，就以Agreement_YNSHigh_Authorize为例，一般都是这样超做的，加上事务，参数化传过来的参数，这样写起来也非常麻烦。我们这里了也对INSERT 做一下封装。

    #region 生成插入Insert方法

             #region 生成插入sql语句

             StringBuilder strTmp = new StringBuilder();
             try
             {
                 for (int i = ; i < dr.Table.Columns.Count; i++)//生成insert
                 {
                     if (i == )
                     {
                         strTmp.AppendLine("               INSERT " + Table + "(");
                     }
                     if (i == dr.Table.Columns.Count - )
                     {
                         strTmp.AppendLine("                 " + dr.Table.Columns[i].ToString() + ")");
                     }
                     else
                     {
                         strTmp.AppendLine("                 " + dr.Table.Columns[i].ToString() + ",");
                     }
                 }

                 for (int i = ; i < dr.Table.Columns.Count; i++)
                 {
                     if (i == )
                     {
                         strTmp.AppendLine("                 VALUES " + "(");
                     }
                     if (i == dr.Table.Columns.Count - )
                     {
                         strTmp.AppendLine("                   @" + dr.Table.Columns[i].ToString() + ")");
                     }
                     else
                     {
                         strTmp.AppendLine("                    @" + dr.Table.Columns[i].ToString() + ",");
                     }

                 }
             }
             catch (System.Exception ex)
             {

                 throw ex;
             }

             #endregion

             strBuilder.AppendLine("   public bool Insert" + Table + "(Model model)");
             strBuilder.AppendLine(@"    {
            ");
             strBuilder.AppendLine("                    string strSql = @\"");
             strBuilder.AppendLine(strTmp.ToString());
             strBuilder.AppendLine("                     \";");

             strBuilder.AppendLine(@"                 SqlParameter[] parameters = new SqlParameter[]
                                                   {");
             //参数类型
             for (int i = ; i < dr.Table.Columns.Count; i++)
             {
                 string Type = dr.Table.Columns[i].DataType.ToString();
                 switch (Type)
                 {
                     case "System.String":
                         strBuilder.AppendLine("                     new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.NVarChar, 255),");
                         break;
                     case "System.Int":
                         strBuilder.AppendLine("                     new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.Int),");
                         break;
                     case "System.Int32":
                         strBuilder.AppendLine("                     new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.Int),");
                         break;
                     case "System.DateTime":
                         strBuilder.AppendLine("                     new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.DateTime),");
                         break;
                     case "System.Decimal":
                         strBuilder.AppendLine("                      new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.Decimal),");
                         break;
                     default:
                         strBuilder.AppendLine("                      new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.NVarChar, 255),");
                         break;
                 }
             }
             strBuilder.AppendLine(@"                            };");

             for (int i = ; i < dr.Table.Columns.Count; i++)
             {
                 strBuilder.AppendLine("                        parameters[" + i + "].Value =" + "model." + dr.Table.Columns[i] + ";");
             }

             strBuilder.AppendLine(@"
             using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
             {
                 conn.Open();
                 using (SqlTransaction trans = conn.BeginTransaction())
                 {
                     try
                     {
                         int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
                           if (i > 0)
                         {
                             trans.Commit();
                             return i > 0;
                         }
                         else
                         {
                             trans.Rollback();
                             return false;
                         }
                     }
                     catch (System.Exception e)
                     {
                          return false;
                         trans.Rollback();
                         throw e;
                     }
                   }
                }
                ");
             strBuilder.AppendLine("}");

             #endregion

首先是根据sql生成字段和参数字段。这都是很繁琐的工作，而且不能出错。也是对DataRow table的循环和封装处理。需要注意一下转义字符处理，注意对事务的处理。另外update,select,delete的处理方式与insert类似，不在累述，放上代码。

七、update,select,delete

  if ((bool)inputSql[])
             {
                 returnstr.AppendLine(strBuilder.ToString());
             }
             strBuilder = new StringBuilder();
             #region 生成更新Update 方法

             #region 生成更新Update sql语句

             strTmp = new StringBuilder(); //sql
             try
             {
                 for (int i = ; i < dr.Table.Columns.Count; i++)//生成Update
                 {
                     if (i == )
                     {
                         strTmp.AppendLine("               Update " + Table + "  SET ");
                     }
                     if (i == dr.Table.Columns.Count - )
                     {

                         strTmp.AppendLine("                 " + dr.Table.Columns[i].ToString() + "=" + "@" + dr.Table.Columns[i].ToString() + " where " + Id + "=" + "@" + Id + "  ");
                     }
                     else
                     {
                         strTmp.AppendLine("                  " + dr.Table.Columns[i].ToString() + "=" + "@" + dr.Table.Columns[i].ToString() + ",");
                     }
                 }
             }
             catch (System.Exception ex)
             {

                 throw ex;
             }

             #endregion

             strBuilder.AppendLine("   public bool Update" + Table + "ById(Model model)");
             strBuilder.AppendLine(@"    {
            ");
             strBuilder.AppendLine("                    string strSql = @\"");
             strBuilder.AppendLine(strTmp.ToString());
             strBuilder.AppendLine("                     \";");

             strBuilder.AppendLine(@"                 SqlParameter[] parameters = new SqlParameter[]
                                                   {");
             //参数类型
             for (int i = ; i < dr.Table.Columns.Count; i++)
             {
                 string Type = dr.Table.Columns[i].DataType.ToString();
                 switch (Type)
                 {
                     case "System.String":
                         strBuilder.AppendLine("                     new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.NVarChar, 255),");
                         break;
                     case "System.Int":
                         strBuilder.AppendLine("                     new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.Int),");
                         break;
                     case "System.DateTime":
                         strBuilder.AppendLine("                     new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.DateTime),");
                         break;
                     case "System.Decimal":
                         strBuilder.AppendLine("                      new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.Decimal),");
                         break;
                     default:
                         strBuilder.AppendLine("                      new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.NVarChar, 255),");
                         break;
                 }
             }
             strBuilder.AppendLine(@"                            };");

             for (int i = ; i < dr.Table.Columns.Count; i++)
             {
                 strBuilder.AppendLine("                        parameters[" + i + "].Value =" + "model." + dr.Table.Columns[i] + ";");
             }

             strBuilder.AppendLine(@"
             using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
             {
                 conn.Open();
                 using (SqlTransaction trans = conn.BeginTransaction())
                 {
                     try
                     {
                         int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
                           if (i > 0)
                         {
                             trans.Commit();
                             return i > 0;
                         }
                         else
                         {
                             trans.Rollback();
                             return false;
                         }
                     }
                     catch (System.Exception e)
                     {
                         return false;
                         trans.Rollback();
                         throw e;
                     }
                   }
                }
                ");
             strBuilder.AppendLine("}");

             #endregion

             if ((bool)inputSql[])
             {
                 returnstr.AppendLine(strBuilder.ToString());
             }
             strBuilder = new StringBuilder();
             #region 生成查询方法

             strBuilder.AppendLine("   public DataTable GetDataBy" + Table + "(Model model , int pageNo, int pageSize, ref int iRecordCount)");
             strBuilder.AppendLine(@"    {    ");
             strTmp = new StringBuilder();
             strTmp.AppendLine("WITH temp AS ( SELECT rn =  ROW_NUMBER() OVER (ORDER BY " + Id + "  desc), *FROM  " + Table + " WHERE 1=1 {0} )");
             strTmp.AppendLine("SELECT *,rc=(select count(1) from temp) FROM temp WHERE rn BETWEEN {1} AND {2}");

             strBuilder.AppendLine("        List<SqlParameter> parameters = new List<SqlParameter>();");
             strBuilder.AppendLine("        StringBuilder sqlWhere = new StringBuilder();");
             for (int i = ; i < dr.Table.Columns.Count; i++)
             {
                 strBuilder.AppendLine("       if (!string.IsNullOrEmpty(model." + dr.Table.Columns[i].ToString() + ".ToString())) ");
                 strBuilder.AppendLine("       {");
                 strBuilder.AppendLine("          sqlWhere.Append(\" AND " + dr.Table.Columns[i].ToString() + "=@" + dr.Table.Columns[i].ToString() + "\");");
                 strBuilder.AppendLine("          parameters.Add(new SqlParameter(\"@" + dr.Table.Columns[i].ToString() + "\", SqlDbType.NVarChar, 255) { SqlValue = model." + dr.Table.Columns[i].ToString() + " });");
                 strBuilder.AppendLine("       } ");
             }
             strBuilder.AppendLine("                    string strSql = string.Format(@\"");
             strBuilder.AppendLine(strTmp.ToString());
             strBuilder.AppendLine("                     \" ,sqlWhere.ToString(), (pageNo - 1) * pageSize + 1, pageNo * pageSize);");

             strBuilder.AppendLine(@"    

                    DataTable dt = SqlHelper.Query(strSql, parameters.ToArray()).Tables[0];
                   if (dt!=null)
                    {");
             strBuilder.AppendLine("        iRecordCount = int.Parse(dt.Rows[0][\"" + "rc" + "\"].ToString());");
             strBuilder.AppendLine(@"      return dt;
                             }
                            else
                           {
                               iRecordCount = 0;
                             return null;
                         }
                 }    ");

             #endregion

             if ((bool)inputSql[])
             {
                 returnstr.AppendLine(strBuilder.ToString());
             }
             strBuilder = new StringBuilder();
             #region 生成删除的方法

             strBuilder.AppendLine("    public bool Delete" + Table + "(string " + Id + ")");
             strBuilder.AppendLine("   {");
             strBuilder.AppendLine("        List<SqlParameter> parameters = new List<SqlParameter>();");
             strTmp = new StringBuilder();
             strTmp.AppendLine("DELETE " + Table + " WHERE " + Id + "=" + Id + "");
             strBuilder.AppendLine("                    string strSql = string.Format(@\"");
             strBuilder.AppendLine(strTmp.ToString());
             strBuilder.AppendLine("                     \";");
             strBuilder.AppendLine("     int rowAffect =SqlHelper.Query(strSql, parameters.ToArray()).ToInt();;");
             strBuilder.AppendLine("      return rowAffect > 0 ? true : false;");
             strBuilder.AppendLine("      }");

             #endregion

             if ((bool)inputSql[])
             {
                 returnstr.AppendLine(strBuilder.ToString());
             }
             strBuilder = new StringBuilder();

if ((bool)inputSql[7])主要是对控件值得判断，是否勾选上，也就是是否需要生成改方法。导出的方法主要在公司已经封装好的代码上提取重复代码的，平时我们在做功能的时候只需要写这些代码即可。

八，导出Excel方法及其他

         strBuilder.AppendLine(@"     protected void btnExcel_Click(object sender, System.EventArgs e)
         {
             int intPageNo = 1;
             int intPageSize = 65535;
             int recordCount = 0;
             DataTable dt = GetUnAuthorizeAgreePassedDT( Model model intPageNo, intPageSize, out recordCount); ");
             strBuilder.AppendLine("     string strFileName = \"" + "导出Excel" + "\" + System.DateTime.Now.ToString(\" " + "yyyyMMddHHmmss" + "\");");
             strBuilder.AppendLine(@"        ExcelUtility excelUtil = new ExcelUtility(this, strFileName);
             List<ExcelHeader> headerS = new List<ExcelHeader>() { ");

             for (int i = ; i < dr.Table.Columns.Count; i++)
             {
                 string Type = dr.Table.Columns[i].DataType.ToString();
                 switch (Type)
                 {
                     case "System.DateTime":
                         strBuilder.AppendLine("           new ExcelHeader() { Name = \"字段名称自行补全\", DataType = EnumColumnDataType.日期, Width = 15 },");
                         break;
                     default:
                         strBuilder.AppendLine("           new ExcelHeader() { Name = \"字段名称自行补全\", DataType = EnumColumnDataType.文本, Width = 15 },");
                         break;
                 }
             }
             strBuilder.AppendLine(@"          };
             excelUtil.CreateHeader(headerS);
             if (dt != null && dt.Rows.Count > 0)
             {
                 foreach (DataRow dr in dt.Rows)
                 {
                     List<string> dataVals = new List<string>() { ");

             for (int i = ; i < dr.Table.Columns.Count; i++)
             {
                 strBuilder.AppendLine("                            dr[\" " + dr.Table.Columns[i] + "\"].ToString()  ");
             }

             strBuilder.AppendLine(@"          };
                     excelUtil.CreateItemRow(dataVals);
                 }
             }
             excelUtil.Export();
             }
            ");
             #endregion

另外做了几个功能，但是不是很好用，感兴趣的朋友可以去下载源码看一下。
github地址：https://github.com/Jimmey-Jiang/JWorkHelper

然后看一下生成的代码展示：

     public class Model
         {

             private string _Team_code;
             public string Team_code
             {
                 get { return _Team_code; }
                 set { _Team_code = value; }
             }
             private string _Team_name;
             public string Team_name
             {
                 get { return _Team_name; }
                 set { _Team_name = value; }
             }
             private string _Team_status;
             public string Team_status
             {
                 get { return _Team_status; }
                 set { _Team_status = value; }
             }
             private string _Team_user;
             public string Team_user
             {
                 get { return _Team_user; }
                 set { _Team_user = value; }
             }
             private System.DateTime _Team_date;
             public System.DateTime Team_date
             {
                 get { return _Team_date; }
                 set { _Team_date = value; }
             }

         }

         public bool InsertTeam_(Model model)
         {

             string strSql = @"
                INSERT Team_(
                  Team_code,
                  Team_name,
                  Team_status,
                  Team_user,
                  Team_date)
                  VALUES (
                     @Team_code,
                     @Team_name,
                     @Team_status,
                     @Team_user,
                    @Team_date)

                      ";
             SqlParameter[] parameters = new SqlParameter[]
                                                   {
                      new SqlParameter("@Team_code", SqlDbType.NVarChar, ),
                      new SqlParameter("@Team_name", SqlDbType.NVarChar, ),
                      new SqlParameter("@Team_status", SqlDbType.NVarChar, ),
                      new SqlParameter("@Team_user", SqlDbType.NVarChar, ),
                      new SqlParameter("@Team_date", SqlDbType.DateTime),
                             };
             parameters[].Value = model.Team_code;
             parameters[].Value = model.Team_name;
             parameters[].Value = model.Team_status;
             parameters[].Value = model.Team_user;
             parameters[].Value = model.Team_date;

             using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
             {
                 conn.Open();
                 using (SqlTransaction trans = conn.BeginTransaction())
                 {
                     try
                     {
                         int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
                         if (i > )
                         {
                             trans.Commit();
                             return i > ;
                         }
                         else
                         {
                             trans.Rollback();
                             return false;
                         }
                     }
                     catch (System.Exception e)
                     {
                         return false;
                         trans.Rollback();
                         throw e;
                     }
                 }
             }

         }

         public bool UpdateTeam_ById(Model model)
         {

             string strSql = @"
                Update Team_  SET
                   Team_code=@Team_code,
                   Team_name=@Team_name,
                   Team_status=@Team_status,
                   Team_user=@Team_user,
                  Team_date=@Team_date where Team_code=@Team_code  

                      ";
             SqlParameter[] parameters = new SqlParameter[]
                                                   {
                      new SqlParameter("@Team_code", SqlDbType.NVarChar, ),
                      new SqlParameter("@Team_name", SqlDbType.NVarChar, ),
                      new SqlParameter("@Team_status", SqlDbType.NVarChar, ),
                      new SqlParameter("@Team_user", SqlDbType.NVarChar, ),
                      new SqlParameter("@Team_date", SqlDbType.DateTime),
                             };
             parameters[].Value = model.Team_code;
             parameters[].Value = model.Team_name;
             parameters[].Value = model.Team_status;
             parameters[].Value = model.Team_user;
             parameters[].Value = model.Team_date;

             using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
             {
                 conn.Open();
                 using (SqlTransaction trans = conn.BeginTransaction())
                 {
                     try
                     {
                         int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
                         if (i > )
                         {
                             trans.Commit();
                             return i > ;
                         }
                         else
                         {
                             trans.Rollback();
                             return false;
                         }
                     }
                     catch (System.Exception e)
                     {
                         return false;
                         trans.Rollback();
                         throw e;
                     }
                 }
             }

         }

         public DataTable GetDataByTeam_(Model model, int pageNo, int pageSize, ref int iRecordCount)
         {
             List<SqlParameter> parameters = new List<SqlParameter>();
             StringBuilder sqlWhere = new StringBuilder();
             if (!string.IsNullOrEmpty(model.Team_code.ToString()))
             {
                 sqlWhere.Append(" AND Team_code=@Team_code");
                 parameters.Add(new SqlParameter("@Team_code", SqlDbType.NVarChar, ) { SqlValue = model.Team_code });
             }
             if (!string.IsNullOrEmpty(model.Team_name.ToString()))
             {
                 sqlWhere.Append(" AND Team_name=@Team_name");
                 parameters.Add(new SqlParameter("@Team_name", SqlDbType.NVarChar, ) { SqlValue = model.Team_name });
             }
             if (!string.IsNullOrEmpty(model.Team_status.ToString()))
             {
                 sqlWhere.Append(" AND Team_status=@Team_status");
                 parameters.Add(new SqlParameter("@Team_status", SqlDbType.NVarChar, ) { SqlValue = model.Team_status });
             }
             if (!string.IsNullOrEmpty(model.Team_user.ToString()))
             {
                 sqlWhere.Append(" AND Team_user=@Team_user");
                 parameters.Add(new SqlParameter("@Team_user", SqlDbType.NVarChar, ) { SqlValue = model.Team_user });
             }
             if (!string.IsNullOrEmpty(model.Team_date.ToString()))
             {
                 sqlWhere.Append(" AND Team_date=@Team_date");
                 parameters.Add(new SqlParameter("@Team_date", SqlDbType.NVarChar, ) { SqlValue = model.Team_date });
             }
             string strSql = string.Format(@"
 WITH temp AS ( SELECT rn =  ROW_NUMBER() OVER (ORDER BY Team_code  desc), *FROM  Team_ WHERE 1=1 {0} )
 SELECT *,rc=(select count(1) from temp) FROM temp WHERE rn BETWEEN {1} AND {2}

                      ", sqlWhere.ToString(), (pageNo - ) * pageSize + , pageNo * pageSize);

             DataTable dt = SqlHelper.Query(strSql, parameters.ToArray()).Tables[];
             if (dt != null)
             {
                 iRecordCount = int.Parse(dt.Rows[]["rc"].ToString());
                 return dt;
             }
             else
             {
                 iRecordCount = ;
                 return null;
             }
         }

         public bool DeleteTeam_(string Team_code)
    {
         List<SqlParameter> parameters = new List<SqlParameter>();
                     string strSql = string.Format(@"
 DELETE Team_ WHERE Team_code=Team_code

                      ";
      int rowAffect =SqlHelper.Query(strSql, parameters.ToArray()).ToInt();;
       return rowAffect >  ? true : false;
       }

         protected void btnExcel_Click(object sender, System.EventArgs e)
         {
             int intPageNo = ;
             int intPageSize = ;
             int recordCount = ;
             DataTable dt = GetUnAuthorizeAgreePassedDT( Model model intPageNo, intPageSize, out recordCount);
      string strFileName = "导出Excel" + System.DateTime.Now.ToString(" yyyyMMddHHmmss");
         ExcelUtility excelUtil = new ExcelUtility(this, strFileName);
             List<ExcelHeader> headerS = new List<ExcelHeader>() {
            new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.文本, Width =  },
            new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.文本, Width =  },
            new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.文本, Width =  },
            new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.文本, Width =  },
            new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.日期, Width =  },
           };
             excelUtil.CreateHeader(headerS);
             if (dt != null && dt.Rows.Count > )
             {
                 foreach (DataRow dr in dt.Rows)
                 {
                     List<string> dataVals = new List<string>() {
                             dr[" Team_code"].ToString()
                             dr[" Team_name"].ToString()
                             dr[" Team_status"].ToString()
                             dr[" Team_user"].ToString()
                             dr[" Team_date"].ToString()
           };
                     excelUtil.CreateItemRow(dataVals);
                 }
             }
             excelUtil.Export();
             }

有人说这个版本太麻烦，我就做了T4版本

懒人小工具：T4自动生成Model,Insert,Select,Delete以及导出Excel的方法