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;
}
public static List<SiteInfo> GetComboBoxSiteInfo(DbConnection myDbCon)
{
List<SiteInfo> listSiteInfo = null;
try
{
//string queryString = "SELECT NAME FROM MOVIE_CONTENTS GROUP BY NAME";
string queryString = "SELECT NAME FROM MOVIE_SITESTORE GROUP BY NAME";
SqlDataAdapter adapter = new SqlDataAdapter(queryString, myDbCon.getSqlConnection());
DataSet dtsetGroupName = new DataSet();
adapter.Fill(dtsetGroupName, "SiteInfo");
// SiteInfoクラスを使用しないとAnonymousType0の型が戻りになる
// その場合にSelectedItemで取得した時に各プロパティの値がとれない
// ※ 複数行で取得した場合はforeach内でプロパティ名で値が取得出来る
var rows = dtsetGroupName.Tables["SiteInfo"].AsEnumerable();
var query = rows.Select(el => new SiteInfo
{
name = el["NAME"].ToString()
});
// 以下のようにqueryを直接ItemsSourceに設定すると次のようなエラーになる
// 'EditItem' is not allowed for this view（'EditItem' は、このビューに対して許可されていません）
// ※ linq to queryからの取得元がGROUP BYで更新不可な構成のためで、List型へ移してから設定する
// dgridGroupItem.ItemsSource = query;
SiteInfo allsite = new SiteInfo();
allsite.name = "全て";
listSiteInfo = query.ToList<SiteInfo>();
listSiteInfo.Insert(0, allsite);
return listSiteInfo;
}
catch (Exception exp)
{
Debug.Write(exp);
}
return listSiteInfo;
}
/// <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 void DbUpdate(DbConnection myDbCon)
{
string sqlCommand = "UPDATE MOVIE_SITECONTENTS ";
sqlCommand += "SET SITE_NAME = @pSiteName, NAME = @pName, PARENT_PATH = @pParentPath, MOVIE_NEWDATE = @pMovieNewDate, MOVIE_COUNT = @pMovieCount, PHOTO_COUNT = @pPhotoCount, EXTENSION = @pExtension ";
sqlCommand += "WHERE ID = @pId ";
MovieFileContentsParent parent = new MovieFileContentsParent();
SqlCommand command = new SqlCommand();
command = new SqlCommand(sqlCommand, myDbCon.getSqlConnection());
SqlParameter[] sqlparams = new SqlParameter[8];
// Create and append the parameters for the Update command.
sqlparams[0] = new SqlParameter("@pSiteName", SqlDbType.VarChar);
sqlparams[0].Value = SiteName;
sqlparams[1] = new SqlParameter("@pName", SqlDbType.VarChar);
sqlparams[1].Value = Name;
sqlparams[2] = new SqlParameter("@pParentPath", SqlDbType.VarChar);
sqlparams[2].Value = ParentPath;
sqlparams[3] = new SqlParameter("@pMovieNewDate", SqlDbType.DateTime);
if (MovieNewDate.Year >= 2000)
sqlparams[3].Value = MovieNewDate;
else
sqlparams[3].Value = Convert.DBNull;
sqlparams[4] = new SqlParameter("@pMovieCount", SqlDbType.VarChar);
sqlparams[4].Value = MovieCount;
// sqlparams[4] = new SqlParameter("@pSellDate", SqlDbType.DateTime);
// if (data.SellDate.Year >= 2000)
// sqlparams[4].Value = data.SellDate;
// else
// sqlparams[4].Value = Convert.DBNull;
sqlparams[5] = new SqlParameter("@pPhotoCount", SqlDbType.VarChar);
sqlparams[5].Value = PhotoCount;
sqlparams[6] = new SqlParameter("@pExtension", SqlDbType.VarChar);
sqlparams[6].Value = Extension;
sqlparams[7] = new SqlParameter("@pId", SqlDbType.Int);
sqlparams[7].Value = Id;
myDbCon.SetParameter(sqlparams);
myDbCon.execSqlCommand(sqlCommand);
}
public void DbExport(DbConnection myDbCon)
{
string sqlCommand = "INSERT MOVIE_SITECONTENTS ";
sqlCommand += "( SITE_NAME, NAME, PARENT_PATH, MOVIE_NEWDATE, MOVIE_COUNT, PHOTO_COUNT, EXTENSION ) ";
sqlCommand += "VALUES( @pSiteName, @pName, @pParentPath, @pMovieNewDate, @pMovieCount, @pPhotoCount, @pExtension )";
SqlCommand command = new SqlCommand();
command = new SqlCommand(sqlCommand, myDbCon.getSqlConnection());
SqlParameter[] sqlparams = new SqlParameter[7];
// Create and append the parameters for the Update command.
sqlparams[0] = new SqlParameter("@pSiteName", SqlDbType.VarChar);
sqlparams[0].Value = SiteName;
sqlparams[1] = new SqlParameter("@pName", SqlDbType.VarChar);
sqlparams[1].Value = Name;
sqlparams[2] = new SqlParameter("@pParentPath", SqlDbType.VarChar);
sqlparams[2].Value = ParentPath;
sqlparams[3] = new SqlParameter("@pMovieNewDate", SqlDbType.DateTime);
if (MovieNewDate.Year >= 2000)
sqlparams[3].Value = MovieNewDate;
else
sqlparams[3].Value = Convert.DBNull;
sqlparams[4] = new SqlParameter("@pMovieCount", SqlDbType.VarChar);
sqlparams[4].Value = MovieCount;
sqlparams[5] = new SqlParameter("@pPhotoCount", SqlDbType.VarChar);
sqlparams[5].Value = PhotoCount;
sqlparams[6] = new SqlParameter("@pExtension", SqlDbType.VarChar);
sqlparams[6].Value = Extension;
myDbCon.SetParameter(sqlparams);
myDbCon.execSqlCommand(sqlCommand);
}
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;
}
public void DbDelete(DbConnection myDbCon)
{
string sqlCommand = "DELETE FROM MOVIE_FILES WHERE ID = @pId";
SqlCommand command = new SqlCommand();
command = new SqlCommand(sqlCommand, myDbCon.getSqlConnection());
SqlParameter[] sqlparams = new SqlParameter[1];
// Create and append the parameters for the Update command.
sqlparams[0] = new SqlParameter("@pId", SqlDbType.VarChar);
sqlparams[0].Value = Id;
myDbCon.SetParameter(sqlparams);
myDbCon.execSqlCommand(sqlCommand);
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;
}
public void DbDelete(DbConnection myDbCon)
{
string sqlCommand = "DELETE FROM MOVIE_SITECONTENTS ";
sqlCommand += "WHERE ID = @pId ";
MovieFileContentsParent parent = new MovieFileContentsParent();
SqlCommand command = new SqlCommand();
command = new SqlCommand(sqlCommand, myDbCon.getSqlConnection());
SqlParameter[] sqlparams = new SqlParameter[1];
sqlparams[0] = new SqlParameter("@pId", SqlDbType.Int);
sqlparams[0].Value = Id;
myDbCon.SetParameter(sqlparams);
myDbCon.execSqlCommand(sqlCommand);
}
/// <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;
}
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 void DbExport(DbConnection myDbCon)
{
string sqlCommand = "INSERT INTO MOVIE_FILES (NAME, SIZE, FILE_DATE, LABEL, SELL_DATE, PRODUCT_NUMBER, FILE_COUNT, EXTENSION) VALUES( @pName, @pSize, @pFileDate, @pLabel, @pSellDate, @pProductNumber, @pFileCount, @pExtension )";
SqlCommand command = new SqlCommand();
command = new SqlCommand(sqlCommand, myDbCon.getSqlConnection());
SqlParameter[] sqlparams = new SqlParameter[8];
// Create and append the parameters for the Update command.
sqlparams[0] = new SqlParameter("@pName", SqlDbType.VarChar);
sqlparams[0].Value = Name;
sqlparams[1] = new SqlParameter("@pSize", SqlDbType.Decimal);
sqlparams[1].Value = Size;
sqlparams[2] = new SqlParameter("@pFileDate", SqlDbType.DateTime);
sqlparams[2].Value = FileDate;
sqlparams[3] = new SqlParameter("@pLabel", SqlDbType.VarChar);
sqlparams[3].Value = Label;
sqlparams[4] = new SqlParameter("@pSellDate", SqlDbType.DateTime);
if (SellDate.Year >= 2000)
sqlparams[4].Value = SellDate;
else
sqlparams[4].Value = Convert.DBNull;
sqlparams[5] = new SqlParameter("@pProductNumber", SqlDbType.VarChar);
sqlparams[5].Value = ProductNumber;
sqlparams[6] = new SqlParameter("@pFileCount", SqlDbType.Int);
sqlparams[6].Value = FileCount;
sqlparams[7] = new SqlParameter("@pExtension", SqlDbType.VarChar);
sqlparams[7].Value = Extension;
myDbCon.SetParameter(sqlparams);
myDbCon.execSqlCommand(sqlCommand);
return;
}