/// <summary>
/// 获取细节数据
/// </summary>
/// <param name="channelType"></param>
/// <returns></returns>
public IEnumerable<DDPhotoDetail> SelectGalleryData(DDChannelType channelType, string tableName)
{
if (_detailDataHelper == null)
{
_detailDataHelper = new DDPhotoDetailDataHelper(_dbConn);
}
_detailDataHelper.PhotoDetailTable = tableName;
return _detailDataHelper.SelectGalleryData(channelType);
}
/// <summary>
/// 向内容细节表插入数据
/// </summary>
/// <param name="detail"></param>
/// <param name="channelType"></param>
/// <returns></returns>
public bool InsertDataToDetailTable(DDPhotoDetail detail, DDChannelType channelType, string tableName)
{
if (_detailDataHelper == null)
{
_detailDataHelper = new DDPhotoDetailDataHelper(_dbConn);
}
_detailDataHelper.PhotoDetailTable = tableName;
return _detailDataHelper.InsertData(detail, channelType);
}
/// <summary>
/// 获取某个分类的所有细节信息
/// </summary>
/// <param name="channelType"></param>
/// <returns></returns>
public IEnumerable<DDPhotoDetail> SelectGalleryData(DDChannelType channelType)
{
List<DDPhotoDetail> detailList = null;
try
{
using (SqliteCommand dbCmd = _dbConn.CreateCommand())
{
string select_sql = string.Empty;
if (channelType == DDChannelType.EDDUnknownChannel)
{
select_sql = string.Format("SELECT * FROM {0}", PhotoDetailTable);
}
else
{
select_sql = string.Format("SELECT * FROM {0} where {1}=@{1};", PhotoDetailTable, GalleryTypeCol);
dbCmd.Parameters.Add("@" + GalleryTypeCol, null);
dbCmd.Parameters["@" + GalleryTypeCol].Value = channelType.ToString();
}
dbCmd.CommandText = select_sql;
using (SqliteDataReader reader = dbCmd.ExecuteReader())
{
detailList = new List<DDPhotoDetail>();
while (reader.Read())
{
DDPhotoDetail detail = new DDPhotoDetail();
detail.PhotoTitle = reader.GetString(2);
if (!reader.IsDBNull(3))
{
detail.PhotoIntro = reader.GetString(3);
}
detail.PhotoParentWebUrl = reader.GetString(4);
detail.PhotoCoverImageWebUrl = reader.GetString(5);
detail.PhotoUGC = new DDPhotoUGC();
if (!reader.IsDBNull(6))
{
detail.PhotoUGC.PhotoUploadTime = reader.GetString(6);
}
if (!reader.IsDBNull(7))
{
detail.PhotoUGC.PhotoClickTimes = reader.GetString(7);
}
if (!reader.IsDBNull(8))
{
detail.PhotoUGC.PhotoGoodComments = reader.GetString(8);
}
detailList.Add(detail);
}
reader.Close();
reader.Dispose();
}
dbCmd.Dispose();
}
}
catch (Exception)
{
}
return detailList;
}
/// <summary>
/// 插入一个列表
/// </summary>
/// <param name="detailList"></param>
/// <param name="channelType"></param>
/// <returns></returns>
public void InsertDataListToDetailTable(IEnumerable<DDPhotoDetail> detailList, DDChannelType channelType, string tableName)
{
if (_detailDataHelper == null)
{
_detailDataHelper = new DDPhotoDetailDataHelper(_dbConn);
}
_detailDataHelper.PhotoDetailTable = tableName;
_detailDataHelper.InsertDataList(detailList, channelType);
}
/// <summary>
/// 插入一个数据列表
/// </summary>
/// <param name="detailList"></param>
public void InsertDataList(IEnumerable<DDPhotoDetail> detailList, DDChannelType channelType)
{
if (detailList != null && channelType != DDChannelType.EDDUnknownChannel)
{
SqliteTransaction sqlTrans = null;
try
{
using (SqliteCommand dbCmd = _dbConn.CreateCommand())
{
sqlTrans = _dbConn.BeginTransaction();
string sql_insert = string.Format("INSERT INTO {0}({1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}) VALUES(@{1}, @{2}, @{3}, @{4}, @{5}, @{6}, @{7}, @{8}, @{9});",
PhotoDetailTable,
IdCol,
GalleryTypeCol,
PhotoTitleCol,
PhotoIntroCol,
PhotoParentWebUrlCol,
PhotoCoverImageWebUrlCol,
PhotoUploadTimeCol,
PhotoClickTimesCol,
PhotoGoodCommentsCol);
dbCmd.CommandText = sql_insert;
//dbCmd.ExecuteNonQuery();
dbCmd.Parameters.Add("@" + IdCol, null);
dbCmd.Parameters.Add("@" + GalleryTypeCol, null);
dbCmd.Parameters.Add("@" + PhotoTitleCol, null);
dbCmd.Parameters.Add("@" + PhotoIntroCol, null);
dbCmd.Parameters.Add("@" + PhotoParentWebUrlCol, null);
dbCmd.Parameters.Add("@" + PhotoCoverImageWebUrlCol, null);
dbCmd.Parameters.Add("@" + PhotoUploadTimeCol, null);
dbCmd.Parameters.Add("@" + PhotoClickTimesCol, null);
dbCmd.Parameters.Add("@" + PhotoGoodCommentsCol, null);
foreach (var detail in detailList)
{
dbCmd.Parameters["@" + GalleryTypeCol].Value = channelType.ToString();
dbCmd.Parameters["@" + PhotoTitleCol].Value = detail.PhotoTitle;
dbCmd.Parameters["@" + PhotoIntroCol].Value = detail.PhotoIntro;
dbCmd.Parameters["@" + PhotoParentWebUrlCol].Value = detail.PhotoParentWebUrl;
dbCmd.Parameters["@" + PhotoCoverImageWebUrlCol].Value = detail.PhotoCoverImageWebUrl;
dbCmd.Parameters["@" + PhotoUploadTimeCol].Value = detail.PhotoUGC.PhotoUploadTime;
dbCmd.Parameters["@" + PhotoClickTimesCol].Value = detail.PhotoUGC.PhotoClickTimes;
dbCmd.Parameters["@" + PhotoGoodCommentsCol].Value = detail.PhotoUGC.PhotoGoodComments;
dbCmd.ExecuteScalar();
}
dbCmd.Dispose();
sqlTrans.Commit();
sqlTrans = null;
}
}
catch (Exception)
{
if (sqlTrans != null)
{
sqlTrans.Rollback();
}
}
}
}
