• 懒人小工具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,这里是作为主程序入口。

     1 using System;
     2 using System.Windows.Forms;
     3 
     4 namespace DevLogHelper
     5 {
     6     static class Program
     7     {
     8         /// <summary>
     9         /// 应用程序的主入口点。
    10         /// </summary>
    11         [STAThread]
    12         static void Main()
    13         {
    14             Application.EnableVisualStyles();
    15             Application.SetCompatibleTextRenderingDefault(false);
    16             Application.Run(new BaseSqlBuilder());
    17         }
    18 
    19     }
    20 }
    View Code
     

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

    二、winform界面设计

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

     1 using System;
     2 using System.Resources;
     3 using System.Text;
     4 using System.Windows.Forms;
     5 using DevLogHelper.Resources;
     6 
     7 namespace DevLogHelper
     8 {
     9     public partial class BaseSqlBuilder : Form
    10     {
    11         readonly ResourceManager _rm = new ResourceManager(typeof(ResourceDevCode));
    12         public BaseSqlBuilder()
    13         {
    14             InitializeComponent();
    15         }
    16 
    17         private void BaseSqlBuilder_Load(object sender, EventArgs e)
    18         {
    19 
    20         }
    21         /// <summary>
    22         /// 生成
    23         /// </summary>
    24         /// <param name="sender"></param>
    25         /// <param name="e"></param>
    26         private void btnCreate_Click(object sender, EventArgs e)
    27         {
    28             string msg = _rm.GetString("BaseSqlTip");
    29             try
    30             {
    31                 BaseSql.BaseSql sq = new BaseSql.BaseSql();
    32                 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);
    33                 txtResult.Text = str.ToString();
    34                 Clipboard.SetDataObject(str.ToString());
    35             }
    36             catch (Exception ex)
    37             {
    38                 msg = ex.Message;
    39             }
    40             labTip.Text = msg;
    41         }
    42 
    43     }
    44 }
    View Code
     

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

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

       跟踪BuilderCode到BaseSql类。

     四、BaseSql类。

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

                string Table = inputSql[2].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了。

        五、实体

     1   #region 封装实体Model
     2 
     3    #region 封装实体Model
     4 
     5             strBuilder.AppendLine(@"
     6                public class Model
     7             {
     8                ");
     9             for (int i = 0; i < dr.Table.Columns.Count; i++)
    10             {
    11                 if (i == 0)
    12                 {
    13                     Id = dr.Table.Columns[0].ToString();//一般情况第一个字段是主键,当然如果第一个字段不是主键,那就需要修改了
    14                 }
    15                 string Type = dr.Table.Columns[i].DataType.ToString();
    16                 switch (Type)
    17                 {
    18                     case "System.String":
    19                         strBuilder.AppendLine("       private string " + "_" + dr.Table.Columns[i] + ";");
    20                         strBuilder.AppendLine("       public string " + dr.Table.Columns[i] + "");
    21                         strBuilder.AppendLine("         {");
    22                         strBuilder.AppendLine("            get { return " + "_" + dr.Table.Columns[i] + "; }");
    23                         strBuilder.AppendLine("            set { " + "_" + dr.Table.Columns[i] + " = value; }");
    24                         strBuilder.AppendLine("          }");
    25                         break;
    26                     case "System.Int":
    27                         strBuilder.AppendLine("       private Int " + "_" + dr.Table.Columns[i] + ";");
    28                         strBuilder.AppendLine("       public Int " + dr.Table.Columns[i] + "");
    29                         strBuilder.AppendLine("         {");
    30                         strBuilder.AppendLine("            get { return " + "_" + dr.Table.Columns[i] + "; }");
    31                         strBuilder.AppendLine("             set { " + "_" + dr.Table.Columns[i] + " = value; }");
    32                         strBuilder.AppendLine("         }");
    33                         break;
    34                     case "System.Int32":
    35                         strBuilder.AppendLine("       private Int " + "_" + dr.Table.Columns[i] + ";");
    36                         strBuilder.AppendLine("       public Int " + dr.Table.Columns[i] + "");
    37                         strBuilder.AppendLine("         {");
    38                         strBuilder.AppendLine("            get { return " + "_" + dr.Table.Columns[i] + "; }");
    39                         strBuilder.AppendLine("             set { " + "_" + dr.Table.Columns[i] + " = value; }");
    40                         strBuilder.AppendLine("         }");
    41                         break;
    42                     case "System.DateTime":
    43                         strBuilder.AppendLine("       private System.DateTime " + "_" + dr.Table.Columns[i] + ";");
    44                         strBuilder.AppendLine("       public System.DateTime " + dr.Table.Columns[i] + "");
    45                         strBuilder.AppendLine("          {");
    46                         strBuilder.AppendLine("             get { return " + "_" + dr.Table.Columns[i] + "; }");
    47                         strBuilder.AppendLine("             set { " + "_" + dr.Table.Columns[i] + " = value; }");
    48                         strBuilder.AppendLine("          }");
    49                         break;
    50                     case "System.Decimal":
    51                         strBuilder.AppendLine("       private System.Decimal " + "_" + dr.Table.Columns[i] + ";");
    52                         strBuilder.AppendLine("       public System.Decimal " + dr.Table.Columns[i] + "");
    53                         strBuilder.AppendLine("        {");
    54                         strBuilder.AppendLine("            get { return " + "_" + dr.Table.Columns[i] + "; }");
    55                         strBuilder.AppendLine("            set { " + "_" + dr.Table.Columns[i] + " = value; }");
    56                         strBuilder.AppendLine("         }");
    57                         break;
    58                     default:
    59                         strBuilder.AppendLine("       private string " + "_" + dr.Table.Columns[i] + ";");
    60                         strBuilder.AppendLine("       public string " + dr.Table.Columns[i] + "");
    61                         strBuilder.AppendLine("        {");
    62                         strBuilder.AppendLine("             get { return " + "_" + dr.Table.Columns[i] + "; }");
    63                         strBuilder.AppendLine("             set { " + "_" + dr.Table.Columns[i] + " = value; }");
    64                         strBuilder.AppendLine("         }");
    65                         break;
    66                 }
    67             }
    68             strBuilder.AppendLine(@"
    69               }
    70                ");
    71 
    72             #endregion
    Model部分

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

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

     六、生成insert方法

      1 public bool InsertAgreement_YNSHigh_Authorize(Model model)
      2         {
      3 
      4             string strSql = @"
      5                INSERT Agreement_YNSHigh_Authorize(
      6                  AYA_Code,
      7                  AYHA_Code,
      8                  Ctg_ID,
      9                  HospitalCode,
     10                  HospitalName,
     11                  HospitalGrad,
     12                  HospitalRegionID,
     13                  DevelopmentLimitTime,
     14                  EffectiveTime,
     15                  MarketingManagementFee,
     16                  BidPrice,
     17                  DeliveryCode,
     18                  DeliveryName,
     19                  AgreeAdjuestType,
     20                  ChangeContent,
     21                  Status,
     22                  DeliveryPrice,
     23                  BasePrice,
     24                  CategoryCommodityName,
     25                  CategorySystemName,
     26                  CategoryCommName,
     27                  CategorySpec,
     28                  Formulation,
     29                  BusinessModel)
     30                  VALUES (
     31                     @AYA_Code,
     32                     @AYHA_Code,
     33                     @Ctg_ID,
     34                     @HospitalCode,
     35                     @HospitalName,
     36                     @HospitalGrad,
     37                     @HospitalRegionID,
     38                     @DevelopmentLimitTime,
     39                     @EffectiveTime,
     40                     @MarketingManagementFee,
     41                     @BidPrice,
     42                     @DeliveryCode,
     43                     @DeliveryName,
     44                     @AgreeAdjuestType,
     45                     @ChangeContent,
     46                     @Status,
     47                     @DeliveryPrice,
     48                     @BasePrice,
     49                     @CategoryCommodityName,
     50                     @CategorySystemName,
     51                     @CategoryCommName,
     52                     @CategorySpec,
     53                     @Formulation,
     54                    @BusinessModel)
     55 
     56                      ";
     57             SqlParameter[] parameters = new SqlParameter[]
     58                                                   {
     59                       new SqlParameter("@AYA_Code", SqlDbType.NVarChar, 255),
     60                      new SqlParameter("@AYHA_Code", SqlDbType.NVarChar, 255),
     61                      new SqlParameter("@Ctg_ID", SqlDbType.NVarChar, 255),
     62                      new SqlParameter("@HospitalCode", SqlDbType.NVarChar, 255),
     63                      new SqlParameter("@HospitalName", SqlDbType.NVarChar, 255),
     64                      new SqlParameter("@HospitalGrad", SqlDbType.NVarChar, 255),
     65                      new SqlParameter("@HospitalRegionID", SqlDbType.NVarChar, 255),
     66                      new SqlParameter("@DevelopmentLimitTime", SqlDbType.DateTime),
     67                      new SqlParameter("@EffectiveTime", SqlDbType.DateTime),
     68                       new SqlParameter("@MarketingManagementFee", SqlDbType.Decimal),
     69                       new SqlParameter("@BidPrice", SqlDbType.Decimal),
     70                      new SqlParameter("@DeliveryCode", SqlDbType.NVarChar, 255),
     71                      new SqlParameter("@DeliveryName", SqlDbType.NVarChar, 255),
     72                      new SqlParameter("@AgreeAdjuestType", SqlDbType.NVarChar, 255),
     73                      new SqlParameter("@ChangeContent", SqlDbType.NVarChar, 255),
     74                      new SqlParameter("@Status", SqlDbType.NVarChar, 255),
     75                       new SqlParameter("@DeliveryPrice", SqlDbType.Decimal),
     76                       new SqlParameter("@BasePrice", SqlDbType.Decimal),
     77                      new SqlParameter("@CategoryCommodityName", SqlDbType.NVarChar, 255),
     78                      new SqlParameter("@CategorySystemName", SqlDbType.NVarChar, 255),
     79                      new SqlParameter("@CategoryCommName", SqlDbType.NVarChar, 255),
     80                      new SqlParameter("@CategorySpec", SqlDbType.NVarChar, 255),
     81                      new SqlParameter("@Formulation", SqlDbType.NVarChar, 255),
     82                      new SqlParameter("@BusinessModel", SqlDbType.NVarChar, 255),
     83                             };
     84             parameters[0].Value = model.AYA_Code;
     85             parameters[1].Value = model.AYHA_Code;
     86             parameters[2].Value = model.Ctg_ID;
     87             parameters[3].Value = model.HospitalCode;
     88             parameters[4].Value = model.HospitalName;
     89             parameters[5].Value = model.HospitalGrad;
     90             parameters[6].Value = model.HospitalRegionID;
     91             parameters[7].Value = model.DevelopmentLimitTime;
     92             parameters[8].Value = model.EffectiveTime;
     93             parameters[9].Value = model.MarketingManagementFee;
     94             parameters[10].Value = model.BidPrice;
     95             parameters[11].Value = model.DeliveryCode;
     96             parameters[12].Value = model.DeliveryName;
     97             parameters[13].Value = model.AgreeAdjuestType;
     98             parameters[14].Value = model.ChangeContent;
     99             parameters[15].Value = model.Status;
    100             parameters[16].Value = model.DeliveryPrice;
    101             parameters[17].Value = model.BasePrice;
    102             parameters[18].Value = model.CategoryCommodityName;
    103             parameters[19].Value = model.CategorySystemName;
    104             parameters[20].Value = model.CategoryCommName;
    105             parameters[21].Value = model.CategorySpec;
    106             parameters[22].Value = model.Formulation;
    107             parameters[23].Value = model.BusinessModel;
    108 
    109             using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
    110             {
    111                 conn.Open();
    112                 using (SqlTransaction trans = conn.BeginTransaction())
    113                 {
    114                     try
    115                     {
    116                         int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
    117                         if (i > 0)
    118                         {
    119                             trans.Commit();
    120                             return i > 0;
    121                         }
    122                         else
    123                         {
    124                             trans.Rollback();
    125                             return false;
    126                         }
    127                     }
    128                     catch (System.Exception e)
    129                     {
    130                         return false;
    131                         trans.Rollback();
    132                         throw e;
    133                     }
    134                 }
    135             }
    136 
    137         }
    View Code

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

      1    #region 生成插入Insert方法
      2 
      3             #region 生成插入sql语句
      4 
      5             StringBuilder strTmp = new StringBuilder();
      6             try
      7             {
      8                 for (int i = 0; i < dr.Table.Columns.Count; i++)//生成insert 
      9                 {
     10                     if (i == 0)
     11                     {
     12                         strTmp.AppendLine("               INSERT " + Table + "(");
     13                     }
     14                     if (i == dr.Table.Columns.Count - 1)
     15                     {
     16                         strTmp.AppendLine("                 " + dr.Table.Columns[i].ToString() + ")");
     17                     }
     18                     else
     19                     {
     20                         strTmp.AppendLine("                 " + dr.Table.Columns[i].ToString() + ",");
     21                     }
     22                 }
     23 
     24                 for (int i = 0; i < dr.Table.Columns.Count; i++)
     25                 {
     26                     if (i == 0)
     27                     {
     28                         strTmp.AppendLine("                 VALUES " + "(");
     29                     }
     30                     if (i == dr.Table.Columns.Count - 1)
     31                     {
     32                         strTmp.AppendLine("                   @" + dr.Table.Columns[i].ToString() + ")");
     33                     }
     34                     else
     35                     {
     36                         strTmp.AppendLine("                    @" + dr.Table.Columns[i].ToString() + ",");
     37                     }
     38 
     39                 }
     40             }
     41             catch (System.Exception ex)
     42             {
     43 
     44                 throw ex;
     45             }
     46 
     47             #endregion
     48 
     49 
     50 
     51 
     52 
     53             strBuilder.AppendLine("   public bool Insert" + Table + "(Model model)");
     54             strBuilder.AppendLine(@"    {   
     55            ");
     56             strBuilder.AppendLine("                    string strSql = @"");
     57             strBuilder.AppendLine(strTmp.ToString());
     58             strBuilder.AppendLine("                     ";");
     59 
     60             strBuilder.AppendLine(@"                 SqlParameter[] parameters = new SqlParameter[]
     61                                                   {");
     62             //参数类型
     63             for (int i = 0; i < dr.Table.Columns.Count; i++)
     64             {
     65                 string Type = dr.Table.Columns[i].DataType.ToString();
     66                 switch (Type)
     67                 {
     68                     case "System.String":
     69                         strBuilder.AppendLine("                     new SqlParameter("" + "@" + dr.Table.Columns[i] + "", SqlDbType.NVarChar, 255),");
     70                         break;
     71                     case "System.Int":
     72                         strBuilder.AppendLine("                     new SqlParameter("" + "@" + dr.Table.Columns[i] + "", SqlDbType.Int),");
     73                         break;
     74                     case "System.Int32":
     75                         strBuilder.AppendLine("                     new SqlParameter("" + "@" + dr.Table.Columns[i] + "", SqlDbType.Int),");
     76                         break;
     77                     case "System.DateTime":
     78                         strBuilder.AppendLine("                     new SqlParameter("" + "@" + dr.Table.Columns[i] + "", SqlDbType.DateTime),");
     79                         break;
     80                     case "System.Decimal":
     81                         strBuilder.AppendLine("                      new SqlParameter("" + "@" + dr.Table.Columns[i] + "", SqlDbType.Decimal),");
     82                         break;
     83                     default:
     84                         strBuilder.AppendLine("                      new SqlParameter("" + "@" + dr.Table.Columns[i] + "", SqlDbType.NVarChar, 255),");
     85                         break;
     86                 }
     87             }
     88             strBuilder.AppendLine(@"                            };");
     89 
     90             for (int i = 0; i < dr.Table.Columns.Count; i++)
     91             {
     92                 strBuilder.AppendLine("                        parameters[" + i + "].Value =" + "model." + dr.Table.Columns[i] + ";");
     93             }
     94 
     95             strBuilder.AppendLine(@"
     96             using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
     97             {
     98                 conn.Open();
     99                 using (SqlTransaction trans = conn.BeginTransaction())
    100                 {
    101                     try
    102                     {
    103                         int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
    104                           if (i > 0)
    105                         {
    106                             trans.Commit();
    107                             return i > 0;
    108                         }
    109                         else
    110                         {
    111                             trans.Rollback();
    112                             return false;
    113                         }
    114                     }
    115                     catch (System.Exception e)
    116                     {
    117                          return false;
    118                         trans.Rollback();
    119                         throw e;
    120                     }
    121                   }
    122                }
    123                ");
    124             strBuilder.AppendLine("}");
    125 
    126             #endregion
    View Code

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

       七、update,select,delete

      1  if ((bool)inputSql[4])
      2             {
      3                 returnstr.AppendLine(strBuilder.ToString());
      4             }
      5             strBuilder = new StringBuilder();
      6             #region 生成更新Update 方法
      7 
      8             #region 生成更新Update sql语句
      9 
     10             strTmp = new StringBuilder(); //sql 
     11             try
     12             {
     13                 for (int i = 0; i < dr.Table.Columns.Count; i++)//生成Update 
     14                 {
     15                     if (i == 0)
     16                     {
     17                         strTmp.AppendLine("               Update " + Table + "  SET ");
     18                     }
     19                     if (i == dr.Table.Columns.Count - 1)
     20                     {
     21 
     22 
     23                         strTmp.AppendLine("                 " + dr.Table.Columns[i].ToString() + "=" + "@" + dr.Table.Columns[i].ToString() + " where " + Id + "=" + "@" + Id + "  ");
     24                     }
     25                     else
     26                     {
     27                         strTmp.AppendLine("                  " + dr.Table.Columns[i].ToString() + "=" + "@" + dr.Table.Columns[i].ToString() + ",");
     28                     }
     29                 }
     30             }
     31             catch (System.Exception ex)
     32             {
     33 
     34                 throw ex;
     35             }
     36 
     37             #endregion
     38 
     39 
     40 
     41 
     42             strBuilder.AppendLine("   public bool Update" + Table + "ById(Model model)");
     43             strBuilder.AppendLine(@"    {   
     44            ");
     45             strBuilder.AppendLine("                    string strSql = @"");
     46             strBuilder.AppendLine(strTmp.ToString());
     47             strBuilder.AppendLine("                     ";");
     48 
     49             strBuilder.AppendLine(@"                 SqlParameter[] parameters = new SqlParameter[]
     50                                                   {");
     51             //参数类型
     52             for (int i = 0; i < dr.Table.Columns.Count; i++)
     53             {
     54                 string Type = dr.Table.Columns[i].DataType.ToString();
     55                 switch (Type)
     56                 {
     57                     case "System.String":
     58                         strBuilder.AppendLine("                     new SqlParameter("" + "@" + dr.Table.Columns[i] + "", SqlDbType.NVarChar, 255),");
     59                         break;
     60                     case "System.Int":
     61                         strBuilder.AppendLine("                     new SqlParameter("" + "@" + dr.Table.Columns[i] + "", SqlDbType.Int),");
     62                         break;
     63                     case "System.DateTime":
     64                         strBuilder.AppendLine("                     new SqlParameter("" + "@" + dr.Table.Columns[i] + "", SqlDbType.DateTime),");
     65                         break;
     66                     case "System.Decimal":
     67                         strBuilder.AppendLine("                      new SqlParameter("" + "@" + dr.Table.Columns[i] + "", SqlDbType.Decimal),");
     68                         break;
     69                     default:
     70                         strBuilder.AppendLine("                      new SqlParameter("" + "@" + dr.Table.Columns[i] + "", SqlDbType.NVarChar, 255),");
     71                         break;
     72                 }
     73             }
     74             strBuilder.AppendLine(@"                            };");
     75 
     76             for (int i = 0; i < dr.Table.Columns.Count; i++)
     77             {
     78                 strBuilder.AppendLine("                        parameters[" + i + "].Value =" + "model." + dr.Table.Columns[i] + ";");
     79             }
     80 
     81             strBuilder.AppendLine(@"
     82             using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
     83             {
     84                 conn.Open();
     85                 using (SqlTransaction trans = conn.BeginTransaction())
     86                 {
     87                     try
     88                     {
     89                         int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
     90                           if (i > 0)
     91                         {
     92                             trans.Commit();
     93                             return i > 0;
     94                         }
     95                         else
     96                         {
     97                             trans.Rollback();
     98                             return false;
     99                         }
    100                     }
    101                     catch (System.Exception e)
    102                     {
    103                         return false;
    104                         trans.Rollback();
    105                         throw e;
    106                     }
    107                   }
    108                }
    109                ");
    110             strBuilder.AppendLine("}");
    111 
    112             #endregion
    113 
    114             if ((bool)inputSql[5])
    115             {
    116                 returnstr.AppendLine(strBuilder.ToString());
    117             }
    118             strBuilder = new StringBuilder();
    119             #region 生成查询方法
    120 
    121             strBuilder.AppendLine("   public DataTable GetDataBy" + Table + "(Model model , int pageNo, int pageSize, ref int iRecordCount)");
    122             strBuilder.AppendLine(@"    {    ");
    123             strTmp = new StringBuilder();
    124             strTmp.AppendLine("WITH temp AS ( SELECT rn =  ROW_NUMBER() OVER (ORDER BY " + Id + "  desc), *FROM  " + Table + " WHERE 1=1 {0} )");
    125             strTmp.AppendLine("SELECT *,rc=(select count(1) from temp) FROM temp WHERE rn BETWEEN {1} AND {2}");
    126 
    127             strBuilder.AppendLine("        List<SqlParameter> parameters = new List<SqlParameter>();");
    128             strBuilder.AppendLine("        StringBuilder sqlWhere = new StringBuilder();");
    129             for (int i = 0; i < dr.Table.Columns.Count; i++)
    130             {
    131                 strBuilder.AppendLine("       if (!string.IsNullOrEmpty(model." + dr.Table.Columns[i].ToString() + ".ToString())) ");
    132                 strBuilder.AppendLine("       {");
    133                 strBuilder.AppendLine("          sqlWhere.Append(" AND " + dr.Table.Columns[i].ToString() + "=@" + dr.Table.Columns[i].ToString() + "");");
    134                 strBuilder.AppendLine("          parameters.Add(new SqlParameter("@" + dr.Table.Columns[i].ToString() + "", SqlDbType.NVarChar, 255) { SqlValue = model." + dr.Table.Columns[i].ToString() + " });");
    135                 strBuilder.AppendLine("       } ");
    136             }
    137             strBuilder.AppendLine("                    string strSql = string.Format(@"");
    138             strBuilder.AppendLine(strTmp.ToString());
    139             strBuilder.AppendLine("                     " ,sqlWhere.ToString(), (pageNo - 1) * pageSize + 1, pageNo * pageSize);");
    140 
    141             strBuilder.AppendLine(@"    
    142 
    143                    DataTable dt = SqlHelper.Query(strSql, parameters.ToArray()).Tables[0];
    144                   if (dt!=null)
    145                    {");
    146             strBuilder.AppendLine("        iRecordCount = int.Parse(dt.Rows[0]["" + "rc" + ""].ToString());");
    147             strBuilder.AppendLine(@"      return dt;
    148                             }
    149                            else
    150                           {
    151                               iRecordCount = 0;
    152                             return null;
    153                         }
    154                 }    ");
    155 
    156             #endregion
    157 
    158             if ((bool)inputSql[6])
    159             {
    160                 returnstr.AppendLine(strBuilder.ToString());
    161             }
    162             strBuilder = new StringBuilder();
    163             #region 生成删除的方法
    164 
    165             strBuilder.AppendLine("    public bool Delete" + Table + "(string " + Id + ")");
    166             strBuilder.AppendLine("   {");
    167             strBuilder.AppendLine("        List<SqlParameter> parameters = new List<SqlParameter>();");
    168             strTmp = new StringBuilder();
    169             strTmp.AppendLine("DELETE " + Table + " WHERE " + Id + "=" + Id + "");
    170             strBuilder.AppendLine("                    string strSql = string.Format(@"");
    171             strBuilder.AppendLine(strTmp.ToString());
    172             strBuilder.AppendLine("                     ";");
    173             strBuilder.AppendLine("     int rowAffect =SqlHelper.Query(strSql, parameters.ToArray()).ToInt();;");
    174             strBuilder.AppendLine("      return rowAffect > 0 ? true : false;");
    175             strBuilder.AppendLine("      }");
    176 
    177             #endregion
    178 
    179             if ((bool)inputSql[7])
    180             {
    181                 returnstr.AppendLine(strBuilder.ToString());
    182             }
    183             strBuilder = new StringBuilder();
    View Code

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

    八,导出Excel方法及其他

     1         strBuilder.AppendLine(@"     protected void btnExcel_Click(object sender, System.EventArgs e)
     2         {
     3             int intPageNo = 1;
     4             int intPageSize = 65535;
     5             int recordCount = 0;
     6             DataTable dt = GetUnAuthorizeAgreePassedDT( Model model intPageNo, intPageSize, out recordCount); ");
     7             strBuilder.AppendLine("     string strFileName = "" + "导出Excel" + "" + System.DateTime.Now.ToString(" " + "yyyyMMddHHmmss" + "");");
     8             strBuilder.AppendLine(@"        ExcelUtility excelUtil = new ExcelUtility(this, strFileName);
     9             List<ExcelHeader> headerS = new List<ExcelHeader>() { ");
    10 
    11             for (int i = 0; i < dr.Table.Columns.Count; i++)
    12             {
    13                 string Type = dr.Table.Columns[i].DataType.ToString();
    14                 switch (Type)
    15                 {
    16                     case "System.DateTime":
    17                         strBuilder.AppendLine("           new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.日期, Width = 15 },");
    18                         break;
    19                     default:
    20                         strBuilder.AppendLine("           new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.文本, Width = 15 },");
    21                         break;
    22                 }
    23             }
    24             strBuilder.AppendLine(@"          };
    25             excelUtil.CreateHeader(headerS);
    26             if (dt != null && dt.Rows.Count > 0)
    27             {
    28                 foreach (DataRow dr in dt.Rows)
    29                 {
    30                     List<string> dataVals = new List<string>() { ");
    31 
    32 
    33             for (int i = 0; i < dr.Table.Columns.Count; i++)
    34             {
    35                 strBuilder.AppendLine("                            dr[" " + dr.Table.Columns[i] + ""].ToString()  ");
    36             }
    37 
    38             strBuilder.AppendLine(@"          };       
    39                     excelUtil.CreateItemRow(dataVals);
    40                 }
    41             }
    42             excelUtil.Export();
    43             }
    44            "); 
    45             #endregion
    View Code

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

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

      1     public class Model
      2         {
      3 
      4             private string _Team_code;
      5             public string Team_code
      6             {
      7                 get { return _Team_code; }
      8                 set { _Team_code = value; }
      9             }
     10             private string _Team_name;
     11             public string Team_name
     12             {
     13                 get { return _Team_name; }
     14                 set { _Team_name = value; }
     15             }
     16             private string _Team_status;
     17             public string Team_status
     18             {
     19                 get { return _Team_status; }
     20                 set { _Team_status = value; }
     21             }
     22             private string _Team_user;
     23             public string Team_user
     24             {
     25                 get { return _Team_user; }
     26                 set { _Team_user = value; }
     27             }
     28             private System.DateTime _Team_date;
     29             public System.DateTime Team_date
     30             {
     31                 get { return _Team_date; }
     32                 set { _Team_date = value; }
     33             }
     34 
     35         }
     36 
     37 
     38         public bool InsertTeam_(Model model)
     39         {
     40 
     41             string strSql = @"
     42                INSERT Team_(
     43                  Team_code,
     44                  Team_name,
     45                  Team_status,
     46                  Team_user,
     47                  Team_date)
     48                  VALUES (
     49                     @Team_code,
     50                     @Team_name,
     51                     @Team_status,
     52                     @Team_user,
     53                    @Team_date)
     54 
     55                      ";
     56             SqlParameter[] parameters = new SqlParameter[]
     57                                                   {
     58                      new SqlParameter("@Team_code", SqlDbType.NVarChar, 255),
     59                      new SqlParameter("@Team_name", SqlDbType.NVarChar, 255),
     60                      new SqlParameter("@Team_status", SqlDbType.NVarChar, 255),
     61                      new SqlParameter("@Team_user", SqlDbType.NVarChar, 255),
     62                      new SqlParameter("@Team_date", SqlDbType.DateTime),
     63                             };
     64             parameters[0].Value = model.Team_code;
     65             parameters[1].Value = model.Team_name;
     66             parameters[2].Value = model.Team_status;
     67             parameters[3].Value = model.Team_user;
     68             parameters[4].Value = model.Team_date;
     69 
     70             using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
     71             {
     72                 conn.Open();
     73                 using (SqlTransaction trans = conn.BeginTransaction())
     74                 {
     75                     try
     76                     {
     77                         int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
     78                         if (i > 0)
     79                         {
     80                             trans.Commit();
     81                             return i > 0;
     82                         }
     83                         else
     84                         {
     85                             trans.Rollback();
     86                             return false;
     87                         }
     88                     }
     89                     catch (System.Exception e)
     90                     {
     91                         return false;
     92                         trans.Rollback();
     93                         throw e;
     94                     }
     95                 }
     96             }
     97 
     98         }
     99 
    100         public bool UpdateTeam_ById(Model model)
    101         {
    102 
    103             string strSql = @"
    104                Update Team_  SET 
    105                   Team_code=@Team_code,
    106                   Team_name=@Team_name,
    107                   Team_status=@Team_status,
    108                   Team_user=@Team_user,
    109                  Team_date=@Team_date where Team_code=@Team_code  
    110 
    111                      ";
    112             SqlParameter[] parameters = new SqlParameter[]
    113                                                   {
    114                      new SqlParameter("@Team_code", SqlDbType.NVarChar, 255),
    115                      new SqlParameter("@Team_name", SqlDbType.NVarChar, 255),
    116                      new SqlParameter("@Team_status", SqlDbType.NVarChar, 255),
    117                      new SqlParameter("@Team_user", SqlDbType.NVarChar, 255),
    118                      new SqlParameter("@Team_date", SqlDbType.DateTime),
    119                             };
    120             parameters[0].Value = model.Team_code;
    121             parameters[1].Value = model.Team_name;
    122             parameters[2].Value = model.Team_status;
    123             parameters[3].Value = model.Team_user;
    124             parameters[4].Value = model.Team_date;
    125 
    126             using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
    127             {
    128                 conn.Open();
    129                 using (SqlTransaction trans = conn.BeginTransaction())
    130                 {
    131                     try
    132                     {
    133                         int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
    134                         if (i > 0)
    135                         {
    136                             trans.Commit();
    137                             return i > 0;
    138                         }
    139                         else
    140                         {
    141                             trans.Rollback();
    142                             return false;
    143                         }
    144                     }
    145                     catch (System.Exception e)
    146                     {
    147                         return false;
    148                         trans.Rollback();
    149                         throw e;
    150                     }
    151                 }
    152             }
    153 
    154         }
    155 
    156         public DataTable GetDataByTeam_(Model model, int pageNo, int pageSize, ref int iRecordCount)
    157         {
    158             List<SqlParameter> parameters = new List<SqlParameter>();
    159             StringBuilder sqlWhere = new StringBuilder();
    160             if (!string.IsNullOrEmpty(model.Team_code.ToString()))
    161             {
    162                 sqlWhere.Append(" AND Team_code=@Team_code");
    163                 parameters.Add(new SqlParameter("@Team_code", SqlDbType.NVarChar, 255) { SqlValue = model.Team_code });
    164             }
    165             if (!string.IsNullOrEmpty(model.Team_name.ToString()))
    166             {
    167                 sqlWhere.Append(" AND Team_name=@Team_name");
    168                 parameters.Add(new SqlParameter("@Team_name", SqlDbType.NVarChar, 255) { SqlValue = model.Team_name });
    169             }
    170             if (!string.IsNullOrEmpty(model.Team_status.ToString()))
    171             {
    172                 sqlWhere.Append(" AND Team_status=@Team_status");
    173                 parameters.Add(new SqlParameter("@Team_status", SqlDbType.NVarChar, 255) { SqlValue = model.Team_status });
    174             }
    175             if (!string.IsNullOrEmpty(model.Team_user.ToString()))
    176             {
    177                 sqlWhere.Append(" AND Team_user=@Team_user");
    178                 parameters.Add(new SqlParameter("@Team_user", SqlDbType.NVarChar, 255) { SqlValue = model.Team_user });
    179             }
    180             if (!string.IsNullOrEmpty(model.Team_date.ToString()))
    181             {
    182                 sqlWhere.Append(" AND Team_date=@Team_date");
    183                 parameters.Add(new SqlParameter("@Team_date", SqlDbType.NVarChar, 255) { SqlValue = model.Team_date });
    184             }
    185             string strSql = string.Format(@"
    186 WITH temp AS ( SELECT rn =  ROW_NUMBER() OVER (ORDER BY Team_code  desc), *FROM  Team_ WHERE 1=1 {0} )
    187 SELECT *,rc=(select count(1) from temp) FROM temp WHERE rn BETWEEN {1} AND {2}
    188 
    189                      ", sqlWhere.ToString(), (pageNo - 1) * pageSize + 1, pageNo * pageSize);
    190 
    191 
    192             DataTable dt = SqlHelper.Query(strSql, parameters.ToArray()).Tables[0];
    193             if (dt != null)
    194             {
    195                 iRecordCount = int.Parse(dt.Rows[0]["rc"].ToString());
    196                 return dt;
    197             }
    198             else
    199             {
    200                 iRecordCount = 0;
    201                 return null;
    202             }
    203         }
    204 
    205         public bool DeleteTeam_(string Team_code)
    206    {
    207         List<SqlParameter> parameters = new List<SqlParameter>();
    208                     string strSql = string.Format(@"
    209 DELETE Team_ WHERE Team_code=Team_code
    210 
    211                      ";
    212      int rowAffect =SqlHelper.Query(strSql, parameters.ToArray()).ToInt();;
    213       return rowAffect > 0 ? true : false;
    214       }
    215 
    216         protected void btnExcel_Click(object sender, System.EventArgs e)
    217         {
    218             int intPageNo = 1;
    219             int intPageSize = 65535;
    220             int recordCount = 0;
    221             DataTable dt = GetUnAuthorizeAgreePassedDT( Model model intPageNo, intPageSize, out recordCount); 
    222      string strFileName = "导出Excel" + System.DateTime.Now.ToString(" yyyyMMddHHmmss");
    223         ExcelUtility excelUtil = new ExcelUtility(this, strFileName);
    224             List<ExcelHeader> headerS = new List<ExcelHeader>() { 
    225            new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.文本, Width = 15 },
    226            new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.文本, Width = 15 },
    227            new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.文本, Width = 15 },
    228            new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.文本, Width = 15 },
    229            new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.日期, Width = 15 },
    230           };
    231             excelUtil.CreateHeader(headerS);
    232             if (dt != null && dt.Rows.Count > 0)
    233             {
    234                 foreach (DataRow dr in dt.Rows)
    235                 {
    236                     List<string> dataVals = new List<string>() { 
    237                             dr[" Team_code"].ToString()  
    238                             dr[" Team_name"].ToString()  
    239                             dr[" Team_status"].ToString()  
    240                             dr[" Team_user"].ToString()  
    241                             dr[" Team_date"].ToString()  
    242           };       
    243                     excelUtil.CreateItemRow(dataVals);
    244                 }
    245             }
    246             excelUtil.Export();
    247             }
    View Code

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

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

    您的资助是我最大的动力!
    金额随意,欢迎来赏!

    如果,您认为阅读这篇博客让您有些收获,不妨点击一下右下角的推荐按钮。
    如果,您希望更容易地发现我的新博客,不妨点击一下绿色通道的关注我

    如果,想给予我更多的鼓励,求打

    因为,我的写作热情也离不开您的肯定支持,感谢您的阅读,我是【安与生】!

    本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
  • 相关阅读:
    jquery学习
    java--MVC引入JUnit单元测试
    BAE引擎发布到外网
    ORACLE1.26 综合:游标和动态SQL
    ORACLE1.25 动态SQL
    ORACLE1.24 银行系统操作和游标
    ORACLE1.23 loop,whild.for循环
    ORACLE1.23 if case when
    ORACLE1.22 %type %rowtype
    ORACLE1.21 PLSQL 01
  • 原文地址:https://www.cnblogs.com/anyushengcms/p/7552371.html
Copyright © 2020-2023  润新知