10000条数据的datatable插入数据库

adapter.Update() 这个吧！其他的解决方案我是不知道，看楼下的了。

“放using里面应该差不多”，这个回答比较抽象，不懂

批量更新用adapter.Update()，先将数据存在datatable中，再一次性更新上去，比你频繁访问数据库效率高很多

两个都差不多，要效率考虑参数化Sql， sqlcommmand.Prepare()方法；之间连接不关闭。你这个数量很小啊应该很快的。如果是SqlServer，SqlBulkCopy类可以看看

查了下SqlBulkCopy ，网上说性能不错，没用过，先试试

我没有用过adapter.Update()，一直用insert受教。有时间试试

用存储过程 insert into 。。 select from。。

用timer控件在解决方案中写一个小程序　做一下比较就行了

有个大虾告诉过俺，再大的数据也是一条一条插进去的，用存储过程比较好，也有的用Excel导入

using(Sqlconnection a=new ...等等){
//插入操作
}using能及时释放‘稀缺’资源，如数据库链接资源，提高资源利用率

adapter.Update() 比较早就有了。SqlBulkCopy 是.net 2.0以后的新功能，会用哪个就用哪个。添加操作没有日志记录的负担，性能差不多的。

.net 2.0 好像有一个SqlBulkCopy 专门处理批量操作。

SqlBulkCopy 每秒插入10000条数据

楼主可以使用ADO.NET2.0里提供的新的特性，SqlBulkCopy类，该类可以高效率的将大量数据插入数据库中。
    Private Sub CopyData()
        Dim OriginCon, DestinationCon As New SqlConnection()
        Dim OriginCmd, DestinationCmd As New SqlCommand()
        Dim Reader As SqlDataReader        OriginCon.ConnectionString = _conStr
        DestinationCon.ConnectionString = _conStr        OriginCmd.Connection = OriginCon
        OriginCmd.CommandText = "select * from customers"
        OriginCmd.CommandType = CommandType.Text        OriginCon.Open()        Dim NorthwindBulk As SqlBulkCopy
        NorthwindBulk = New SqlBulkCopy(_conStr, SqlBulkCopyOptions.UseInternalTransaction)
        NorthwindBulk.DestinationTableName = "Cu"
        NorthwindBulk.ColumnMappings.Add("CustomerID", "CustomerID")
        NorthwindBulk.ColumnMappings.Add("CompanyName", "CompanyName")
        NorthwindBulk.ColumnMappings.Add("ContactName", "ContactName")
        NorthwindBulk.ColumnMappings.Add("ContactTitle", "ContactTitle")
        NorthwindBulk.ColumnMappings.Add("Address", "Address")
        NorthwindBulk.ColumnMappings.Add("City", "City")        NorthwindBulk.ColumnMappings.Add("Region", "Region")
        NorthwindBulk.ColumnMappings.Add("PostalCode", "PostalCode")
        NorthwindBulk.ColumnMappings.Add("Country", "Country")
        NorthwindBulk.ColumnMappings.Add("Phone", "Phone")
        'NorthwindBulk.ColumnMappings.Add("Fax", "Fax")        Dim Map As SqlBulkCopyColumnMapping = New SqlBulkCopyColumnMapping("Fax", "Fax")        NorthwindBulk.ColumnMappings.Add(Map)
        NorthwindBulk.BulkCopyTimeout = 99999999
        NorthwindBulk.NotifyAfter = 1000        Reader = OriginCmd.ExecuteReader()        Try
            NorthwindBulk.WriteToServer(Reader)
        Catch ex As Exception        End Try    End Sub    Private Sub BatchUpdate()
        'Submit the updates in batches by setting the UpdateBatchSize property        'Retrieve all products in a category
        'Discount the unit pice 10% for all products that are not discontinued
        'Submit all changes (in a System.Transaction that will roll back)        Dim tbl As New DataTable()        Dim strSQL As String = "SELECT ProductID, ProductName, UnitPrice, Discontinued FROM Products"
        Dim da As New SqlDataAdapter()
        Dim cb As New SqlCommandBuilder(da)        Using cn As New SqlConnection(_conStr)
            da.SelectCommand = New SqlCommand(strSQL, cn)
            da.Fill(tbl)
        End Using        For Each row As DataRow In tbl.Select("Discontinued = 0")
            row("UnitPrice") = CDec(row("UnitPrice")) * 0.9
        Next        strSQL = "UPDATE Products SET ProductName = @ProductName_New, UnitPrice = @UnitPrice_New " & _
                 "    WHERE @ProductID_Old = ProductID AND @ProductName_Old = ProductName AND " & _
                 "         (@UnitPrice_Old = UnitPrice OR (@UnitPrice_Null = 1 AND UnitPrice IS NULL))"
        da.UpdateCommand = New SqlCommand(strSQL)
        da.UpdateCommand.Parameters.Add("@ProductName_New", SqlDbType.NVarChar, 40, "ProductName")
        da.UpdateCommand.Parameters.Add("@UnitPrice_New", SqlDbType.Money, 0, "UnitPrice")
        da.UpdateCommand.Parameters.Add("@ProductID_Old", SqlDbType.Int, 0, "ProductID").SourceVersion = DataRowVersion.Original
        da.UpdateCommand.Parameters.Add("@ProductName_Old", SqlDbType.NVarChar, 40, "ProductName").SourceVersion = DataRowVersion.Original
        da.UpdateCommand.Parameters.Add("@UnitPrice_Old", SqlDbType.Money, 0, "UnitPrice").SourceVersion = DataRowVersion.Original
        With da.UpdateCommand.Parameters.Add("@UnitPrice_Null", SqlDbType.Int, 0, "UnitPrice")
            .SourceVersion = DataRowVersion.Original
            .SourceColumnNullMapping = True
        End With
        da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None        da.UpdateBatchSize = 25
        da.ContinueUpdateOnError = True        'Wrap the changes in a transaction that will be rolled back implicitly
        'This is done to make sure you can run the sample without actually changing the database
        Using txn As New TransactionScope()
            Using cn As New SqlConnection(_conStr)
                da.SelectCommand.Connection = cn
                da.UpdateCommand.Connection = cn
                da.Update(tbl)
            End Using
            'Uncomment this line to commit the transaction
            'txn.Complete()
        End Using
    End Sub

  Private Sub BatchCopy()
        Dim strSQL As String
        'Clean out the contents of the destination table
        Using cnDestination As New SqlConnection(_conStr)
            cnDestination.Open()
            Dim cmdDestinationPrepTable As New SqlCommand("DELETE FROM BCP_Demo", cnDestination)
            Try
                cmdDestinationPrepTable.ExecuteNonQuery()
            Catch
                strSQL = "CREATE TABLE BCP_Demo (EmployeeID int identity PRIMARY KEY, LastName nvarchar(40) NOT NULL, FirstName nvarchar(10) NOT NULL, Title nvarchar(30), ReportsTo int REFERENCES BCP_Demo(EmployeeID))"
                cmdDestinationPrepTable.CommandText = strSQL
                cmdDestinationPrepTable.ExecuteNonQuery()
            End Try
            cnDestination.Close()
        End Using
        Using cnSource As New SqlConnection(_conStr), _
              cnDestination As New SqlConnection(_conStr)            'Execute a query against the source database
            'Close the connection as soon as the reader is closed
            strSQL = "SELECT EmployeeID, Title, LastName, FirstName, ReportsTo FROM Employees"
            Dim cmdSource As New SqlCommand(strSQL, cnSource)
            cnSource.Open()
            Using rdrSource As SqlDataReader = cmdSource.ExecuteReader(CommandBehavior.CloseConnection)
                Dim dt As New DataTable()
                dt.Load(rdrSource)
                'Wrap the work in a transaction
                cnDestination.Open()
                Using txn As SqlTransaction = cnDestination.BeginTransaction()                    'Console.WriteLine("  Writing data to server...")
                    'Create a BCP object that will send rows to the destination table
                    Using bulkcopy As New SqlBulkCopy(cnDestination, SqlBulkCopyOptions.KeepIdentity Or SqlBulkCopyOptions.CheckConstraints, txn)
                        'Specify the name of the destination table
                        bulkcopy.DestinationTableName = "BCP_Demo"                        'Columns are mapped from the source stream to the destination table by ordinal
                        'In this case, the order of columns in the source query does not match the order of columns in the destination table
                        'So, we can use the ColumnMappings to map based on their names, as shown here
                        bulkcopy.ColumnMappings.Add("EmployeeID", "EmployeeID")
                        bulkcopy.ColumnMappings.Add("LastName", "LastName")
                        bulkcopy.ColumnMappings.Add("FirstName", "FirstName")
                        bulkcopy.ColumnMappings.Add("Title", "Title")
                        bulkcopy.ColumnMappings.Add("ReportsTo", "ReportsTo")                        'Mapping columns based on their ordinals is also an option, as shown here
                        'bulkcopy.ColumnMappings.Add(0, 0)
                        'bulkcopy.ColumnMappings.Add(2, 1)
                        'bulkcopy.ColumnMappings.Add(3, 2)
                        'bulkcopy.ColumnMappings.Add(1, 3)
                        'bulkcopy.ColumnMappings.Add(4, 4)                        'Pass the source data reader to the BCP object to write the data to the server
                        bulkcopy.WriteToServer(dt)                        bulkcopy.Close()
                    End Using                    'Commit the transaction
                    txn.Commit()
                End Using                rdrSource.Close()
            End Using
            'Now display the contents of the destination table
            Response.Write("  Contents of the destination table <br />")
            Dim cmdDestination As New SqlCommand("SELECT EmployeeID, LastName + ', ' + FirstName, Title, ReportsTo FROM BCP_Demo", cnDestination)
            Using rdrDestination As SqlDataReader = cmdDestination.ExecuteReader()
                While rdrDestination.Read
                    Dim objValues(3) As Object
                    rdrDestination.GetValues(objValues)
                    Response.Write(String.Format("  ID:{0} Name:{1,-17} Title:{2,-24} ReportsTo:{3}", objValues(0), objValues(1), objValues(2), IIf(objValues(3) Is DBNull.Value, "<Null>", objValues(3))))
                End While
            End Using            cnDestination.Close()
        End Using
    End Sub

调试易

10000条数据的datatable插入数据库

解决方案 »