CODEWITHSUNDEEP
vb.netlistdatatable
Alban Nurkollari
Alban Nurkollari

Reputation: 65

VB : Is it possible to cast/convert from List(Of DataRow) to List(Of String)?

I'm trying to solve a problem regarding types of list. First of all I have a stored procedure in my DB which does a select of a single column and I try to proceed it in my app in VB. By making a method function I declared a DataTable that loads through the SqlCommand(with the CloseConnection behavior). After that I publicly declared a List(Of String) which needs to be populated with the rows/items from the stored procedure that is on the way. Below is my snippet of the code:

Dim dt As New DataTable()

        Try
            If conn.State = ConnectionState.Open Then
                conn.Close()
            Else
                conn.Open()
                Dim cmd = New SqlCommand("LoadCodes", conn)
                cmd.CommandType = CommandType.StoredProcedure

                dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection))
                Dim collection As New List(Of DataRow)
                collection = dt.AsEnumerable.ToList

                LPrefix = collection.Cast(Of String)()

            End If
        Catch ex As Exception
            MsgBox(ex.Message + vbCritical)
        End Try

It's LPrefix = collection.Cast(Of String)() where I get an exception error telling me that I can't really convert it. The old fashion way is to iterate with for/for each loop but that's not what I want for best use of performance especially if the list will have thousands of rows from a single column. So basically I want to insert those items from that DataTable to the List(Of String) without using For/For Each loop.

Running on Visual Studio 2010 Ultimate, .NET Framework 4.0.

Upvotes: 2

Views: 5932

Answers (2)

Filburt
Filburt

Reputation: 18061

Assuming your DataRow only has one column you just need to instruct ConvertAll to cast it:

LPrefix = collection.ConvertAll(Function(x) x[0].ToString)

Thanks to Binary Worrier for c#-2-vb translation!

Upvotes: 1

Heinzi
Heinzi

Reputation: 172270

You don't need your collection at all. Using LINQ, you can extract the first column directly out of your data table:

dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection))  
LPrefix = (From row In dt.AsEnumerable()
           Select row.Field(Of String)(0)).ToList()

Of course, this might use a loop internally, but since you want to copy each value into a list of strings, you cannot do it without looping through the data rows.

Another alternative would be to use an IEnumerable(Of String) instead of a List(Of String):

dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection))  
Dim LPrefixNew As IEnumerable(Of String) = _
    From row In dt.AsEnumerable()
    Select row.Field(Of String)(0)

You can iterate through IEnumerable just as you would through a list, but evaluation is lazy: As long as you don't access the elements, the DataTable is not traversed. So, accessing this IEnumerable is like reading the elements directly from the DataTable, just in a more convenient way.

Another word of advice: You should not try to reason about performance until you have measured it. For example, your line collection = dt.AsEnumerable.ToList probably already loops through your entire DataTable and copies each DataRow reference into a List of DataRows; so, with this line, you already have the performance penalty that you are trying to avoid.

So, don't automatically assume that some For loop is always slower than some single statement. Measure it, then optimize.

Upvotes: 3

Related Questions