Labels

Thursday, February 16, 2012

How to create an in-memory DataSet or DataTable from scratch in C#

Typically, a DataTable (or a DataSet) is a .NET  object used for holding database rows in a program's memory space. However, there is nothing stopping us from  creating an “in-memory” DataTable or DataSet that has no underlying database rows. We can then  add DataRows to the in-memory DataTable  and use it as the DataSource of data controls  such as a DataList (Web Forms) or a  DataGridView (Windows Forms). This article shows that this is remarkably easy in C#.

Download VS2010 projects:
  1. asp.net DataList using a memory DataTable
  2. Windows Forms DataGridView using a memory DataTable

Motivation

The class DataTable has a convenient structure for holding typed tabular data; a DataSet can hold a collection of  DataTables. The rows of a DataTable can be inserted/updated/deleted in program memory using C#.

We normally use a DataTable to hold data retrieved from a database via a single SQL SELECT statement. Although this is the norm, there may be cases wherein we want to create a DataTable in a program, define its columns and fill it up with data without actually connecting to a database and selecting the data from there. For example:
  1. The generic structure of a DataTable and a DataSet is convenient for holding application data that must be transmitted to another process or programme. As an example, consider a web-enabled desktop application, which allows users to take a computerized test. During testing, the application should be disconnected from the Internet. However test results must be uploaded  to a server after the completion of the test. Such an application can be implemented easily using in-memory DataTable(s) that hold test answers. At the end of the test, the DataTable can be  transmitted over the wire (after serialization, compression and possibly encryption) to an asp.net  Web server process, which can form  the necessary SQL statements and send them in batch form to a DBMS such as SQL Server.
  2. Several “multiple-row” data controls of Windows Forms and Web Forms (e.g., data grids) require a DataSource which is typically a DataTable. If we wish to bind such a control to data originating from a database but requiring  non-trivial transformation  (e.g. complicated  unions of tables having different structures, procedural pre-processing of data), it may be advisable to actually get the necessary data from the database, transform them using the power of C# and store them into a memory DataTable which has the exact structurerequired by our data control. Then we can bind the memory datatable to the data control. (In certain cases,  a better alternative may be the use of stored procedures using Transact-SQL in SQL Server, PL/SQL in Oracle etc. Depending on the data transformation required, for some developers may be much easier to transform the data in C# rather than writing non-trivial stored procedures in SQL).
Below, we create a memory DataTable in C# and provide examples of binding it to multiple-row data controls such as a Web Forms DataList and a Windows Forms DataGridView.

 

Creating an “In-memory” DataTable

We first construct a DataTable instance called dt. We then define the columns  of dt and add rows to it. That’s it!

   1:  //construct a new empty datatable 
   2:  //optional argument: give it a name (Employee). 
   3:  //Otherwise it will be named Table1, Table2 etc.
   4:  DataTable dt = new DataTable("Employee");
   5:   
   6:  //Define the table columns. For example:
   7:  dt.Columns.Add("id", typeof(int));
   8:  dt.Columns.Add("name", typeof(string));
   9:  dt.Columns.Add("salary", typeof(decimal));
  10:  dt.Columns.Add("dateHired", typeof(DateTime));
  11:   
  12:  //Add rows 
  13:  dt.Rows.Add(1, "Tim Smith", 3500.20, new DateTime(2009, 1, 15));
  14:  dt.Rows.Add(2, "Ann Jones", 2700, new DateTime(2010,7,28));
  15:  dt.Rows.Add(3, "Tom Chong", 2900, DateTime.Now);       

We can now use the memory  datatable as a datasource of data controls.We provide two "bare bones" examples using a Web Forms DataList and a Windows Forms DataGridView.

Using a memory DataTable with an asp.net DataList

Download the project (VS 2010)
This simple asp.net project sets the DataSourceID property of a DataList to the memory data table created above. Specifically:

MemoryDataTableProvider.cs: In this class, a static method called GetMemoryDataTable creates the memory DataTable as shown in the code above and returns it to the caller.

Default.aspx: An object data source is created and linked to the DataList:
   1:  <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"  
   2:  SelectMethod="GetMemoryDataTable"
   3:  TypeName="MemoryDataTableProvider">
   4:  </asp:ObjectDataSource>
   5:  <asp:DataList ID="DataList1" runat="server" 
   6:  DataSourceID="ObjectDataSource1">
   7:  
   8:  </asp:DataList>

Using a memory DataTable with a WebForms DataGrid

Download the project (VS2010)
On form load, we simply set the DataGridView's DataSource property to the memory DataTable.
   1:  private void Form1_Load(object sender, EventArgs e)
   2:  {
   3:   dataGridView1.DataSource = 
   4:         MemoryDataTableProvider.GetMemoryDataTable();
   5:  }
   6:   

Creating an in-memory DataSet

We can also create a DataSet d holding program data. We just need to create the DataSet and attach one or more in-memory DataTables to it:
   1:  //create dt as before
   2:  DataTable dt = new DataTable("Employee");
   3:  dt.Columns.Add("id", typeof(int));
   4:  ...
   5:  //attach dt to a DataSet
   6:  DataSet ds = new DataSet();
   7:  ds.Tables.Add(dt);

1 comment:

  1. You line numbers are copied with the code. It will be easier to copy/paste, if numbers will be hidden

    ReplyDelete