T4模板
<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ output extension=".cs" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Data.SQLite" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SQLite" #>
//------------------------------------------------------------------------------
//姜佳泉测试T4模板
//说明:与DapperHelper绑定使用，基础功能只有增删改，没有查询，没有分页。
//理由-->只作通用性，各数据库分页及高级查询语法各异，需要不同sql语句通过DapperHelper实现高级查询与分页
//特殊说明:id自增
//------------------------------------------------------------------------------
<#
//配置
//连接数据库字符串SqlLite数据库
string connectionString = "Data Source=C:\\Users\\jjq\\Desktop\\T4Test\\WebApplication1\\App_Data\\student.db;Pooling=true;FailIfMissing=false";
//指定表
string tableName="students";
//自增的主键
string ID="id";
//指定空间名
string nameSpace="WebApplication1";
#>
<#
//连接数据库，获取表头
SQLiteConnection connection = new SQLiteConnection(connectionString);
DataSet ds = new DataSet();
connection.Open();
SQLiteDataAdapter command = new SQLiteDataAdapter("select * from "+tableName+" WHERE 1 = 0 ", new SQLiteConnection(connectionString));
command.Fill(ds, "ds");
connection.Close();
#>
<#
//拼sql
string insertSql1="insert into "+tableName+"(";
string insertSql2=") values(";
string insertSql3=")";
string updateSql1="update "+tableName+" set ";
string updateSql2="";
string updateSql3=" where "+ID+"=@"+ID;
#>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace <#=nameSpace#>
{
public class <#=tableName#>
{
<#
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
if (ds.Tables[0].Columns[i].DataType.Name.ToString()=="Int64"||ds.Tables[0].Columns[i].DataType.Name.ToString()=="Int32")
{
#>
public int <#=ds.Tables[0].Columns[i].ColumnName#> { get; set; }
<#
}
else
{
#>
public <#=ds.Tables[0].Columns[i].DataType.Name#> <#=ds.Tables[0].Columns[i].ColumnName#> { get; set; }
<#
}
if (ds.Tables[0].Columns[i].ColumnName!=ID)
{
insertSql1+=ds.Tables[0].Columns[i].ColumnName+",";
insertSql2+="@"+ds.Tables[0].Columns[i].ColumnName+",";
updateSql2+= ds.Tables[0].Columns[i].ColumnName+"="+"@"+ds.Tables[0].Columns[i].ColumnName+",";
}
}
string insertSql=insertSql1.Substring(0, insertSql1.Length - 1)+insertSql2.Substring(0, insertSql2.Length - 1)+insertSql3;
string deleteSql="delete from students where "+ID+"=@"+ID;
string updateSql=updateSql1+updateSql2.Substring(0, updateSql2.Length - 1)+updateSql3;
#>
//新增
public Message Insert()
{
Message mg = new Message();
try
{
int RetNum = DapperHelper<<#=tableName#>>.Execute("<#=insertSql#>", this);
if (RetNum == 1)
{
mg.MessageResult = "Y";
mg.MessageContext = "新增成功";
return mg;
}
else
{
mg.MessageContext = "新增失败";
return mg;
}
}
catch (Exception ex)
{
mg.MessageContext = "Exception，严重错误！";
return mg;
}
}
//删除
public Message Delete()
{
Message mg = new Message();
try
{
int RetNum = DapperHelper<<#=tableName#>>.Execute("<#=deleteSql#>", this);
if (RetNum == 1)
{
mg.MessageResult = "Y";
mg.MessageContext = "删除成功";
return mg;
}
else
{
mg.MessageContext = "删除失败";
return mg;
}
}
catch (Exception ex)
{
mg.MessageContext = "Exception，严重错误！";
return mg;
}
}
//更新
public Message Update()
{
Message mg = new Message();
try
{
int RetNum = DapperHelper<<#=tableName#>>.Execute("<#=updateSql#>", this);
if (RetNum == 1)
{
mg.MessageResult = "Y";
mg.MessageContext = "更新成功";
return mg;
}
else
{
mg.MessageContext = "更新失败";
return mg;
}
}
catch (Exception ex)
{
mg.MessageContext = "Exception，严重错误！";
return mg;
}
}
}
//返回信息
public class Message
{
private string messageResult = "N";
private string messageContext;
/// <summary>
/// 返回的结果（Y,N）
/// </summary>
public string MessageResult
{
set { messageResult = value; }
get { return messageResult; }
}
/// <summary>
/// 提示内容
/// </summary>
public string MessageContext
{
set { messageContext = value; }
get { return messageContext; }
}
}
}
T4模板产品（测试可用）
//------------------------------------------------------------------------------
//姜佳泉测试T4模板
//说明:与DapperHelper绑定使用，基础功能只有增删改，没有查询，没有分页。
//理由-->只作通用性，各数据库分页及高级查询语法各异，需要不同sql语句通过DapperHelper实现高级查询与分页
//特殊说明:id自增
//------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace WebApplication1
{
public class students
{
public int id { get; set; }
public String name { get; set; }
public String address { get; set; }
//新增
public Message Insert()
{
Message mg = new Message();
try
{
int RetNum = DapperHelper<students>.Execute("insert into students(name,address) values(@name,@address)", this);
if (RetNum == 1)
{
mg.MessageResult = "Y";
mg.MessageContext = "新增成功";
return mg;
}
else
{
mg.MessageContext = "新增失败";
return mg;
}
}
catch (Exception ex)
{
mg.MessageContext = "Exception，严重错误！";
return mg;
}
}
//删除
public Message Delete()
{
Message mg = new Message();
try
{
int RetNum = DapperHelper<students>.Execute("delete from students where id=@id", this);
if (RetNum == 1)
{
mg.MessageResult = "Y";
mg.MessageContext = "删除成功";
return mg;
}
else
{
mg.MessageContext = "删除失败";
return mg;
}
}
catch (Exception ex)
{
mg.MessageContext = "Exception，严重错误！";
return mg;
}
}
//更新
public Message Update()
{
Message mg = new Message();
try
{
int RetNum = DapperHelper<students>.Execute("update students set name=@name,address=@address where id=@id", this);
if (RetNum == 1)
{
mg.MessageResult = "Y";
mg.MessageContext = "更新成功";
return mg;
}
else
{
mg.MessageContext = "更新失败";
return mg;
}
}
catch (Exception ex)
{
mg.MessageContext = "Exception，严重错误！";
return mg;
}
}
}
//返回信息
public class Message
{
private string messageResult = "N";
private string messageContext;
/// <summary>
/// 返回的结果（Y,N）
/// </summary>
public string MessageResult
{
set { messageResult = value; }
get { return messageResult; }
}
/// <summary>
/// 提示内容
/// </summary>
public string MessageContext
{
set { messageContext = value; }
get { return messageContext; }
}
}
}
DapperHelper
using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
using System.Linq;
namespace WebApplication1
{
public class DapperHelper<T>
{
//说明：更换SQLiteConnection与using System.Data.SQLite即可更换其他数据库
/// <summary>
/// 数据库连接字符串
/// </summary>
private static readonly string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
/// <summary>
/// 查询返回列表List《T》
/// .QueryToList("select * from students where id=@id and name=@name", new { id = 1 ,name= "张小花" })
/// </summary>
/// <param name="sql">查询的sql</param>
/// <param name="param">替换参数</param>
/// <returns></returns>
public static List<T> QueryToList(string sql, object param = null)
{
using (SQLiteConnection con = new SQLiteConnection(connectionString))
{
return con.Query<T>(sql, param).ToList();
}
}
/// <summary>
/// 查询返回DataTable
/// .QueryToTable("select * from students where id=???常规sql语句")
/// </summary>
/// <param name="sql">查询的sql</param>
/// <param name="param">替换参数</param>
/// <returns></returns>
public static DataTable QueryToTable(string sql, object param = null)
{
using (SQLiteConnection con = new SQLiteConnection(connectionString))
{
DataTable dt = new DataTable();
var reader = con.ExecuteReader(sql);
dt.Load(reader);
return dt;
}
}
/// <summary>
/// 查询第一个数据,返回一个实体T
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static T QueryFirstOrNull(string sql, object param = null)
{
using (SQLiteConnection con = new SQLiteConnection(connectionString))
{
return con.Query<T>(sql, param).ToList().SingleOrDefault();
}
}
/// <summary>
/// 执行single-增删改-("insert/delete/update T_User set username=@username where uid=@uid", new { username = "李四", uid = 1})
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns>执行影响行数 int 0or1</returns>
public static int Execute(string sql, object param)
{
using (SQLiteConnection con = new SQLiteConnection(connectionString))
{
return con.Execute(sql, param);
}
}
/// <summary>
/// 执行many-增删改-("insert/delete/update T_User set username=@username where uid=@uid", List《T》)
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns>执行影响行数 int 0or1+</returns>
/// List<students> listStu = new List<students>();
public static int ExecuteList(string sql, List<T> t)
{
// listStu.Add(new students { id = 1, name = "新1", address = "年1" });
// listStu.Add(new students { id = 2, name = "快1", address = "乐1" });
// var a = DapperHelper<students>.ExecuteList("update students set name = @name, address = @address where id=@id", listStu);
using (SQLiteConnection con = new SQLiteConnection(connectionString))
{
return con.Execute(sql, t);
}
}
/// <summary>
/// 无参事务-List《stringSql》
/// </summary>
/// <param name="sqlarr">多条SQL</param>
/// <param name="param">param</param>
/// <returns></returns>
public static int ExecuteTransaction(List<string> sqlList)
{
using (SQLiteConnection con = new SQLiteConnection(connectionString))
{
con.Open();//在dapper中使用事务，需要手动打开连接
using (var transaction = con.BeginTransaction())
{
try
{
int result = 0;
foreach (var sql in sqlList)
{
result += con.Execute(sql, null, transaction);
}
transaction.Commit();
con.Close();//在dapper中使用事务，需要手动关闭连接
return result;
}
catch (Exception ex)
{
transaction.Rollback();
con.Close();//在dapper中使用事务，需要手动关闭连接
return 0;
}
}
}
}
/// <summary>
/// 带参事务-Dictionary.Add（"Insert into Users values (@UserName, @Email, @Address)"，new { UserName = "jack", Email = "380234234@qq.com", Address = "上海" })
/// </summary>
/// <param name="dic"></param>
/// <returns></returns>
public static int ExecuteTransactionDic(Dictionary<string, T> dic)
{
//Dictionary<string, students> dic = new Dictionary<string, students>();
//dic.Add("delete from students where id=@id", new students { id = 1 });
//dic.Add("delete from students where id=@id ", new students { id = 2 });
using (SQLiteConnection con = new SQLiteConnection(connectionString))
{
con.Open();//在dapper中使用事务，需要手动打开连接
using (var transaction = con.BeginTransaction())
{
try
{
int result = 0;
foreach (var param in dic)
{
result += con.Execute(param.Key, param.Value, transaction);
}
transaction.Commit();
con.Close();//在dapper中使用事务，需要手动关闭连接
return result;
}
catch (Exception ex)
{
transaction.Rollback();
con.Close();//在dapper中使用事务，需要手动关闭连接
return 0;
}
}
}
}
}
}