C# save 2 datatable then database then off/on it saves duplicate data

Question

0.00/5 (No votes)

See more:

I am saving incoming data to a DataTable. I would like to save this DataTable to a Database Table so I have all the data recorded. How often do you think I should record this data to the database?
Also 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

C#

private void BtnStartCapture_Click(object sender, RoutedEventArgs e)
        {
            if (!Running)
            {
                Running = true;
                var incomingData = BinanceSocketClient.SubscribeToTradeUpdatesAsync("BTCUSD", (data) =>
                {
                    _ = AddNewIncomingTradeAsync(data);
                    AddDataRow(data);
                    counter++;
                    if(counter >= 2 )
                    {
                        SaveToDBTableV2();
                        counter = 0;
                    }
                });
                IncomingStreamOn();
            }            
        }
public void AddDataRow(BinanceStreamTrade data)
        {
            if(DataTable != null)
            {
                DataRow row = DataTable.NewRow();
                row["TradeID"] = data.OrderId;
                row["Price"] = data.Price;
                row["Quantity"] = data.Quantity;
                row["TradeTime"] = data.TradeTime;
                row["Symbol"] = data.Symbol;
                row["BuyerID"] = data.BuyerOrderId;
                row["SellerID"] = data.SellerOrderId;
                DataTable.Rows.Add(row);
            }
        }
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);
                    }
                }
            }
        }

What I have tried:

I am currently using SqlBulkCopy what is the difference from saving it with a simple INSERT Query compared to this?
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 websocket 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. What would any of you recommend me doing for this task? What would you do to not get duplicate data.

Posted 16-Aug-20 15:52pm

TheBigBearNow

Updated 20-Aug-20 4:21am

Add a Solution

Comments

[no name] 16-Aug-20 22:11pm

You need "numbers". Anything else is like buying pants (online) without knowing your waist size.

2 solutions

Solution 1

As Gerry said, it depends on the numbers .. I note you dont have a 'stop data capture' or you haven't shown it here if you have such .. so, possibly, approach 1, take this

private void BtnStartCapture_Click(object sender, RoutedEventArgs e)
        {
            if (!Running)
            {
                Running = true;
                var incomingData = BinanceSocketClient.SubscribeToTradeUpdatesAsync("BTCUSD", (data) =>
                {
                    _ = AddNewIncomingTradeAsync(data);
                    AddDataRow(data);
                    counter++;
                    if(counter >= 2 )
                    {
                        SaveToDBTableV2();
                        counter = 0;
                    }
                });
                IncomingStreamOn();
            }            
        }

and remove the

SaveToDBTableV2();

(and possibly the counter logic) so that you create another button 'Stop Data Capture' and event for button clicked

private void BtnStopCapture_Click(object sender, RoutedEventArgs e)
{
  SaveToDBTableV2();
}

Also, not sure what this is doing

_ = AddNewIncomingTradeAsync(data);

so it might need 'moving' ....

Posted 16-Aug-20 16:55pm

Garth J Lancaster

Comments

TheBigBearNow 17-Aug-20 11:12am

My websockets are almost 'ALWAYS' running. I do have a stopcapture button but it doesnt get clicked often because I dont want it off. Its a trading bot that runs and captures all incoming data. I need to record all incoming data which are trades. I would like to record all the incoming data but i dont know how often i should save it to a table. and which method to use to save it. Addnewinconingtradeasync shows all the incoming data on a listview. my stopcapture just currently shuts off the websocket. Right now my code almost works. It saves all data to a database but when i click stopcapture then turn it back on it saves the whole datatable again creating duplicate rows. I would like to know is this a good way i have it setup?

Garth J Lancaster 18-Aug-20 6:53am

"I would like to know is this a good way i have it setup?" .. well, I would say no - you still want to decouple the trade receive from the processing (so you don't want to block your socket) .. it still depends on numbers and speed (of received trades) - I'd be looking at adding the received trade to (maybe) a shared datatable/data structure, maybe firing off an event to say data has arrived, but then processing that data in a second (and safe) thread - one could use an observable collection here as well ... do you also need robustness ? what happens if you 'drop' a trade ?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

TheBigBearNow · Accepted Answer · 2020-08-20T04:21:00

Here is the answer I got.

C#

public async Task SaveToDBTableV5()
        {
            using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.Connection))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand("dbo.INSERTEmailTestV2", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    cmd.Parameters.AddWithValue("@EmailTstType", DataTable);
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }
CREATE TYPE [dbo].[EmailTstType] AS TABLE(
	[ID] [int] NULL,
	[TradeID] [int] NULL,
	[Price] [decimal](18, 0) NULL,
	[Quantity] [decimal](18, 0) NULL,
	[TradeTime] [datetime] NULL,
	[Symbol] [nvarchar](150) NULL,
	[EventType] [nvarchar](150) NULL,
	[IsMaker] [int] NULL,
	[EventTime] [datetime] NULL,
	[BuyerID] [int] NULL,
	[SellerID] [int] NULL,
	[Filled] [bit] NULL,
	[Name] [nvarchar](100) NULL
 )
CREATE PROCEDURE [dbo].[INSERTEmailTestV2]
	@EmailTstType EmailTstType READONLY
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	MERGE INTO dbo.EmailTest e1
	USING @EmailTstType e2
	ON e1.TradeID = e2.TradeID
	WHEN NOT MATCHED THEN
	INSERT VALUES (e2.TradeID, e2.Price, e2.Quantity, e2.TradeTime, e2.Symbol, e2.EventType, 
				   e2.IsMaker, e2.EventTime, e2.BuyerID, e2.SellerID, e2.Filled, e2.Name);
END