public List <HouseOwnerNoticeInfo> GetList()
{
string cmdText = @"select Id,HouseOwnerId,NoticeId,Status,IsRead,LastUpdatedDate
from HouseOwnerNotice
order by LastUpdatedDate desc " ;
List <HouseOwnerNoticeInfo> list = new List <HouseOwnerNoticeInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
HouseOwnerNoticeInfo model = new HouseOwnerNoticeInfo();
model.Id = reader.GetGuid(0);
model.HouseOwnerId = reader.GetGuid(1);
model.NoticeId = reader.GetGuid(2);
model.Status = reader.GetByte(3);
model.IsRead = reader.GetBoolean(4);
model.LastUpdatedDate = reader.GetDateTime(5);
list.Add(model);
}
}
}
return(list);
}
public List <HouseOwnerNoticeInfo> GetList(string sqlWhere, params SqlParameter[] cmdParms)
{
string cmdText = @"select Id,HouseOwnerId,NoticeId,Status,IsRead,LastUpdatedDate
from HouseOwnerNotice";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += " where 1=1 " + sqlWhere;
}
List <HouseOwnerNoticeInfo> list = new List <HouseOwnerNoticeInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
HouseOwnerNoticeInfo model = new HouseOwnerNoticeInfo();
model.Id = reader.GetGuid(0);
model.HouseOwnerId = reader.GetGuid(1);
model.NoticeId = reader.GetGuid(2);
model.Status = reader.GetByte(3);
model.IsRead = reader.GetBoolean(4);
model.LastUpdatedDate = reader.GetDateTime(5);
list.Add(model);
}
}
}
return(list);
}
public HouseOwnerNoticeInfo GetModel(object Id)
{
HouseOwnerNoticeInfo model = null;
string cmdText = @"select top 1 Id,HouseOwnerId,NoticeId,Status,IsRead,LastUpdatedDate
from HouseOwnerNotice
where Id = @Id " ;
SqlParameter parm = new SqlParameter("@Id", SqlDbType.UniqueIdentifier);
parm.Value = Guid.Parse(Id.ToString());
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parm))
{
if (reader != null)
{
while (reader.Read())
{
model = new HouseOwnerNoticeInfo();
model.Id = reader.GetGuid(0);
model.HouseOwnerId = reader.GetGuid(1);
model.NoticeId = reader.GetGuid(2);
model.Status = reader.GetByte(3);
model.IsRead = reader.GetBoolean(4);
model.LastUpdatedDate = reader.GetDateTime(5);
}
}
}
return(model);
}
public List <HouseOwnerNoticeInfo> GetListByJoin(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
string cmdText = @"select count(*) from HouseOwnerNotice hon
left join HouseOwner ho on ho.Id = hon.HouseOwnerId
left join Notice n on n.Id = hon.NoticeId
";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += " where 1=1 " + sqlWhere;
}
totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms);
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
cmdText = @"select * from(select row_number() over(order by hon.LastUpdatedDate desc) as RowNumber,
hon.Id,hon.HouseOwnerId,hon.NoticeId,hon.Status,hon.IsRead,hon.LastUpdatedDate,
ho.HouseOwnerName,ho.MobilePhone,ho.TelPhone,n.Title
from HouseOwnerNotice hon
left join HouseOwner ho on ho.Id = hon.HouseOwnerId
left join Notice n on n.Id = hon.NoticeId
";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " ";
List <HouseOwnerNoticeInfo> list = new List <HouseOwnerNoticeInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
HouseOwnerNoticeInfo model = new HouseOwnerNoticeInfo();
model.Id = reader.GetGuid(1);
model.HouseOwnerId = reader.GetGuid(2);
model.NoticeId = reader.GetGuid(3);
model.Status = reader.GetByte(4);
model.IsRead = reader.GetBoolean(5);
model.LastUpdatedDate = reader.GetDateTime(6);
model.HouseOwnerName = reader.IsDBNull(7) ? "" : reader.GetString(7);
model.MobilePhone = reader.IsDBNull(8) ? "" : reader.GetString(8);
model.TelPhone = reader.IsDBNull(9) ? "" : reader.GetString(9);
model.NoticeTitle = reader.IsDBNull(10) ? "" : reader.GetString(10);
list.Add(model);
}
}
}
return(list);
}
public List <HouseOwnerNoticeInfo> GetList(int pageIndex, int pageSize, string sqlWhere, params SqlParameter[] cmdParms)
{
int startIndex = (pageIndex - 1) * pageSize + 1;
int endIndex = pageIndex * pageSize;
string cmdText = @"select * from(select row_number() over(order by LastUpdatedDate desc) as RowNumber,
Id,HouseOwnerId,NoticeId,Status,IsRead,LastUpdatedDate
from HouseOwnerNotice" ;
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += " where 1=1 " + sqlWhere;
}
cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " ";
List <HouseOwnerNoticeInfo> list = new List <HouseOwnerNoticeInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
HouseOwnerNoticeInfo model = new HouseOwnerNoticeInfo();
model.Id = reader.GetGuid(1);
model.HouseOwnerId = reader.GetGuid(2);
model.NoticeId = reader.GetGuid(3);
model.Status = reader.GetByte(4);
model.IsRead = reader.GetBoolean(5);
model.LastUpdatedDate = reader.GetDateTime(6);
list.Add(model);
}
}
}
return(list);
}
public int Update(HouseOwnerNoticeInfo model)
{
string cmdText = @"update HouseOwnerNotice set HouseOwnerId = @HouseOwnerId,NoticeId = @NoticeId,Status = @Status,IsRead = @IsRead,LastUpdatedDate = @LastUpdatedDate
where Id = @Id" ;
SqlParameter[] parms =
{
new SqlParameter("@Id", SqlDbType.UniqueIdentifier),
new SqlParameter("@HouseOwnerId", SqlDbType.UniqueIdentifier),
new SqlParameter("@NoticeId", SqlDbType.UniqueIdentifier),
new SqlParameter("@Status", SqlDbType.TinyInt),
new SqlParameter("@IsRead", SqlDbType.Bit),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime)
};
parms[0].Value = model.Id;
parms[1].Value = model.HouseOwnerId;
parms[2].Value = model.NoticeId;
parms[3].Value = model.Status;
parms[4].Value = model.IsRead;
parms[5].Value = model.LastUpdatedDate;
return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms));
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(HouseOwnerNoticeInfo model)
{
return(dal.Update(model));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(HouseOwnerNoticeInfo model)
{
return(dal.Insert(model));
}