Direct SQL (VB.Net / netframework)

Note

This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\vb\VS2022\netframework\20.Reports\92.Direct SQL and also at https:​//​github.​com/​tmssoftware/​TMS-​FlexCel.​NET-​demos/​tree/​master/​vb/​VS2022/​netframework/​Modules/​20.​Reports/​92.​Direct SQL

Overview

In normal FlexCel reports, you create the data layer in code and the template in Excel. This allows your user to easily modify the presentation layer with Excel and without needing to modify the code.

While this is a very good choice for most reports (as the data layer does not change much, and the presentation does), sometimes you might want to allow your users to directly modify the data layer from Excel. This is when you can use Direct SQL in templates.

Concepts

  • By default, you can't use Direct SQL on reports. To be able to use it, you need to add a connection to the report with FlexCelReport.AddConnection in the code.

  • Allowing the final user to specify the SQL directly on the report might carry big security risks.

    1. A user might use the SQL to modify the data. For example, instead of a normal SQL like "select * from customer" he might write "delete from customers" and erase all information. While FlexCel does a little validation on the SQL string, (for example it will not allow SQLs not starting with "SELECT") there are always ways to modify the data.

    2. A user might get access to data you don't intend him to. For example, he might know the user passwords are on the table "Users" and use an existing unrelated report to get the data from this table.

  • To keep your application secure, it is recommended that:

  • You give Read Only access rights to the connection you add to the report, and only give access to the tables the report needs to use

  • You enable Direct SQL on cases where you can control the templates. For example on a web application (where the templates are always on the server) or a GUI App where templates are kept on a server. Or, of course, if you don't care about the data because it is not important, or the customer has administrator access to all the database anyway.

  • Do not use Encrypted templates to give security to the system. While this can give some extra security, encrypted xls files can be cracked with tools available in Internet.

  • To use Direct SQL on the templates, you write SQL(connection, sqlstring) on the "Source name" column in the config sheet.

  • You can't use expressions inside an SQL statement. The "sqlstring" you pass as parameter to the SQL() command will not allow any expression replacement. Again, this is a security decision to avoid SQL Injection attacks. (If you don't know what this is, you can search for "SQL injection" in Internet to get a more in-depth description of the problem)

  • As you can't use expressions inside the SQL, you need to be able to pass parameters to it. To pass a parameter to the SQL:

  • In SQL databases you can normally use two types of parameters: positional ("?") and named ("@param" or ":param"). On order to keep your templates database-independent, FlexCel Templates will always use named parameters starting with "@". Even if you are accessing ODBC, you should write "SELECT * from table where field = @field" and never "SELECT * from table where field = ?" in the template. The only supported syntax in templates is "@" parameters; FlexCel will convert the parameters to what's needed when calling the actual SQL.

  • You then can set the parameters in the code, by using FlexCelReport.AddSqlParameter This is not database independent, and you need to add the correct type of parameter for the actual database here.

Files

AssemblyInfo.vb

Imports System.Reflection
Imports System.Runtime.CompilerServices

'
' General Information about an assembly is controlled through the following 
' set of attributes. Change these attribute values to modify the information
' associated with an assembly.
'
<Assembly: AssemblyTitle("")>
<Assembly: AssemblyDescription("")>
<Assembly: AssemblyConfiguration("")>
<Assembly: AssemblyCompany("")>
<Assembly: AssemblyProduct("")>
<Assembly: AssemblyCopyright("(c) 2002 - 2014 TMS Software")>
<Assembly: AssemblyTrademark("")>
<Assembly: AssemblyCulture("")>

'
' Version information for an assembly consists of the following four values:
'
'      Major Version
'      Minor Version 
'      Build Number
'      Revision
'
' You can specify all the values or you can default the Revision and Build Numbers 
' by using the '*' as shown below:

<Assembly: AssemblyVersion("6.2.1.0")>

