VB.NET - DataTable.Load() vs. DataReader

Getting large data around 200MB using stored procedure from Database. Previously used DataTable.Load() method to populate this data in DataTable. But it caused a performance issue and the DataTable is not responding because of size of Data.

Using reader As DbDataReader = cmdHelper.ExecuteReader(CommandBehavior.CloseConnection)
    Using rstResult As New DataTable()
        rstResult.Locale = CultureInfo.InvariantCulture
        rstResult.Load(reader)
        Return rstResult
    End Using
End Using

But Now inorder to improve the performance started using DataReader directly, but since DatReader is connected architecture, the DB connections will be open till the BusinessLogic is done.

Dim cnHelper As New DbConnectionHelper(_strDBConnection)
Dim cmdHelper As DbCommandHelper = cnHelper.CreateCommandHelper(strSP)
cmdHelper.Command.CommandType = CommandType.StoredProcedure

Dim reader As DbDataReader = cmdHelper.ExecuteReader(CommandBehavior.CloseConnection)
Return reader

So, i don't want to use DatReader since DB Connections will be open till BusineesLogic is executed.

In this scenario is there any alternative to improve the performance without using DataReader?

This question and answers originated from www.stackoverflow.com
Question by phanish m (2/13/2013 5:49:12 AM)

Answer

In this scenario is there any alternative to improve the performance without using DataReader?

Since DataReader is connection oriented it will keep the connection open with the database, and if you can't afford that can't load all the data in DataTable/DataSet then I guess other option would be to load data in chunks from the database in DataTable and work on that. Otherwise I don't think there are other options.

Answer by Habib

Find More Answers
Related Topics  vb.net  sql-server-2008  ado.net  datatable  sqldatareader
Related Questions