A Fast CSV Writer Using ATL

• Download ATLCSV - 26.2 KB
• Download ATLV6Client - 10.4 KB
• Download execution files - 62.7 KB
• Download header files- 4.3 KB

Introduction

In some cases, the table data from the database is required to be exported in the form of a CSV file. Generally it seems easy to implement. But when high performance for exporting is required, we have to consider what technology should be adopted and how to realize it. The article shows you a way to build such a program. As you may know, one main goal of ATL is providing a good way to build small, efficient components. The ATL component I'll show you is just written in ATL. The ATL CSV writer provides better efficiency than the same program written in other languages.

Supported features

This CSV writer supports CSV file format. In the ATL CSV writer, <city w:st="on"><place w:st="on">ADO is adopted to access the database which enables you to easily access different database. The writer also implements a simple event sink you can receive data operation related event notification in your client program. In addition, considering performance, the CSV writer also provides many optimizing parameters for improving performance you can specify in your client program. The demo project above shows you how to use these parameters.

Using the code

In order to use ATLCSV component, you need to register the component first using the following command:

> regsvr32 /c atlcsv.dll

I have listed the interface methods and properties in the tables below.

ATLCSV Methods

ATLCSV Properties

After registering the CSV writer, you can run the demo program at command line:

D:\>atlv6client
Usage:
atlv6client user=... table=... sql=... field=... row=... suffix=...
(@) Copyright Thomas WANG 2007, all rights reserved.
Parameters:
user=username/password@tnsname.mandatory parameter
table=table1|table2|...|tablen.mandatory parameter if not specify sql
sql=SQL statement.mandatory parameter if not specify table
opt=nMode|nStart|nDelta start position and Delta.optional.nMode can
be 1,2or3. 1:default value 2:segmentation operations on a table
3:fetch to cache and write.if say 2, also speficy nStart,nDelta.
field=seperator string between fields. default is ","
row=seperator string between rows. default is "\n"
suffix=file suffix. default is csv
Notes: No space adjacent to =
D:\>atlclient user=system/Helpdesk1@test table="vehicle|van" opt="2|1|10000"

Writing Client Codes

Since the ATL CSV Writer implements dual-interfaces, it can be easily used in interpreted languages like C#.NET, VB.NET. If you choose Visual C++, the demo project above is just what you want. Below is an example in Visual C++, for the purpose of simple testing, you can choose to create a simple Win32 console application, then add the following codes in the appropriate place:

// Include header files
#include "..\ATLCSV\ATLCSV\ATLCSV.h"
#include "..\ATLCSV\ATLCSV\ATLCSV_i.c"
// Initialize COM library
::CoInitialize(NULL);

// Get Interface
IComCsvSrv* pIComCsv = NULL;
HRESULT hr = ::CoCreateInstance(CLSID_ComCsvSrv,NULL,CLSCTX_INPROC_SERVER,
        IID_IComCsvSrv,(void**)&pIComCsv);


// Set Database connection
BSTR bstrString = ::SysAllocString(getWCHAR(string));
pIComCsv->put_ConnectionString(bstrString);
::SysFreeString(bstrString);

// Set Field Separator
bstrString = ::SysAllocString(getWCHAR(field));
pIComCsv->put_FieldsDelimiter(bstrString);
::SysFreeString(bstrString);

// Set tables you want to retrieve
pIComCsv->put_NumOfTables(i);
for(int j=0;j<i;j++)
{
    bstrString = ::SysAllocString(_T("tabName[j]"));
    pIComCsv->put_bstrTableName(j,bstrString);
    ::SysFreeString(bstrString);
}

// Fetch Data from database and write to CSV file
pIComCsv->BlindFetchCSVData(2,1,10000);
}

// Unload COM Library
::CoUninitialize(); 

Below is an example in C#. It seems it is more easy to use an ATL component in such a programming language. First, import this COM component in Visual Studio.NET,then add the following sample codes:

using ATLCSVLib;
namespace CSharpClient
{
    public partial class Form1 : Form
    {
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        ATLCSVLib.ComCsvSrvClass bb = new ComCsvSrvClass();
    bb.ConnectionString = "dsn=test;user=system;pwd=Helpdesk1;";
    bb.NumOfTables = 2;
        bb.OnConnectDbOk += 
        new _IComCsvSrvEvents_OnConnectDbOkEventHandler(bb_OnConnectDbOk);
        bb.OnDataFechFinished += 
            new _IComCsvSrvEvents_OnDataFechFinishedEventHandler
                    (bb_OnDataFechFinished);
    bb.OnWriteCSV += 
            new _IComCsvSrvEvents_OnWriteCSVEventHandler(bb_OnWriteCSV);
    bb.set_bstrTableName(0, "vehicle");
     bb.set_bstrTableName(1, "van");
    bb.BlindFetchCSVData();
    }


    void bb_OnWriteCSV(string bstrFileInfo)
    {
        MessageBox.Show("Write!");
    }

    void bb_OnDataFechFinished(string theString)
    {
        MessageBox.Show("Fetch!");
    }

    void bb_OnConnectDbOk(string strDBInfo)
    {
        MessageBox.Show("Connection!");
    }
}
}

Performance

In order to keep high performance, in addition to using ATL, the writer also provides some optimizing parameters to save time when writing CSV files for the operation on a table in which there are mass records.

Bugs

This is not production quality code by any stretch of the imagination. I've tested the code with all the samples I've found. If there is a specific spec/document not working, please post it and I'll have a look. Of course, you can try fixing it yourself!

History

• 8 June 2007: Initial posting