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

• Download source - 384.18 KB
• Download setup - 339.31 KB

Introduction

It’s a common scenario for programmers/users to have situations like they have to insert data in a sqlserver database table from a different data source. It's easy to do so using sqlserver’s integrated data import facility. But what if you’re using sqlexpress which is a light version, which doesn’t have support for data import.

And also data import feature sometimes may not fulfill business requirements. You might not want the user to access the database directly.

What if you have a file which contains 10000000 rows, and you have some validation after reading each single line, and need to parse using certain programming logic. Direct data import won’t work at that time.

It's better to implement your business logic in your code rather than in database using stored procedures, views and functions. It’s not a better idea to write a stored procedure which has 3000 lines. You will definitely suffer from this when the time for debugging this kind of stored procedure will come. Insert, Delete, Update, and Select statement best fit in the database without any validation.

Solution to this Problem

There are many ways to solve this kind of problem. But the most common approach is:

1. You can use SQL Command
2. You can use SQLBulkCopy (faster than sqlcommand, but doesn’t fire database triggers)

In this article, we will discuss how to use sqlbulkcopy.

The step by step solution is given below:

1. First we will create a sqltable using the following lines of code. You need to execute this in the database query window:
   SQL

   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

2. Second we will create a project. And place the following controls in the default Form1 Form, and place the codes at the same time.
   1. 1 Text Box to specify the server configuration.
   2. 1 Text box to specify the file path of the file that will be read.
   3. 1 Text box to specify where the data will be stored.
   4. 1 Open file dialog to open the specified file.
   5. One button, name it btnLoadFile to load the file path
      C#

      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
          }
      }

   6. One DataGridView control to show the data
   7. One button, name it btnLoadData to load the data from the file to the gridview
      C#

      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 are 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 gridview
          rdr.Close(); //release the stream reader
          }
          catch (Exception)
          {
              throw;
          }
      }

   8. One button, name it btnSaveFile to save the raw data to the database.
      C#

      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 gridview, 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
          }
      }

Points of Interest

I could not find a complete solution like this anywhere. I learned how to read file and write it to a database table using SQL bulk copy. Since mistakes happens in life, any comments/information/violent reactions are always welcome.

History

• 11^th February, 2010: Initial post

I will keep this article updated from time to time.