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 Using a Database as a Data Source

Using a Database as a Data Source

Code Sample: Importing from a Database

[C#][VB.NET]

The sample above generat= es a spreadsheet from the template using ADO.NET objects as the data source= .

The template contains the following data markers:

Data Markers
=20
 %%=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:

=20
using System.Data;
using System.Data.OleDb;
=20
=20
Imports System.Data
Imports System.Data.OleDb
=20

 

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.

    <= /li>
  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 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.

=20
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
=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:

=20
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:

=20
xlt.BindData(OrdersDs, "Orders", xlt.CreateDataBinding=
Properties());
=20
=20
 xlt.BindData(OrdersDs, "Orders", xlt.CreateDataBindi=
ngProperties())
=20

 

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.

    <= /li>
  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, the following code creates and fills = EmployeeDt, the DataTable which will be used as the data source for the set= of %%=3DEmployee.* data markers.

=20
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
=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:

=20
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:

=20
xlt.BindData(EmployeeDt,"Employee",xlt.CreateDataBindi=
ngProperties());
=20
=20
 xlt.BindData(EmployeeDt, "Employee", xlt.CreateDataB=
indingProperties())
=20

 

 

 

------=_Part_7736_2126126370.1711619562855--