Export from Database In Different Formats: PDF, Excel, Access, XML, HTML, FoxPro, CSV

• Download source - 7,674.1 KB
• Download demo - 2,144.3 KB

Introduction

Problem definition: when you need to export data from the client application you write a lot of code to do it. More than this when you need to export data from a slower client there may be a problem.

The solution is to create an application server to do the job for the slower clients. But what if your client is strong enough and doesn't need to create the export file on the server? Then you set your client to do the export by himself.

My intention is to solve the old issue of exporting data from the database.

Library explanation

Comutility.dll is used for managing a COM server connection and administrating the catalog.

Database.dll is used for managing the database connection.

DatabaseExportServer.dll is the COM server responsible for getting the data from the database and exporting them in different formats.

DatabaseExport.dll is the components which can be embedded into your application (the interface for the clients).

Installcomserver.exe is a useful application for installing the COM server.

And testDatabaseExport, testDatabaseExportCCharp are examples for using the library.

For having a background to understanding this article you need to know: COM, COM+, ADO, ODBC, STL and ATL.

UML class diagram for the export structure.

Using The Code

Using the components from an ATL dialog project you need to include the file ../include/includeExportClient.h into your project.

The namespace ATL_PROJECT_EXPORT defined into this file contains these methods:

Parameters description: HWND parent is the handle to the parent which containts the control and unsigned int IDD is the id of the control.

The solution file for the code is in the databaseExport directory. For installing the COM server you have a BAT file named installcomserver.bat in the demo project. Also, you have two TXT files: one is com_components.txt (which components belongs to the server) and another Connection.txt which stores information about the connection to the COM and the database servers.

//

namespace ATL_PROJECT_EXPORT
{ 
   inline void db_address(HWND parent,unsigned int IDD,
       const char *val) // set the address of the database

   inline void db_name(HWND parent,unsignedint IDD,
       const char * val) // set the name of the database 

   inline void db_user(HWND parent,unsigned int IDD,
       const char * val) // set the user name for accessing the  database

   inline void db_password(HWND parent,unsigned int IDD,
       const char * val) // set the password for the database user

   inline void ComAddress(HWND parent,unsigned int IDD,
      const char * val) // set the address of the com server(the IP of the 

      // host)

   inline void ComDomain(HWND parent,unsigned int IDD,
       const char * val) // set the domain name of the com server if 

       // you have one else set the IP of the host

   inline void ComUser(HWND parent,unsigned int IDD,
       const char * val) // set the user name for accessing com server    

   inline void ComPassword(HWND parent,unsigned int IDD,
       const char * val) // set the password for the user who access the com 

       // server        

   inline void SqlStatement(HWND parent,unsigned int IDD,
      const char * val) // set the sql statement for your export

   inline void ComServer(HWND parent,unsigned int IDD,
      bool val) // specify if you want or not to use a com server

   inline void ClientSideExport(HWND parent,unsigned int IDD,
      bool val) // set where do you want to make the export on the client side 

      /// or on the server side

   inline void ExportType(HWND parent,unsigned int IDD,
      ExportType val) // set the document type to be exported

   inline void FileName(HWND parent,unsigned int IDD,
      const char* val) // set the name of the file where you will get the 

      // result after the export

   inline void ConnectToExportServer(HWND parent,
      unsigned int IDD) // connect to the application server(com server)

   inline void Export(HWND parent,unsigned int IDD) // do the export

}

In the example testDatabaseExportCCharp made in C#, I use a database for the test named TestExport. After importing the databaseExport control into the project, StudioNet generates this wrapper:

//documents types

namespace databaseExportLib
{
    public enum ExportType
    {
        Excel = 0,
        FoxPro = 1,
        Csv = 2,
        Pdf = 3,
        Access = 4,
        Html = 5,
        Xml = 6,
    }
}

//database servers types//it's functional only for the _SqlMicrosoft

