Saturday 2 June 2012

Upload CSV file to SQL Server using CsvDataReader, and SqlBulkCopy in ASP.Net 2.0


Upload CSV file to SQL Server using
CsvDataReader, and SqlBulkCopy in ASP.Net 2.0



File Format
First,Last,Date,Amount
John,Smith,02/05/2005,$52.15
Jane,Doe,01/23/2006,$71.95
...
Create Table Script
CREATE TABLE [dbo].[Customer] (
        [customer_id] [int] IDENTITY (1, 1) NOT NULL ,
        [first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [first_sale] [datetime] NOT NULL ,
        [sale_amount] [money] NOT NULL 
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD 
        CONSTRAINT [PK_Customer] PRIMARY KEY  CLUSTERED 
        (
               [customer_id]
        )  ON [PRIMARY] 
GO
C#
protected void uploadButton_Click(object sender, EventArgs e)
{
    // declare CsvDataReader object which will act as a source for data for SqlBulkCopy
    using (CsvDataReader csvData = new CsvDataReader(fileUpload.PostedFile.InputStream, Encoding.Default))
    {
        // will read in first record as a header row and
        // name columns based on the values in the header row
        csvData.Settings.HasHeaders = true;
 
        // must define data types to use while parsing data
        csvData.Columns.Add("varchar"); // First
        csvData.Columns.Add("varchar"); // Last
        csvData.Columns.Add("datetime"); // Date
        csvData.Columns.Add("money"); // Amount
 
        // declare SqlBulkCopy object which will do the work of bringing in data from
        // CsvDataReader object, connecting to SQL Server, and handling all mapping
        // of source data to destination table.
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy("Data Source=.;Initial Catalog=Test;User ID=sa;Password="))
        {
            // set the name of the destination table that data will be inserted into.
            // table must already exist.
            bulkCopy.DestinationTableName = "Customer";
 
            // mappings required because we're skipping the customer_id column
            // and letting SQL Server handle auto incrementing of primary key.
            // mappings not required if order of columns is exactly the same
            // as destination table definition. here we use source column names that
            // are defined in header row in file.
            bulkCopy.ColumnMappings.Add("First", "first_name"); // map First to first_name
            bulkCopy.ColumnMappings.Add("Last", "last_name"); // map Last to last_name
            bulkCopy.ColumnMappings.Add("Date", "first_sale"); // map Date to first_sale
            bulkCopy.ColumnMappings.Add("Amount", "sale_amount"); // map Amount to sale_amount
 
            // call WriteToServer which starts import
            bulkCopy.WriteToServer(csvData);
 
        } // dispose of SqlBulkCopy object
 
    } // dispose of CsvDataReader object
 
} // end uploadButton_Click

2 comments:

  1. Bhutni k Copy paste maarta hai bus...

    ReplyDelete
  2. I want to get data from CsvDataReader to DataTable

    -------------------------------------
    using (CsvDataReader csvData = new CsvDataReader(new StreamReader(fl_Import_Item.PostedFile.InputStream, true)))
    {
    csvData.Settings.HasHeaders = true;

    csvData.Columns.Add("varchar");
    csvData.Columns.Add("varchar");
    csvData.Columns.Add("datetime");
    csvData.Columns.Add("money");

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Common_Class.connection_string))
    {
    bulkCopy.DestinationTableName = "Customer";

    bulkCopy.ColumnMappings.Add("First", "first_name");
    bulkCopy.ColumnMappings.Add("Last", "last_name");
    bulkCopy.ColumnMappings.Add("Date", "first_sale");
    bulkCopy.ColumnMappings.Add("Amount", "sale_amount");

    bulkCopy.WriteToServer(csvData);

    var dataTable = new DataTable();

    dataTable.Load(csvData.ReadRecord);
    //bulkCopy.WriteToServer(dataTable);
    }
    }

    ReplyDelete