儲存過程分頁 Ado.Net分頁 EF分頁 滿足90%以上

儲存過程分頁：

create proc PR_PagerDataByTop
@pageIndex int,
@pageSize int,
@count int out
as
select top(@pageSize) * from dbo.userInfo where ID not in
(
    select top((@pageIndex-1)*@pageSize) ID from dbo.userInfo
)
set @count = (select COUNT(1) from dbo.userInfo)

alter proc PR_PagerDataByRow
@pageIndex int,
@pageSize int,
@count int out
as
select * from 
(
    select *,ROW_NUMBER() over (order by id) as xh from dbo.userInfo 
)as tb2
where tb2.xh between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize
set @count = (select COUNT(1) from dbo.userInfo)

Ado.Net分頁:

這裡只上傳一個例子(同理：可以呼叫上面2個儲存過程)：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace UseStoredProcedure
{
    class Program
    {
        static void Main(string[] args)
        {
            string conStr = "server=.;database=MyDb;uid=sa;pwd=123";
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "PR_PagerData";
                    cmd.Parameters.Add("@pageSize", 12);
                    cmd.Parameters.Add("@pageIndex", 1);
                    cmd.Parameters.Add("@count", 1).Direction = ParameterDirection.Output;
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        conn.Open();
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        int count = (int)cmd.Parameters["@count"].Value;
                    }
                }
            }
        }
    }
}

EF分頁:

Linq(EF實際也是呼叫Ado.Net):

public IEnumerable<T> GetPageUserInfos<s>(int pageIndex, int pageSize, Func<T, bool> whereLmd, Func<T, s> orderLmd, out int count, bool isAsc)
        {
            var set = db.Set<T>();
            count = set.Count();
            return set.Where(whereLmd).OrderBy(orderLmd).
                Skip(pageSize * (pageIndex - 1)).Take(pageSize);
        } 

EF增刪改查：

        #region 增刪改查
        //增
        public int Add(T model)
        {
            db.Set<T>().Add(model);
            return db.SaveChanges();
        }

        //刪
        public int Del(T model)
        {
            db.Set<T>().Attach(model);
            db.Set<T>().Remove(model);
            return db.SaveChanges();
        }

        public int DelBy(Func<T, bool> whereLmd)
        {
            db.Set<T>().RemoveRange(db.Set<T>().Where(whereLmd));
            return db.SaveChanges();
        }

        //改
        public int Modify(T model, params string[] propName)
        {
            DbEntityEntry de = db.Entry(model);
            de.State = System.Data.Entity.EntityState.Unchanged;
            foreach (var str in propName)
            {
                de.Property(str).IsModified = true;
            }
            return db.SaveChanges();
        }

        //查
        public IQueryable<T> GetList(Expression<Func<T, int, bool>> whereLmd)
        {
            return db.Set<T>().Where(whereLmd);
        }

        //分頁
        public IEnumerable<T> GetPageUserInfos<s>(int pageIndex, int pageSize, Func<T, bool> whereLmd, Func<T, s> orderLmd, out int count, bool isAsc)
        {
            var set = db.Set<T>();
            count = set.Count();
            return set.Where(whereLmd).OrderBy(orderLmd).
                Skip(pageSize * (pageIndex - 1)).Take(pageSize);
        } 
        #endregion

TT模板：

<#@ template language="C#" debug="false" hostspecific="true"#>
<#@ include file="EF.Utility.CS.ttinclude"#><#@
 output extension=".cs"#>
 
 <#

    CodeGenerationTools code = new CodeGenerationTools(this);
    MetadataLoader loader = new MetadataLoader(this);
    CodeRegion region = new CodeRegion(this, 1);
    MetadataTools ef = new MetadataTools(this);

    string inputFile = @"..\MODEL\{模板檔名}.edmx";

    EdmItemCollection ItemCollection = loader.CreateEdmItemCollection(inputFile);
    string namespaceName = code.VsNamespaceSuggestion();

    EntityFrameworkTemplateFileManager fileManager = EntityFrameworkTemplateFileManager.Create(this);

#>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace IDAL
{
<#

    // Emit Entity Types
    foreach (EntityType entity in ItemCollection.GetItems<EntityType>().OrderBy(e => e.Name))
    {
        //fileManager.StartNewFile(entity.Name + "RepositoryExt.cs");
        //BeginNamespace(namespaceName, code);
    
#>
    public partial interface I<#=            entity.Name#>DAL : IBaseDAL<MODEL.<#=            entity.Name#>>
    {
    }

<#    }#>

}

 

 

MySqlHelper:

using System;
using System.Data;
using System.Data.SqlClient;

namespace AdoNet
{
    public static class SQLHelper
    {
        #region simplerHelper
        static string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
        #region 1.0 返回受影響行數
        public static int ExecuteNonQuery(string sql, params SqlParameter[] sqlparams)
        {
            return ExecuteNonQuery(sql, CommandType.Text, sqlparams);
        }
        public static int ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] sqlparams)
        {
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        cmd.CommandType = type;
                        if (sqlparams.Length > 0)
                        {
                            cmd.Parameters.AddRange(sqlparams);
                        }
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch
                    {
                        return -1;
                    }
                }
            }
        } 
        #endregion
        #region 2.0 查詢單個值
        public static object ExecuteScalar(string sql, params SqlParameter[] sqlparams)
        {
            return ExecuteScalar(sql, CommandType.Text, sqlparams);
        }
        public static object ExecuteScalar(string sql, CommandType type, params SqlParameter[] sqlparams)
        {
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        cmd.CommandType = type;
                        if (sqlparams.Length > 0)
                        {
                            cmd.Parameters.AddRange(sqlparams);
                        }
                        return cmd.ExecuteScalar();
                    }
                    catch
                    {
                        return null;
                    }
                }
            }
        } 
        #endregion
        #region 3.0 查詢單表
        public static DataTable GetTable(string sql, params SqlParameter[] sqlparams)
        {
            return GetTable(sql, CommandType.Text, sqlparams);
        }
        public static DataTable GetTable(string sql, CommandType type, params SqlParameter[] sqlparams)
        {
            using (SqlConnection conn = new SqlConnection(strCon))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    try
                    {
                        conn.Open();
                        cmd.CommandType = type;
                        if (sqlparams.Length > 0)
                        {
                            cmd.Parameters.AddRange(sqlparams);
                        }
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            DataTable dt = new DataTable();
                            da.Fill(dt);
                            return dt;
                        }
                    }
                    catch
                    {
                        return null;
                    }
                }
            }
        } 
        #endregion
        #endregion
    }
}