/// <summary>
/// 分页查找全部投票
/// </summary>
/// <param name="page"></param>
/// <returns>返回当前页的数据</returns>
public List <VotingTheme> FindAll(PageObject <VotingTheme> page)
{
List <VotingTheme> votes = new List <VotingTheme>();
try
{
using (SqlConnection connection = new SqlConnection(DbConfig.ConnectionString))
{
string sql = @"SELECT id,title,type,startTime,endTime,createTime FROM votingTheme ORDER BY id DESC OFFSET @start ROWS FETCH NEXT @size ROWS ONLY;";
SqlParameter[] pms =
{
new SqlParameter("@start", (page.CurrentPage - 1) * page.PageSize),
new SqlParameter("@size", page.PageSize)
};
connection.Open();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(pms);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
VotingTheme vote = new VotingTheme();
vote = GetVote(vote, reader);
votes.Add(vote);
}
}
}
catch (Exception e)
{
_logger.LogInformation("VoteMapperImpl类FindAll()方法异常：\n" + e.Message);
}
return(votes);
}
/// <summary>
/// 添加投票信息，并将选项写入对应表中
/// </summary>
/// <param name="vote"></param>
/// <returns></returns>
public int AddVote(VotingTheme vote)
{
int id = _voteMapper.SaveVoteTheme(vote);
_optionMapper.SaveOptions(id, vote.Options);
return(id);
}
/// <summary>
/// 修改投票信息
/// </summary>
/// <param name="vote"></param>
/// <returns>返回影响行数</returns>
public int UpdateVote(VotingTheme vote)
{
int row = 0;
try
{
using (SqlConnection connection = new SqlConnection(DbConfig.ConnectionString))
{
string sql = @"UPDATE votingTheme SET title=@title,type=@type,startTime=@startTime,endTime=@endTime WHERE id=@id";
SqlParameter[] pms =
{
new SqlParameter("@title", vote.Title),
new SqlParameter("@type", vote.Type?1:0),
new SqlParameter("@startTime", vote.StartTime),
new SqlParameter("@endTime", vote.EndTime),
new SqlParameter("@id", vote.Id)
};
connection.Open();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(pms);
row = cmd.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception e)
{
_logger.LogInformation("VoteMapperImpl类UpdateVote()方法异常：\n" + e.Message);
}
return(row);
}
/// <summary>
/// 添加投票信息
/// </summary>
/// <param name="vote"></param>
/// <returns>投票编号</returns>
public int SaveVoteTheme(VotingTheme vote)
{
int id = 0;
try
{
using (SqlConnection connection = new SqlConnection(DbConfig.ConnectionString))
{
string sql = @"INSERT INTO votingTheme(title,type,startTime,endTime) values(@title,@type,@startTime,@endTime) SELECT @@IDENTITY as Id";
SqlParameter[] pms =
{
new SqlParameter("@title", vote.Title),
new SqlParameter("@type", vote.Type?1:0),
new SqlParameter("@startTime", vote.StartTime),
new SqlParameter("@endTime", vote.EndTime)
};
connection.Open();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(pms);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
id = Convert.ToInt32(reader["id"]);
}
connection.Close();
}
}
catch (Exception e)
{
_logger.LogInformation("VoteMapperImpl类SaveVoteTheme()方法异常：\n" + e.Message);
}
return(id);
}
/// <summary>
/// 通过reader封装对象
/// </summary>
/// <param name="vote"></param>
/// <param name="reader"></param>
/// <returns></returns>
private VotingTheme GetVote(VotingTheme vote, SqlDataReader reader)
{
vote.Id = Convert.ToInt32(reader["id"]);
vote.Title = reader["title"].ToString();
vote.Type = Convert.ToInt32(reader["type"]) == 1 ? true : false;
vote.StartTime = Convert.ToDateTime(reader["startTime"]).ToString();
vote.EndTime = Convert.ToDateTime(reader["endTime"]).ToString();
vote.CreateTime = Convert.ToDateTime(reader["createTime"]);
return(vote);
}
public VotingTheme VoteDetail(VotingTheme vote)
{
int len = vote.Options.Count();
vote.Count = 0;
for (int i = 0; i < len; i++)
{
vote.Options[i].Num = _pollMapper.FindNumByVidAndOid(vote.Id, vote.Options[i].Id);
vote.Count += vote.Options[i].Num;
}
return(vote);
}
/// <summary>
/// 修改投票信息，1.修改投票信息，删除所有选项，重新添加选项
/// </summary>
/// <param name="vote"></param>
/// <returns></returns>
public int UpdateVote(VotingTheme vote)
{
int row = _voteMapper.UpdateVote(vote);
if (row == 0)
{
return(0);
}
row = _optionMapper.DeleteOptionsByVid(vote.Id);
if (row == 0)
{
return(0);
}
row = _optionMapper.SaveOptions(vote.Id, vote.Options);
return(row);
}