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
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 "..\ATLCSV\ATLCSV\ATLCSV.h"
#include "..\ATLCSV\ATLCSV\ATLCSV_i.c"
::CoInitialize(NULL);
IComCsvSrv* pIComCsv = NULL;
HRESULT hr = ::CoCreateInstance(CLSID_ComCsvSrv,NULL,CLSCTX_INPROC_SERVER,
IID_IComCsvSrv,(void**)&pIComCsv);
BSTR bstrString = ::SysAllocString(getWCHAR(string));
pIComCsv->put_ConnectionString(bstrString);
::SysFreeString(bstrString);
bstrString = ::SysAllocString(getWCHAR(field));
pIComCsv->put_FieldsDelimiter(bstrString);
::SysFreeString(bstrString);
pIComCsv->put_NumOfTables(i);
for(int j=0;j<i;j++)
{
bstrString = ::SysAllocString(_T("tabName[j]"));
pIComCsv->put_bstrTableName(j,bstrString);
::SysFreeString(bstrString);
}
pIComCsv->BlindFetchCSVData(2,1,10000);
}
::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