469,625 Members | 1,171 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

vb6's recordset replacement in vb.net?

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?

--
Endy
Jun 27 '08 #1
11 4896
endy_tj wrote:
>
Is there any other way? Or should I just bite the bullet and build my
own command generator?
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.
Jun 27 '08 #2
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

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #3
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" <en*****@lycos.comschreef in bericht
news:62**********************************@i36g2000 prf.googlegroups.com...
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?

--
Endy
Jun 27 '08 #4
On Jun 16, 10:00*pm, "Steve Gerrard" <mynameh...@comcast.netwrote:
endy_tj wrote:
Is there any other way? Or should I just bite the bullet and build my
own command generator?

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 :P

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.
Jun 27 '08 #5
On Jun 16, 11:25*pm, "Cor Ligthert[MVP]" <notmyfirstn...@planet.nl>
wrote:
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" <endy...@lycos.comschreef in berichtnews:62**********************************@i 36g2000prf.googlegroups.com...
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?
--
Endy
Jun 27 '08 #6
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" <en*****@lycos.comschreef in bericht
news:a0**********************************@d19g2000 prm.googlegroups.com...
On Jun 16, 11:25 pm, "Cor Ligthert[MVP]" <notmyfirstn...@planet.nl>
wrote:
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" <endy...@lycos.comschreef in
berichtnews:62**********************************@i 36g2000prf.googlegroups.com...
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?
--
Endy
Jun 27 '08 #7
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

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #8
On Jun 17, 12:41*am, Rich P <rpng...@aol.comwrote:
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?
Jun 27 '08 #9
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
Jun 27 '08 #10
endy_tj wrote:
>
Thanks to Steve, Rich, and Cor, I have created a function to save
datatable:
(snip)
>
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.
Nice! Now a couple of comments. :)

- - - - -
First, I think I would explicitly close the new connection created to build the
adapter.
conn = New
Odbc.OdbcConnection(Trans.Connection.ConnectionStr ing)
You are changing the connection afterwards to use Trans.Connection, so conn
probably closes on its own when it goes out of scope, but adding conn.Close or
conn.Dispose might be a good idea.

- - - -
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.

You could get around this by first retrieving all the column names from the
table, by adding a few lines:
lsSQL = "select * from " & CleanTableName & " where 1 = 2"
adp = New Odbc.OdbcDataAdapter(lsSQL, conn)
Dim dt As New DataTable
adp.Fill(dt)

Using dt.Columns, you could now loop through the column names, to see which ones
are included in the passed in Table. Any that are go into a string of the form
"col1, col2" etc. Then continue as before:

lsSQL = "select " & strColumns & " from " & CleanTableName & "
where 1 = 2"
adp = New Odbc.OdbcDataAdapter(lsSQL, conn)
' and build the commands on that...

- - - - -
The nice thing about have a class like this is that you can continue to develop
it at your leisure, and you have a centralized way of handling updates
throughout the programs that use it.
Jun 29 '08 #11
On Jun 30, 12:39*am, "Steve Gerrard" <mynameh...@comcast.netwrote:
endy_tj wrote:
Thanks to Steve, Rich, and Cor, I have created a function to save
datatable:

(snip)
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.

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!
Jun 30 '08 #12

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Brian Parker | last post: by
reply views Thread by Dot Netizen | last post: by
4 posts views Thread by Terry Diederich | last post: by
3 posts views Thread by Lumpierbritches | last post: by
2 posts views Thread by slinky | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.