ExamGuru IT/.Net Guru : DataSet Questions on ASPFriends.com 'aspngdata' list

.NETGURU

DataSet Questions

Messages

Related Types

This message was discovered on ASPFriends.com 'aspngdata' list.

Jordan Breckenridge

I have created a dataset that represents about 11 database tables and their
relationships. I've then generated a .cs class to access the dataset. I've
wrapped the dataset class with a data access class that encapsulates and
provides a level of abstraction.

Its worked great for selects, but I'm having trouble doing updates.
Specifically, I'm trying to use a command builder to generate insert,
update, and delete commands. In the case of an insert, I can see my rows
successfully added to the datatable, but no matter what I've tried, the data
adapter won't insert the row into the physical database table. I don't get
an error or exception and I'm not sure what logs to check to figure out
where its going. I haven't tried an update or delete yet nor have I tried a
stored procedure.

Here are my questions:
(1) Is there anything I have to do populate parameters on my data adapter if
I'm using a command builder?
(2) Is there a way that I can debug into my adapter or stored procedure to
figure out what's happening?
(3) Is it best to keep my data adapters around in my data abstraction class
and just invoke the appropriate Insert(), or Update() method as appropriate?
Or, is it better just to instantiate them as required in the appropriate
method? I was trying to avoid this because of the amount of code to setup
and configure the adapter. It just seems easier to keep the data adapter
around and access it as required... Which, leads into my next question.
(4) Will a data adapter automatically open the connection that it is
associated with if it is closed? Or, should I explictly open and close a
connection each time I use a data adapter?

Any opinions would be apprecaited.
JB

Jordan

Reply to this message...

Michael Pizzo

What happens when you try to do the Update()? Do you get an exception,
or does it appear to succeed, but with no rows inserted?

It's hard to answer what might be going on without seeing your code.
The most common problem I've seen in people trying to update data from
the dataset to the database is that they call AcceptChanges() on the
table or dataset prior to calling Update(), thinking that this is a
necessary pre-update operation. In fact, it is the opposite; calling
AcceptChanges on a row marks the row as UnModified, so the DataAdapter
skips over it.

Also, note that you will generally use a separate DataAdapter to update
each DataTable in your DataSet.

Other responses embedded below. HTH,

-Mike

-----Original Message-----
From: Jordan Breckenridge [mailto:Click here to reveal e-mail address]=20
Sent: Wednesday, March 13, 2002 5:22 PM
To: aspngdata
Subject: [aspngdata] DataSet Questions

I have created a dataset that represents about 11 database tables and
their
relationships. I've then generated a .cs class to access the dataset.
I've
wrapped the dataset class with a data access class that encapsulates and
provides a level of abstraction.

Its worked great for selects, but I'm having trouble doing updates.
Specifically, I'm trying to use a command builder to generate insert,
update, and delete commands. In the case of an insert, I can see my rows
successfully added to the datatable, but no matter what I've tried, the
data
adapter won't insert the row into the physical database table. I don't
get
an error or exception and I'm not sure what logs to check to figure out
where its going. I haven't tried an update or delete yet nor have I
tried a
stored procedure.

Here are my questions:
(1) Is there anything I have to do populate parameters on my data
adapter if
I'm using a command builder?
[Mike] No, the commandbuilder will configure your parameters to point to
the relevant columns of your datatable.

(2) Is there a way that I can debug into my adapter or stored procedure
to
figure out what's happening?
[Mike] You can register for the RowUpdating event on the DataAdapter in
order to see what rows the DataAdapter tries to update, what commands it
tries to use for them, etc.

(3) Is it best to keep my data adapters around in my data abstraction
class
and just invoke the appropriate Insert(), or Update() method as
appropriate?
Or, is it better just to instantiate them as required in the appropriate
method? I was trying to avoid this because of the amount of code to
setup
and configure the adapter. It just seems easier to keep the data adapter
around and access it as required... Which, leads into my next question.
[Mike] Not sure I understand what you mean by "invoke the appropriate
Insert() or Update() method as appropriate. Are you calling Execute()
on the Insert/Update/Delete commands directly? In this case you would
have to set the parameter values to the individual row values.
Generally, you would set the Insert/Update/Delete commands on the
DataAdapter and then call Update() on the DataAdapter which would use
the appropriate command(s) to do the update.

