C# - How to Load data to SQL Server Tables from flat files according to file names by using C Sharp

Scenario : Download Script

You are working as C# developer for insurance company. You get multiple files on daily basis in your Source folder. The files comes with date and some of them comes with date and time. You need to extract Name from file and then load to table accordingly. Each file Name does match with table Name.

How to load flat files to SQL Server Tables according to the name of file by using C sharp

Here is script that I used to create tables for above sample files.

Create table dbo.Customer
(Id int, 
Name varchar(100)
)
GO

Create Table dbo.TechBrothersInfo
(
Id int, 
CourseTitle VARCHAR(100)
)

The program should be able to load any number of files as long as the tables exist and file name contains table name as part of full name.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data.SqlClient;

namespace TechBrothersIT.com_CSharp_Tutorial
{
    class Program
    {
        static void Main(string[] args)
        {

            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            string LogFolder = @"C:\Log\";
            try
            {

                //Declare Variables and provide values
                string SourceFolderPath = @"C:\Source\";
                string FileExtension = ".txt";
                string FileDelimiter = ",";
                string ArchiveFolder = @"C:\Archive\";
                string SchemaName = "dbo";


                    //Create Connection to SQL Server in which you like to load files
                    SqlConnection SQLConnection = new SqlConnection();
                    SQLConnection.ConnectionString = "Data Source = (local); Initial Catalog =TechBrothersIT; "
                       + "Integrated Security=true;";

                    //Reading file names one by one
                    string SourceDirectory = SourceFolderPath;
                    string[] fileEntries = Directory.GetFiles(SourceDirectory, "*" + FileExtension);
                    foreach (string fileName in fileEntries)
                    {

                        string TableName = "";
                        //Remove All Numbers and other characters and leave alphabets for name
                        System.Text.RegularExpressions.Regex rgx = new System.Text.RegularExpressions.Regex("[^a-zA-Z]");
                        TableName = rgx.Replace(fileName.Replace(SourceDirectory, "").Replace(FileExtension, ""), "");
                  
                        //Writing Data of File Into Table
                        int counter = 0;
                        string line;
                        string ColumnList = "";
 
                        System.IO.StreamReader SourceFile =
                        new System.IO.StreamReader(fileName);
                        SQLConnection.Open();
                        while ((line = SourceFile.ReadLine()) != null)
                        {
                            if (counter == 0)
                            {
                                ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";

                            }
                            else
                            {
                                string query = "Insert into " + SchemaName + "." + TableName + " (" + ColumnList + ") ";
                                query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')";

                                SqlCommand SQLCmd = new SqlCommand(query, SQLConnection);
                                SQLCmd.ExecuteNonQuery();
                            }

                            counter++;
                        }
                        SourceFile.Close();
                        SQLConnection.Close();
                        
                    //move the file to archive folder after adding datetime to it
                        File.Move(fileName, ArchiveFolder + "\\" +
                            (fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "")
                            + "_" + datetime + FileExtension);
                      
                    }
                }
            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(LogFolder
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                   
                }

            }

        }
    }
}

Save the program and execute.After loading the files , the files should be moved to archive folder. I tested and it load the files data successfully to my tables as shown below.

How to load csv/ txt files to SQL Server Tables according to name of files by using C#