Re: Limit Rows in DataTable

> Be careful with this.  We recently had a customer do an emailing with 22,000
> rows in a datatable in a dataset.  The dataset simply is not designed for
> this amount of records and it was SLOW. We broke it up into smaller datasets
> of about 500 record each.

I'll second this, and remind people that  <singing> Anything you can
do with a DataSet, I can do with a DataReader faster </singing>

My usual pattern these days is to pass a delegate that takes the
SqlDataReader and new up the DTO and parties on... then call that guy
inside the loop body in my DAL.  (which prevents Dispose-forgotten
leaks). Something like this in VB for variety :):

    Public Interface ILoadFromReader
        Sub LoadFromReader(ByVal reader As SqlDataReader)
    End Interface

        Public Function ExecuteDataReader(Of T As {New,
ILoadFromReader})(ByVal connection As SqlConnection, ByVal commandType
As CommandType, ByVal commandText As String, ByVal handler As
HandleOne(Of T), ByVal ParamArray commandParameters() As
SqlClient.SqlParameter) As Long

            If (connection Is Nothing) Then Throw New
ArgumentNullException("connection")
            ' Create a command and prepare it for execution
            Dim cmd As SqlCommand = New SqlCommand
            Dim mustCloseConnection As Boolean = False

            Try
                PrepareCommand(cmd, connection, CType(Nothing,
SqlTransaction), commandType, commandText, commandParameters,
mustCloseConnection)

                Dim count As Long = 0
                Dim reader As SqlDataReader = Nothing
                Try
                    reader = cmd.ExecuteReader()

                    If reader.HasRows Then

                        While reader.Read()
                            count = count + 1
                            Dim entry As T = New T()
                            entry.LoadFromReader(reader)
                            If Not handler(entry) Then Exit While
                        End While
                    End If
                Finally
                    If Not (reader Is Nothing) Then reader.Dispose()
                End Try

                ' Detach the SqlParameters from the command object, so
they can be used again
                cmd.Parameters.Clear()

                Return count
            Finally
                If (mustCloseConnection) Then connection.Close()
            End Try
        End Function

--
"Your lack of planning DOES constitute an emergency on my part... so
PLAN BETTER! "

Marc C. Brooks
http://musingmarc.blogspot.com

===================================
This list is hosted by DevelopMentor®  http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com