Upconverting SQL from VB6

[Perilous1]

I am attempting to upgrade myself from VB6 to 2008. I am having trouble however wrapping my head around how SQL is queried in 2008. It appears that gone is the way of assigning an ADO on the form and simply building query statements in a Data form that references the ADO. Everything appears to be handled directly from the code now, which is not a bad thing, just confusing at first.

I would love to be able to still use an ADO "build" query method as I transition over, if there is one. If not, can someone please break down line by line achieving a connection to a SQL server through to a "Select * From <table> Where (blah = blah) so that I can learn the near methodology? I'd appreciate any remarks in the code that explains the new functions.

 

[Perilous1]

I may not have explained very well what I am going for here..

Is there a way to query and update a SQL DB in VB 2008 in the same manner that it was done in VB6 (my way was a ADODC object and created queries in a DataEnvironment). Would appreciate any help with getting started in this area.

 

[RiverGuy]

Yes, I believe there is similar functionality to the DataEnvironment in .Net. However, I never use these wizard-driven programming techniques. I rarely used DataEnvironment and ADODC controls in VB6 either.

However if you want to use more wizard-driven database programming techniques in .Net, have a look at the TableAdapter:

http://msdn.microsoft.com/en-us/library/bz9tthwx(VS.80).aspx

 

[bboffin]

The suggestion of using a TableAdapter is a good one but you may find the process of creating one somewhat obscure.

1. add a new DataSet to your project. The dataset designer opens.
2. add a TableAdapter to the Dataset design surface and right click on the section headed ...TableAdapter and choose Configure.
3. select or define your connection
4. choose whether to use SQL statements, new Stored Procedures or Existing Stored Procedures. Let's assume Use SQL
5. The Query designer opens and you can build your Select query including any parameter placeholders
6. click Next then Finish

The wizard will have generated code to execute the Select query, any parameters to the query, an Update query with appropriate parameters, an Insert query with appropriate parameters and a Delete query with appropriate parameters.

In addition you will see that the DataSet shown on the design page now contains the names of the columns you selected and you may select each one and examine its properties.

Now build your application. This will cause the custom dataset and tableadapter to be added to the toolbox.

Drag an instance of your custom dataset and an instance of your custom tableadapter from the toolbox to your form. They will be given names with a 1 added to the end.

Code up your application. To load the data call the Fill or GetData method of the tableadapter with any required parameters into the Dataset's custom table.

Carry out all your data editing etc on the table in the dataset.

When you have made changes commit them to the database by calling the tableadapter's Update method which will examine the state of each row in the table and call the appropriate INSERT, UPDATE or DELETE SQL command.

This should get you started but there's a whole lot more to learn to get the best out of ADO.NET!

Best of luck

Bob Boffin