namespace databaseExportLib
{
    public enum __DbServerType
    {
        _SqlMicrosoft = 0,
        _MySql= 1,
        _Oracle = 2,
    }
}
namespace AxdatabaseExportLib
{
    [DesignTimeVisible(true)]
public class Axdrb_databaseExport : AxHost
{
    public Axdrb_databaseExport();

    protected override void AttachInterfaces();
    protected override void CreateSink();
    protected override void DetachSink();

    [DesignerSerializationVisibility(0)]
    [DispId(11)]
    public virtual bool ClientSideExport { get; set; } // set where do you 

    // want to make the export on the client side or on the server side


    [DesignerSerializationVisibility(0)]
    [DispId(5)]
    public virtual string ComAddress { get; set; } // set the address of the 

    // com server(the IP of the host)


    [DispId(6)]
    [DesignerSerializationVisibility(0)]
    public virtual string ComDomain { get; set; } // set the domain name of 

    // the com server if you have one else set the IP of the host


    [DispId(8)]
    [DesignerSerializationVisibility(0)]
    public virtual string ComPassword { get; set; } // set the password for 

    // the user who access the com server


    [DesignerSerializationVisibility(0)]
    [DispId(10)]
    public virtual bool ComServer { get; set; } // specify if you want or not 

    // to use a com server


    [DispId(7)]
    [DesignerSerializationVisibility(0)]
    public virtual string ComUser { get; set; } // set the user name for 

    // accessing com server


    [DispId(1)]
    [DesignerSerializationVisibility(0)]
    public virtual string db_address { get; set; } // set the address of the 

    // database


    [DesignerSerializationVisibility(0)]
    [DispId(2)]
    public virtual string db_name { get; set; } // set the name of the database


    [DesignerSerializationVisibility(0)]
    [DispId(4)]
    public virtual string db_password { get; set; } // set the password for 

    // the database user


    [DesignerSerializationVisibility(0)]
    [DispId(3)]
    public virtual string db_user { get; set; } // set the user name for 

    // accessing the database


    [DesignerSerializationVisibility(0)]
    [DispId(16)]
    public virtual __DbServerType DbServerType { get; set; } // set type of 

    // the database server it's works only for SqlMicrosoft


    [DispId(14)]
    [DesignerSerializationVisibility(0)]
    public virtual ExportType ExportType { get;set; } // set the document type 

    // to be exported


    [DispId(17)]
    [DesignerSerializationVisibility(0)]
    public virtual string FileName { get; set; } // set the name of the file 

    // where you will get the result after the export


    [DesignerSerializationVisibility(0)]
    [DispId(18)]
    public virtual bool IfFileExistsRecreate { get; set; } // specify if you 

    // want to overwrite an existing file


    [DispId(19)]
    [DesignerSerializationVisibility(0)]
    public virtual bool MessageConfirmation { get; set; } // with or without 

    // message box confirmation after the export was done


    [DispId(9)]
    [DesignerSerializationVisibility(0)]
    public virtual string SqlStatement { get; set; } // set the sql statement 

    // for your export


    [DispId(12)]
    [DesignerSerializationVisibility(0)]
    public virtual bool StartAtOnCreate { get; set; } // connect to the 

    // application server automatically after the control was created or 

    // connecting to the com server explicit by using the function 

    // ConnectToExportServer public virtual void ConnectToExportServer(); 

    // connect to the application server(com server)