'
' In order to sign your assembly you must specify a key to use. Refer to the 
' Microsoft .NET Framework documentation for more information on assembly signing.
'
' Use the attributes below to control which key is used for signing. 
'
' Notes: 
'   (*) If no key is specified, the assembly is not signed.
'   (*) KeyName refers to a key that has been installed in the Crypto Service
'       Provider (CSP) on your machine. KeyFile refers to a file which contains
'       a key.
'   (*) If the KeyFile and the KeyName values are both specified, the 
'       following processing occurs:
'       (1) If the KeyName can be found in the CSP, that key is used.
'       (2) If the KeyName does not exist and the KeyFile does exist, the key 
'           in the KeyFile is installed into the CSP and used.
'   (*) In order to create a KeyFile, you can use the sn.exe (Strong Name) utility.
'       When specifying the KeyFile, the location of the KeyFile should be
'       relative to the project output directory which is
'       %Project Directory%\obj\<configuration>. For example, if your KeyFile is
'       located in the project directory, you would specify the AssemblyKeyFile 
'       attribute as [assembly: AssemblyKeyFile("..\\..\\mykey.snk")]
'   (*) Delay Signing is an advanced option - see the Microsoft .NET Framework
'       documentation for more information on this.
'
<Assembly: AssemblyDelaySign(False)>
<Assembly: AssemblyKeyFile("")>
<Assembly: AssemblyKeyName("")>

Form1.Designer.vb

