ADO.NET三種模式-連接配接模式

一、什麼是ADO.NET

簡單的講，**ADO.NET是一組允許.NET開發人員使用标準的，結構化的，甚至無連接配接的方式與資料互動的技術。**對于ADO.NET來說，可以處理資料源是多樣的。可以是應用程式唯一使用的建立在記憶體中資料，也可以是與應用程式分離，存儲在存儲區域的資料（如文本檔案、XML、關系資料庫等）。

具體來說，ADO.NET 對 Microsoft SQL Server 和 XML 等資料源以及通過 OLE DB 和 XML 公開的資料源提供一緻的通路。資料共享使用者應用程式可以使用 ADO.NET 來連接配接到這些資料源，并檢索、處理和更新所包含的資料。

作為.NET架構的重要組成部分，ADO.NET 類封裝在 System.Data.dll 中，并且與 System.Xml.dll 中的 XML 類內建。當編譯使用 System.Data 命名空間的代碼時，需要引用System.Data.dll 和 System.Xml.dll。

二、使用ADO.NET的三種方式

連接配接模式

您的代碼庫顯式連接配接到基礎資料存儲并與之斷開連接配接。當您以這種方式使用ADO.NET時，通常使用連接配接對象，指令對象和資料讀取器對象與資料存儲進行互動。
斷開模式

斷開連接配接模式允許您操作一組DataTable對象（包含在DataSet中），該對象充當外部資料的用戶端副本。調用者收到資料集後，便可以周遊和操縱内容。如果調用者想将更改送出回資料存儲，則使用資料擴充卡（與一組SQL語句結合）來更新資料源。
使用ORM

第三種方法是使用對象關系映射器(ORM)，例如NHibernate或Entity Framework。對象關系映射器使用C＃對象以應用程式為中心的方式表示資料，并從開發人員手中提取許多資料通路代碼。 EF還允許您使用強類型LINQ查詢進行程式與資料庫的互動，動态查詢建立特定于資料庫的查詢。

有關命名空間：

System.Data.dll

System.Data.Common.dll

System.Data.Sql

System.Data.SqlTypes

Microsoft.SqlServer.Server

三、ADO.NET資料提供程式

ADO.NET資料提供程式(Data Provider)核心對象

Conection

Command

DataReader

DataAdapter

Parameter

Transaction

ADO.NET三種模式-連接配接模式

ADO.NET核心元件及用途：

ADO.NET三種模式-連接配接模式

微軟提供的ADO.NET資料提供程式：

Data Provider	命名空間	程式集	作用
OLE DB	System.Data.OleDb	System.Data.dll	提供對使用 OLE DB 公開的資料源中資料的通路。使用 System.Data.OleDb 命名空間。
Microsoft SQL Server	System.Data.SqlClient	System.Data.dll	提供對 Microsoft SQL Server 7.0 或更高版本中資料的通路。使用 System.Data.SqlClient 命名空間。
ODBC	System.Data.Odbc	System.Data.dll	提供對使用 ODBC 公開的資料源中資料的通路。使用 System.Data.Odbc 命名空間。

四、使用接口抽象化DataProvider(Data Provider的動态切換)

使用Enum枚舉和App.config,根據自定義枚舉類型擷取資料庫連接配接對象Connection,示例代碼：

using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using static System.Console;

