存储过程分页:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 create proc PR_PagerDataByTop 2 @pageIndex int, 3 @pageSize int, 4 @count int out 5 as 6 select top(@pageSize) * from dbo.userInfo where ID not in 7 ( 8 select top((@pageIndex-1)*@pageSize) ID from dbo.userInfo 9 ) 10 set @count = (select COUNT(1) from dbo.userInfo)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 alter proc PR_PagerDataByRow 2 @pageIndex int, 3 @pageSize int, 4 @count int out 5 as 6 select * from 7 ( 8 select *,ROW_NUMBER() over (order by id) as xh from dbo.userInfo 9 )as tb2 10 where tb2.xh between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize 11 set @count = (select COUNT(1) from dbo.userInfo)
Ado.Net分页:
这里只上传一个例子(同理:可以调用上面2个存储过程):
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 6 using System.Data; 7 using System.Data.SqlClient; 8 9 namespace UseStoredProcedure 10 { 11 class Program 12 { 13 static void Main(string[] args) 14 { 15 string conStr = "server=.;database=MyDb;uid=sa;pwd=123"; 16 using (SqlConnection conn = new SqlConnection(conStr)) 17 { 18 using (SqlCommand cmd = new SqlCommand()) 19 { 20 cmd.Connection = conn; 21 cmd.CommandType = CommandType.StoredProcedure; 22 cmd.CommandText = "PR_PagerData"; 23 cmd.Parameters.Add("@pageSize", 12); 24 cmd.Parameters.Add("@pageIndex", 1); 25 cmd.Parameters.Add("@count", 1).Direction = ParameterDirection.Output; 26 using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 27 { 28 conn.Open(); 29 DataSet ds = new DataSet(); 30 da.Fill(ds); 31 int count = (int)cmd.Parameters["@count"].Value; 32 } 33 } 34 } 35 } 36 } 37 }
EF分页:
Linq(EF实际也是调用Ado.Net):
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 public IEnumerable<T> GetPageUserInfos<s>(int pageIndex, int pageSize, Func<T, bool> whereLmd, Func<T, s> orderLmd, out int count, bool isAsc) 2 { 3 var set = db.Set<T>(); 4 count = set.Count(); 5 return set.Where(whereLmd).OrderBy(orderLmd). 6 Skip(pageSize * (pageIndex - 1)).Take(pageSize); 7 }
EF增删改查:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 #region 增删改查 2 //增 3 public int Add(T model) 4 { 5 db.Set<T>().Add(model); 6 return db.SaveChanges(); 7 } 8 9 //删 10 public int Del(T model) 11 { 12 db.Set<T>().Attach(model); 13 db.Set<T>().Remove(model); 14 return db.SaveChanges(); 15 } 16 17 public int DelBy(Func<T, bool> whereLmd) 18 { 19 db.Set<T>().RemoveRange(db.Set<T>().Where(whereLmd)); 20 return db.SaveChanges(); 21 } 22 23 //改 24 public int Modify(T model, params string[] propName) 25 { 26 DbEntityEntry de = db.Entry(model); 27 de.State = System.Data.Entity.EntityState.Unchanged; 28 foreach (var str in propName) 29 { 30 de.Property(str).IsModified = true; 31 } 32 return db.SaveChanges(); 33 } 34 35 //查 36 public IQueryable<T> GetList(Expression<Func<T, int, bool>> whereLmd) 37 { 38 return db.Set<T>().Where(whereLmd); 39 } 40 41 //分页 42 public IEnumerable<T> GetPageUserInfos<s>(int pageIndex, int pageSize, Func<T, bool> whereLmd, Func<T, s> orderLmd, out int count, bool isAsc) 43 { 44 var set = db.Set<T>(); 45 count = set.Count(); 46 return set.Where(whereLmd).OrderBy(orderLmd). 47 Skip(pageSize * (pageIndex - 1)).Take(pageSize); 48 } 49 #endregion
TT模板:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 <#@ template language="C#" debug="false" hostspecific="true"#> 2 <#@ include file="EF.Utility.CS.ttinclude"#><#@ 3 output extension=".cs"#> 4 5 <# 6 7 CodeGenerationTools code = new CodeGenerationTools(this); 8 MetadataLoader loader = new MetadataLoader(this); 9 CodeRegion region = new CodeRegion(this, 1); 10 MetadataTools ef = new MetadataTools(this); 11 12 string inputFile = @"..\MODEL\{模板文件名}.edmx"; 13 14 EdmItemCollection ItemCollection = loader.CreateEdmItemCollection(inputFile); 15 string namespaceName = code.VsNamespaceSuggestion(); 16 17 EntityFrameworkTemplateFileManager fileManager = EntityFrameworkTemplateFileManager.Create(this); 18 19 #> 20 21 using System; 22 using System.Collections.Generic; 23 using System.Linq; 24 using System.Text; 25 26 namespace IDAL 27 { 28 <# 29 30 // Emit Entity Types 31 foreach (EntityType entity in ItemCollection.GetItems<EntityType>().OrderBy(e => e.Name)) 32 { 33 //fileManager.StartNewFile(entity.Name + "RepositoryExt.cs"); 34 //BeginNamespace(namespaceName, code); 35 36 #> 37 public partial interface I<#= entity.Name#>DAL : IBaseDAL<MODEL.<#= entity.Name#>> 38 { 39 } 40 41 <# }#> 42 43 }
MySqlHelper:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 using System; 2 using System.Data; 3 using System.Data.SqlClient; 4 5 namespace AdoNet 6 { 7 public static class SQLHelper 8 { 9 #region simplerHelper 10 static string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["sql"].ConnectionString; 11 #region 1.0 返回受影响行数 12 public static int ExecuteNonQuery(string sql, params SqlParameter[] sqlparams) 13 { 14 return ExecuteNonQuery(sql, CommandType.Text, sqlparams); 15 } 16 public static int ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] sqlparams) 17 { 18 using (SqlConnection conn = new SqlConnection(strCon)) 19 { 20 using (SqlCommand cmd = new SqlCommand(sql, conn)) 21 { 22 try 23 { 24 conn.Open(); 25 cmd.CommandType = type; 26 if (sqlparams.Length > 0) 27 { 28 cmd.Parameters.AddRange(sqlparams); 29 } 30 int rows = cmd.ExecuteNonQuery(); 31 return rows; 32 } 33 catch 34 { 35 return -1; 36 } 37 } 38 } 39 } 40 #endregion 41 #region 2.0 查询单个值 42 public static object ExecuteScalar(string sql, params SqlParameter[] sqlparams) 43 { 44 return ExecuteScalar(sql, CommandType.Text, sqlparams); 45 } 46 public static object ExecuteScalar(string sql, CommandType type, params SqlParameter[] sqlparams) 47 { 48 using (SqlConnection conn = new SqlConnection(strCon)) 49 { 50 using (SqlCommand cmd = new SqlCommand(sql, conn)) 51 { 52 try 53 { 54 conn.Open(); 55 cmd.CommandType = type; 56 if (sqlparams.Length > 0) 57 { 58 cmd.Parameters.AddRange(sqlparams); 59 } 60 return cmd.ExecuteScalar(); 61 } 62 catch 63 { 64 return null; 65 } 66 } 67 } 68 } 69 #endregion 70 #region 3.0 查询单表 71 public static DataTable GetTable(string sql, params SqlParameter[] sqlparams) 72 { 73 return GetTable(sql, CommandType.Text, sqlparams); 74 } 75 public static DataTable GetTable(string sql, CommandType type, params SqlParameter[] sqlparams) 76 { 77 using (SqlConnection conn = new SqlConnection(strCon)) 78 { 79 using (SqlCommand cmd = new SqlCommand(sql, conn)) 80 { 81 try 82 { 83 conn.Open(); 84 cmd.CommandType = type; 85 if (sqlparams.Length > 0) 86 { 87 cmd.Parameters.AddRange(sqlparams); 88 } 89 using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 90 { 91 DataTable dt = new DataTable(); 92 da.Fill(dt); 93 return dt; 94 } 95 } 96 catch 97 { 98 return null; 99 } 100 } 101 } 102 } 103 #endregion 104 #endregion 105 } 106 }