Solved

Populating a DataTable: best method

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

617 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