Read Flat File and insert each line to database

The task is to read a flat file which is pipe delimited (first row has column names). Insert all data fields into the database (MS SQL; using sqlclient). The code is in c#. I have here the code to read line and split fields. Please help me include the sqlparemeters in the foreach line loop so each line can be inserted into the table.
I haven't been able to assign values of the parameters inside the loop while reading each line. Help me out. Thanks
string strLine;
string[] strArray;
char[] charArray = new char[] { '|' };
FileStream aFile = new FileStream(pathToFile, FileMode.Open);
StreamReader sr = new StreamReader(aFile);
strLine = sr.ReadLine();
string sqlcmd=@"Insert into table value1, value2, value3, value4 values (@value1,@value2,@value3,@value4)"; 
strArray = strLine.Split(charArray);
//read column names
for (int x = 0; x <= strArray.GetUpperBound(0); x++)
  {
     strArray[x].Trim();
  }
strLine = sr.ReadLine();
while (strLine != null)
  {
     strArray = strLine.Split(charArray);
     for (int i = 0; i <= strArray.GetUpperBound(0); i++)
     {
       strArray[i].Trim();
       // here add code to add SqlParameters 
       // cmd.Parameters.Add(new SqlParameter("@value1", null));
       //cmd.Parameters["value1].Value = strArray[1];
     }
      strLine = sr.ReadLine();
      sqlcmd.ExecuteNonQuery();
   }

Open in new window

darthvader747Asked:
Who is Participating?
 
Miguel OzSoftware EngineerCommented:
Try: (Replace lines 17-26 with:)
     strArray = strLine.Split(charArray);
     for (int i = 0; i <= strArray.GetUpperBound(0); i++)
     {
       strArray[i].Trim();
       // here add code to add SqlParameters
       SqlParameter param  = new SqlParameter();
       param.ParameterName = "@Value" + i.ToString();
       param.Value         = strArray[i];
       cmd.Parameters.Add(param);
     }
      sqlcmd.ExecuteNonQuery();
      strLine = sr.ReadLine();

check:
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx
http://msdn.microsoft.com/en-us/library/yy6y35y8(VS.80).aspx

Note: Be aware that you database table needs to have value1, ... , valuen fields defined else code will not work. If you require further help please provide sample of flat file as well as table definition.
0
 
darthvader747Author Commented:
This method works. Just implemented and tested it. Thank You so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.