SQL批量插入（带强类型数据和验证）
使用强类型数据（包括验证）进行 SQL 批量复制。
引言
SQLBulkCopy 是一种非常有用的快速方法，可以在几秒钟内插入大量数据，但它只接受 DataTable 插入到表中。 管理 DataTable 非常麻烦，我们必须手动指定列和行，并使用所有字符串值。 如果我们遗漏了某些内容，那么一切都会崩溃，并且不支持任何基本验证。
在本教程中，我将创建一些类，这些类将充当一个 API，通过该 API 我们可以利用 SqlBulkCopy 使用强类型对象并借助数据注释支持基本验证。
自定义属性类
首先，我们将创建自定义属性。 通过这些，我们将能够在模型本身中指定列名和表名。
DataTableAttribute 实现，用于指定表名。
/// <summary>
/// Maps a class to a Database Table or XML Element
/// </summary>
[AttributeUsage(AttributeTargets.Class)]
// Specifying target which will be only applicable for classes
public class DataTableAttribute : Attribute
{
/// <summary>
/// Maps a class to a Database Table or XML Element
/// </summary>
/// <param name="containerName">Name of the Table
/// or XML Parent Element to map</param>
public DataTableAttribute(string containerName)
: this()
{
TableName = containerName;
}
/// <summary>
/// Prevents a default instance of the <see
/// cref="DataTableAttribute"/> class from being
/// created.
/// </summary>
private DataTableAttribute()
{
TableName = string.Empty;
}
/// <summary>
/// Gets or sets the name of the table.
/// </summary>
/// <value>The name of the table.</value>
public string TableName { get; set; }
}DataColumnAttribute 实现，用于指定列名。
/// <summary>
/// Definition for identifying column name with type
/// </summary>
[AttributeUsage(AttributeTargets.Property)] // Only applicable for properties
public class DataColumnAttribute : Attribute
{
/// <summary>
/// Map a property to a database column or XML element
/// </summary>
/// <param name="name">Name of the column or element to map</param>
/// <param name="type">Underlying DbType of the column</param>
public DataColumnAttribute(string name, Type type)
: this()
{
ColumnName = name;
ColumnType = type;
}
/// <summary>
/// Prevents a default instance of the <see cref="DataColumnAttribute"/> class from being
/// created.
/// </summary>
private DataColumnAttribute()
{
ColumnName = string.Empty;
ColumnType = null;
}
/// <summary>
/// Gets or sets the name of the column.
/// </summary>
/// <value>The name of the column.</value>
public string ColumnName { get; set; }
/// <summary>
/// Gets or sets the type of the column.
/// </summary>
/// <value>The type of the column.</value>
public Type ColumnType { get; set; }
} 您可能想知道为什么我们需要拥有这些自定义属性，而不是我们可以从类本身获取表名、列名和数据类型。 这种方法的优点是我们可以指定更多属性，这些属性不参与数据库操作，或者在数据库和代码级别具有不同的名称。
Data Model
在第二阶段，让我们用上述自定义属性和数据注释属性来装饰我们的数据库模型类以进行验证。 如果您不需要对批量插入进行任何验证，可以跳过数据注释属性。
注意：如果计划进行验证，您需要添加 System.ComponentModel.DataAnnotations.dll。
用于批量插入的数据模型
/// <summary>
/// Book information
/// </summary>
[DataTable("TBookInfo")]
public class BookInfo
{
/// <summary>
/// Gets or sets the EAN.
/// </summary>
/// <value>The EAN.</value>
[DataColumn("ISBN", typeof(string))]
[Required(ErrorMessage = "EAN is required")]
[MaxLength(50, ErrorMessage = "EAN is exceeded max length of 50")]
public string EAN { get; set; }
/// <summary>
/// Gets or sets the title.
/// </summary>
/// <value>The title.</value>
[DataColumn("Title", typeof(string))]
[Required(ErrorMessage = "Book title is required")]
[MaxLength(255, ErrorMessage = "Book title is exceeded max length of 255")]
public string Title { get; set; }
/// <summary>
/// Gets or sets the name of the publisher.
/// </summary>
/// <value>The name of the publisher.</value>
[DataColumn("PublisherName", typeof(string))]
[Required(ErrorMessage = "Publisher name is required")]
[MaxLength(255, ErrorMessage = "Publisher name is exceeded max length of 255")]
public string PublisherName { get; set; }
/// <summary>
/// Gets or sets the synopsis.
/// </summary>
/// <value>The synopsis.</value>
[DataColumn("Synopsis", typeof(string))]
public string Synopsis { get; set; }
/// <summary>
/// Gets or sets the release date.
/// </summary>
/// <value>The release date.</value>
[DataColumn("ReleaseDate", typeof(DateTime))]
public DateTime? ReleaseDate { get; set; }
/// <summary>
/// Gets or sets the number of pages.
/// </summary>
/// <value>The number of pages.</value>
[DataColumn("NumberOfPages", typeof(int))]
public int? NumberOfPages { get; set; }
}
DataColumn 和 DataTable 是我们自定义定义的属性，将在稍后使用。 Required 和 MaxLength 属性来自数据注释属性。
现在我们的模型用必需的属性装饰，这些属性将参与 SqlBulkCopy。
数据模型转换实现
让我们进入将上述类自动转换为 DataTable 的实现部分。 从对象获取表名的函数实现
/// <summary>
/// Gets the table name from object.
/// </summary>
/// <typeparam name="T">Source for DataTable that need to be inserted</typeparam>
/// <returns>Get table name for object</returns>
public static String GetTableNameFromObject<T>()
{
var arrTableAttrib = typeof(T).GetCustomAttributes(typeof(DataTableAttribute), true);
return arrTableAttrib.Any() ? ((DataTableAttribute)arrTableAttrib.First()).TableName : null;
}
因此，我们已经获得了获取表名的函数。 让我们编写一个函数来获取 PropertyInfo，这将帮助我们通过反射检索属性值，并使用 DataColumnAttribute 获取数据列名及其类型。
注意：我使用简单的缓存机制来存储我们的 PropertyInfo 和 DataColumnAttribute 以进行快速检索。 您需要添加 System.Runtime.Caching.dll 才能使用缓存。
让我们看看获取这两个值的代码
/// <summary>
/// Gets all custom property.
/// </summary>
/// <typeparam name="T">Source for DataTable that need to be inserted</typeparam>
/// <returns>Property info and DataColumnAttribute</returns>
public static Dictionary<PropertyInfo, DataColumnAttribute> GetAllCustomProperty<T>()
{
if (MemoryCache.Default[typeof(T).ToString()] == null)
{
var props = (from prop in typeof(T).GetProperties()
where prop.GetCustomAttributes(typeof(DataColumnAttribute), true).Any()
select new
{
prop,
dataColumn = (DataColumnAttribute)
prop.GetCustomAttributes(typeof(DataColumnAttribute), true).FirstOrDefault()
}).ToDictionary(item => item.prop, item => item.dataColumn);
// Set Caching key name according to class
MemoryCache.Default.Set(typeof(T).ToString(), props, null);
return props;
}
return (Dictionary<PropertyInfo, DataColumnAttribute>)MemoryCache.Default[typeof(T).ToString()];
}
我通过上面提到的单个函数和 LINQ 查询简化了事情，这些更容易理解。 DataTable 接受 DataColumns 的数组。 因此，让我们声明一个获取 DataColumn 数组的方法，该方法将被多次使用，并且将使用上述函数。
/// <summary>
/// Gets all data table column.
/// </summary>
/// <typeparam name="T">Source for DataTable that need to be inserted</typeparam>
/// <returns>Data column array</returns>
public static DataColumn[] GetAllDataTableColumn<T>()
{
return (from item in GetAllCustomProperty<T>()
select new DataColumn(item.Value.ColumnName, item.Value.ColumnType))
.ToArray();
}
现在，我们需要一个函数，该函数应该通过传递自定义列属性的值来返回属性值。
/// <summary>
/// Gets the property.
/// </summary>
/// <typeparam name="T">Source for DataTable that need to be inserted</typeparam>
/// <param name="dataColumnAttributeName">Name of the data column attribute.</param>
/// <returns>PropertyInfo for data attribute column name</returns>
public static PropertyInfo GetPropertyName<T>(string dataColumnAttributeName)
{
return (from item in GetAllCustomProperty<T>()
where item.Value.ColumnName == dataColumnAttributeName
select item.Key).FirstOrDefault();
}
上面的函数再次使用相同的 GetAllCustomProperty() 来提高性能。
所有事情都已设置完毕。 现在，我们需要创建将对象解析为 DataTable 以在 SqlBulkCopy 上操作的函数。
/// <summary>
/// Gets the transformed data.
/// </summary>
/// <typeparam name="T">Source for DataTable that need to be inserted</typeparam>
/// <param name="myDataObjectList">My data object list.</param>
/// <returns>DataTable with mapped columns</returns>
public static DataTable GetTransformedData<T>(IList<T> myDataObjectList)
{
var dataTable = new DataTable(GetTableNameFromObject<T>());
// Get all Data columns
var allColumns = GetAllDataTableColumn<T>();
dataTable.Columns.AddRange(allColumns);
foreach (T record in myDataObjectList)
{
var row = dataTable.NewRow();
foreach (var colmnAttrib in allColumns)
{
row[colmnAttrib] = GetPropertyName<T>(
colmnAttrib.ColumnName).GetValue(record) ?? DBNull.Value;
}
dataTable.Rows.Add(row);
}
return dataTable;
}
验证
我们在第一阶段定义了自定义属性类，并在第二阶段装饰了我们的模型类。 第三阶段代码处理将模型转换为 DataTable。
假设您从某个源获取值并将其保存到 lstBooks 对象。 验证 lstBooks 并删除具有无效行的项目的代码
/// <summary>Validates data and remove any failed rows</summary>
/// <param name="lstBookInfoes">Modified book info list after validation.</param>
private static void ValidateData(ref List<BookInfo> lstBookInfoes)
{
var results = new List<ValidationResult>();
// Validate all fields of bookInfo
lstBookInfoes.RemoveAll(bookInfo =>
!Validator.TryValidateObject(
bookInfo,
new ValidationContext(bookInfo, null, null),
results,
true));
// TODO: Log the cause of failed rows
}
注意：如果我们需要一些复杂的验证逻辑，则继承 IValidatableObject 接口并在模型类上实现 IEnumerable<validationresult> Validate(ValidationContext validationContext)。 在此方法中，我们可以进行任何自定义实现。
实现执行 SqlBulkInsert 的核心代码
var lstBooks = GetSampleBookInfo();
// Validate and remove invalid rows
ValidateData(ref lstBooks);
using (var con = new SqlConnection(
ConfigurationManager.ConnectionStrings["BulkInsertDemo"].ConnectionString))
{
using (var bulkCopy = new SqlBulkCopy(con))
{
con.Open();
// Get transformed data into DataTable from given generic list
var dt = DataTableConverter.GetTransformedData(lstBooks);
// Set the destination table name
bulkCopy.DestinationTableName =
DataTableConverter.GetTableNameFromObject<BookInfo>();
bulkCopy.BatchSize = 5000;
bulkCopy.WriteToServer(dt);
}
}
转换以通用方式完成。 因此，每当您需要进行批量插入时，请使用所需的属性创建一个类，其余一切都将得到处理。 通过反射获取值时，它真的很快，因为它使用了缓存机制。 因此，性能开销很小或没有。
注意：在某些情况下，DataTable 存在与排序相关的问题。 您可以更改您的类成员顺序或表列顺序。 DataColumnAttribute 类也可以修改为具有排序序列模型类。
注意：从演示下载中，如果数据库连接失败，请从 app.config 修改数据库文件的实际路径。