Parameters order in ADO.Net command objects.

I didn’t know that the order of parameters in a SQl statement mattered. At least when using MS Access as your back-end. Untill recently when I wrote the function below that updates an MS Access  2003 Database.

Public Function Update() As Integer
           Using cn As New OleDbConnection(ConnectionString)
            Dim sql As String = "UPDATE SupplyItem SET Balance = @Balance " & _
                                       "WHERE ProductID = @ProductID AND UnitID = @UnitID;"

            Dim cmd As OleDbCommand = New OleDbCommand
            cmd.Connection = cn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sql
            cmd.Parameters.AddWithValue("@UnitID", Me.UnitID)
            cmd.Parameters.AddWithValue("@Balance", Me.Balance)
            cmd.Parameters.AddWithValue("@ProductID", Me.ProductID)
            cn.Open()
            Dim res As Integer = cmd.ExecuteNonQuery
            cn.Close()
            Return res
        End Using

    End Function

I ran a unit test on the function and the test was failing. I tried again and again and it failed with some parameter error or something. I checked and rechecked the database, the parameters and everything. But it was still failing.

I got confused. You see, I thought that because the parameters are named, then it doesn’t matter in which order you suppply them to the command object. As long as you supply all the parameters and values, the query should work. Is it different for SQL server and for MS Access? or it doesn’t matter at all and maybe my system had other problems? I’m not sure.

So after a while and after trying everything else, I decided to change the order of the parameters to this.

            cmd.Parameters.AddWithValue("@Balance", Me.Balance)
            cmd.Parameters.AddWithValue("@ProductID", Me.ProductID)
            cmd.Parameters.AddWithValue("@UnitID", Me.UnitID)

And voila! It worked like magic. I was confused even more.

But I guess the more you write code, the more you discover little things that you overlooked before or didn’t bother to go into the details at an earlier time.

Till next time, yours truly.

Save and load Dataset Objects from XML

I love the .NET framework for the simplicity it has brought to application development in so many ways. Like in datacentric application development for example. You could save a whole dataset to disk with just one line of code. Thats right. 1 line.  And retrieve it again with just one other line.

In this post we’ll see a sample of how to do it.

So suppose you have this simple Student class.

Public Class Student
    Private first As String
    Private last As String
    Private _address As String
    Private _age As Integer
    Private _gender As String
    Private _Id As Integer

    Public Property ID() As Integer
        Get
            Return _Id
        End Get
        Set(ByVal value As Integer)
            _Id = value
        End Set
    End Property
    Public Property FirstName() As String
        Get
            Return first
        End Get
        Set(ByVal value As String)
            first = value
        End Set
    End Property

    Public Property LastName() As String
        Get
            Return last
        End Get
        Set(ByVal value As String)
            last = value
        End Set
    End Property

    Public Property Address() As String
        Get
            Return _address
        End Get
        Set(ByVal value As String)
            _address = value
        End Set
    End Property

    Public Property Age() As Integer
        Get
            Return _age
        End Get
        Set(ByVal value As Integer)
            _age = value
        End Set
    End Property

    Public Property Gender() As String
        Get
            Return _gender
        End Get
        Set(ByVal value As String)
            _gender = value
        End Set
    End Property

    Public Overrides Function ToString() As String
        Return FirstName & " " & LastName & ", " & Gender & ", " & Age & ", " & Address
    End Function
End Class

And let’s say you have this function that populates and returns a list of student objects.

Public Function GetPeople() As List(Of Student)
        Dim students As New List(Of Student)
        Dim one As New Student
        one.ID = 1
        one.FirstName = "Chan"
        one.LastName = "Dong"
        one.Gender = "Male"
        one.Age = Integer.Parse("26")
        one.Address = "Xin Xiao, China"
        students.Add(one)

        Dim two As New Student
        two.ID = 2
        two.FirstName = "Ruth"
        two.LastName = "Mtaita"
        two.Gender = "Female"
        two.Age = Integer.Parse("23")
        two.Address = "6032, Olasity, Arusha"
        students.Add(two)

        Dim three As New Student
        three.ID = 3
        three.FirstName = "Sydney"
        three.LastName = "Masao"
        three.Gender = "Female"
        three.Age = Integer.Parse("2")
        three.Address = "6032, Olasity, Arusha"
        students.Add(three)

        Dim four As New Student
        four.ID = 4
        four.FirstName = "Willy"
        four.LastName = "Masao"
        four.Gender = "Male"
        four.Age = Integer.Parse("24")
        four.Address = "Moshi, Tanzania"
        students.Add(four)

        Return students

    End Function

Now I’m going to create a dataset and a table, and fill the dataset with the students objects from above, save the dataset to disk as an xml file, modify the data in xml and then populate the dataset with the modified data from the xml file.

