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 void DbDelete(DbConnection myDbCon)
{
string DeleteCommand = "";
DeleteCommand = "DELETE MOVIE_MAKERS WHERE ID = @pId ";
//Debug.Print(InsertCommand);
SqlParameter[] sqlparams = new SqlParameter[1];
// ID
sqlparams[0] = new SqlParameter("@pId", SqlDbType.Int);
sqlparams[0].Value = Id;
myDbCon.SetParameter(sqlparams);
// DELETE文の実行
int cnt = myDbCon.execSqlCommand(DeleteCommand);
if (cnt == 0)
throw new Exception("ID[" + Id + "]の削除対象のデータが存在しません");
}
public void DbExport(DbConnection myDbCon)
{
DbConnection dbcon;
string sqlcmd = "";
// 引数にコネクションが指定されていた場合は指定されたコネクションを使用
if (myDbCon != null)
dbcon = myDbCon;
else
dbcon = new DbConnection();
sqlcmd = "INSERT INTO MOVIE_MAKERS ( NAME, LABEL, KIND, MATCH_STR, MATCH_PRODUCT_NUMBER ) ";
sqlcmd = sqlcmd + "VALUES( @NAME, @LABEL, @KIND, @MATCH_STR, @MATCH_PRODUCT_NUMBER ) ";
SqlCommand scmd = new SqlCommand(sqlcmd, dbcon.getSqlConnection());
DataTable dtSaraly = new DataTable();
SqlParameter[] sqlparams = new SqlParameter[5];
sqlparams[0] = new SqlParameter("@NAME", SqlDbType.VarChar);
sqlparams[0].Value = Name;
sqlparams[1] = new SqlParameter("@LABEL", SqlDbType.VarChar);
sqlparams[1].Value = Label;
sqlparams[2] = new SqlParameter("@KIND", SqlDbType.Int);
sqlparams[2].Value = Kind;
sqlparams[3] = new SqlParameter("@MATCH_STR", SqlDbType.VarChar);
sqlparams[3].Value = MatchStr;
sqlparams[4] = new SqlParameter("@MATCH_PRODUCT_NUMBER", SqlDbType.VarChar);
sqlparams[4].Value = MatchProductNumber;
dbcon.SetParameter(sqlparams);
dbcon.execSqlCommand(sqlcmd);
return;
}
public void DbUpdate(DbConnection myDbCon)
{
DbConnection dbcon;
string sqlcmd = "";
// 引数にコネクションが指定されていた場合は指定されたコネクションを使用
if (myDbCon != null)
dbcon = myDbCon;
else
dbcon = new DbConnection();
sqlcmd = "UPDATE MOVIE_MAKERS ";
sqlcmd = sqlcmd + "SET NAME = @NAME, LABEL = @LABEL, KIND = @KIND, MATCH_STR = @MATCH_STR, MATCH_PRODUCT_NUMBER = @MATCH_PRODUCT_NUMBER ";
sqlcmd = sqlcmd + "WHERE ID = @ID ";
SqlCommand scmd = new SqlCommand(sqlcmd, dbcon.getSqlConnection());
DataTable dtSaraly = new DataTable();
SqlParameter[] sqlparams = new SqlParameter[6];
sqlparams[0] = new SqlParameter("@NAME", SqlDbType.VarChar);
sqlparams[0].Value = Name;
sqlparams[1] = new SqlParameter("@LABEL", SqlDbType.VarChar);
sqlparams[1].Value = Label;
sqlparams[2] = new SqlParameter("@KIND", SqlDbType.Int);
sqlparams[2].Value = Kind;
sqlparams[3] = new SqlParameter("@MATCH_STR", SqlDbType.VarChar);
sqlparams[3].Value = MatchStr;
sqlparams[4] = new SqlParameter("@MATCH_PRODUCT_NUMBER", SqlDbType.VarChar);
sqlparams[4].Value = MatchProductNumber;
sqlparams[5] = new SqlParameter("@ID", SqlDbType.Int);
sqlparams[5].Value = Id;
dbcon.SetParameter(sqlparams);
dbcon.execSqlCommand(sqlcmd);
return;
}
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);
}