asp.net 批量向資料庫高效插入資料的方法

asp教程.net 批量向資料庫教程高效插入資料的方法

有三種方法可以批量向資料庫插入資料,利用dataset,利用事務,使用資料庫中的Table類型變數實現資料插入
這種方法的前提是資料庫中必須支援Table類型的變數

一、利用事務（transaction）

‘定義執行批量插入資料的函數,參數為insert SQL語句的數組

sub ExeTransaction (Byval Sqlstrlist as string())

dim Conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Tax.mdb"

dim trans as OledbTransaction=Nothing

try

if conn.State=connectionstate.closed then

conn.open()

end if

dim cmd as oledbCommand=new oledbcommand()

cmd.connection=conn

cmd.commandtype=commandtype.text

trans=conn.begintransaction()

cmd.transaction=trans

dim I as integer

for I=0 to Sqlstrlist.GetupperBound(0)

cmd.commandtext= Sqlstrlist(i) ‘取得參數（數組）中的值

cmd.executenonquery()

next

trans.commit()

catch ex as oledbexception

trans.rollback()

fanlly

conn.close()

end try

end sub

方式二：使用資料庫中的Table類型變數實現資料插入
這種方法的前提是資料庫中必須支援Table類型的變數。

--建立自訂表格變數
Create Type PassportTableType as Table(columns.... nvarchar(50))

--建立預存程序

Create PROCEDURE [dbo].[CreatePassportWithTVP]
@TVP PassportTableType readonly
AS
BEGIN
SET NOCOUNT ON;
Insert into Passport(PassportKey) select PassportKey from @TVP
END

--在程式中調用

SqlParameter[] sqlParameter = { new SqlParameter("@TVP", dataTable) };
SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "CreatePassportWithTVP", sqlParameter);

方式二：使用SqlBulkCopy

使用SqlBulkCopy類進行資料插入其原理是採用了SQL Server的BCP協議進行資料的批量複製，使用這個有點要注意的地方，就是資料來源表中的列和目標表中的列一一對應

/// <summary>
        /// 批量插入資料
        /// </summary>
        /// <param name="DestinationTableName">目標表</param>
        /// <param name="dt">資料來源表</param>
        /// <param name="connectionString">連結字串</param>
        /// <returns></returns>
        public static bool SqlBulkCopyInsert(string DestinationTableName, DataTable dt,string connectionString)
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString))
            {
                try
                {
                    sqlBulkCopy.DestinationTableName = DestinationTableName;
                    sqlBulkCopy.BatchSize = dt.Rows.Count;
                    SqlConnection sqlConnection = new SqlConnection(connectionString);
                    sqlConnection.Open();

                    if (dt != null && dt.Rows.Count != 0)
                    {
                        sqlBulkCopy.WriteToServer(dt);
                    }
                    sqlBulkCopy.Close();
                    sqlConnection.Close();
                }
                catch
                {
                    return false;
                }
            }
            return true;
        }

下面是想寫成泛型的，這是個半成品還沒有想好怎樣實現通用性會更好

/// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="P"></typeparam>
        /// <param name="DestinationTableName"></param>
        /// <param name="DataColumnName"></param>
        /// <param name="dictionary"></param>
        /// <returns></returns>
        public static bool SqlBulkCopyInsert<T, P>(string DestinationTableName, string[] DataColumnName, Dictionary<T, P> dictionary)
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(WebConfig.AutoSiteV2String))
            {
                try
                {
                    DataTable dataTable = BaseDalc.GetTableSchema(DataColumnName);

                    foreach (KeyValuePair<T, P> keyValuePair in dictionary)
                    {
                        DataRow dataRow = dataTable.NewRow();
                        dataRow.ItemArray = new object[] { keyValuePair.Key, keyValuePair.Value };
                        dataTable.Rows.Add(dataRow);
                    }

                    sqlBulkCopy.DestinationTableName = DestinationTableName;
                    sqlBulkCopy.BatchSize = dataTable.Rows.Count;
                    SqlConnection sqlConnection = new SqlConnection(WebConfig.AutoSiteV2String);
                    sqlConnection.Open();

                    if (dataTable != null && dataTable.Rows.Count != 0)
                    {
                        sqlBulkCopy.WriteToServer(dataTable, DataRowState.Detached);
                    }
                    sqlBulkCopy.Close();
                    sqlConnection.Close();
                }
                catch
                {
                    return false;
                }
            }
            return true;
        }

三、利用dataset

public sub Insert()

‘建立datatable資料來源

dim dt as DataTable=New DataTable()

dim dr as DataRow

dt.columns.add(new DataColumn(“name”))

dim j as Integer

for j =0 to 10

dr=dt.newrow()

dr(0)=”name”+j.tostring

dt.rows.add(dr)

next

dim Conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Tax.mdb"

conn.open()

dim MyAdapter as Oledbdataadapter=new OleDataAdapter()

….

dim cmd as Oledbcommand=New Oledbcommand(“Insert Into table(name) values (@name)”,conn)

cmd.parameters.item(“@name”).SourceColumns=dt.columns(“name”).columnsName

myadapter.update(dt)

conn.close()

end sub

利用以上的三種方法，都可以完成向資料庫批量插入資料。