patterncsharpMinor

Parsing remote text file and inserting into database table

Submitted by: @import:stackexchange-codereview·Mar 10, 2026·

Viewed 0 times

insertingfileintotextanddatabaseparsingremotetable

Problem

I did run this code through StyleCop. Goal was to optimize the reading, parsing and inserting of a file on the web and write the rows to a database table using a stored procedure. Contrast this (roughly) to my most previous code review here: C# helper class - mailto Different code but I feel like I'm making progress, for being still fairly new to C#. Thanks.

Program.cs

```
using System;
using System.Collections.Generic;
using System.Linq;

namespace NewReleases
{
public class Program
{
public static void Main(string[] args)
{
const string PremierPublishers = "PREMIER PUBLISHERS";
const string NewReleasesFor = "New Releases For";

ReleaseItem releaseItem = new ReleaseItem();
List premierPublishers = Release.PremierPublishers();
DateTime releaseDate = new DateTime();

foreach (var line in Release.GetRelease())
{
if (line.Contains(NewReleasesFor))
{
releaseDate = DateTime.Parse(line.Substring(line.Length - 10));
}
else
{
if (!line.Any(p => p.ToString().Contains("\t")) & !premierPublishers.Any(p => p == line))
{
releaseItem.Category = line.Trim();
}
else if (premierPublishers.Any(p => p == line))
{
releaseItem.Publisher = line.Trim();
}
else
{
string[] lineitem = line.Split('\t');

if (lineitem.Count() == 3)
{
releaseItem.ItemCode = lineitem[0].Trim();
releaseItem.Title = lineitem[1].Trim();
releaseItem.Price = lineitem[2].Trim();

if (releaseItem.Category != PremierPublishers &

Solution

Let's refactor it a little bit toward SOLID. It's not yet perfect but is should give you an idea how to start (you should take @Heslacher's advices into account)

Add the release date to the release item:

public class ReleaseItem
{
    public string Category { get; set; }
    public string Publisher { get; set; }
    public string ItemCode { get; set; }
    public string Title { get; set; }
    public string Price { get; set; }
    public DateTime ReleasedOn { get; set; }
}

Create a release downloader to get the lines:

public class ReleaseDownloader
{
    public IEnumerable GetRelease(string remoteReleaseFile)
    {
        using (WebClient webClient = new WebClient())
        {
            Stream stream = webClient.OpenRead(remoteReleaseFile);
            List lines = new List();

            using (StreamReader streamReader = new StreamReader(stream))
            {
                while (!streamReader.EndOfStream)
                {
                    lines.Add(streamReader.ReadLine());
                }
            }

            return lines.Where(f => !string.IsNullOrWhiteSpace(f));
        }
    }    
}

Create a release repository to take care of the database interaction:

public class ReleaseRepository
{    
    public ReleaseRepository(string connectionString)
    {
        ConnectionString = connectionString;
    }

    public string ConnectionString { get; }

    public List PremierPublishers()
    {
        using (IDbConnection connection = new SqlConnection(ConnectionString))
        {
            return connection.Query("Select PremierPublisher From PremierPublishers").ToList();
        }
    }

    public void SaveRelease(ReleaseItem releaseItem)
    {
        using (var connection = new SqlConnection(ConnectionString))
        {
            decimal price = 0;
            string note = null;
            bool isDecimal = decimal.TryParse(releaseItem.Price.Replace("$", string.Empty), out price);

            if (!isDecimal)
            {
                note = releaseItem.Price;
            }
            else
            {
                price = Convert.ToDecimal(releaseItem.Price.Replace("$", string.Empty));
            }

            connection.Execute(
                "InsertReleaseItem",
                new
                {
                    releaseItem.ReleasedOn,
                    releaseItem.Category,
                    releaseItem.Publisher,
                    releaseItem.ItemCode,
                    releaseItem.Title,
                    price,
                    note
                },
                commandType: CommandType.StoredProcedure);
        }
    }
}

It would also be a good idea to remove these lines from the SaveRelease method as they are not related to saving and put them in the ReleaseItem class as properties with a getter only:

bool isDecimal = decimal.TryParse(releaseItem.Price.Replace("$", string.Empty), out price);

if (!isDecimal)
{
    note = releaseItem.Price;
}
else
{
   price = Convert.ToDecimal(releaseItem.Price.Replace("$", string.Empty));
}

Create a release parser that's specialization is to parse the lines only:

public class ReleaseParser
{
    const string PremierPublishers = "PREMIER PUBLISHERS";
    const string NewReleasesFor = "New Releases For";

    public static IEnumerable Parse(IEnumerable lines, IEnumerable premierPublishers)
    {
        var releaseItem = new ReleaseItem
        {
            ReleasedOn = DateTime.Now
        };

        foreach (var line in lines)
        {
            if (line.Contains(NewReleasesFor))
            {
                releaseItem.ReleasedOn = DateTime.Parse(line.Substring(line.Length - 10));
            }
            else
            {
                if (!line.Any(p => p.ToString().Contains("\t")) & !premierPublishers.Any(p => p == line))
                {
                    releaseItem.Category = line.Trim();
                }
                else if (premierPublishers.Any(p => p == line))
                {
                    releaseItem.Publisher = line.Trim();
                }
                else
                {
                    string[] lineitem = line.Split('\t');

                    if (lineitem.Count() == 3)
                    {
                        releaseItem.ItemCode = lineitem[0].Trim();
                        releaseItem.Title = lineitem[1].Trim();
                        releaseItem.Price = lineitem[2].Trim();

                        if (releaseItem.Category != PremierPublishers & !premierPublishers.Any(p => p == line))
                        {
                            releaseItem.Publisher = null;
                        }

                        yield return releaseItem;
                    }
                }
            }
        }
    }
}

Refactor the main method and get the connection string from the connections-string section and not the app-settings. Create the downloader, the parser, the repository and let th

Code Snippets

public class ReleaseItem
{
    public string Category { get; set; }
    public string Publisher { get; set; }
    public string ItemCode { get; set; }
    public string Title { get; set; }
    public string Price { get; set; }
    public DateTime ReleasedOn { get; set; }
}

public class ReleaseDownloader
{
    public IEnumerable<string> GetRelease(string remoteReleaseFile)
    {
        using (WebClient webClient = new WebClient())
        {
            Stream stream = webClient.OpenRead(remoteReleaseFile);
            List<string> lines = new List<string>();

            using (StreamReader streamReader = new StreamReader(stream))
            {
                while (!streamReader.EndOfStream)
                {
                    lines.Add(streamReader.ReadLine());
                }
            }

            return lines.Where(f => !string.IsNullOrWhiteSpace(f));
        }
    }    
}

public class ReleaseRepository
{    
    public ReleaseRepository(string connectionString)
    {
        ConnectionString = connectionString;
    }

    public string ConnectionString { get; }

    public List<string> PremierPublishers()
    {
        using (IDbConnection connection = new SqlConnection(ConnectionString))
        {
            return connection.Query<string>("Select PremierPublisher From PremierPublishers").ToList();
        }
    }

    public void SaveRelease(ReleaseItem releaseItem)
    {
        using (var connection = new SqlConnection(ConnectionString))
        {
            decimal price = 0;
            string note = null;
            bool isDecimal = decimal.TryParse(releaseItem.Price.Replace("$", string.Empty), out price);

            if (!isDecimal)
            {
                note = releaseItem.Price;
            }
            else
            {
                price = Convert.ToDecimal(releaseItem.Price.Replace("$", string.Empty));
            }

            connection.Execute(
                "InsertReleaseItem",
                new
                {
                    releaseItem.ReleasedOn,
                    releaseItem.Category,
                    releaseItem.Publisher,
                    releaseItem.ItemCode,
                    releaseItem.Title,
                    price,
                    note
                },
                commandType: CommandType.StoredProcedure);
        }
    }
}

bool isDecimal = decimal.TryParse(releaseItem.Price.Replace("$", string.Empty), out price);

if (!isDecimal)
{
    note = releaseItem.Price;
}
else
{
   price = Convert.ToDecimal(releaseItem.Price.Replace("$", string.Empty));
}

public class ReleaseParser
{
    const string PremierPublishers = "PREMIER PUBLISHERS";
    const string NewReleasesFor = "New Releases For";

    public static IEnumerable<ReleaseItem> Parse(IEnumerable<string> lines, IEnumerable<string> premierPublishers)
    {
        var releaseItem = new ReleaseItem
        {
            ReleasedOn = DateTime.Now
        };

        foreach (var line in lines)
        {
            if (line.Contains(NewReleasesFor))
            {
                releaseItem.ReleasedOn = DateTime.Parse(line.Substring(line.Length - 10));
            }
            else
            {
                if (!line.Any(p => p.ToString().Contains("\t")) & !premierPublishers.Any(p => p == line))
                {
                    releaseItem.Category = line.Trim();
                }
                else if (premierPublishers.Any(p => p == line))
                {
                    releaseItem.Publisher = line.Trim();
                }
                else
                {
                    string[] lineitem = line.Split('\t');

                    if (lineitem.Count() == 3)
                    {
                        releaseItem.ItemCode = lineitem[0].Trim();
                        releaseItem.Title = lineitem[1].Trim();
                        releaseItem.Price = lineitem[2].Trim();

                        if (releaseItem.Category != PremierPublishers & !premierPublishers.Any(p => p == line))
                        {
                            releaseItem.Publisher = null;
                        }

                        yield return releaseItem;
                    }
                }
            }
        }
    }
}

Context

StackExchange Code Review Q#133250, answer score: 3

Revisions (0)

No revisions yet.