关系型数据库映射在MVC中的应用,通用分页存储

时间:2020-04-07 14:23来源:江苏十一选五手机版数据库
createprocsp_PublicTurnPageWebSite(@TBNamenvarchar(100卡塔尔国='',--表名,如pinyin@PageSizeint=10,--每页的记录数,默感到10 @CurPageint=1,--表示当前页1@KeyFieldnvarchar(100卡塔尔(قطر‎='ID',--关键字段名,暗中认

createprocsp_PublicTurnPageWebSite( @TBNamenvarchar(100卡塔尔国='',--表名,如pinyin @PageSizeint=10,--每页的记录数,默感到10 @CurPageint=1,--表示当前页1 @KeyFieldnvarchar(100卡塔尔(قطر‎='ID',--关键字段名,暗中认可为ID,该字段须要是表中的目录或无重复和不为空的字段 @KeyAscDescnvarchar(4卡塔尔='ASC',--关键字的升、降序,默许为升序ASC,降序为DESC @Fieldsnvarchar(500卡塔尔(قطر‎='*',--所选拔的列名,暗许为全选 @Conditionnvarchar(200State of Qatar='',--where条件,默感到空 @Ordernvarchar(200卡塔尔=''--排序条件,默感到空 卡塔尔国withencryptionas if@TBName='' begin raiserror('请钦点表名!',11,1卡塔尔(قطر‎ return end if@PageSize=0or@CurPage0 begin raiserror('当前页数和每页的记录数都必妥当先零!',11,1卡塔尔 return end if@KeyAscDesc='DESC' set@KeyAscDesc='' else set@KeyAscDesc='' if@Condition'' set@Condition='where'+@Condition declare@SQLnvarchar(2003卡塔尔 set@SQL='' if@CurPage=1 set@SQL=@SQL+'SelectTop'+cast(@PageSizeasnvarchar(20卡塔尔国卡塔尔国+''+@Fields+'FROM'+@TBName+@Condition+''+@Order else begin declare@iTopNumint set@iTopNum=@PageSize*(@CurPage-1) set@SQL=@SQL+'declare@sLastValuenvarchar(100)'+char(13) set@SQL=@SQL+'SelectTop'+cast(@iTopNumasnvarchar(20))+'@sLastValue='+@KeyField+'FROM'+@TBName+@Condition+''+@Order+char(13) declare@Condition2nvarchar(200) if@Condition='' set@Condition2='where'+@KeyField+@KeyAscDesc+'@sLastValue' else set@Condition2='and'+@KeyField+@KeyAscDesc+'@sLastValue' set@SQL=@SQL+'SelectTop'+cast(@PageSizeasnvarchar(20))+''+@Fields+'FROM'+@TBName+@Condition+@Condition2+@Order end EXECUTEsp_executesql@SQL

持久未有上来写点东西了,前些天刚刚有空,分享一些民用体会,就是有关分页的储存进程,那么些标题应当是老生重谈了,网络的通用存款和储蓄进程的项目已经够多了,可是,好象见到的多数不能够满意一些错综相连的SQL语句的分页,举例下边这句: select''asCheckBox,A.TargetID,A.TargetPeriod,Convert(varchar(10卡塔尔国,B.BeginDate,120卡塔尔(قطر‎asBeginDate, Convert(varchar(10卡塔尔国,B.EndDate,120State of QatarasEndDate,C.SalesCode,C.SalesName,D.CatalogCode,D.CatalogName, E.OrgID,E.OrgName,F.OrgIDasBranchOrgID,F.OrgCodeasBranchOrgCode,F.OrgNameasBranchOrgName, A.Amount,''asDetailButton fromChlSalesTargetasA leftouterjoinChlSalesTargetPeriodasBonA.TargetPeriod=B.TargetPeriod leftouterjoinChlSalesasConA.Sales=C.SalesCode leftouterjoinChlItemCatalogasDonA.ItemCatalog=D.CatalogCode leftouterjoinChlOr瓦斯EonA.OrgID=E.OrgID leftouterjoinChlOr瓦斯FonC.BranchOrgID=F.OrgID whereA.TargetPeriod='二〇〇七07'andA.TargetPeriod='二〇〇七08'andF.OrgCodelike'%123%'andE.OrgCodelike'%123%' orderbyA.TargetPerioddesc,C.SalesName,D.CatalogName下边那句SQL里面有一部分非常景况,比如采取了Convert函数,并且从不主键,有多表连接,有表别称,字段小名等等,这几个意况管理起来恐怕相比吃力,当然,此中的“''asCheckBox”是本身系统个中的特例情状,用来做一些拍卖的。 小编这里提供四个和好开销的通用分页存款和储蓄进程,有何样好的提商谈意见,大家请多都赐教。代码如下:

  3、DataBase基类,查询情势再次来到值是List<T>,并且是分页的,ThePart.dll版本2.0中封装了三个PageInfo类,作为分页的连串。这种措施很机械,也很狗血..建议我们不那样用,下边这些例子是项目中同事写的。为了DataBase这些基类的操作数据库重返值各类类型,让大伙儿知道各类气象的数额都足以拍卖。的那样二个指标,作者或许把例子贴出来。小编自身都难得去费神看。

通用分页存款和储蓄进程----Sp_Paging /**//*

作用:通用分页存款和储蓄进程 参数: @PKvarchar(50卡塔尔(قطر‎,主键,用来排序的单一字段,空的话,表示从未主键,存款和储蓄进度将活动创制标志列主键 @Fieldsvarchar(500卡塔尔国,要出示的字段列表(格式如:ID,Code,Name卡塔尔@Tablesvarchar(1000卡塔尔国,要使用的表集结(OrgState of Qatar@Wherevarchar(500卡塔尔国,查询条件(Codelike'100'卡塔尔(قطر‎@OrderByvarchar(100State of Qatar,排序条件(帮衬三个排序字段,如:ID,Codedesc,NamedescState of Qatar@PageIndexint,当前要显示的页的页索引,索引从1起首,无记录时为0。 @PageSizeint,页大小 创造者:HollisYao 创造日期:二零零七-08-06 备注: ============================================================ */ CreatePROCEDURE[dbo].[Sp_Paging] @PKvarchar(50卡塔尔国='', @Fieldsvarchar(500卡塔尔, @Tablesvarchar(1000卡塔尔, @Wherevarchar(500卡塔尔国='', @OrderByvarchar(100卡塔尔国, @PageIndexint, @PageSizeint AS --替换单引号,幸免布局SQL出错 set@Fields=replace(@Fields,'''',''''''卡塔尔--要施行的SQL,切分为多少个字符串,幸免现身长度超过4k时的题材declare@SQL1varchar(4000卡塔尔国 declare@SQL2varchar(4000State of Qatar set@SQL1='' set@SQL2='' if@Whereisnotnullandlen(ltrim(rtrim(@WhereState of QatarState of Qatar卡塔尔国0 set@Where='where'+@Where else set@Where='where1=1' set@SQL1=@SQL1+'declare@TotalCountint'--声美素佳儿个变量,总记录数 set@SQL1=@SQL1+'declare@PageCountint'--声美素佳儿个变量,总页数 set@SQL1=@SQL1+'declare@PageIndexint'--声贝拉米个变量,页索引 set@SQL1=@SQL1+'declare@StartRowint'--声圣元个变量,当前页第一条记下的索引 set@SQL1=@SQL1+'select@TotalCount=count(*State of Qatarfrom'+@Tables+@Where--获取总记录数 set@SQL1=@SQL1+'if@PageCount=0begin'--假使记录数为0,直接输出空的结果集 set@SQL1=@SQL1+'select'+@Fields+'from'+@Tables+'where11' set@SQL1=@SQL1+'select0asPageIndex,0asPageCount,'+convert(varchar,@PageSize卡塔尔+'asPageSize,0asTotalCount' set@SQL1=@SQL1+'returnend' set@SQL1=@SQL1+'set@PageCount=(@TotalCount+'+convert(varchar,@PageSizeState of Qatar+'-1卡塔尔(قطر‎/'+convert(varchar,@PageSize卡塔尔--获取总页数 set@SQL1=@SQL1+'set@PageIndex='+convert(varchar,@PageIndex卡塔尔(قطر‎--设置科学的页索引 set@SQL1=@SQL1+'if@PageIndex0set@PageIndex=1' set@SQL1=@SQL1+'if@PageIndex@PageCountand@PageCount0set@PageIndex=@PageCount' set@SQL1=@SQL1+'set@StartRow=(@PageIndex-1State of Qatar*'+convert(varchar,@PageSize)+'+1' if(charindex(',',@OrderBy)=0andcharindex(@PK,@OrderBy)0) begin --**************************************************************************** --****************不须要创立主键******************************************** --**************************************************************************** declare@SortDirectionvarchar(10State of Qatar--排序方向,=:升序,=:倒序 set@SortDirection='=' ifcharindex('desc',@OrderBy卡塔尔国0 set@SortDirection='=' set@SQL2=@SQL2+'declare@Sortvarchar(100卡塔尔'--声美赞臣(Karicare卡塔尔(AptamilState of Qatar个变量,用来记录当前页第一条记下的排序字段值 set@SQL2=@SQL2+'setrowcount@StartRow'--设置再次回到记录数结束到当前页的首先条 set@SQL2=@SQL2+'select@Sort='+@PK+'from'+@Tables+@Where+'orderby'+@OrderBy--获取当前页第一个排序字段值 set@SQL2=@SQL2+'setrowcount'+convert(varchar,@PageSize卡塔尔国--设置重临记录数为页大小 set@Where=@Where+'and'+@PK+@SortDirection+'@Sort' set@SQL2=@SQL2+'select'+@菲尔德s+'from'+@Tables+@Where+'orderby'+@OrderBy--输出最后展现结果 end else begin --**************************************************************************** --*************急需创建自拉长主键****************************************** --**************************************************************************** set@SQL2=@SQL2+'declare@EndRowint' set@SQL2=@SQL2+'set@EndRow=@PageIndex*'+convert(varchar,@PageSize卡塔尔set@SQL2=@SQL2+'setrowcount@EndRow' set@SQL2=@SQL2+'declare@PKBeginint'--声贝因美个变量,早先索引 set@SQL2=@SQL2+'declare@PKEndint'--声澳优个变量,停止索引 set@SQL2=@SQL2+'set@PKBegin=@StartRow' set@SQL2=@SQL2+'set@PKEnd=@EndRow' --**************************************************************************** --************对特种字段实行转移,以便能够插入到不常表****************** --**************************************************************************** declare@TempFieldsvarchar(500卡塔尔国 set@TempFields=@Fields set@TempFields=replace(@TempFields,'''''asCheckBox',''卡塔尔(قطر‎set@TempFields=replace(@TempFields,'''''asDetailButton',''卡塔尔set@TempFields=replace(@Temp菲尔德s,'''''asRadio',''卡塔尔set@TempFields=LT奥迪Q7IM(RT福睿斯IM(@TempFieldsState of Qatar卡塔尔ifleft(@TempFields,1卡塔尔国=','--去除最左侧包车型客车逗号 set@TempFields=substring(@TempFields,2,len(@TempFields)State of Qatarifright(@TempFields,1State of Qatar=','--去除最左边的逗号 set@TempFields=substring(@Temp菲尔德s,1,len(@Temp菲尔德s卡塔尔国-1State of Qatarset@SQL2=@SQL2+'selectidentity(int,1,1卡塔尔(قطر‎asPK,'+@TempFields+'into#tbfrom'+@Tables+@Where+'orderby'+@OrderBy --**************************************************************************** --********删去字段的表名前缀,当有字段有小名时,只保留字段小名********* --**************************************************************************** declare@TotalFieldsvarchar(500卡塔尔(قطر‎ declare@tmpvarchar(50卡塔尔国 declare@iint declare@jint declare@iLeftint--左括号的个数 declare@iRightint--右括号的个数 set@i=0 set@j=0 set@iLeft=0 set@iRight=0 set@tmp='' set@TotalFields='' while(len(@FieldsState of Qatar0State of Qatar begin set@i=charindex(',',@Fields卡塔尔(قطر‎ --去除字段的表名前缀 if(@i=0卡塔尔 begin --找不到逗号分割,即意味着只剩下最终多个字段 set@tmp=@Fields end else begin set@tmp=substring(@Fields,1,@i卡塔尔 end set@j=charindex('.',@tmp卡塔尔国if(@j0卡塔尔国 set@tmp=substring(@tmp,@j+1,len(@tmp卡塔尔(قطر‎)--*******当有字段有别称时,只保留字段小名********* --带括号的情景要独立管理,如Convert(varchar(10State of Qatar,B.EndDate,120卡塔尔asEndDate while(charindex('(',@tmp卡塔尔国0卡塔尔国 begin set@iLeft=@iLeft+1 set@tmp=substring(@tmp,charindex('(',@tmp卡塔尔(قطر‎+1,Len(@tmpState of Qatar卡塔尔 end while(charindex('卡塔尔',@tmpState of Qatar0State of Qatar begin set@iRight=@iRight+1 set@tmp=substring(@tmp,charindex('State of Qatar',@tmpState of Qatar+1,Len(@tmp卡塔尔卡塔尔国 end --当括号正好组成代表队的时候,手艺拓宽字段小名的处理 if(@iLeft=@iRight卡塔尔国 begin set@iLeft=0 set@iRight=0 --不对那多少个非常字段作管理:CheckBox、DetailButton、Radio if(charindex('CheckBox',@tmpState of Qatar=0andcharindex('DetailButton',@tmpState of Qatar=0andcharindex('Radio',@tmpState of Qatar=0卡塔尔国begin --剖断是或不是有别名if(charindex('as',@tmp卡塔尔0卡塔尔(قطر‎--小名的第一种写法,带'as'的格式 begin set@tmp=substring(@tmp,charindex('as',@tmpState of Qatar+2,len(@tmpState of Qatar卡塔尔(قطر‎ end else begin if(charindex('',@tmp卡塔尔(قطر‎0卡塔尔(قطر‎--小名的第三种写法,带空格(""State of Qatar的格式 begin while(charindex('',@tmpState of Qatar0State of Qatar begin set@tmp=substring(@tmp,charindex('',@tmp卡塔尔(قطر‎+1,len(@tmpState of Qatar卡塔尔(قطر‎ end end end end set@TotalFields=@TotalFields+@tmp end if(@i=0卡塔尔(قطر‎ set@Fields='' else set@菲尔德s=substring(@Fields,@i+1,len(@Fields卡塔尔卡塔尔 end --print@Total菲尔德s set@SQL2=@SQL2+'select'+@Total菲尔德s+'from#tbwherePKbetween@PKBeginand@PKEndorderbyPK'--输出最终展现结果 set@SQL2=@SQL2+'droptable#tb' end --输出“PageIndex(页索引State of Qatar、PageCount(页数卡塔尔国、PageSize(页大小卡塔尔(قطر‎、TotalCount(总记录数State of Qatar” set@SQL2=@SQL2+'select@PageIndexasPageIndex,@PageCountasPageCount,' +convert(varchar,@PageSize卡塔尔(قطر‎+'asPageSize,@TotalCountasTotalCount' --print@SQL1+@SQL2 exec(@SQL1+@SQL2卡塔尔(قطر‎要是应用那么些通用分页存款和储蓄进程的话,那么调用方法如下:

        public static List<SportsBetRecords> SportsGetListPager(string SaleID, string UserName, string status, string classtype, string StarTime, string EndTime, int? pSize, int? pIndex, out int count)
        {
            pSize = pSize ?? 10;  //每页最大记录数
            pIndex = pIndex ?? 1;  //当前第几页
            PagerInfo pager = new PagerInfo(卡塔尔国;  //ThePart.dll中封装的如此一个分页类PagerInfo
            pager.ProcName = "pr_Pager2007";  //供给的数据库的囤积进度
            pager.TableNames = "SportsBetRecords";  //须求操作的数据库表名
            pager.PageSize = (intState of QatarpSize;    //最大记录数
            pager.CurrentPage = (intState of QatarpIndex;    //当前第几页
            StringBuilder sb = new StringBuilder("1=1 and SaleID='" + SaleID + "'"卡塔尔国;  //可变字符串的准则
            if (!string.IsNullOrEmpty(UserName))
                sb.Append(" and UserName='" + UserName + "'");  
            if (!string.IsNullOrEmpty(StarTime) && !string.IsNullOrEmpty(EndTime))
                sb.Append(" and [T] between '" + Convert.ToDateTime(StarTime) + "' and '" + Convert.ToDateTime(EndTime).AddDays(1) + "'");
            pager.Condition = sb.ToString(卡塔尔国;    //sql里面where后的条件
            pager.Count = 1;
            pager.Fields = "*";
            pager.IndexName = "Id";
            pager.OrderNames = "[T] desc";
            List<SportsBetRecords> recordList = ORMBase.GetList<SportsBetRecords>(ref pager);
            count = pager.Count;
            return recordList;
        }

选取通用分页存款和储蓄进度进行分页 /**//*

效益:获取发售目的,依据标准 参数: @UserTypeint, @OrgIDvarchar(500卡塔尔(قطر‎, @TargetPeriodBeginnvarchar(50卡塔尔, @TargetPeriodEndnvarchar(50卡塔尔(قطر‎, @BranchOrgCodenvarchar(50卡塔尔(قطر‎, @BranchOrgNamenvarchar(50卡塔尔, @OrgCodenvarchar(50卡塔尔国, @OrgNamenvarchar(50State of Qatar, @SalesCodenvarchar(50卡塔尔(قطر‎, @SalesNamenvarchar(50State of Qatar, @CatalogCodenvarchar(50卡塔尔(قطر‎, @CatalogNamenvarchar(50卡塔尔, @PageIndexint,当前要体现的页的页索引,索引从1开始,无记录时为0。 @PageSizeint,页大小 成立者:HollisYao 创立日期:2007-08-11 备注: ============================================================ */ CreatePROCEDURE[dbo].[GetSalesTargetList] @UserTypeint, @OrgIDnvarchar(500), @TargetPeriodBeginnvarchar(50), @TargetPeriodEndnvarchar(50), @BranchOrgCodenvarchar(50), @BranchOrgNamenvarchar(50), @OrgCodenvarchar(50), @OrgNamenvarchar(50), @SalesCodenvarchar(50), @SalesNamenvarchar(50), @CatalogCodenvarchar(50), @CatalogNamenvarchar(50), @PageIndexint, @PageSizeint AS declare@Conditionnvarchar(2000) set@Condition='' if(@UserType1) set@Condition=@Condition+'andA.OrgIDin('+@OrgID+')' if(len(@TargetPeriodBegin)0) set@Condition=@Condition+'andA.TargetPeriod='''+@TargetPeriodBegin+'''' if(len(@TargetPeriodEnd)0) set@Condition=@Condition+'andA.TargetPeriod='''+@TargetPeriodEnd+'''' if(len(@BranchOrgCode)0) set@Condition=@Condition+'andF.OrgCodelike''%'+@BranchOrgCode+'%''' if(len(@BranchOrgName)0) set@Condition=@Condition+'andF.OrgNamelike''%'+@BranchOrgName+'%''' if(len(@OrgCode)0) set@Condition=@Condition+'andE.OrgCodelike''%'+@OrgCode+'%''' if(len(@OrgName)0) set@Condition=@Condition+'andE.OrgNamelike''%'+@OrgName+'%''' if(len(@SalesCode)0) set@Condition=@Condition+'andC.SalesCodelike''%'+@SalesCode+'%''' if(len(@SalesName)0) set@Condition=@Condition+'andC.SalesNamelike''%'+@SalesName+'%''' if(len(@CatalogCode)0) set@Condition=@Condition+'andD.CatalogCodelike''%'+@CatalogCode+'%''' if(len(@CatalogName)0) set@Condition=@Condition+'andD.CatalogNamelike''%'+@CatalogName+'%''' if(len(@Condition)0) set@Condition=substring(@Condition,5,len(@Condition)) --print@Condition execsp_Paging N'',N'''asCheckBox,A.TargetID,A.TargetPeriod,Convert(varchar(10),B.BeginDate,120)asBeginDate,Convert(varchar(10),B.EndDate,120)asEndDate, C.SalesCode,C.SalesName,D.CatalogCode,D.CatalogName,E.OrgID,E.OrgName,F.OrgIDasBranchOrgID,F.OrgCodeasBranchOrgCode,F.OrgNameasBranchOrgName,A.Amount,''asDetailButton', N'ChlSalesTargetasA leftouterjoinChlSalesTargetPeriodasBonA.TargetPeriod=B.TargetPeriod leftouterjoinChlSalesasConA.Sales=C.SalesCode leftouterjoinChlItemCatalogasDonA.ItemCatalog=D.CatalogCode leftouterjoinChlOrgasEonA.OrgID=E.OrgID leftouterjoinChlOrgasFonC.BranchOrgID=F.OrgID', @Condition, N'A.TargetPerioddesc,C.SalesName,D.CatalogName', @PageIndex,@PageSize

 

以下是相应该积累进程:

USE [Casino]
GO
/****** Object:  StoredProcedure [dbo].[pr_Pager2005]    Script Date: 05/13/2014 15:05:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pr_Pager2005]
(
    @TableNames VARCHAR(4000), -- 表名

    @Fields VARCHAR(1000) = '*', -- 须要再次回到的列(不可能有相似的字段现身,如有相符字段,可把第二个外的其余字段转为小名,如:b.ID As bID卡塔尔(قطر‎
    @Condition VARCHA奥德赛(1500State of Qatar = '', -- 查询条件
    @IndexName VARCHAENVISION(20卡塔尔国 = 'ID', -- 主索引字段(此处作废,只为包容卡塔尔国
    @PageSize INT = 20, -- 每页记录数

    @CurrentPage INT = 1, -- 当前页码
    @OrderNames VARCHA悍马H2(50卡塔尔国 = '', -- 排序字段,要包蕴排序类型
    @OrderType BIT = 1, -- 排序类型,0-降序  1-升序(暗中同意卡塔尔国(此处作废,只为宽容卡塔尔国
    @Count INT=null OUTPUT ,
    @Distinct VARCHA瑞鹰(9卡塔尔(قطر‎='', --去重新记录distinct
    @GroupBy VARCHAR(100)='',
    @TSQL nVARCHA凯雷德(4000State of Qatar='' OUTPUT --这一次施行的TSQL语句或在分页前实行的TSQL
)
AS
    DECLARE @SQL nVARCHAR(max) -- 主语句
    DECLARE @OrderBy VARCHAR(300) -- 排序
    DECLARE @StartRow INT,@ENDRow INT

    SET @SQL = ''
    
    BEGIN
        SET NOCOUNT ON
        IF(@CurrentPage<1)
            SET @CurrentPage = 1

        SET @StartRow = (@CurrentPage-1)*@PageSize+1
        SET @ENDRow = @CurrentPage*@PageSize

        IF(@Condition != '')
            SET @Condition = ' Where ' + @Condition

        IF(@GroupBy!='')
            SET @GroupBy = ' Group By ' + @GroupBy

        IF(@Distinct!='')
            SET @Distinct = @Distinct + ' '

        IF(@OrderNames = '')
            SET @OrderBy = ''
        ELSE
            SET @OrderBy = ' Order By ' + @OrderNames

        IF ((@Count is not null) And @Count > 0 )
        BEGIN

            IF(@GroupBy='')
                SET @SQL = @TSQL + ' Select ' +@Distinct+ ' @c=Count(0) From ' + @TableNames + @Condition
            ELSE
                SET @SQL = @TSQL + ' Select ' +@Distinct+ ' @c=Count(0) From (Select 0 As ID From ' + @TableNames + @Condition + @GroupBy +') As T'

            EXEC sp_executesql @SQL,N'@c INT OUTPUT',@Count OUTPUT
        END

        --以下达成了带Group By和不带Group By的动静
        IF(@GroupBy='')
        Begin
            SET @SQL = @TSQL + ' With T1 As ( Select ' + @Distinct + @Fields + ',Row_Number() Over (' + @OrderBy +') As RowNo From ' + @TableNames + @Condition + ' ) '
            SET @SQL = @SQL + 'Select * From T1 Where RowNo Between ' + Cast(@StartRow As VARCHAR) + ' And ' + Cast(@ENDRow AS VARCHAR)
        END
        ELSE
        Begin
            SET @SQL = @TSQL + ' With T1 As ( Select ' + @Distinct + @Fields +' From ' + @TableNames + @Condition

  • @GroupBy + ' ) '
                SET @SQL = @SQL + 'Select * From (Select *,Row_Number() Over (' + @OrderBy +') As RowNo From T1) As T2 Where RowNo Between ' + Cast(@StartRow As VARCHAR) + ' And ' + Cast(@ENDRow As VARCHAR)
            END
        END
    EXEC (@SQL)

 

   4、依据sql前面where条件查询直接回到实体

     /// <summary>
        /// 根据ID查询等第表音讯
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static MemberGrade GetMemberGById(int id)
        {
            return MemberGrade.GetInfo<MemberGrade>("ID=@ID", new object[] { id });
        }

  5、直接重回全体List表记录:不须求查询条件和写sql语句

         public static UserDetail GetModelAllInfo(int id)
        {
            return UserDetail.ExecQuery<UserDetail>("select * from Users u left join UserInfo ui on u.Id=ui.UserId where u.Id=" + id, new object[] { });
        }

 

 

  6、直接回到全部List表记录:无需查询条件和写sql语句。  实体类.GetList<实体类>(0State of Qatar

  public static List<BetResultInfo> BetRInfo()
        {
            return BetResultInfo.GetList<BetResultInfo>(0);
        }

  

  7、依据Sql前面where条件直接询问重回List<实体音信>:

     /// <summary>
        /// 依据会员名查询会员列表音信
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static List<Members> GetMemberList(string name)
        {
            return Members.GetList<Members>("UserName=@Name", new object[] { name });
        }

  8、查询出单个的值,值是个Object类型:实体类.ExecSQLObj("数据库名","完整的Sql语句",new Object[]{});

              /// <summary>
        /// 依照级别ID查询限注值范围
        /// </summary>
        /// <param name="gradeID"></param>
        /// <returns></returns>
        public static string GetRegardByID(int gradeID)
        {
            string sql = "select LimitValue from Regard where GradeID=" + gradeID;
            return Convert.ToString(Regard.ExecSQLObj("Casino", sql, new object[] { }));
        }

    

    //(1)聚合函数直接再次来到到实体里面ID,

    //  (2State of QatarGetList<实体>(记录条数,"where条件", new object[] { SaleID, ProjectCode, MaxID(where条件里面的参数) }State of Qatar;

        public static List<AccountDetails> GetList(int SaleID,string ProjectCode,int MaxID,out int Count)
        {
            var CountInfo = AccountDetails.GetInfo<AccountDetails>("Count(ID) as ID", "SaleID=@SaleID and ProjectCode=@ProjectCode and ID>@MaxID", new object[] { SaleID, ProjectCode, MaxID });
            if (CountInfo != null && CountInfo.ID.HasValue)
            {
                Count = CountInfo.ID.Value;
            }
            else
            {
                Count = 0;
            }
            return AccountDetails.GetList<AccountDetails>(1000,"SaleID=@SaleID and ProjectCode=@ProjectCode and ID>@MaxID", new object[] { SaleID, ProjectCode, MaxID });
        }

 

   9、根据Sql条件向来询问再次回到DataTable(单个表卡塔尔国和DataTableCollection(多张表),这一个最实用

  实例1:Members那一个实体类与sql里面包车型大巴涉及的表能够未有关系ExecSQLDataTable("Casino", sql, new object[] { }State of Qatar;——Members实体类在这里边只然而起了施行sql语句的效能。不用回去Members实体。

     /// <summary>
        /// 依据ID查询会员音信
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static DataTable GetMemberById(int id)
        {
            string sql = "select * from Members where ID=" + id;
            return Members.ExecSQLDataTable("Casino", sql, new object[] { });
        }

  实例2:直接行使ThePart.DataHelper.ORM查询出表的集聚,在仓储进度之中能够查询多张表DataTable重临值为DataTableCollection,依据第一张表ds[0],第二张表ds[1],就可以

      拿出表的值了。

     /// <summary>
        /// 根据条件查询体育投注记录              二零一六.05.15
        /// </summary>
        /// <param name="userName">游戏的使用者名称</param>
        /// <param name="dateBegin">交易时间初叶</param>
        /// <param name="dateEnd">交易时间截止</param>
        /// <param name="betOrderID">交易单号</param>
        /// <param name="pageSize">每页最大记录数</param>
        /// <param name="pageIndex">当前页</param>
        /// <returns>返回json值</returns>
        public static string GetBetRecordsByPage(string userName, string dateBegin, string dateEnd, string betOrderID, int pageSize, int pageIndex)
        {

            List<IDataParameter> pars = new List<IDataParameter>(){
                new SqlParameter("@userName",userName),
                new SqlParameter("@dateBegin",dateBegin),
                new SqlParameter("@dateEnd",dateEnd),
                new SqlParameter("@betOrderID",betOrderID),
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex)
            };                       
            DataTableCollection ds = ThePart.DataHelper.ORM.ORMBase.ExecProcDataTableCollectionP("Casino", "up_GetBetRecordsByPage", pars);
            DataTable data =  ds[0];

            string jsonData = JsonConvert.SerializeObject(dataState of Qatar;  //须要使用Newtonsoft.Json.dll这几个类库
            return jsonData;
            
        }

 

   10、增多实体新闻,并赶回1或0(影响多少行数)

   /// <summary>
        /// 增添等级新闻
        /// </summary>
        /// <param name="memberGrade"></param>
        /// <returns></returns>
        public static int AddMemberGrade(MemberGrade memberGrade)
        {
            return MemberGrade.Insert<MemberGrade>(memberGrade);
        }

 

  11、直接运用累积进程传参数值,试行增多,修正,删除

        /// <summary>
        /// SportsBetRecords澳洲体育记录更新时间更换后,AccountDetails在原始记录底工上再新扩充加一条相仿父RecordID的笔录
        /// </summary>
        /// <param name="RecordID">原纪录的父ID</param>
        /// <param name="RecordType">记录类型,南美洲体育投注记录类型7</param>
        /// <param name="W">盈利</param>
        /// <param name="T">变动更新时间</param>
        /// <param name="B">投注额</param>
        /// <returns></returns>
        public string AddSportsBetRecord(string RecordID, int RecordType, decimal W, DateTime T, decimal B)
        {

            List<IDataParameter> paralist = new List<IDataParameter>{
                new SqlParameter("@RecordID",RecordID),
                new SqlParameter("@RecordType",RecordType),
                new SqlParameter("@W",W),
                new SqlParameter("@T",T),
                new SqlParameter("@B",B),
            };

            long insertCount = AccountDetails.ExecProcP("UserCenter", "Up_UpAccountDetailsByRecordID", paralist);
            return insertCount.ToString();
        }

  12、更新数据表字段的值: 实体类.Update(整个须求改良的实业实例对象,"where前面包车型客车原则字段表达式"卡塔尔(قطر‎

  实例1:

  public static int Update(W88BetRecords item)
        {
            return W88BetRecords.Update(item, "Id=" + item.Id.Value + "and TerraceId=2");
        }

  

  实例2:string.Formart("",,)格式

     /// <summary>
        /// 修改会员卡包余额
        /// </summary>
        /// <param name="blanace"></param>
        /// <param name="userID"></param>
        /// <param name="walletID"></param>
        /// <returns></returns>
        public static int DetailMoneyByID(decimal blanace, int userID, int walletID)
        {
            return MemberMoney.Update<MemberMoney>(new MemberMoney { Balance = blanace }, string.Format("UserID='{0}' and WalletID={1}", userID, walletID));
        }

 

编辑:江苏十一选五手机版数据库 本文来源:关系型数据库映射在MVC中的应用,通用分页存储

关键词:

  • 上一篇:没有了
  • 下一篇:没有了