如何使用控制台应用将Web API数据存储至SQL Server数据库？

1. 定义与API返回匹配的Person实体类 #

先创建和XML结构对应的实体类，用于反序列化API返回的数据：

using System.Xml.Serialization;

[XmlRoot(ElementName = "Person", Namespace = "http://schemas.datacontract.org/2004/07/API.Models")]
public class Person
{
    [XmlElement(ElementName = "ID")]
    public int ID { get; set; }

    [XmlElement(ElementName = "PersonID")]
    public int PersonID { get; set; }

    [XmlElement(ElementName = "Mobile")]
    public string Mobile { get; set; }

    [XmlElement(ElementName = "Name")]
    public string Name { get; set; }

    [XmlElement(ElementName = "CreateDate")]
    public DateTime CreateDate { get; set; }

    [XmlElement(ElementName = "ModifyDate")]
    public DateTime ModifyDate { get; set; }
}

[XmlRoot(ElementName = "ArrayOfPerson", Namespace = "http://schemas.datacontract.org/2004/07/API.Models")]
public class ArrayOfPerson
{
    [XmlElement(ElementName = "Person", Namespace = "http://schemas.datacontract.org/2004/07/API.Models")]
    public List<Person> Persons { get; set; }
}

2. 解析API返回的XML数据 #

替换原代码中读取字符串的逻辑，用XmlSerializer直接反序列化数据流，高效且避免手动解析错误：

// 在成功响应分支替换原ReadAsStringAsync逻辑
var streamTask = result.Content.ReadAsStreamAsync();
streamTask.Wait();

var serializer = new XmlSerializer(typeof(ArrayOfPerson));
var personList = (ArrayOfPerson)serializer.Deserialize(streamTask.Result);

// 示例：提取第一条数据的ID
Console.WriteLine("第一条Person的ID：" + personList.Persons.FirstOrDefault()?.ID);

3. 批量插入SQL Server数据库 #

针对1万+数据量，用SqlBulkCopy批量插入效率远高于逐条插入。先在SQL Server创建对应表：

CREATE TABLE Persons (
    ID INT PRIMARY KEY,
    PersonID INT,
    Mobile NVARCHAR(50),
    Name NVARCHAR(100),
    CreateDate DATETIME,
    ModifyDate DATETIME
)

编写批量插入代码：

private static void BulkInsertPersons(List<Person> persons)
{
    // 替换为你的SQL Server连接字符串
    string connectionString = "Server=YOUR_SERVER;Database=YOUR_DB;User Id=YOUR_USER;Password=YOUR_PWD;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
        {
            // 映射实体属性与数据库列名（名称一致可省略，显式映射更安全）
            bulkCopy.ColumnMappings.Add("ID", "ID");
            bulkCopy.ColumnMappings.Add("PersonID", "PersonID");
            bulkCopy.ColumnMappings.Add("Mobile", "Mobile");
            bulkCopy.ColumnMappings.Add("Name", "Name");
            bulkCopy.ColumnMappings.Add("CreateDate", "CreateDate");
            bulkCopy.ColumnMappings.Add("ModifyDate", "ModifyDate");

            bulkCopy.DestinationTableName = "Persons";

            // 将List转换为DataTable
            DataTable dt = ConvertToDataTable(persons);
            bulkCopy.WriteToServer(dt);
        }
    }
}

// 辅助方法：List转DataTable
private static DataTable ConvertToDataTable(List<Person> persons)
{
    DataTable dt = new DataTable();
    dt.Columns.Add("ID", typeof(int));
    dt.Columns.Add("PersonID", typeof(int));
    dt.Columns.Add("Mobile", typeof(string));
    dt.Columns.Add("Name", typeof(string));
    dt.Columns.Add("CreateDate", typeof(DateTime));
    dt.Columns.Add("ModifyDate", typeof(DateTime));

    foreach (var person in persons)
    {
        dt.Rows.Add(person.ID, person.PersonID, person.Mobile, person.Name, person.CreateDate, person.ModifyDate);
    }

    return dt;
}

4. 完整控制台代码整合 #

