Ado.net ...

[spoiledkid]

ADO.NET is not very flexible, is it ? I mean to update a datatable or dataset, I always need to keep its dataAdapter somewhere in the memory. There is no other way to directly update these objects in the DB, is there ?

I wrote a standard DLL with some functions. These functions will get you the data from DB in whichever form with any no. of parameters. They return dataTable or dataSet. They just temporarily open a dataAdapter to fill the datatable or dataset and then destroy the dataAdapter.

Now if user makes some changes to dataTable and sends it back to update, how do i update it ? I have already destroyed the dataAdapter .... ?? Is there any way or am I not understanding it very well ...

[mendhak]

You can run a direct query on the database (UPDATE in your case).

Dim cmdUpdate As New System.Data.SqlClient.SqlCommand("UPDATE BLAH BLAH")
cmdUpdate.ExecuteNonQuery()

Is this what you are looking for?

[spoiledkid]

Originally posted by mendhak
You can run a direct query on the database (UPDATE in your case).



Is this what you are looking for?

Yeah, thats good for single queries. How about batch update ???

[CyberHawke]

For batch updates you will need several objects.

A dataadapter, a dataset, 4 command objects and a connection object.

The four command objects will be coded for your SELECT INSERT UPDATE and DELETE statements, then associate them with your dataadapter.

After you have finished modifying your data, call the update method of the dataadapter.

[mendhak]

Originally posted by spoiledkid
How about batch update ???

AFAIK, it's only possible using the DataAdapter.

[spoiledkid]

Originally posted by mendhak
AFAIK, it's only possible using the DataAdapter.

Thats why I thought that ADO.NET is not as flexible as MS is bragging about it ... Now what do I do .. Is there a way to build that dataAdapter object while updating... my DA is gone once the DLL call is finished.

[CyberHawke]

Well actually, it's very flexible, but as with any object, in order to use it you need to maintain an in-memory reference to that object, this is true of any programming language.

Now I have a question, if your data adapter is gone from memory, where is your data that your users still have access to it?

[spoiledkid]

Originally posted by CyberHawke
Well actually, it's very flexible, but as with any object, in order to use it you need to maintain an in-memory reference to that object, this is true of any programming language.

Now I have a question, if your data adapter is gone from memory, where is your data that your users still have access to it?

Data is in DataSet or DataTable which has been returned to UI layer .. dataAdapter is disposed once the data has been returned. I do not see it very flexible this way ... why do I have to maintain a freakin dataAdapter ??? My dataset or dataTable should be intelligent enough to do updates on there own ... just like VB6 recordSets ..

[CyberHawke]

ok, I agree that the need for the DataAdapter may not be as flexible as one might like, but there is a solution to your problem.

In your server component, try creating an update method that accepts your DataSet/DataTable object and then builds the adapter on the fly when the method is called. Not as simple as calling an update method on a DataSet, but should work nicely.

[spoiledkid]

Originally posted by CyberHawke
ok, I agree that the need for the DataAdapter may not be as flexible as one might like, but there is a solution to your problem.

In your server component, try creating an update method that accepts your DataSet/DataTable object and then builds the adapter on the fly when the method is called. Not as simple as calling an update method on a DataSet, but should work nicely.

exactly, thats what I want .. but how ???

[mendhak]

You can still use classic ADO in VB.NET. It has the BatchUpdate method, if that helps.

[CyberHawke]

do you have an IM account?

[spoiledkid]

Originally posted by mendhak
You can still use classic ADO in VB.NET. It has the BatchUpdate method, if that helps.

My PM will kill me for this, we are trying to go to .NET, nobody wants to keep maintaining the backward compatibility non-sense which will eventually be removed completely.

[spoiledkid]

Originally posted by CyberHawke
do you have an IM account?

whats an IM account ? no I do not have it.

[mendhak]

Originally posted by spoiledkid
whats an IM account ? no I do not have it.

Ahem.

MSN MEssenger.
Yahoo
ICQ
AIM

[spoiledkid]

Originally posted by mendhak
Ahem.

MSN MEssenger.
Yahoo
ICQ
AIM

I have MSN and Yahoo account ...

[mendhak]

Originally posted by spoiledkid
I have MSN and Yahoo account ...

Now if only you were really Cameron Diaz...




Oh, backtracking to your first post, is there a reason you're destroying the DataAdapter? Why not keep it alive'n'kickin for this eventual Batch Update?

[mendhak]

This will be relevant to you at a later point in time, at which point you will thank me profusely:

http://msdn.microsoft.com/library/de...ta11082001.asp

[CyberHawke]

Ok, in your server component, create a method called:

Public Sub UpdateServer(ByVal ds As DataSet)
    Dim cn As New SqlConnection("connectionstring")
    Dim da As New SqlDataAdapter
    Dim cmdSelect As New SqlCommand("SELECT FROM ...")
    Dim cmdInsert As New SqlCommand("INSERT INTO ...")
    Dim cmdUpdate As New SqlCommand("UPDATE TABLE ...")
    Dim cmdDelete As New SqlCommand("DELETE FROM ...")
 
    da.SelectCommand = cmdSelect
    da.InsertCommand = cmdInsert
    da.UpdateCommand = cmdUpdate
    da.DeleteCommand = cmdDelete
 
    da.Update(ds)
End Sub

I just remembered that there is a lot more code that has to go with this. While I do not recommend that you use the code that gets generated by the wizard in your project, you can create a data connection to the server by dragging a table from the Server Explorer tab in the .NET IDE and then use that code as a template for coding your method in your component.

[spoiledkid]

Tx guys, but for some reason I am not convinced with this. Anyway, may be I have to keep the DA alive at all times. Tx again.

[CyberHawke]

Actually, this does work and quite nicely, I have a project where I have a server component that retrives my data from the server and returns it to the client. Then the client returns the dataset back to the server and calls a data adapter update method. The only problem is, it's written in C# and not appropriate for this forum.

[spoiledkid]

how does your method works if dataset has more than one table in it ? it will always be generating commans for the first table ... wouldn'it ?

[CyberHawke]

Ahh.. There's the catch. I never could get this methodology to work with more than one table at a time. The best that I could do is have multiple sets of command objects and associate the ones that I needed for for updating the needed table. Again, not very flexible for what you need, but it does work.

[taxes]

Hi,

I've never seen so many prompt responses!! Must be something to do with the icon

[spoiledkid]

Originally posted by CyberHawke
Ahh.. There's the catch. I never could get this methodology to work with more than one table at a time. The best that I could do is have multiple sets of command objects and associate the ones that I needed for for updating the needed table. Again, not very flexible for what you need, but it does work.

Jesus christ, I hope what you are saying is wrong .. You cannot update multiple tables at once, then what the heck MS is braging about ???

[CyberHawke]

In reading the article that mendhak provided the link to (thanks dude) I would have to believe that my approach is the correct one. You cannot update multiple tables with a single update statement therefore it makes sense that you wouldn't be able to do that with this methodology either.

[spoiledkid]

so in other words, rule of thumb would be ..

You would only be updating dataTables...
For each dataTable you will need separate DataAdapter...

Are these two statements correct ?

[CyberHawke]

Yes, I would consider them correct.