Using a function in a dataset before returning it

[FastEddie]

I have a field in a dataset that I would like to run through a function and then add the result to the dataset before returning it to the sub.

Right now I am getting the dataset, putting the ZipCodes into an array and then getting my distance calc after the fact. This works ok but it would be far better to have included with the original dataset.

This is what I am doing now.

Dim getZip As String = txtZip.Text
        Dim getRad As Integer = RadioButtonList1.SelectedValue
        Dim myDS As DataSet = GetUE_ZipRadius(getZip, getRad)
        DataList1.DataSource = myDS
        DataList1.DataBind()
 
        With myDS.Tables("ZipCodes")
            Dim zipCodesA(.Rows.Count - 1) As String
            For i As Integer = 0 To .Rows.Count - 1
                zipCodesA(i) = CStr(DirectCast(.Rows(i).Item("ueZip"), Integer))
                LookUpZipCode(getZip, zipCodesA(i))
                ListBox1.Items.Add(Format(CalcDistance(), "F").ToString)
            Next
        End With
 
        myDS.Dispose()

Is it possible to the (Format(CalcDistance(), "F").ToString) before hand and have the distances added as a column to the dataset?

Oh yea the dataset code looks like this

Dim LowLatitude As Double = iStartLat - LatRange
        Dim HighLatitude As Double = iStartLat + LatRange
        Dim LowLongitude As Double = iStartLon - LonRange
        Dim HighLongitude As Double = iStartLon + LonRange
 
        Dim strSelect2 As String = _
            "Select ueStore, ueAddress, ueCity, ueState, " & _
            "ueZip, uePhone From queUncEdsZCodes " & _
            "Where Latitude <=" & HighLatitude & _
            "And Latitude >=" & LowLatitude & _
            "And Longitude <=" & HighLongitude & _
            "And Longitude >=" & LowLongitude
 
        Dim Cmd2 As New OleDbDataAdapter(strSelect2, conn)
        Dim ds As New DataSet
        Cmd2.Fill(ds, "ZipCodes")
 
        Return ds

[jmcilhinney]

What database are you using? Many databases will allow you to create functions which you could call as part of your SQL code.

[FastEddie]

Currently it is Access 2000 but I (think) could easily change to SQL 2005 Express if that could handle this task better.

[jmcilhinney]

SQL Server will be a better bet if you want to perform calculations in your SQL. It will let you create your own functions. Access has some built in SQL functions but I don't think it will let you create your own. I'm no database guru so I don't know all the details so it would be worth investigating further. Perhaps a post in the Database forum could give you a quick answer as to how easy it would be in each case.

[techgnome]

Keep in mind that databases are be suited for data storage and retrieval. They are poor performers when it comes to processing logic. It may turn out (depending on how complex you calculations are), that it is more efficient to continue to use VB as the number cruncher like you currently are.

-tg

[jmcilhinney]

Keep in mind that databases are be suited for data storage and retrieval. They are poor performers when it comes to processing logic. It may turn out (depending on how complex you calculations are), that it is more efficient to continue to use VB as the number cruncher like you currently are.

-tg

Excellent point. I invoke my aforementioned non-database-guru status when I say that I have no idea how efficient using database functions would be. I guess they're useful when you're using the database directly, but when you have a powerful front-end like something written in VB.NET then maybe you would be best to stick to what you have. It could be a fun exercise to find out though, depending on your definition of fun.

[techgnome]

Yeah... trust me.... it's not "fun".... as some one who just did a bunch of optimizations on some SQL stuff.... we ended up pulling all of the logic processing out of the SP and putting it into VB... a 130 minute process dropped to 5 minutes!

-tg