Question Beginner Confusion about Data Readers

[MaxSmart]

I am a VERY advanced VBA programmer making my way into VB.net.

So far, I have set up a MyDatabase.mdf database file in visual studio, and followed tutorials to make pretty forms that allow the user to view and update the data.

Now what I want to do is manipulate the data with VB code - go through all the records in tblTable and do complex algorithms that can't be done with SQL statements, and update the data in tblTable to reflect the calculated values. It seems like I can do this with code like this:
x = MyDatabaseDataSet.tblTable.rows(1).Field(Of String)("Field1")
x=ComplexCalculation(x)
MyDatabaseDataSet.tblTable.rows(1).SetField(Of String)("Field1",x)

But, from what I'm reading, the proper way to do this is to
1. Create a DataTable variable,
2. Load the table records I want to manipulate into that,
3. Edit the records in the DataTable,
4. Load the DataTable back into my database

I've found hundreds of resources telling me how to do #1 and #3, but I can't figure out how to do #2 and #4. I can't even guess how #4 would work - do you overwrite all the data in the table? Isn't this terribly inefficient, and wouldn't it cause issues if other people were accessing the database at the same time? (eg, this function would overwrite changes other users had made between steps #2 and #4)

Hope I don't sounds like an idiot.

Thanks!

 

[jmcilhinney]

Point 1 is not quite correct. You don't create variables. You declare variables and create objects.

'Declare the variable.
Dim table As DataTable

'Create the object and assign it to the variable.
table = New DataTable

That can be condensed to this:

'Declare the variable, create the object and assign it to the variable.
Dim table As DataTable = New DataTable

or even more so to this:

'Declare the variable, create the object and assign it to the variable.
Dim table As New DataTable

Point 2 can be done either using a DataReader or a DataAdapter. Use a DataReader if you don;t need to save changes back to the database but, if you do, use a DataAdapter.

It looks like you've got point 3 covered.

Point 4 involves using the aforementioned DataAdapter. You call Fill to populate a DataTable and Update to save the changes back to the database. Check this out for examples of this an other common ADO.NET scenarios:

Retrieving and Saving Data in Databases

Now, the DataAdapter in that example uses a "last in wins" approach. As you suggest, that could lead to one user overwriting the changes that were saved by another. To avoid that, ADO.NET uses "optimistic concurrency". I'm not going to go into details but, basically, it involves comparing the current data in the database to the original data you retrieved and only saving if they are the same. If they are different, your app must handle the resulting error and proceed appropriately. What's appropriate will vary from app to app. For more, search MSDN and the web for information about optimistic concurrency and how it is implemented in ADO.NET. Note that CommandBuilders use optimistic concurrency by default.

 

[MaxSmart]

Point 2 can be done either using a DataReader or a DataAdapter. Use a DataReader if you don;t need to save changes back to the database but, if you do, use a DataAdapter.

That's precisely the part I'm lost at! How do I use a dataAdapter?
(Thanks!)

 

[MaxSmart]

Is this myDatabaseDataSet.xsd a set of intermediate tables, into which data is loaded from the database, gets worked on, then gets uploaded back into the database? Or am I misunderstanding how these things work?

 

[jmcilhinney]

So, you have a typed DataSet. In that case, you use a TableAdapter to populate a DataTable and the same TableAdapter to save the changes back to the database. As with a DataAdapter, Fill does the retrieving and Update does the saving.

 

[MaxSmart]

So, you have a typed DataSet. In that case, you use a TableAdapter to populate a DataTable and the same TableAdapter to save the changes back to the database. As with a DataAdapter, Fill does the retrieving and Update does the saving.

Thanks! I think I get it. How do I execute a SQL UPDATE statement? Do I need another reference included? I assume I can do SQL on this database connection since myDatabase.mdf if I am not mistaken is a SQL database format?

 

[jmcilhinney]

SQL is not a database. It is a language. It is Structured Query Language, a language created with the intention of querying data sources. Pretty much all modern databases support SQL in one form or another. Such databases include MySQL, SQLite, SQL Server, Oracle and Access. An MDF file is a Microsoft SQL Server data file.

Your typed DataSet already contains SQL code in the TableAdapters. When you use the Data Source wizard to generate a typed DataSet, a DataTable is created to correspond to each database table that you select and a corresponding TableAdapter is also created. The DataTable is the local store for the data and the TableAdapter is used to retrieve data and save changes. You call Fill to retrieve data by executing the SELECT statement in the adapter's SelectCommand and you call Update to save changes by executing the DELETE, INSERT and UPDATE statements in the adapter's DeleteCommand, InsertCommand and UpdateCommand respectively. You can view all the SQL code and corresponding parameters in the Properties window of the DataSet designer.

You are free to add new TableAdapters if you want to execute a query whose result set doesn't match the schema of any existing DataTable. You can also add queries to existing TableAdapters when the result set schema matches an existing DataTable but you want to use, for instance, one or more parameters to filter the data.