Why Create a DataSet, use a DataTable
| This article asks the question, "Why do you create a whole DataSet when all you are using is a single DataTable?". Its a valid question because Ive seen it done over and over again. A DataSet is quite a large object when compared to a DataTable and we all know that performance and optimization is very important in todays application design and development. Of course, for optimum performance you should use a DataReader, but sometimes you have to use the DataTable. This How To demonstrates a built in method of creating a DataTable without having to create a DataSet. |
| You can create just a DataTable by using a different overload of the SqlDataAdapter Fill method. As you can see below there are quite a few overloads to the Fill method: |
| Adds or refreshes rows in the DataSet to match those in the data source using the DataSet name, and creates a DataTable named "Table". |
| [Visual Basic] Overloads Overrides Public Function Fill(DataSet) As Integer Implements IDataAdapter.Fill [C#] public override int Fill(DataSet); |
| Adds or refreshes rows in a DataTable to match those in the data source using the DataTable name. |
| [Visual Basic] Overloads Public Function Fill(DataTable) As Integer [C#] public int Fill(DataTable); |
| Adds or refreshes rows in the DataSet to match those in the data source using the DataSet and DataTable names. |
| [Visual Basic] Overloads Public Function Fill(DataSet, String) As Integer [C#] public int Fill(DataSet, string); |
| Adds or refreshes rows in a DataTable to match those in the data source using the specified DataTable and IDataReader names. |
| [Visual Basic] Overloads Overridable Protected Function Fill(DataTable, IDataReader) As Integer [C#] protected virtual int Fill(DataTable, IDataReader); |
| Adds or refreshes rows in a DataTable to match those in the data source using the DataTable name, the specified SQL SELECT statement, and CommandBehavior. |
| [Visual Basic] Overloads Overridable Protected Function Fill(DataTable, IDbCommand, CommandBehavior) As Integer [C#] protected virtual int Fill(DataTable, IDbCommand, CommandBehavior); |
| Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataSet and DataTable names. |
| [Visual Basic] Overloads Public Function Fill(DataSet, Integer, Integer, String) As Integer [C#] public int Fill(DataSet, int, int, string); |
| Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataSet, DataTable, and IDataReader names. |
| [Visual Basic] Overloads Overridable Protected Function Fill(DataSet, String, IDataReader, Integer, Integer) As Integer [C#] protected virtual int Fill(DataSet, string, IDataReader, int, int); |
| Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataSet and source table names, command string and command behavior. |
| [Visual Basic] Overloads Overridable Protected Function Fill(DataSet, Integer, Integer, String, IDbCommand, CommandBehavior) As Integer [C#] protected virtual int Fill(DataSet, int, int, string, IDbCommand, CommandBehavior); |
| * SDK Help Files |
| As you can see there are also some very interesting overridable items too but we must implement our own class to take advantage of some of them. Ill leave that to you. |
| The one that we are going to be using is the second (2nd) overload where just a DataTable is used. Lets take a look at the code: |
| WebForm |
| <%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="CSharpArticles.Data.WhyUseDataSet.WebForm1" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML> <body> <form id="WebForm1" method="post" runat="server"> <asp:DataGrid ID="DgMethodOne" Runat="server" Font-Size="8"></asp:DataGrid> </form> </body> </HTML> |
| C# Code Behind |
| using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Data.SqlClient; namespace CSharpArticles.Data.WhyUseDataSet { public class WebForm1 : System.Web.UI.Page { protected System.Web.UI.WebControls.DataGrid DgMethodOne; #region E V E N T H A N D L E R S private void Page_Load(object sender, System.EventArgs e) { this.MethodOne(); this.DataBind(); } #endregion #region U S E R - D E F I N E D public void MethodOne() { using ( SqlCommand cmd = new SqlCommand("SELECT * FROM Authors", new SqlConnection( "server=(local); database=Pubs; uid=dmack; pwd=mack" )) ) { SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dtAuthors = new DataTable(); adapter.Fill(dtAuthors); // Just pass the DataTable into the SqlDataAdapters Fill Method this.DgMethodOne.DataSource = dtAuthors; } } #endregion #region A U T O - G E N E R A T E D override protected void OnInit(EventArgs e) { InitializeComponent(); base.OnInit(e); } private void InitializeComponent() { this.Load += new System.EventHandler(this.Page_Load); } #endregion } } |
