Solved

Populating a DataTable: best method

Posted on 2014-02-28
5
519 Views
Last Modified: 2014-02-28
In a previous post, I was told it was best to databind a grid with DataTable rather than a SqlDataReader.

After doing some research, I found this page and forum post:
http://forums.asp.net/t/1381705.aspx

In it, the suggested answer is to use an executeReader to populate the Datatable.


using (SqlConnection Con = new SqlConnection())
        {
            Con.ConnectionString = YourConnectionString;

            SqlCommand SqlCmd = Con.CreateCommand();
            SqlCmd.CommandText = "YourSpName or SQL Statement";
            SqlCmd.CommandType = CommandType.StoredProcedure;   //Please set CommandType as per your need i.e. if You 

            // using SP then CommandType.StoredProcedure and if SQL Statement then CommandType.Text


            SqlParameter SqlParam;


            SqlParam = new SqlParameter("@YouParameterName", SqlDbType.NVarChar, 200);
            SqlParam.Value = YourParameterValue;
            SqlCmd.Parameters.Add(SqlParam);


            DataTable Dt= new DataTable();
            Con.Open();
            Dt.Load(SqlCmd.ExecuteReader());
            SqlCmd.Dispose();
            Con.Close();
            Con.Dispose();
            return Dt;
        }

Open in new window


In another example, I've seen this, using a SQLDataAdapter:

           Dim ConnString As [String] = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
            Dim conn As New SqlConnection(ConnString)
            Dim adapter As New SqlDataAdapter()
            adapter.SelectCommand = New SqlCommand(query, conn)
 
            Dim myDataTable As New DataTable()
 
            conn.Open()
            Try
                adapter.Fill(myDataTable)
            Finally
                conn.Close()
            End Try
 
            Return myDataTable

Open in new window


Which is the best method?

thanks!
0
Comment
Question by:BobCSD
  • 3
5 Comments
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 250 total points
Comment Utility
It depends on what you need to do with the data.

The DataReader will most of the time load your table a lot faster than the DataAdapter. But as its name imply, it's job is only to read. It might be the better choice if all you have to do is display the data and if you do not need to send back changes to the database later.

The DataDapter, specially if you call FillSchema before calling Fill, does a better job of building a table whose definition is closer to the table in the database. The DataTable it creates will thus be able to perform some operations as far as data integrity is concerned, such as fields that must have unique data. It takes longer to load the data, but is usually a better choice if you have to update the database with changes after the user or the code has worked with it. In simple scenarios, combined with a CommandBuilder, it can also automatically generates the UPDATE, INSERT and DELETE commands necessaries for the update, which might save some time.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 250 total points
Comment Utility
Tableadapter approach in your second snippet is what i use though i dont declare the connection explicitly and i dispose the adapter after calling fill.


           Dim ConnString As [String] = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
            Dim adapter As New SqlDataAdapter(query, ConnString)
            Dim myDataTable As New DataTable()
            Try
                adapter.Fill(myDataTable)
            Finally
                adapter.Dispose()
            End Try
 
            Return myDataTable

Open in new window

0
 
LVL 1

Author Comment

by:BobCSD
Comment Utility
James, You say:
>>The DataReader will most of the time load your table a lot faster than the DataAdapter

The first example uses: ExecuteReader.
Is that what you are calling a DataReader? I don't see a DataReader defined.

My bottom line is my entire project uses SqlDataReader for every grid.

So if I am simply populating a grid or combobox and not using the grid to do inserts, deletes, etc. by using the built-in grid commands then I could use DataReader or ExecuteReader? How about using my SqlDataReader? Should I still use the DataTable with the ExecuteReader?

I have a grid. I populate the data. I use the pager to go to the next page, etc. I sort the grid. I click the grid to retrieve the value of the current row. But I don't do anything else.

All the operations I use hit the NeedDataSource and reload the grid. (I'm using a RadGrid, if that's different.)
0
 
LVL 1

Author Comment

by:BobCSD
Comment Utility
Ignore last post. Answered elsewhere.
0
 
LVL 1

Author Closing Comment

by:BobCSD
Comment Utility
thanks everyone!!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now