首页 » SQL Server » C#自己封裝的SQL幫助類

C#自己封裝的SQL幫助類

原文 http://blog.csdn.net/u011511086/article/details/79138864

2018-01-24 02:01:44阅读(346)

這裡用的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;
        }

最新发布

CentOS专题

关于本站

5ibc.net旗下博客站精品博文小部分原创、大部分从互联网收集整理。尊重作者版权、传播精品博文,让更多编程爱好者知晓!

小提示

按 Ctrl+D 键,
把本文加入收藏夹