asp mysql insert_asp.net 批量向数据库高效插入数据的方法

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协议进行数据的批量复制，使用这个有点要注意的地方，就是数据源表中的列和目标表中的列一一对应

///

/// 批量插入数据

///

/// 目标表

/// 数据源表

/// 链接字符串

///

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;

}

下面是想写成泛型的，这是个半成品还没有想好怎样实现通用性会更好

///

///

///

///

///

///

///

///

///

public static bool SqlBulkCopyInsert(string DestinationTableName, string[] DataColumnName, Dictionary dictionary)

{

using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(WebConfig.AutoSiteV2String))

{

try

{

DataTable dataTable = BaseDalc.GetTableSchema(DataColumnName);

foreach (KeyValuePair 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

利用以上的三种方法，都可以完成向数据库批量插入数据。