public static int AddSingerInfoDataAccess(SingerInfo singerInfo)
{
var sql = $"insert into {TABLENAME}({FIELDNAME})" +
$" values('{singerInfo.SingerName}','{singerInfo.SingerEnglishName}','{singerInfo.SingerOtherName}','{singerInfo.SingerInitials}','{singerInfo.SingerNationality}','{singerInfo.SingerPhotoUrl}','{singerInfo.SingerClickNum}','{singerInfo.SingerSex}','{singerInfo.SingerIntroduce}')";
return(SqlServerHelper.ExecuteNonQuery(CommandType.Text, sql, 30, null));
}
/// <summary>
/// 获取歌手列表
/// </summary>
/// <param name="singerType"></param>
/// <returns></returns>
public List <SingerInfo> GetSingerList(string singerType)
{
string getSingerSql = "";
if (singerType != "")
{
string getTypeSql = string.Format("select SRT_SingerTypeId from SingerType where SRT_SingerTypeName = '{0}'", singerType);
int singerTypeId = Convert.ToInt32(db.GetOneResult(getTypeSql));
getSingerSql = string.Format("select * from SingerInfo as i,SingerType as t where i.SRI_SingeTypeId = t.SRT_SingerTypeId and SRI_SingeTypeId = {0}", singerTypeId);
}
else
{
getSingerSql = "select * from SingerInfo as i,SingerType as t where i.SRI_SingeTypeId = t.SRT_SingerTypeId";
}
SqlDataReader reader = db.GetManyResult(getSingerSql);
List <SingerInfo> singerList = new List <SingerInfo>();
while (reader.Read())
{
SingerInfo singer = new SingerInfo();
singer.SRI_SingeId1 = Convert.ToInt32(reader["SRI_SingeId"]);
singer.SRI_SingePhotoURL1 = reader["SRI_SingePhotoURL"].ToString();
singer.SRI_SingerName1 = reader["SRI_SingerName"].ToString();
singer.SRI_SingeType1 = reader["SRT_SingerTypeName"].ToString();
singerList.Add(singer);
}
return(singerList);
}
static Image GetWebMusicPictureByKugou(this string MusicName)
{
try
{
string url = string.Format(@"http://mobilecdn.kugou.com/new/app/i/yueku.php?cmd=104&size=240&singer={0}&type=softhead", MusicName);
SingerInfo singer = HttpLib.Http.Get(url).redirect(true).request().ToJson <SingerInfo>();
if (singer != null && !string.IsNullOrEmpty(singer.url))
{
return(HttpLib.Http.Get(singer.url).redirect(true).requestData().ToImage());
}
}
catch { }
return(null);
}
public SingerInfo GetSingerInfoBySingerXmId(string singerXmId)
{
SingerInfo singerInfo = new SingerInfo();
singerInfo.SingerXmId = singerXmId;
string strUrl = "http://www.xiami.com/artist/" + singerXmId;
string strHtmlContent = HttpHelper.Open(strUrl, Encoding.GetEncoding("UTF-8"));
strHtmlContent = strHtmlContent.Replace("\r\n", "").Replace("\r", "").Replace("\n", "");
singerInfo.SingerAddress = CommonHelper.NoHTML(CommonHelper.GetMatchRegex("<tr>.+?地区：</td>(.+?)</tr>", strHtmlContent));
singerInfo.SingerAddress = Regex.Replace(singerInfo.SingerAddress, "\\t", "");
singerInfo.SingerDesc = CommonHelper.NoHTML(CommonHelper.GetMatchRegex("档案：</td>(.+?)</div>", strHtmlContent));
singerInfo.SingerDesc = Regex.Replace(singerInfo.SingerDesc, "\\t", "");
return(singerInfo);
}
public static SingerInfo GetSingerInfoByIdDataAccess(string id)
{
var sql = $"select id, {FIELDNAME} from {TABLENAME} where id = '{id}'";
DataTable result = SqlServerHelper.GetDataFromKtvdb(sql);
DataRow row = result.Rows[0];
SingerInfo resultReturn = new SingerInfo();
resultReturn.Id = int.Parse(row["id"].ToString());
resultReturn.SingerName = row["singername"].ToString();
resultReturn.SingerEnglishName = row["singerenglishname"].ToString();
resultReturn.SingerOtherName = row["singerothername"].ToString();
resultReturn.SingerInitials = row["singerinitials"].ToString();
resultReturn.SingerNationality = row["singernationality"].ToString();
resultReturn.SingerPhotoUrl = row["singerphotourl"].ToString();
resultReturn.SingerClickNum = row["singerclicknum"].ToString();
resultReturn.SingerSex = row["singersex"].ToString();
resultReturn.SingerIntroduce = row["singerintroduce"].ToString();
return(resultReturn);
}
/// <summary>
/// 得到歌手Id
/// </summary>
/// <param name="singerInfo"></param>
/// <returns></returns>
public int GetSingerId(SingerInfo singerInfo)
{
try
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("singerName", singerInfo.SingerName),
new SqlParameter("singerdesc", singerInfo.SingerDesc),
new SqlParameter("singeraddress", singerInfo.SingerAddress),
new SqlParameter("singerxmid", singerInfo.SingerXmId)
};
object obj = HelperSQL.ExecScalar("usp_XM_Insert_XM_Singer", parms, CommandType.StoredProcedure);
return(Convert.ToInt32(obj));
}
catch (Exception ex)
{
LogNet.LogBLL.Error("GetSingerId", ex);
return(0);
}
}
public static int UpdateSingerInfoDataAccess(SingerInfo singerInfo)
{
var sql = $"update {TABLENAME} set singername = '{singerInfo.SingerName}', singerenglishname = '{singerInfo.SingerEnglishName}',singerothername = '{singerInfo.SingerOtherName}',singerinitials = '{singerInfo.SingerInitials}',singernationality = '{singerInfo.SingerNationality}',singerphotourl = '{singerInfo.SingerPhotoUrl}',singerclicknum = '{singerInfo.SingerClickNum}',singersex = '{singerInfo.SingerSex}',singerintroduce = '{singerInfo.SingerIntroduce}' where id = {singerInfo.Id}";
return(SqlServerHelper.ExecuteNonQuery(CommandType.Text, sql, 30, null));
}
public int UpdateSingerInfo(SingerInfo singerInfo)
{
return(SingerInfoManagementDataAccess.UpdateSingerInfoDataAccess(singerInfo));
}
public int AddSingerInfo(SingerInfo singerInfo)
{
return(SingerInfoManagementDataAccess.AddSingerInfoDataAccess(singerInfo));
}
/// <summary>
/// 得到专辑歌曲 的基本信息,包括 艺人,语种,唱片公司,发行时间
/// </summary>
/// <param name="strHtmlContent"></param>
/// <returns></returns>
public AlbumSongInfo GetAlbumSongInfo(string strHtmlContent)
{
AlbumSongInfo albumSongInfo = new AlbumSongInfo();
try
{
AlbumInfo albumInfo = new AlbumInfo();
SingerInfo singerInfo = new SingerInfo();
string pattern = "";
pattern = "<h1 property=\"v:item(.+?)</h1>";
string matchResult = CommonHelper.GetMatchRegex(pattern, strHtmlContent);
albumInfo.AlbumName = CommonHelper.NoHTML(matchResult);
albumInfo.AlbumSinger = CommonHelper.GetMatchRegex("艺人：</td>(.+?)</tr>", strHtmlContent, "<a(.+?)</a>");
albumInfo.AlbumSinger = CommonHelper.NoHTML(albumInfo.AlbumSinger);//href="/artist/111382">洪佳琦
/*----------虾米 艺人介绍----------------------------------------------------------------------*/
singerInfo.SingerXmId = CommonHelper.GetMatchRegex("艺人：</td>(.+?)</tr>", strHtmlContent, "<a href=\"/artist/(\\d+)");
singerInfo.SingerXmId = singerInfo.SingerXmId.Replace("<a href=\"/artist/", "");
// 进入歌手 信息 得到 歌手的 档案,和地区
singerInfo = GetSingerInfoBySingerXmId(singerInfo.SingerXmId);
albumInfo.AlbumDesc = CommonHelper.GetMatchRegex("专辑介绍(.+?)album_intro_toggle\">", strHtmlContent);
albumInfo.AlbumDesc = CommonHelper.NoHTML(albumInfo.AlbumDesc).Replace("专辑介绍:", "").Replace("\"", "");
/*----------专辑类别,专辑风格----------------------------------------------------------------------*/
albumInfo.AlbumStyle = CommonHelper.NoHTML(CommonHelper.GetMatchRegex("专辑风格：</td>(.+?)</tr>", strHtmlContent));
albumInfo.AlbumStyle = Regex.Replace(albumInfo.AlbumStyle, "\\t", "").Replace("专辑风格：", "");;
albumInfo.AlbumType = CommonHelper.NoHTML(CommonHelper.GetMatchRegex("专辑类别：</td>(.+?)</tr>", strHtmlContent));
albumInfo.AlbumType = Regex.Replace(albumInfo.AlbumType, "\\t", "").Replace("专辑类别：", "");
albumSongInfo.AlbumInfo = albumInfo;
//得到歌手
singerInfo.SingerName = albumInfo.AlbumSinger;
albumSongInfo.SingerInfo = singerInfo;
albumSongInfo.SongLanguage = CommonHelper.GetMatchRegex(">语种：</td>(.+?)</td>", strHtmlContent, "<td(.+?)</td>");
albumSongInfo.SongLanguage = CommonHelper.NoHTML(albumSongInfo.SongLanguage);
albumSongInfo.OriRightsHolder = CommonHelper.GetMatchRegex(">唱片公司：</td>(.+?)</td>", strHtmlContent, "<td(.+?)</td>");
albumSongInfo.OriRightsHolder = CommonHelper.NoHTML(albumSongInfo.OriRightsHolder);
string pubsishDate = CommonHelper.GetMatchRegex(">发行时间：</td>(.+?)</td>", strHtmlContent, "<td(.+?)</td>");
pubsishDate = CommonHelper.NoHTML(pubsishDate);
DateTime dtime = new DateTime(2010, 1, 1);
DateTime.TryParse(pubsishDate, out dtime);
albumSongInfo.PublishDate = dtime;
return(albumSongInfo);
}
catch (Exception ex)
{
LogNet.LogBLL.Info("GetAlbumSongInfo", ex);
return(null);
}
}
