Reading Data from Flat File Database and Writing into SQL Database

Download Source Code

Introduction

This is a simple programs written in C# which reads data of records from a Plain Text File (Or Flat File Database) separated by custom delimiters, In case of my example here the delimiter is a cylinder ( | ), And write these records to a SQL Database accordingly.

Background

Sometimes we need to store records while we have to use very limited memory. In that case a plain text file is used to store records and information or in other words called Flat File Database. Its less size feature is very useful when we are transferring this database over network via streams. The record is stored in text files as One Record Per Line and is separated by a specific delimiter to differentiate between columns. Let’s analyze it. .

Using the Code

Before we go to the code there is one necessary step we have to perform which is

-We need to create a Text file in which we will write a few lines like below1

 

First line is my text file name from which I want to read the data which is “a384391.txt”.

In second line we described the delimiter of this text file which is cylinder “( | )” in my case.

And in third line ColNameHeader=True means that first line of our text file contains Column names so now DataAdapter place the Column Names in the Column Names section in the Data table.

There are few important things about this file

1-File Name must be “Schema”

2-File Extension must be “.ini”

3-This File should at the same location where our text file is residing.

As far as this programs is concerned I already placed the Text File as well as its Schema.ini file in the same folder as my project folder as “projectfolder/bin/Release/a384391/” so that it’s easy for the program to pick this location up.

Alright now let’s move through the code

 

            //File Name from which we want the read the Record
            string fileName = "a384391.txt";

            //Programatically Getting the File Location
            string directory = projectDirectory + "\\a384391";   //
            string folderName = Path.GetFileName(directory);

            //here we have a full path of that file now
            string fullPath = Path.GetFullPath(folderName);

            //Select Query to Get the Data
            string query = @"SELECT *FROM [" + fileName + "]";

            //Connection String to read the Data Via OLEDB in exteneded properties it is defined as a Text file
            string c_String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullPath + ";" + "Extended Properties=text;";

            // Created a dataAdapterObject
            OleDbDataAdapter comm = new OleDbDataAdapter(query, c_String);

            // Creaated a DataTable
            DataTable dt = new DataTable();

            //Tranferring data from Text File to DataTable via DataAdapter
            comm.Fill(dt);

 

This is a simple piece of code which fetch the records from Text File separate them on the base of Delimiter and the place them in the Datatable object which is “dt” using Microsoft Jet OLEDB Adapter.

Now we have all the records of the Text File into datatable each at its respective column and row. It’s time to write these records to Microsoft SQL Server. We already have a Table there  with all the column names. In Database “TextFileData” the Table is “dbo.a384391”

3

Now we have to move write the data which is present in our datatable into the SQL Server database. So let’s have a look at that code

First we need to make a SQL connection to connect the SQL Server

 

static public void makeSqlConnection()
   {
       string sSqlConnectionString = "SERVER=DOTEXE\\SQLEXPRESS;Initial Catalog=TextFileData; " +
                                      "CONNECTION RESET=FALSE;Integrated Security=SSPI";
       SqlConn = new SqlConnection(sSqlConnectionString);

   }

 

We need a Insert Query to write into Database

 

string insertQueryText = "INSERT INTO a384391 VALUES ( @merchantID @organization, @contactname, @email,"+
"@www, @dsalec, @dleadc, @dhitc, @catsub, @catname,@startdate, @dcookie, @7dayepc, @30dayepc, @7dayreversal,"+
"@30dayreversal, @powerrank, @compliance, @csalec, @cleadc, @chitc, @ccookie, @agroup, @gsalec, @gleadc, @ghitc, "+
 "@applydate, @holidays )";

 

and data is written into the SQL Database until all the rows successfully transferred to the SQL database.

 

foreach (DataRow dr in dt.Rows)
            {
                SqlCommand cmd = new SqlCommand(insertQueryText, SqlConn);
                cmd.Parameters.Add("@merchantID", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][0];
                cmd.Parameters.Add("@organization", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][1];
                cmd.Parameters.Add("@contactname", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][2];
                cmd.Parameters.Add("@email", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][3];
                cmd.Parameters.Add("@www", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][4];
                cmd.Parameters.Add("@dsalec", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][5];
                cmd.Parameters.Add("@dleadc", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][6];
                cmd.Parameters.Add("@dhitc", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][7];
                cmd.Parameters.Add("@catsub", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][8];
                cmd.Parameters.Add("@catname", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][9];
                cmd.Parameters.Add("@startdate", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][10];
                cmd.Parameters.Add("@dcookie", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][11];
                cmd.Parameters.Add("@7dayepc", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][12];
                cmd.Parameters.Add("@30dayepc", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][13];
                cmd.Parameters.Add("@7dayreversal", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][14];
                cmd.Parameters.Add("@30dayreversal", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][15];
                cmd.Parameters.Add("@powerrank", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][16];
                cmd.Parameters.Add("@compliance", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][17];
                cmd.Parameters.Add("@csalec", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][18];
                cmd.Parameters.Add("@cleadc", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][19];
                cmd.Parameters.Add("@chitc", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][20];
                cmd.Parameters.Add("@ccookie", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][21];
                cmd.Parameters.Add("@agroup", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][22];
                cmd.Parameters.Add("@gsalec", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][23];
                cmd.Parameters.Add("@gleadc", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][24];
                cmd.Parameters.Add("@ghitc", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][25];
                cmd.Parameters.Add("@applydate", SqlDbType.VarChar, 255).Value = dt.Rows[rowIndex][26];
                cmd.Parameters.Add("@holidays", SqlDbType.VarChar, 1000).Value = dt.Rows[rowIndex][27];
                rowIndex++;
                cmd.ExecuteNonQuery(); 
}

 

 

each time the query executed  first row of each column is written into Database.

That’s it. The second section is completed we copied all the data from datatable to SQL Table.

There is another approach available to write data into database is that to make a procedure into SQL Managment Studio and then simply call that  through your code in c#.