设为首页
收藏本站
027热线15007117091
 位置: 武汉软件网 >> 文章频道 >> 急救教室 >> 正文 软件定制热线 15007117091  [2008-04-23 08:50:07]      "科能健康体检系统"完成  [2007-12-02 11:15:55]      文档镇守使最新版下载  [2007-12-02 11:13:22]      网站改版完成  [2007-12-02 11:11:40]
 
热门文章
 实力游资席位及私募持仓图...
 盘点激情床戏抢镜的10大影...
 英文三字趣味经
 14种智能化系统在医院的应...
 深入浅出 Linux命令行札记...
 十大免费服务器工具
 六大科技趋势将改变家庭健...
 53年林彪高岗密谋架空总理...
推荐文章
 实力游资席位及私募持仓图...
 盘点激情床戏抢镜的10大影...
 英文三字趣味经
 14种智能化系统在医院的应...
 深入浅出 Linux命令行札记...
 十大免费服务器工具
 六大科技趋势将改变家庭健...
 53年林彪高岗密谋架空总理...
相关文章
C# - CSV Import Export
[ 作者:Admin     来源:武汉软件网     点击数:3635     更新时间:2009-1-28     文章录入:Admin

Download source - 88.67 KB
 http://www.codeproject.com/KB/database/Cs_CSV_import_export/CSV_import_export.zip
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:

 Collapse Copy Codepublic 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:

 Collapse Copy Code/*
 * Loads the csv file into a DataSet.
 *
 * If the numberOfRows parameter is -1, it loads all rows, otherwise it
 * loads the first specified number of rows (for preview)
 */

public DataSet LoadCSV(int numberOfRows)
{
 DataSet ds = new DataSet();
 try
 {
  // Creates and opens an ODBC connection
  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();

  //Creates the select command text
  if (numberOfRows == -1)
  {
   sql_select = "select * from [" +
     this.FileNevCSV.Trim() + "]";
  }
  else
  {
   sql_select = "select top " + numberOfRows +
    " * from [" + this.FileNevCSV.Trim() + "]";
  }

  //Creates the data adapter
  OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);
    
  //Fills dataset with the records from CSV file
  obj_oledb_da.Fill(ds, "csv");
    
  //closes the connection
  conn.Close();
 }
 catch (Exception e) //Error
 {
  MessageBox.Show(e.Message, "Error - LoadCSV",
    MessageBoxButtons.OK,MessageBoxIcon.Error);
 }
 return ds;
}Transfer data from an ODBC connection into the SQL server with SqlBulkCopy:

 Collapse Copy Code/*
 * Imports data to the database with SqlBulkCopy.
 * This method doesn't use a temporary dataset, it loads
 * data immediately from the ODBC connection
 */

private void SaveToDatabaseDirectly()
{
 try
 {
  if (fileCheck())
  {
   // select format, encoding, and write the schema file
   Format();
   Encoding();
   writeSchema();

   // Creates and opens an ODBC connection
   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();

   //Counts the row number in csv file - with an SQL query
   OdbcCommand commandRowCount = new OdbcCommand
    ("SELECT COUNT(*) FROM [" +
    this.FileNevCSV.Trim() + "]", conn);
   this.rowCount = System.Convert.ToInt32
     (commandRowCount.ExecuteScalar());

   // Creates the ODBC command
   sql_select = "select * from [" +
     this.FileNevCSV.Trim() + "]";
   OdbcCommand commandSourceData =
    new OdbcCommand(sql_select, conn);

   // Makes on OdbcDataReader for reading data from CSV
   OdbcDataReader dataReader =
    commandSourceData.ExecuteReader();

   // Creates schema table.
                           // It gives column names for create table command.
   DataTable dt;
   dt = dataReader.GetSchemaTable();

   // You can view that schema table if you want:
   //this.dataGridView_preView.DataSource = dt;

   // Creates a new and empty table in the sql database
   CreateTableInDatabase(dt, this.txtOwner.Text,
    this.txtTableName.Text, prop.sqlConnString);

   // Copies all rows to the database from the data reader.
   using (SqlBulkCopy bc = new SqlBulkCopy
    ("server=(local);database=Test_CSV_impex;
    Trusted_Connection=True"))
   {
    // Destination table with owner -
    // this example doesn't
    // check the owner and table names!
    bc.DestinationTableName = "[" +
     this.txtOwner.Text + "].[" +
     this.txtTableName.Text + "]";

    // User notification with the SqlRowsCopied event
    bc.NotifyAfter = 100;
    bc.SqlRowsCopied +=
       new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

    // Starts the bulk copy.
    bc.WriteToServer(dataReader);

    // Closes the SqlBulkCopy instance
    bc.Close();
   }

   // Writes the number of imported rows to the form
   this.lblProgress.Text = "Imported: " +
    this.rowCount.ToString() + "/" +
    this.rowCount.ToString() + " row(s)";
   this.lblProgress.Refresh();
     
   // Notifies user
   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:

 Collapse Copy Code/*
 * Generates the create table command using the schema table, and
 * runs it in the SQL database.
 */

private bool CreateTableInDatabase(DataTable dtSchemaTable, string tableOwner,
  string tableName, string connectionString)
{
 try
 {
  // Generates the create table command.
  // The first column of schema table contains the column names.
  // The data type is nvarcher(4000) in all columns.

  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 += ")";

  // You can check the SQL statement if you want:
  //MessageBox.Show(ctStr);

  // Runs the SQL command to make the destination table.  
  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:

 Collapse Copy Code/*
 * Loads list of user tables from the SQL database, and fills
 * a ListBox control with tatble names.
 */

private void loadTables()
{
         // Connects to database, and selects the table names.
         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();
   
         // Fills the list to an DataTable.
 da.Fill(dt);

         // Clears the ListBox
 this.lbxTables.Items.Clear();

         // Fills the table names to the ListBox.
         // Notifies user if there is no user table in the database yet.
 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:

 Collapse Copy Code/*
 * Exports data to the CSV file.
 */

private void exportToCSVfile(string fileOut)
{
    // Connects to the database, and makes the select command.
    SqlConnection conn = new SqlConnection(prop.sqlConnString);
    string sqlQuery = "select * from " + this.lbxTables.SelectedItem.ToString();
    SqlCommand command = new SqlCommand(sqlQuery, conn);
    conn.Open();
   
    // Creates a SqlDataReader instance to read data from the table.
    SqlDataReader dr = command.ExecuteReader();

    // Retrieves the schema of the table.
    DataTable dtSchema = dr.GetSchemaTable();

    // Creates the CSV file as a stream, using the given encoding.
    StreamWriter sw = new StreamWriter(fileOut, false, this.encodingCSV);
   
    string strRow; // represents a full row

    // Writes the column headers if the user previously asked that.
    if (this.chkFirstRowColumnNames.Checked)
    {
 sw.WriteLine(columnNames(dtSchema, this.separator));
    }

    // Reads the rows one by one from the SqlDataReader
    // transfers them to a string with the given separator character and
    // writes it to the file.
    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);
    }

    // Closes the text stream and the database connection.
    sw.Close();
    conn.Close();

    // Notifies the user.
    MessageBox.Show("ready");
}Reference

发表评论   告诉好友   打印此文  收藏此页  关闭窗口  返回顶部
  • 上一篇文章: RAID磁盘阵列安装配置详解

  • 下一篇文章: 注意事项! 笔记本电脑更换操作系统
  • ∷相关文章评论∷    (评论内容只代表网友观点,与本站立场无关!)
     
     

    Copyright© 2001-2007 ICP备案号:鄂ICP备05027628号
    027-soft.com 武汉软件定制
    本站为武汉城市圈内企业提供管理软件开发,软件策划等服务
    发表、收集一些企业E化的实例及其经验等的资料与大家共享。
    希望大家多提意见,同时共同进步。

    | 页面执行时:0.154 毫秒