namespace MyConnectionFactory
{
    class Program
    {
        static void Main(string[] args)
        {
            WriteLine("*** 資料連接配接工廠 *** \n");
		   //根據App.config配置檔案擷取連接配接類型
            string dataProviderString = ConfigurationManager.AppSettings["provider"];
            DataProvider dataProvider = DataProvider.None;
            //根據自定義枚舉類型擷取資料連接配接對象Connection
            if (Enum.IsDefined(typeof(DataProvider), dataProviderString))
            {
                dataProvider = (DataProvider)Enum.Parse(typeof(DataProvider), dataProviderString);
            }
            else
            {
                WriteLine("Sorry,no provider exists!");
                ReadLine();
                return;
            }
            IDbConnection myConnection = GetConnection(dataProvider);
            WriteLine($"Your connection is a {myConnection.GetType().Name}");
            ReadLine();
        }
        /// <summary>
        /// //根據自定義枚舉類型擷取資料連接配接對象Connection
        /// </summary>
        /// <param name="dataProvider"></param>
        /// <returns></returns>
        private static IDbConnection GetConnection(DataProvider dataProvider)
        {
            IDbConnection connection = null;
            switch (dataProvider)
            {
                case DataProvider.SqlServer:
                    connection = new SqlConnection();
                    break;
                case DataProvider.OleDb:
                    connection = new OleDbConnection();
                    break;
                case DataProvider.Odbc:
                    connection = new OleDbConnection();
                    break;
            }
            return connection;
        }

        enum DataProvider
        {
            SqlServer,
            OleDb,
            Odbc,
            None
        }
    }
}
App.config檔案内容：
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <!-- This key value maps to one of our enum values. -->
    <add key="provider" value="SqlServer"/>
  </appSettings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
    </startup>
</configuration>

五、ADO.NET資料提供程式工廠模型

.NET資料提供程式工廠模式使您可以使用通用資料通路類型來建構單個代碼庫。此外，使用應用程式配置檔案（和子元素），可以以聲明方式擷取提供程式和連接配接字元串，而無需重新編譯或重新部署使用ADO.NET API的程式集。

DataProvider Factory示例程式：通過配置檔案擷取Data Provider類型和連接配接字元串：

using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using static System.Console;

namespace DataProviderFactory2
{
    class Program
    {
        static void Main(string[] args)
        {
            WriteLine("*** 資料提供程式工廠 *** \n");
            string dataProvider = ConfigurationManager.AppSettings["provider"];
            string connectionString = ConfigurationManager.ConnectionStrings["AutoLotSqlProvider"].ConnectionString;
            //擷取資料提供程式工廠
            DbProviderFactory factory = DbProviderFactories.GetFactory(dataProvider);
            using (DbConnection connection = factory.CreateConnection())
            {
                if (connection == null)
                {
                    ShowError("Connection");
                    return;
                }
                WriteLine($"資料連接配接對象為: {connection.GetType().Name}");
                connection.ConnectionString = connectionString;
                connection.Open();

                var sqlConnection = connection as SqlConnection;
                if (sqlConnection != null)
                {
                    WriteLine("sql server執行個體的版本:" + sqlConnection.ServerVersion);
                }
                //建立指令對象
                DbCommand command = factory.CreateCommand();
                if (command == null)
                {
                    ShowError("Command");
                    return;
                }
                WriteLine($"指令對象為:{command.GetType().Name}");
                command.Connection = connection;
                command.CommandText = "select * from inventory";
                //這裡sql語句可以為多個，例如：“Select * From Inventory;Select * From Customers”;如果是多個語句查詢出來的結果是依次列出，而不是并列查詢;
                using (DbDataReader dataReader = command.ExecuteReader())
                {
                    WriteLine($"資料讀取器對象為:{dataReader.GetType().Name}");
                    WriteLine("\n*** 目前庫存 ***");
                    while (dataReader.Read())
                    {
                        WriteLine($"-> Car #{dataReader["CarId"]} is a {dataReader["Make"]}.");
                    }
                }
                ReadLine();
            }
        }

        private static void ShowError(string objectName)
        {
            WriteLine($"建立{objectName}出粗");
            ReadLine();
        }
    }
}

App.config檔案内容：
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="provider" value="System.Data.SqlClient"/>
  </appSettings>
  <connectionStrings>
    <add name="AutoLotSqlProvider" connectionString="Data Source=.;Initial Catalog=AutoLot;Integrated Security=true;"/>
    <add name="AutoLotOleDbProvider" connectionString="Provider=SQLNCLI11;Data Source=.;Initial Catalog=AutoLot;Integrated Security=SSPI;"/>
  </connectionStrings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
    </startup>
