private int ProcessRECORD_PET_SIZE(string selected_field)
{
/*
* CREATE TABLE [dbo].[RECORD_PET_SIZE] (
* [ID_PK] INT IDENTITY (1, 1) NOT NULL,
* [DESCRIPTION] NVARCHAR (25) NOT NULL,
* PRIMARY KEY CLUSTERED ([ID_PK] ASC)
* );
*/
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT ID_PK FROM [RECORD_PET_SIZE] WHERE [DESCRIPTION] = @FIELD_TEXT";
command.Parameters.AddWithValue("@FIELD_TEXT", selected_field);
int field_id = DAOHelper.RetreiveID(command);
SqlCommand executeDataCommand = new SqlCommand();
executeDataCommand.Parameters.AddWithValue("@FIELD_TEXT", selected_field);
if (field_id == -1) /* New Record */
{
executeDataCommand.CommandText = "INSERT INTO [RECORD_PET_SIZE] ([DESCRIPTION]) VALUES(@FIELD_TEXT) ;";
DAOHelper.InsertData(executeDataCommand);
field_id = DAOHelper.RetreiveID(command);
}
else
{
executeDataCommand.CommandText = "UPDATE [RECORD_PET_SIZE] SET [DESCRIPTION] = @FIELD_TEXT WHERE [ID_PK] = @ID_PK ;";
executeDataCommand.Parameters.AddWithValue("@ID_PK", field_id);
DAOHelper.InsertData(executeDataCommand);
}
return(field_id);
}
private void ProcessMarineSpeciesData(int class_id, int counter, string scientific, string common)
{
int record_id = -1;
SqlCommand command;
/*
* CREATE TABLE [dbo].[MARINE_SPECIES] (
* [ID_PK] INT IDENTITY (1, 1) NOT NULL,
* [CLASS_FK] INT NOT NULL,
* [SPECIES_FK] INT NOT NULL,
* [SCIENTIFIC] NVARCHAR (40) NOT NULL,
* [COMMON] NVARCHAR (80) NULL,
* [TEXT] NVARCHAR (50) NULL,
* [FAMILY_FK] INT NULL,
* CONSTRAINT [PK_MARINE_SPECIES] PRIMARY KEY CLUSTERED ([ID_PK] ASC),
* CONSTRAINT [FK_MARINE_SPECIES_MARINE_CLASS] FOREIGN KEY ([CLASS_FK]) REFERENCES [dbo].[MARINE_CLASS] ([ID_PK]),
* CONSTRAINT [FK_MARINE_SPECIES_MARINE_FAMILY] FOREIGN KEY ([FAMILY_FK]) REFERENCES [dbo].[MARINE_FAMILY] ([ID_PK])
* );
*/
/*
* For Hard Coral, [Acanthastrea lordhowensis] is repeated twice in the document
*
*/
command = new SqlCommand();
command.CommandText = "SELECT ID_PK FROM [MARINE_SPECIES] WHERE [SCIENTIFIC] = @SCIENTIFIC_TEXT";
command.Parameters.AddWithValue("@SCIENTIFIC_TEXT", scientific);
record_id = DAOHelper.RetreiveID(command);
SqlCommand insertDataCommand = new SqlCommand();
insertDataCommand.Parameters.AddWithValue("@CLASS_ID", class_id);
insertDataCommand.Parameters.AddWithValue("@SPECIES_ID", counter);
insertDataCommand.Parameters.AddWithValue("@SCIENTIFIC_TEXT", scientific);
insertDataCommand.Parameters.AddWithValue("@COMMON_TEXT", common);
if (record_id == -1) /* New Record */
{
insertDataCommand.CommandText = "INSERT INTO [MARINE_SPECIES] " +
"([CLASS_FK], [SPECIES_FK], [SCIENTIFIC], [COMMON]) VALUES (@CLASS_ID, @SPECIES_ID, @SCIENTIFIC_TEXT,@COMMON_TEXT) ;";
DAOHelper.InsertData(insertDataCommand);
//record_id = DAOHelper.RetreiveID(command);
}
else
{
insertDataCommand.CommandText = "UPDATE [MARINE_SPECIES] " +
"SET [CLASS_FK] = @CLASS_ID, [SPECIES_FK] = @SPECIES_ID, [SCIENTIFIC] = @SCIENTIFIC_TEXT, [COMMON] = @COMMON_TEXT " +
" WHERE [ID_PK] = @ID_PK ;";
insertDataCommand.Parameters.AddWithValue("@ID_PK", record_id);
DAOHelper.InsertData(insertDataCommand);
}
//return record_id;
}
private int ProcessMarineClassData(string selected_field)
{
SqlCommand command;
//string selected_field = GetSelectedWord(line, field_name);
/*
* CREATE TABLE [dbo].[MARINE_CLASS] (
* [ID_PK] INT IDENTITY (1, 1) NOT NULL,
* [TEXT] NVARCHAR (40) NULL,
* [SCHEDULE4] NVARCHAR (40) NOT NULL,
* PRIMARY KEY CLUSTERED ([ID_PK] ASC)
* );
*/
/* Check whether the field name is in [MARINE_CLASS] */
//sql_statement = "SELECT ID_PK FROM [MARINE_CLASS] WHERE [SCHEDULE4] = @FIELD_TEXT";
command = new SqlCommand();
command.CommandText = "SELECT ID_PK FROM [MARINE_CLASS] WHERE [SCHEDULE4] = @FIELD_TEXT";
command.Parameters.AddWithValue("@FIELD_TEXT", selected_field);
int field_id = DAOHelper.RetreiveID(command);
//command.CommandText = "SELECT ID_PK FROM [MARINE_CLASS] WHERE [SCHEDULE4] = @FIELD_TEXT";
SqlCommand executeDataCommand = new SqlCommand();
executeDataCommand.Parameters.AddWithValue("@FIELD_TEXT", selected_field);
if (field_id == -1) /* New Record */
{
executeDataCommand.CommandText = "INSERT INTO [MARINE_CLASS] ([SCHEDULE4]) VALUES(@FIELD_TEXT) ;";
DAOHelper.InsertData(executeDataCommand);
field_id = DAOHelper.RetreiveID(command);
}
else
{
executeDataCommand.CommandText = "UPDATE [MARINE_CLASS] SET [SCHEDULE4] = @FIELD_TEXT WHERE [ID_PK] = @ID_PK ;";
executeDataCommand.Parameters.AddWithValue("@ID_PK", field_id);
DAOHelper.InsertData(executeDataCommand);
}
//return command;
return(field_id);
}
private void updateSpeciesData(int family_id, string selected_field)
{
/*
* CREATE TABLE [dbo].[MARINE_SPECIES] (
* [ID_PK] INT IDENTITY (1, 1) NOT NULL,
* [CLASS_FK] INT NOT NULL,
* [SPECIES_FK] INT NOT NULL,
* [SCIENTIFIC] NVARCHAR (40) NOT NULL,
* [COMMON] NVARCHAR (80) NULL,
* [TEXT] NVARCHAR (50) NULL,
* [FAMILY_FK] INT NULL,
* CONSTRAINT [PK_MARINE_SPECIES] PRIMARY KEY CLUSTERED ([ID_PK] ASC),
* CONSTRAINT [FK_MARINE_SPECIES_MARINE_CLASS] FOREIGN KEY ([CLASS_FK]) REFERENCES [dbo].[MARINE_CLASS] ([ID_PK])
* );
*/
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT ID_PK FROM [MARINE_SPECIES] WHERE [SCIENTIFIC] = @FIELD_TEXT";
command.Parameters.AddWithValue("@FIELD_TEXT", selected_field);
int field_id = DAOHelper.RetreiveID(command);
if (field_id > 0 && family_id > 0)
{
SqlCommand executeDataCommand = new SqlCommand();
executeDataCommand.CommandText = "UPDATE [MARINE_SPECIES] SET [FAMILY_FK] = @FAMILY_ID WHERE [ID_PK] = @ID_PK ;";
executeDataCommand.Parameters.AddWithValue("@FAMILY_ID", family_id);
executeDataCommand.Parameters.AddWithValue("@ID_PK", field_id);
DAOHelper.InsertData(executeDataCommand);
Console.Write(" ==> Record Found !!!");
}
/*
* Please note that due to an error in the doucment, the following reocord is not updated with family id
*
* Puntius cumingii, it should be Puntius cumungii
*
*/
}
private int ProcessMarineFamilyData(string selected_field)
{
/*
* CREATE TABLE [dbo].[MARINE_FAMILY] (
* [ID_PK] INT IDENTITY (1, 1) NOT NULL,
* [TEXT] NVARCHAR (25) NOT NULL,
* [SCHEDULE3] NVARCHAR (25) NOT NULL,
* PRIMARY KEY CLUSTERED ([ID_PK] ASC)
* );
*/
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT ID_PK FROM [MARINE_FAMILY] WHERE [SCHEDULE3] = @FIELD_TEXT";
command.Parameters.AddWithValue("@FIELD_TEXT", selected_field);
int field_id = DAOHelper.RetreiveID(command);
//return field_id;
SqlCommand executeDataCommand = new SqlCommand();
executeDataCommand.Parameters.AddWithValue("@FIELD_TEXT", selected_field);
if (field_id == -1) /* New Record */
{
executeDataCommand.CommandText = "INSERT INTO [MARINE_FAMILY] ([SCHEDULE3],[TEXT]) VALUES(@FIELD_TEXT, @FIELD_TEXT) ;";
DAOHelper.InsertData(executeDataCommand);
field_id = DAOHelper.RetreiveID(command);
}
else
{
executeDataCommand.CommandText = "UPDATE [MARINE_FAMILY] SET [SCHEDULE3] = @FIELD_TEXT WHERE [ID_PK] = @ID_PK ;";
executeDataCommand.Parameters.AddWithValue("@ID_PK", field_id);
DAOHelper.InsertData(executeDataCommand);
}
return(field_id);
}
public static int GetIDByScientificName(string scientific_name)
{
/*
* CREATE TABLE [dbo].[MARINE_SPECIES] (
* [ID_PK] INT IDENTITY (1, 1) NOT NULL,
* [CLASS_FK] INT NOT NULL,
* [SPECIES_FK] INT NOT NULL,
* [SCIENTIFIC] NVARCHAR (40) NOT NULL,
* [COMMON] NVARCHAR (80) NULL,
* [TEXT] NVARCHAR (50) NULL,
* [FAMILY_FK] INT NULL,
* CONSTRAINT [PK_MARINE_SPECIES] PRIMARY KEY CLUSTERED ([ID_PK] ASC),
* CONSTRAINT [FK_MARINE_SPECIES_MARINE_CLASS] FOREIGN KEY ([CLASS_FK]) REFERENCES [dbo].[MARINE_CLASS] ([ID_PK]),
* CONSTRAINT [FK_MARINE_SPECIES_MARINE_FAMILY] FOREIGN KEY ([FAMILY_FK]) REFERENCES [dbo].[MARINE_FAMILY] ([ID_PK])
* );
*/
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT ID_PK FROM [MARINE_SPECIES] WHERE [SCIENTIFIC] LIKE @SCIENTIFIC_TEXT";
command.Parameters.AddWithValue("@SCIENTIFIC_TEXT", "%" + scientific_name + "%");
return(DAOHelper.RetreiveID(command));
}
private void processRECORD_PET(RECORD_PET data)
{
/*
* CREATE TABLE [dbo].[RECORD_PET] (
* [ID_PK] INT IDENTITY (1, 1) NOT NULL,
* [CODE] NVARCHAR (10) NOT NULL,
* [DESCRIPTION] NVARCHAR (50) NULL,
* [SPECIES_FK] INT NULL,
* [SIZE_FK] INT NOT NULL,
* [GROUP_FK] INT NULL,
* PRIMARY KEY CLUSTERED ([ID_PK] ASC)
* );
*/
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT ID_PK FROM [RECORD_PET] WHERE [CODE] = @CODE_FIELD";
command.Parameters.AddWithValue("@CODE_FIELD", data.CODE);
int field_id = DAOHelper.RetreiveID(command);
SqlCommand executeDataCommand = new SqlCommand();
executeDataCommand.Parameters.AddWithValue("@CODE_FIELD", data.CODE);
if (field_id == -1) /* New Record */
{
//executeDataCommand.CommandText = "INSERT INTO [RECORD_PET] ([CODE]) VALUES(@CODE_FIELD) ;";
if (data.SPECIES_FK > 0)
{
executeDataCommand.CommandText = "INSERT INTO [RECORD_PET]" +
" ([CODE], [DESCRIPTION], [SPECIES_FK], [SIZE_FK], [GROUP_FK])" +
" VALUES(@CODE_FIELD, @DESCRIPTION_FIELD, @SPECIES_FIELD, @SIZE_FIELD, @GROUP_FIELD) ;";
executeDataCommand.Parameters.AddWithValue("@SPECIES_FIELD", data.SPECIES_FK);
}
else
{
executeDataCommand.CommandText = "INSERT INTO [RECORD_PET]" +
" ([CODE], [DESCRIPTION], [SIZE_FK], [GROUP_FK])" +
" VALUES(@CODE_FIELD, @DESCRIPTION_FIELD, @SIZE_FIELD, @GROUP_FIELD) ;";
}
executeDataCommand.Parameters.AddWithValue("@DESCRIPTION_FIELD", data.DESCRIPTION);
executeDataCommand.Parameters.AddWithValue("@SIZE_FIELD", data.SIZE_FK);
executeDataCommand.Parameters.AddWithValue("@GROUP_FIELD", data.GROUP_FK);
DAOHelper.InsertData(executeDataCommand);
//field_id = DAOHelper.RetreiveID(command);
}
else
{
//executeDataCommand.CommandText = "UPDATE [RECORD_PET_SIZE] SET [CODE] = @CODE_FIELD" + "WHERE [ID_PK] = @ID_PK ;";
executeDataCommand.Parameters.AddWithValue("@ID_PK", field_id);
if (data.SPECIES_FK > 0)
{
executeDataCommand.CommandText = "UPDATE [RECORD_PET] "
+ " SET [CODE] = @CODE_FIELD, [DESCRIPTION] = @DESCRIPTION_FIELD, [SPECIES_FK] = @SPECIES_FIELD, [SIZE_FK] = @SIZE_FIELD, [GROUP_FK] = @GROUP_FIELD"
+ " WHERE [ID_PK] = @ID_PK ;";
executeDataCommand.Parameters.AddWithValue("@SPECIES_FIELD", data.SPECIES_FK);
}
else
{
executeDataCommand.CommandText = "UPDATE [RECORD_PET] "
+ " SET [CODE] = @CODE_FIELD, [DESCRIPTION] = @DESCRIPTION_FIELD, [SIZE_FK] = @SIZE_FIELD, [GROUP_FK] = @GROUP_FIELD"
+ " WHERE [ID_PK] = @ID_PK ;";
}
executeDataCommand.Parameters.AddWithValue("@DESCRIPTION_FIELD", data.DESCRIPTION);
executeDataCommand.Parameters.AddWithValue("@SIZE_FIELD", data.SIZE_FK);
executeDataCommand.Parameters.AddWithValue("@GROUP_FIELD", data.GROUP_FK);
DAOHelper.InsertData(executeDataCommand);
}
}