Hi Nisha,
What you can do read the file and store in a datatable.
The following function read all the lines to a collection of string array.
private IEnumerable<string[]> GetAllLines()
{
string str;
using (StreamReader rd=new StreamReader(<Flat File Path>))
{
while ((str = rd.ReadLine()) != null)
{
yield return str.Split('|');
}
}
}
Now lets create a datatable out of these collection,
Here I assume that first line of flat file contains the Column Name
var values = GetAllLines();
DataTable dt = new DataTable();
foreach (var item in values.FirstOrDefault())
{
if (!dt.Columns.Contains(item))
dt.Columns.Add(item);
}
var allvalues=values.Skip(1).ToList();
for (int i = 0; i < allvalues.Count; i++)
{
dt.Rows.Add(allvalues[i].Take(dt.Columns.Count).ToArray());
}
Then you can use SQLBulkCopy class to save the datatable to an actual table in database.
SqlConnection dbconnection = new SqlConnection(<Connection String>);
SqlBulkCopy sqlblkcpy=new SqlBulkCopy(dbconnection);
try
{
dbconnection.Open();
sqlblkcpy.DestinationTableName = "<Your destination table name in database>";
// I assume column name in flat file is same as table column names in database
// else use explicit column mapping.
foreach (var item in dt.Columns)
{
sqlblkcpy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(item, item));
}
//this statement writes the datatable data in to database in destination table.
if(dt.Rows.Count>0)
sqlblkcpy.WriteToServer(dt);
}
Thanks,
Debata
Nisha03, if this helps please login to Mark As Answer. | Alert Moderator