/// <summary>
/// 插入角色信息
/// </summary>
/// <param name="cInfo"></param>
/// <returns></returns>
public bool Insert(Character cInfo)
{
string sqlcmd = @"INSERT INTO {0} (
CHARACTER_NO
,CHARACTER_NAME
,ANIME_NO
,CV_ID
,LEADING_FLG
,ENABLE_FLG
,LAST_UPDATE_DATETIME
)
VALUES (
@characterNo
,@charactername
,@animeNo
,@CVID
,@leadingFlg
,1
,GETDATE()
)";
Collection<DbParameter> paras = new Collection<DbParameter>();
paras.Add(new SqlParameter("@characterNo", cInfo.No));
paras.Add(new SqlParameter("@charactername", cInfo.name));
paras.Add(new SqlParameter("@animeNo", cInfo.animeNo));
paras.Add(new SqlParameter("@CVID", cInfo.CVID));
paras.Add(new SqlParameter("@leadingFlg", cInfo.leadingFLG));
DbCmd.DoCommand(string.Format(sqlcmd, CommonConst.TableName.T_CHARACTER_TBL), paras);
return true;
}
/// <summary>
/// 删除角色信息(伦理)
/// </summary>
/// <param name="pInfo">角色信息，仅需要ID</param>
/// <returns></returns>
public bool Delete(Character cInfo)
{
string sqlcmd = @"
UPDATE {0}
SET ENABLE_FLG = 0
,LAST_UPDATE_DATETIME = GETDATE()
WHERE CHARACTER_NO = @characterNo ";
Collection<DbParameter> paras = new Collection<DbParameter>();
paras.Add(new SqlParameter("@characterNo", cInfo.No));
try
{
DbCmd.DoCommand(string.Format(sqlcmd, CommonConst.TableName.T_CHARACTER_TBL), paras);
return true;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 根据角色信息获得最大角色编号
/// </summary>
/// <param name="chara"></param>
/// <returns></returns>
public string GetMaxCharacterIDByCharacterInfo(Character chara)
{
string sqlcmd = @"SELECT
MAX(CHARACTER_NO)
FROM {0}
WHERE ANIME_NO = @animeNo
AND LEADING_FLG = @leadingflg";
Collection<DbParameter> paras = new Collection<DbParameter>();
paras.Add(new SqlParameter("@animeNo", chara.animeNo));
paras.Add(new SqlParameter("@leadingflg", chara.leadingFLG));
DataSet ds = DbCmd.DoSelect(string.Format(sqlcmd, CommonConst.TableName.T_CHARACTER_TBL), paras);
if (ds.Tables[0].Rows.Count == 0)
{
return null;
}
else
{
string maxNo = ds.Tables[0].Rows[0][0].ToString();
return maxNo;
}
}
/// <summary>
/// 从数据库中设定角色ID
/// </summary>
/// <param name="chara"></param>
/// <returns></returns>
public string SetCharacterNoFromDB(Character chara)
{
string maxCharaNo = dao.GetMaxCharacterIDByCharacterInfo(chara);
int leadingFlg = 0;
if (chara.leadingFLG)
leadingFlg = 1;
string nextCharaNo;
//数据库内未找到，新发番
if (string.IsNullOrEmpty(maxCharaNo))
{
nextCharaNo = chara.animeNo + "_" + leadingFlg.ToString() + "_001";
}
//数据库内找到，接续
else
{
int maxCharaNumber = Convert.ToInt32(maxCharaNo.Substring(7, 3));
nextCharaNo = chara.animeNo + "_" + leadingFlg.ToString() + "_" + (maxCharaNumber + 1).ToString("D3");
}
return nextCharaNo;
}
/// <summary>
/// 更新角色信息
/// </summary>
/// <param name="cInfo">角色信息</param>
/// <returns></returns>
public bool Update(Character cInfo)
{
if (cInfo.No.Equals(string.Empty))
{
throw new Exception("角色编号未设置即进行更新。");
}
StringBuilder sqlcmd = new StringBuilder();
sqlcmd.Append(@"UPDATE {0} SET
ANIME_NO = @animeNo
,CHARACTER_NAME = @charactername
,CV_ID = @cvid
,LEADING_FLG = @leadingFlg
,LAST_UPDATE_DATETIME = GETDATE()
WHERE CHARACTER_NO = @characterNo ");
Collection<DbParameter> paras = new Collection<DbParameter>();
paras.Add(new SqlParameter("@characterNo", cInfo.No));
paras.Add(new SqlParameter("@charactername", cInfo.name));
paras.Add(new SqlParameter("@animeNo", cInfo.animeNo));
paras.Add(new SqlParameter("@CVID", cInfo.CVID));
paras.Add(new SqlParameter("@leadingFlg", cInfo.leadingFLG));
DbCmd.DoCommand(string.Format(sqlcmd.ToString(), CommonConst.TableName.T_CHARACTER_TBL), paras);
return true;
}
/// <summary>
/// 通过动画No查找角色信息
/// </summary>
/// <param name="animeNo"></param>
/// <returns></returns>
public List<Character> GetCharacterListFromAnimeNo(string animeNo)
{
List<Character> cInfoList = new List<Character>();
string sqlcmd = @"SELECT *
FROM {0}
WHERE ANIME_NO = @animeNo
AND ENABLE_FLG = 1
ORDER BY LEADING_FLG DESC";
Collection<DbParameter> paras = new Collection<DbParameter>();
paras.Add(new SqlParameter("@animeNo", animeNo));
DataSet ds = DbCmd.DoSelect(string.Format(sqlcmd, CommonConst.TableName.T_CHARACTER_TBL), paras);
if (ds.Tables[0].Rows.Count == 0)
{
return null;
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
Character cInfo = new Character();
cInfo.No = dr[0].ToString();
cInfo.name = dr[1].ToString();
cInfo.animeNo = animeNo;
cInfo.CVID = Convert.ToInt32(dr[3]);
cInfo.leadingFLG = Convert.ToBoolean(dr[4]);
cInfoList.Add(cInfo);
}
return cInfoList;
}
/// <summary>
/// 检查该ID的声优是否有使用
/// </summary>
/// <param name="CVID">声优ID</param>
/// <returns>null:未使用 重复的角色信息</returns>
public List<Character> IsUsedCheck(int CVID)
{
const string sqlcmd = @"SELECT
ANIME_NO,
CHARACTER_NAME
FROM {0}
WHERE CV_ID = @CVID";
Collection<DbParameter> paras = new Collection<DbParameter>();
paras.Add(new SqlParameter("@CVID", CVID));
DataSet ds = DbCmd.DoSelect(string.Format(sqlcmd, CommonConst.TableName.T_CHARACTER_TBL), paras);
if (ds.Tables[0].Rows.Count != 0)
{
List<Character> repeatCVList = new List<Character>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Character repeatInfo = new Character();
repeatInfo.animeNo = ds.Tables[0].Rows[i][0].ToString();
repeatInfo.name = ds.Tables[0].Rows[i][1].ToString();
repeatCVList.Add(repeatInfo);
}
return repeatCVList;
}
return null;
}
/// <summary>
/// 通过动画No获得角色信息列表
/// </summary>
/// <param name="animeNo"></param>
/// <returns></returns>
private List<Character> GetCharacterListByAnimeNo(string animeNo)
{
//获取动画信息DataTable
DataTable dt = dao.GetCharacterListByAnimeNo(animeNo);
List<Character> characherInfoList = new List<Character>();
for (int i = 1; i < dt.Rows.Count; i++)
{
Character chara = new Character();
chara.No = dt.Rows[0][0].ToString();
chara.name = dt.Rows[0][1].ToString();
chara.CVID = Convert.ToInt32(dt.Rows[0][2]);
chara.leadingFLG = Convert.ToBoolean(dt.Rows[0][3]);
characherInfoList.Add(chara);
}
return characherInfoList;
}
/// <summary>
/// 添加角色信息
/// </summary>
/// <param name="cinfo"></param>
public void AddCharacterInfo(Character cinfo)
{
this.characterList.Add(cinfo);
}
/// <summary>
/// 更新动画信息
/// </summary>
/// <param name="anime"></param>
/// <returns></returns>
public bool UpdateAnime(Animation anime)
{
#region 播放信息更新
//所有既存该动画的播放信息
string sql1 = @"SELECT PLAYINFO_ID
FROM {0}
WHERE ANIME_NO = @animeNo";
Collection<DbParameter> paras1 = new Collection<DbParameter>();
paras1.Add(new SqlParameter("@animeNo",anime.No));
DataSet ds1 = DbCmd.DoSelect(string.Format(sql1, CommonConst.TableName.T_PLAYINFO_TBL), paras1);
List<int> ToDelPlayinfoIds = new List<int>();
foreach (DataRow dr in ds1.Tables[0].Rows)
{
if (dr[0].ToString().Equals(string.Empty))
{
continue;
}
ToDelPlayinfoIds.Add(Convert.ToInt32(dr[0]));
}
//播放信息全走查确认，更新
foreach (PlayInfo pInfo in anime.playInfoList)
{
//确认播放是否存在
string sql = @"SELECT PLAYINFO_ID
FROM {0}
WHERE PLAYINFO_ID = @playinfoID
AND ANIME_NO = @animeno ";
Collection<DbParameter> paras = new Collection<DbParameter>();
paras.Add(new SqlParameter("@playinfoID", pInfo.ID));
paras.Add(new SqlParameter("@animeno", anime.No));
try
{
DataSet ds = DbCmd.DoSelect(string.Format(sql, CommonConst.TableName.T_PLAYINFO_TBL), paras);
if (ds.Tables[0].Rows.Count > 0 && Convert.ToInt32(ds.Tables[0].Rows[0][0]) == pInfo.ID)
{
pInfo.Update();
//将已更新的播放信息从待删除列表中移除
if (ToDelPlayinfoIds.Contains(pInfo.ID))
{
ToDelPlayinfoIds.Remove(pInfo.ID);
}
}
else
{
pInfo.Insert();
}
}
catch (Exception ex)
{
throw ex;
}
}
//删除更新后不存在的播放信息
foreach(int toDelPlayinfoId in ToDelPlayinfoIds)
{
PlayInfo pInfo = new PlayInfo(toDelPlayinfoId, anime.No);
pInfo.Delete();
}
#endregion
#region 角色信息更新
//所有既存该动画的角色信息
string sql2 = @"SELECT CHARACTER_NO
FROM {0}
WHERE ANIME_NO = @animeNo ";
Collection<DbParameter> paras2 = new Collection<DbParameter>();
paras2.Add(new SqlParameter("@animeNo", anime.No));
DataSet ds2 = DbCmd.DoSelect(string.Format(sql2, CommonConst.TableName.T_CHARACTER_TBL), paras2);
List<string> ToDelCharacterNos = new List<string>();
foreach (DataRow dr in ds2.Tables[0].Rows)
{
if (dr[0].ToString().Equals(string.Empty))
{
continue;
}
ToDelCharacterNos.Add(dr[0].ToString());
}
//角色信息全走查确认，更新
foreach (Character cInfo in anime.characterList)
{
string sql = @"SELECT CHARACTER_NO
FROM {0}
WHERE CHARACTER_NO = @characterNo";
Collection<DbParameter> paras = new Collection<DbParameter>();
paras.Add(new SqlParameter("@characterNo", cInfo.No));
try
{
DataSet ds = DbCmd.DoSelect(string.Format(sql, CommonConst.TableName.T_CHARACTER_TBL), paras);
if (ds.Tables[0].Rows.Count > 0 && ds.Tables[0].Rows[0][0].ToString().Equals(cInfo.No))
{
cInfo.Update();
//将已更新的角色信息从待删除列表中移除
if (ToDelCharacterNos.Contains(cInfo.No))
{
ToDelCharacterNos.Remove(cInfo.No);
}
}
else
{
cInfo.Insert();
}
}
catch (Exception ex)
{
throw ex;
}
}
//删除更新后不存在的播放信息
foreach (string toDelCharacterNo in ToDelCharacterNos)
{
Character cInfo = new Character(toDelCharacterNo);
cInfo.Delete();
}
#endregion
#region 基本信息更新
//动画信息插入
string sqlcmd = @"UPDATE {0} SET
ANIME_CHN_NAME = @animeCNName
,ANIME_JPN_NAME = @animeJPName
,ANIME_NN = @animeNickName
,STATUS = @status
,ORIGINAL = @original
,LAST_UPDATE_DATETIME = GETDATE()
WHERE ANIME_NO = @animeNo
";
Collection<DbParameter> paras0 = new Collection<DbParameter>();
paras0.Add(new SqlParameter("@animeNo", anime.No));
paras0.Add(new SqlParameter("@animeCNName", anime.CNName));
paras0.Add(new SqlParameter("@animeJPName", anime.JPName));
paras0.Add(new SqlParameter("@animeNickName", anime.Nickname));
paras0.Add(new SqlParameter("@status", anime.status));
paras0.Add(new SqlParameter("@original", anime.original));
try
{
DbCmd.DoCommand(string.Format(sqlcmd, CommonConst.TableName.T_ANIME_TBL), paras0);
return true;
}
catch (Exception ex)
{
throw ex;
}
#endregion
}