SqlDataAdaptor problem

[Steven Blair]

Hi,

I have a problem understanding the SqlDataAdaptor and the DataSet.

I have a dll which has various methods for running stored procedures etc. It
also has a method which allows the client to return a DataSet from a table.
The client can modify this DataSet and I have wrote a function which will
take this DataSet back in and update the Database.

I assume this is what is meant with disconnected datasets?

Anyway, the method for updating the DataSet is not very good.

The problem is, a SqlDataAdaptor is required to update the Database.
When the DataSet was first returned to the user, the SqlDataAdaptor was a
local variable and is now out of scope. To update I have to do this:

1 - Create a new SqlDataAdaptor with the same SQL statement when I
originally got the DataSet

2. Use the Update method to update the DataSet

This really isnt a good way of working, sicne the original SQL statment to
get the DataSet needs to be kept.

I dont think I fully understand how this should work.

Any help on this would be appreciated.

--
Steven
www.stevenblair.com

[Pete Davis]

It's kind of hard to follow what you're doing in your code. Can you supply a
sample?

You don't want to copy the adapter. That won't work.

Why is the SqlDataAdapter out of scope?

If you have a method that creates the SqlDataAdapter locally and then
returns it, then a reference is returned and the original won't be disposed
of.

Pete

"Steven Blair" <st**********@b tinternet.com> wrote in message
news:bu******** **@titan.btinte rnet.com...

Hi,

I have a problem understanding the SqlDataAdaptor and the DataSet.

I have a dll which has various methods for running stored procedures etc. It also has a method which allows the client to return a DataSet from a table. The client can modify this DataSet and I have wrote a function which will
take this DataSet back in and update the Database.

I assume this is what is meant with disconnected datasets?

Anyway, the method for updating the DataSet is not very good.

The problem is, a SqlDataAdaptor is required to update the Database.
When the DataSet was first returned to the user, the SqlDataAdaptor was a
local variable and is now out of scope. To update I have to do this:

1 - Create a new SqlDataAdaptor with the same SQL statement when I
originally got the DataSet

2. Use the Update method to update the DataSet

This really isnt a good way of working, sicne the original SQL statment to
get the DataSet needs to be kept.

I dont think I fully understand how this should work.

Any help on this would be appreciated.

--
Steven
www.stevenblair.com

[Nicholas Paldino [.NET/C# MVP]]

Steven,

I assume you are using some sort of generic method to handle this. You
are right, you are either going to have to keep around the SQL statement, or
you will have to have some way of reconstructing it. However, this isn't a
bad design. The way to get around this is to have some sort of unique
identifier which allows you to know the operation (or SQL statement) that
you need to perform.

For example, you could have separate methods, like UpdateMyTable1,
UpdateMyTable2, etc, etc. Because each of the methods has a unique
identity, you can easily determine what the SQL should be. Another way to
do this would be to insert something into the data set upon creation that
would allow you to create the SQL when you get it back for updating.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Steven Blair" <st**********@b tinternet.com> wrote in message
news:bu******** **@titan.btinte rnet.com...

Hi,

I have a problem understanding the SqlDataAdaptor and the DataSet.

I have a dll which has various methods for running stored procedures etc. It also has a method which allows the client to return a DataSet from a table. The client can modify this DataSet and I have wrote a function which will
take this DataSet back in and update the Database.

I assume this is what is meant with disconnected datasets?

Anyway, the method for updating the DataSet is not very good.

The problem is, a SqlDataAdaptor is required to update the Database.
When the DataSet was first returned to the user, the SqlDataAdaptor was a
local variable and is now out of scope. To update I have to do this:

1 - Create a new SqlDataAdaptor with the same SQL statement when I
originally got the DataSet

2. Use the Update method to update the DataSet

This really isnt a good way of working, sicne the original SQL statment to
get the DataSet needs to be kept.

I dont think I fully understand how this should work.

Any help on this would be appreciated.

--
Steven
www.stevenblair.com

[Steven Blair]

Here is a example of what I am doing:

public bool Update(DataSet ds,ref int rowsUpdated,str ing table)

{

string select;

select = String.Format(" SELECT * FROM {0}",table);

SqlConnection myConnection = new SqlConnection(G etConnectionStr ing() );

SqlDataAdapter m_DataAdapter = new SqlDataAdapter( select,myConnec tion);

SqlCommandBuild er objBuilder = new SqlCommandBuild er(m_DataAdapte r);

m_DataAdapter.U pdateCommand = objBuilder.GetU pdateCommand();

rowsUpdated = m_DataAdapter.U pdate(ds);

return true;

}

So the client has to send in the original table that was used, then I am
guessing the line:

SqlDataAdapter m_DataAdapter = new SqlDataAdapter( select,myConnec tion);

Doea another full select on the database then an update after that.
Thats why i I think this way is flawed.

Hope this makes a bit more sense now.

--
Steven
www.stevenblair.com

"Steven Blair" <st**********@b tinternet.com> wrote in message
news:bu******** **@titan.btinte rnet.com...

Hi,

I have a problem understanding the SqlDataAdaptor and the DataSet.

I have a dll which has various methods for running stored procedures etc. It also has a method which allows the client to return a DataSet from a table. The client can modify this DataSet and I have wrote a function which will
take this DataSet back in and update the Database.

I assume this is what is meant with disconnected datasets?

Anyway, the method for updating the DataSet is not very good.

The problem is, a SqlDataAdaptor is required to update the Database.
When the DataSet was first returned to the user, the SqlDataAdaptor was a
local variable and is now out of scope. To update I have to do this:

1 - Create a new SqlDataAdaptor with the same SQL statement when I
originally got the DataSet

2. Use the Update method to update the DataSet

This really isnt a good way of working, sicne the original SQL statment to
get the DataSet needs to be kept.

I dont think I fully understand how this should work.

Any help on this would be appreciated.

--
Steven
www.stevenblair.com