</configuration>

六、ADO.NET連接配接模式

連接配接模式使用到Connection,Command,DataReader對象，需要顯式地打開關閉資料庫。

使用連接配接模式的操作資料庫的步驟：

建立connection對象，配置連接配接字元串
建立command對象，構造函數參數為sql查詢語句和connection對象
調用command對象的ExecuteReader方法擷取DataReader
調用DataReader的Read方法讀取資料
示例程式：使用連接配接模式示例，用DataReader讀取資料

using System.Data.SqlClient;
using static System.Console;
namespace AutoLotDataReader
{
	class Program
	{
		static void Main(string[] args)
		{
			WriteLine("***** 使用 Readers *****\n");
			// 通過Builder建立資料庫連接配接字元串ConnectionString
			// 也可以直接使用字元串
			//var strConnect = @"Data Source=.;Initial Catalog=AutoLot;Integrated Security=true";
			var cnStringBuilder = new SqlConnectionStringBuilder
			{
				InitialCatalog = "AutoLot",
				DataSource = @".",
				ConnectTimeout = 30,
				IntegratedSecurity = true
			};

			// 建立資料庫連接配接對象Connection
			using (var connection = new SqlConnection())
			{
				connection.ConnectionString = cnStringBuilder.ConnectionString;
				connection.Open();
				ShowConnectionStatus(connection);

				// 建立指令對象Command
				string sql = "Select * From Inventory;Select * from Customers";

				using (SqlCommand myCommand = new SqlCommand(sql, connection))
				{
					// 通過ExecuteReader擷取DataReader對象
					// Obtain a data reader a la ExecuteReader().
					using (SqlDataReader myDataReader = myCommand.ExecuteReader())
					{
						do
						{
							//Read方法讀取資料
							while (myDataReader.Read())
							{
								WriteLine("***** Record *****");
								for (int i = 0; i < myDataReader.FieldCount; i++)
								{
									WriteLine($"{myDataReader.GetName(i)} = {myDataReader.GetValue(i)}");
								}
								WriteLine();
							}
						} while (myDataReader.NextResult());
					}
				}
			}
			ReadLine();

		}

		/// <summary>
		/// 顯示資料庫連接配接資訊
		/// </summary>
		/// <param name="connection"></param>
		private static void ShowConnectionStatus(SqlConnection connection)
		{
			WriteLine("**** 資料庫連接配接資訊: ***");
			WriteLine($"資料庫連接配接位址: {connection.DataSource}");
			WriteLine($"資料庫名稱: {connection.Database}");
			WriteLine($"連接配接逾時時間: {connection.ConnectionTimeout}");
			WriteLine($"連接配接狀态:{connection.State}\n");
		}
	}
}

建立、修改、删除資料

準備工作：建立DAL類庫

