public int Update(ResidentialBuildingInfo model)
{
if (IsExist(model.BuildingCode, model.PropertyCompanyId, model.ResidenceCommunityId, model.Id))
{
return(110);
}
string cmdText = @"update ResidentialBuilding set BuildingCode = @BuildingCode,CoveredArea = @CoveredArea,PropertyCompanyId = @PropertyCompanyId,ResidenceCommunityId = @ResidenceCommunityId,Remark = @Remark,LastUpdatedDate = @LastUpdatedDate
where Id = @Id" ;
SqlParameter[] parms =
{
new SqlParameter("@Id", SqlDbType.UniqueIdentifier),
new SqlParameter("@BuildingCode", SqlDbType.VarChar, 20),
new SqlParameter("@CoveredArea", SqlDbType.Float),
new SqlParameter("@PropertyCompanyId", SqlDbType.UniqueIdentifier),
new SqlParameter("@ResidenceCommunityId", SqlDbType.UniqueIdentifier),
new SqlParameter("@Remark", SqlDbType.NVarChar, 50),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime)
};
parms[0].Value = model.Id;
parms[1].Value = model.BuildingCode;
parms[2].Value = model.CoveredArea;
parms[3].Value = model.PropertyCompanyId;
parms[4].Value = model.ResidenceCommunityId;
parms[5].Value = model.Remark;
parms[6].Value = model.LastUpdatedDate;
return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms));
}
public List <ResidentialBuildingInfo> GetList()
{
string cmdText = @"select Id,BuildingCode,CoveredArea,PropertyCompanyId,ResidenceCommunityId,Remark,LastUpdatedDate
from ResidentialBuilding
order by LastUpdatedDate desc " ;
List <ResidentialBuildingInfo> list = new List <ResidentialBuildingInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
ResidentialBuildingInfo model = new ResidentialBuildingInfo();
model.Id = reader.GetGuid(0);
model.BuildingCode = reader.GetString(1);
model.CoveredArea = reader.GetDouble(2);
model.PropertyCompanyId = reader.GetGuid(3);
model.ResidenceCommunityId = reader.GetGuid(4);
model.Remark = reader.GetString(5);
model.LastUpdatedDate = reader.GetDateTime(6);
list.Add(model);
}
}
}
return(list);
}
public List <ResidentialBuildingInfo> GetList(string sqlWhere, params SqlParameter[] cmdParms)
{
string cmdText = @"select Id,BuildingCode,CoveredArea,PropertyCompanyId,ResidenceCommunityId,Remark,LastUpdatedDate
from ResidentialBuilding";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += " where 1=1 " + sqlWhere;
}
List <ResidentialBuildingInfo> list = new List <ResidentialBuildingInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
ResidentialBuildingInfo model = new ResidentialBuildingInfo();
model.Id = reader.GetGuid(0);
model.BuildingCode = reader.GetString(1);
model.CoveredArea = reader.GetDouble(2);
model.PropertyCompanyId = reader.GetGuid(3);
model.ResidenceCommunityId = reader.GetGuid(4);
model.Remark = reader.GetString(5);
model.LastUpdatedDate = reader.GetDateTime(6);
list.Add(model);
}
}
}
return(list);
}
public ResidentialBuildingInfo GetModel(object Id)
{
ResidentialBuildingInfo model = null;
string cmdText = @"select top 1 Id,BuildingCode,CoveredArea,PropertyCompanyId,ResidenceCommunityId,Remark,LastUpdatedDate
from ResidentialBuilding
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 ResidentialBuildingInfo();
model.Id = reader.GetGuid(0);
model.BuildingCode = reader.GetString(1);
model.CoveredArea = reader.GetDouble(2);
model.PropertyCompanyId = reader.GetGuid(3);
model.ResidenceCommunityId = reader.GetGuid(4);
model.Remark = reader.GetString(5);
model.LastUpdatedDate = reader.GetDateTime(6);
}
}
}
return(model);
}
public List <ResidentialBuildingInfo> GetListByJoin(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms)
{
string cmdText = @"select count(*) from ResidentialBuilding ";
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 rb.LastUpdatedDate desc) as RowNumber,
rb.Id,rb.BuildingCode,rb.CoveredArea,rb.PropertyCompanyId,rb.ResidenceCommunityId,rb.Remark,rb.LastUpdatedDate,
pc.CompanyName,rc.CommunityName
from ResidentialBuilding rb
left join PropertyCompany pc on rb.PropertyCompanyId = pc.Id
left join ResidenceCommunity rc on rb.ResidenceCommunityId = rc.Id
";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " ";
List <ResidentialBuildingInfo> list = new List <ResidentialBuildingInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
ResidentialBuildingInfo model = new ResidentialBuildingInfo();
model.Id = reader.GetGuid(1);
model.BuildingCode = reader.GetString(2);
model.CoveredArea = reader.GetDouble(3);
model.PropertyCompanyId = reader.GetGuid(4);
model.ResidenceCommunityId = reader.GetGuid(5);
model.Remark = reader.GetString(6);
model.LastUpdatedDate = reader.GetDateTime(7);
model.CompanyName = reader.IsDBNull(8) ? "" : reader.GetString(8);
model.CommunityName = reader.IsDBNull(9) ? "" : reader.GetString(9);
list.Add(model);
}
}
}
return(list);
}
private void Bind(ref string myDataAppend)
{
if (!Id.Equals(Guid.Empty))
{
Page.Title = "编辑楼信息";
ResidentialBuilding bll = new ResidentialBuilding();
ResidentialBuildingInfo model = bll.GetModelByJoin(Id);
if (model != null)
{
txtName.Value = model.BuildingCode;
txtCoveredArea.Value = model.CoveredArea.ToString();
txtaRemark.Value = model.Remark;
myDataAppend += "{ \"Id\":\"" + model.Id + "\",\"PropertyCompanyId\":\"" + model.PropertyCompanyId + "\",\"ResidenceCommunityId\":\"" + model.ResidenceCommunityId + "\",\"CompanyName\":\"" + model.CompanyName + "\",\"CommunityName\":\"" + model.CommunityName + "\"}";
}
}
}
public List <ResidentialBuildingInfo> 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,BuildingCode,CoveredArea,PropertyCompanyId,ResidenceCommunityId,Remark,LastUpdatedDate
from ResidentialBuilding" ;
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += " where 1=1 " + sqlWhere;
}
cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " ";
List <ResidentialBuildingInfo> list = new List <ResidentialBuildingInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
ResidentialBuildingInfo model = new ResidentialBuildingInfo();
model.Id = reader.GetGuid(1);
model.BuildingCode = reader.GetString(2);
model.CoveredArea = reader.GetDouble(3);
model.PropertyCompanyId = reader.GetGuid(4);
model.ResidenceCommunityId = reader.GetGuid(5);
model.Remark = reader.GetString(6);
model.LastUpdatedDate = reader.GetDateTime(7);
list.Add(model);
}
}
}
return(list);
}
public ResidentialBuildingInfo GetModelByJoin(object Id)
{
ResidentialBuildingInfo model = null;
string cmdText = @"select top 1 rb.Id,rb.BuildingCode,rb.CoveredArea,rb.PropertyCompanyId,rb.ResidenceCommunityId,rb.Remark,rb.LastUpdatedDate,
pc.CompanyName,rc.CommunityName
from ResidentialBuilding rb
left join PropertyCompany pc on rb.PropertyCompanyId = pc.Id
left join ResidenceCommunity rc on rb.ResidenceCommunityId = rc.Id
where rb.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 ResidentialBuildingInfo();
model.Id = reader.GetGuid(0);
model.BuildingCode = reader.GetString(1);
model.CoveredArea = reader.GetDouble(2);
model.PropertyCompanyId = reader.GetGuid(3);
model.ResidenceCommunityId = reader.GetGuid(4);
model.Remark = reader.GetString(5);
model.LastUpdatedDate = reader.GetDateTime(6);
model.CompanyName = reader.IsDBNull(7) ? "" : reader.GetString(7);
model.CommunityName = reader.IsDBNull(8) ? "" : reader.GetString(8);
}
}
}
return(model);
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(ResidentialBuildingInfo model)
{
return(dal.Update(model));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(ResidentialBuildingInfo model)
{
return(dal.Insert(model));
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int UpdateResidentialBuilding(ResidentialBuildingInfo model)
{
ResidentialBuilding bll = new ResidentialBuilding();
return(bll.Update(model));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int InsertResidentialBuilding(ResidentialBuildingInfo model)
{
ResidentialBuilding bll = new ResidentialBuilding();
return(bll.Insert(model));
}
