這裡用的Dapper
using static Dapper.SqlMapper;
using Dapper;
/// <summary>
/// 分页-可以多表(推荐参数查询)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="sql">必须是个表名或视图(可含条件),视图比如 (select * from table) tt</param>
/// <param name="orderby">比如 id asc</param>
/// <param name="parames">参数,比如where age>=@Age,new {Category=1, SubCategory=2}</param>
/// <returns></returns>
public static Pager<T> PageMulite<T>(int pageIndex, int pageSize, string sql, string orderby, object parames)
{
bool CheckNullOrEmpty = string.IsNullOrEmpty(orderby) || string.IsNullOrWhiteSpace(orderby);
CheckHelper.isTrue(CheckNullOrEmpty, "orderby不能为空");
if (pageSize == 0)
pageSize = 10;
if (pageIndex == 0)
pageIndex = 1;
int start = (pageIndex - 1) * pageSize + 1;
int end = pageIndex * pageSize;
StringBuilder sqlText = new StringBuilder();
sqlText.AppendFormat(" select * from (SELECT ROW_NUMBER() OVER(ORDER BY {0} ) as RID,* from ({1}) tt", orderby, sql);
sqlText.Append(") as tempTable WHERE RID BETWEEN " + start + " AND " + end);
//查询总条数
string sqlCount = string.Format("select count(1) from ({0}) tt", sql);
using (IDbConnection conn = Db.GetDbConnectionRead())
{
var list = SqlMapper.Query<T>(conn, sqlText.ToString(), parames);
var result = new Pager<T>();
result.PageIndex = pageIndex;
result.PageSize = pageSize;
result.TotalCount = SqlMapper.QuerySingle<int>(conn, sqlCount, parames);
result.TotalPages = (result.TotalCount + pageSize - 1) / pageSize; //分页
result.Items = list.ToList();
return result;
}
}
/// <summary>
/// 执行事务,批量(sql和参数装一个object数组中,数组里面下标0为sql语句,1为SqlParameter[]参数)集合,返回受影响行数
/// </summary>
/// <param name="cmdList">参数列表,数组里面下标0为sql语句,1为SqlParameter[]参数</param>
/// <returns>返回执行成功数</returns>
public static int ExecSqlTransacation(List<object[]> cmdList)
{
int count = 0;
SqlCommand cmd = null;
SqlConnection conn = null;
SqlTransaction tran = null;
try
{
conn = new SqlConnection(Db.connString);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
tran = conn.BeginTransaction();
foreach (object[] arr in cmdList)
{
string sql = arr[0] as string; //sql语句
cmd = new SqlCommand(sql, conn);
if (arr[1] != null)
{
SqlParameter[] parmes = arr[1] as SqlParameter[];//参数
cmd.Parameters.AddRange(parmes);
}
cmd.Transaction = tran;
int efCount = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
count += efCount;
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw new DbException(ex.Message, ex);
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
return count;
}