using AutoLotDAL.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace AutoLotDAL.DataOperations
{
    public class InventoryDAL
    {
        private readonly string connectionString;
        private SqlConnection sqlConnection = null;

        public InventoryDAL() : this(@"Data Source=.;Initial Catalog=AutoLot;Integrated Security=true")
        {

        }
        public InventoryDAL(string connectionString)
        {
            this.connectionString = connectionString;
        }

        /// <summary>
        /// 擷取資料集合
        /// </summary>
        /// <returns></returns>
        public List<Car> GetAllInventory()
        {
            OpenConnection();
            List<Car> inventory = new List<Car>();
            string sql = "select * from inventory";
            using (SqlCommand command = new SqlCommand(sql, sqlConnection))
            {
                command.CommandType = CommandType.Text;
                SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (dataReader.Read())
                {
                    inventory.Add(new Car
                    {
                        CarId = (int)dataReader["CarId"],
                        Color = (string)dataReader["Color"],
                        Make = (string)dataReader["Make"],
                        PetName = (string)dataReader["PetName"]
                    });
                }
                dataReader.Close();
            }
            return inventory;
        }

        /// <summary>
        /// 查詢單個資料
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Car GetCar(int id)
        {
            OpenConnection();
            Car car = null;
            string sql = $"select * from inventory where carid={id}";
            using (SqlCommand command = new SqlCommand(sql, sqlConnection))
            {
                command.CommandType = CommandType.Text;
                SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (dataReader.Read())
                {
                    car = new Car
                    {
                        CarId = (int)dataReader["CarId"],
                        Color = (string)dataReader["Color"],
                        Make = (string)dataReader["Make"],
                        PetName = (string)dataReader["PetName"]
                    };
                }
                dataReader.Close();
            }
            return car;
        }

        /// <summary>
        /// 插入資料
        /// </summary>
        /// <param name="color"></param>
        /// <param name="make"></param>
        /// <param name="petName"></param>
        public void InsertAuto(string color, string make, string petName)
        {
            OpenConnection();
            // Format and execute SQL statement.
            string sql = $"Insert Into Inventory (Make, Color, PetName) Values ('{make}', '{color}','{petName}')";
            // Execute using our connection.
            using (SqlCommand command = new SqlCommand(sql, sqlConnection))
            {
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
            }
            CloseConnection();
        }

        public void InsertAuto(Car car)
        {
            OpenConnection();
            // Format and execute SQL statement.
            string sql = "Insert Into Inventory (Make, Color, PetName) Values " +
            $"('{car.Make}', '{car.Color}', '{car.PetName}')";
            // Execute using our connection.
            using (SqlCommand command = new SqlCommand(sql, sqlConnection))
            {
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
            }
            CloseConnection();
        }

        /// <summary>
        /// 參數化sql語句
        /// </summary>
        /// <param name="car"></param>
        public void InsertAuto2(Car car)
        {
            OpenConnection();
            string sql = "Insert into Inventory" +
                "(Make,Color,PetName) Values" +
                "(@Make,@Color,@PetName)";
            using (SqlCommand command = new SqlCommand(sql, sqlConnection))
            {
                command.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@Make",
                    Value = car.Make,
                    SqlDbType = SqlDbType.Char,
                    Size = 10
                });
                command.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@Color",
                    Value = car.Color,
                    SqlDbType = SqlDbType.Char,
                    Size = 10
                });
                command.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@PetName",
                    Value = car.PetName,
                    SqlDbType = SqlDbType.Char,
                    Size = 10
                });

                command.ExecuteNonQuery();
                CloseConnection();
            }
        }

        /// <summary>
        /// 删除資料
        /// </summary>
        /// <param name="id"></param>
        public void DeleteCar(int id)
        {
            OpenConnection();
            // Get ID of car to delete, then do so.
            string sql = $"Delete from Inventory where CarId = '{id}'";
            using (SqlCommand command = new SqlCommand(sql, sqlConnection))
            {
                try
                {
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Exception error = new Exception("Sorry! That car is on order!", ex);
                    throw error;
                }
            }
            CloseConnection();
        }

        /// <summary>
        /// 更新資料
        /// </summary>
        /// <param name="id"></param>
        /// <param name="newPetName"></param>
        public void UpdateCarPetName(int id, string newPetName)
        {
            OpenConnection();
            // Get ID of car to modify the pet name.
            string sql = $"Update Inventory Set PetName = '{newPetName}' Where CarId = '{id}'";
            using (SqlCommand command = new SqlCommand(sql, sqlConnection))
            {
                command.ExecuteNonQuery();
            }
            CloseConnection();
        }

        /// <summary>
        /// 使用存儲過程
        /// </summary>
        /// <param name="carId"></param>
        /// <returns></returns>
        public string LookUpPetName(int carId)
        {
            OpenConnection();
            string carPetName;
            //建立存儲過程指令
            using (SqlCommand command = new SqlCommand("GetPetName", sqlConnection))
            {
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter parameter = new SqlParameter
                {
                    ParameterName = "@carId",
                    SqlDbType = SqlDbType.Int,
                    Value = carId,
                    Direction = ParameterDirection.Input
                };
                command.Parameters.Add(parameter);
                parameter = new SqlParameter
                {
                    ParameterName = "@petName",
                    SqlDbType = SqlDbType.Char,
                    Size = 10,
                    Direction = ParameterDirection.Output
                };
                command.Parameters.Add(parameter);
                command.ExecuteNonQuery();
                carPetName = (string)command.Parameters["@petName"].Value;
                CloseConnection();
            }
            return carPetName;
        }

        /// <summary>
        /// 使用事務
        /// </summary>
        /// <param name="throwEx"></param>
        /// <param name="custId"></param>
        public void ProcessCreditRisk(bool throwEx, int custId)
        {
            OpenConnection();
            string fName;
            string lName;
            var cmdSelect = new SqlCommand($"Select * from Customers where CustId = {custId}", sqlConnection);
            using (var dataReader = cmdSelect.ExecuteReader())
            {
                if (dataReader.HasRows)
                {
                    dataReader.Read();
                    fName = (string)dataReader["FirstName"];
                    lName = (string)dataReader["LastName"];
                }
                else
                {
                    CloseConnection();
                    return;
                }
            }

            // Create command objects that represent each step of the operation.
            var cmdRemove =
            new SqlCommand($"Delete from Customers where CustId = {custId}", sqlConnection);
            var cmdInsert =
            new SqlCommand("Insert Into CreditRisks" + $"(FirstName, LastName) Values('{fName}', '{lName}')", sqlConnection);

            SqlTransaction tx = null;
            try
            {
                tx = sqlConnection.BeginTransaction();
                cmdInsert.Transaction = tx;
                cmdRemove.Transaction = tx;
                cmdInsert.ExecuteNonQuery();
                cmdRemove.ExecuteNonQuery();
                if (throwEx)
                {
                    throw new Exception("事務送出發生錯誤");
                }
                tx.Commit();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                tx?.Rollback();
            }
            finally
            {
                CloseConnection();
            }

        }

        /// <summary>
        /// 打開資料庫
        /// </summary>
        private void OpenConnection()
        {
            sqlConnection = new SqlConnection { ConnectionString = connectionString };
            sqlConnection.Open();
        }
        
        /// <summary>
        /// 關閉資料庫
        /// </summary>
        private void CloseConnection()
        {
            if (sqlConnection?.State != ConnectionState.Closed)
            {
                sqlConnection?.Close();
            }
        }

    }
}

