none
Linq query to empty DataReader RRS feed

  • Question

  •  

    Hi,

     

    I am a Linq beginner, so much so that I don't know how naive this question is. I am looking for an expression to empty

    all fields in a datareader.

     

    This works with SQL but does not work with OLEDb

     

    Public Function GetResultsOf(ByVal Selectstr As String, _
                                    Optional ByVal params As SqlParameter() = Nothing) _
                                         As List(Of System.Data.Common.DbDataRecord)
            Dim rdr As SqlDataReader = Nothing
            Using Command As New SqlCommand
                With Command
                    .Connection = Con
                    .CommandType = CommandType.Text
                    If params IsNot Nothing Then
                        For Each param As SqlParameter In params
                            .Parameters.Add(param)
                        Next
                    End If
                    .CommandText = Selectstr
                    .Connection.Open()
                    Try
                        rdr = .ExecuteReader(CommandBehavior.CloseConnection)
                    Catch ex As Exception
                        MessageBox.Show(ex.Message, "GetResultsOf")
                    End Try
                End With
            End Using
            Return (From data In rdr)
        End Function

     

    I find that this seems "delicate".  When there is an error, there is no exception, the function just returns with no notification. What I am wondering if it just works by fiat?

     

    Anyway, the functionality I am looking for is to return a list of whatever records are in the reader.

     

    Thanks...

    Sunday, April 20, 2008 3:20 PM

Answers

  •  

    Here is the solution and a discussion.

     

    Public Function GetResultsOf(ByVal Selectstr As String, _

                      Optional ByVal params As SqlParameter() = Nothing) _

                                         As List(Of Object)

            Dim rdr As SqlDataReader = Nothing

            Using Command As New SqlCommand

                With Command

                    .Connection = con

                    .CommandType = CommandType.Text

                    If params IsNot Nothing Then

                        For Each param As SqlParameter In params

                            .Parameters.Add(param)

                        Next

                    End If

                    .CommandText = Selectstr

                    Try

                        .Connection.Open()

                    Catch ex As Exception

                        MessageBox.Show(ex.Message, _

                          "Failed to open connection")

                        Return Nothing

                    End Try

                    Try

                        rdr = _
                      .ExecuteReader(CommandBehavior.CloseConnection)

                    Catch ex As Exception

                        MessageBox.Show(ex.Message, "GetResultsOf")

                    End Try

                End With

            End Using

            Dim array As List(Of Object) = (From data In rdr).ToList

            rdr.Close() : rdr.Dispose()

            Return array

        End Function

     

     

    This method does what I wanted. It returns persistant light weight objects without the overhead of a table or a dataset.

    It has inconviences.

     

    The returned datatype  of each row is system.common.DataRecordInternal

     

    Intellisense does not know the properties or methods of this datatype. It is similar to system.common.dbRecord but it will not cast to that even with a direct cast.

     

    Other problems are that it does not understand field names and it's usable output is an array of objects. The array conrtents must be described by field offset number:

     

    Example:

     

    Dim Violations = dataLayer.GetResultsOf("select * from   
            Parking_Violations where permitNumber = @permitNumber"
    , params)

                tbViolationsFound.Text = Violations.Count

                Dim total As Double = 0.0

                For Each record As System.Data.Common.DbDataRecord _
                                In Violations

                    cmbViolations.Items.Add(record(18))

                    total += record(19)

                Next

     

     

    Notice that the loop casts is as System.Data.Common.DbDataRecord. This makes no difference

    the system still knows that the record is a system.common.DataRecordInternal.

     

    So this is a partially satisfactory solution.

     

     

    Saturday, April 26, 2008 7:02 PM

