private void showButton_Click(object sender, EventArgs e)
        {
            FileStream aStream = new FileStream(fileLocation, FileMode.OpenOrCreate);
            CsvFileReader aReader = new CsvFileReader(aStream);
            List<string> aPeronRecord = new List<string>();


            personListView.Items.Clear();



            while (aReader.ReadRow(aPeronRecord))
            {
                ListViewItem listViewItem = new ListViewItem(new string[]
                {
                    aPeronRecord[0],
                    aPeronRecord[1],
                    aPeronRecord[2],
                    aPeronRecord[3],
                    aPeronRecord[4]
                
                }
            );
                personListView.Items.Add(listViewItem);
            }
            aStream.Close();
        }

        private void saveButton_Click(object sender, EventArgs e)
        {
            FileStream aStreamForReading = new FileStream(fileLocation, FileMode.Open);
            CsvFileReader aReader = new CsvFileReader(aStreamForReading);
            List<string> aRecord = new List<string>();

            while (aReader.ReadRow(aRecord))
            {

                string regNo = aRecord[0];
                if (regNoTextBox.Text == regNo)
                {
                    MessageBox.Show(@"Reg no already exists");
                    aStreamForReading.Close();
                    return;
                }
            }
            aStreamForReading.Close();

            FileStream aStream = new FileStream(fileLocation, FileMode.Append);
            CsvFileWriter aWriter = new CsvFileWriter(aStream);
            List<string> aStudentRecord = new List<string>();
            aStudentRecord.Add(regNoTextBox.Text);
            aStudentRecord.Add(nameTextBox.Text);
            aWriter.WriteRow(aStudentRecord);
            aStream.Close();
        }

 public Dictionary<string, Player> readPlayers()
 {
     Dictionary<string, Player> listPlayers = new Dictionary<string, Player>();
     using (CsvFileReader reader = new CsvFileReader(file))
     {
         CsvRow row = new CsvRow();
         while (reader.ReadRow(row))
         {
             listPlayers.Add(row[Constants.FName], new Player(row[Constants.FName], boolFromString(row[Constants.FBoolTerran]),
                 boolFromString(row[Constants.FBoolZerg]), boolFromString(row[Constants.FBoolProtoss]), boolFromString(row[Constants.FBoolRandom])));
             Player temp = listPlayers[row[Constants.FName]];
             int.TryParse(row[Constants.FVictoryTerran], out temp.Victory[Constants.Terran]);
             int.TryParse(row[Constants.FVictoryZerg], out temp.Victory[Constants.Zerg]);
             int.TryParse(row[Constants.FVictoryProtoss], out temp.Victory[Constants.Protoss]);
             int.TryParse(row[Constants.FVictoryRandom], out temp.Victory[Constants.Random]);
             int.TryParse(row[Constants.FDefeatTerran], out temp.Defeat[Constants.Terran]);
             int.TryParse(row[Constants.FDefeatZerg], out temp.Defeat[Constants.Zerg]);
             int.TryParse(row[Constants.FDefeatProtoss], out temp.Defeat[Constants.Protoss]);
             int.TryParse(row[Constants.FDefeatRandom], out temp.Defeat[Constants.Random]);
             int.TryParse(row[Constants.FEloTerran], out temp.Elo[Constants.Terran]);
             int.TryParse(row[Constants.FEloZerg], out temp.Elo[Constants.Zerg]);
             int.TryParse(row[Constants.FEloProtoss], out temp.Elo[Constants.Protoss]);
             int.TryParse(row[Constants.FEloRandom], out temp.Elo[Constants.Random]);
         }
     }
     return listPlayers;
 }

        public static void CsvLoadTripRoutes(string filename, bool lngFirst)
        {
            // load trip routes
            Dictionary<string, LinkedList<Waypoint>> routes = new Dictionary<string, LinkedList<Waypoint>>();
            using (CsvFileReader reader = new CsvFileReader(filename))
            {
                CsvRow row = new CsvRow();
                while (reader.ReadRow(row, ','))
                {
                    string routeID = row[0];
                    double distance = 0;
                    double lat = Convert.ToDouble(lngFirst ? row[2] : row[1]);
                    double lng = Convert.ToDouble(lngFirst ? row[1] : row[2]);
                    if (routes.ContainsKey(routeID))
                        distance = routes[routeID].First.Value.GetDistance(new Location(lat, lng, "null"));
                    Waypoint waypoint = new Waypoint(lat, lng, TimeSpan.Parse(row[3]), distance, row[4].Replace("\"", ""));

                    // Scenario #1
                    if (!routes.ContainsKey(routeID))
                        routes[routeID] = new LinkedList<Waypoint>();
                    routes[routeID].AddLast(waypoint);

                }
            }
            foreach (LinkedList<Waypoint> w in routes.Values)
            {
                Route r = new Route(w.ToArray());
                string key = Route.GetKey(r.start, r.end);
                MapTools.routes.Add(key, r);
            }
        }

        public EventPointsReader(String filePath, Boolean skipHeader)
        {
            this.EventPoints = new List<EventPoint>();

            CsvFileReader reader = new CsvFileReader(filePath);

            Boolean headerSkipped = false;

            while (true)
            {
                CsvRow row = new CsvRow();
                if (reader.ReadRow(row))
                {
                    if ((skipHeader && headerSkipped) ||
                        !skipHeader)
                    {
                        EventPoint ep = new EventPoint();
                        ep.LongCode = row[0];
                        ep.ShortCode = row[1];
                        ep.DateTimeFrom = DateTime.ParseExact(row[2], "yyyy-MM-dd HH:mm:ss.fff", null);
                        ep.DateTimeTo = DateTime.ParseExact(row[3], "yyyy-MM-dd HH:mm:ss.fff", null);
                        ep.RangeMax = Convert.ToDouble(row[4]);
                        ep.RangeMin = Convert.ToDouble(row[5]);

                        this.EventPoints.Add(ep);
                    }
                    headerSkipped = true;
                }
                else
                {
                    break;
                }
            }
        }

        private void loadSNPSPToolStripMenuItem_Click(object sender, EventArgs e)
        {
            _openFileDialog.FileName = "SNPCC.csv";
            _openFileDialog.Filter = "csv files (*.csv)|*.csv";
            _openFileDialog.DefaultExt = "txt";
            _openFileDialog.FilterIndex = 2;
            _openFileDialog.AddExtension = true;
            if (_openFileDialog.ShowDialog() == DialogResult.OK)
            {
                string fileName = _openFileDialog.FileName;
                CsvFileReader csv = new CsvFileReader(fileName);
                char[] _separators = new char[] { '\n', '"', ',' };
                CsvRow row = new CsvRow();
                bool entering = true;
                while (entering == true)
                {
                    entering = csv.ReadRow(row);
                    staters.Add(new Stater(row[1], row[0]));
                    char[] bar = row[2].ToCharArray();
                    string bs = Convert.ToString(bar[0]) + Convert.ToString(bar[1]) + Convert.ToString(bar[2]) + Convert.ToString(bar[3]);
                    staters[staters.Count - 1].Barcode = Convert.ToInt32(bs);
                    staters[staters.Count - 1].City = row[3];
                    staters[staters.Count - 1].County = row[4];
                }

                csv.Close();
                staters.RemoveAt(staters.Count - 1);
                List<Stater> testing = staters;
            }
        }

        private void btnShow_Click(object sender, EventArgs e)
        {
            FileStream aStream = new FileStream(fileLocation, FileMode.Open);
            CsvFileReader aReader = new CsvFileReader(aStream);

            List<string> outList = new List<string>();

            lstViewOut.SelectedIndices[0];
        }

 public void TestCsvFileReadUsingSoundex()
 {
     var reader = new CsvFileReader();
     reader.RecordBuilder = new SinglePropertyRecordBuilder(@"Name");
     reader.ValueFactory = new SoundexValueFactory(5);
     var dataset = reader.ReadFile(new FileInfo(@"data/ReferenceDataset1.csv"), true, ',');
     Assert.AreEqual(1, dataset.Records.Count, @"Record count is wrong!");
     foreach (var record in dataset.Records)
     {
         Assert.AreEqual(1, record.Properties.Count, @"Property count is wrong!");
         Assert.AreEqual(@"A4253", record.Properties[@"Name"], @"Soundex code is wrong!");
     }
 }

        private void showButton_Click(object sender, EventArgs e)
        {
            FileStream aStream = new FileStream(fileLocation, FileMode.Open);
            CsvFileReader aReader = new CsvFileReader(aStream);
            List<string> aStudentRecord = new List<string>();

            studentListBox.Items.Clear();

            while (aReader.ReadRow(aStudentRecord))
            {
                string regNo = aStudentRecord[0];
                string name = aStudentRecord[1];
                studentListBox.Items.Add(regNo + " " + name);
            }
            aStream.Close();
        }

 private static IList<Record> LoadRecords(string fileContent)
 {
     const string filepath = "filepath";
     var mocks = new MockRepository(MockBehavior.Strict);
     Mock<IFileSystem> fileSystemMock = mocks.Create<IFileSystem>();
     fileSystemMock.Setup(it => it.OpenFileForRead(filepath))
         .Returns(new MemoryStream(Encoding.ASCII.GetBytes(fileContent)));
     IFileSystem fileSystem = fileSystemMock.Object;
     var reader = new CsvFileReader(fileSystem);
     reader.Open(filepath);
     IList<Record> records = new List<Record>();
     while (reader.MoveNext()) {
         records.Add(reader.CurrentRecord);
     }
     return records;
 }

        private void btnEntryShow_Click(object sender, EventArgs e)
        {
            FileStream aStream = new FileStream(fileLocation, FileMode.Open);
            CsvFileReader aReader = new CsvFileReader(aStream);

            List<string> record = new List<string>();

            while (aReader.ReadRow(record))
            {
                string bill = record[0];
                double aBill = Convert.ToDouble(bill);
                aBill += aBill;
                txtBoxTotalExpns.Text = aBill.ToString();
            }
            aStream.Close();
        }

        private void saveButton_Click(object sender, EventArgs e)
        {

            FileStream aStream = new FileStream(fileLocation, FileMode.OpenOrCreate);
           
            CsvFileReader aReader = new CsvFileReader(aStream);
            List<string> aPeronRecord = new List<string>();


            //personListView.Items.Clear();



            while (aReader.ReadRow(aPeronRecord))
            {
                if (aPeronRecord[2] == personalCnTextBox.Text)
                {
                    MessageBox.Show("conflict");

                    aStream.Close();
                    return;
                }
            }
            aStream.Close();



            FileStream aStream2 = new FileStream(fileLocation, FileMode.Append);
            CsvFileWriter aWriter = new CsvFileWriter(aStream2);
            List<string> aPersonRecod = new List<string>();

            aPersonRecod.Add(nameTextBox.Text);
            aPersonRecod.Add(emailTextBox.Text);
            aPersonRecod.Add(personalCnTextBox.Text);
            aPersonRecod.Add(homeCnTextBox.Text);
            aPersonRecod.Add(homeAddressTextBox.Text);

            aWriter.WriteRow(aPersonRecod);

            aStream2.Close();

            
                    


        }

        private static void InMemoryFileWithNoHeader()
        {
            Console.WriteLine("Test In Memory File With No Header");
            var csvDefinition = new CsvDefinition
                {
                    Header = "ClientId|FirstName|LastName|Occupation|City",
                    FieldSeparator = '|'
                };

            var csvWithNoHeader = new CsvFileReader<Client>(
                new StringReader("1|Pascal|Ganaye|CodeMonkey|Macon\r" +
                                 "2|Mick|Jagger|Singer|Dartford"), csvDefinition
                );
            foreach (var c in csvWithNoHeader)
                Console.WriteLine(string.Format("Client #{0}: {1} ({2} in {3})",
                                                c.ClientId,
                                                c.FirstName + (string.IsNullOrEmpty(c.MiddleName) ? " " : " " + c.MiddleName + " ") + c.LastName,
                                                c.Occupation,
                                                c.City));
        }

        public void TestWritingCsvRecords()
        {
            var reader = new CsvFileReader();
            var dataset = reader.ReadFile(new FileInfo(@"data/ReferenceDataset1.csv"), true, ',');
            Assert.AreEqual(1, dataset.Records.Count, @"Record count is wrong!");

            var record = Enumerable.First<IReferenceRecord>(dataset.Records);
            const string Key = @"Name";

            var indexWriter = new IndexFileWriter();
            var indexFile = new FileInfo(Path.GetTempFileName());
            indexWriter.WriteFile(indexFile, dataset.Records);

            var indexReader = new IndexFileReader();
            var writtenRecords = indexReader.ReadFile(indexFile);
            var writtenRecord = Enumerable.First<IReferenceRecord>(writtenRecords);
            Assert.AreEqual(record.Properties.Count, writtenRecord.Properties.Count, @"The number of properties do not match!");
            Assert.IsTrue(writtenRecord.Properties.ContainsKey(@"Name"), @"There is no property having a key equal to 'Name'!");
            Assert.AreEqual(record.Properties[Key], writtenRecord.Properties[Key], @"The property values do not match!");
        }

        public List<string> CreateInsertQueries(string fileName)
        {
            List<string> queries = new List<string>();

            CsvFileReader reader = new CsvFileReader(fileName);

            bool firstRowRead = false;
            while (true)
            {
                if (reader.EndOfStream)
                {
                    break;
                }

                CsvRow row = new CsvRow();
                reader.ReadRow(row);

                if (!firstRowRead)
                {
                    firstRowRead = true;
                    continue;
                }

                if (row[4].CompareTo("USD") == 0)
                {
                    continue;
                }

                string ticker = String.Format("USD{0} Curncy", row[4]);
                string desc = String.Format("{0} / 1 usd currency", row[0]);
                string queryDelete = String.Format(
                    "delete from product_list where ticker = '{0}'", ticker);
                string queryInsert = String.Format(
                    "insert into product_list values ('{0}', 'USD', '{1}', 0, 0, 'Index')",
                    ticker, desc);
                queries.Add(queryDelete);
                queries.Add(queryInsert);
            }

            return queries;
        }

        /// <summary> Reads the earthquakes from a csv file and adds them to a clusterer. The clusterer then groups
        /// them to clusters. </summary>
        /// <returns> Documentation in progress... </returns>
        public static IEnumerable<Location> ReadCSVFile()
        {
            var filePath = AppDomain.CurrentDomain.BaseDirectory + @"wikilocations25000.csv";
            using (var reader = new CsvFileReader(filePath, (char)0x09))
            {
                var row = new CsvRow();
                while (reader.ReadRow(row))
                {
                    if (row.Count < 3)
                        continue;

                    double x, y;
                    bool parsed = Double.TryParse(row[2], NumberStyles.Float, CultureInfo.InvariantCulture, out x);
                    x = parsed ? x : Double.NaN;
                    parsed = Double.TryParse(row[1], NumberStyles.Float, CultureInfo.InvariantCulture, out y);
                    y = parsed ? y : Double.NaN;
                    var post = new Location { Title = row[0], Coordinate = new Point(x, y) };
                    yield return post;
                }
            }
        }

        // GET api/product/5
        public IEnumerable<ProductItem> Get(int id)
        {
            List<ProductItem> productList = new List<ProductItem>();

            int rowid = 0;
            // Read sample data from CSV file
            using (CsvFileReader reader = new CsvFileReader(productfilePath))
            {
                CsvRow row = new CsvRow();

                while (reader.ReadRow(row))
                {
                    if (rowid > 0)
                    {
                        productList.Add(new ProductItem() { ProductId = Convert.ToInt32(row[0]), Name = row[1], ImageUrl = row[2], Price = Convert.ToDouble(row[3]), Calories = Convert.ToInt32(row[4]) });
                    }
                    rowid++;
                }
            }
            return productList.FindAll(product=>product.ProductId==id);
        }

        public CsvToStringQueries(String fileName, String tableName)
        {
            this._fileName = fileName;
            this._tableName = tableName;
            CsvFileReader reader = new CsvFileReader(fileName);
            CsvRow header = new CsvRow();
            reader.ReadRow(header);

            while (true)
            {
                if (reader.EndOfStream)
                {
                    break;
                }

                CsvRow body = new CsvRow();
                reader.ReadRow(body);
                String query = CreateQuery(body, tableName);

                this._queries.Add(query);
            }
        }

        public static IEnumerable<Record> DeserializeRecordsCsv(string path)
        {
            if (!File.Exists(path))
            {
                throw new FileNotFoundException(
                    "The file at the specified path could not be found.");
            }

            List<Record> retList = new List<Record>();

            using (CsvFileReader reader = new CsvFileReader(path))
            {
                CsvRow headers = new CsvRow();
                reader.ReadRow(headers);
                if (headers != ExpectedColumnHeaders.ToList())
                {
                    throw new Exception("CSV file headers are invalid.");
                }

                CsvRow row = new CsvRow();
                while (reader.ReadRow(row))
                {
                    retList.Add(new Record
                    {
                        Id = Convert.ToInt32(row[0]),
                        GivenName = row[1],
                        Surname = row[2],
                        StreetAddress = row[3],
                        City = row[4],
                        State = row[5],
                        ZipCode = row[6],
                        EmailAddress = row[7],
                        TelephoneNumber = row[8]
                    });
                }
            }

            return retList;
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            FileStream aFileStream = new FileStream(fileLocation, FileMode.Open);

            CsvFileReader aReader = new CsvFileReader(aFileStream);

            List<string> aRecordList = new List<string>();

            while (aReader.ReadRow(aRecordList))
            {
                string number = aRecordList[0];

                if(number == txtPhoneNumber.Text)
                {
                    MessageBox.Show("Wrong Entry");
                    aFileStream.Close();
                    return;
                }
            }

            aFileStream.Close();

            FileStream aStream = new FileStream(fileLocation, FileMode.Append);
            CsvFileWriter aWriter = new CsvFileWriter(aStream);

            List<string> addressRecord = new List<string>();

            addressRecord.Add(txtPhoneNumber.Text);
            addressRecord.Add(txtName.Text);
            addressRecord.Add(txtEmail.Text);
            addressRecord.Add(txtAddress.Text);
            addressRecord.Add(txtHomePhone.Text);

            aWriter.WriteRow(addressRecord);

            aStream.Close();
        }