基于TCP协议的C#时序数据采集并写入SQL数据库实现咨询

Hey there! Let's walk through how to build this C# application step by step—this is a super common use case for time-series data ingestion, so you’ve got a clear path forward.

Since you’re prioritizing TCP, first nail down the communication layer. The key here is defining which side acts as the server vs. client:

• If the VM’s executable is sending data actively, your C# app should run a TCP listener to accept incoming connections. If you need to pull data from the VM, your app will act as a TCP client connecting to the VM’s listener.
• Use C# built-in System.Net.Sockets namespace—TcpClient (for client-side) and TcpListener (for server-side) are your go-to tools.
  • Example client-side code to connect and read data from the VM:

    using System.Net.Sockets;
    using System.Text;
    
    async Task FetchDataFromVM()
    {
        var vmIp = "192.168.x.x"; // Replace with your VM's local IP
        var port = 5000; // Match the port your VM executable uses
    
        using var client = new TcpClient();
        try
        {
            await client.ConnectAsync(vmIp, port);
            using var stream = client.GetStream();
            var buffer = new byte[1024];
            int bytesRead = await stream.ReadAsync(buffer, 0, buffer.Length);
            
            // Convert raw bytes to usable data (adjust encoding/format as needed)
            string rawData = Encoding.UTF8.GetString(buffer, 0, bytesRead);
            var parsedData = ParseRawData(rawData); // Your custom parsing logic
            // Add parsed data to a queue for later DB write
            _dataQueue.Enqueue(parsedData);
        }
        catch (SocketException ex)
        {
            // Handle connection errors—add retries here (e.g., with Polly library or custom loops)
            Console.WriteLine($"Failed to connect to VM: {ex.Message}");
        }
    }
    

• Critical detail: Agree on a data serialization format with the VM executable. Protobuf is great for performance (ideal for time-series), while JSON is easier to debug. Use Google.Protobuf or System.Text.Json in C# to serialize/deserialize.

Turn raw TCP data into a strongly-typed C# object to simplify database operations:

• Define a model that matches your time-series data:

  public class TimeSeriesRecord
    {
        public DateTime Timestamp { get; set; } // Use UTC to avoid timezone issues
        public double MetricValue { get; set; }
        public string MetricId { get; set; } // e.g., CPU usage, memory usage
        public string VmIdentifier { get; set; } // To track which VM the data comes from
    }
  

• Write a parsing method (ParseRawData in the example above) to convert the raw TCP string/bytes into this model. Validate the data here to avoid writing bad records to the database.

To avoid overwhelming your database with frequent small writes, use a batch approach:

• Cache data in memory: Use a thread-safe collection like ConcurrentQueue<TimeSeriesRecord> to store incoming data between writes.
• Schedule batch writes: Use a timer or background service to trigger writes every minute. For long-running apps, BackgroundService (from Microsoft.Extensions.Hosting) is more reliable than a basic Timer.
  • Example batch write using SqlBulkCopy (far faster than individual inserts):

    private readonly ConcurrentQueue<TimeSeriesRecord> _dataQueue = new();
    private const string ConnectionString = "Your_SQL_Connection_String";
    
    async Task WriteBatchToSql()
    {
        var recordsToWrite = new List<TimeSeriesRecord>();
        // Dequeue all stored records
        while (_dataQueue.TryDequeue(out var record))
        {
            recordsToWrite.Add(record);
        }
    
        if (!recordsToWrite.Any()) return;
    
        using var connection = new SqlConnection(ConnectionString);
        await connection.OpenAsync();
    
        using var bulkCopy = new SqlBulkCopy(connection);
        bulkCopy.DestinationTableName = "TimeSeriesData"; // Match your SQL table name
        // Map model properties to SQL columns
        bulkCopy.ColumnMappings.Add(nameof(TimeSeriesRecord.Timestamp), "Timestamp");
        bulkCopy.ColumnMappings.Add(nameof(TimeSeriesRecord.MetricValue), "MetricValue");
        bulkCopy.ColumnMappings.Add(nameof(TimeSeriesRecord.MetricId), "MetricId");
        bulkCopy.ColumnMappings.Add(nameof(TimeSeriesRecord.VmIdentifier), "VmIdentifier");
    
        // Convert list to a DataReader (use DataTable if preferred)
        await bulkCopy.WriteToServerAsync(recordsToWrite.AsDataReader());
    }
    

• SQL Table Optimization: Create a clustered index on the Timestamp column to speed up querying time-series data. If using SQL Server, consider memory-optimized tables for higher throughput.

• Logging: Add logging with Serilog or NLog to track connection issues, failed writes, and data parsing errors—critical for debugging later.
• Configuration: Store VM IP, port, connection string, and interval in appsettings.json (instead of hardcoding) so you can adjust settings without recompiling.
• Retry Logic: Add retries for TCP connection failures and database writes (Polly is a great library for this, but you can also write simple loops with delays).
• Health Checks: Add basic health checks to monitor if the TCP connection is active and if database writes are succeeding.

• C# TCP Basics: Microsoft’s official documentation for TcpClient and TcpListener includes practical examples for both client and server scenarios.
• Batch SQL Inserts: The official SqlBulkCopy docs cover all scenarios for bulk data insertion, including column mapping and error handling.
• Background Services: If building a long-running app, check out the BackgroundService documentation for .NET Core/.NET 5+—it’s designed for exactly this kind of persistent task.
• Serialization: For JSON, use System.Text.Json docs; for high-performance Protobuf, refer to the Google.Protobuf official guides.

内容的提问来源于stack exchange，提问作者Alireza Bani