Tornado Class Library

Special Topics - Download

There are 5 download options -

  1. Normal download via a download button.
  2. Normal large file download as Csv file. A link will be offer to save the file.
  3. Direct download as Csv file without any prompt and display.
  4. Direct download as Excel file without any prompt and display.
  5. Use ASPDBDownload("File=Physical_or_Virtual_Filename| DownloadSave=True") - This is a direct download of any file without any prompt and display.

Note: 

 

Small Download

The following single property example is the most commonly used in downloading a small file. The download data is he same as the display data.

<script language="vb" runat="server"> 
Sub Page_Load(Source as Object, E as EventArgs) 
  Dim Dload3 As New Tornado.Z()
  With Dload3
    .dbQP = "U=12| S=5| Ps=10| D=Nwind| Q=Orders| Ni=b5,download| 
             Th=ti=Small Download Demo (855 Records)"
    .ASPdbNET()
  End With
End Sub 
</script>
 
Different Display and Download Fields

Download field is limited as field #0 and #1 and is different from display fields.

<script language="vb" runat="server"> 
Sub Page_Load(Source as Object, E as EventArgs) 
  Dim Dload3 As New Tornado.Z()
  With Dload3
    .dbQP = "U=12| S=5| Ps=10| D=Nwind| Q=Orders| Ni=b5,download| 
             Th=ti=Small Download Demo (855 Records)"
 .dbDownloadFlds = "0,1"
    .ASPdbNET()
  End With
End Sub 
</script>

 

Large Download

The following is used to download a larger file. ASP-db buffer the download data in a single file to avoid sending the data rows one at a time. The download data is he same as the display data.

<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
   Dim Dload3 As New Tornado.Z()
   With Dload3
     .dbQP = "U=12| S=5| Ps=10| D=Hotel2000| Ni=b5,download| 
              Th=ti=Large Download Demo (34273 Records)"
     .dbLargeDownload=True 
     .dbSQL = "Select * From Table01 Where City Like 'A%'"
     .ASPdbNET()
   End With
End Sub
</script> 

 

Download as Excel instead of Csv with Customization

This advanced example illustrates the download of Excel file instead of Csv file. Features includes - Header, Title highlight and Row strips. When Excel=True, the download button will be of Excel Style as an indication of Excel Download. The application starts with defining the non-essential properties in dbQP. Since we are producing an Excel worksheet, the PageSize should be 'All' and no Skin. We'll just select 10 records from the Orders table to download. Since we know how many records are coming we can just hard code that using dbRecordCount to by pass all guessing work by the system. We use MagicCell to format the OrderDate as MMM-dd-yy. A lookup table is setup to transform the employeeID to First and LastName. The dbDownloadParams keyword specifies MagicCell and LookUp filter should be use, Download is an Excel format, Perform a SUM in Column 0, 1 and 4. Inser a column after Column 1 with a Caption of 'IDx2', a  formula of =A[[Row]]*2 -> e.g. for row 4=, formula will be =A4*2; for row 5, formula will be A5*2 etc. Insert another column after Column 3 also with Caption=IDx3, Formula = A[[row]]*3. So you can tailor the SS in creating columns. There is a special keyword in TextHolder - ExcelTitle. We specify the Title as <H3> and that we know it'll take up two rows. The titlerow is then set to 2.

This is really a pretty good size application with just a few lines of code.

<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
  Dim XL As New Tornado.z
  With XL
    .dbQP = "U=2| Ps=-1| D=NWIND| S=None"
    .dbSQL = "Select TOP 10 * From Orders"
    .dbRecordCount = 10
    .dbDownloadFlds = "0,1,2,3,4,5,6"
    .dbTextHolder = "DownloadTitle=<h3>Excel Direct Download</h3>"
    .dbGridMagicCell = "field=OrderDate|macro=#OrderDate::MMM-dd-yy#"
    .dbCommonTables = "Index=EID,FullName|SQL=Select employeeid, 
               Firstname & ' ' & Lastname from employees"
    .dbLookUpFlds = "field=EmployeeId|keyindex=EID|lookupIndex=FullName"
    .dbDownloadParams = "(;,~)magic=true| titlerow=2| lookup=true| excel=true| 
               CSUM=0,1,4| InsertAfterCol=1,ID x 2,=a[[row]]*2~3,ID x 3,=a[[row]]*3"
    .dbNavigationItem = "basic5,download"
    .ASPdbDownload()
  End With
End Sub
</script> 

 

StandAlone Direct Generic Download

This variation of ASPDBDownload will download a file directly. DownloadSave defaults to f'alse' and if set to 'true' will force a download/save of the associated files (".htm", ".html", ".doc", ".rtf", ".csv", ".xls", ".gif", ".jpg", ".jpeg", ".pdf", ".zip", ".xml", ".txt") instead of display file in browser. This is the only Method that does not require a dbUnit property.

