Click here to Skip to main content
14,695,431 members
Please Sign up or sign in to vote.
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

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
Updated 20-Aug-20 5:21am
Comments
Gerry Schmitz 16-Aug-20 22:11pm
   
You need "numbers". Anything else is like buying pants (online) without knowing your waist size.

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' ....
   
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 ?
Here is the answer I got.
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
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900