正文
ORMBase对象/关系型数据库映射在MVC中的应用(二)
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
3、DataBase基类，查询方法返回值是List<T>,并且是分页的，ThePart.dll版本2.0中封装了一个PageInfo类，作为分页的类型。这种方法很机械，也很狗血..建议大家不这么用，下面这个例子是项目中同事写的。为了DataBase这个基类的操作数据库返回值各种类型，让人们知道各种情况的数据都可以处理。的这么一个目的，我还是把例子贴出来。我自己都难得去费神看。
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_Pager2005";//需要的数据库的存储过程
pager.TableNames = "SportsBetRecords";//需要操作的数据库表名
pager.PageSize = (int)pSize;//最大记录数
pager.CurrentPage = (int)pIndex;//当前第几页
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;
}
以下是相应该存储过程：
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 VARCHAR(1500) = '', -- 查询条件
@IndexName VARCHAR(20) = 'ID', -- 主索引字段(此处作废，只为兼容)
@PageSize INT = 20, -- 每页记录数
@CurrentPage INT = 1, -- 当前页码
@OrderNames VARCHAR(50) = '', -- 排序字段，要包括排序类型
@OrderType BIT = 1, -- 排序类型,0-降序 1-升序(默认)(此处作废，只为兼容)
@Count INT=null OUTPUT ,
@Distinct VARCHAR(9)='', --去重复记录distinct
@GroupBy VARCHAR(100)='',
@TSQL nVARCHAR(4000)='' 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<实体类>(0)
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,
// (2)GetList<实体>(记录条数,"where条件", new object[] { SaleID, ProjectCode, MaxID（where条件里面的参数） });
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[] { });——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>
/// 根据条件查询体育投注记录 2014.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(data); //需要用到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));
}
