vb.net 使用 BindingSource 数据更新数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26576839/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating database with BindingSource data
提问by Junior Zancan
This is my first post in here, but this forum already helped me a lot.
这是我在这里的第一篇文章，但这个论坛已经帮了我很多。
First, sorry for my English, i'm from Brazil and i'm trying to write without a translator.
首先，对不起我的英语，我来自巴西，我想在没有翻译的情况下写作。
I'm developing a software for a supermarket, but i'm having problems with the connection to the database. I'm trying to make all the connections and transactions programmatically (DataSets, BindingSources and so).
我正在为一家超市开发一个软件，但是我在连接到数据库时遇到了问题。我正在尝试以编程方式建立所有连接和事务（DataSet、BindingSources 等）。
I've already managed to connect with SQL Server Express 2008, using a Function ("consulta") inside a Module ("db"):
我已经设法使用模块（“db”）中的函数（“consulta”）与 SQL Server Express 2008 连接：
Dim ad As SqlDataAdapter = New SqlDataAdapter
Function consulta(ByVal tabela As String, Optional opt As Boolean = False, Optional optparam As String = "") As DataSet
Dim ds As New DataSet
Try
Dim connstring As String = "Data Source=NOTEBOOK\SQLEXPRESS;Initial Catalog=SysMarket;Persist Security Info=True;User ID=admin;Password=XXXXXX"
Dim conObj As New SqlConnection(connstring)
Dim sql As String
If opt = True Then
sql = "SELECT * FROM " & tabela & " " & optparam
Else
sql = "SELECT * FROM " & tabela
End If
Dim cmd As SqlCommand = New SqlCommand(sql, conObj)
ad.SelectCommand = cmd
conObj.Open()
ad.Fill(ds, tabela)
ad.Dispose()
cmd.Dispose()
conObj.Close()
Return ds
Catch ex As Exception
MessageBox.Show("Erro na consulta" & vbCrLf & ex.InnerException.ToString, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
ds.Clear()
Return ds
End Try
End Function
And this is a part of the main code where I make a SelectQuery and put into a BindingSource:
这是我创建 SelectQuery 并放入 BindingSource 的主要代码的一部分：
Dim ds As DataSet = db.consulta("departamentos")
Private Sub cad_departamento_Load(sender As Object, e As EventArgs) Handles MyBase.Load
BindingSource1.DataSource = ds
BindingSource1.DataMember = "departamentos"
TextBox1.DataBindings.Add("Text", BindingSource1, "id")
TextBox2.DataBindings.Add("Text", BindingSource1, "departamento")
End Sub
But my problem is when I have to Update the database, by adding, editing or deleting some item from BindingSource. Because in the Module I've closed the connection to the SQL Server. So I will need reopen this connection and then, somehow "read" the DataSet with the change and Update the database?
但我的问题是当我必须通过从 BindingSource 添加、编辑或删除某些项目来更新数据库时。因为在模块中我关闭了与 SQL Server 的连接。所以我需要重新打开这个连接，然后以某种方式“读取”带有更改的数据集并更新数据库？
Someone could explain this to me or show me a example?
有人可以向我解释这一点或向我展示一个例子吗？
Thank you.
谢谢你。
回答by jmcilhinney
You will use a data adapter to save the data, just as you used one to retrieve the data. You will have to create an InsertCommandif you want to insert new records, an UpdateCommandif you want to update existing records and a DeleteCommandif you want to delete existing records. You can write those yourself or, if the conditions are right, you can use a command builder to do it for you.
您将使用数据适配器来保存数据，就像您使用数据适配器检索数据一样。您必须创建一个InsertCommand是否要插入新记录、一个UpdateCommand是否要更新现有记录以及一个DeleteCommand是否要删除现有记录。您可以自己编写这些，或者，如果条件合适，您可以使用命令生成器为您完成。
If your query is based on a single table and you want to insert/update all the columns you retrieve back to that same table then a SqlCommandBuildermay be your best bet. You simply pass in the query and the command builder will use it to generate the action commands. That gives you limited flexibility but if you're just doing single-table operations then you don't need that added flexibility.
如果您的查询基于单个表，并且您希望将检索到的所有列插入/更新回同一个表，那么 aSqlCommandBuilder可能是您最好的选择。您只需传入查询，命令生成器将使用它来生成操作命令。这为您提供了有限的灵活性，但如果您只是在执行单表操作，那么您不需要额外的灵活性。
Such a method might look something like this:
这样的方法可能看起来像这样：
Public Sub SaveChanges(tableName As String, data As DataSet)
Dim query = "SELECT * FROM " & tableName
Using adapter As New SqlDataAdapter(query, "connection string here")
Dim builder As New SqlCommandBuilder(adapter)
adapter.Update(data, tableName)
End Using
End Sub
回答by Junior Zancan
I did what you said, but when I open the Form again, the new data are not there.
我按照你说的做了，但是当我再次打开表单时，新数据不存在。
I made some changes in the code, perhaps because it did not work
我对代码进行了一些更改，可能是因为它不起作用
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
BindingSource1.EndEdit()
ds.AcceptChanges()
db.SaveChanges("departamentos", "INSERT INTO departamentos VALUES('', " & TextBox2.Text & ")", ds)
ds = db.consulta("departamentos")
End Sub
And the code in the Module
以及模块中的代码
Function SaveChanges(tableName As String, query As String, data As DataSet)
Using adapter As New SqlDataAdapter(query, "Data Source=NOTEBOOK\SQLEXPRESS;Initial Catalog=SysMarket;Persist Security Info=True;User ID=admin;Password=XXXXX")
Dim builder As New SqlCommandBuilder(adapter)
adapter.Update(data, tableName)
Return True
End Using
End Function
