Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

Populating a DataTable: best method

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
Starr Duskk
Asked:
Starr Duskk
  • 3
2 Solutions
 
Jacques Bourgeois (James Burger)Commented:
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
 
CodeCruiserCommented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
Ignore last post. Answered elsewhere.
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
thanks everyone!!!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now