All replies

  • Hi Renee,

     

    The datareader is a connected-mode object...meaning that the object is disposed when the connection is closed. The code you posted shouldn't work with any providor.

     

    You should get an error that states you're trying to access the reader while the reader is closed

     

    I believe what you are trying to do is to return a dataset or datatable which is available when the connection closes and use the datarows of the datatable to LINQ your list.

     

     

    HereToHelp

     

    Monday, April 21, 2008 9:26 PM
  •  

    No, that's exactly what i am NOT trying to do.

     

    Datasets and tables are heavy objects and I don't want to use them for this application.

     

    You can empty a datareader before it is closed into a list of objects with Linq. That is what i am interested in.

    Saturday, April 26, 2008 5:21 AM
  •  

    Here is the solution and a discussion.

     

    Public Function GetResultsOf(ByVal Selectstr As String, _

                      Optional ByVal params As SqlParameter() = Nothing) _

                                         As List(Of Object)

            Dim rdr As SqlDataReader = Nothing

            Using Command As New SqlCommand

                With Command

                    .Connection = con

                    .CommandType = CommandType.Text

                    If params IsNot Nothing Then

                        For Each param As SqlParameter In params

                            .Parameters.Add(param)

                        Next

                    End If

                    .CommandText = Selectstr

                    Try

                        .Connection.Open()

                    Catch ex As Exception

                        MessageBox.Show(ex.Message, _

                          "Failed to open connection")

                        Return Nothing

                    End Try

                    Try

                        rdr = _
                      .ExecuteReader(CommandBehavior.CloseConnection)

                    Catch ex As Exception

                        MessageBox.Show(ex.Message, "GetResultsOf")

                    End Try

                End With

            End Using

            Dim array As List(Of Object) = (From data In rdr).ToList

            rdr.Close() : rdr.Dispose()

            Return array

        End Function

     

     

    This method does what I wanted. It returns persistant light weight objects without the overhead of a table or a dataset.

    It has inconviences.

     

    The returned datatype  of each row is system.common.DataRecordInternal

     

    Intellisense does not know the properties or methods of this datatype. It is similar to system.common.dbRecord but it will not cast to that even with a direct cast.

     

    Other problems are that it does not understand field names and it's usable output is an array of objects. The array conrtents must be described by field offset number:

     

    Example:

     

    Dim Violations = dataLayer.GetResultsOf("select * from   
            Parking_Violations where permitNumber = @permitNumber"
    , params)

                tbViolationsFound.Text = Violations.Count

                Dim total As Double = 0.0

                For Each record As System.Data.Common.DbDataRecord _
                                In Violations

                    cmbViolations.Items.Add(record(18))

                    total += record(19)

                Next

     

     

    Notice that the loop casts is as System.Data.Common.DbDataRecord. This makes no difference

    the system still knows that the record is a system.common.DataRecordInternal.

     

    So this is a partially satisfactory solution.

     

     

    Saturday, April 26, 2008 7:02 PM
  • Hi Renee,

     

    This is not a very good approach. A datareader carries much more baggage along with it as does any cursor. You are not populating any control and are not doing any manipulation on the data. You are simply creating a datasource. For this, you should use a dataset.

     

    I'm not sure why you're trying to re-invent the wheel here when proven methods, which are much more efficient, are already available....but I'm glad you found your answer.

     

    HereToHelp

    Sunday, April 27, 2008 8:00 PM
  •  

     

    Your's was not an informed response. You have no idea what I am doing, because all you can see is a function in a datalayer.

     

    A datareader, has far, far lower overhead than a dataset and tables. This code, is a prototype to eliminating that over head and it meets that goal. The trouble is that it does not support field names which is a loss. But it supports field offsets just fine.

     

    And by the way, in the GUI, it does populate controls which is what it was intended for.

    Monday, April 28, 2008 11:15 PM
  •  ReneeC wrote:

    A datareader, has far, far lower overhead than a dataset and tables.

    Renee,

     

    I'm sorry to say that you are mistaken on this. A cursory read (any reader) carries overhead from the application layer, the network data layer, and the database. You are killing 3 resources simultaneously as well as locking the tables from other processes.

     

    The secret, that I don't think you realize, is to write refined queries that only return the recordset that is needed for the task at hand. After reviewing some of  your posts, it seems you are not familiar with good query practices. Now I'm not trying to insult your intelligence here, so please don't respond in haste...but datareaders should be reserved for text-like files such as XML and should not be considered for db interaction. Well written queries and datasets are always the wisest choice.

     

    I hope this helps,

     

    HereToHelp

    Friday, May 2, 2008 8:11 PM
  •  

    I didn't ask for your help. This question is answered and you continue to stalk me around the fora just as Adam Turner has and I suspect you are he.

     

    I fully understand Query practices and this has always been central issue with with Adam which also leds me to believe that you are him.

     

    You notice I said example above. It is easier in examples to type "Select * from X" than it it is to type a real query and queries themselves are never central to my questions since queries are a fourth generation language, I'm not very interested in them.

     

    Also you are incorrect about datareaders:

     

    "In all of the preceding tests, we saw that DataReader outperformed DataSet. As mentioned earlier, the DataReader offers better performance because it avoids the performance and memory overhead associated with the creation of the DataSet.

     

    The DataReader is a better choice for applications that require optimized read-only and forward-only data access. The sooner you load the data off the DataReader, close the DataReader, and close the database connection, the better performance you get. Because the DataReader holds a database connection that cannot be used for any other purpose while the application reads data, it could limit scalability if the application holds the DataReader long enough for contention to occur. The DataSet only needs to hold a connection while it is being populated. Once it is filled, the connection may be closed and returned to the pool. Delaying reading the data from the DataSet will not cause contention to occur, as the connection might have been returned to the pool already.

     

    In all of the preceding tests, the maximum connection pool size was 100 (default size), and because the number of simultaneous users never exceeded 100, there was no connection contention as such. We wanted to see how the DataReader and the DataSet would behave if we introduce a little delay while loading off the data, which in turn will introduce connection contention"

     

    According to Mircrosoft performance benchmarks: http://msdn.microsoft.com/en-us/library/ms978388.aspx

     

    So much for your lecturing people.

     

    "we saw that DataReader outperformed DataSet. As mentioned earlier, the DataReader offers better performance because it avoids the performance and memory overhead associated with the creation of the DataSet. "

     

    This is exactly what I said above.

     

    You would also think that any novice would know that datareaders do not lock tables. Adam used to say wierd tecnically incompetent things too. Since your skill levels and behavior are so similar, this is why I think you are him.

    Tuesday, May 6, 2008 4:26 AM
  • RenneeC,

     

    My name is not Adam. I am not associated with Adam (although great minds do think and act alike). Please stop your high school assumptions.

     

    After reviewing your threads 'with' this Adam character, he is far more advanced than you as are most developers posting to this forum. You should listen to him and everyone else and not continue a pretention of knowledge.

     

    You are not familiar with databases or query practices and are a novice with .net. I'm guessing you are very young and inexperienced considering you believe everything you read and don't wander outside of the box to get the real answers.

     

    Also, no one is stalking you. With almost 10,000 postings of your false pretenses, it's very difficult to not bump into you.

     

    Please go away,

     

    Evan - HereToHelp

     

     

     

    Wednesday, May 7, 2008 8:24 PM
  • Hey Reena,

    Y dont U return a Generic List<yourobject> tat will solve the csating and feild names prob.

    This is wat I do in c#

    IEnumerable

     

    <Country> countries = null;

     

    try

    {

    reader = GetCountryList();

    countries = (

    from row in reader.Cast<DbDataRecord>()

     

    select new Country

    {

    CountryId =

    Convert.ToString(row["country_id"]),

    CountryCode =

    Convert.ToString(row["country_code"]),

    CountryName =

    Convert.ToString(row["country_name"]),

    Continent =

    Convert.ToString(row["continent"])

    }).ToList();

    }

     

    catch (Exception ex)

    {

     

    throw;

    }

     

    finally

    {

     

    if (reader != null)

    reader.Close();

    }

     

    return countries;

    Thursday, January 13, 2011 8:49 AM