Code Snippets (Logging Onto the Database and Exporting Data)

A tool I like to use for various programming tasks is code snippets.  For example, I keep code snippets available that programmatically log on to a Report Documents tables collection for a Crystal Reports document object.  You can find this code in other articles.  I use these examples as snippets because I like the ability to drag and drop the code I need from my Toolbox into my class or code.  Another option would be to create a class to do these generic Crystal Reports tasks.  I do have a series of articles that address this, called “Automagically Display Crystal Parameters: Parts I, II, and III”.  For occasions when the class approach will not work, I have used code snippets to add this code to different applications.

In case you haven’t utilized code snippets, here’s a quick rundown.  You create a new section in the Toolbox of Visual Studio .NET 2003 for your different code snippets by right–clicking in the Toolbox, choosing Add Tab, and keying in My Snippets as the tab name.  With a code page or class open, you highlight the section of code you want to save as a snippet, and drag that section of code into the My Snippets area of the Toolbox (see Figure 1).

Figure 1

Code Listing 1 (Login Snippet)

C# code

private void ApplyCRLogin(CrystalDecisions.CrystalReports.Engine.ReportDocument  oRpt )
{
 CrystalDecisions.CrystalReports.Engine.Database oCRDb = oRpt.Database; 
 CrystalDecisions.CrystalReports.Engine.Tables oCRTables = oCRDb.Tables; 
 CrystalDecisions.Shared.TableLogOnInfo oCRTableLogonInfo; 
 CrystalDecisions.Shared.ConnectionInfo oCRConnectionInfo = new CrystalDecisions.Shared.ConnectionInfo(); 
 oCRConnectionInfo.DatabaseName = "Northwind"; 
 oCRConnectionInfo.ServerName = "(local)"; 
 oCRConnectionInfo.UserID = "NorthwindUser"; 
 oCRConnectionInfo.Password = "NorthwindUser"; 
 foreach (CrystalDecisions.CrystalReports.Engine.Table oCRTable in oCRTables) 
 { 
  oCRTableLogonInfo = oCRTable.LogOnInfo; 
  oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo; 
  oCRTable.ApplyLogOnInfo(oCRTableLogonInfo); 
 }
}

VB.NET code

Public Sub ApplyInfo(ByRef _oRpt As CrystalDecisions.CrystalReports.Engine.ReportDocument)
 Dim oCRDb As CrystalDecisions.CrystalReports.Engine.Database = _oRpt.Database
 Dim oCRTables As CrystalDecisions.CrystalReports.Engine.Tables = oCRDb.Tables
 Dim oCRTable As CrystalDecisions.CrystalReports.Engine.Table
 Dim oCRTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo
 Dim oCRConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo()
 oCRConnectionInfo.DatabaseName = "Northwind" 
 oCRConnectionInfo.ServerName = "(local)" 
 oCRConnectionInfo.UserID = "NorthwindUser" 
 oCRConnectionInfo.Password = "NorthwindUser" 
 For Each oCRTable In oCRTables
  oCRTableLogonInfo = oCRTable.LogOnInfo
  oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo
  oCRTable.ApplyLogOnInfo(oCRTableLogonInfo)
 Next
End Sub

Code Listing 2 (ExportData Snippet)

C# code

public void ExportData(ref CrystalDecisions.CrystalReports.Engine.ReportDocument oRpt) 
{ 
 FileStream fs; 
 long FileSize; 
 CrystalDecisions.Shared.DiskFileDestinationOptions oDest = 
     new CrystalDecisions.Shared.DiskFileDestinationOptions(); 
 string ExportFileName =
     Server.MapPath("/") + ConfigurationSettings.AppSettings["ExportDir"] + Session.SessionID + ".pdf"; 
 try 
 { 
  oRpt.ExportOptions.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile; 
  oRpt.ExportOptions.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.PortableDocFormat; 
  oDest.DiskFileName = ExportFileName; 
  oRpt.ExportOptions.DestinationOptions = oDest; 
  oRpt.Export(); 
  Response.Clear(); 
  Response.Buffer = true; 
  Response.AddHeader("Content-Type", "application/pdf"); 
  fs = new FileStream(ExportFileName, FileMode.Open); 
  FileSize = fs.Length; 
  byte[] bBuffer = new byte[System.Convert.ToInt32(FileSize)]; 
  fs.Read(bBuffer, 0, System.Convert.ToInt32(FileSize)); 
  fs.Close(); 
  Response.BinaryWrite(bBuffer); 
  Response.Flush(); 
  Response.Close(); 
 } 
 catch (Exception e) 
 { 
 } 
}

Public Sub ExportData(ByRef oRpt As Object)
 Dim fs As FileStream
 Dim FileSize As Long
 Dim oDest As New CrystalDecisions.Shared.DiskFileDestinationOptions
 Dim ExportFileName As String =
     Server.MapPath("/") & ConfigurationSettings.AppSettings("ExportDir") & Session.SessionID & ".pdf"
 Try
  oRpt.ExportOptions.ExportDestinationType = CrystalDecisions.[Shared].ExportDestinationType.DiskFile
  oRpt.ExportOptions.ExportFormatType = CrystalDecisions.[Shared].ExportFormatType.PortableDocFormat
  oDest.DiskFileName = ExportFileName
  oRpt.ExportOptions.DestinationOptions = oDest
  oRpt.Export()
  Response.Clear()
  Response.Buffer = True
  Response.AddHeader("Content-Type", "application/pdf")
  fs = New FileStream(ExportFileName, FileMode.Open)
  FileSize = fs.Length
  Dim bBuffer(CInt(FileSize)) As Byte
  fs.Read(bBuffer, 0, CInt(FileSize))
  fs.Close()
  Response.BinaryWrite(bBuffer)
  Response.Flush()
  Response.Close()
 Catch e As Exception
 End Try
End Sub