Introduction
This article introduces and implements import and export functions between a flat CSV file and a SQL server. This project implemented in Visual Studio C# 2005, and uses the .NET Framework 2.0.
The base of this project was a former Code Project article: Importing CSV Data and saving it in database.
This project contains new functionalities, for example: export functions, creating table in the database and using bulk copy, which is new in .NET 2.0. (The former project uses .NET 1.1.)
This example gives commented code samples to the following processes:
Import:
- Connect to a CSV file through an ODBC driver, and read it as a database table (based on referenced article)
- Use different encodings and separators (based on referenced article)
- Load a CSV file into a
DataSet
(based on referenced article)
- How to show a preview of a CSV file (based on referenced article)
- Transfer data with a
SqlBulkCopy
instance to a SQL server
Source: a DataSet
instance
- Transfer data with a
SqlBulkCopy
instance to a SQL server
Source: an ODBC connection
- Create a new table in a SQL database based on the CSV file using schema tables
- Write the progress of the bulk copy operation using an event
Export:
- Browse user tables in a SQL database
- Use different encodings and separators
- Read data from SQL server using a
SqlDataReader
, and transfer data to a flat file using a StreamWriter
Using
-
Download the project.
-
Make a new database on a SQL server, or use an existing database.
-
Modify the connection string in the application. You can find it in prop.cs:
public static string sqlConnString = "server=(local);
database=Test_CSV_impex;Trusted_Connection=True";
-
Run the project.
Some Piece of Code
Loading data from CSV into a DataSet
:
public DataSet LoadCSV(int numberOfRows)
{
DataSet ds = new DataSet();
try
{
string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};
Dbq=" + this.dirCSV.Trim() + ";
Extensions=asc,csv,tab,txt;Persist Security Info=False";
string sql_select;
OdbcConnection conn;
conn = new OdbcConnection(strConnString.Trim());
conn.Open();
if (numberOfRows == -1)
{
sql_select = "select * from [" +
this.FileNevCSV.Trim() + "]";
}
else
{
sql_select = "select top " + numberOfRows +
" * from [" + this.FileNevCSV.Trim() + "]";
}
OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);
obj_oledb_da.Fill(ds, "csv");
conn.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Error - LoadCSV",
MessageBoxButtons.OK,MessageBoxIcon.Error);
}
return ds;
}
Transfer data from an ODBC connection into the SQL server with SqlBulkCopy
:
private void SaveToDatabaseDirectly()
{
try
{
if (fileCheck())
{
Format();
Encoding();
writeSchema();
string strConnString = "Driver={Microsoft Text Driver
(*.txt; *.csv)};Dbq=" + this.dirCSV.Trim() + ";
Extensions=asc,csv,tab,txt;
Persist Security Info=False";
string sql_select;
OdbcConnection conn;
conn = new OdbcConnection(strConnString.Trim());
conn.Open();
OdbcCommand commandRowCount = new OdbcCommand
("SELECT COUNT(*) FROM [" +
this.FileNevCSV.Trim() + "]", conn);
this.rowCount = System.Convert.ToInt32
(commandRowCount.ExecuteScalar());
sql_select = "select * from [" +
this.FileNevCSV.Trim() + "]";
OdbcCommand commandSourceData =
new OdbcCommand(sql_select, conn);
OdbcDataReader dataReader =
commandSourceData.ExecuteReader();
DataTable dt;
dt = dataReader.GetSchemaTable();
CreateTableInDatabase(dt, this.txtOwner.Text,
this.txtTableName.Text, prop.sqlConnString);
using (SqlBulkCopy bc = new SqlBulkCopy
("server=(local);database=Test_CSV_impex;
Trusted_Connection=True"))
{
bc.DestinationTableName = "[" +
this.txtOwner.Text + "].[" +
this.txtTableName.Text + "]";
bc.NotifyAfter = 100;
bc.SqlRowsCopied +=
new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
bc.WriteToServer(dataReader);
bc.Close();
}
this.lblProgress.Text = "Imported: " +
this.rowCount.ToString() + "/" +
this.rowCount.ToString() + " row(s)";
this.lblProgress.Refresh();
MessageBox.Show("ready");
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Error - SaveToDatabaseDirectly",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
Creating table in the database based on a schema table:
private bool CreateTableInDatabase(DataTable dtSchemaTable, string tableOwner,
string tableName, string connectionString)
{
try
{
string ctStr = "CREATE TABLE [" + tableOwner + "].[" +
tableName + "](\r\n";
for (int i = 0; i < dtSchemaTable.Rows.Count; i++)
{
ctStr += " [" + dtSchemaTable.Rows[i][0].ToString() + "]
[nvarchar](4000) NULL";
if (i < dtSchemaTable.Rows.Count)
{
ctStr += ",";
}
ctStr += "\r\n";
}
ctStr += ")";
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand command = conn.CreateCommand();
command.CommandText = ctStr;
conn.Open();
command.ExecuteNonQuery();
conn.Close();
return true;
}
catch (Exception e)
{
MessageBox.Show(e.Message, "CreateTableInDatabase",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
Getting user table names from the database:
private void loadTables()
{
SqlConnection cn = new SqlConnection(prop.sqlConnString);
SqlDataAdapter da = new SqlDataAdapter
("select name from dbo.sysobjects where xtype = 'U'
and name <> 'dtproperties' order by name", cn);
DataTable dt = new DataTable();
da.Fill(dt);
this.lbxTables.Items.Clear();
if (dt.Rows.Count == 0)
{
MessageBox.Show("There is no user table in the specified database.
Import a CSV file first.", "Warning",
MessageBoxButtons.OK, MessageBoxIcon.Warning);
this.lbxTables.Items.Add("< no user table in database >");
this.btnExportToCSV.Enabled = false;
}
else
{
this.btnExportToCSV.Enabled = true;
for (int i = 0; i < dt.Rows.Count; i++)
{
this.lbxTables.Items.Add(dt.Rows[i][0].ToString());
}
this.lbxTables.SelectedIndex = 0;
}
}
Writing data with a StreamWriter
:
private void exportToCSVfile(string fileOut)
{
SqlConnection conn = new SqlConnection(prop.sqlConnString);
string sqlQuery = "select * from " + this.lbxTables.SelectedItem.ToString();
SqlCommand command = new SqlCommand(sqlQuery, conn);
conn.Open();
SqlDataReader dr = command.ExecuteReader();
DataTable dtSchema = dr.GetSchemaTable();
StreamWriter sw = new StreamWriter(fileOut, false, this.encodingCSV);
string strRow;
if (this.chkFirstRowColumnNames.Checked)
{
sw.WriteLine(columnNames(dtSchema, this.separator));
}
while (dr.Read())
{
strRow = "";
for (int i = 0; i < dr.FieldCount; i++)
{
strRow += dr.GetString(i);
if (i < dr.FieldCount - 1)
{
strRow += this.separator;
}
}
sw.WriteLine(strRow);
}
sw.Close();
conn.Close();
MessageBox.Show("ready");
}
Reference
History
- 5th November, 2008: Initial post