把所有逻辑整合为完整代码：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Xml.Serialization;

namespace ApiToSqlConsole
{
    [XmlRoot(ElementName = "Person", Namespace = "http://schemas.datacontract.org/2004/07/API.Models")]
    public class Person
    {
        [XmlElement(ElementName = "ID")]
        public int ID { get; set; }

        [XmlElement(ElementName = "PersonID")]
        public int PersonID { get; set; }

        [XmlElement(ElementName = "Mobile")]
        public string Mobile { get; set; }

        [XmlElement(ElementName = "Name")]
        public string Name { get; set; }

        [XmlElement(ElementName = "CreateDate")]
        public DateTime CreateDate { get; set; }

        [XmlElement(ElementName = "ModifyDate")]
        public DateTime ModifyDate { get; set; }
    }

    [XmlRoot(ElementName = "ArrayOfPerson", Namespace = "http://schemas.datacontract.org/2004/07/API.Models")]
    public class ArrayOfPerson
    {
        [XmlElement(ElementName = "Person", Namespace = "http://schemas.datacontract.org/2004/07/API.Models")]
        public List<Person> Persons { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            HttpClient client = new HttpClient();

            Console.WriteLine("Calling Api...");

            client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/xml"));

            var responseTask = client.GetAsync("http://localhost/api/api/Person");
            responseTask.Wait();

            if (responseTask.IsCompleted)
            {
                var result = responseTask.Result;
                if (result.IsSuccessStatusCode)
                {
                    var streamTask = result.Content.ReadAsStreamAsync();
                    streamTask.Wait();

                    var serializer = new XmlSerializer(typeof(ArrayOfPerson));
                    var personList = (ArrayOfPerson)serializer.Deserialize(streamTask.Result);

                    Console.WriteLine($"成功获取到{personList.Persons.Count}条数据");

                    // 批量插入数据库
                    BulkInsertPersons(personList.Persons);
                    Console.WriteLine("数据已成功插入SQL Server");
                }
                else
                {
                    Console.WriteLine($"API请求失败，状态码：{result.StatusCode}");
                }
            }

            Console.WriteLine("操作完成");
            Console.ReadLine();
        }

        private static void BulkInsertPersons(List<Person> persons)
        {
            string connectionString = "Server=YOUR_SERVER;Database=YOUR_DB;User Id=YOUR_USER;Password=YOUR_PWD;";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                {
                    bulkCopy.ColumnMappings.Add("ID", "ID");
                    bulkCopy.ColumnMappings.Add("PersonID", "PersonID");
                    bulkCopy.ColumnMappings.Add("Mobile", "Mobile");
                    bulkCopy.ColumnMappings.Add("Name", "Name");
                    bulkCopy.ColumnMappings.Add("CreateDate", "CreateDate");
                    bulkCopy.ColumnMappings.Add("ModifyDate", "ModifyDate");

                    bulkCopy.DestinationTableName = "Persons";

                    DataTable dt = ConvertToDataTable(persons);
                    bulkCopy.WriteToServer(dt);
                }
            }
        }

        private static DataTable ConvertToDataTable(List<Person> persons)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("PersonID", typeof(int));
            dt.Columns.Add("Mobile", typeof(string));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("CreateDate", typeof(DateTime));
            dt.Columns.Add("ModifyDate", typeof(DateTime));

            foreach (var person in persons)
            {
                dt.Rows.Add(person.ID, person.PersonID, person.Mobile, person.Name, person.CreateDate, person.ModifyDate);
            }

            return dt;
        }
    }
}

5. 关键注意事项 #

• 连接字符串替换：务必将代码中的connectionString替换为你的SQL Server实际连接信息
• XML命名空间：实体类的命名空间必须和API返回的XML命名空间完全一致，否则反序列化失败
• 大数据量处理：若API数据超出内存上限，可请求API分页接口，分批次插入数据库
• 异常处理：生产环境需添加try-catch块处理网络、数据库等异常
• 依赖引用：确保项目引用System.Data.SqlClient（.NET Framework）或Microsoft.Data.SqlClient（.NET Core/.NET 5+）包

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