Need help on multiple text files in a path to insert in database

Question

I have two text files in my path, any idea why only one text file inserts into my database? enter image description here

After running the application it stops and return an 'System.NullReferenceException'

Additional information: Object reference not set to an instance of an object.

This is the code.

try
{
    var files = from file in Directory.EnumerateFiles(@"C:\Users\K\Desktop\New folder", "*.txt", SearchOption.AllDirectories)
                from line in File.ReadLines(file)
                select new
                {
                    File = file,
                    Line = line
                };

    foreach (var f in files)
    {
        Console.WriteLine("{0}\t{1}", f.File, f.Line);
        using (StreamReader sr = new StreamReader(f.File))
        {
            foreach (string line in File.ReadAllLines(f.File).SkipWhile(x => !x.Contains("[Start]")))
          {

              string readLine;
              do
              {
                  readLine = sr.ReadLine();
                  string[] readLineSplit = readLine.Split('|');

                  if(readLineSplit.Length > 1)
                  {
                      using (MySqlConnection con = new MySqlConnection(@"server=localhost;database=test;uid=root;pwd=pw;"))
                      {
                          con.Open();
                          MySqlCommand cmd = new MySqlCommand("INSERT INTO Products(Product_Name, Product_Price, QTY) VALUES (@Product_Name, @Product_Price, @QTY)", con);
                          cmd.Parameters.AddWithValue("@Product_Name", readLineSplit[0].ToString());
                          cmd.Parameters.AddWithValue("@Product_Price", readLineSplit[1].ToString());
                          cmd.Parameters.AddWithValue("@QTY", readLineSplit[2]);
                          cmd.ExecuteNonQuery();
                      }
                  }


                          } while (!sr.EndOfStream);
                      }

On which line exactly? And [What is a `NullReferenceException` and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Soner Gönül, Apr 02 '15 at 07:12

Thomas Krojer · Accepted Answer · 2015-04-02T08:39:35.540

0

should be something like this: not testest ´cause no mysql here and to less time:

var files = Directory.EnumerateFiles(@"C:\Users\K\Desktop\New folder", "*.txt", SearchOption.AllDirectories);

using (MySqlConnection con = new MySqlConnection(@"server=localhost;database=test;uid=root;pwd=pw;"))
{
    con.Open(); // new Position of con.Open()
    foreach (var f in files)
    {
        Console.WriteLine(f);
        string[] Lines = File.ReadAllLines(f);
        bool processRecord = false;

        foreach (string line in Lines)
        {
            if (!processRecord)
            {
                if (Lines.Contains("[Start]"))
                {
                    processRecord = true;
                    continue;
                }
            }
            if (Lines.Contains("[End]"))
            {
                processRecord = false;
                continue;
            }
            if (processRecord)
            {
                string[] readLineSplit = line.Split('|');

                if (readLineSplit.Length > 1)
                {
                    MySqlCommand cmd = new MySqlCommand("INSERT INTO Products(Product_Name, Product_Price, QTY) VALUES (@Product_Name, @Product_Price, @QTY)", con);
                    cmd.Parameters.AddWithValue("@Product_Name", readLineSplit[0]);
                    cmd.Parameters.AddWithValue("@Product_Price", readLineSplit[1]);
                    cmd.Parameters.AddWithValue("@QTY", readLineSplit[2]);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

EDIT: Sorry, made a mistake ... now complete.

EDIT: NOW complete!

EDIT: [End] condition added.

EDIT: Moved con.Open() to be called just once.

EDIT: Changed small typo and removed the faulty File Enumeration

edited Apr 02 '15 at 08:39

answered Apr 02 '15 at 07:31

Thomas Krojer

1,018
7
9

thanks this works! but it inserts the two text files multiple times into the database, i have a [end] tag also how can i stop inserting if it reads that line? – Kevin Rodriguez Apr 02 '15 at 07:42
please explain: Show me the datafile Content - or maybe my new check for process record is corret, the first one was erroneous. – Thomas Krojer Apr 02 '15 at 07:43
this is the text file text here,text here, text here. text here, text here, text here. text here, text here, text here. text here, text here, text here. text here, text here, text here. [Start] "Donuts"|"30"|"54" "Egg"|"20"|"25" "Apple"|"15"|"18" "Orange"|"20"|"15" "Pineapple"|"30"|"29" [End] – Kevin Rodriguez Apr 02 '15 at 07:47
See the code now - I made a small change **:-)** - moved con.Open() to the place where it belongs - and please don´t forget to vote! – Thomas Krojer Apr 02 '15 at 07:56
nice! :) but look it still inserting multiple records. this is the image: http://i.imgur.com/lXm526Y.jpg?1 – Kevin Rodriguez Apr 02 '15 at 08:02
First: Are you sure you copied my code correct? There are two nested loops: The outer loop over the files, the inner loop over all lines to process. If date are doubled, there is maybe a problem with your date files.Just to be sure: you delete the old inserted data? please provide the textfiles (in your post, as code so we can read it). – Thomas Krojer Apr 02 '15 at 08:08
yes, i even drop then recreated the table then run the program. – Kevin Rodriguez Apr 02 '15 at 08:10
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/74322/discussion-between-thomas-krojer-and-kevin-rodriguez). – Thomas Krojer Apr 02 '15 at 08:10

Need help on multiple text files in a path to insert in database

1 Answers1