Click here to Skip to main content
Click here to Skip to main content

Tagged as

Reading .CSV File And Inserting Data to SQL Table using SqlBulkCopy

, 11 Feb 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Introduction...

Introduction

 
This trick is a complete solution to the programmers who want a solution to read CSV file using Open file dialog, store the raw data in a Data Grid View control and lastly insert the raw data in a SQL table using SQLbulkcopy.
 

Using the Code

First u need to create a Database with a table named Company for example. The table creation SQL script is provided below.
 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Company]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Company]
GO
 
CREATE TABLE [dbo].[Company] (
    [CompanyName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Volume] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO
 

The sql table column names have to be similar to the column names that you have in the .CSV file .

 
You need to create a C#.Net Project and 
place the following controls on a form:
1.Three Textbox
2.Three Buttons 
3.One DataGridView
4.Lastly one OpenFileDialogControl
And paste the code and modify the above controls name as below
I am not going to indent the following code. Poke tongue | ;-P
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient; 
//Include this name space inorder to use SqlBulkCopy
namespace FileReader
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void btnSaveFile_Click(object sender, EventArgs e)
        {
            try
            {
                if (string.IsNullOrEmpty(txtServerConfiguration.Text.Trim())) //Check if server exists
                {
                    MessageBox.Show("Please supply Server Configuration");
                    return;
                }
                if (string.IsNullOrEmpty(txtTableName.Text.Trim())) 
                    //Check if Table name was provided 
                {
                    MessageBox.Show("Please supply Table Name");
                    return;
                }
 
                SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(txtServerConfiguration.Text.Trim()); 
                //Define the Server Configuration
                sqlbulkCopy.DestinationTableName = txtTableName.Text.Trim(); 
                //You need to define the target table name where the data will be copied
                //Since the requirement is not to read data directly from the file
                //I have included a grid view, where the raw data will be shown to the user.
                //So that the user can change the data if he wants
                for (int i = 0; i < dataGridView1.ColumnCount; i++) //reading the column names 
                {
                    sqlbulkCopy.ColumnMappings.Add(dataGridView1.Columns[i].Name, 
                        dataGridView1.Columns[i].Name);
                    //Column mapping , supplying source column and destination column
                }
                DataTable dt = new DataTable();
                dt = (DataTable)dataGridView1.DataSource;
                // Storing the data in a data table, though you can do it by 
                //directly passing Grid View Data Source to the following function
                sqlbulkCopy.WriteToServer(dt);
                sqlbulkCopy.Close();//Release the resources
                MessageBox.Show("Saved Successfully");
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);//Catch any exception if occurs
            }
        }
 
        private void btnLoadFile_Click(object sender, EventArgs e)
        {
            try
            {
                //I have used a open file Dialog to let the 
                //program know where the file is located
                DialogResult dResult = new DialogResult(); 
                //Dialog result sets the openfile dialog result
                dResult = openFileDialog1.ShowDialog();
                if (dResult == DialogResult.OK)
                    //If dialog result is ok then show the file 
                //name and path in the Text box
                {                              
                    txtFilePath.Text = openFileDialog1.FileName;
                }
                else
                {
                    return; //If Dialog result is other than ok then do nothing
                }
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);//Catch any exception if occurs
            }
        }
 
        private void btnLoadData_Click(object sender, EventArgs e)
        {
            try
            {
 
                if (string.IsNullOrEmpty(txtFilePath.Text.Trim())) //Check if File was supplied
                {
                    MessageBox.Show("Please supply file name");
                    return;
                }
                System.IO.StreamReader rdr = new System.IO.StreamReader(txtFilePath.Text.Trim()); 
                //Stream reader reads a file. File path 
                //and name is supplied from where to read the file. 
                string inputLine = "";
                DataTable dt = new DataTable(); 
                //A data table is similar to a Database table. 
                //Define the columns.
                dt.Columns.Add("CompanyName");
                dt.Columns.Add("Volume");
                DataRow row; //Declare a row, which will be added to the above data table
                while ((inputLine = rdr.ReadLine()) != null) 
                    //Read while the line is not null
                {
                    string[] arr;
                    arr = inputLine.Split(','); 
                    //splitting the line which was read by the stream reader object
                    row = dt.NewRow();
                    row["Companyname"] = arr[0];
                    row["Volume"] = arr[1];
                    dt.Rows.Add(row);
                }
                dt.Rows.RemoveAt(0); //Remove the first column since Its 
                //the column name not necessary to insert in the database table
                dataGridView1.DataSource = dt; //setting the data source to the grid view
                rdr.Close(); //release the stream reader
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}
Take a look on the code.
You need to do the follwing:
  1. Define a server configuration.
  2. Set a Table Name
That's it. Happy coding Big Grin | :-D

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Angsuman Chakraborty
Software Developer NA
Bangladesh Bangladesh
Graduated with IT in 2006.
 
Currently working as a software engineer.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141220.1 | Last Updated 12 Feb 2010
Article Copyright 2010 by Angsuman Chakraborty
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid