/// <summary>
/// 获取数据列表
/// </summary>
/// <returns></returns>
public List <ReportContentInfo> GetList()
{
string cmdText = @"select * from [ReportContent] ";
List <ReportContentInfo> list = new List <ReportContentInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
ReportContentInfo model = new ReportContentInfo();
model.Id = reader["Id"];
model.UserId = reader["UserId"];
model.FromUrl = reader["FromUrl"].ToString();
model.FromType = reader["FromType"].ToString();
model.GiveName = reader["GiveName"].ToString();
model.FromDate = DateTime.Parse(reader["FromDate"].ToString());
list.Add(model);
}
}
}
return(list);
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(ReportContentInfo model)
{
if (model == null)
{
return(-1);
}
//定义查询命令
string cmdText = @"update [ReportContent] set UserId = @UserId,FromUrl = @FromUrl,FromType = @FromType,GiveName = @GiveName,FromDate = @FromDate where Id = @Id";
//创建查询命令参数集
SqlParameter[] parms =
{
new SqlParameter("@Id", SqlDbType.UniqueIdentifier),
new SqlParameter("@UserId", SqlDbType.UniqueIdentifier),
new SqlParameter("@FromUrl", SqlDbType.NVarChar, 300),
new SqlParameter("@FromType", SqlDbType.NVarChar, 36),
new SqlParameter("@GiveName", SqlDbType.NVarChar, 256),
new SqlParameter("@FromDate", SqlDbType.DateTime)
};
parms[0].Value = Guid.Parse(model.Id.ToString());
parms[1].Value = Guid.Parse(model.UserId.ToString());
parms[2].Value = model.FromUrl;
parms[3].Value = model.FromType;
parms[4].Value = model.GiveName;
parms[5].Value = model.FromDate;
return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(ReportContentInfo model)
{
if (model == null)
{
return(-1);
}
string cmdText = "insert into [ReportContent] (UserId,FromUrl,FromType,GiveName,FromDate) values (@UserId,@FromUrl,@FromType,@GiveName,@FromDate)";
//创建查询命令参数集
SqlParameter[] parms =
{
new SqlParameter("@UserId", SqlDbType.UniqueIdentifier),
new SqlParameter("@FromUrl", SqlDbType.NVarChar, 300),
new SqlParameter("@FromType", SqlDbType.NVarChar, 36),
new SqlParameter("@GiveName", SqlDbType.NVarChar, 256),
new SqlParameter("@FromDate", SqlDbType.DateTime)
};
parms[0].Value = model.UserId;
parms[1].Value = model.FromUrl;
parms[2].Value = model.FromType;
parms[3].Value = model.GiveName;
parms[4].Value = model.FromDate;
//执行数据库操作
return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms));
}
/// <summary>
/// 获取对应的数据
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public ReportContentInfo GetModel(object Id)
{
ReportContentInfo model = null;
string cmdText = @"select top 1 Id,UserId,FromUrl,FromType,GiveName,FromDate from [ReportContent] 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 ReportContentInfo();
model.Id = reader["Id"];
model.UserId = reader["UserId"];
model.FromUrl = reader["FromUrl"].ToString();
model.FromType = reader["FromType"].ToString();
model.GiveName = reader["GiveName"].ToString();
model.FromDate = DateTime.Parse(reader["FromDate"].ToString());
}
}
}
return(model);
}
/// <summary>
/// 获取数据分页列表，并返回所有记录数
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <param name="sqlWhere"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public List <ReportContentInfo> GetList(int pageIndex, int pageSize, out int totalCount, string sqlWhere, params SqlParameter[] cmdParms)
{
//获取数据集总数
string cmdText = "select count(*) from [ReportContent] t1 ";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
totalCount = (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 t1.Sort) as RowNumber,t1.Id,t1.UserId,t1.FromUrl,t1.FromType,t1.GiveName,t1.FromDate,u.UserName from [ReportContent] t1
left join Aspnet_Users u on u.UserId = t1.UserId ";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " ";
List <ReportContentInfo> list = new List <ReportContentInfo>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, cmdParms))
{
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
ReportContentInfo model = new ReportContentInfo();
model.Id = reader["Id"];
model.UserId = reader["UserId"];
model.FromUrl = reader["FromUrl"].ToString();
model.FromType = reader["FromType"].ToString();
model.GiveName = reader["GiveName"].ToString();
model.FromDate = DateTime.Parse(reader["FromDate"].ToString());
list.Add(model);
}
}
}
return(list);
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(ReportContentInfo model)
{
return(dal.Update(model));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(ReportContentInfo model)
{
return(dal.Insert(model));
}
