Download VS2010 projects:
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:
- 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.
- 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).
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);
