懒人小工具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的方法
转载于:https://www.cnblogs.com/anyushengcms/p/7552371.html
相关资源：Codematic c#代码自动生成器
