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="myDbCon"></param>
/// <returns></returns>
public static string GetExistContentsPath(string mySiteName, string myContentsName, DbConnection myDbCon)
{
string queryString = "SELECT PATH FROM MOVIE_SITESTORE WHERE NAME = '" + mySiteName + "'";
SqlCommand command = new SqlCommand(queryString, myDbCon.getSqlConnection());
myDbCon.openConnection();
SqlDataReader reader = command.ExecuteReader();
string FilePathname = "";
do
{
while (reader.Read())
{
string ContentsPath = DbExportCommon.GetDbString(reader, 0);
string LinkPath = System.IO.Path.Combine(ContentsPath, myContentsName);
if (Directory.Exists(LinkPath))
{
FilePathname = LinkPath;
break;
}
else if (System.IO.File.Exists(LinkPath))
{
FilePathname = LinkPath;
break;
}
}
} while (reader.NextResult());
reader.Close();
myDbCon.closeConnection();
return FilePathname;
}
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;
}
public List<MovieFileContents> GetDbContents(string myLabel)
{
DbConnection dbcon = new DbConnection();
List<MovieFileContents> listMContents = new List<MovieFileContents>();
string queryString = "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("@pLabel", SqlDbType.VarChar);
param.Value = myLabel;
command.Parameters.Add(param);
SqlDataReader reader = command.ExecuteReader();
do
{
while (reader.Read())
{
MovieFileContents data = new MovieFileContents();
data.Id = DbExportCommon.GetDbInt(reader, 0);
data.Name = DbExportCommon.GetDbString(reader, 1);
data.Size = DbExportCommon.GetLong(reader, 2);
data.FileDate = DbExportCommon.GetDbDateTime(reader, 3);
data.Label = DbExportCommon.GetDbString(reader, 4);
data.SellDate = DbExportCommon.GetDbDateTime(reader, 5);
data.ProductNumber = DbExportCommon.GetDbString(reader, 6);
data.FileCount = DbExportCommon.GetDbInt(reader, 7);
data.Extension = DbExportCommon.GetDbString(reader, 8);
data.Rating = DbExportCommon.GetDbInt(reader, 9);
data.Comment = DbExportCommon.GetDbString(reader, 10);
listMContents.Add(data);
}
} while (reader.NextResult());
reader.Close();
dbcon.closeConnection();
return listMContents;
}
/// <summary>
/// MOVIE_ACTRESSのACTIVITY_DATEを更新する
/// </summary>
/// <param name="myDbCon"></param>
/// <returns></returns>
public static List<SiteInfo> UpdateGroupInfoActivityDate(DateTime myUpdateDate, GroupInfo myTargetInfo, DbConnection myDbCon)
{
List<SiteInfo> listSiteInfo = new List<SiteInfo>();
string cmdUpdate = "update MOVIE_ACTRESS set ACTIVITY_DATE = @pActivityDate where ID = @pId";
SqlParameter[] sqlparams = new SqlParameter[2];
sqlparams[0] = new SqlParameter("@pActivityDate", SqlDbType.DateTime);
sqlparams[0].Value = myUpdateDate;
sqlparams[1] = new SqlParameter("@pId", SqlDbType.Int);
sqlparams[1].Value = myTargetInfo.id;
myDbCon.openConnection();
myDbCon.SetParameter(sqlparams);
myDbCon.execSqlCommand(cmdUpdate);
myDbCon.closeConnection();
return listSiteInfo;
}
/// <summary>
/// 存在するコンテンツ内のパス名をサイト内のパス群から取得する
/// </summary>
/// <param name="myDbCon"></param>
/// <returns></returns>
public static DateTime GetContentsDateOld(GroupInfo myTargetInfo, DbConnection myDbCon)
{
List<SiteInfo> listSiteInfo = new List<SiteInfo>();
string queryString = "SELECT CONTENTS_DATE FROM MOVIE_CONTENTS WHERE ACTRESS_NAME = @pName AND CONTENTS_DATE IS NOT NULL ORDER BY CONTENTS_DATE ";
SqlCommand command = new SqlCommand(queryString, myDbCon.getSqlConnection());
myDbCon.openConnection();
SqlParameter sqlparam = new SqlParameter();
sqlparam = new SqlParameter("@pName", SqlDbType.VarChar);
sqlparam.Value = myTargetInfo.name;
command.Parameters.Add(sqlparam);
SqlDataReader reader = command.ExecuteReader();
DateTime dt = new DateTime();
if (reader.Read())
{
dt = DbExportCommon.GetDbDateTime(reader, 0);
}
reader.Close();
myDbCon.closeConnection();
return dt;
}
/// <summary>
/// MOVIE_SITESTOREのID一致ROWを削除する
/// </summary>
/// <param name="myDbCon"></param>
/// <returns></returns>
public static void DeleteSiteStore(SiteStore myTargetData, DbConnection myDbCon)
{
List<SiteInfo> listSiteInfo = new List<SiteInfo>();
string cmdUpdate = "DELETE FROM MOVIE_SITESTORE WHERE ID = @pId";
SqlParameter[] sqlparams = new SqlParameter[1];
sqlparams[0] = new SqlParameter("@pId", SqlDbType.Int);
sqlparams[0].Value = myTargetData.Id;
myDbCon.openConnection();
myDbCon.SetParameter(sqlparams);
myDbCon.execSqlCommand(cmdUpdate);
myDbCon.closeConnection();
return;
}
/// <summary>
/// MOVIE_SITESTOREに行の更新をする
/// </summary>
/// <param name="myDbCon"></param>
/// <returns></returns>
public static void UpdateSiteStore(SiteStore myTargetData, DbConnection myDbCon)
{
List<SiteInfo> listSiteInfo = new List<SiteInfo>();
string cmdUpdate = "UPDATE MOVIE_SITESTORE SET NAME = @pName, LABEL = @pLabel, EXPLANATION = @pExplanation, KIND = @pKind WHERE ID = @pId";
SqlParameter[] sqlparams = new SqlParameter[5];
sqlparams[0] = new SqlParameter("@pName", SqlDbType.VarChar);
sqlparams[0].Value = myTargetData.Name;
sqlparams[1] = new SqlParameter("@pLabel", SqlDbType.VarChar);
sqlparams[1].Value = myTargetData.Label;
sqlparams[2] = new SqlParameter("@pExplanation", SqlDbType.VarChar);
sqlparams[2].Value = myTargetData.Explanation;
sqlparams[3] = new SqlParameter("@pKind", SqlDbType.Int);
sqlparams[3].Value = myTargetData.Kind;
sqlparams[4] = new SqlParameter("@pId", SqlDbType.Int);
sqlparams[5].Value = myTargetData.Id;
myDbCon.openConnection();
myDbCon.SetParameter(sqlparams);
myDbCon.execSqlCommand(cmdUpdate);
myDbCon.closeConnection();
return;
}
/// <summary>
/// MOVIE_SITESTOREに行を作成する
/// </summary>
/// <param name="myDbCon"></param>
/// <returns></returns>
public static void InsertSiteStore(SiteStore myTargetData, DbConnection myDbCon)
{
List<SiteInfo> listSiteInfo = new List<SiteInfo>();
string cmdUpdate = "INSERT INTO MOVIE_SITESTORE(NAME, LABEL, EXPLANATION, KIND) VALUES (@pName, @pLabel, @pExplanation, @pKind)";
SqlParameter[] sqlparams = new SqlParameter[4];
sqlparams[0] = new SqlParameter("@pName", SqlDbType.VarChar);
sqlparams[0].Value = myTargetData.Name;
sqlparams[1] = new SqlParameter("@pLabel", SqlDbType.VarChar);
sqlparams[1].Value = myTargetData.Label;
sqlparams[2] = new SqlParameter("@pExplanation", SqlDbType.VarChar);
sqlparams[2].Value = myTargetData.Explanation;
sqlparams[3] = new SqlParameter("@pKind", SqlDbType.Int);
sqlparams[3].Value = myTargetData.Kind;
myDbCon.openConnection();
myDbCon.SetParameter(sqlparams);
myDbCon.execSqlCommand(cmdUpdate);
myDbCon.closeConnection();
return;
}
/// <summary>
/// 存在するコンテンツ内のパス名をサイト内のパス群から取得する
/// </summary>
/// <param name="myDbCon"></param>
/// <returns></returns>
public static List<SiteStore> GetSiteStore(DbConnection myDbCon)
{
List<SiteStore> listSiteStore = new List<SiteStore>();
//string queryString = "SELECT ID, NAME, PATH, KIND FROM MOVIE_SITESTORE ORDER BY NAME, PATH";
string queryString = "SELECT ID, NAME, EXPLANATION, LABEL, KIND FROM MOVIE_GROUP WHERE KIND = 3 ORDER BY NAME, EXPLANATION";
SqlCommand command = new SqlCommand(queryString, myDbCon.getSqlConnection());
myDbCon.openConnection();
SqlDataReader reader = command.ExecuteReader();
do
{
while (reader.Read())
{
SiteStore site = new SiteStore();
site.Id = DbExportCommon.GetDbInt(reader, 0);
site.Name = DbExportCommon.GetDbString(reader, 1);
site.Explanation = DbExportCommon.GetDbString(reader, 2);
site.Label = DbExportCommon.GetDbString(reader, 3);
site.Kind = DbExportCommon.GetDbInt(reader, 4);
listSiteStore.Add(site);
}
} while (reader.NextResult());
reader.Close();
myDbCon.closeConnection();
return listSiteStore;
}
/// <summary>
/// 存在するコンテンツ内のパス名をサイト内のパス群から取得する
/// </summary>
/// <param name="myDbCon"></param>
/// <returns></returns>
public static List<SiteInfo> GetSiteStoreInfo(DbConnection myDbCon)
{
List<SiteInfo> listSiteInfo = new List<SiteInfo>();
string queryString = "SELECT NAME, PATH FROM MOVIE_CONTENTS ";
SqlCommand command = new SqlCommand(queryString, myDbCon.getSqlConnection());
myDbCon.openConnection();
SqlDataReader reader = command.ExecuteReader();
do
{
while (reader.Read())
{
SiteInfo siteinfo = new SiteInfo();
siteinfo.name = DbExportCommon.GetDbString(reader, 0);
siteinfo.path = DbExportCommon.GetDbString(reader, 1);
listSiteInfo.Add(siteinfo);
}
} while (reader.NextResult());
reader.Close();
myDbCon.closeConnection();
return listSiteInfo;
}