public static List<MovieMaker> GetAllData() { DbConnection dbcon = new DbConnection(); List<MovieMaker> listMMakers = new List<MovieMaker>(); string queryString = "SELECT ID, NAME, LABEL, KIND, MATCH_STR, MATCH_PRODUCT_NUMBER, CREATE_DATE, UPDATE_DATE FROM MOVIE_MAKERS ORDER BY NAME 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()) { MovieMaker data = new MovieMaker(); data.Id = DbExportCommon.GetDbInt(reader, 0); data.Name = DbExportCommon.GetDbString(reader, 1); data.Label = DbExportCommon.GetDbString(reader, 2); data.Kind = DbExportCommon.GetDbInt(reader, 3); data.MatchStr = DbExportCommon.GetDbString(reader, 4); data.MatchProductNumber = DbExportCommon.GetDbString(reader, 5); data.CreateDate = DbExportCommon.GetDbDateTime(reader, 6); data.UpdateDate = DbExportCommon.GetDbDateTime(reader, 7); listMMakers.Add(data); } } while (reader.NextResult()); reader.Close(); dbcon.closeConnection(); return listMMakers; }
public static MovieMaker GetSearchByProductNumber(string myProductNumber) { DbConnection dbcon = new DbConnection(); if (myProductNumber == null) myProductNumber = ""; string[] product = myProductNumber.Split('-'); List<MovieFileContents> listMContents = new List<MovieFileContents>(); string queryString = "SELECT ID, NAME, SIZE, FILE_DATE, LABEL, SELL_DATE, PRODUCT_NUMBER FROM MOVIE_FILES WHERE PRODUCT_NUMBER LIKE @pProduct ORDER BY FILE_DATE DESC "; dbcon.openConnection(); SqlCommand command = new SqlCommand(queryString, dbcon.getSqlConnection()); SqlParameter param = new SqlParameter("@pProduct", SqlDbType.VarChar); param.Value = product[0] + "-%"; 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); listMContents.Add(data); } } while (reader.NextResult()); reader.Close(); dbcon.closeConnection(); MovieMaker maker = null; foreach(MovieFileContents data in listMContents) { maker = new MovieMaker(); maker.ParseFromFileName(data); break; } return maker; }
public void DatabaseExport() { DbConnection dbcon = new DbConnection(); // データベースへ登録 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(sqlCommand, dbcon.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 = DatabaseMovieFile.Name; sqlparams[1] = new SqlParameter("@pSize", SqlDbType.Decimal); sqlparams[1].Value = DatabaseMovieFile.Size; sqlparams[2] = new SqlParameter("@pFileDate", SqlDbType.DateTime); sqlparams[2].Value = DatabaseMovieFile.FileDate; sqlparams[3] = new SqlParameter("@pLabel", SqlDbType.VarChar); sqlparams[3].Value = LabelPath; sqlparams[4] = new SqlParameter("@pSellDate", SqlDbType.DateTime); if (DatabaseMovieFile.SellDate.Year >= 2000) sqlparams[4].Value = DatabaseMovieFile.SellDate; else sqlparams[4].Value = Convert.DBNull; sqlparams[5] = new SqlParameter("@pProductNumber", SqlDbType.VarChar); sqlparams[5].Value = DatabaseMovieFile.ProductNumber; sqlparams[6] = new SqlParameter("@pFileCount", SqlDbType.Int); sqlparams[6].Value = DatabaseMovieFile.FileCount; sqlparams[7] = new SqlParameter("@pExtension", SqlDbType.VarChar); sqlparams[7].Value = DatabaseMovieFile.Extension; dbcon.SetParameter(sqlparams); dbcon.execSqlCommand(sqlCommand); }