懒人小工具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 }
为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 }
窗体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才有下面的增删改查的方法。
其实也很简单,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 }
平时我们在新增数据的时候,就以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
首先是根据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();
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
另外做了几个功能,但是不是很好用,感兴趣的朋友可以去下载源码看一下。
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 }
有人说这个版本太麻烦,我就做了T4版本