Solved

Populating a DataTable: best method

Posted on 2014-02-28
5
521 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
  • 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
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…

776 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