Direct SQL (C# / netframework)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\csharp\VS2022\netframework\20.Reports\92.Direct SQL and also at https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/csharp/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.
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.
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.cs
using System.Reflection;
using 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 - 2024 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("7.21.0.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.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Diagnostics;
using System.Reflection;
using System.Resources;
using System.Globalization;
using FlexCel.Core;
using FlexCel.XlsAdapter;
using FlexCel.Report;
using FlexCel.Demo.SharedData;
namespace DirectSQL
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public partial class mainForm: System.Windows.Forms.Form
{
public mainForm()
{
InitializeComponent();
}
private void button1_Click(object sender, System.EventArgs e)
{
using (FlexCelReport genericReport = new FlexCelReport(true))
{
IDbDataAdapter genericAdapter = 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));
string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
genericReport.Run(DataPath + "Direct SQL.template.xls", saveFileDialog1.FileName);
if (MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
using (Process p = new Process())
{
p.StartInfo.FileName = saveFileDialog1.FileName;
p.StartInfo.UseShellExecute = true;
p.Start();
}
}
}
}
finally
{
((IDisposable)genericAdapter).Dispose();
}
}
}
private void btnCancel_Click(object sender, System.EventArgs e)
{
Close();
}
}
}
Form1.Designer.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Diagnostics;
using System.Reflection;
using System.Resources;
using System.Globalization;
using FlexCel.Core;
using FlexCel.XlsAdapter;
using FlexCel.Report;
namespace DirectSQL
{
public partial class mainForm: System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.SaveFileDialog saveFileDialog1;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Button btnCancel;
private System.Windows.Forms.DateTimePicker startDate;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.DateTimePicker endDate;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose(bool disposing)
{
if (disposing)
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose(disposing);
}
#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 void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
this.label1 = new System.Windows.Forms.Label();
this.btnCancel = new System.Windows.Forms.Button();
this.startDate = new System.Windows.Forms.DateTimePicker();
this.label2 = new System.Windows.Forms.Label();
this.endDate = new System.Windows.Forms.DateTimePicker();
this.SuspendLayout();
//
// button1
//
this.button1.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.button1.BackColor = System.Drawing.Color.Green;
this.button1.ForeColor = System.Drawing.Color.White;
this.button1.Location = new System.Drawing.Point(104, 136);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(112, 23);
this.button1.TabIndex = 0;
this.button1.Text = "GO!";
this.button1.UseVisualStyleBackColor = false;
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// saveFileDialog1
//
this.saveFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm|Excel 97/2003|*.xls|Excel 2007|*.xlsx;*.xlsm|All files|*.*";
this.saveFileDialog1.RestoreDirectory = true;
//
// label1
//
this.label1.Location = new System.Drawing.Point(24, 24);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(288, 24);
this.label1.TabIndex = 2;
this.label1.Text = "A demo on how to use direct SQL on a report. ";
//
// btnCancel
//
this.btnCancel.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.btnCancel.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(192)))), ((int)(((byte)(0)))), ((int)(((byte)(0)))));
this.btnCancel.DialogResult = System.Windows.Forms.DialogResult.Cancel;
this.btnCancel.ForeColor = System.Drawing.Color.White;
this.btnCancel.Location = new System.Drawing.Point(224, 136);
this.btnCancel.Name = "btnCancel";
this.btnCancel.Size = new System.Drawing.Size(112, 23);
this.btnCancel.TabIndex = 3;
this.btnCancel.Text = "Cancel";
this.btnCancel.UseVisualStyleBackColor = false;
this.btnCancel.Click += new System.EventHandler(this.btnCancel_Click);
//
// startDate
//
this.startDate.Format = System.Windows.Forms.DateTimePickerFormat.Short;
this.startDate.Location = new System.Drawing.Point(24, 80);
this.startDate.Name = "startDate";
this.startDate.Size = new System.Drawing.Size(144, 20);
this.startDate.TabIndex = 4;
this.startDate.Value = new System.DateTime(1996, 1, 1, 15, 55, 0, 0);
//
// label2
//
this.label2.Location = new System.Drawing.Point(24, 56);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(224, 16);
this.label2.TabIndex = 5;
this.label2.Text = "Get orders between:";
//
// endDate
//
this.endDate.Format = System.Windows.Forms.DateTimePickerFormat.Short;
this.endDate.Location = new System.Drawing.Point(192, 80);
this.endDate.Name = "endDate";
this.endDate.Size = new System.Drawing.Size(144, 20);
this.endDate.TabIndex = 6;
this.endDate.Value = new System.DateTime(1997, 1, 1, 0, 0, 0, 0);
//
// mainForm
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(368, 182);
this.Controls.Add(this.endDate);
this.Controls.Add(this.label2);
this.Controls.Add(this.startDate);
this.Controls.Add(this.btnCancel);
this.Controls.Add(this.label1);
this.Controls.Add(this.button1);
this.Name = "mainForm";
this.Text = "Direct SQL";
this.ResumeLayout(false);
}
#endregion
}
}
Program.cs
using System;
using System.Windows.Forms;
namespace DirectSQL
{
static class Program
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new mainForm());
}
}
}