public VWCGOrderEntity GetVWCGOrderByCode(long cgcode, int memid) { VWCGOrderEntity entity = new VWCGOrderEntity(); string where = " where Code=@Code "; if (memid != -1) { where += "and CGMemId=@CGMemId "; } string sql = @" SELECT a.[Id],a.[Code],a.[Title],a.[SourceCode],a.[CreateTime],a.[TransFee],a.[Status], a.[SendTime],a.[Remark], b.[ReceiptName] ,b.[ReceiptProvince] ,b.[ReceiptCity] ,b.[ReceiptTown] ,b.[ReceiptAddress] ,b.[ReceiptPhone] FROM dbo.[CGOrder] a WITH(NOLOCK) inner join dbo.[CGOrderAddress] b WITH(NOLOCK) on a.Code=b.CGOrderCode " + where; DbCommand cmd = db.GetSqlStringCommand(sql); db.AddInParameter(cmd, "@Code", DbType.Int64, cgcode); if (memid != -1) { db.AddInParameter(cmd, "@CGMemId", DbType.Int32, memid); } using (IDataReader reader = db.ExecuteReader(cmd)) { if (reader.Read()) { entity.Id = StringUtils.GetDbInt(reader["Id"]); entity.Code = StringUtils.GetDbLong(reader["Code"]); entity.Title = StringUtils.GetDbString(reader["Title"]); entity.SourceCode = StringUtils.GetDbLong(reader["SourceCode"]); entity.CreateTime = StringUtils.GetDbDateTime(reader["CreateTime"]); entity.TransFee = StringUtils.GetDbDecimal(reader["TransFee"]); entity.Status = StringUtils.GetDbInt(reader["Status"]); entity.SendTime = StringUtils.GetDbDateTime(reader["SendTime"]); entity.Remark = StringUtils.GetDbString(reader["Remark"]); entity.ReceiptName = StringUtils.GetDbString(reader["ReceiptName"]); entity.ReceiptProvince = StringUtils.GetDbInt(reader["ReceiptProvince"]); entity.ReceiptCity = StringUtils.GetDbInt(reader["ReceiptCity"]); entity.ReceiptTown = StringUtils.GetDbInt(reader["ReceiptTown"]); entity.ReceiptAddress = StringUtils.GetDbString(reader["ReceiptAddress"]); entity.ReceiptPhone = StringUtils.GetDbString(reader["ReceiptPhone"]); } } return(entity); }
public IList <VWCGOrderEntity> GetVWCGOrderBySourceCode(long sourceCode) { string sql = @"SELECT a.[Id],a.[Code],a.ExpressCom,a.[Title],a.[SourceCode],a.[CreateTime], [TransFee],a.[Status], a.[SendTime],a.[Remark], b.[ReceiptName] ,b.[ReceiptProvince] ,b.[ReceiptCity] ,b.[ReceiptTown] ,b.[ReceiptAddress] ,b.[ReceiptPhone] FROM dbo.[CGOrder] a WITH(NOLOCK) inner join dbo.[CGOrderAddress] b WITH(NOLOCK) on a.Code=b.CGOrderCode WHERE [SourceCode]=@SourceCode" ; DbCommand cmd = db.GetSqlStringCommand(sql); db.AddInParameter(cmd, "@SourceCode", DbType.Int64, sourceCode); IList <VWCGOrderEntity> entityList = new List <VWCGOrderEntity>(); using (IDataReader reader = db.ExecuteReader(cmd)) { while (reader.Read()) { VWCGOrderEntity entity = new VWCGOrderEntity(); entity.Id = StringUtils.GetDbInt(reader["Id"]); entity.Code = StringUtils.GetDbLong(reader["Code"]); entity.Title = StringUtils.GetDbString(reader["Title"]); entity.SourceCode = StringUtils.GetDbLong(reader["SourceCode"]); entity.CreateTime = StringUtils.GetDbDateTime(reader["CreateTime"]); entity.TransFee = StringUtils.GetDbDecimal(reader["TransFee"]); entity.Status = StringUtils.GetDbInt(reader["Status"]); entity.SendTime = StringUtils.GetDbDateTime(reader["SendTime"]); entity.Remark = StringUtils.GetDbString(reader["Remark"]); entity.ReceiptName = StringUtils.GetDbString(reader["ReceiptName"]); entity.ReceiptProvince = StringUtils.GetDbInt(reader["ReceiptProvince"]); entity.ReceiptCity = StringUtils.GetDbInt(reader["ReceiptCity"]); entity.ReceiptTown = StringUtils.GetDbInt(reader["ReceiptTown"]); entity.ReceiptAddress = StringUtils.GetDbString(reader["ReceiptAddress"]); entity.ReceiptPhone = StringUtils.GetDbString(reader["ReceiptPhone"]); entityList.Add(entity); } } return(entityList); }
/// <summary> /// 读取记录列表。 /// </summary> /// <param name="db">数据库操作对象</param> /// <param name="columns">需要返回的列,不提供任何列名时默认将返回所有列</param> public IList <VWCGOrderEntity> GetCGOrderTakeList(int pagesize, int pageindex, ref int recordCount, string keyword, int memid) { string where = " where 1=1"; if (!string.IsNullOrEmpty(keyword)) { where += " And a.Title like @keyword "; } if (memid != -1) { where += " And b.CGMemId=@CGMemId "; } string sql = @"SELECT [Id],[Code],[Title],[SourceCode],[CreateTime],[ReceiptName],[ReceiptProvince],[ReceiptCity],[ReceiptTown],[ReceiptAddress],[ReceiptPhone], [Status], [SendTime],[Remark] FROM (SELECT ROW_NUMBER() OVER (ORDER BY a.Id desc) AS ROWNUMBER, a.[Id],a.[Code],a.[Title],a.[SourceCode],a.[CreateTime],c.[ReceiptName],c.[ReceiptProvince],c.[ReceiptCity],c.[ReceiptTown],c.[ReceiptAddress], c.[ReceiptPhone], a.[Status],a.[SendTime],a.[Remark] from dbo.[CGOrder] a WITH(NOLOCK) inner join dbo.[CGOrderAddress] c WITH(NOLOCK) on a.Code=c.CGOrderCode inner join dbo.[CGOrderTake] b WITH(NOLOCK) on a.Code=b.CGOrderCode " + where + @") as temp where rownumber BETWEEN ((@PageIndex - 1) * @PageSize + 1) AND @PageIndex * @PageSize" ; string sql2 = @"Select count(1) from dbo.[CGOrder] a WITH(NOLOCK) inner join dbo.[CGOrderTake] b WITH(NOLOCK) on a.Code=b.CGOrderCode " + where; IList <VWCGOrderEntity> entityList = new List <VWCGOrderEntity>(); DbCommand cmd = db.GetSqlStringCommand(sql); db.AddInParameter(cmd, "@PageIndex", DbType.Int32, pageindex); db.AddInParameter(cmd, "@PageSize", DbType.Int32, pagesize); if (!string.IsNullOrEmpty(keyword)) { db.AddInParameter(cmd, "@keyword", DbType.String, "%" + keyword + "%"); } if (memid != -1) { db.AddInParameter(cmd, "@CGMemId", DbType.Int32, memid); } using (IDataReader reader = db.ExecuteReader(cmd)) { while (reader.Read()) { VWCGOrderEntity entity = new VWCGOrderEntity(); entity.Id = StringUtils.GetDbInt(reader["Id"]); entity.Code = StringUtils.GetDbLong(reader["Code"]); entity.Title = StringUtils.GetDbString(reader["Title"]); entity.SourceCode = StringUtils.GetDbLong(reader["SourceCode"]); entity.CreateTime = StringUtils.GetDbDateTime(reader["CreateTime"]); entity.ReceiptName = StringUtils.GetDbString(reader["ReceiptName"]); entity.ReceiptProvince = StringUtils.GetDbInt(reader["ReceiptProvince"]); entity.ReceiptCity = StringUtils.GetDbInt(reader["ReceiptCity"]); entity.ReceiptTown = StringUtils.GetDbInt(reader["ReceiptTown"]); entity.ReceiptAddress = StringUtils.GetDbString(reader["ReceiptAddress"]); entity.ReceiptPhone = StringUtils.GetDbString(reader["ReceiptPhone"]); entity.Status = StringUtils.GetDbInt(reader["Status"]); entity.SendTime = StringUtils.GetDbDateTime(reader["SendTime"]); entity.Remark = StringUtils.GetDbString(reader["Remark"]); entityList.Add(entity); } } cmd = db.GetSqlStringCommand(sql2); if (!string.IsNullOrEmpty(keyword)) { db.AddInParameter(cmd, "@keyword", DbType.String, "%" + keyword + "%"); } if (memid != -1) { db.AddInParameter(cmd, "@CGMemId", DbType.Int32, memid); } using (IDataReader reader = db.ExecuteReader(cmd)) { if (reader.Read()) { recordCount = StringUtils.GetDbInt(reader[0]); } else { recordCount = 0; } } return(entityList); }