(4) Will a data adapter automatically open the connection that it is
associated with if it is closed? Or, should I explictly open and close a
connection each time I use a data adapter?
[Mike] The DataAdapter will automatically open the connection, if it is
not already opened, and will close it when its finished if it opened it.
Both the SqlClient and OleDb .NET Data Providers support connection
pooling, so it is efficient to open/close the connection (open just
selects a connection from the pool, and close returns it to the pool...)

Any opinions would be apprecaited.
JB

Jordan

| [aspngdata] member Click here to reveal e-mail address =3D YOUR ID
| http://www.asplists.com/asplists/aspngdata.asp =3D JOIN/QUIT
| http://www.asplists.com/search =3D SEARCH Archives

Reply to this message...

Jordan Breckenridge

Mike,

Thanks for your response.

I don't get an exception, it appears to succeed and nothing is returned from
the DataAdapter.Update() method. The table remains untouched. I invoke
AcceptChanges() after I attempt the update so the rowstate is correct at the
time of the update. I have also created separate DataAdapters for each table
in the dataset (the various Insert, Update, and Delete commands are built
using CommandBuilders right now).

I tried adding event handlers and they don't even fire (although there are
no exceptions generated). I set breakpoints inside the event handlers
themselves to validate and.

The following is a code snippet. I am using strongly typed datasets and
therefore relying on the subclassed definitions provided by the generated
DataSet and not by DataTable and DataSet classes respectively.

---
insert (_sFirstName,_sLastName, _sEmailAddress)
{
myNameSpace.SubscriberDataSet.tblSubscriberDataTable myTable = new
myNamespace.SubscriberDataSet.tblSubscriberDataTable();
myTable.AddtblSubscriberRow(_sFirstName,_sLastName, _sEmailAddress,
System.DateTime.Now);
daSubscriber.RowUpdating += new SqlRowUpdatingEventHandler(
OnRowUpdating );
daSubscriber.RowUpdated += new SqlRowUpdatedEventHandler(
OnRowUpdated );

int i = daSubscriber.Update(mySubscribers,"tblSubscriber");
myTable.AcceptChanges();

daSubscriber.RowUpdating -= new SqlRowUpdatingEventHandler(
OnRowUpdating );
daSubscriber.RowUpdated -= new SqlRowUpdatedEventHandler(
OnRowUpdated );
}

----

You referred to a .Execute() method on the DataAdapter. I could only find
.Update() method. Perhaps I misunderstood. I've ensured that the Insert,
Update, and Delete properties point to the appropriate CommandBuilder
results.

If I use the CommandBuilder do I need to set the CommandType of anything?
Should it be set to text?

The only other thing that I could think of is that I instantiate the
DataAdapter (and the DataSet) in the constructor of this object. I'm not
sure how that might make a difference, but perhaps you know. Here is the
high level class structure.

Class Subscribers

Subscribers()
- instantiate DataSet and store it as a protected member attribute
- instantitate DataAdapter and store it as a protected member
attribute
- set Select statement to Select property; Insert, Update, Delete
properties to CommandBuilders
- Fill the adapter

Insert (name, email, etc.) -- the code snippet from above
- get DataSet & DataTable
- Add row to DataTable
- get DataAdapter
- invoke DataAdapter.Update(DataSet, tablename)

Any thoughts would be appreciated.

Regards,
Jordan

"Michael Pizzo" <Click here to reveal e-mail address> wrote in message
news:614484@aspngdata...

What happens when you try to do the Update()? Do you get an exception,
or does it appear to succeed, but with no rows inserted?

It's hard to answer what might be going on without seeing your code.
The most common problem I've seen in people trying to update data from
the dataset to the database is that they call AcceptChanges() on the
table or dataset prior to calling Update(), thinking that this is a
necessary pre-update operation. In fact, it is the opposite; calling
AcceptChanges on a row marks the row as UnModified, so the DataAdapter
skips over it.

Also, note that you will generally use a separate DataAdapter to update
each DataTable in your DataSet.

Other responses embedded below. HTH,

-Mike

-----Original Message-----
From: Jordan Breckenridge [mailto:Click here to reveal e-mail address]
Sent: Wednesday, March 13, 2002 5:22 PM
To: aspngdata
Subject: [aspngdata] DataSet Questions

I have created a dataset that represents about 11 database tables and
their
relationships. I've then generated a .cs class to access the dataset.
I've
wrapped the dataset class with a data access class that encapsulates and
provides a level of abstraction.

