Message-ID: <1825498157.7737.1711619562855.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_7736_2126126370.1711619562855" ------=_Part_7736_2126126370.1711619562855 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
The template contains the following data markers:
%%=3DEmployee.Name %%=3DEmployee.Title %%=3DOrders.OrderID %%=3DOrders.Customer %%=3DOrders.OrderDate %%=3DOrders.OrderTotal=20
These data markers will be populated in the ASP.NET code by two database= data sources. The data source for the %%=3DEmployee.* data markers is= an ADO.NET DataTable, and the data source for the %%=3DOrders.* data marke= rs is an ADO.NET DataSet.
To set a template data source to a DataSet or DataTable using OleDB, imp= ort the System.Data and System.Data.OleDB namespaces to the ASP.NET page. F= or example:
using System.Data; using System.Data.OleDb;=20
Imports System.Data Imports System.Data.OleDb=20
To create a DataSet to use as a data source:
Create an OleDbConnection object to open a database connection.
<= /li>Create a SQL query to get data from the database.
Create an OleDbDataAdapter that will execute the SQL command at the = data source and fill the DataSet.
Create a DataSet and use the OleDbDataAdapter to fill it with the da= ta retrieved from the database.
For example, in Databasebind.aspx, the following code creates and fills = OrdersDs, the DataSet which will be used as the data source for the set of = %%=3DOrders.* data markers.
OleDbConnection Conn =3D new OleDbConnection(); DataSet OrdersDs =3D null; try { Conn.ConnectionString =3D Application["connstring"].ToString= (); //--- SQL Query for orders string OrdersSQL =3D "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=3D[Order Details].OrderID AND "= =09+ "Orders.CustomerID=3DCustomers.CustomerID AND Orders.Employe= eID=3D?"; OleDbCommand CmdOrders =3D new OleDbCommand(OrdersSQL, Conn); CmdOrders.Parameters.Add("@EmployeeID", EmployeeId); OleDbDataAdapter AdptSales =3D new OleDbDataAdapter(CmdOrders); OrdersDs =3D new DataSet(); AdptSales.Fill(OrdersDs, "Orders"); }=20
Dim Conn As New OleDbConnection() Dim OrdersDs As DataSet =3D Nothing Try Conn.ConnectionString =3D Application("connstring").ToString= () '--- SQL Query for orders Dim OrdersSQL As String =3D "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=3D[Order Details].OrderID AND "= =09& _ "Orders.CustomerID=3DCustomers.CustomerID AND Orders.Employe= eID=3D?" Dim CmdOrders As New OleDbCommand(OrdersSQL, Conn) CmdOrders.Parameters.Add("@EmployeeID", EmployeeId) Dim AdptSales As New OleDbDataAdapter(CmdOrders) OrdersDs =3D New DataSet() AdptSales.Fill(OrdersDs, "Orders") End Try=20
To bind a DataSet to a template data marker, call [BindData|ExcelTemplat= e.BindData] using the following signature:
ExcelTemplate.BindData(DataSet aData, String aDataMarkerName, DataBindingProperties aDataBindingProperties)=20
This signature takes a DataSet, the name of the data marker to which the= data source should bind, and a collection of binding property values. In D= atabasebind.aspx, this BindData call binds the DataSet OrdersDs to the data= markers *%%=3DOrders.OrderID*, *%%=3DOrders.Custo= mer*, *%%=3DOrders.OrderDate*, and *%%=3D= Orders.OrderTotal* in the template:
xlt.BindData(OrdersDs, "Orders", xlt.CreateDataBinding= Properties());=20
xlt.BindData(OrdersDs, "Orders", xlt.CreateDataBindi= ngProperties())=20
To create a DataTable to use as a data source:
Create an OleDbConnection object to open a database connection.
<= /li>Create a SQL query to get data from the database.
Create an OleDbDataAdapter that will execute the SQL command at the = data source and fill the DataTable.
Create a DataTable and use the OleDbDataAdapter to fill it with the = data retrieved from the database.
For example, in Databasebind.aspx, the following code creates and fills = EmployeeDt, the DataTable which will be used as the data source for the set= of %%=3DEmployee.* data markers.
OleDbConnection Conn =3D new OleDbConnection(); DataTable EmployeeDt =3D null; try { Conn.ConnectionString =3D Application["connstring"].ToString= (); //--- SQL Query for employee information string EmployeeSQL =3D "SELECT FirstName + ' ' + LastName As Name= , Title " + "FROM Employees WHERE EmployeeID=3D?"; OleDbCommand CmdEmployee =3D new OleDbCommand(EmployeeSQL, Conn); CmdEmployee.Parameters.Add("@EmployeeID", EmployeeId); OleDbDataAdapter AdptEmployee =3D new OleDbDataAdapter(CmdEmployee); EmployeeDt =3D new DataTable(); AdptEmployee.Fill(EmployeeDt); }=20
Dim Conn As New OleDbConnection() Dim EmployeeDt As DataTable =3D Nothing Try Conn.ConnectionString =3D Application("connstring").ToString= () '--- SQL Query for employee information Dim EmployeeSQL As String =3D "SELECT FirstName & ' ' & L= astName As Name, Title " & _ "FROM Employees WHERE EmployeeID=3D?" Dim CmdEmployee As New OleDbCommand(EmployeeSQL, Conn) CmdEmployee.Parameters.Add("@EmployeeID", EmployeeId) Dim AdptEmployee As New OleDbDataAdapter(CmdEmployee) EmployeeDt =3D New DataTable() AdptEmployee.Fill(EmployeeDt) End Try=20
To bind a DataTable to a template data marker, call [BindData|ExcelTempl= ate.BindData] using the following signature:
BindData(DataTable aData, String aDataMarkerName, DataBindingPro= perties, aDataBindingProperties)=20
This signature takes a DataTable, the name of the data marker to which t= he data source should bind, and a collection of binding property values. In= Databasebind.aspx, this BindData call binds the DataTable EmployeeDt to th= e data markers *%%=3DEmployee.Name* and *%%=3DEmpl= oyee.Title* in the template:
xlt.BindData(EmployeeDt,"Employee",xlt.CreateDataBindi= ngProperties());=20
xlt.BindData(EmployeeDt, "Employee", xlt.CreateDataB= indingProperties())=20