使用DAL處理資料：

using AutoLotDAL.DataOperations;
using AutoLotDAL.Models;
using System;
using System.Linq;

namespace AutoLotClient
{
    class Program
    {
        static void Main(string[] args)
        {
            InventoryDAL dal = new InventoryDAL();

            //查詢資料
            var list = dal.GetAllInventory();

            Console.WriteLine("*** All Cars ***");
            Console.WriteLine("CarId\tMake\tColor\tPet Name");
            foreach (var item in list)
            {
                Console.WriteLine($"{item.CarId}\t{item.Make}\t{item.Color}\t{item.PetName}");
            }
            Console.WriteLine();

            //查詢資料
            var car = dal.GetCar(list.OrderBy(x => x.Color).Select(x => x.CarId).First());

            Console.WriteLine("*** First Car By Color ***");
            Console.WriteLine("CarId\tMake\tColor\tPet Name");
            Console.WriteLine($"{car.CarId}\t{car.Make}\t{car.Color}\t{car.PetName}");

            try
            {
                //删除資料
                dal.DeleteCar(5);
                Console.WriteLine("Car deleted.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An exception occurred: {ex.Message}");
            }
            //插入資料
            dal.InsertAuto(new Car { Color = "Blue", Make = "Pilot", PetName = "TowMonster" });

            list = dal.GetAllInventory();

            var newCar = list.First(x => x.PetName == "TowMonster");
            Console.WriteLine(" ************** New Car ************** ");
            Console.WriteLine("CarId\tMake\tColor\tPet Name");
            Console.WriteLine($"{newCar.CarId}\t{newCar.Make}\t{newCar.Color}\t{newCar.PetName}");

            dal.DeleteCar(newCar.CarId);

            //使用存儲過程查詢資料
            var petName = dal.LookUpPetName(car.CarId);

            Console.WriteLine(" ************** New Car ************** ");
            Console.WriteLine($"Car pet name: {petName}");
            Console.Write("Press enter to continue...");
            Console.ReadLine();
        }

