存储过程分页 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)

top pager

 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)

row_number pager

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", );

                     cmd.Parameters.Add("@pageIndex", );

                     cmd.Parameters.Add("@count", ).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 - )).Take(pageSize);

         }

Linq通用完整分页

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 - )).Take(pageSize);

         }

         #endregion

EF通用增删该查泛型

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, );

     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 > )

                         {

                             cmd.Parameters.AddRange(sqlparams);

                         }

                         int rows = cmd.ExecuteNonQuery();

                         return rows;

                     }

                     catch

                     {

                         return -;

                     }

                 }

             }

         }

         #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 > )

                         {

                             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 > )

                         {

                             cmd.Parameters.AddRange(sqlparams);

                         }

                         using (SqlDataAdapter da = new SqlDataAdapter(cmd))

                         {

                             DataTable dt = new DataTable();

                             da.Fill(dt);

                             return dt;

                         }

                     }

                     catch

                     {

                         return null;

                     }

                 }

             }

         }

         #endregion

         #endregion

     }

 }

MySqlHelper

巴特西

存储过程分页 Ado.Net分页 EF分页满足90%以上

最新文章

热门文章

巴特西

存储过程分页 Ado.Net分页 EF分页 满足90%以上

最新文章

热门文章

存储过程分页 Ado.Net分页 EF分页满足90%以上