Dim Dn As New Tornado.z
Dn.ASPdbDownload("File=/tornado/DB/Email.Csv")

 

Advanced Download - Aggregate, SP, Magic

Support for dbAggregate download is added to .id6 versions. A new option dbDownloadParams="StripHtmlTags=True" is added to allow user to down  a clean copy of the grid. dbAggregate invloves a grid controlled by the system and aggregate column(s) and row(s) controlled by the user. In order to download the grid to the csv format, the user specified aggregate template cannot contain cross cell boundary html tags like COLSPAN or ROWSPAN as this cannot be converted to csv file. Normally, the header and the grid should be stripped of any html code before download to the csv file. The following examples illustrate different scenarios.

DownloadMagicCell

<script language='vb' runat='server'>
Sub Page_Load(Source as Object, E as EventArgs)
Dim SP1 As New Tornado.z
   With SP1
      .dbQP = "U=1|S=7|Ps=10|Th=ti=Magic Formula| gdf=0,1,5,6| ni=b5,download"
      .dbDSN = "nwind"
      .dbSQL = "Select * From Products"
      .dbNameMap = "fi=6|al=Your Price"
      .dbMagicCell = "fi=6|Mac=<<#5# + 10.0:c>>" 'Display Magic Add $10
      .dbDownloadMagicCell = "fi=6|Mac=<<#5# + 20.0:c>>" 'Download Magic Add $20
      .dbDownloadParams = "magic=t"
      .ASPdbNET()
   End With
End Sub 
</script>

This example uses a different magic cell for display and download. The display one add $10 to the unit price and the download magic filter adds $20 to the Unit Price. The :c is to convert the number to currency format.
Note: In order to activate the dbDownloadMagicCell, magic=true must be set in dbDownloadParams.

SP Download

This example is to get download from an SP.

<script language='vb' runat='server'>
Sub Page_Load(Source as Object, E as EventArgs)
   Dim SP1 As New Tornado.z
   With SP1
      .dbQP = "Unit=1|S=7|Ps=-1|Ni=None|Th=ti=Advanced SP| pv=SQLClient| dt=SQL| ni=DownLoad| df=0,1,2"
      .dbDSN = "off2;pubs;sa;sa"
      .dbStoredProcCmdParams = "sp=sptest; Na=@strWhere| Ty=string| Val=Type LIKE 'bus%'| Size=16"
      .dbMagicCell = "fi=0|Mac=<b>#0:c#</b>"
      .dbDownloadParams = "striphtmltags=t" '...w/o this, the <b></b> will stay!
       .ASPdbSP()
   End With
End Sub 
</script>

Create the two storedprocedures and test the code for stand alone and parameters SP. Note that you can display in a pagesize of 5 rows but download always pull down the entire recordset.

CREATE PROCEDURE OrderSP @ID nvarchar(15)
AS
SELECT * from Orders where OrderID > @id

CREATE PROCEDURE OrderSP0
AS
SELECT * from Orders

<script language='vb' runat='server'>
Sub Page_Load(Source as Object, E as EventArgs)
 Dim SP As New Tornado.z
      With SP
         .dbQP = "U=1|S=7|Ps=5|Ni=Download|Th=ti=SP Download| pv=SQLClient| dt=SQL| ni=DownLoad|gdf=0,1,2|df=0,1,2|m=grid!sys=t"
         .dbDSN = "off2;northwind;sa;sa"
         .dbDownloadParams = "Striphtmltags=t"
         Dim SPcmd As String = "SPname=OrderSP; Name=@ID|Type=Integer|val=1000"
         '.dbStoredProc = "sp=OrderSP0"   '...stand alone SP
         .dbStoredProcCmdParams = SPcmd    '...SP with parameter(s)
         .ASPdbSP()
      End With
End Sub 
</script>


Aggregate Download

This example download an Aggregate Grid which is composed of a standard grid and the user aggregate template. In order to keep the csv format, there should be no COLSPAN and ROWSPAN in the Aggregate template.