Its worked great for selects, but I'm having trouble doing updates.
Specifically, I'm trying to use a command builder to generate insert,
update, and delete commands. In the case of an insert, I can see my rows
successfully added to the datatable, but no matter what I've tried, the
data
adapter won't insert the row into the physical database table. I don't
get
an error or exception and I'm not sure what logs to check to figure out
where its going. I haven't tried an update or delete yet nor have I
tried a
stored procedure.

Here are my questions:
(1) Is there anything I have to do populate parameters on my data
adapter if
I'm using a command builder?
[Mike] No, the commandbuilder will configure your parameters to point to
the relevant columns of your datatable.

(2) Is there a way that I can debug into my adapter or stored procedure
to
figure out what's happening?
[Mike] You can register for the RowUpdating event on the DataAdapter in
order to see what rows the DataAdapter tries to update, what commands it
tries to use for them, etc.

(3) Is it best to keep my data adapters around in my data abstraction
class
and just invoke the appropriate Insert(), or Update() method as
appropriate?
Or, is it better just to instantiate them as required in the appropriate
method? I was trying to avoid this because of the amount of code to
setup
and configure the adapter. It just seems easier to keep the data adapter
around and access it as required... Which, leads into my next question.
[Mike] Not sure I understand what you mean by "invoke the appropriate
Insert() or Update() method as appropriate. Are you calling Execute()
on the Insert/Update/Delete commands directly? In this case you would
have to set the parameter values to the individual row values.
Generally, you would set the Insert/Update/Delete commands on the
DataAdapter and then call Update() on the DataAdapter which would use
the appropriate command(s) to do the update.

(4) Will a data adapter automatically open the connection that it is
associated with if it is closed? Or, should I explictly open and close a
connection each time I use a data adapter?
[Mike] The DataAdapter will automatically open the connection, if it is
not already opened, and will close it when its finished if it opened it.
Both the SqlClient and OleDb .NET Data Providers support connection
pooling, so it is efficient to open/close the connection (open just
selects a connection from the pool, and close returns it to the pool...)

Any opinions would be apprecaited.
JB

Jordan

| [aspngdata] member Click here to reveal e-mail address = YOUR ID
| http://www.asplists.com/asplists/aspngdata.asp = JOIN/QUIT
| http://www.asplists.com/search = SEARCH Archives

Reply to this message...

Lubomir Simeonov

Hi Jordan, i wonder if you solved the problem with the SqlCommandBuilder.
I also have the same problem. I have 8 tables in one dataset. I use the SqlCommandBuilder to
build the INSERT, UPDATE, DELETE commands. Then when i execute the SqlDataAdapter.Update method
although there are changes in the DataTable nothing happens in the database.

I tried to find the problem, but so far i couldn't.
Here is what i found.
In the DataTable right before i execute the Update method, i have row with the Modified RowState. But when the method call returns the RowState is Unchanged and the method returns 1 (which is the number
of the changed rows).
I want to note that i do not call AcceptChanges.
I added handlers to the RowUpdating and RowUpdated events, and here is the result:
in the RowUpdating event the e.Command is null, which means that the command is missing, although
i pass DataAdapter in the constructor of the SqlCommandBuilder. If i explicitly set the SqlDataAdapter.UpdateCommand to SqlCommandBuilder.GetUpdateCommand(), after the execution of the Update method of the DataAdapter, the command is changed and the UPDATE COMMAND i like the following:
UPDATE <table_name> SET WHERE. As you can see after the SET there is nothing.

And the strangest thing in my tests, is that the tables that do not have child relations are updated.

Please if you found the problem, or my notes help you do this tell me how!!!

--------------------------------
From: Lubomir Simeonov

Reply to this message...

Lubomir Simeonov

I found the reason for my problem. I don't know if yours is the same.
The reason is very simple:
- The Current and Original values of the Modified row are the same, so
i suppose the SqlDataAdapter is smart enough to find this and not generate
the UPDATE command.

But anyway it is misleading that the RowState is Modified and after the Update
nothing goes to the database.

--------------------------------
From: Lubomir Simeonov

Reply to this message...

	System.Data.CommandType
	System.Data.Common.DataAdapter
	System.Data.DataSet
	System.Data.DataTable
	System.Data.SqlClient.SqlCommandBuilder
	System.Data.SqlClient.SqlDataAdapter
	System.Data.SqlClient.SqlRowUpdatedEventHandler
	System.Data.SqlClient.SqlRowUpdatingEventHandler
	System.DateTime

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification