如何用DataTable和DataAdapter在SQL Server存储序列化对象至XML/字符串列？

你遇到的异常本质是：.NET数据提供程序无法识别自定义对象类型，无法直接映射到SQL Server的原生类型（XML/字符串）。可以通过监听DataTable或DataAdapter的事件，在数据进出数据库时自动完成序列化/反序列化，无需手动在业务层重复转换。

核心思路 #

数据库列用NVARCHAR(MAX)（存序列化后的JSON/XML字符串）或XML类型，DataTable中对应列设为string或XmlDocument类型，通过事件拦截数据读写动作，自动转换自定义对象与数据库兼容类型。

具体实现方案 #

1. 准备序列化工具类 #

根据需求选择JSON或XML序列化，以下以JSON为例（使用System.Text.Json）：

public static class ObjectSerializer
{
    // 序列化自定义对象为JSON字符串
    public static string Serialize(object obj)
    {
        return JsonSerializer.Serialize(obj);
    }

    // 反序列化JSON字符串为指定类型对象
    public static T Deserialize<T>(string json)
    {
        return string.IsNullOrEmpty(json) ? default : JsonSerializer.Deserialize<T>(json);
    }
}

2. 通过DataTable事件自动转换 #

这种方式最直观，业务层可以直接给DataRow赋值自定义对象，读取时直接获取对象：

// 初始化DataTable，对应数据库表结构
DataTable dt = new DataTable("MyTable");
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("CustomData", typeof(string)); // 对应数据库的NVARCHAR(MAX)列

// 写入时：将自定义对象序列化为字符串
dt.ColumnChanging += (sender, e) =>
{
    if (e.Column.ColumnName == "CustomData" && e.ProposedValue is MyCustomObject customObj)
    {
        e.ProposedValue = ObjectSerializer.Serialize(customObj);
    }
};

// 读取时：将字符串反序列化为自定义对象
dt.ColumnChanged += (sender, e) =>
{
    if (e.Column.ColumnName == "CustomData" && e.Row[e.Column] is string json)
    {
        e.Row[e.Column] = ObjectSerializer.Deserialize<MyCustomObject>(json);
    }
};

3. 通过DataAdapter事件处理 #

如果更倾向于在DataAdapter层面控制转换逻辑，可以用以下方式：

// 初始化SqlDataAdapter
SqlCommand selectCmd = new SqlCommand("SELECT Id, CustomData FROM MyTable", connection);
SqlDataAdapter da = new SqlDataAdapter(selectCmd);

// 配置更新命令（示例）
SqlCommand updateCmd = new SqlCommand("UPDATE MyTable SET CustomData = @CustomData WHERE Id = @Id", connection);
updateCmd.Parameters.Add("@CustomData", SqlDbType.NVarChar, -1, "CustomData");
updateCmd.Parameters.Add("@Id", SqlDbType.Int, 4, "Id");
da.UpdateCommand = updateCmd;

// 更新数据库前：序列化自定义对象
da.RowUpdating += (sender, e) =>
{
    if (e.Row["CustomData"] is MyCustomObject customObj)
    {
        e.Command.Parameters["@CustomData"].Value = ObjectSerializer.Serialize(customObj);
    }
};

// 读取数据后：反序列化为自定义对象
da.FillCompleted += (sender, e) =>
{
    foreach (DataRow row in e.Result.Rows)
    {
        if (row["CustomData"] is string json)
        {
            row["CustomData"] = ObjectSerializer.Deserialize<MyCustomObject>(json);
        }
    }
};

注意事项 #

• 自定义对象需保证可序列化：JSON序列化对类型要求宽松，XML序列化需标记[Serializable]或提供无参构造函数
• 避免事件循环：转换时可增加类型判断（如仅当值为自定义对象时才序列化）
• 兼容性：若对象结构变更，需配置序列化选项（如JSON的JsonSerializerOptions.IgnoreUnknownProperties）避免反序列化失败
• 性能：序列化/反序列化有一定开销，大数据量场景需评估是否适用

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