遇見零壹魔王

程式範例:

void Main()
{
	var members = new List<Member>();

	members.Add(new Member()
	{
		Id = 1,
		UserName = "Allen",
		UserAge = 20
	});

	members.Add(new Member()
	{
		Id = 2,
		UserName = "Alex",
		UserAge = 30
	});

	var articles = new List<Article>();

	articles.Add(new Article()
	{
		Id = 1,
		Title = "標題A",
		Desc = "描述A"
	});

	articles.Add(new Article()
	{
		Id = 1,
		Title = "標題B",
		Desc = "描述B"
	});

    var input = new List<InsertModel>();

	input.Add(new InsertModel() { TableName = "Member", InsertDataList = members.Cast<object>().ToList() });
	input.Add(new InsertModel() { TableName = "Article" , InsertDataList = articles.Cast<object>().ToList()});

	using (var conn = new SqlConnection(connStr))
	{
		conn.Open();

		using (var transaction = conn.BeginTransaction())
		{
			using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
			{
				try
				{
					foreach (var item in input)
					{
						DataTable dt = new DataTable();

						dt = item.InsertDataList.ListToDataTable();

						sqlBulkCopy.BatchSize = 1000;
						sqlBulkCopy.BulkCopyTimeout = 300;

						sqlBulkCopy.DestinationTableName = item.TableName;
						sqlBulkCopy.WriteToServer(dt);
					}

					transaction.Commit();
				}
				catch (Exception ex)
				{
					transaction.Rollback();
				}
			}

		}

		conn.Close();
	}
}

// You can define other methods, fields, classes and namespaces here

public static class DataTableTools
{
	public static DataTable ListToDataTable<T>(this List<T> listValue)
	{
		//建立DataTable
		var dt = new DataTable();

		PropertyInfo[] propInfoList = null;

		foreach (var item in listValue)
		{
			//判斷DataTable又沒有定義欄位名稱與型態
			if (dt.Columns.Count == 0)
			{
				//取得本次輸入物件的所有的屬性
				propInfoList = item.GetType().GetProperties();

				//在DataTable中加入欄位的名稱與型別
				foreach (var propItem in propInfoList)
				{
					try
					{
						dt.Columns.Add(propItem.Name, Nullable.GetUnderlyingType(propItem.PropertyType) ?? propItem.PropertyType);
					}
					catch (Exception ex)
					{
						throw;
					}

				}
			}

			//建立新的列
			DataRow dr = dt.NewRow();

			//將資料逐筆加到DataTable
			foreach (var propItem in propInfoList)
			{
				try
				{
					var propValue = propItem.GetValue(item, null);
					propValue = propValue ?? DBNull.Value;
					dr[propItem.Name] = propValue;
				}
				catch (Exception ex)
				{
					throw;
				}

			}

			dt.Rows.Add(dr);
		}

		dt.AcceptChanges();

		return dt;
	}
}

public class InsertModel
{
	public string TableName { get; set; }
	public List<object> InsertDataList { get; set; }
}

public class Member
{
	public int Id { get; set; }

	public string UserName { get; set; }

	public int UserAge { get; set; }
}

public class Article
{
	public int Id { get; set; }

	public string Title { get; set; }

	public string Desc { get; set; }
}