        /// <summary>
        /// 使用事務
        /// </summary>
        public static void MoveCustomer()
        {
            Console.WriteLine("事務示例");
            bool throwEx = true;
            Console.WriteLine("是否抛出事務異常:");
            var userAnswer = Console.ReadLine();
            if (userAnswer?.ToLower() == "n")
            {
                throwEx = false;
            }
            var dal = new InventoryDAL();
            dal.ProcessCreditRisk(throwEx, 1);
            Console.WriteLine("檢查 CreditRisk表");
            Console.ReadLine();
        }

    }
}

使用SqlBulkCopy批量插入資料

SqlBulkCopy的WriteToServer()能夠批量插入資料，它的參數為DataTable,DataReader,DataRows類型。這裡使用自定義DataReaer,把泛型集合轉換為DataReader

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace AutoLotDAL.BulkImport
{
    public interface IMyDataReader<T> : IDataReader
    {
        List<T> Records { get; set; }
    }

    public class MyDataReader<T> : IMyDataReader<T>
    {
        private int _currentIndex = -1;
        private readonly PropertyInfo[] _propertyInfos;
        private readonly Dictionary<string, int> _nameDictionary;

        public MyDataReader()
        {
            _propertyInfos = typeof(T).GetProperties();
            _nameDictionary = _propertyInfos
                .Select((x, index) => new { x.Name, index })
                .ToDictionary(pair => pair.Name, pair => pair.index);
        }

        public MyDataReader(List<T> records) : this()
        {
            Records = records;
        }
        public List<T> Records { get; set; }

        public void Dispose()
        {

        }
        public bool Read()
        {
            if ((_currentIndex + 1) >= Records.Count) return false;
            _currentIndex++;
            return true;
        }

        public int FieldCount
            => _propertyInfos.Length;

        public string GetName(int i)
            => i >= 0 && i < FieldCount ? _propertyInfos[i].Name : string.Empty;

        public int GetOrdinal(string name)
            => _nameDictionary.ContainsKey(name) ? _nameDictionary[name] : -1;

        public object GetValue(int i)
            => _propertyInfos[i].GetValue(Records[_currentIndex]);

        //public object GetValue(int i)
        //{
        //    Car currentRecord = Records[_currentIndex] as Car;
        //    switch (i)
        //    {
        //        case 0: return currentRecord.CarId;  
        //        case 1: return currentRecord.Color;  
        //        case 2: return currentRecord.Make;  
        //        case 3: return currentRecord.PetName;
        //        default: return string.Empty;
        //    }
        //}

        public string GetDataTypeName(int i) => throw new NotImplementedException();

        public Type GetFieldType(int i) => throw new NotImplementedException();

        public int GetValues(object[] values) => throw new NotImplementedException();

        public bool GetBoolean(int i) => throw new NotImplementedException();

        public byte GetByte(int i) => throw new NotImplementedException();

        public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
            => throw new NotImplementedException();

        public char GetChar(int i) => throw new NotImplementedException();

        public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
            => throw new NotImplementedException();

        public Guid GetGuid(int i) => throw new NotImplementedException();

        public short GetInt16(int i) => throw new NotImplementedException();

        public int GetInt32(int i) => throw new NotImplementedException();

        public long GetInt64(int i) => throw new NotImplementedException();

        public float GetFloat(int i) => throw new NotImplementedException();

        public double GetDouble(int i) => throw new NotImplementedException();

        public string GetString(int i) => throw new NotImplementedException();

        public decimal GetDecimal(int i) => throw new NotImplementedException();

        public DateTime GetDateTime(int i) => throw new NotImplementedException();

        public IDataReader GetData(int i) => throw new NotImplementedException();

        public bool IsDBNull(int i) => throw new NotImplementedException();

        object IDataRecord.this[int i] => throw new NotImplementedException();

        object IDataRecord.this[string name] => throw new NotImplementedException();

        public void Close() => throw new NotImplementedException();

        public DataTable GetSchemaTable() => throw new NotImplementedException();

        public bool NextResult() => throw new NotImplementedException();

        public int Depth { get; }

        public bool IsClosed { get; }

        public int RecordsAffected { get; }

    }
}