    public virtual void Export(); // do the export }

}

C# Example code:
// For storing the connections address for the application server 

// and the database I use a file Connection.txt with this structure:

// each lines contain a string which represents something for the 

// connections.Format example :"db_address=HostOne"

// which mean the address for the database server is HostOne principal form


public Form1()
{
    InitializeComponent();
    InitControl();
}
// the event for the export button


private void button1_Click(object sender, EventArgs e)
{
    // If I dont find the seetings for type of the database, Export and if I 

    // use or not a server side export


    if(this.comboBox1.Text=="" || this.comboBox2.Text=="" || 
        this.comboBox3.Text=="")
    {
        MessageBox.Show("Error");
        return ;
    }

    //the implementation is available only for Microsoft Sql(future for Oracle 

    // and MySql,if you want more please post a message on the board below


    if(this.comboBox1.Text!="SqlMicrosoft")
    {
        MessageBox.Show("Not yet implemented!");
        return ;
    }

    if(this.comboBox3.Text =="Client Side With ComServer") 
    {
        this.axdrb_databaseExport1.ComServer = true; 
        this.axdrb_databaseExport1.ClientSideExport= true;
    }
    else
        if (this.comboBox3.Text == "Client Side Without ComServer")
        {
            this.axdrb_databaseExport1.ComServer= false;
            this.axdrb_databaseExport1.ClientSideExport = true;
        }
        else //server side export

        {
            this.axdrb_databaseExport1.ComServer = true;
            this.axdrb_databaseExport1.ClientSideExport= false;
        }

        if(this.comboBox2.Text=="Excel") 
this.axdrb_databaseExport1.ExportType= databaseExportLib.ExportType.Excel;
        else
            if(this.comboBox2.Text=="FoxPro") 
this.axdrb_databaseExport1.ExportType= databaseExportLib.ExportType.FoxPro;
            else
                if(this.comboBox2.Text=="Csv") 
this.axdrb_databaseExport1.ExportType= databaseExportLib.ExportType.Csv;
                else
                    if(this.comboBox2.Text=="Pdf") 
this.axdrb_databaseExport1.ExportType= databaseExportLib.ExportType.Pdf;
                    else
                        if(this.comboBox2.Text=="Access") 
this.axdrb_databaseExport1.ExportType= databaseExportLib.ExportType.Access;
                        else
                            if(this.comboBox2.Text=="Html") 
this.axdrb_databaseExport1.ExportType= databaseExportLib.ExportType.Html;
                            else
                                if(this.comboBox2.Text=="Xml") 
this.axdrb_databaseExport1.ExportType= databaseExportLib.ExportType.Xml;

        axdrb_databaseExport1.Export();//do the export

}

private void InitControl()
{
    //prepare the connection to the com server


    this.comboBox1.Text="SqlMicrosoft";
    this.comboBox2.Text="Html";
    this.comboBox3.Text="Client Side With ComServer";
    StreamReader readFile=System.IO.File.OpenText("Connection.txt");

    int count = 0;

    while (!readFile.EndOfStream)
    {
        string temp=readFile.ReadLine();
        int pos=temp.LastIndexOf("=");

        switch (count)
        { 
            case 0: 
            { 
                this.axdrb_databaseExport1.db_address=temp.Substring(pos + 1); 
                break;
            }
            case 1: 
            {
                this.axdrb_databaseExport1.db_name=temp.Substring(pos + 1); 
                break; 
            }
            case 2: 
            { 
                this.axdrb_databaseExport1.db_user=temp.Substring(pos + 1); 
                break;
            }
            case 3: 
            {
                this.axdrb_databaseExport1.db_password=temp.Substring(pos + 1); 
                break; 
            }
            case 4: 
            { 
                this.axdrb_databaseExport1.ComAddress=temp.Substring(pos + 1); 
                break;
            }
            case 5: 
            { 
                this.axdrb_databaseExport1.ComDomain=temp.Substring(pos + 1); 
                break;
            }
            case 6: 
            { 
                this.axdrb_databaseExport1.ComUser=temp.Substring(pos + 1); 
                break;
            }
            case 7: 
            { 
                this.axdrb_databaseExport1.ComPassword=temp.Substring(pos + 1); 
                break; 
            }
        } 
        count++;
    }

    readFile.Close();
    this.axdrb_databaseExport1.SqlStatement = 
        "select * from test_table";
    this.axdrb_databaseExport1.FileName = "DefaultName";
    this.axdrb_databaseExport1.ConnectToExportServer();
}

// Also you have an example in C++ which is made in ATL dialog project type 

// (the sample is the same like in C#).

Points of Interest

I'm interested in your opinion about my code and the utility of it (also if you use this library please post me a message at the forum below).

History

Version 1.0 on 30.07.2007.