The method below creates the dataset and fills it with our students data.

Public StudentDataset As DataSet

    Public Sub FillDataset()
        StudentDataset = New DataSet
‘you need to make it perfect, add datatypes for the columns etc.
        Dim tb1 As New DataTable("People")
        Dim col1 As New DataColumn("ID")
        Dim col2 As New DataColumn("Firstname")
        Dim col3 As New DataColumn("Lastname")
        Dim col4 As New DataColumn("Gender")
        Dim col5 As New DataColumn("Age")
        Dim col6 As New DataColumn("Address")
        tb1.Columns.Add(col1)
        tb1.Columns.Add(col2)
        tb1.Columns.Add(col3)
        tb1.Columns.Add(col4)
        tb1.Columns.Add(col5)
        tb1.Columns.Add(col6)

        Dim row As DataRow = tb1.NewRow
        row(0) = GetPeople(0).ID
        row(1) = GetPeople(0).FirstName
        row(2) = GetPeople(0).LastName
        row(3) = GetPeople(0).Gender
        row(4) = GetPeople(0).Age
        row(5) = GetPeople(0).Address
        tb1.Rows.Add(row)

        Dim row1 As DataRow = tb1.NewRow
        row1(0) = GetPeople(1).ID
        row1(1) = GetPeople(1).FirstName
        row1(2) = GetPeople(1).LastName
        row1(3) = GetPeople(1).Gender
        row1(4) = GetPeople(1).Age
        row1(5) = GetPeople(1).Address
        tb1.Rows.Add(row1)

        Dim row2 As DataRow = tb1.NewRow
        row2(0) = GetPeople(2).ID
        row2(1) = GetPeople(2).FirstName
        row2(2) = GetPeople(2).LastName
        row2(3) = GetPeople(2).Gender
        row2(4) = GetPeople(2).Age
        row2(5) = GetPeople(2).Address
        tb1.Rows.Add(row2)

        Dim row3 As DataRow = tb1.NewRow
        row3(0) = GetPeople(3).ID
        row3(1) = GetPeople(3).FirstName
        row3(2) = GetPeople(3).LastName
        row3(3) = GetPeople(3).Gender
        row3(4) = GetPeople(3).Age
        row3(5) = GetPeople(3).Address
        tb1.Rows.Add(row3)

        StudentDataset.Tables.Add(tb1)
    End Sub

Now I’ll create a simple form, add a datagridview control in it. I’ll create a method within the form to save the dataset to XML, and then I’ll add another method to get the dataset from XML and fill the datagrid with the student records from our dataset.

Saving a Dataset to XML

This single line of code does that for us. The .Net method Dataset.WriteXml takes a file path and saves your whole dataset as an xml file. In this case, the path is the local application folder, and the file name is Studentdataset.xml

    Public Sub DatasetToXML()
        StudentDataset.WriteXml("StudentsDataset.xml")
    End Sub

See the dataset in Xml format below.

Retrieving the Dataset from XML

Again notice the single line of code that retrieves the dataset from XML. The .Net method for the action is Dataset.ReadXml

Public Sub FillDatasetFromXML()
        Dim mydataset As New DataSet
'this line loads the dataset from Xml
        mydataset.ReadXml("StudentsDataset.xml")
'This code adds the records to the datagridview
        For Each row As DataRow In mydataset.Tables(0).Rows
            Dim i(5) As String
            i(0) = row(0)
            i(1) = row(1)
            i(2) = row(2)
            i(3) = row(3)
            i(4) = row(4)
            i(5) = row(5)
            DataGridView1.Rows.Add(i)
        Next

    End Sub

Now I’m going to open the xml file in notepad, or whichever xml editor that you may use, modify one record, save changes, and fill our datagrid with the modified dataset.

Here is the form loaded with data from the dataset that we retrieved from Xml.

Edit the dataset in Xml

I’m changing all the details for the first student in xml format using notepad.

After modifying the record and saving the changes, I go back to my form again and load the records from the modified Xml dataset. See the updated changes for the first student displayed in the form now.

And here is the modified dataset in Xml view

And here is the rest of the form’s code.

    Private Sub ButtonToXML_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonToXML.Click
        Call DatasetToXML()
    End Sub

    Private Sub ButtonFromXML_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonFromXML.Click
        DataGridView1.Rows.Clear()
        Call FillDatasetFromXML()
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call FillDataset()
    End Sub

The dataset class has many other important methods you could use, for example in saving and retrieving table schemas etc. Its up to everyone to explore the powerful features and use them in their projects.

This was just a scrath at the top, on how you could save a dataset in xml, transfer it maybe over the network, and load it again on the other side ready for use.

Till next time, yours truly.