懒人小工具2：T4自动生成Model,Insert,Select,Delete以及导出Excel的方法
github地址：https://github.com/Jimmey-Jiang/JWorkHelper
在开发的过程中，我们为了节约时间，往往会将大量重复机械的代码封装，考虑代码的复用性，这样我们可以节约很多时间来做别的事情。最近跳槽到一节webform开发的公司，主要是开发自己公司用的ERP。开始因为一些诱惑进来的，现在感觉其实有些后悔放弃了很好的学习ABP以及新知识点像一些很新颖的前端框架进这个公司。但现在跳槽也不是一个明智之举，不好意思扯远了，既来之则安之。最近写了个小工具。主要针对webform开发过程中一些重复的代码处理。
github地址：https://github.com/Jimmey-Jiang/JWorkHelper
其实没有什么特别大的技术难度，其实webform开发貌似也没有特别大的技术难度。
一、 首先新建一个类库WorkHelper。
然后新建类Program.cs，这里是作为主程序入口。
using System;
using System.Windows.Forms; namespace DevLogHelper
{
static class Program
{
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new BaseSqlBuilder());
} }
}
为Main()方法加上STAThread标签。每次只能启动一个窗口。
new 一个BaseSqlBuilder实例，BaseSqlBuilder实例是什么呢，就是我们今天的主题了。
二、winform界面设计
其实界面特别简单的。就是几个label、多选框、容器。
看下代码：
using System;
using System.Resources;
using System.Text;
using System.Windows.Forms;
using DevLogHelper.Resources; namespace DevLogHelper
{
public partial class BaseSqlBuilder : Form
{
readonly ResourceManager _rm = new ResourceManager(typeof(ResourceDevCode));
public BaseSqlBuilder()
{
InitializeComponent();
} private void BaseSqlBuilder_Load(object sender, EventArgs e)
{ }
/// <summary>
/// 生成
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCreate_Click(object sender, EventArgs e)
{
string msg = _rm.GetString("BaseSqlTip");
try
{
BaseSql.BaseSql sq = new BaseSql.BaseSql();
StringBuilder str = sq.BuilderCode(txtInput.Text, cbIsModel, txt_TableName.Text, ckb_Model.Checked, ckb_Insert.Checked,ckb_Update.Checked,ckb_Select.Checked,ckb_Delete.Checked,ckbExcel.Checked);
txtResult.Text = str.ToString();
Clipboard.SetDataObject(str.ToString());
}
catch (Exception ex)
{
msg = ex.Message;
}
labTip.Text = msg;
} }
}
窗体BaseSqlBuilder继承自Form不必多说。ResourceManager是什么呢，ResourceManager就是一个资源文件，用于处理消息，或者路径什么。后面补上图。
新建一个BaseSql用于处理sql。把页面上的控件消息传递过去。封装一个数据的形式。
三、 资源文件以及解决方案结构。
跟踪BuilderCode到BaseSql类。
四、BaseSql类。
首先是对控件传递过来值得检查，参数是否为空等。
string Table = inputSql[].ToString();
if (string.IsNullOrWhiteSpace(inputCode))
{
inputCode = "select *from " + Table;
}
StringBuilder returnstr = new StringBuilder();
StringBuilder strBuilder = new StringBuilder();
DataSet ds = SqlHelper.Query(inputCode);
DataRow dr = null;
DataTable dt = new DataTable();
这里如果传递过来的sql语句为空，我们会根据表名自动生成查询SQL。然后根据ado.net 链接数据库生成DataSet 、DataRow 、DataTable 等。当然这里得有SqlHelper。SqlHelper如果需要可以去我源码上下载，稍后会放上源码。主要是链接数据库。执行SQL了。
五、实体
#region 封装实体Model #region 封装实体Model strBuilder.AppendLine(@"
public class Model
{
");
for (int i = ; i < dr.Table.Columns.Count; i++)
{
if (i == )
{
Id = dr.Table.Columns[].ToString();//一般情况第一个字段是主键，当然如果第一个字段不是主键，那就需要修改了
}
string Type = dr.Table.Columns[i].DataType.ToString();
switch (Type)
{
case "System.String":
strBuilder.AppendLine(" private string " + "_" + dr.Table.Columns[i] + ";");
strBuilder.AppendLine(" public string " + dr.Table.Columns[i] + "");
strBuilder.AppendLine(" {");
strBuilder.AppendLine(" get { return " + "_" + dr.Table.Columns[i] + "; }");
strBuilder.AppendLine(" set { " + "_" + dr.Table.Columns[i] + " = value; }");
strBuilder.AppendLine(" }");
break;
case "System.Int":
strBuilder.AppendLine(" private Int " + "_" + dr.Table.Columns[i] + ";");
strBuilder.AppendLine(" public Int " + dr.Table.Columns[i] + "");
strBuilder.AppendLine(" {");
strBuilder.AppendLine(" get { return " + "_" + dr.Table.Columns[i] + "; }");
strBuilder.AppendLine(" set { " + "_" + dr.Table.Columns[i] + " = value; }");
strBuilder.AppendLine(" }");
break;
case "System.Int32":
strBuilder.AppendLine(" private Int " + "_" + dr.Table.Columns[i] + ";");
strBuilder.AppendLine(" public Int " + dr.Table.Columns[i] + "");
strBuilder.AppendLine(" {");
strBuilder.AppendLine(" get { return " + "_" + dr.Table.Columns[i] + "; }");
strBuilder.AppendLine(" set { " + "_" + dr.Table.Columns[i] + " = value; }");
strBuilder.AppendLine(" }");
break;
case "System.DateTime":
strBuilder.AppendLine(" private System.DateTime " + "_" + dr.Table.Columns[i] + ";");
strBuilder.AppendLine(" public System.DateTime " + dr.Table.Columns[i] + "");
strBuilder.AppendLine(" {");
strBuilder.AppendLine(" get { return " + "_" + dr.Table.Columns[i] + "; }");
strBuilder.AppendLine(" set { " + "_" + dr.Table.Columns[i] + " = value; }");
strBuilder.AppendLine(" }");
break;
case "System.Decimal":
strBuilder.AppendLine(" private System.Decimal " + "_" + dr.Table.Columns[i] + ";");
strBuilder.AppendLine(" public System.Decimal " + dr.Table.Columns[i] + "");
strBuilder.AppendLine(" {");
strBuilder.AppendLine(" get { return " + "_" + dr.Table.Columns[i] + "; }");
strBuilder.AppendLine(" set { " + "_" + dr.Table.Columns[i] + " = value; }");
strBuilder.AppendLine(" }");
break;
default:
strBuilder.AppendLine(" private string " + "_" + dr.Table.Columns[i] + ";");
strBuilder.AppendLine(" public string " + dr.Table.Columns[i] + "");
strBuilder.AppendLine(" {");
strBuilder.AppendLine(" get { return " + "_" + dr.Table.Columns[i] + "; }");
strBuilder.AppendLine(" set { " + "_" + dr.Table.Columns[i] + " = value; }");
strBuilder.AppendLine(" }");
break;
}
}
strBuilder.AppendLine(@"
}
"); #endregion
Model部分
首先我们是生成Model。有了Model才有下面的增删改查的方法。
其实也很简单，dr.Table.Columns[i].DataType.ToString(),我们就是根据DataRow循环table列，判断字段类型，然后根据对应的字段类型封装成model.单选框默认勾选的，这里先去掉勾选，输入表名，点击生成，然后和数据库表对应，看一下效果。
Model 是最实用的，就算以后我们在用mvc或者ABP等其他框架底层用codefirst等orm框架的时候，我们也可以用这种方式生成Model.
六、生成insert方法
public bool InsertAgreement_YNSHigh_Authorize(Model model)
{ string strSql = @"
INSERT Agreement_YNSHigh_Authorize(
AYA_Code,
AYHA_Code,
Ctg_ID,
HospitalCode,
HospitalName,
HospitalGrad,
HospitalRegionID,
DevelopmentLimitTime,
EffectiveTime,
MarketingManagementFee,
BidPrice,
DeliveryCode,
DeliveryName,
AgreeAdjuestType,
ChangeContent,
Status,
DeliveryPrice,
BasePrice,
CategoryCommodityName,
CategorySystemName,
CategoryCommName,
CategorySpec,
Formulation,
BusinessModel)
VALUES (
@AYA_Code,
@AYHA_Code,
@Ctg_ID,
@HospitalCode,
@HospitalName,
@HospitalGrad,
@HospitalRegionID,
@DevelopmentLimitTime,
@EffectiveTime,
@MarketingManagementFee,
@BidPrice,
@DeliveryCode,
@DeliveryName,
@AgreeAdjuestType,
@ChangeContent,
@Status,
@DeliveryPrice,
@BasePrice,
@CategoryCommodityName,
@CategorySystemName,
@CategoryCommName,
@CategorySpec,
@Formulation,
@BusinessModel) ";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@AYA_Code", SqlDbType.NVarChar, ),
new SqlParameter("@AYHA_Code", SqlDbType.NVarChar, ),
new SqlParameter("@Ctg_ID", SqlDbType.NVarChar, ),
new SqlParameter("@HospitalCode", SqlDbType.NVarChar, ),
new SqlParameter("@HospitalName", SqlDbType.NVarChar, ),
new SqlParameter("@HospitalGrad", SqlDbType.NVarChar, ),
new SqlParameter("@HospitalRegionID", SqlDbType.NVarChar, ),
new SqlParameter("@DevelopmentLimitTime", SqlDbType.DateTime),
new SqlParameter("@EffectiveTime", SqlDbType.DateTime),
new SqlParameter("@MarketingManagementFee", SqlDbType.Decimal),
new SqlParameter("@BidPrice", SqlDbType.Decimal),
new SqlParameter("@DeliveryCode", SqlDbType.NVarChar, ),
new SqlParameter("@DeliveryName", SqlDbType.NVarChar, ),
new SqlParameter("@AgreeAdjuestType", SqlDbType.NVarChar, ),
new SqlParameter("@ChangeContent", SqlDbType.NVarChar, ),
new SqlParameter("@Status", SqlDbType.NVarChar, ),
new SqlParameter("@DeliveryPrice", SqlDbType.Decimal),
new SqlParameter("@BasePrice", SqlDbType.Decimal),
new SqlParameter("@CategoryCommodityName", SqlDbType.NVarChar, ),
new SqlParameter("@CategorySystemName", SqlDbType.NVarChar, ),
new SqlParameter("@CategoryCommName", SqlDbType.NVarChar, ),
new SqlParameter("@CategorySpec", SqlDbType.NVarChar, ),
new SqlParameter("@Formulation", SqlDbType.NVarChar, ),
new SqlParameter("@BusinessModel", SqlDbType.NVarChar, ),
};
parameters[].Value = model.AYA_Code;
parameters[].Value = model.AYHA_Code;
parameters[].Value = model.Ctg_ID;
parameters[].Value = model.HospitalCode;
parameters[].Value = model.HospitalName;
parameters[].Value = model.HospitalGrad;
parameters[].Value = model.HospitalRegionID;
parameters[].Value = model.DevelopmentLimitTime;
parameters[].Value = model.EffectiveTime;
parameters[].Value = model.MarketingManagementFee;
parameters[].Value = model.BidPrice;
parameters[].Value = model.DeliveryCode;
parameters[].Value = model.DeliveryName;
parameters[].Value = model.AgreeAdjuestType;
parameters[].Value = model.ChangeContent;
parameters[].Value = model.Status;
parameters[].Value = model.DeliveryPrice;
parameters[].Value = model.BasePrice;
parameters[].Value = model.CategoryCommodityName;
parameters[].Value = model.CategorySystemName;
parameters[].Value = model.CategoryCommName;
parameters[].Value = model.CategorySpec;
parameters[].Value = model.Formulation;
parameters[].Value = model.BusinessModel; using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
if (i > )
{
trans.Commit();
return i > ;
}
else
{
trans.Rollback();
return false;
}
}
catch (System.Exception e)
{
return false;
trans.Rollback();
throw e;
}
}
} }
平时我们在新增数据的时候，就以Agreement_YNSHigh_Authorize为例，一般都是这样超做的，加上事务，参数化传过来的参数，这样写起来也非常麻烦。我们这里了也对INSERT 做一下封装。
#region 生成插入Insert方法 #region 生成插入sql语句 StringBuilder strTmp = new StringBuilder();
try
{
for (int i = ; i < dr.Table.Columns.Count; i++)//生成insert
{
if (i == )
{
strTmp.AppendLine(" INSERT " + Table + "(");
}
if (i == dr.Table.Columns.Count - )
{
strTmp.AppendLine(" " + dr.Table.Columns[i].ToString() + ")");
}
else
{
strTmp.AppendLine(" " + dr.Table.Columns[i].ToString() + ",");
}
} for (int i = ; i < dr.Table.Columns.Count; i++)
{
if (i == )
{
strTmp.AppendLine(" VALUES " + "(");
}
if (i == dr.Table.Columns.Count - )
{
strTmp.AppendLine(" @" + dr.Table.Columns[i].ToString() + ")");
}
else
{
strTmp.AppendLine(" @" + dr.Table.Columns[i].ToString() + ",");
} }
}
catch (System.Exception ex)
{ throw ex;
} #endregion strBuilder.AppendLine(" public bool Insert" + Table + "(Model model)");
strBuilder.AppendLine(@" {
");
strBuilder.AppendLine(" string strSql = @\"");
strBuilder.AppendLine(strTmp.ToString());
strBuilder.AppendLine(" \";"); strBuilder.AppendLine(@" SqlParameter[] parameters = new SqlParameter[]
{");
//参数类型
for (int i = ; i < dr.Table.Columns.Count; i++)
{
string Type = dr.Table.Columns[i].DataType.ToString();
switch (Type)
{
case "System.String":
strBuilder.AppendLine(" new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.NVarChar, 255),");
break;
case "System.Int":
strBuilder.AppendLine(" new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.Int),");
break;
case "System.Int32":
strBuilder.AppendLine(" new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.Int),");
break;
case "System.DateTime":
strBuilder.AppendLine(" new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.DateTime),");
break;
case "System.Decimal":
strBuilder.AppendLine(" new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.Decimal),");
break;
default:
strBuilder.AppendLine(" new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.NVarChar, 255),");
break;
}
}
strBuilder.AppendLine(@" };"); for (int i = ; i < dr.Table.Columns.Count; i++)
{
strBuilder.AppendLine(" parameters[" + i + "].Value =" + "model." + dr.Table.Columns[i] + ";");
} strBuilder.AppendLine(@"
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
if (i > 0)
{
trans.Commit();
return i > 0;
}
else
{
trans.Rollback();
return false;
}
}
catch (System.Exception e)
{
return false;
trans.Rollback();
throw e;
}
}
}
");
strBuilder.AppendLine("}"); #endregion
首先是根据sql生成字段和参数字段。这都是很繁琐的工作，而且不能出错。也是对DataRow table的循环和封装处理。需要注意一下转义字符处理，注意对事务的处理。另外update,select,delete的处理方式与insert类似，不在累述，放上代码。
七、update,select,delete
if ((bool)inputSql[])
{
returnstr.AppendLine(strBuilder.ToString());
}
strBuilder = new StringBuilder();
#region 生成更新Update 方法 #region 生成更新Update sql语句 strTmp = new StringBuilder(); //sql
try
{
for (int i = ; i < dr.Table.Columns.Count; i++)//生成Update
{
if (i == )
{
strTmp.AppendLine(" Update " + Table + " SET ");
}
if (i == dr.Table.Columns.Count - )
{ strTmp.AppendLine(" " + dr.Table.Columns[i].ToString() + "=" + "@" + dr.Table.Columns[i].ToString() + " where " + Id + "=" + "@" + Id + " ");
}
else
{
strTmp.AppendLine(" " + dr.Table.Columns[i].ToString() + "=" + "@" + dr.Table.Columns[i].ToString() + ",");
}
}
}
catch (System.Exception ex)
{ throw ex;
} #endregion strBuilder.AppendLine(" public bool Update" + Table + "ById(Model model)");
strBuilder.AppendLine(@" {
");
strBuilder.AppendLine(" string strSql = @\"");
strBuilder.AppendLine(strTmp.ToString());
strBuilder.AppendLine(" \";"); strBuilder.AppendLine(@" SqlParameter[] parameters = new SqlParameter[]
{");
//参数类型
for (int i = ; i < dr.Table.Columns.Count; i++)
{
string Type = dr.Table.Columns[i].DataType.ToString();
switch (Type)
{
case "System.String":
strBuilder.AppendLine(" new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.NVarChar, 255),");
break;
case "System.Int":
strBuilder.AppendLine(" new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.Int),");
break;
case "System.DateTime":
strBuilder.AppendLine(" new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.DateTime),");
break;
case "System.Decimal":
strBuilder.AppendLine(" new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.Decimal),");
break;
default:
strBuilder.AppendLine(" new SqlParameter(\"" + "@" + dr.Table.Columns[i] + "\", SqlDbType.NVarChar, 255),");
break;
}
}
strBuilder.AppendLine(@" };"); for (int i = ; i < dr.Table.Columns.Count; i++)
{
strBuilder.AppendLine(" parameters[" + i + "].Value =" + "model." + dr.Table.Columns[i] + ";");
} strBuilder.AppendLine(@"
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
if (i > 0)
{
trans.Commit();
return i > 0;
}
else
{
trans.Rollback();
return false;
}
}
catch (System.Exception e)
{
return false;
trans.Rollback();
throw e;
}
}
}
");
strBuilder.AppendLine("}"); #endregion if ((bool)inputSql[])
{
returnstr.AppendLine(strBuilder.ToString());
}
strBuilder = new StringBuilder();
#region 生成查询方法 strBuilder.AppendLine(" public DataTable GetDataBy" + Table + "(Model model , int pageNo, int pageSize, ref int iRecordCount)");
strBuilder.AppendLine(@" { ");
strTmp = new StringBuilder();
strTmp.AppendLine("WITH temp AS ( SELECT rn = ROW_NUMBER() OVER (ORDER BY " + Id + " desc), *FROM " + Table + " WHERE 1=1 {0} )");
strTmp.AppendLine("SELECT *,rc=(select count(1) from temp) FROM temp WHERE rn BETWEEN {1} AND {2}"); strBuilder.AppendLine(" List<SqlParameter> parameters = new List<SqlParameter>();");
strBuilder.AppendLine(" StringBuilder sqlWhere = new StringBuilder();");
for (int i = ; i < dr.Table.Columns.Count; i++)
{
strBuilder.AppendLine(" if (!string.IsNullOrEmpty(model." + dr.Table.Columns[i].ToString() + ".ToString())) ");
strBuilder.AppendLine(" {");
strBuilder.AppendLine(" sqlWhere.Append(\" AND " + dr.Table.Columns[i].ToString() + "=@" + dr.Table.Columns[i].ToString() + "\");");
strBuilder.AppendLine(" parameters.Add(new SqlParameter(\"@" + dr.Table.Columns[i].ToString() + "\", SqlDbType.NVarChar, 255) { SqlValue = model." + dr.Table.Columns[i].ToString() + " });");
strBuilder.AppendLine(" } ");
}
strBuilder.AppendLine(" string strSql = string.Format(@\"");
strBuilder.AppendLine(strTmp.ToString());
strBuilder.AppendLine(" \" ,sqlWhere.ToString(), (pageNo - 1) * pageSize + 1, pageNo * pageSize);"); strBuilder.AppendLine(@" DataTable dt = SqlHelper.Query(strSql, parameters.ToArray()).Tables[0];
if (dt!=null)
{");
strBuilder.AppendLine(" iRecordCount = int.Parse(dt.Rows[0][\"" + "rc" + "\"].ToString());");
strBuilder.AppendLine(@" return dt;
}
else
{
iRecordCount = 0;
return null;
}
} "); #endregion if ((bool)inputSql[])
{
returnstr.AppendLine(strBuilder.ToString());
}
strBuilder = new StringBuilder();
#region 生成删除的方法 strBuilder.AppendLine(" public bool Delete" + Table + "(string " + Id + ")");
strBuilder.AppendLine(" {");
strBuilder.AppendLine(" List<SqlParameter> parameters = new List<SqlParameter>();");
strTmp = new StringBuilder();
strTmp.AppendLine("DELETE " + Table + " WHERE " + Id + "=" + Id + "");
strBuilder.AppendLine(" string strSql = string.Format(@\"");
strBuilder.AppendLine(strTmp.ToString());
strBuilder.AppendLine(" \";");
strBuilder.AppendLine(" int rowAffect =SqlHelper.Query(strSql, parameters.ToArray()).ToInt();;");
strBuilder.AppendLine(" return rowAffect > 0 ? true : false;");
strBuilder.AppendLine(" }"); #endregion if ((bool)inputSql[])
{
returnstr.AppendLine(strBuilder.ToString());
}
strBuilder = new StringBuilder();
if ((bool)inputSql[7])主要是对控件值得判断，是否勾选上，也就是是否需要生成改方法。导出的方法主要在公司已经封装好的代码上提取重复代码的，平时我们在做功能的时候只需要写这些代码即可。
八，导出Excel方法及其他
strBuilder.AppendLine(@" protected void btnExcel_Click(object sender, System.EventArgs e)
{
int intPageNo = 1;
int intPageSize = 65535;
int recordCount = 0;
DataTable dt = GetUnAuthorizeAgreePassedDT( Model model intPageNo, intPageSize, out recordCount); ");
strBuilder.AppendLine(" string strFileName = \"" + "导出Excel" + "\" + System.DateTime.Now.ToString(\" " + "yyyyMMddHHmmss" + "\");");
strBuilder.AppendLine(@" ExcelUtility excelUtil = new ExcelUtility(this, strFileName);
List<ExcelHeader> headerS = new List<ExcelHeader>() { "); for (int i = ; i < dr.Table.Columns.Count; i++)
{
string Type = dr.Table.Columns[i].DataType.ToString();
switch (Type)
{
case "System.DateTime":
strBuilder.AppendLine(" new ExcelHeader() { Name = \"字段名称自行补全\", DataType = EnumColumnDataType.日期, Width = 15 },");
break;
default:
strBuilder.AppendLine(" new ExcelHeader() { Name = \"字段名称自行补全\", DataType = EnumColumnDataType.文本, Width = 15 },");
break;
}
}
strBuilder.AppendLine(@" };
excelUtil.CreateHeader(headerS);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
List<string> dataVals = new List<string>() { "); for (int i = ; i < dr.Table.Columns.Count; i++)
{
strBuilder.AppendLine(" dr[\" " + dr.Table.Columns[i] + "\"].ToString() ");
} strBuilder.AppendLine(@" };
excelUtil.CreateItemRow(dataVals);
}
}
excelUtil.Export();
}
");
#endregion
另外做了几个功能，但是不是很好用，感兴趣的朋友可以去下载源码看一下。
github地址：https://github.com/Jimmey-Jiang/JWorkHelper
然后看一下生成的代码展示：
public class Model
{ private string _Team_code;
public string Team_code
{
get { return _Team_code; }
set { _Team_code = value; }
}
private string _Team_name;
public string Team_name
{
get { return _Team_name; }
set { _Team_name = value; }
}
private string _Team_status;
public string Team_status
{
get { return _Team_status; }
set { _Team_status = value; }
}
private string _Team_user;
public string Team_user
{
get { return _Team_user; }
set { _Team_user = value; }
}
private System.DateTime _Team_date;
public System.DateTime Team_date
{
get { return _Team_date; }
set { _Team_date = value; }
} } public bool InsertTeam_(Model model)
{ string strSql = @"
INSERT Team_(
Team_code,
Team_name,
Team_status,
Team_user,
Team_date)
VALUES (
@Team_code,
@Team_name,
@Team_status,
@Team_user,
@Team_date) ";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Team_code", SqlDbType.NVarChar, ),
new SqlParameter("@Team_name", SqlDbType.NVarChar, ),
new SqlParameter("@Team_status", SqlDbType.NVarChar, ),
new SqlParameter("@Team_user", SqlDbType.NVarChar, ),
new SqlParameter("@Team_date", SqlDbType.DateTime),
};
parameters[].Value = model.Team_code;
parameters[].Value = model.Team_name;
parameters[].Value = model.Team_status;
parameters[].Value = model.Team_user;
parameters[].Value = model.Team_date; using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
if (i > )
{
trans.Commit();
return i > ;
}
else
{
trans.Rollback();
return false;
}
}
catch (System.Exception e)
{
return false;
trans.Rollback();
throw e;
}
}
} } public bool UpdateTeam_ById(Model model)
{ string strSql = @"
Update Team_ SET
Team_code=@Team_code,
Team_name=@Team_name,
Team_status=@Team_status,
Team_user=@Team_user,
Team_date=@Team_date where Team_code=@Team_code ";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Team_code", SqlDbType.NVarChar, ),
new SqlParameter("@Team_name", SqlDbType.NVarChar, ),
new SqlParameter("@Team_status", SqlDbType.NVarChar, ),
new SqlParameter("@Team_user", SqlDbType.NVarChar, ),
new SqlParameter("@Team_date", SqlDbType.DateTime),
};
parameters[].Value = model.Team_code;
parameters[].Value = model.Team_name;
parameters[].Value = model.Team_status;
parameters[].Value = model.Team_user;
parameters[].Value = model.Team_date; using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
if (i > )
{
trans.Commit();
return i > ;
}
else
{
trans.Rollback();
return false;
}
}
catch (System.Exception e)
{
return false;
trans.Rollback();
throw e;
}
}
} } public DataTable GetDataByTeam_(Model model, int pageNo, int pageSize, ref int iRecordCount)
{
List<SqlParameter> parameters = new List<SqlParameter>();
StringBuilder sqlWhere = new StringBuilder();
if (!string.IsNullOrEmpty(model.Team_code.ToString()))
{
sqlWhere.Append(" AND Team_code=@Team_code");
parameters.Add(new SqlParameter("@Team_code", SqlDbType.NVarChar, ) { SqlValue = model.Team_code });
}
if (!string.IsNullOrEmpty(model.Team_name.ToString()))
{
sqlWhere.Append(" AND Team_name=@Team_name");
parameters.Add(new SqlParameter("@Team_name", SqlDbType.NVarChar, ) { SqlValue = model.Team_name });
}
if (!string.IsNullOrEmpty(model.Team_status.ToString()))
{
sqlWhere.Append(" AND Team_status=@Team_status");
parameters.Add(new SqlParameter("@Team_status", SqlDbType.NVarChar, ) { SqlValue = model.Team_status });
}
if (!string.IsNullOrEmpty(model.Team_user.ToString()))
{
sqlWhere.Append(" AND Team_user=@Team_user");
parameters.Add(new SqlParameter("@Team_user", SqlDbType.NVarChar, ) { SqlValue = model.Team_user });
}
if (!string.IsNullOrEmpty(model.Team_date.ToString()))
{
sqlWhere.Append(" AND Team_date=@Team_date");
parameters.Add(new SqlParameter("@Team_date", SqlDbType.NVarChar, ) { SqlValue = model.Team_date });
}
string strSql = string.Format(@"
WITH temp AS ( SELECT rn = ROW_NUMBER() OVER (ORDER BY Team_code desc), *FROM Team_ WHERE 1=1 {0} )
SELECT *,rc=(select count(1) from temp) FROM temp WHERE rn BETWEEN {1} AND {2} ", sqlWhere.ToString(), (pageNo - ) * pageSize + , pageNo * pageSize); DataTable dt = SqlHelper.Query(strSql, parameters.ToArray()).Tables[];
if (dt != null)
{
iRecordCount = int.Parse(dt.Rows[]["rc"].ToString());
return dt;
}
else
{
iRecordCount = ;
return null;
}
} public bool DeleteTeam_(string Team_code)
{
List<SqlParameter> parameters = new List<SqlParameter>();
string strSql = string.Format(@"
DELETE Team_ WHERE Team_code=Team_code ";
int rowAffect =SqlHelper.Query(strSql, parameters.ToArray()).ToInt();;
return rowAffect > ? true : false;
} protected void btnExcel_Click(object sender, System.EventArgs e)
{
int intPageNo = ;
int intPageSize = ;
int recordCount = ;
DataTable dt = GetUnAuthorizeAgreePassedDT( Model model intPageNo, intPageSize, out recordCount);
string strFileName = "导出Excel" + System.DateTime.Now.ToString(" yyyyMMddHHmmss");
ExcelUtility excelUtil = new ExcelUtility(this, strFileName);
List<ExcelHeader> headerS = new List<ExcelHeader>() {
new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.文本, Width = },
new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.文本, Width = },
new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.文本, Width = },
new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.文本, Width = },
new ExcelHeader() { Name = "字段名称自行补全", DataType = EnumColumnDataType.日期, Width = },
};
excelUtil.CreateHeader(headerS);
if (dt != null && dt.Rows.Count > )
{
foreach (DataRow dr in dt.Rows)
{
List<string> dataVals = new List<string>() {
dr[" Team_code"].ToString()
dr[" Team_name"].ToString()
dr[" Team_status"].ToString()
dr[" Team_user"].ToString()
dr[" Team_date"].ToString()
};
excelUtil.CreateItemRow(dataVals);
}
}
excelUtil.Export();
}
有人说这个版本太麻烦，我就做了T4版本
