Click here to Skip to main content
11,480,626 members (64,375 online)
Click here to Skip to main content

A Fast CSV Writer Using ATL

, 7 Jun 2007 27.6K 1K 29
Rate this:
Please Sign up or sign in to vote.
A Fast CSV Writer Using ATL

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, 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

Methods Description
BlindFetchCSVData Fetch data from one or multiple tables and write to CSV files. The file name has the same name as the table name
FetchCsvData Fetch data into cache specified by a SQL
WriteToCSV Write Cache Data into a CSV file.
GetConnectionStringFromCfg Get DB connection info from a profile file
FetchAndWriteCsv Fetch data specified by a SQL and write to CSV file

ATLCSV Properties

Properties Description
ConnectionString DB connection string

FileSuffix

File suffix.default is CSV
FieldsDelimiter Delimiter between fields
RecordsDelimiter Delimiter between rows
Header Specify header is required or not. Default is required
NumOfTables Number of tables to be queried
TableName Table name to be queried

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

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

callmepls

China China
No Biography provided

Comments and Discussions

 
GeneralWideCharToMultiByte 64 bit Pin
rizomatosa30-Dec-10 12:15
memberrizomatosa30-Dec-10 12:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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
Web04 | 2.8.150520.1 | Last Updated 7 Jun 2007
Article Copyright 2007 by callmepls
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid