none
Button to allow and upload of a CSV RRS feed

  • Question

  • Hi

    I want add an upload button that access the CSV File from the computer then uploads to the database, not sure how... am new to this code. its basically so it can populate the report online that I have created every time it is uploaded.

    thanks in advance

    Sabo

    Thursday, August 30, 2018 12:05 PM

All replies

  • To connection with database you need to know if you want to use entity framework or ADO.NET. You can see repository or DAO pattern which should be used in layer which works with database. You need to create entity which would be in relation with row in csv file.

    So you need to implement class which can insert data into database (see SqlConnection, SqlCommand). You need to parse CSV file which is text file (see TextReader, StreamReader) and convert line into entity (each column is property of your entity). How to choose file see OpenFileDialog if you work with windows form application.

    In base you need load file, parse each line into entity and each entity save by data layer. 

    Thursday, August 30, 2018 12:46 PM
  • I would suggest this free library CsvHelper installed via NuGet to read from csv file.

    My sample csv file.

    Kent,Choi,07/03/2007
    Jorge,Edwards,08/28/1975
    Greg,Higgins,08/29/1953
    Tricia,Tanner,12/31/1955
    Marlon,Campbell,05/27/1991
    Cara,Downs,09/09/1977
    Teresa,Tapia,12/25/1978
    Charlotte,Blevins,03/06/1994
    Angelica,Ayala,02/19/1966
    Alfred,Petty,06/16/1985
    Lea,Wise,01/13/1974
    Curtis,Alexander,04/12/1983
    Kelli,Stuart,01/04/1996
    Cornelius,Manning,01/23/1982
    Dawn,Pineda,11/28/1982
    Noah,Dominguez,02/22/1979
    Ramiro,Benjamin,03/13/1996
    Rebecca,Clark,06/26/2000
    Roland,Moon,02/29/1972
    Chastity,Copeland,08/13/1998
    Melissa,Herrera,09/25/1989
    Betsy,Gibbs,08/25/1976
    Eduardo,O'Connor,01/22/1958
    Toby,Perez,06/12/1958
    Ruby,Odom,07/09/1973
    Timmy,Clark,02/18/1962
    Adriana,Conner,11/03/1988

    A class to represent the data which is used for the CsvHelper CsvReader.GetRecords method.

    public class Person
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime Birthday { get; set; }
    }

    Here I read the csv file into a collection of Person. Check to ensure there is data then setup a connection and command object with parameters to insert the data. This is very basic.

    • CsvReader has many options.
    • I don't account for null values
    • No full exception handling, only the basics.
    • All code is in one form, this should be broken out into class files, do the least amount in the form.
    using CsvHelper;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Linq;
    using System.Windows.Forms;
    
    namespace CsvProject
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
    
                var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Text1.csv");
    
                using (TextReader fileReader = File.OpenText(fileName))
                {
                    var csv = new CsvReader(fileReader);
                    csv.Configuration.HasHeaderRecord = false;
                    var records = csv.GetRecords<Person>();
                    if (records.Count() >0)
                    {
                        //
                        // to configure connection string for SQL-Server
                        // https://www.connectionstrings.com/sql-server/
                        //
                        using (var cn = new SqlConnection() { ConnectionString = "TODO" })
                        {
                            using (var cmd = new SqlCommand() { Connection = cn })
                            {
                                cmd.CommandText = "INSERT INTO dbo.People (FirstName, LastName, BirthDay) " + 
                                    "VALUES (@FirstName, @LastName, @BirthDay);";
    
                                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@FirstName", SqlDbType = SqlDbType.NVarChar });
                                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@LastName", SqlDbType = SqlDbType.NVarChar });
                                cmd.Parameters.Add(new SqlParameter() { ParameterName = "@BirthDay", SqlDbType = SqlDbType.DateTime });
    
                                cn.Open();
    
                                foreach (Person p in records)
                                {
                                    try
                                    {
                                        cmd.Parameters["@FirstName"].Value = p.FirstName;
                                        cmd.Parameters["@LastName"].Value = p.LastName;
                                        cmd.Parameters["@BirthDay"].Value = p.Birthday;
    
                                        cmd.ExecuteNonQuery();
                                    }
                                    catch (Exception)
                                    {
                                        // handle recovery
                                    }
                                }
                            }
                        }
    
                    }
                }
            }
        }
        public class Person
        {
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public DateTime Birthday { get; set; }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, August 30, 2018 1:51 PM
    Moderator
  • Hi

    I want add an upload button that access the CSV File from the computer then uploads to the database, not sure how... am new to this code. its basically so it can populate the report online that I have created every time it is uploaded.

    thanks in advance

    Sabo

    https://www.codeproject.com/Articles/25133/LINQ-to-CSV-library

    Thursday, August 30, 2018 10:57 PM