Description

DataStreams is a library of components designed to allow you to read, write, and bulk insert several common file formats including csv, xls, xml, and fixed width. Components work with SqlBulkCopy class to allow for DTS like speed during bulk inserts. Readers allow for record by record looping and data manipulation.

Syntax Example

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 VB.Net Protected Sub uploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles uploadButton.Click ' declare CsvDataReader object which will act as a source for data for SqlBulkCopy Using 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 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) End Using ' dispose of SqlBulkCopy object End Using ' dispose of CsvDataReader object End Sub ' end uploadButton_Click 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

Reviews

Submit a review