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; }