Writing to Database

Since we just created the SQL server, database, and table, we need to add the data to it. Since we have the System.Data.SqlClient package, we can use SqlConnection to connect to the database. Note that, an ORM like Entity Framework can be used instead of the methods from the System.Data.SqlClient package.

Real quick, though, let's set up by adding a couple of fields on the class. One to hold the SQL connection string and another to have a constant string of the file name for the model we will create.

private static string _sqlConectionString; private static readonly string fileName = "wine.zip";

Next, let's use the ConfigurationBuilder to build the configuration object and to allow us to read in the config file values.

var builder = new ConfigurationBuilder() .SetBasePath(Directory.GetCurrentDirectory()) .AddJsonFile("config.json"); var configuration = builder.Build();

With the configuration built, we can use it to pull out the SQL connection string and assign it to the field created earlier.

_sqlConectionString = configuration["connectionString"];

To write the data into the database we need to read in from the file. I put the file in the solution and make sure it can be read using the same method as the config file to make sure it gets copied over.

Using LINQ, we can read from the file and parse out each of the columns into a WineData object.

var items = File.ReadAllLines("./winequality.csv") .Skip(1) .Select(line => line.Split(",")) .Select(i => new WineData { Type = i[0], FixedAcidity = Parse(i[1]), VolatileAcidity = Parse(i[2]), CitricAcid = Parse(i[3]), ResidualSugar = Parse(i[4]), Chlorides = Parse(i[5]), FreeSulfurDioxide = Parse(i[6]), TotalSulfurDioxide = Parse(i[7]), Density = Parse(i[8]), Ph = Parse(i[9]), Sulphates = Parse(i[10]), Alcohol = Parse(i[11]), Quality = Parse(i[12]) });

The WineData class holds all of the fields that are in the data file.

public class WineData { public string Type; public float FixedAcidity; public float VolatileAcidity; public float CitricAcid; public float ResidualSugar; public float Chlorides; public float FreeSulfurDioxide; public float TotalSulfurDioxide; public float Density; public float Ph; public float Sulphates; public float Alcohol; public float Quality; }

There's an additional Parse method added to all but one of the fields. That's due to us getting back a string value of the data, but our class says it should be of type float . The Parse method is fairly straight forward in that it just tries to parse out the field and if it can't it uses the default value of float , which is 0.0.

private static float Parse(string value) { return float.TryParse(value, out float parsedValue) ? parsedValue : default(float); }

Now that we have the data, we can save it to the database. In a using statement, new up an instance of SqlConnection and pass in the connection string as the parameter. Inside here, we need to call the Open method of the connection and then create an insert statment. Then, loop over each item from the results of reading in the file and add each field from the item as parameters for the insert statement. After that, call the ExecuteNonQuery method to execute the query on the database.

using (var connection = new SqlConnection(_sqlConectionString)) { connection.Open(); var insertCommand = @"INSERT INTO dbo.WineData VALUES (@type, @fixedAcidity, @volatileAcidity, @citricAcid, @residualSugar, @chlorides, @freeSulfureDioxide, @totalSulfurDioxide, @density, @ph, @sulphates, @alcohol, @quality);"; foreach (var item in items) { var command = new SqlCommand(insertCommand, connection); command.Parameters.AddWithValue("@type", item.Type); command.Parameters.AddWithValue("@fixedAcidity", item.FixedAcidity); command.Parameters.AddWithValue("@volatileAcidity", item.VolatileAcidity); command.Parameters.AddWithValue("@citricAcid", item.CitricAcid); command.Parameters.AddWithValue("@residualSugar", item.ResidualSugar); command.Parameters.AddWithValue("@chlorides", item.Chlorides); command.Parameters.AddWithValue("@freeSulfureDioxide", item.FreeSulfurDioxide); command.Parameters.AddWithValue("@totalSulfurDioxide", item.TotalSulfurDioxide); command.Parameters.AddWithValue("@density", item.Density); command.Parameters.AddWithValue("@ph", item.Ph); command.Parameters.AddWithValue("@sulphates", item.Sulphates); command.Parameters.AddWithValue("@alcohol", item.Alcohol); command.Parameters.AddWithValue("@quality", item.Quality); command.ExecuteNonQuery(); } }

We can run this and check the database to make sure the data got added.