Is there a Data Adapter Wizard that works from a Dataset?

[CraigBest]

I am relatively new at working with VB.NET, though I've been a VB6 programmer for years. This is my second attempt at re-writing a large VB6 app into .NET, the first collaped because there were too many differences, particularly with data access that wouldn't translate. So thins time I'm taking a different path, trying to establish the data access first then writing the components around that.

I've created/defined a dataset object that should give me the data I want, but I'm having trouble trying to establish a Data Adapter that will fill it properly. The wizard that uses the Server Explorer isn't working for me, ant least I haven't figured out how to have it generate all four of the SQL statements. Is there a wizard that I can feed my Dataset definition to and have it build a Data Adapter from it?

Or do I simply need to multi-select all the tables from the server explorer and drag them onto a form at the same time?

If it matters I'm using the OLEDB.NET provider for Oracle. I've no trouble connecting to the DB.

Thanks

CraigHartz

 

[richself]

I use the server explorer and what I've found is that if I don't include the primary key as one of the items I drag over, then the Insert, Update and Delete SQL commands are not generated for you, only the Select is.

 

[cluelessAnyAdvise]

I am not sure what your requirement is but -

1) When you drag and drop DataAdapter, VB.NET runs the wizard for you that will walk you through the process of generating all four SQL statements (of course you could choose not to in which case it will generate only select statement)
2) You need not pick all tables you need in application to generate data adapter. You can have seperate data adapters for each table or logically grouped tables and still generate single dataset for all data adapters.
3) Once you have all the data adapters you need, generate dataset. Select any one data adapter and right click and select "Generate Dataset" menu option. This will prompt you for dataset name and here you can choose the tables you wish to merge in one dataset.
4) I dont think there is a wizard that works backwords, that is feeds data adapter from dataset.
5) I am not sure what you mean by "dragging all tables onto the form". Are you refering to binding controls on the form?

Hope this helps.

 

[cluelessAnyAdvise]

if you do not want wizard to generate insert, update, delete, in the query builder click "Advance options" and deselect 1st checkbox.
If you do not include primary key in select then yes wizard fails to generate these statements as primary key is essential for those statements. It gives the failure warning and then omits those statements.

 

[CraigBest]

Thanks for the responses!

Richself: I think you understand what I'm doing -- part of the problem is that it isn't finding the primary key. Part of the problem is that the master table in Oracle uses a different key than I want to. Is there a way to specify the primary key when using the drag and drop process from the Server Explorer?

Cluless Any Advise: I guess I did it backwards (though I was following the help file). I defined the Dataset first and now I'm trying to create Data Adapter(s) to provide the data to it. I'm sorry to hear that the process doesn't work "in reverse".

Answering 1: Unfortunately using the drag & drop method I'm not prompted to define the primary key, so the wizard won't generate the Update and Delete methods.

Answering 2: Yes, the wizard creates a separate Data Adapter for each table. I guess that's all right? I was expecting / hoping it be combined into one Data Adapter. I can work with it but it looks like I'm going to have a lot of data adapters to manage.

Answering 3: I guess I can go back and redefine my Dataset this way, It'll only set me back a couple of hours, but hopefully it'll work.

Answering 5: There's a wizard you can use to create an ADODB Connection and Data Adapters; you open the Server Explorer and drag and drop tables onto a Window form. That creates your Connection and apparently a separate Data Adapter for each table you drag over on that Window form.

BTW Is there a way of creating the Connection and Data Adapters in a code module instead? In the past I've usually kept code like this in a code module with public scope. I'm not pleased about having it attached to a specific form.

Thanks again

 

[chrissie1]

answering 1: yes it does you just have to read what it says.

answering 2: I would advise 1 dataadapter and several commands, several dataadapters and several commands will do also

answering 3: if time is a problem then I would not start.

answering 5:

answering whatever: using the wizard is a very bad idea at best and it will leave you in trouble after a while. try getting to know the code and code it all manually you think you will save time using the wizard but you don't. Even using the wizard won't prevent you from using the datasets and adapters in another form/project it's all OOP remember.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[CraigBest]

Christiaan,

<< answering 1: yes it does you just have to read what it says. >>

??? Sorry, you are wrong -- If I was being prompted I ought to know about it. No prompt, nothing to read. Hence the frustration. Perhaps I'm doing something differently than you expect.

<< answering 2: I would advise 1 dataadapter and several commands, several dataadapters and several commands will do also >>

That's what I would prefer. I don't yet know how to proceed here, though. The wizard doesn't let me specify how I want it done, and I don't have the experience yet to define it for myself -- which is why I'm using the wizard.

<< answering 3: if time is a problem then I would not start. >>

Not a time problem so much as a duplicated effort problem.

<< answering 5:

answering whatever: using the wizard is a very bad idea at best and it will leave you in trouble after a while. try getting to know the code and code it all manually you think you will save time using the wizard but you don't. Even using the wizard won't prevent you from using the datasets and adapters in another form/project it's all OOP remember. >>

In VB6 I code all my own stuff, I agree that the wizards are not usually dependable or reliable in the long term. But now I'm working in .NET, and as I found out the last time I tried a conversion on this very program things are VERY different from what I'm used to. I've had to scrap all my data access routines and now I'm trying to build up something new from scratch which will work very differently. Cut me a little slack, huh?

Hey, if I knew what I was doing I wouldn't have built the Dataset first, right?

Right now I'm just trying to find out if it will even work. It's my intent to use the wizard to create 90% of the grunt work and then go in and customize as necessary. If I can get the wizard to do most of the tedious line-by-line work why shouldn't I? Like I said, I'm just getting used to the new syntax and strucutres of .NET, and having the wizard generate the code in the proper formats at least gives me a roadmap to work from.

Not a very helpful post, Christiaan.

 

[chrissie1]

oh believe me in a couple of months time you will find that I am correct and will regret all the code you have created with the wizard and you will see that the thousands of lines of code it created are reduced to a couple of hundred at best. Do a little more research and you will find that coding what you need can be so simple. just an example.
remember I am doing this without VS

dim contemp as new sqlconnection
dim comtemp as new sqlcommand
dim adptemp as new sqldataadapter
dim dttemp as new datatable
dim cbutemp as new sqlcommandbuilder

contemp.connectionstring = "..."
try 
  contemp.open
catch ex as exception
end try
comtemp.commandtext = "select * from table"
comtemp.connection = contemp
adptemp.selectcommand = comtemp
adptemp.fill(dttemp)
adptemp.insertcommand = cbutemp.getinsertcommand
'update and delete the same as above

did I forget something??

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[chrissie1]

BTW I'm not here to tell you what you want hear, I'm here to tell you what you need to hear. And let you learn from my excperience. And not let you make the same misstakes I made.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting