Solved

Populating a DataTable: best method

Posted on 2014-02-28
5
525 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:Starr Duskk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 250 total points
ID: 39895613
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
ID: 39895642
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 2

Author Comment

by:Starr Duskk
ID: 39895865
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 2

Author Comment

by:Starr Duskk
ID: 39896208
Ignore last post. Answered elsewhere.
0
 
LVL 2

Author Closing Comment

by:Starr Duskk
ID: 39896211
thanks everyone!!!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 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