Import Text file into Database using SqlBulkCopy in C# and VB.Net

Hi Amit,

In order to read the text file line by line you need to make use of ReadAllLines method of File class which belongs to the System.IO namespace.

Each line of Text file are read and inserted into DataTable. Finally the the DataTable is inserted in SQL Server database table using the SqlBulkCopy class.

Refer the below sample example.

Sample Text file

Database

CREATE TABLE Table1
(
    Id INT IDENTITY PRIMARY KEY,
    Description VARCHAR(500)
)

Code

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

class Program
{
    static void Main(string[] args)
    {
        // Reading All Line from Text File.
        string[] lines = File.ReadAllLines("D:\\Files\\Sample.txt");
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[] {
            new DataColumn("Id", typeof(int)),
            new DataColumn("Description", typeof(string))
        });
        // Reading line by line and insert to DataTable.
        for (int i = 0; i < lines.Length; i++)
        {
            dt.Rows.Add(i, lines[i]);
        }

        // Insert into Database.
        if (dt.Rows.Count > 0)
        {
            string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(consString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    //Set the database table name.
                    sqlBulkCopy.DestinationTableName = "dbo.Table1";

                    //[OPTIONAL]: Map the DataTable columns with that of the database table.
                    sqlBulkCopy.ColumnMappings.Add("Id", "Id");
                    sqlBulkCopy.ColumnMappings.Add("Description", "Description");
                    con.Open();
                    sqlBulkCopy.WriteToServer(dt);
                    con.Close();
                }
            }
        }
    }
}

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient
Imports System.IO

Module Module1
    Sub Main()
        ' Reading All Line from Text File.
        Dim lines As String() = File.ReadAllLines("D:\Files\Sample.txt")
        Dim dt As DataTable = New DataTable()
        dt.Columns.AddRange(New DataColumn() {
                            New DataColumn("Id", GetType(Integer)),
                            New DataColumn("Description", GetType(String))})
        ' Reading line by line and insert to DataTable.
        For i As Integer = 0 To lines.Length - 1 Step 1
            dt.Rows.Add(i, lines(i))
        Next

        ' Insert into Database.
        If dt.Rows.Count > 0 Then
            Dim consString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using con As New SqlConnection(consString)
                Using sqlBulkCopy As New SqlBulkCopy(con)
                    'Set the database table name.
                    sqlBulkCopy.DestinationTableName = "dbo.Table1"

                    '[OPTIONAL]: Map the DataTable columns with that of the database table.
                    sqlBulkCopy.ColumnMappings.Add("Id", "Id")
                    sqlBulkCopy.ColumnMappings.Add("Description", "Description")
                    con.Open()
                    sqlBulkCopy.WriteToServer(dt)
                    con.Close()
                End Using
            End Using
        End If
    End Sub
End Module

Screenshot