public static List<MovieSiteContents> GetDbContents(string mySiteName)
{
DbConnection dbcon = new DbConnection();
List<MovieSiteContents> listMContents = new List<MovieSiteContents>();
string queryString = "SELECT ID, NAME,MOVIE_NEWDATE,RATING,SITE_NAME,LABEL "
+ ", COMMENT, REMARK, PARENT_PATH, MOVIE_COUNT, PHOTO_COUNT, EXTENSION, TAG "
+ "FROM MOVIE_SITECONTENTS "
+ "WHERE SITE_NAME = @pSiteName "
+ "ORDER BY MOVIE_NEWDATE DESC ";
//"SELECT ID, NAME, SIZE, FILE_DATE, LABEL, SELL_DATE, PRODUCT_NUMBER, FILE_COUNT, EXTENSION, RATING, COMMENT FROM MOVIE_FILES WHERE LABEL = @pLabel ORDER BY FILE_DATE DESC ";
dbcon.openConnection();
SqlCommand command = new SqlCommand(queryString, dbcon.getSqlConnection());
SqlParameter param = new SqlParameter("@pSiteName", SqlDbType.VarChar);
param.Value = mySiteName;
command.Parameters.Add(param);
SqlDataReader reader = command.ExecuteReader();
do
{
while (reader.Read())
{
MovieSiteContents data = new MovieSiteContents();
data.Id = DbExportCommon.GetDbInt(reader, 0);
data.Name = DbExportCommon.GetDbString(reader, 1);
data.MovieNewDate = DbExportCommon.GetDbDateTime(reader, 2);
data.Rating = DbExportCommon.GetDbInt(reader, 3);
data.SiteName = DbExportCommon.GetDbString(reader, 4);
data.Label = DbExportCommon.GetDbString(reader, 5);
data.Comment = DbExportCommon.GetDbString(reader, 6);
data.Remark = DbExportCommon.GetDbString(reader, 7);
data.ParentPath = DbExportCommon.GetDbString(reader, 8);
data.MovieCount = DbExportCommon.GetDbString(reader, 9);
data.PhotoCount = DbExportCommon.GetDbString(reader, 10);
data.Extension = DbExportCommon.GetDbString(reader, 11);
//data.Tag = DbExportCommon.GetDbString(reader, 12);
//data.ChildTableName = MovieContents.TABLE_KIND_MOVIE_SITECONTENTS;
listMContents.Add(data);
}
} while (reader.NextResult());
reader.Close();
dbcon.closeConnection();
return listMContents;
}
/// <summary>
/// データベースの更新が必要かのチェックを行う
/// </summary>
/// <param name="myData"></param>
/// <returns>string 更新対象の列名</returns>
public string IsDbUpdate(MovieSiteContents myData)
{
string updatecolname = "";
string srcDate = MovieNewDate.ToString("yyyy/MM/dd HH:mm:SS");
string DestDate = MovieNewDate.ToString("yyyy/MM/dd HH:mm:SS");
if (!srcDate.Equals(DestDate))
updatecolname += "MovieNewDate,";
if (MovieCount != myData.MovieCount)
updatecolname += "MovieCount,";
if (PhotoCount != myData.PhotoCount)
updatecolname += "PhotoCount,";
if (ParentPath != myData.ParentPath)
updatecolname += "ParentPath,";
if (Extension != myData.Extension)
updatecolname += "Extension,";
return updatecolname;
}
public static MovieSiteContents GetDbDataByName(string mySiteName, string myParentPath, string myName, DbConnection myDbCon)
{
SqlDataReader reader = null;
MovieSiteContents sitedata = null;
try
{
string sqlCommand = "SELECT ID, NAME, SITE_NAME, PARENT_PATH, MOVIE_COUNT, PHOTO_COUNT, MOVIE_NEWDATE, EXTENSION FROM MOVIE_SITECONTENTS WHERE SITE_NAME = @pSiteName AND NAME = @pName AND PARENT_PATH = @pParentPath";
SqlCommand command = new SqlCommand(sqlCommand, myDbCon.getSqlConnection());
myDbCon.openConnection();
SqlParameter param = new SqlParameter();
param = new SqlParameter("@pSiteName", SqlDbType.VarChar);
param.Value = mySiteName;
command.Parameters.Add(param);
param = new SqlParameter("@pName", SqlDbType.VarChar);
param.Value = myName;
command.Parameters.Add(param);
param = new SqlParameter("@pParentPath", SqlDbType.VarChar);
param.Value = myParentPath;
command.Parameters.Add(param);
reader = command.ExecuteReader();
if (reader.Read())
{
sitedata = new MovieSiteContents();
sitedata.Id = DbExportCommon.GetDbInt(reader, 0);
sitedata.Name = DbExportCommon.GetDbString(reader, 1);
sitedata.SiteName = DbExportCommon.GetDbString(reader, 2);
sitedata.ParentPath = DbExportCommon.GetDbString(reader, 3);
sitedata.MovieCount = DbExportCommon.GetDbString(reader, 4);
sitedata.PhotoCount = DbExportCommon.GetDbString(reader, 5);
sitedata.MovieNewDate = DbExportCommon.GetDbDateTime(reader, 6);
sitedata.Extension = DbExportCommon.GetDbString(reader, 7);
}
reader.Close();
}
catch (Exception exp)
{
Debug.Write(exp);
throw new Exception(exp.Message);
}
finally
{
reader.Close();
}
return sitedata;
}