[RESOLVED] Datasets?

I'm not sure if this belongs in the database section or the NET section. I will leave that up to the moderators.

I have converted a few of my VB6 systems to NET. However, I have retained the ADO coding technique. I probably need to update that. Below is a sample from one of my applications. As you can see, I retrieve the data and, based on conditions, I then issue an UPDATE command. From what I have been reading (and not really grasping) a DATASET/DATA ADAPTER combination will do the same thing. If so, could somebody rewrite this small block of code using "proper" ADO.NET techniques. If I see my own code rewritten it will be much more meaningful to me. Thanks in advance.

Code:

---

                Dim cnxn As New SqlClient.SqlConnection(cnxnstring)
                cnxn.Open()
                Dim sql As New SqlClient.SqlCommand
                sql.CommandText = "SELECT * FROM PART WHERE LTRIM(RTRIM([PARTNUMBER])) = " & "'" & Trim([PARTNUMBER]) & "'"
                sql.CommandType = CommandType.Text
                sql.Connection = cnxn
                Dim reader As SqlDataReader
                reader = sql.ExecuteReader
                Using reader
                    If reader.HasRows Then
                        reader.Read()
                        '
                        Dim cnxnupdate As New SqlClient.SqlConnection(cnxnstring)
                        cnxnupdate.Open()
                        Dim sqlupdate As New SqlClient.SqlCommand
                        If Not IsDBNull(reader("BALANCE")) Then
                            sqlupdate.CommandText = "UPDATE PART SET [BALANCE] = [BALANCE] - " & CInt(txtQuantity.Text)
                        Else
                            sqlupdate.CommandText = "UPDATE PART SET [BALANCE] =  " & CInt(txtQuantity.Text) * -1
                        End If
                        sqlupdate.CommandType = CommandType.Text
                        sqlupdate.Connection = cnxnupdate
                        sqlupdate.ExecuteNonQuery()
                        sqlupdate.Dispose()
                        cnxnupdate.Close()
                        cnxnupdate.Dispose()
                        '====================
                    End If
                End Using
                sql.Dispose()
                reader.Close()
                cnxn.Close()
                cnxn.Dispose()
                '===================
                MsgBox("Record added", MsgBoxStyle.Information)

---

I do not see any reason to do your task with datasets etc but you could optimize by using one connection instead of two

If you really want to go a different direction there is DataConext and LINQ to SQL

Sample DataContext Where clause

Sample DataContext Update

If you are happy with what you have now you could simply optimize a bit, like set all properties of your command object at once.

Code:

---

Dim sql As New SqlClient.SqlCommand With _
{ _
.CommandText = _
    <SQL>
    SELECT * FROM PART WHERE LTRIM(RTRIM([PARTNUMBER])) ='Trim([PARTNUMBER])'
    </SQL>.Value, _
.CommandType = CommandType.Text, _
.Connection = cnxn _
}

---

I guess i was under the impression that if you went to .NET, you should also go to ADO.NET, just to be in good form.

That code you're having is ADO.NET (or at least I could not find anything that doesn't belong to ADO.Net in it). However, you can optimize it by using the same connection. Just create 2 different commands: 1 to read and 1 to update. Also use the keyword "Using" so that you don't have to manually dispose the objects when done.

Code:

---

Using cnxn As New SqlClient.SqlConnection(cnxnstring)
            cnxn.Open()
            Using sql As New SqlClient.SqlCommand
                sql.CommandText = "SELECT * FROM PART WHERE LTRIM(RTRIM([PARTNUMBER])) = " & "'" & Trim([PARTNUMBER]) & "'"
                sql.CommandType = CommandType.Text
                sql.Connection = cnxn
                Using reader As SqlClient.SqlDataReader = sql.ExecuteReader
                    If reader.HasRows Then
                        reader.Read()
                        Using sqlupdate As New SqlClient.SqlCommand
                            If Not IsDBNull(reader("BALANCE")) Then
                                sqlupdate.CommandText = "UPDATE PART SET [BALANCE] = [BALANCE] - " & CInt(txtQuantity.Text)
                            Else
                                sqlupdate.CommandText = "UPDATE PART SET [BALANCE] =  " & CInt(txtQuantity.Text) * -1
                            End If
                            sqlupdate.CommandType = CommandType.Text
                            sqlupdate.Connection = cnxn
                            sqlupdate.ExecuteNonQuery()
                        End Using
                        '====================
                    End If
                End Using
            End Using
        End Using

---

The technique you are using is ADO.NET... you're just using the command objects directly... the dataset/datatable objects allow for a slighly more abstracted layer. If you were to look under the hood of the .Update command of a datatable, you'd see that it essentially does a loop, checking the rowstate of each row and calling the UpdateCommand or InsertCommand or DeleteCommand as needed.

Now... if you had been using ado.recordset and ado.command objects (and not SQLClient.SQLCommand) then you would have probably gotten the reaction you were expecting. ;)

-tg

Interesting comments. Thanks. I guess I was farther ahead than I thought.

I will go back and cancel some of the the "multiple" connections. I'm not sure why I thought each reader/updater needed its own connection.