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

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. ;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 :-D