예제 #1
0
        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);
        }
예제 #4
0
        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
             *
             */
        }
예제 #5
0
        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);
        }
예제 #6
0
        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));
        }
예제 #7
0
        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);
            }
        }