[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

1.首先判断问题出在哪里

确定插入数据没有错误,然后用数据库的客户端去查看是否插入了数据

如果插入 *** 作正确,那么就是你的更新程序有了问题,就把问题集中在执行更新的

代码编写那里.

如果数据都没有插入进去,

那么问题在于你的表里是否有唯一性约束，或者是自动增长的字段以及序列SEQUENCE等等

2.先正确在数据库客户端测试你的SQL语句是否都正确

如果正确在去程序里做测试,这样可以帮你区分开是SQL写的不对,还是程序的代码有问题

看不到你的代码也看不到的报错，

只能这样了!

可以参考Spire.XLS for .NET关于数据导入导出的方法。

excel数据导入dataset如下代码：

'创建Workbook对象并加载Excel文档

Dim workbook As New Workbook()

workbook.LoadFromFile("F:\ExportData.xlsx", ExcelVersion.Version2013)

'获取第一张sheet

Dim sheet As Worksheet = workbook.Worksheets(0)

'设置range范围

Dim range As CellRange = sheet.Range(sheet.FirstRow, sheet.FirstColumn, sheet.LastRow, sheet.LastColumn)

'输出数据, 同时输出列名以及公式值

Dim dt As DataTable = sheet.ExportDataTable(range, True, True)

---