Ⅰ [VB.NET+sqlserver]如何往新建的dataset中插入數據
我這有一個Oracle 數據批量插入的你看看有沒有用
一，直接構建SQL語句插入
1 sw.Start()
2 ''''Read Z02J from SQL Server
3 Dim sqlCmd As New SqlCommand()
4 sqlCmd.Connection = sqlConnection
5 sqlCmd.CommandText = "SELECT * FROM Z02J"
6
7 Dim sqlDr As SqlDataReader
8 sqlDr = sqlCmd.ExecuteReader()
9
10 Dim cmdInsertZ02J As New OracleCommand()
11 cmdInsertZ02J.Connection = oraConnection
12 cmdInsertZ02J.CommandText = BuildSQLStatement(SQLType.Insert,"z02j")
13
14 Dim plantLever, material, oldMaterialNum, materialDescription As Object
15 While sqlDr.Read()
16 plantLever = ReadSqlDataReader(sqlDr, 0, "")
17 material = ReadSqlDataReader(sqlDr, 1, "")
18 oldMaterialNum = ReadSqlDataReader(sqlDr, 2, "")
19 materialDescription = ReadSqlDataReader(sqlDr, 3, "")
20 ''Insert to Oracle table Z02J
21 cmdInsertZ02J.Parameters.AddWithValue(":plantLever", plantLever)
22 cmdInsertZ02J.Parameters.AddWithValue(":material", material)
23 cmdInsertZ02J.Parameters.AddWithValue(":oldMaterialNum", oldMaterialNum)
24 cmdInsertZ02J.Parameters.AddWithValue(":materialDescription", materialDescription)
25 cmdInsertZ02J.ExecuteNonQuery()
26 End While
27 sw.Stop()
28 Loger.Info("Reading z02j form sql sever used", sw.Elapsed.TotalSeconds.ToString())
二，採用DataAdapter實現批量插入
1 sw.Start()
2 ''''Read Z02J from SQL Server
3 Dim sqlCmd As New SqlCommand()
4 sqlCmd.Connection = sqlConnection
5 sqlCmd.CommandText = "SELECT * FROM Z02J"
6
7 Dim sqlDr As SqlDataReader
8 sqlDr = sqlCmd.ExecuteReader()
9
10 Dim cmdInsertZ02J As New OracleCommand()
11 cmdInsertZ02J.Connection = oraConnection
12 cmdInsertZ02J.CommandText = BuildSQLStatement(SQLType.Insert,"z02j")
13
14 Dim dtSqlZ02J As New DataTable
15 dtSqlZ02J.Columns.Add("plantLever")
16 dtSqlZ02J.Columns.Add("material")
17 dtSqlZ02J.Columns.Add("oldMaterialNum")
18 dtSqlZ02J.Columns.Add("materialDescription")
19
20 Dim plantLever, material, oldMaterialNum, materialDescription As Object
21 While sqlDr.Read()
22 plantLever = ReadSqlDataReader(sqlDr, 0, "")
23 material = ReadSqlDataReader(sqlDr, 1, "")
24 oldMaterialNum = ReadSqlDataReader(sqlDr, 2, "")
25 materialDescription = ReadSqlDataReader(sqlDr, 3, "")
26 dtSqlZ02J.Rows.Add(plantLever, material, oldMaterialNum, materialDescription)
27 End While
28 sw.Stop()
29 Loger.Info("Reading z02j form sql sever used", sw.Elapsed.TotalSeconds.ToString())
30
31 sw.Start()
32 Dim oraDa As New OracleDataAdapter()
33 oraDa.InsertCommand = cmdInsertZ02J
34 oraDa.InsertCommand.Parameters.Add(":plantLever", OracleType.Char, 255, "plantLever")
35 oraDa.InsertCommand.Parameters.Add(":material", OracleType.Char, 255, "material")
36 oraDa.InsertCommand.Parameters.Add(":oldMaterialNum", OracleType.Char, 255, "oldMaterialNum")
37 oraDa.InsertCommand.Parameters.Add(":materialDescription", OracleType.Char, 500, "materialDescription")
38
39 oraDa.InsertCommand.UpdatedRowSource = UpdateRowSource.None
40 oraDa.UpdateBatchSize = 20 '''Adjust the batch size based on testing result
41
42 oraDa.Update(dtSqlZ02J)
43 sw.Stop()
44 Loger.Info("Insert to oracle used", sw.Elapsed.TotalSeconds.ToString())
貌似SQL Server中.net驅動程序提供了SqlBulkCopy類來提高大量數據導入的性能。有需要可以查下MSDN