DataTable to SQL CE 4 Server

Question

I have a DataTable in C# and would like to send it to my SQL CE 4 server. One thing that makes it a bit more complicated is that when it encounters an duplicate, it should either ignore it and move on to the next row in the DataTable. I've looked around but a lot of information I find doesn't seem to work with the CE version of SQL Server. What's an efficient way of doing this?

http://stackoverflow.com/questions/8566784/invalid-option-specified-in-create-index-statement . I then came across this library a couple hours ago http://sqlcebulkcopy.codeplex.com/ but it just throws an exception when it comes across a duplicate and there doesn't seem to be an override option. — Skoder, Dec 20 '11 at 01:50
why not simply remove the duplicate rows from the data table (ala The complicators gloves) before uploading? — Mitch Wheat, Dec 20 '11 at 01:52
To do that, would I convert the Table to a DataTable, then use Linq or something to detect which are not duplicates, add those to a new datatable and write that to the server? — Skoder, Dec 20 '11 at 01:54

score 1 · Accepted Answer · answered Dec 20 '11 at 01:58

Filter your DataTable to exclude the duplicate rows before uploading, using the DataTable.Select Method

e.g.

    DataTable table = DataSet1.Tables["Orders"];

    // Presuming the DataTable has a column named Date.
    string expression;
    expression = "Date > #1/1/00#"; // you will need logic to remove your duplicates
    DataRow[] foundRows;

    // Use the Select method to find all rows excluding duplicates
    foundRows = table.Select(expression);

    // .NET 3.5 onwards
    DataTable filteredDataTable = foundRows.copyToDataTable();

Thanks, I haven't come across DataTable.Select expressions. – Skoder Dec 20 '11 at 02:02 — Skoder, Dec 20 '11 at 02:02

score 0 · Answer 2 · answered Dec 20 '11 at 02:03

Try this Logic.

 var dt = new DataTable(); //Supposed that this is your DataTable

 foreach(DataRow row in dt.Rows)
    {

      var find = MyFindMethod("Id"); 1. select statement that find if the id is on database

       if(find.Rows > 0)
          {
            //Id exist do nothing
          }
       else
          {
             //Id not exist then 2. Do Insert to sql ce id I not exist
            MyInsertMethod("Id"); 
          }  
    }

Regards

DataTable to SQL CE 4 Server

2 Answers2