Inserting only new data to SQL Table

Question

Right now I have a SQL Server Table that's populated with data from external .CSV file that gets updated occasionally by having new data appended to it. Originally I had my program set up to delete everything in the table every time its ran and it would upload everything from the .CSV file. Now I'm trying to reconfigure it so that it only inserts newly appended data from the file instead of deleting everything first.

The program works by first delimiting data from the .CSV file that gets uploaded to array. Then I basically go through the array and upload each element. Right now I have secondary array that contains all the current primary keys from the Table and so far I have tried to compare these to the ones in the .CSV file that's being read. So it checks if the the primary key from the .CSV file is not already in the table / secondary array it goes ahead and does the inserting. This seems like a sound logic to be but for some reason I can't get it to work.

Here is the meat of my code:

private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
    {

        //Create new SQL connection and adapter using Windows Authentication
        SqlConnection myConnection = new SqlConnection("Data Source=database; Initial Catalog=Dashboard; Integrated Security=SSPI; Persist Security Info=false; Trusted_Connection=Yes");
        SqlDataAdapter da = new SqlDataAdapter();

        try
        {
            myConnection.Open();

            da.InsertCommand = new SqlCommand("INSERT INTO DashboardLibAnswer(Id,Date,Time,Question,Details,Answer,Notes,EnteredBy,WhereReceived,QuestionType,AnswerMethod,TransactionDuration)"
                + "VALUES(@Id,@Date,@Time,@Question,@Details,@Answer,@Notes,@EnteredBy,@WhereReceived,@QuestionType,@AnswerMethod,@TransactionDuration)", myConnection);

            da.InsertCommand.Parameters.Add("@Id", SqlDbType.NVarChar);
            da.InsertCommand.Parameters.Add("@Date", SqlDbType.Text);
            da.InsertCommand.Parameters.Add("@Time", SqlDbType.Text);
            da.InsertCommand.Parameters.Add("@Question", SqlDbType.Text);
            da.InsertCommand.Parameters.Add("@Details", SqlDbType.Text);
            da.InsertCommand.Parameters.Add("@Answer", SqlDbType.Text);
            da.InsertCommand.Parameters.Add("@Notes", SqlDbType.Text);
            da.InsertCommand.Parameters.Add("@EnteredBy", SqlDbType.NVarChar);
            da.InsertCommand.Parameters.Add("@WhereReceived", SqlDbType.NVarChar);
            da.InsertCommand.Parameters.Add("@QuestionType", SqlDbType.NVarChar);
            da.InsertCommand.Parameters.Add("@AnswerMethod", SqlDbType.NVarChar);
            da.InsertCommand.Parameters.Add("@TransactionDuration", SqlDbType.NVarChar);

            //Using the global variable counter this loop will go through each valid entry and insert it into the specifed database/table
            for (int i = 0; i < counter; i++)
            {
                //This is where I try to do the comparision. 
                //idS is the secondary array with all the current primary keys in the Table
                //collection is the primary array that stores new data from the .CSV file
                if (idS.ElementAt(i) != collection.getIdItems(i))
                {
                    da.InsertCommand.Parameters["@Id"].Value = collection.getIdItems(i);
                    da.InsertCommand.Parameters["@Date"].Value = collection.getDateItems(i);
                    da.InsertCommand.Parameters["@Time"].Value = collection.getTimeItems(i);
                    da.InsertCommand.Parameters["@Question"].Value = collection.getQuestionItems(i);
                    da.InsertCommand.Parameters["@Details"].Value = collection.getDetailsItems(i);
                    da.InsertCommand.Parameters["@Answer"].Value = collection.getAnswerItems(i);
                    da.InsertCommand.Parameters["@Notes"].Value = collection.getNotesItems(i);
                    da.InsertCommand.Parameters["@EnteredBy"].Value = collection.getEnteredByItems(i);
                    da.InsertCommand.Parameters["@WhereReceived"].Value = collection.getWhereItems(i);
                    da.InsertCommand.Parameters["@QuestionType"].Value = collection.getQuestionTypeItems(i);
                    da.InsertCommand.Parameters["@AnswerMethod"].Value = collection.getAnswerMethodItems(i);
                    da.InsertCommand.Parameters["@TransactionDuration"].Value = collection.getTransactionItems(i);
                    da.InsertCommand.ExecuteNonQuery();
                }


                //Updates the progress bar using the i in addition to 1 
                _worker.ReportProgress(i + 1);

            } // end for

            //Once the importing is done it will show the appropriate message
            MessageBox.Show("Finished Importing");

        } // end try
        catch (Exception exceptionError)
        {
            //To show exceptions thrown just uncomment bellow line
            //rtbOutput.AppendText(exceptionError.ToString);

        } // end catch

        //Closes the SQL connection after importing is done
        myConnection.Close();

    } // end backgroundWorker1_DoWork

Right now the program executes but no new data is being inserted. It seems that program does not get out of the for loop because if it runs successfully it shows the "Finished Importing" message box.

What's counter set to? And how do you know the id's are in the same position as the rows in the collection? Also, I suspect an exception is getting thrown and the empty catch is preventing you from seeing anything. You should use the debugger to validate that. — Jeff Siver, Feb 20 '13 at 15:30
counter is a global variable that I use to keep count of the collection array / size. — Nick, Feb 20 '13 at 15:39

score 1 · Accepted Answer · edited May 23 '17 at 11:49

The line

if (idS.ElementAt(i) != collection.getIdItems(i))

looks odd. Depending on what the getIdItems return, it looks to me as if you will match the first time the two lists are out of synch - so if items don't appear in the exact same order as you have already persisted them, you may get a false positive. Ie. the code will evaluate that the item doesn't exist and should be persisted, even if it exists at some other place in your collection.

Then, if you have a unique constraint the insert will fail, send you to the exception handler that is commented out - and then exit.

The first thing I would do would be to activate the exception handler to know what was going on. Second, I would probably change the code above so that you lookup, rather than iterate through, your idS collection. Ie. if you make idS a list or you include the Linq extension methods, you could do:

if (!idS.Contains(collection.getIdItems(i))

EDIT: Do note that for large idS, this is not particularly efficient and doesn't scale very well as your list grows. If you expect to handle a large number of idS, you might want to change the list to be a hash-collection or use the solution suggested here Check if list<t> contains any of another list.

When I enable exception it returns this: "Cross-thread operation not valid: Control 'rtbOutput' accessed from a thread other than the thread it was created on." Also what do you mean by lookup? — Nick, Feb 20 '13 at 15:37
As for the exception returned, take a look at http://stackoverflow.com/a/5037487/806549 — , Feb 20 '13 at 15:54
That seemed to do the trick. I'll have to examine it more to make sure it working fully and I'll let you know. Thanks! — Nick, Feb 20 '13 at 16:04

Inserting only new data to SQL Table

1 Answers1