Help with updating database

[apgriffiths]

I have some code that adds records to a database but it is quite common for there to be a lot of duplicate records. I am trying to write some code that will remove the duplicate rows in the database table. I am using table adapters for this task, i have the following code written that produces a table without the duplicate rows but i do not know how to update the database table with the results,

AddDetailsTableAdapter detailsAdapter = new AddDetailsTableAdapter();
                DataTable dTable = new DataTable();
                dTable = detailsAdapter.GetData();

                DataView dv = new DataView(dTable);
                string[] strColumns = { "DepotNo", "Route", "Add1", "Add2", "Add3",
                    "Add4", "Add5", "Postcode" };
                dTable = dv.ToTable(true, strColumns);

                detailsAdapter.Update(dTable);

The line 'detailsAdapter.Update(dTable)' will not compile.

How do i update the database table with the dTable result??

Any help is much appreciated.

Thanks

[ndeniche]

that is because detailsAdapter is not a DataTable, its a TableAdapter. What you should do is, instead of reading a DataTableAdapter, reading a DataRow and run a nested for loop within another for loop, gather the information from the outer's for loop index row and compare it to the rest of the table. That way you remove the duplicates in the table, and you can update the database using a sql query. What database areyou using?

the nested for loops would go something like this:

DataRow row;
DataTable table;
int rowcount;
table = yourDataBinding.yourTable;
rowcount = yourDataBinding.yourTable.Rows.Count;
for (int i=0;i<rowcount;i++)
{
    row = yourDataBinding.yourTable.Row[i];
    for (int j=0;j<rowcount;j++)
    {
        if(row == yourDataBinding.yourTable.Row[j])
        {
            table.RemoveAt(j);
            rowcount--;
        }
    }
}

[apgriffiths]

The database is a sql server .mdf file that i have added to the project as a datasource.

What does 'yourDataBinding' represent?

[ndeniche]

my bad... it's your DataSet... so it represents the DataSet created by c# when you add a datasource

[ndeniche]

so, after reviewing some notest on SQL connections for c#, here's the final code...

string conStr, cmdStr;
conStr = "--Copy Connection String Here--"
System.Data.OleDB.OleDBConnection con = new System.Data.OleDB.OleDBConnection(conStr);
cmdStr = "Delete YourTable Where ID_Primary_Key = ?"
DataRow row;
int rowcount, registryID;
rowcount = YourDatabaseDataSet.YourTableName.Rows.Count;
for (int i=0;i<rowcount;i++)
{
    row = YourDatabaseDataSet.YourTableName.Rows[i];
    for (int j=0;j<rowcount;j++)
    {
        if(row == YourDatabaseDataSet.YourTableName.Rows[j])
        {
            registryID = Int32.Parse(YourDatabaseDataSet.YourTableName.Rows[j].ItemArray[0].ToString());
            using (System.Data.OleDB.OleDBCommand cmd = new System.Data.OleDB.OleDBCommand(cmdSTR, con))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("ID_Primary_Key",registryID);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
            rowcount--;
        }
    }
}

[ndeniche]

so, after reviewing some notest on SQL connections for c#, here's the final code...

string conStr, cmdStr;
conStr = "--Copy Connection String Here--"
System.Data.OleDB.OleDBConnection con = new System.Data.OleDB.OleDBConnection(conStr);
cmdStr = "Delete YourTable Where ID_Primary_Key = ?"
DataRow row;
int rowcount, registryID;
rowcount = YourDatabaseDataSet.YourTableName.Rows.Count;
for (int i=0;i<rowcount;i++)
{
    row = YourDatabaseDataSet.YourTableName.Rows[i];
    for (int j=0;j<rowcount;j++)
    {
        if(row == YourDatabaseDataSet.YourTableName.Rows[j])
        {
            registryID = Int32.Parse(YourDatabaseDataSet.YourTableName.Rows[j].ItemArray[0].ToString());
            using (System.Data.OleDB.OleDBCommand cmd = new System.Data.OleDB.OleDBCommand(cmdSTR, con))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("ID_Primary_Key",registryID);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
            rowcount--;
        }
   }
}