Question ADO.NET update through DataSet

[nK0de]

This is not really a question. More like a clarification. I wanna know As to why this is happening.


This is an exercise from a tutorial which I did last year. I came across this today and thought of just trying it out. Its about using a DataSet and how you can make changes in it and update the database through it. Now the original exercise was done using Data controls such as SqlDataAdapter and generating DataSet through that. This is the form.



And this is the code for that.

Public Class Form1


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SqlDataAdapter1.Fill(DataSet11)
    End Sub


    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        BindingContext(DataSet11, "master").Position = 0
    End Sub


    Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
        BindingContext(DataSet11, "master").Position -= 1
    End Sub


    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        BindingContext(DataSet11, "master").Position += 1
    End Sub


    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        BindingContext(DataSet11, "master").Position = BindingContext(DataSet11, "master").Count - 1
    End Sub


    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        BindingContext(DataSet11, "master").AddNew()
    End Sub


    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click


        Dim chDataset As DataSet
        BindingContext(DataSet11, "master").EndCurrentEdit()


        If DataSet11.HasChanges Then
            chDataset = DataSet11.GetChanges
            SqlDataAdapter1.Update(chDataset)
        End If
        DataSet11.AcceptChanges()


    End Sub


    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        BindingContext(DataSet11, "master").RemoveAt(BindingContext(DataSet11, "master").Position)
    End Sub


    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        BindingContext(DataSet11, "master").CancelCurrentEdit()
    End Sub


    Private Sub btnCancelAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelAll.Click
        DataSet11.RejectChanges()
    End Sub


End Class

---

But this time I thought of doing it through code.

Imports System.Data
Imports System.Data.SqlClient


Public Class Form4


    Dim constr As String = "Data Source=NK0DE-PC;Initial Catalog=stockcontrol;User Id=sa;Password=abc123;"
    Dim ds As New DataSet()
    Dim adap As New SqlDataAdapter()


    Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        Dim sqlcon As New SqlConnection(constr)
        sqlcon.Open()


        adap.SelectCommand = New SqlCommand("SELECT * FROM Master", sqlcon)
        adap.Fill(ds, "mst")


        txtCode.DataBindings.Add("Text", ds, "mst.Code")
        txtName.DataBindings.Add("Text", ds, "mst.Name")


    End Sub


    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        BindingContext(ds, "mst").Position = 0
    End Sub


    Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
        BindingContext(ds, "mst").Position -= 1
    End Sub


    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        BindingContext(ds, "mst").Position += 1
    End Sub


    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        BindingContext(ds, "mst").Position = BindingContext(ds, "mst").Count - 1
    End Sub


    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        BindingContext(ds, "mst").AddNew()
    End Sub


    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click


        'updating the dataset with the new data
        Dim newData As DataSet
        BindingContext(ds, "mst").EndCurrentEdit()


        If ds.HasChanges Then
            newData = ds.GetChanges
            Dim combuild As New SqlCommandBuilder(adap)
            adap.Update(newData, "mst")
        End If
        ds.AcceptChanges()


    End Sub


    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        BindingContext(ds, "mst").RemoveAt(BindingContext(ds, "mst").Position)
    End Sub


    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        BindingContext(ds, "mst").CancelCurrentEdit()
    End Sub


    Private Sub btnCancelAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelAll.Click
        ds.RejectChanges()
    End Sub


End Class

---

Everything works fine but my doubt is with the updating part.

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click


        'updating the dataset with the new data
        Dim newData As DataSet
        BindingContext(ds, "mst").EndCurrentEdit()


        If ds.HasChanges Then
            newData = ds.GetChanges
            'Dim combuild As New SqlCommandBuilder(adap)
            adap.Update(newData, "mst")  'Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
        End If
        ds.AcceptChanges()


    End Sub

If I don't build a command using the SqlCommandBuilder, it throws the error Update requires a valid UpdateCommand when passed DataRow collection with modified rows. Note that I didn't have to do so when I was using Data controls.

Can anyone please explain why this is happening? I'm a little skeptical about using the SqlCommandBuilder bacause I've heard that although it works well with small queries, when it comes to complicated ones, its not very ideal. So is there any other way I can go about this?

I know its lengthy. Sorry about that. I need to clear up my mind on this. Thank you all.

 

[jmcilhinney]

There are no "data controls". I assume that you mean that you added the data adapter, etc, to the form in the designer. You can do that because they are components. Any component, i.e. a class that implements IComponent, can be added in the designer. Controls are a special type of component. Your first example must either have added a command builder in the designer too or else you wrote the SQL code yourself. Those are the only two options. Check this out for contrasting examples of using those two approaches:

Retrieving and Saving Data in Databases

Noone would add data adapters, etc, in the designer any more because if you wanted to do that it would be better to use the Data Source wizard to generate a typed DataSet with TableAdapters.

 

[nK0de]

ah yes, I meant the controls under the Data category in VS. In the first example, I didn't add a command builder. The only query I wrote was in the Data Adapter Configuration Wizard, a select query to retrieve all the fields from a table.
And thanks for the link to your article. I've been looking for something like that. I wanted to learn what to use and when. That is very helpful.
I don't really like adding components and going through wizards anymore. I tend to do all that work through code so that it's visible to me and others that go through my code.
Is there any source where I can learn more about TableAdapters?

Thank you for clearing out my doubts.

 

[jmcilhinney]

I haven't used that Data Adapter Configuration Wizard for a loooooooong time. Not since VS 2005 was released. I had actually forgotten about it. It will do basically what a command builder does: use the SELECT statement you provide to generate INSERT, UPDATE and DELETE statements. If you want to write all the code yourself then the link I provided earlier will show you how to do that.

If you don't like using wizards and such then you won't want to be using TableAdapters. They are part of a technology that is basically a beefed-up version of that data adapter wizard. You use the Data Source Wizard to generate a typed DataSet and that includes the TableAdapters, which are basically typed data adapters. If you follow the Data Walkthroughs link in my signature you'll find various examples that make use of that.

Wanting to write all the code yourself is admirable enough and many will tell you that that's the best way to go because you learn more of the detail. That's not a bad thing but, realistically, you won't want to be doing that for too long. I've been a professional developer for 12 years and worked in .NET for 9 and I don't write data access code myself. Data access is one of those things that is basically the same thing over and over in each project with only minor variation, so you want to use tools to make the whole process more streamlined. Personally, I almost always use the Entity Framework. I don't use the Data Source wizard but you can create a EF model that way, as an alternative to a typed DataSet. I would recommend the EF as a first choice for serious data access for any .NET developer.

 

[nK0de]

Although I have heard about the Entity Framework, I never really got to a situation which I have to learn it. I guess this is that time. Thank you very much for all the information.

 

[nK0de]

I went through a couple of tutorials on Entity Framework. It's not very difficult to understand. Frankly a little into it, I saw how easy and efficient it is to work with data access with EF. But it seems EF is kinda based on LINQ(?) I have no prior knowledge of Linq so I should prolly start with Linq to SQL first and then come to EF. I think it's better that way.

 

[jmcilhinney]

No, don't bother with LINQ to SQL. LINQ is an over-arching technology for querying data and there are many different LINQ providers for many different data sources. There's LINQ to Objects for querying in memory lists like arrays and collections. There's LINQ to XML for querying XML data and files. There's LINQ to DataSet for querying ADO.NET DataSets. There's LINQ to SQL for querying SQL Server databases. There's LINQ to Entities for querying Entity Framework models. Those are all built into the .NET Framework but there are various third-party LINQ providers for other data sources too. As you can see, LINQ to SQL is just another LINQ provider like LINQ to Entities, so there's no advantage to learning it first. You need to learn the principles and syntax of LINQ in general, which all LINQ providers support. It's easiest to start with LINQ to Objects because you can create simple arrays and collections and practice various LINQ techniques on them without needing anything external.

 

[nK0de]

When I saw the Linq-like syntax in EF, I thought learning Linq was essential before migrating to EF. Being familiar with linq syntax sounds like a good idea. Thanks

 

[jmcilhinney]

I thought learning Linq was essential before migrating to EF.

It pretty much is, but that's LINQ in a general sense. 'LINQ to SQL' and 'LINQ' are two different things, just as 'SQL Server' and 'SQL' are different things. Like I said, there are various LINQ providers for various data sources. Those providers take the standard LINQ syntax and map it to whatever run time code is appropriate. In the case of Entity Framework, you could do it all without LINQ but it would be very inefficient. That's because doing so would require you to retrieve entire tables of data into your app and then work with the data there to filter, sort and combine. When you use LINQ to Entities, your LINQ queries are converted to SQL code and executed at the database.