Imports System.Collections
Imports System.ComponentModel
Imports System.IO
Imports System.Reflection
Imports System.Resources
Imports System.Globalization
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Imports FlexCel.Report
Namespace DirectSQL
    Partial Public Class mainForm
        Inherits System.Windows.Forms.Form

        Private WithEvents button1 As System.Windows.Forms.Button
        Private saveFileDialog1 As System.Windows.Forms.SaveFileDialog
        Private label1 As System.Windows.Forms.Label
        Private WithEvents btnCancel As System.Windows.Forms.Button
        Private startDate As System.Windows.Forms.DateTimePicker
        Private label2 As System.Windows.Forms.Label
        Private endDate As System.Windows.Forms.DateTimePicker
        ''' <summary>
        ''' Required designer variable.
        ''' </summary>
        Private components As System.ComponentModel.Container = Nothing

        ''' <summary>
        ''' Clean up any resources being used.
        ''' </summary>
        Protected Overrides Sub Dispose(ByVal disposing As Boolean)
            If disposing Then
                If components IsNot Nothing Then
                    components.Dispose()
                End If
            End If
            MyBase.Dispose(disposing)
        End Sub

        #Region "Windows Form Designer generated code"
        ''' <summary>
        ''' Required method for Designer support - do not modify
        ''' the contents of this method with the code editor.
        ''' </summary>
        Private Sub InitializeComponent()
            Me.button1 = New System.Windows.Forms.Button()
            Me.saveFileDialog1 = New System.Windows.Forms.SaveFileDialog()
            Me.label1 = New System.Windows.Forms.Label()
            Me.btnCancel = New System.Windows.Forms.Button()
            Me.startDate = New System.Windows.Forms.DateTimePicker()
            Me.label2 = New System.Windows.Forms.Label()
            Me.endDate = New System.Windows.Forms.DateTimePicker()
            Me.SuspendLayout()
            ' 
            ' button1
            ' 
            Me.button1.Anchor = (CType((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles))
            Me.button1.BackColor = System.Drawing.Color.Green
            Me.button1.ForeColor = System.Drawing.Color.White
            Me.button1.Location = New System.Drawing.Point(104, 136)
            Me.button1.Name = "button1"
            Me.button1.Size = New System.Drawing.Size(112, 23)
            Me.button1.TabIndex = 0
            Me.button1.Text = "GO!"
            Me.button1.UseVisualStyleBackColor = False
'           Me.button1.Click += New System.EventHandler(Me.button1_Click)
            ' 
            ' saveFileDialog1
            ' 
            Me.saveFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm|Excel 97/2003|*.xls|Excel 2007|*.xlsx;*.xlsm|All files|*.*"
            Me.saveFileDialog1.RestoreDirectory = True
            ' 
            ' label1
            ' 
            Me.label1.Location = New System.Drawing.Point(24, 24)
            Me.label1.Name = "label1"
            Me.label1.Size = New System.Drawing.Size(288, 24)
            Me.label1.TabIndex = 2
            Me.label1.Text = "A demo on how to use direct SQL on a report. "
            ' 
            ' btnCancel
            ' 
            Me.btnCancel.Anchor = (CType((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles))
            Me.btnCancel.BackColor = System.Drawing.Color.FromArgb((CInt((CByte(192)))), (CInt((CByte(0)))), (CInt((CByte(0)))))
            Me.btnCancel.DialogResult = System.Windows.Forms.DialogResult.Cancel
            Me.btnCancel.ForeColor = System.Drawing.Color.White
            Me.btnCancel.Location = New System.Drawing.Point(224, 136)
            Me.btnCancel.Name = "btnCancel"
            Me.btnCancel.Size = New System.Drawing.Size(112, 23)
            Me.btnCancel.TabIndex = 3
            Me.btnCancel.Text = "Cancel"
            Me.btnCancel.UseVisualStyleBackColor = False
'           Me.btnCancel.Click += New System.EventHandler(Me.btnCancel_Click)
            ' 
            ' startDate
            ' 
            Me.startDate.Format = System.Windows.Forms.DateTimePickerFormat.Short
            Me.startDate.Location = New System.Drawing.Point(24, 80)
            Me.startDate.Name = "startDate"
            Me.startDate.Size = New System.Drawing.Size(144, 20)
            Me.startDate.TabIndex = 4
            Me.startDate.Value = New Date(1996, 1, 1, 15, 55, 0, 0)
            ' 
            ' label2
            ' 
            Me.label2.Location = New System.Drawing.Point(24, 56)
            Me.label2.Name = "label2"
            Me.label2.Size = New System.Drawing.Size(224, 16)
            Me.label2.TabIndex = 5
            Me.label2.Text = "Get orders between:"
            ' 
            ' endDate
            ' 
            Me.endDate.Format = System.Windows.Forms.DateTimePickerFormat.Short
            Me.endDate.Location = New System.Drawing.Point(192, 80)
            Me.endDate.Name = "endDate"
            Me.endDate.Size = New System.Drawing.Size(144, 20)
            Me.endDate.TabIndex = 6
            Me.endDate.Value = New Date(1997, 1, 1, 0, 0, 0, 0)
            ' 
            ' mainForm
            ' 
            Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.ClientSize = New System.Drawing.Size(368, 182)
            Me.Controls.Add(Me.endDate)
            Me.Controls.Add(Me.label2)
            Me.Controls.Add(Me.startDate)
            Me.Controls.Add(Me.btnCancel)
            Me.Controls.Add(Me.label1)
            Me.Controls.Add(Me.button1)
            Me.Name = "mainForm"
            Me.Text = "Direct SQL"
            Me.ResumeLayout(False)

        End Sub
        #End Region
    End Class
End Namespace

Form1.vb

Imports System.Collections
Imports System.ComponentModel
Imports System.IO
Imports System.Reflection
Imports System.Resources
Imports System.Globalization
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Imports FlexCel.Report
Imports FlexCel.Demo.SharedData


Namespace DirectSQL
    ''' <summary>
    ''' Summary description for Form1.
    ''' </summary>
    Partial Public Class mainForm
        Inherits System.Windows.Forms.Form

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.Click
            Using genericReport As New FlexCelReport(True)
                Dim genericAdapter As IDbDataAdapter = SharedData.GetDataAdapter()
                Try
                    genericReport.SetValue("ReportCaption", "Sales by Country and Employee")
                    genericReport.AddConnection("Northwind", genericAdapter, CultureInfo.CurrentCulture)

                    'In OleDb the parameters are positional, you don't really need to name them when creating them.
                    'But when you are using an SQL Server connection, you *need*
                    'to specify the parameter name ("@StartDate") and make it equal to "@" + the name
                    'of the parameter. It is recommended that you always specify the name, even in OleDb connections.

                    'Also, we are not going to create the parameters directly here (using new SqlCeParameter(...).
                    'We are going to centralize all data access for the demos in SharedData, so we can change it and change all demos.
                    genericReport.AddSqlParameter("StartDate", SharedData.CreateParameter("@StartDate", startDate.Value.Date))
                    genericReport.AddSqlParameter("EndDate", SharedData.CreateParameter("@EndDate", endDate.Value.Date))
                    Dim DataPath As String = Path.Combine(Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), ".."), "..") & Path.DirectorySeparatorChar

                    If saveFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                        genericReport.Run(DataPath & "Direct SQL.template.xls", saveFileDialog1.FileName)

                        If MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) = System.Windows.Forms.DialogResult.Yes Then
                            Process.Start(saveFileDialog1.FileName)
                        End If
                    End If
                Finally
                    CType(genericAdapter, IDisposable).Dispose()
                End Try
            End Using
        End Sub

        Private Sub btnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCancel.Click
            Close()
        End Sub
    End Class

End Namespace

Program.vb

Namespace DirectSQL
    Friend NotInheritable Class Program

        Private Sub New()
        End Sub

        ''' <summary>
        ''' The main entry point for the application.
        ''' </summary>
       <STAThread> _
        Shared Sub Main()
            Application.EnableVisualStyles()
            Application.SetCompatibleTextRenderingDefault(False)
            Application.Run(New mainForm())
        End Sub
    End Class
End Namespace