How to refresh a C# DataTable with updated data due to adding to table with autoincrement?

Question

How do you refresh a datatable with the newly updated data after you have pushed the data to the database?

I have an auto incrementing table that is being populated by a datatable/sqlbulkcopy combination. I need the key information that is generated in the autoincrement down the line, and instead of building out a massive SQL statement, to requery the database, I'm convinced there has to be a better way to do it.

The solution is has to be extremely quick. I'm working with hundreds of thousands of records that need to be processed in a timely manner.

Building the DataTable

    public void SetupTable()
    {
        m_Table = new DataTable("Table");
        m_Table.Columns.Add(new DataColumn("TagID", typeof(int)));
        m_Table.Columns.Add(new DataColumn("ResultID", typeof(int)));
        m_Table.Columns.Add(new DataColumn("TypeID", typeof(int)));
        m_Table.Columns.Add(new DataColumn("Name", typeof(string)));
        m_Table.Columns.Add(new DataColumn("bDel", typeof(int)));
        m_Table.Columns.Add(new DataColumn("Notes", typeof(string)));
        m_Table.Columns.Add(new DataColumn("Units", typeof(string)));
    }

Populating the DataTable

 m_Table.Rows.Add(m_Table.Rows.Count + 1, resultid, tagtypeid, SqlStr(tagname), (isDel ? "1" : "0"), "", "");

Pushing to the Database

    public bool AddProcessedTable()
    {
        bool bSuccess = true;
        try
        {
            if (sqlConn.State != ConnectionState.Open) sqlConn.Open();

            SqlBulkCopy SqlCmd = new SqlBulkCopy(sqlConn);
            SqlCmd.BulkCopyTimeout = 600;
            SqlCmd.DestinationTableName = "Table";
            SqlCmd.WriteToServer(m_Table);
        }
        catch (Exception ex)
        {
            bSuccess = false;
            plog.LogMsg(String.Format("General Exception during DestWarehouse.AddProcessedTable(): {0}", ex.Message));
        }
        return bSuccess;

    }

Refreshing the DataTable

Unsure what to attempt here. I have investigating using Linq to rebuild a SQL statement based upon the data in the DataTable.
I have investigate creating a DataTableReader from the DataTable and then using Load to reload the information. However I did not see any connection information in the examples and seemed incorrect.

I rarely post here to SO - so if I need to correct this post and add more details I definitely can but any help is extremely appreciated! Thanks.

I think you want the OUTPUT clause [ https://msdn.microsoft.com/en-us/library/ms177564.aspx ] as in http://stackoverflow.com/questions/810962/getting-new-ids-after-insert — Chris F Carroll, Mar 23 '15 at 22:06

score 0 · Answer 1 · edited Mar 23 '15 at 22:37

0

Why don't you throw a new SELECT to the database, storing the result in a DataReader and then using Load, refresh the DataTable.

DataTable dt = //Your table
//Here a select to the database
DataReader dr = //Your select query
dt.Load(dr)

somethinbg like that. But you know, using the using statements to close datareaders, etc...

edited Mar 23 '15 at 22:37

mason

31,774
10
77
121

answered Mar 23 '15 at 22:35

Oscar Bralo

1,912
13
12

That was my original thought - I was just hoping for something native within the .NET Framework that would be faster than that. I'm thinking about adding a "SessionID" column, where when I insert, I generate a GUID and store it to the column. Then just select back based off of that GUID and then drop the column once I finish DB operations. – MTAG11 Mar 24 '15 at 13:05

score 0 · Answer 2 · answered Mar 24 '15 at 13:14

0

Or only adding and ID column, good indexes and then store the last Id and select the ids based on the last one used.

answered Mar 24 '15 at 13:14

Oscar Bralo

1,912
13
12

How to refresh a C# DataTable with updated data due to adding to table with autoincrement?

2 Answers2