jdis_import/CSVObject.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace jdis_import
{
    public class CSVObject
    {
        public string CustomerNumber { get; set; }
        public string CustomerName { get; set; }
        public string Address { get; set; }
        public string Address2 { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string ZipCode { get; set; }
        public string EquipmentNumber { get; set; }
        public string SerialNumber { get; set; }
        public string Model { get; set; }
        public string MeterReading { get; set; }
        public string Year { get; set; }
        public string FamilyCode { get; set; }
        public string FamilyDescription { get; set; }
        public string Make { get; set; }
        public string LastServiceDate { get; set; }
        public string DBSWorkOrderNumber { get; set; }
        public string DateActualStart { get; set; }
        public string CodeAndDescriptionOriginal { get; set; }
        public string CodeAndDescription { get; set; }
        public string Store { get; set; }
        public string StoreDescription { get; set; }
        public string ContractTypeCode { get; set; }
        public string JobSiteContactName { get; set; }
        public string JobSiteContactPhone { get; set; }
        public string PriorityCode { get; set; }
        public string SpecialInstructions { get; set; }
        public string PartNumber { get; set; }
        public string PartNumberDescription { get; set; }
        public string ReferenceDocNumber { get; set; }
        public string Quantity { get; set; }
        public string SegmentNumber { get; set; }
        public string ServiceTech { get; set; }
        public string TechName { get; set; }
        public string InspectionType { get; set; }
        public string ScheduledStartDate { get; set; }

        public string SQLStatement { get; set; }

        public static int MachineExists(string serialnumber, string connStr)
        {
            int? id = 0;
            string sql = string.Format("Select ID from equipment where serialnumber = '{0}'", serialnumber);
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                id = Convert.ToInt32(cmd.ExecuteScalar());
                if (id > 0)
                {
                    cmd.CommandText = string.Format("Select ModelID from Equipment where ID = {0}", id);
                    id = Convert.ToInt32(cmd.ExecuteScalar());
                }
            }
            return Convert.ToInt32(id);
        }

        public void Validate()
        {
            if (this.CustomerNumber.Length > 50) throw new ApplicationException(string.Format("Customer Number too long. Text: {0}", this.CustomerNumber));
            if (this.CustomerName.Length > 50) throw new ApplicationException("Customer Name too long.");
            if (this.Address.Length > 50) throw new ApplicationException("Address too long.");
            if (this.Address2.Length > 50) throw new ApplicationException("Address2 too long.");
            if (this.City.Length > 50) throw new ApplicationException("City too long.");
            if (this.State.Length > 3) throw new ApplicationException("State too long.");
            if (this.ZipCode.Length > 50) throw new ApplicationException("ZipCode too long.");
            if (this.EquipmentNumber.Length > 50) throw new ApplicationException("Equipment Number too long.");
            if (this.SerialNumber.Length > 50) throw new ApplicationException("Serial Number too long.");

            if (this.Model.Length > 50) throw new ApplicationException("Model too long.");
            if (this.MeterReading.Length > 50) throw new ApplicationException("Meter Reading too long.");
            if (this.Year.Length > 50) throw new ApplicationException("Year too long.");
            if (this.FamilyCode.Length > 50) throw new ApplicationException("Family Code too long.");
            if (this.FamilyDescription.Length > 50) throw new ApplicationException("Family Description too long.");
            if (this.Make.Length > 50) throw new ApplicationException("Make too long.");
            if (this.LastServiceDate.Length > 50) throw new ApplicationException("Last Service Date too long.");
            if (this.DBSWorkOrderNumber.Length > 50) throw new ApplicationException("DBS Work Order Number too long.");
            if (this.DateActualStart.Length > 50) throw new ApplicationException("Date Actual Start too long.");
            if (this.CodeAndDescriptionOriginal.Length > 50) throw new ApplicationException("Code and Description Original too long.");
            if (this.Store.Length > 50) throw new ApplicationException("Store too long.");
            if (this.StoreDescription.Length > 50) throw new ApplicationException("Store Description too long.");
            if (this.ContractTypeCode.Length > 50) throw new ApplicationException("Contact Type Code too long.");
            if (this.JobSiteContactName.Length > 50) throw new ApplicationException("Job Site Contact Name too long.");
            if (this.JobSiteContactPhone.Length > 50) throw new ApplicationException("job Site contact Phone too long.");
            if (this.PriorityCode.Length > 50) throw new ApplicationException("Priority Code too long.");
            if (this.PartNumber.Length > 50) throw new ApplicationException("Part Number too long.");
            if (this.PartNumberDescription.Length > 50) throw new ApplicationException("Part Number Description too long.");
            if (this.ReferenceDocNumber.Length > 50) throw new ApplicationException("Reference Document Number too long.");
            if (this.Quantity.Length > 50) throw new ApplicationException("Quantity too long.");
            if (this.SegmentNumber.Length > 50) throw new ApplicationException("Segment too long.");
            if (this.ServiceTech.Length > 50) throw new ApplicationException("Service Tech too long.");
            if (this.TechName.Length > 50) throw new ApplicationException("Tech Name too long.");
            if (this.InspectionType.Length > 50) throw new ApplicationException("Inspection Type too long.");
        }

        public static string WriteToDB(List<CSVObject> items, SqlConnection conn)
        {
            SqlCommand cmd = conn.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            string insertcmd = @"Insert Into Results_Brandt(CustomerNumber, CustomerName, [Address], Address2, City, [State], ZipCode, EquipmentNumber, SerialNumber, [Model], MeterReading, [Year], FamilyCode, FamilyDescription, Make, LastServiceDate, DBSWorkOrderNumber, DateActualStart, CodeAndDescriptionOriginal, CodeAndDescription, Store, StoreDescription, ContractTypeCode, JobSiteContactName, JobSiteContactPhone, PriorityCode, SpecialInstructions, PartNumber, PartNumberDescription, ReferenceDocNumber, Quantity, SegmentNumber, ServiceTech, TechName, InspectionType) Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}', '{34}')";
            List<string> msg = new List<string>();
            string msgs = string.Empty;
            foreach (var item in items)
            {
                cmd.CommandText = string.Format(insertcmd,
                    item.CustomerNumber,
                    item.CustomerName,
                    item.Address,
                    item.Address2,
                    item.City,
                    item.State,
                    item.ZipCode,
                    item.EquipmentNumber,
                    item.SerialNumber,
                    item.Model,
                    item.MeterReading,
                    item.Year,
                    item.FamilyCode,
                    item.FamilyDescription,
                    item.Make,
                    item.LastServiceDate,
                    item.DBSWorkOrderNumber,
                    item.ScheduledStartDate,
                    item.CodeAndDescriptionOriginal,
                    item.CodeAndDescription,
                    item.Store,
                    item.StoreDescription,
                    item.ContractTypeCode,
                    item.JobSiteContactName,
                    item.JobSiteContactPhone,
                    item.PriorityCode,
                    string.IsNullOrEmpty(item.SpecialInstructions) ? string.Empty : item.SpecialInstructions,
                    item.PartNumber,
                    item.PartNumberDescription,
                    item.ReferenceDocNumber,
                    item.Quantity,
                    item.SegmentNumber,
                    item.ServiceTech,
                    item.TechName,
                    item.InspectionType);
                try
                {
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                catch (Exception ex)
                {
                    item.SQLStatement = cmd.CommandText;
                    msg.Add(ex.Message);
                }
            }
            if (msg.Count > 0) return string.Join("\r\n", msg);
            else return string.Empty;
        }

        public static void WriteCSV<T>(IEnumerable<T> items, string path)
        {
            if (System.IO.File.Exists(path)) System.IO.File.Delete(path);
            Type itemType = typeof(T);
            var props = itemType.GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
            using (var writer = new System.IO.StreamWriter(path))
            {
                string[] clmns = props.Select(p => p.Name).ToArray();
                string header = string.Join(",", clmns);
                writer.WriteLine(header);
                foreach (var item in items)
                {
                    List<string> vals = new List<string>();
                    foreach (string prop in clmns)
                        vals.Add(item.GetType().GetProperty(prop).GetValue(item, null).ToString());
                    string valueline = string.Join(",", vals);
                    writer.WriteLine(valueline);
                }
            }
        }
    }
}