求助：DataTable按9999条批量处理及循环调用RestSharp的逻辑问题

原代码核心问题 #

• 循环逻辑完全错误，未实现按指定批次（9999条）分割DataTable数据的功能
• 手动拼接JSON字符串易出现格式错误（比如末尾逗号处理）
• SQL语句直接拼接参数存在注入风险
• 资源管理不规范（重复创建SqlConnection，未合理释放）
• 仅处理了一次接口请求，未实现分批循环调用

修正后的完整代码 #

protected void run_Click1(object sender, ImageClickEventArgs e)
{
    var daytime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss", CultureInfo.GetCultureInfo("sv-SE"));
    var day = DateTime.Now.ToString("yyyyMMdd", CultureInfo.GetCultureInfo("sv-SE"));
    string[] siteIds = new string[] { "2", "3", "4" }; // 变量名优化，更贴合业务含义
    int batchSize = 9999; // 改为需求的9999条每批

    foreach (string siteId in siteIds)
    {
        DataTable dt1 = null;
        // 1. 执行参数化SQL查询获取数据
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConnection1"].ConnectionString))
        {
            // 参数化查询避免SQL注入
            string query = @"SELECT [number],[surname],[forename],[emailAddress],[taxIdentifier] 
                            FROM[Customer] WITH(NOLOCK) 
                            WHERE[CustomerID] IN(SELECT[CustomerID] FROM[Visit] WITH(NOLOCK) WHERE[GamingDate] Between convert(Date, DATEADD(DAY, -365, GETDATE())) AND convert(Date, getdate())) 
                            AND(EmailAddress IS NOT NULL) AND(ContactTypes & 1 = 1) 
                            AND(HomePropertyID = @SiteId) 
                            AND(LEN(TaxIdentifier) = 12)";

            using (SqlCommand comm = new SqlCommand(query, conn))
            {
                comm.Parameters.AddWithValue("@SiteId", siteId);
                conn.Open();
                dt1 = new DataTable();
                using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
                {
                    adapter.Fill(dt1);
                }
            }
        }

        if (dt1 == null || dt1.Rows.Count == 0)
            continue; // 无数据则跳过当前站点

        // 2. 初始化CSV文件（原ort变量未定义，用站点ID对应名称替换）
        string siteName = siteId switch
        {
            "2" => "Malmö",
            "3" => "Göteborg",
            "4" => "Stockholm",
            _ => "Unknown"
        };
        string newFileName = $"C:\\temp\\{siteName}{day}FromNeon_Cleaned.csv";
        string clientHeader = "number,\"surname\",\"forename\",\"emailAddress\",\"responseId\",\"responseTime\"" + Environment.NewLine;
        File.WriteAllText(newFileName, clientHeader, Encoding.Unicode);

        // 3. 计算总批次，执行分批处理
        int totalRows = dt1.Rows.Count;
        int totalBatches = (int)Math.Ceiling((double)totalRows / batchSize);

        for (int batchIndex = 0; batchIndex < totalBatches; batchIndex++)
        {
            // 计算当前批次的起始和结束索引
            int startRow = batchIndex * batchSize;
            int endRow = Math.Min(startRow + batchSize, totalRows);

            // 构建请求体对象（避免手动拼接JSON出错）
            var requestBody = new
            {
                requestId = "144x25",
                items = new List<object>()
            };

            // 遍历当前批次的行
            for (int rowIndex = startRow; rowIndex < endRow; rowIndex++)
            {
                DataRow dtRow = dt1.Rows[rowIndex];
                var number = dtRow["number"].ToString();
                var taxidentifier = dtRow["taxIdentifier"].ToString();

                if (Personnummer.Valid(taxidentifier))
                {
                    requestBody.items.Add(new
                    {
                        itemId = number,
                        subjectId = taxidentifier
                    });
                }
            }

            if (!requestBody.items.Any())
                continue; // 当前批次无有效数据，跳过接口调用

            // 4. 调用RestSharp接口
            var url = "https://marketing.spelpaus.se/api/marketing-subjectid/mw41yZpBFC";
            var client = new RestClient(url);
            var request = new RestRequest(Method.Post);
            request.AddHeader("authorization", "vGrpkoNEWHpMuQVrIKVGfsfx2l7SLbsSfQ7PbISjMyiK545ezHW5BzsfPgJq0HA6");
            request.AddHeader("Content-Type", "application/json");
            // 序列化对象为JSON，避免手动拼接的格式问题
            request.AddBody(requestBody, ContentType.Json);

            RestResponse response = client.Execute(request);

            // 5. 处理接口响应，写入CSV
            if (response.StatusCode == HttpStatusCode.OK)
            {
                var obj = JsonConvert.DeserializeObject<dynamic>(response.Content);
                var allowedItemIds = obj.allowedItemIds as IEnumerable<int>;
                var responseId = obj.responseId.ToString();

                if (allowedItemIds == null)
                    continue;

                // 设置主键方便查找匹配数据
                dt1.PrimaryKey = new DataColumn[] { dt1.Columns["number"] };

                foreach (int allowedItem in allowedItemIds)
                {
                    DataRow drw = dt1.Rows.Find(allowedItem);
                    if (drw == null)
                        continue;

                    string clientDetails = $"{drw["number"]},\"{drw["surname"]}\",\"{drw["forename"]}\",\"{drw["emailAddress"]}\",\"{responseId}\",\"{daytime}\"" + Environment.NewLine;
                    File.AppendAllText(newFileName, clientDetails, Encoding.Unicode);
                }
            }
        }
    }
}

关键修正点说明 #

• 分批逻辑：通过计算总批次，循环处理每一批的起始/结束索引，确保每批最多9999条
• JSON构建：用匿名对象构建请求体，再通过JsonConvert序列化，彻底避免手动拼接的格式错误
• SQL安全：改用参数化查询，消除SQL注入风险
• 资源管理：规范using语句，确保数据库连接等资源正确释放
• 异常防护：增加空数据判断，避免无数据时执行无效操作
• 命名优化：变量名更贴合业务含义，提升代码可读性

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