批量插入的方法：

public static void ExecuteBulkImport<T>(IEnumerable<T> records, string tableName)
{
    OpenConnection();
    using (SqlConnection conn = _sqlConnection)
    {
        SqlBulkCopy bc = new SqlBulkCopy(conn)
        {
            DestinationTableName = tableName
        };
        var dataReader = new MyDataReader<T>(records.ToList());
        try
        {
            bc.WriteToServer(dataReader);
        }
        catch (Exception ex)
        {
            //Should do something here
        }
        finally
        {
            CloseConnection();
        }
    }
}

使用批量插入：

public static void DoBulkCopy()
{
 Console.WriteLine(" ************** Do Bulk Copy ************** ");
 var cars = new List<Car>
 {
 new Car() {Color = "Blue", Make = "Honda", PetName = "MyCar1"},
 new Car() {Color = "Red", Make = "Volvo", PetName = "MyCar2"},
 new Car() {Color = "White", Make = "VW", PetName = "MyCar3"},
 new Car() {Color = "Yellow", Make = "Toyota", PetName = "MyCar4"}
 };
 ProcessBulkImport.ExecuteBulkImport(cars, "Inventory");
 InventoryDAL dal = new InventoryDAL();
 var list = dal.GetAllInventory();
 Console.WriteLine(" ************** All Cars ************** ");
 Console.WriteLine("CarId\tMake\tColor\tPet Name");
 foreach (var itm in list)
 {
 Console.WriteLine($"{itm.CarId}\t{itm.Make}\t{itm.Color}\t{itm.PetName}");
 }
 Console.WriteLine();
}

ADO.NET三種模式-連接配接模式

一、什麼是ADO.NET

二、使用ADO.NET的三種方式

三、ADO.NET資料提供程式

四、使用接口抽象化DataProvider(Data Provider的動态切換)

五、ADO.NET資料提供程式工廠模型

六、ADO.NET連接配接模式

繼續閱讀

C#進階程式設計——Reflection反射

在 Visual Basic 2005 中使用 My.Settings

FineUploader 結合一般處理程式【上傳示例】

How To: Use Role Manager in ASP.NET 2.0

C# Http請求(WebClient,HttpWebRequest)

Code Page Identifiers Code Page Identifiers

Enterprise Library- Data Block使用oracle存儲過程，字元串參數傳入值為""時出現問題的解決

在C#中運用API函數編寫多功能關機程式

C#得到漢字的GB2312碼

TPL使用異步程式設計模型(APM)實作簡單網頁抓取

開發Teams Tabs應用程式

Microsoft Teams的Outgoing Webhook開發入門

【C#】擷取檔案、目錄下所有檔案、子目錄檔案

遇到的問題之dotnet騰訊COS生成臨時憑證

Serialization

教你如何使用Visual C#來建立Windows服務程式