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);
                    }
                }
            }
        }