ToArray Is Not a member of SqlDataReader

Question

Greetings mates,

I have the following Linq to Sql code:

 [System.Web.Services.WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute()]           
        public static string[] FillAddress(string prefixText, int count, string contextKey)  
        {  
            if (contextKey == "Install")  
            {  
                var matches = from a in Addresses  
                              where a.currentAddress.StartsWith(prefixText.Trim())  
                              where a.Installs.Equals(true)  
                              select a.currentAddress;  
                return matches.ToArray<string>();  
            }  
  
        }  

I tried to convert it to the following vb.net with a much easier to understand SQL query:

       <System.Web.Services.WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute()>  
        Public Shared Function FillAddress(ByVal prefixText As String, ByVal count As Integer, ByVal contextKey As String) As String()  
  
            If contextKey = "Install" Then  
                Dim addresscmd As New SqlCommand("SELECT currentAddress FROM Addresses where currentAddress LIKE '" & prefixText & " %' and Installs = 1")  
                Dim matches As SqlDataReader = addresscmd.ExecuteReader()  
                Return matches.ToArray(Of String)()  
            End If  
        End Function  

Problem is I am running into the following error:

ToArray is not a member of SqlDataReader

Any ideas how to resolve?

Answer 1

SqlDataReader has never had a ToArray method. ToArray is generally an extension method on IEnumerable<T> that converts a set of items to an array. A data reader is a forward, readonly enumeration of records.

The original code was using LINQ. The result of that LINQ query (aka matches) is IEnumerable<string> which is then converted to a string array. Your new code is executing a reader and getting the reader back but it never enumerates the results. You are missing code to get this to work.

   Dim matches As New List(Of String)  
     
   Using reader As SqlDataReader = addresscmd.ExecuteReader()  
      While (reader.Read())  
          Dim address As String = reader.GetFieldValue(Of String)("currentAddress")  
          matches.Add(address)  
      End While  
     
      Return matches.ToArray()  
   End Using  

Note that this is quite a bit extra code over the LINQ version. I don't personally find it easier to read or maintain but if that is really what you want then you are responsible for allocating and cleaning up the reader, parsing the resultset manually and picking out the column(s) you care about.

Answer 2

Thank you so much.

I am not big fan of LINQ though especially since it makes it very hard to understand the embedded query.

Now, I know that I could use a WHILE loop to get same result.

Thank you again.