使用 .net sqladapter 更新插入
Dim sSelect As String = _
"SELECT * FROM Contacts" & _
" WHERE DataSetID = @DataSetID AND ID >= @FirstID AND ID <= @LastID ORDER BY ID"
Dim dsDBFiles As New DataSet()
Dim cmd As New SqlClient.SqlCommand(sSelect, m_connection)
cmd.Parameters.Add("@FirstID", SqlDbType.Int).Value = nFirstID
cmd.Parameters.Add("@LastID", SqlDbType.Int).Value = nLastID
Dim daTable As New SqlClient.SqlDataAdapter(cmd)
Dim bldr As New SqlClient.SqlCommandBuilder(daTable)
daTable.Fill(dsDBFiles, sTable)
Dim tbl As DataTable = dsDBFiles.Tables(sTable)
Dim rdr As New Data.DataTableReader(dsFiles.Tables(0))
dsDBFiles.Load(rdr, LoadOption.Upsert, tbl)
daTable.Update(dsDBFiles, sTable)
有没有办法在不检索记录的情况下实现此更新插入功能？我正在使用 SQL Server 2005。我听说有一种方法可以使用 sqladapter 来执行此操作，而无需运行 select 语句。
我正在努力加快这个过程。有什么建议吗？
干杯。
Dim sSelect As String = _
"SELECT * FROM Contacts" & _
" WHERE DataSetID = @DataSetID AND ID >= @FirstID AND ID <= @LastID ORDER BY ID"
Dim dsDBFiles As New DataSet()
Dim cmd As New SqlClient.SqlCommand(sSelect, m_connection)
cmd.Parameters.Add("@FirstID", SqlDbType.Int).Value = nFirstID
cmd.Parameters.Add("@LastID", SqlDbType.Int).Value = nLastID
Dim daTable As New SqlClient.SqlDataAdapter(cmd)
Dim bldr As New SqlClient.SqlCommandBuilder(daTable)
daTable.Fill(dsDBFiles, sTable)
Dim tbl As DataTable = dsDBFiles.Tables(sTable)
Dim rdr As New Data.DataTableReader(dsFiles.Tables(0))
dsDBFiles.Load(rdr, LoadOption.Upsert, tbl)
daTable.Update(dsDBFiles, sTable)
Is there way to achieve this upsert functionality without retrieving the records ? I am using SQL Server 2005. I heard there is a way to use the sqladapter to do this, without running the select statement.
I am trying to speed up this process. Any suggestions?
Cheers.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
INSERT 部分是一回事 - 插入新行不是问题。
如果要更新现有行，则需要执行以下操作：将
Added添加它们，因此它们将是由 INSERT 语句处理）.SetModified()将其 RowState 设置为modified。现在，UPDATE 语句将选取这些并将它们应用到数据库。当然，您还需要在 SqlDataAdapter 上设置
UpdateCommand，并且您需要确保 SQL UPDATE 语句它的工作方式是仅比较主键以匹配要更新的行。这样，您应该能够将修改的行添加到数据集中并更新它们，而无需首先检索它们。
马克
The INSERT part is one thing - inserting new rows is not a problem.
If you want to update existing rows, you'll need to do the following things:
Added, so they would be handled by the INSERT statement).SetModified()on those rows to set their RowState tomodified. Now, the UPDATE statement will pick those up and apply them to the databaseOf course, you'll also need to set the
UpdateCommandon your SqlDataAdapter, and you'll need to make sure the SQL UPDATE statement works in such a way that it only compares e.g. the primary key to match up rows to be updated.With this, you should be able to add modified rows to your DataSet and update them without ever retrieving them in the first place.
Marc