Introduction
We have drivers like ‘Microsoft Text Driver (*.txt, *.csv)’ for acquiring files as data source and read/write data to the files. These drivers are used to create DSN to a particular directory. After Creating UserDSN to that Directory we can access all the specific format files in that specified directory with the help of SQL command. Now we have our own DSN so we can make use of the ADO connectivity to read the csv file, which is the data source of the connection.
Creating UserDSN
Create a UserDSN in Data Sources (ODBC)
Steps:
1. Click Add button for adding new DSN
2. Select ‘Microsoft Text Driver (*.txt, *.csv)’ or some available driver to read csv file
3. Select a path from where the CSV file has to be considered
Reading a CSV file using UserDSN:
Now, we can use the DSN name in the ADO connection string and access the file using command object then we can read the data using a Reader object
cn=new System.Data.Odbc.OdbcConnection ("Provider=MSDASQL;DSN=test;");
cmd=new System.Data.Odbc.OdbcCommand ("select * from Emp.csv",cn);
dr=cmd. ExecuteReader ();
dr. Read ();
ShowTextBox.Text=dr [0]. ToString ();
This is the simple way to access as CSV file and retrieving data from the file, but it requires to store the particular file, which you need to read should be stored in a specified location in the created DSN.
In real time we will select a file using a browse button and then we will read the selected file. To implement this we need to create a UserDSN at runtime for the selected file path. Please go through the following information for the implementation
Creating UserDSN at Runtime
ODBCCP32.dll contains functions to do data source administration. It is a Win32 DLL that exports API for managing data source. Being an unmanaged DLL, interop is a way to access the function.
I have created a class for importing and accessing functions of ODBCCP32.dll called
using System;
using System.Runtime.InteropServices;
namespace ReadingCSVFile
{
public class ClassImport
{
public ClassImport()
{
}
[DllImport("ODBCCP32.dll")]
public static extern bool SQLConfigDataSource(
IntPtr parent, int request, string driver, string attributes);
public bool AddUserDSN(string DSName, string DBPath)
{
string driver = "Microsoft Text Driver (*.txt; *.csv)";
string param = "DSN=TestDSN\0DBQ=" + DBPath + "\0";
return SQLConfigDataSource((IntPtr)0, 1, driver, param);
}
}
}
The ODBCCP32.dll provided with a function called SQLConfigDataSource. This function is used to set the UserDSN details at runtime. As shown above the parent parameter refers to the parent window handle and can be set to 0. The request parameter is a number from 1 to 6 and refers to the requested action. This is a list of the values and their actions:
1. ODBC_ADD_DSN (use this to add a user DSN)
2. ODBC_CONFIG_DSN (use this to configure a user DSN)
3. ODBC_REMOVE_DSN (use this to remove a user DSN)
4. ODBC_ADD_SYS_DSN (use this to add a system DSN)
5. ODBC_CONFIG_SYS_DSN (use this to configure a system DSN)
6. ODBC_REMOVE_SYS_DSN (use this to remove a system DSN)
The driver parameter is the name of the driver, and the attributes parameter contains the database path and other login information
The AddUserDSN function is executed with the selected file path and a name for the DSN. Now we have created a UserDSN with the specified name and the path.
Now we can access the CSV file using the newly created UserDSN name
Summary
This article gives a complete idea on how to connect and access CSV file using ADO.NET. The same time it gives idea to create UserDSN dynamically
|
| Author: Bruce Dunwiddie 26 Apr 2005 | Member Level: Bronze Points : 0 |
Keep in mind benchmarks for using this approach. At very least, use the OLE adapter instead of the ODBC.
some pages with benchmarks:
http://www.geocities.com/shriop/csv_benchmarks.html
http://www.codeproject.com/cs/database/CsvReader.asp
|