Moving from recordsets to datasets...

[hurricane_number_one]

Been writing VB6 for years and am now getting up to speed with .NET.
In VB6 I used to create classes for each logical grouping of tables,
then have a recordset in that class for each table. Then I'd have
functions that would populate the different recordsets. I had a
function like this: "getRecordset($SQLStr) as ADODB.Recordset" that
would take care of the connection, opening the recordset, then just
return a disconnected recordset from that query. Then I had a
function called "updateRecordset(RS as ADODB.Recordset)" which just
stored the changes. Everything was self contained and very easy to
deal with.

Now in VB.NET, I see that the dataset is essentially doing something
very similar to what I was doing in VB6. A couple problems I'm having
though.

1) When I finished making changes to a recordset in VB6, I'd just call
my updateRecordset function, which would reconnect the recordset, call
RS.UpdateBatch and take care of all the inserts, deletes, updates for
me. In VB.NET it looks like I have to use the same SqlDataAdapter
that I used to read the data to update the data in order to save my
changes, and I have to write INSERT and UPDATE code for it? Am I
correct, or is there any way to use this the same way as I was using
recordsets?

2) With recordsets, I could specify the read type when I opened the
recordset, which allowed me to set this programatically as needed but
I would always be dealing with the RecordSet type regardless of how I
read the data. With VB.NET, I have to use a dataReader if I want read
only. Is there anyway to programatically set a dataTable to read only
forward-looking, so that it works the same way as a dataReader and I
only have to deal with one data type in my code?

3) I looked at the typed-datasets, but I really hate using wizards or
GUIs to do my code. I like being able to see it all at once and copy
and paste functions if needed. I also need to be able to change the
connection string on the fly, because I will be using different
servers and database names. Is there any way to create typed-datasets
programatically?

Any suggestions on guides to help me design a good back end for my
program?

 

[Cowboy \(Gregory A. Beamer\)]

1) When I finished making changes to a recordset in VB6, I'd just call
my updateRecordset function, which would reconnect the recordset, call
RS.UpdateBatch and take care of all the inserts, deletes, updates for
me. In VB.NET it looks like I have to use the same SqlDataAdapter
that I used to read the data to update the data in order to save my
changes, and I have to write INSERT and UPDATE code for it? Am I
correct, or is there any way to use this the same way as I was using
recordsets?

You do not have to write the UPDATE and INSERT code. It can be inferred from
the original command you have created.
http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/UpdateDataFromDB.aspx

Note there are other samples on this site.

This will not work for complex queries that join tables, etc., but that did
not work in ADO either.

If you are going this route, you may end up with some concurrency issues.
For that, I would find Dino Espositos articles on concurrency on MSDN
(http://msdn.microsoft.com).

2) With recordsets, I could specify the read type when I opened the
recordset, which allowed me to set this programatically as needed but
I would always be dealing with the RecordSet type regardless of how I
read the data. With VB.NET, I have to use a dataReader if I want read
only. Is there anyway to programatically set a dataTable to read only
forward-looking, so that it works the same way as a dataReader and I
only have to deal with one data type in my code?

No. Underneath the hood, a DataReader is used as a firehose cursor to fill a
DataSet. Once it is in the DataSet, it is portable.

3) I looked at the typed-datasets, but I really hate using wizards or
GUIs to do my code. I like being able to see it all at once and copy
and paste functions if needed. I also need to be able to change the
connection string on the fly, because I will be using different
servers and database names. Is there any way to create typed-datasets
programatically?

You can use the GUI to design your dataset without issue. If you do not want
to use the TableAdapter, then do not use it.

If you want to create a typed DataSet without using the GUI, that is fine.
Here is some code (not encapsulated):

Dim sql As String = "{SQL String Here}"
Dim connString As String = "{Connection String here}"

Dim conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(sql, conn)
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet("{DataSet Name Here}")

da.TableMappings.Add("Table", "{Name of DataTable #1}")

'Repeat for others
'da.TableMappings.Add("Table1", "XXX")

conn.Open()
da.Fill(ds)

ds.SaveXml("{file path to save to}.xsd")

Then open VS, and add the xsd file to your project. That will create the
DataSet for you. After this, you can reuse this code like so:

Dim sql As String = "{SQL String Here}"
Dim connString As String = "{Connection String here}"

Dim conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(sql, conn)
Dim da As New SqlDataAdapter(cmd)

'This is the part that has changed
Dim ds As New MyCreatedDataSet()

da.TableMappings.Add("Table", "{Name of DataTable #1}")

'Repeat for others
'da.TableMappings.Add("Table1", "XXX")

conn.Open()
da.Fill(ds)

You now return a MyCreatedDataSet rather than a DataSet. And, you will have
full intellisense on the DataSet.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************

 

[Cor Ligthert [MVP]]

Hurricane,

Only as additions to the nice reply from George,

Don't compare a Recordset with a Dataset

You can better compare the Recordset with a DataTable with the main
differences that a DataTable can be wrapped into a DataSet and is therefore
serializable plus that a DataTable is working disconnected and a RecordSet
connected.

Cor