Click here to Skip to main content
12,243,074 members (45,946 online)
Click here to Skip to main content
Add your own
alternative version

Stats

64.4K views
3.8K downloads
75 bookmarked
Posted

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

, 8 Aug 2007
Rate this:
Please Sign up or sign in to vote.
A solution to the problem of exporting data from database in different formats
Screenshot - databaseExport_1.gif

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.

Screenshot - databaseExport_2small.gif

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Dragos Boia
Software Developer
Romania Romania
Language:
C/C++,Python,C#
Technology:
DCOM, MTS, COM, COM+, RPC.
Corba (ACE/TAO implementation).
I'm interested in algorithms and problem solving.

You may also be interested in...

Comments and Discussions

 
QuestionMy vote of 5! Pin
Philip Cotan8-Feb-14 11:57
memberPhilip Cotan8-Feb-14 11:57 
GeneralHelp..... Pin
onsmet23-Jun-08 15:48
memberonsmet23-Jun-08 15:48 
Generalcode.zip is invalid or corruped Pin
SatheeshV16-Jun-08 21:31
memberSatheeshV16-Jun-08 21:31 
GeneralSQL Server Compact Edition Pin
SatheeshV16-Jun-08 21:00
memberSatheeshV16-Jun-08 21:00 
QuestionOFF TOPIC Pin
stixoffire24-May-08 10:11
memberstixoffire24-May-08 10:11 
GeneralFox Pro Pin
stixoffire23-May-08 10:17
memberstixoffire23-May-08 10:17 
GeneralRe: Fox Pro Pin
Dragos Boia24-May-08 8:26
memberDragos Boia24-May-08 8:26 
GeneralRe: Fox Pro Pin
stixoffire24-May-08 10:07
memberstixoffire24-May-08 10:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160426.1 | Last Updated 8 Aug 2007
Article Copyright 2007 by Dragos Boia
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid