C# (CSharp) SqlDapperHelper.CreateConnection Exemples

Exemple #1

        //PageInfo参数必须是dynamic par = new ExpandoObject();
        public void QueryByPage <T>(PageInfo <T> pageinfo)
        {
            if (pageinfo.ReturnFields == null)
            {
                pageinfo.ReturnFields = "[id],[username],[pwd],[sex]";
            }
            if (pageinfo.Where != null)
            {
                pageinfo.Where = "WHERE " + pageinfo.Where;
            }
            if (pageinfo.OrderBy != null)
            {
                pageinfo.OrderBy = "ORDER BY " + pageinfo.OrderBy;
            }
            else
            {
                pageinfo.OrderBy = "ORDER BY [id] DESC";
            }

            string sqlData = null;

            if (pageinfo.Skip == 0)
            {
                sqlData = string.Format("SELECT TOP {0} {1} FROM [admins] {2} {3}", pageinfo.Take, pageinfo.ReturnFields, pageinfo.Where, pageinfo.OrderBy);
            }
            else
            {
                sqlData = string.Format("SELECT TOP {3} {0} FROM (SELECT ROW_NUMBER() OVER({1}) AS Num,{0} FROM [admins] {2}) AS [T] WHERE [T].[Num]>@skip", pageinfo.ReturnFields, pageinfo.OrderBy, pageinfo.Where, pageinfo.Take);
            }
            string sql = string.Format(@"
DECLARE @total BIGINT
SET @total=(SELECT COUNT(1) FROM [admins] {0})
SELECT @total
IF(@total!=0)
	BEGIN
	  {1}
	END"    , pageinfo.Where, sqlData);

            using (var conn = SqlDapperHelper.CreateConnection())
            {
                Dapper.SqlMapper.GridReader Reader = null;

                if (pageinfo.Params == null)
                {
                    Reader = conn.QueryMultiple(sql, new { skip = pageinfo.Skip });
                }
                else
                {
                    pageinfo.Params.skip = pageinfo.Skip;
                    Reader = conn.QueryMultiple(sql, (object)pageinfo.Params);
                }

                pageinfo.Total = Reader.Read <long>().FirstOrDefault();
                if (pageinfo.Total != 0)
                {
                    pageinfo.Data = Reader.Read <T>();
                }
                else
                {
                    pageinfo.Data = new List <T>();
                }
                Reader.Dispose();
            }
        }

Exemple #2

        //PageInfo参数必须是dynamic par = new ExpandoObject();
        public void QueryByPage <T>(PageInfo <T> pageinfo)
        {
            if (pageinfo.ReturnFields == null)
            {
                pageinfo.ReturnFields = "[id],[exam_id],[grade_class],[exam_subject],[exam_avg],[exam_stdevp],[exam_max],[exam_allpassnum],[exam_onepassnum],[exam_twopassnum],[exam_threepassnum],[exam_greatnum],[exam_passnum],[exam_allrank],[exam_onerank],[exam_tworank],[exam_threerank]";
            }
            if (pageinfo.Where != null)
            {
                pageinfo.Where = "WHERE " + pageinfo.Where;
            }
            if (pageinfo.OrderBy != null)
            {
                pageinfo.OrderBy = "ORDER BY " + pageinfo.OrderBy;
            }
            else
            {
                pageinfo.OrderBy = "ORDER BY [id] DESC";
            }

            string sqlData = null;

            if (pageinfo.Skip == 0)
            {
                sqlData = string.Format("SELECT TOP {0} {1} FROM [examtotalcount_without] {2} {3}", pageinfo.Take, pageinfo.ReturnFields, pageinfo.Where, pageinfo.OrderBy);
            }
            else
            {
                sqlData = string.Format("SELECT TOP {3} {0} FROM (SELECT ROW_NUMBER() OVER({1}) AS Num,{0} FROM [examtotalcount_without] {2}) AS [T] WHERE [T].[Num]>@skip", pageinfo.ReturnFields, pageinfo.OrderBy, pageinfo.Where, pageinfo.Take);
            }
            string sql = string.Format(@"
DECLARE @total BIGINT
SET @total=(SELECT COUNT(1) FROM [examtotalcount_without] {0})
SELECT @total
IF(@total!=0)
	BEGIN
	  {1}
	END"    , pageinfo.Where, sqlData);

            using (var conn = SqlDapperHelper.CreateConnection())
            {
                Dapper.SqlMapper.GridReader Reader = null;

                if (pageinfo.Params == null)
                {
                    Reader = conn.QueryMultiple(sql, new { skip = pageinfo.Skip });
                }
                else
                {
                    pageinfo.Params.skip = pageinfo.Skip + 1;
                    pageinfo.Params.take = pageinfo.Take;
                    Reader = conn.QueryMultiple(sql, (object)pageinfo.Params);
                }

                pageinfo.Total = Reader.Read <long>().FirstOrDefault();
                if (pageinfo.Total != 0)
                {
                    pageinfo.Data = Reader.Read <T>();
                }
                else
                {
                    pageinfo.Data = new List <T>();
                }
                Reader.Dispose();
            }
        }