vb6's recordset replacement in vb.net?

[endy_tj]

In vb6 I can use ADO's recordset to retrieve data from database, work
on the data, then send the modifications back to the database.

I use disconnected recordset, and call updatebatch to send updates to
database.

In vb.net, I use datatable to retrieve data from database and work on
the data. The next step, sending the updates to database, is a dilemma
for me.

If I use dataadapter to send updates, then I have to provide insert,
update, delete commands manually.

If I use tableadapter, then I must limit myself to use the matching
strongly typed datatable. Very limiting because I usually do sql join
in my select statement (or sql subquery), display the data on a grid,
let user modify, then save the changes back to database. Using
tableadapter, showing lookup data in transaction data is no longer
easy.

Another way is to use stored procedure, but then I have to use
database that supports stored procedure. I also prefer to keep the
database dumb, and store all the logic in one place, the program.

Is there any other way? Or should I just bite the bullet and build my
own command generator?

 

[Rich P]

The way VS.Net works (VB.Net/C# for this explanation) is that is sort of
creates an in memory database with the objects selected from the server
DB. If you have related tables on the server DB and want to view data
from these tables you would use a select statement as follows in a
dataAdapter:

'--these vars are global
dim da As SqlDataAdapter, ds As Dataset, conn as SqlConnection
---------------------------------------------
Private Sub Form1_Load(...) Handles....
da = New SqlDataAdapter
ds = New Dataset
da.SelectCommand = new SqlCommand
da.SelectComman.Connection = conn
da.InsertCommand = New sqlCommand
da.InsertCommand.Connection = conn
da.InsertCommand.Parameters.Add("@p1",...)
da.UpdateCommand = New SqlCommand
da.UpdateCommand.Connection = conn
da.UpdateCommand.Parameters.Add("@p1",...)
da.SelectCommand.CommandText = "Select * From tbl1"
da.Fill(ds, "tblA")
datagridview1.DataSource = ds.Tables("tblA")
...
End Sub

Private Sub datagridview1CellValue_Changed(...) Handles...
ds.Tables("tblA").Rows(e.RowIndex)(e.ColumnIndex) =
datagridview1.Rows(e.RowIndex).Cells(e.columnIndex).Value

da.Update(ds,"tblA")
End Sub
-----------------------------------------------------

you have to write a specific select statement for the server table that
you want to update or add rows to and the insert/Update statements have
to meet the constraint conditions place on the tables on the server.
Then - to view joined tables just write a simple

"Select t1.* from tbl1 t1 Join tbl2 t2 on t1.ID = t2.ID"

da.Fill(ds, "tblB")

Once you get the hang of ADO.Net you will see that it blows the doors
off classic ADO.


Rich

 

[Cor Ligthert[MVP]]

I did not know that you could update joined datatables with the recordset, I
thought that it was only doing tables.

However if you want to update a datatable using a SQLDataAdapter then the
only thing you need is to do before that update somewhere

dim cmb = new SqlCommandBuilder(TheDataAdapter)

Cor

 

[endy_tj]

I don't use a data adapter or a table adapter. I have 3 methods, one for insert,
update, and delete, that will take any table, and create and run the appropriate
commands. It takes a bit of work to set it up; once you have it, though, that
task is done for good.

OK, no other way around it

These methods of yours, you feed them only connection and datatable or
some more parameters? Does the datatable contain enough metadata to
construct the insert, update, delete command? Would be very glad if
you can give me some hints.

 

[endy_tj]

I did not know that you could update joined datatables with the recordset,I
thought that it was only doing tables.

You can, if you put the table name in the "unique table" property of
the recordset, and your select query contains the primary key of the
table.

However if you want to update a datatable using a SQLDataAdapter then the
only thing you need is to do before that update somewhere

dim cmb = new SqlCommandBuilder(TheDataAdapter)

OK need to try the command builder. Many articles say it is slow and
can only handle simple select statements. If it can't handle simple
subquery like this:

select O.id, O.orderdate,
(select name from customers where id = O.customer_id) as customer_name
from orders O

then it will be as limited as strongly typed datatables, and provides
little help for me.

"endy_tj" <[email protected]> schreef in bericht

In vb6 I can use ADO's recordset to retrieve data from database, work
on the data, then send the modifications back to the database.

I use disconnected recordset, and call updatebatch to send updates to
database.

In vb.net, I use datatable to retrieve data from database and work on
the data. The next step, sending the updates to database, is a dilemma
for me.

If I use dataadapter to send updates, then I have to provide insert,
update, delete commands manually.

If I use tableadapter, then I must limit myself to use the matching
strongly typed datatable. Very limiting because I usually do sql join
in my select statement (or sql subquery), display the data on a grid,
let user modify, then save the changes back to database. Using
tableadapter, showing lookup data in transaction data is no longer
easy.

Another way is to use stored procedure, but then I have to use
database that supports stored procedure. I also prefer to keep the
database dumb, and store all the logic in one place, the program.

Is there any other way? Or should I just bite the bullet and build my
own command generator?

 

[Cor Ligthert[MVP]]

It has the same limites as a strongly typed dataset.

You cannot update joined tables using any adapter, a resultset can exist
from any combinations then of database table even without needed
identifiers.

Cor

"endy_tj" <[email protected]> schreef in bericht

I did not know that you could update joined datatables with the recordset,
I
thought that it was only doing tables.

You can, if you put the table name in the "unique table" property of
the recordset, and your select query contains the primary key of the
table.

However if you want to update a datatable using a SQLDataAdapter then the
only thing you need is to do before that update somewhere

dim cmb = new SqlCommandBuilder(TheDataAdapter)

OK need to try the command builder. Many articles say it is slow and
can only handle simple select statements. If it can't handle simple
subquery like this:

select O.id, O.orderdate,
(select name from customers where id = O.customer_id) as customer_name
from orders O

then it will be as limited as strongly typed datatables, and provides
little help for me.

"endy_tj" <[email protected]> schreef in
bericht

In vb6 I can use ADO's recordset to retrieve data from database, work
on the data, then send the modifications back to the database.

I use disconnected recordset, and call updatebatch to send updates to
database.

In vb.net, I use datatable to retrieve data from database and work on
the data. The next step, sending the updates to database, is a dilemma
for me.

If I use dataadapter to send updates, then I have to provide insert,
update, delete commands manually.

If I use tableadapter, then I must limit myself to use the matching
strongly typed datatable. Very limiting because I usually do sql join
in my select statement (or sql subquery), display the data on a grid,
let user modify, then save the changes back to database. Using
tableadapter, showing lookup data in transaction data is no longer
easy.

Another way is to use stored procedure, but then I have to use
database that supports stored procedure. I also prefer to keep the
database dumb, and store all the logic in one place, the program.

Is there any other way? Or should I just bite the bullet and build my
own command generator?

 

[Rich P]

ADO.Net is quite a bit more sophisticated than classic ADO (does a ton
more stuff). Instead of writing a book on ADO.Net in this post I would
just recommend getting a book on ADO.Net.

Note: Even though you could use the CommandBuilder object to build your
commands - these end up being fairly generic and difficult to
manipulate. With all due respect to people who use the commandbuilder -
most people (myself included) will steer away from it. It is better to
write your own commands.

The big deal with ADO.Net is that the dataAdapter will automatically
create a dataTable for you when you run this statement

da.Fill(ds, "tblwhatever")

da.SelectCommand.CommanText = "Select t1.fld1, t1.fld2, t2.fldx,
t2.fldy, t2.fldz from tbl1 t1 Join tbl2 t2 On t1.ID = t2.ID and t1.Name
= t2.Name Where Not Exists (Select something from tbl3 t3 Where t3.ID =
t1.ID)"

da.Fill(ds, "tblSteve")

so "tblSteve" represents the joins above in memory, and you display the
data in a datagridview (the equivalent of a subform in VB6 or Access -
but 1000+ times more powerful and versatile). Note: If you edit
"tblSteve" you would have to write the appropriate Update statements
behind the datagridview (which has hundreds of events - pick the proper
event) and run da.Update(ds, "tblSteve").

I could go on, but it would be easier to get a book on ADO.Net.


Rich

 

[endy_tj]

ADO.Net is quite a bit more sophisticated than classic ADO (does a ton
more stuff).  Instead of writing a book on ADO.Net in this post I would
just recommend getting a book on ADO.Net.  

Any recommendations for a book that covers the things I intend to do?

 

[Cor Ligthert[MVP]]

Rich

Note: Even though you could use the CommandBuilder object to build your
commands - these end up being fairly generic and difficult to
manipulate. With all due respect to people who use the commandbuilder -
most people (myself included) will steer away from it. It is better to
write your own commands.

In my idea has even William Vaughn changed his opinion of this.

Although I realize me that I seldom use it anymore, is it a fine tool as it
is about one table which has less then 100 columns and is selected by
"Select * from Table".

(But in those situations is Linq to SQL much easier)

Cor

 

[endy_tj]

No ideas on a book, other than doing some of the examples in MSDN, and working
out what is going on.

I have several points, which I will try to make in brief. (PS: turns out that
didn't happen.)

... snip ...

It takes a bit of work to get it all going, but once you have it working for a
couple of rows in one table, it will work for hundreds of rows in dozens of
tables. And you will be driving.

Thanks to Steve, Rich, and Cor, I have created a function to save
datatable:

Public Class AdapterUtility
Private adapters As New Dictionary(Of String,
Odbc.OdbcDataAdapter)

Public Sub Save(ByVal Trans As Odbc.OdbcTransaction, ByVal Table
As DataTable, ByVal TableName As String)
' all columns of the target table must be present
' target columns can be in any order
' extra columns can be inserted anywhere, before target
columns, between target columns, after target columns
' extra column names must not clash with target column names

Dim CleanTableName As String
CleanTableName = LCase(Replace(TableName, " ", ""))

Dim lsSQL As String
Dim adp As Odbc.OdbcDataAdapter
Dim bld As Odbc.OdbcCommandBuilder
Dim conn As Odbc.OdbcConnection

If adapters.ContainsKey(CleanTableName) Then
adp = adapters(CleanTableName)
Else
conn = New
Odbc.OdbcConnection(Trans.Connection.ConnectionString)

lsSQL = "select * from " & CleanTableName & " where 1 = 2"

adp = New Odbc.OdbcDataAdapter(lsSQL, conn)
bld = New Odbc.OdbcCommandBuilder(adp)
adp.DeleteCommand = bld.GetDeleteCommand(True)
adp.InsertCommand = bld.GetInsertCommand(True)
adp.UpdateCommand = bld.GetUpdateCommand(True)
adapters(CleanTableName) = adp
End If

adp.DeleteCommand.Connection = Trans.Connection
adp.InsertCommand.Connection = Trans.Connection
adp.UpdateCommand.Connection = Trans.Connection
adp.DeleteCommand.Transaction = Trans
adp.InsertCommand.Transaction = Trans
adp.UpdateCommand.Transaction = Trans

adp.Update(Table)
End Sub
End Class

With this class, I just have to pass the transaction object, the
datatable, and the name of the table that I want to update, and it
will save the changes. I can do joins, I can add subqueries in the
select clause, and it will ignore the extra columns, as long as I
differentiate the column names, and all the real columns are intact.

I can construct the 'from', 'where', 'order by', etc. however I want,
and I don't have to create the commands and parameters manually. Just
like old times!

Again, thanks guys!

 

[endy_tj]

Nice! Now a couple of comments.

- - - - -
First, I think I would explicitly close the new connection created to build the
adapter.

Yes, forgot that one, and also I have added these lines after
updating:

adp.DeleteCommand.Connection = nothing
...

so the adapter's commands release the connection, so it can be garbage
collected.

Second, the requirement "all columns of the target table must be present" might
be a nuisance in the future. I have a table that stores uploaded BLOBs, for
instance, with a separate title. It is useful to be able to list the titles for
a user, and allow changing the title, without retrieving all the BLOBs. The
BLOBs themselves are only retrieved one at a time, on demand.

Yeah, good point. I forgot about blobs. Thanks!