Insert and Update existing DataTable records in Database using C# and VB.Net in ASP.Net

Last Reply 8 months ago By dharmendr

Posted 8 months ago

Hello All,

I have a web socket with incoming data. I am storing all this data into a DataTable. How often should I save this DataTable to a database?

I would like to save this data to a database without getting duplicate records.

I will shut this web socket off once in a while and come back to start it later and I would like to continue adding data to the database.

Should I save the data row by row with a sql query or use a stored procedure or should I save the data with SqlBulkCopy.

I am currently using SqlBulkCopy. I also do not want to have duplicate data, currently I save the DataTable to the database and I keep saving it and it works perfectly, BUT when I stop the web socket and turn it back on and then go to save the Data It resaves ALL the data in the DataTable so I get duplicate data.

        public void SaveToDBTableV2()
        {
            using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.Connection))
            {
                connection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                {
                    bulkCopy.DestinationTableName = "dbo.EmailTest";
 
                    try
                    {
                        SqlBulkCopyColumnMapping TradeID = new SqlBulkCopyColumnMapping("TradeID", "TradeID");
                        bulkCopy.ColumnMappings.Add(TradeID);
                        SqlBulkCopyColumnMapping Price = new SqlBulkCopyColumnMapping("Price", "Price");
                        bulkCopy.ColumnMappings.Add(Price);
                        SqlBulkCopyColumnMapping Quantity = new SqlBulkCopyColumnMapping("Quantity", "Quantity");
                        bulkCopy.ColumnMappings.Add(Quantity);
                        SqlBulkCopyColumnMapping TradeTime = new SqlBulkCopyColumnMapping("TradeTime", "TradeTime");
                        bulkCopy.ColumnMappings.Add(TradeTime);
                        SqlBulkCopyColumnMapping Symbol = new SqlBulkCopyColumnMapping("Symbol", "Symbol");
                        bulkCopy.ColumnMappings.Add(Symbol);
                        SqlBulkCopyColumnMapping BuyerID = new SqlBulkCopyColumnMapping("BuyerID", "BuyerID");
                        bulkCopy.ColumnMappings.Add(BuyerID);
                        SqlBulkCopyColumnMapping SellerID = new SqlBulkCopyColumnMapping("SellerID", "SellerID");
                        bulkCopy.ColumnMappings.Add(SellerID);
                        bulkCopy.WriteToServer(DataTable, DataRowState.Added);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
            }
        }

 

Posted 8 months ago

Hi bigbear,

In order to insert update DataTable records you have to either loop through all the rows to add (update if record exists) each record through Sql Query or use SqlBulkCopy class.

If you use SqlBulkCopy class then for checking record exist or not you have to create User Defined Table Type and then create an Update Stored Procedure which will check if record exists then update else insert.

Refer below article to use SqlBulkCopy.

SqlBulkCopy- Bulk Insert records and Update existing rows if record exists using C# and VB.Net