循环遍历数据集并使用VB.net在sql server数据库中保存值

问题描述：

我正在尝试遍历数据集并使用VB.net将其保存在SQL Server中。

但是我只能保存数据集中的第一行。怎么了？

I am trying to loop through a dataset and save it in SQL server using VB.net.
But i am able to save only the first row in the dataset. Whats wrong?

con.Open()

Dim cmd1 As SqlCommand

For Each Drr As DataRow In DtSet.Tables(0).Rows
    cmd1 = New SqlCommand("INSERT INTO Students (student_fullname, student_branch, student_scholarship) VALUES ('" & Drr(1).ToString & "', '" & Drr(2).ToString & "', '" & Drr(3).ToString & "')", con)
    cmd1.ExecuteNonQuery()
Next
MsgBox("Successfully Saved")
con.Close()

答

无论您的问题是什么，您是否知道此实现容易受到 SQL注入 [ ^ ]？

我建议你宁愿使用存储过程 [ ^ ]或参数化查询 [ ^ ]。

Irrespective of your question, Do you know that this implementation is susceptible of SQL Injection[^]?

I would suggest you to rather use Stored Procedure[^] or Parameterized Queries[^] in SQL.

尝试更像这样的东西（我展示的内容可能并不完全符合您的需求）。如果你需要遍历集合中的更多表格，那么添加另一个循环。

Try something more like this (what I show may not be exactly correct for your needs). If you need to loop through more tables in the set then add another loop.

con.Open()
 
Dim cmd1 As SqlCommand
    cmd1 = New SqlCommand("INSERT INTO Students (student_fullname, student_branch, student_scholarship) VALUES (@student_fullname, @student_branch, @student_scholarship)", con)

cmd1.Parameters.Add ( new SqlParameter ( @student_fullname    , nothing ) )
cmd1.Parameters.Add ( new SqlParameter ( @student_branch      , nothing ) )
cmd1.Parameters.Add ( new SqlParameter ( @student_scholarship , nothing ) ) 

For Each Drr As DataRow In DtSet.Tables(0).Rows
    cmd1.Parameters ( @student_fullname    ).Value = Drr(1) 
    cmd1.Parameters ( @student_branch      ).Value = Drr(2) 
    cmd1.Parameters ( @student_scholarship ).Value = Drr(3) 

    cmd1.ExecuteNonQuery()
Next
MsgBox("Successfully Saved")
con.Close()

循环遍历数据集并使用VB.net在sql server数据库中保存值

相关推荐