Read and Insert lines of Text file into Database using 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.

For inserting the data into database you need to use the mod operator to check the odd and even lines.

Refer the below sample example.

Sample Text file

Database

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

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

Code

C#

using System.Configuration;
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");
        for (int i = 0; i < lines.Length; i++)
        {
            // Inserting to database.
            Insert(lines[i], i % 2 == 0 ? "Table1" : "Table2");
        }
    }

    private static void Insert(string description, string table)
    {
        string query = string.Format("INSERT INTO {0} (Description) VALUES (@Description)", table);
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                cmd.Parameters.AddWithValue("@Description", description);
                con.Open();
                cmd.ExecuteNonQuery();
                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")
        For i As Integer = 0 To lines.Length - 1
            ' Inserting to database.
            Insert(lines(i), If(i Mod 2 = 0, "Table1", "Table2"))
        Next
    End Sub

    Private Sub Insert(description As String, table As String)
        Dim query As String = String.Format("INSERT INTO {0} (Description) VALUES (@Description)", table)
        Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(conString)
            Using cmd As SqlCommand = New SqlCommand(query, con)
                cmd.Parameters.AddWithValue("@Description", description)
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    End Sub
End Module

Screenshot