<script language='vb' runat='server'>
Sub Page_Load(Source as Object, E as EventArgs)
   Dim SP As New Tornado.z
   SP.dbQP = "Unit=100|ni=DownLoad| stb=f| th=tit=Aggregate download"
   Dim SPcmd As String = "SPname=CustOrdersDetail|SPType=SP|NoRS=false; Name=@OrderID|Type=integer|Dir=in|Size=5|Value=10248"
   Dim MSRMagicCell As String = "fi=0|tag=align=left, "
   MSRMagicCell = MSRMagicCell & "fi=1|tag=align=right|mac=#1:c#,"
   MSRMagicCell = MSRMagicCell & "fi=2|tag=align=right|mac=#2:n#,"
   MSRMagicCell = MSRMagicCell & "fi=3|tag=align=right|mac=#3:c#,fi=4|tag=align=right|mac=#4:c#"
   Dim dbAgg As String = "(~^:)Fields=1^2^3^4|AggType=SUM|"
   dbAgg = dbAgg & "BottomTemp=<TR BGcolor=yellow align=right><td><b>Total</b></td> "
   dbAgg = dbAgg & "<td align=right>[[1:C]]</td> "
   dbAgg = dbAgg & "<td align=right>[[2:N]]</td> "
   dbAgg = dbAgg & "<td align=right></td> "
   dbAgg = dbAgg & "<td align=right>[[4:C]]</td> "
   dbAgg = dbAgg & "</tr>|CellFormat=Val"
   SP.dbAggregate = dbAgg
   SP.dbMagicCell = MSRMagicCell
   SP.dbStoredProcCmdParams = SPcmd
   SP.dbDBType = "SQL"
   SP.dbDSN = "off2;northwind;sa;sa"
   SP.dbDownloadParams = "striphtmltags=t"
   SP.ASPdbSP()
End Sub 
</script>

Another advanced example with both multiple rows and columns Aggregates. Toggle the two DownloadParams properties line to see the normal and Excel download.

<script language='vb' runat='server'>
Sub Page_Load(Source as Object, E as EventArgs)
   Dim AggProp = "(~^:)fields=1^2^3^4|aggtype=SUM|bottomtemp=<tr align=right><td class=gh>Col Total</td>"
   AggProp &= "<td class=CS>[[1:###]] Items</td><td class=CS>[[2:###.00]]</td><td class=CS>[[3:N]] Units</td>"
   AggProp &= "<td class=CS>[[4:N]] Units</td><td class=CS align=right><b>[[GrandTotal:###,###.00]]</b></td>"
   AggProp &= "<td class=CS colspan=3>&nbsp;</td></tr>|"
   AggProp &= "sumcolheader=<td class=gh><b>Row Total</b></td>|"
   AggProp &= "headertag=class=CS align=right|cellformat=Val"
   AggProp &= "~fields=1^2^3^4|aggtype=MAX|bottomtemp=<tr align=right><td class=gh>Col Max</td><td class=CS>[[1]]</td>"
   AggProp &= "<td class=CS>[[2]]</td><td class=CS>[[3]]</td><td class=CS>[[4]]</td><td class=CS colspan=4>&nbsp;</td></tr>|"
   AggProp &= "sumcolheader=<td class=gh>Row Max</td>|headertag=class=CS align=right|cellformat=Val"
   AggProp &= "~fields=1^2^3^4|aggtype=MIN|bottomtemp=<tr align=right><td class=gh>Col Min</td>"
   AggProp &= "<td class=CS>[[1]]</td><td class=CS>[[2]]</td><td class=CS>[[3]]</td><td class=CS>[[4]]</td>"
   AggProp &= "<td class=CS colspan=4>&nbsp;</td></tr>|sumcolheader=<td class=gh><b>Row Min</b></td>|"
   AggProp &= "headertag=class=CS align=right|cellformat=Val"
   AggProp &= "~fields=1^2^3^4|aggtype=AVG|bottomtemp=<tr align=right><td class=gh>Col Avg</td>"
   AggProp &= "<td class=CS>[[1:f]]</td><td class=CS>[[2:f]]</td><td class=CS>[[3:f]]</td>"
   AggProp &= "<td class=CS>[[4:f]]</td><td class=CS colspan=4>&nbsp;</td></tr>|sumcolheader=<td class=gh>Row Avg</td>|"
   AggProp &= "headertag=class=CS align=right|cellformat=Val"
   AggProp &= "~fields=1^2^3^4|aggtype=COUNT|bottomtemp=<tr align=right><td class=gh>Col Count</td>"
   AggProp &= "<td class=CS>[[1]]</td><td class=CS>[[2]]</td><td class=CS>[[3]]</td><td class=CS>[[4]]</td>"
   AggProp &= "<td class=CS colspan=4>&nbsp;</td></tr>|cellformat=Val"
   Dim Agg As New Tornado.z
   With Agg
      .dbQP = "U=33| S=33| PS=-1| D=Nwind| stb=f| ni=download| Th=tit=Advanced Aggregate Download"
      .dbMagicCell = "fi=1|tag=align=right|fi=2|tag=align=right|fi=3|tag=align=right|fi=4|tag=align=right"
      .dbSQL = "Select ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder FROM Products"
      .dbAggregate = AggProp
      '.dbDownloadParams = "(;,~)titlerow=2| lookup=true| excel=true| DownloadTitle=<h3>Excel Direct Download</h3>"
      .dbDownloadParams = "striphtmltags=t" '...toggle these two dbDownloadParams line and see the results
      .ASPdbNET()
   End With
End Sub 
</script>

Send comments on this topic.
Copyright 1998-2006 ASP-db