ExcelTemplate in Depth > Using a Database as a Data Source

Using a Database as a Data Source

Code Sample: Importing from a Database

The sample above generates a spreadsheet from the template using ADO.NET objects as the data source. The template contains the following data markers:

• %%=Employee.Name
• %%=Employee.Title
• %%=Orders.OrderID
• %%=Orders.Customer
• %%=Orders.OrderDate
• %%=Orders.OrderTotal

To set a template data source to a DataSet or DataTable using OleDB, import the System.Data and System.Data.OleDB namespaces to the ASP.NET page. For example, in a C# code-behind page (.aspx.cs), include:

using System.Data;
using System.Data.OleDb;

Setting a Data Source to a DataSet with OleDB

To create a DataSet to use as a data source:

1. Create an OleDbConnection object to open a database connection.
2. Create a SQL query to get data from the database.
3. Create an OleDbDataAdapter that will execute the SQL command at the data source and fill the DataSet.
4. Create a DataSet and use the OleDbDataAdapter to fill it with the data retrieved from the database.

For example, in Databasebind.aspx.cs, the following code creates and fills OrdersDs, the DataSet which will be used as the data source for the set of %%=Orders.* data markers.

OleDbConnection Conn = new OleDbConnection();
DataSet OrdersDs = null;
try
{
  Conn.ConnectionString = Application["connstring"].ToString();
  //--- SQL Query for orders
  string OrdersSQL = "SELECT Orders.OrderID, Customers.CompanyName As Customer, " +
    "Orders.OrderDate, " +
    "([Order Details].UnitPrice * [Order Details].Quantity) As [OrderTotal] " +
    "FROM Orders, [Order Details], Customers " +
    "WHERE Orders.OrderID=[Order Details].OrderID AND "	+
    "Orders.CustomerID=Customers.CustomerID AND Orders.EmployeeID=?";
  OleDbCommand CmdOrders = new OleDbCommand(OrdersSQL, Conn);
  CmdOrders.Parameters.Add("@EmployeeID", EmployeeId);
  OleDbDataAdapter AdptSales = new OleDbDataAdapter(CmdOrders);
  OrdersDs = new DataSet();
  AdptSales.Fill(OrdersDs, "Orders");				
}
...

To bind a DataSet to a template data marker, call SetDataSource, using either of the following signatures:

ExcelTemplate.SetDataSource(DataSet aData, String aDataMarkerName)
ExcelTemplate.SetDataSource(DataSet aData, String aDataMarkerName, int aMaxRows)

The first signature takes a DataSet and the name of the data marker that the data source should bind to. The second signature takes a third parameter - aMaxRows - which specifies the maximum number of rows from the DataSet to insert in the template. In Databasebind.aspx.cs, this SetDataSource call binds the DataSet OrdersDs to the data markers %%=Orders.OrderID, %%=Orders.Customer, %%=Orders.OrderDate, and %%=Orders.OrderTotal in the template:

xlt.SetDataSource(OrdersDs, "Orders");

Setting a Data Source to a DataTable with OleDB

To create a DataTable to use as a data source:

1. Create an OleDbConnection object to open a database connection.
2. Create a SQL query to get data from the database.
3. Create an OleDbDataAdapter that will execute the SQL command at the data source and fill the DataTable.
4. Create a DataTable and use the OleDbDataAdapter to fill it with the data retrieved from the database.

For example, in Databasebind.aspx.cs, the following code creates and fills EmployeeDt, the DataTable which will be used as the data source for the set of %%=Employee.* data markers.

OleDbConnection Conn = new OleDbConnection();
DataTable EmployeeDt = null;
try
{
  Conn.ConnectionString = Application["connstring"].ToString();
  //--- SQL Query for employee information
  string EmployeeSQL = "SELECT FirstName + ' ' + LastName As Name, Title " +
    "FROM Employees WHERE EmployeeID=?";
  OleDbCommand CmdEmployee = new OleDbCommand(EmployeeSQL, Conn);
  CmdEmployee.Parameters.Add("@EmployeeID", EmployeeId);
  OleDbDataAdapter AdptEmployee = new OleDbDataAdapter(CmdEmployee);
  EmployeeDt = new DataTable();
  AdptEmployee.Fill(EmployeeDt);
}
...

To bind a DataTable to a template data marker, call SetDataSource, using either of the following signatures:

SetDataSource(DataTable aData, String aDataMarkerName)
SetDataSource(DataTable aData, String aDataMarkerName, int aMaxRows)

The first signature takes a DataTable and the name of the data marker that the data source should bind to. The second signature takes a third parameter - aMaxRows - which specifies the maximum number of rows from the DataTable to insert in the template. In Databasebind.aspx.cs, this SetDataSource call binds the DataTable EmployeeDt to the data markers %%=Employee.Name and %%=Employee.Title in the template:

xlt.SetDataSource(EmployeeDt, "Employee");

Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.