CODEWITHSUNDEEP
c#sql-serverdatatablemergeado.net
Reeggiie
Reeggiie

Reputation: 822

DataAdapter Update not properly Inserting data into SQL Server?

I have 2 DataTables: 1 that is filled with all the data from a table in my database called Ticket_Report, and a second one that is filled with new tickets.

I am searching the Ticket_report datatable for duplicates from the new ticket DataTable (and deleting them). Then I merge the new tickets datatable into the Ticket_report datatable.

I then try and Update the Ticket_Report table in SQL Server with the newly edited Ticket_report datatable. I am expecting the Update method to Update the table in SQL Server with rows that have matching primary keys from the datatable, and Insert rows that don't exist.

I am not getting any errors, however it doesn't seem like the rows are being updated/inserted.

con.Open();
DataTable ticketReportDT = new DataTable();
ticketReportDT = newTicketdt.Clone();
ticketReportDT.Columns[0].Unique = true;

SqlCommand cmd2 = new SqlCommand("SELECT * FROM ticket_report", con);
SqlDataAdapter da = new SqlDataAdapter(cmd2);

da.Fill(ticketReportDT);
da.UpdateCommand = new SqlCommandBuilder(da).GetUpdateCommand();

int currentTicketCount = ticketReportDT.Rows.Count;//3234
int newTicketCount = newTicketdt.Rows.Count; //339

//Removing Duplicate tickets in ticketReportDt
foreach (DataRow NewTicketsRow in newTicketdt.Rows)
{
   foreach (DataRow currentTicketsRow in ticketReportDT.Select())
    {
        if (currentTicketsRow.Field<string>(ticketReportDT.Columns[0]) == NewTicketsRow.Field<string>(newTicketdt.Columns[0]))
        {
            currentTicketsRow.Delete();
        }
    }
}

ticketReportDT.AcceptChanges();
int currentTicketCount2 = ticketReportDT.Rows.Count;//2903, is less than last count because duplicates removed

ticketReportDT.Merge(newTicketdt);//add new tickets to ticketReportDT
ticketReportDT.AcceptChanges();

int currentTicketCount3 = ticketReportDT.Rows.Count;//3242, count went up because new tickets added

da.Update(ticketReportDT);//apply changes to database table Ticket_Report
con.Close();
Console.WriteLine("Ticket Transfer Complete");

This runs without error. To test, I did a count query in SQL Server before and after this update.

Before update: 

select count(*) from TICKET_REPORT
// = 3234

After Update:

select count(*) from TICKET_REPORT
// = 3234 <-Should be 3242 (row count of ticketReportDT)

It seems like the Datatable has the correct amount of rows, but when I do the update, this does not reflect in SQL Server. Am I doing something wrong?

Upvotes: 2

Views: 1252

Answers (1)

Faisal
Faisal

Reputation: 1397

Since you have called ticketReportDT.AcceptChanges() before updating database it is not going to update anything in the database.

Upvotes: 2

Related Questions