Update from Dataset without original adaptor?

[pmeloy]

I'm playing with VB2010 express and MS SQLServer Express

I've made a small app that uses a Module function to retrieve a dataset then I mess around and change some values in the dataset. Now I want to update the database but all the examples I can find use the original dataadapter to do the update. My original dataadapter is gone so how do I do an update? I can't see any way to create a new datadapter without also creating a new dataset...

Pseudo code so you know what I mean (don't have program handy so don't sweat apparent mistakes in coding)

Function DoQuery(ByRef myDataset as Dataset, myQuery as string) as boolean

	Dim result As Boolean = false

	myConnString = GetConnString()

	Using myConn as new SqlConnection(myConnString)
		myConn.Open()
		Using myAdapter as new SqlDataAdapter(myQuery,myConn)
			myConn.Close()
			myAdapter.Fill(myDataset)
			result = true
		End Using
	End Using
	return result
End Function

Any ideas? Or do I have to drop "Using" and make the adapter and dataset global?

Edit: Just realized... I should give the dataadapter the same scope as the dataset and pass both byref to the DoQuery.. I think. Hmmm, maybe I'll just do everything on each form since after the dataset and adapter there's not much more to doing the queries and I'm not saving a whole lot of coding by using a function in a module.

[jmcilhinney]

Firstly, you shouldn't be passing anything ByRef. With classes, you only pass ByRef if you intend to assign a new value to the parameter inside the method and you want that change to affect the caller as well. If you're not doing that, which is a minority of cases, then you don't pass ByRef. ByVal is the default for good reason.

As for your question, you certainly can pass in the DataSet and the data adapter but creating a new data adapter is completely unrelated to the DataSet so you certainly can create a new one. If you want to create new data adapter then just create a new data adapter. It's that simple.

While it generally makes sense to use the same data adapter to retrieve and save data when your data access code is mixed up with your business logic and presentation code, i.e. all your code is in the form, it's very common for data access code to be extracted out into its own classes or even its own project. In such cases, you will most likely not be using the same data adapter in both cases. That's fine; just write a method that creates, configures and returns a data adapter and then call that method once when you need to get data and once when you need to save the changes. It won't be the same data adapter object but it will contain all the same SQL code and that's what does the work of saving the data. Here's a simple example:

Public Function GetData() As DataTable
    Using adapter = GetDataAdapter()
        'Create a new table.
        Dim table As New DataTable
 
        'Populate the table with data from the database.
        adapter.Fill(table)
 
        'Return the populated table to the caller.
        Return table
    End Using
End Function
 
Public Sub SaveChanges(table As DataTable)
    'The data adapter saves the changes and the command builder generates the INSERT, UPDATE and DELETE commands to do it.
    Using adapter = GetDataAdapter(),
          builder = New SqlCommandBuilder(adapter)
        'Escape all column names in case of reserved words or special characters.
        builder.QuotePrefix = "["
        builder.QuoteSuffix = "]"
 
        'Save the changes back to the database.
        adapter.Update(table)
    End Using
End Sub
 
Private Function GetDataAdapter() As SqlDataAdapter
    'Create a new data adapter with a query and a connection string.
    Dim adapter As New SqlDataAdapter("SELECT * FROM MyTable",
                                      ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
 
    'Retrieve primary key information from the database to allow a command builder to generate UPDATE and DELETE commands.
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
 
    Return adapter
End Function

Note that the calling code never sees the data adapter or any of the other data access objects. It simply asks for data and receives it in a DataTable, then passes that DataTable back and asks that the changes be saved. What happens inside the data access module is of no concern to that calling code.

[pmeloy]

Ah, that's what was throwing me off. I never saw an adapter created without it also doing a new query. That made me think I couldn't use update with an existing dataset because the adapter already had a different one.

Would I be correct to assume that the new adapter's query has to be the same as the one used for the existing dataset? Or can I just select one row from (each) table in order to get the schema then apply the update? Hmmm, it seems to me it would need all the same rows in order to match the updated rows.

So if the original query was

SELECT persons.id, persons.name, persons.title_id, titles.title 
FROM persons,titles
WHERE titles.id = persons.title_id

then I'd have to pass that same query to GetAdapter() for the updating adapter so it has the same data?

[jmcilhinney]

The data adapter doesn't need a query at all if it's going to be saving changes and not retrieving data. When you call Fill it's the SelectCommand that gets executed and when you call Update it's the InsertCommand, UpdateCommand and DeleteCommand that get executed as required. If you create those three commands yourself then you don't need the SelectCommand at all to save data.

I included the SelectCommand in this case because that's what the command builder uses as a basis for generating the other three. Only the columns specified in the SelectCommand will be included in the InsertCommand and the UpdateCommand so any columns that you want to be able to save must be specified in the query.

[pmeloy]

I was playing with this and realized the SaveChanges scheme never calls .fill() so the select command is never executed but that .MissingSchemaAction is used. So I supplied a select string with all the tables from the original query and everything worked great.

I'll try again but this time only use the tables I'm actually updating (my dataset has four tables but only one actually has data changed).

Thanks very much for the help. Next I have to figure out the permissions scheme in sqlserver express. In mysql and postgresql the permissions setup is pretty straightforward but sqlserver's is confusing. Lots of reading ahead 8)