A few simple ADO.NET questions [Resolved]

[mendhak]

Going through the book, studying as usual, I was instructed that when I want to use the SQLDataAdapter, SQLConnection objects, and generate a DataSet object, and then bind my control (textbox, datagrid, whatever) to it to get the value.

My first question is:

Is this the way most of you do it? Or do you prefer doing it through code?

I did it the way described above, and saw that it generated a lot of code. So, my VB6-remnant brain assumed that using these objects to connect to a database would be as inefficient as using the ADODC control in VB6 (You guys remember that one? )

So, I tried to do it through code. Here is what I came up with so far:

Dim scSqlConn As New SqlConnection
        scSqlConn.ConnectionString = "Data Source=(local);" & _
                            "Initial Catalog=NorthWind;uid=sa;pwd=;" & _
                            "Integrated Security=SSPI"
        scSqlConn.Open()
 
        'data set, data adapter
 
        Dim dsDataSet As New DataSet
        Dim daDataAdapter As New SqlDataAdapter
        Dim cmdSELECTCOMMAND As New SqlCommand
        cmdSELECTCOMMAND.CommandText = "SELECT * FROM Customers;"
        cmdSELECTCOMMAND.Connection = scSqlConn
 
 
        daDataAdapter.SelectCommand = cmdSELECTCOMMAND
        daDataAdapter.Fill(dsDataSet, "Customers")

Second question:
Am I doing it right? Am I missing something?

Third question:
I am simply trying to set a Textbox1.Text property to a value in the table.

In VB6, this was:

TextBox1.Text = Objrs.Fields(0).Value

I can't figure out how to do that in ADO.NET. Any ideas what I should do?



Thanks.

[mendhak]

Regarding Question 1, I manged to write this code:

Dim dsDataSet As New DataSet
    Dim daDataAdapter As New SqlDataAdapter("SELECT * FROM Customers", sqlConn)
    Dim sqlConn As New SqlConnection
    Dim cmdSelectCommand As New SqlCommand
 
'''''''''''''''''''''''''''''''
 
 sqlConn.ConnectionString = "Data Source=(local);" & _
                            "Initial Catalog=NorthWind;uid=sa;pwd=;" & _
                            "Integrated Security=SSPI"
 
        sqlConn.Open()
 
        daDataAdapter.Fill(dsDataSet, "Customers")
 
 
        TextBox1.Text = dsDataSet.Tables("Customers").Rows(0).Item(0).ToString()

How's that?

Question 4:

Working with the same code here, how can I place two tables into the Dataset?

[Briantcva]

Is this the way most of you do it? Or do you prefer doing it through code?

You mean code vs. dropping those "objects" from the toolbox? I always write the code. Probably comes from VB6 and the back box data controls provided. Plus, at least for me, I never really got how connection, command, recordset worked until I started writing it manually. ADO.Net has been largely the same.

As far as databinding is concerned, I'm watching your other thread with interest. My gut reaction was the same as yours, data binding is evil and doesn't work. But it does in .Net. And there aren't a lot of answers out there if you don't databind. Which is either b/c it really does work or b/c folks are lazy.

Remember what a dataset is - a mini-db. Depending on what you're doing, a dataset can be overkill. You may just want to return a straight datatable.

But if you are using a dataset, you're going to have to navigate to which datatable you want (b/c a dataset can hold many tables) and then navigate to what row you want (b/c a datatable can have many rows) and then you can set your textbox property.

[Briantcva]

Originally posted by mendhak
Working with the same code here, how can I place two tables into the Dataset?

The dataset tables collection provides a method to add datatables.

[mendhak]

Thanks a lot for your input.

I did indeed decide to go the code-it-by-hand way. I'm somewhat satisfied, but as you mentioned, there's so little documentation out there, it's almost appalling. Either that, or it's my search skills that are appalling.

I managed to solve all three questions, eventually.

Got a new problem though, that'll be a new post since this thread could be considered "saturated"

TY!

[CyberHawke]

I too use the Code-it-by-hand methodology, also probably because of my days as a VB3 thru VB6 coder. but I have also used the objects from the toolbox and found that what you would code by hand is nearly the same code as the designers produce. As for Databinding, it works almost too well. As stated previously, a dataset is a local representation of all objects retrieved from your datasource(mini db). So binding the dataset to say a datagrid will display links for all the tables in your set. If you wish to display individual tables in separate grids get a datatable object for each table in your set and bind the table to your grid.