How to insert the elements of an array into a SQL Server database using C#?

Question

I need to insert the weather forecast (temperature) into a SQL Server database in order to control remotely the heating of a building.

The steps are:

1. Getting data from an RSS feed
2. Putting them in an array
3. Connecting to an SQL database
4. Inserting the elements into the SQL database

I did the 3 first steps but I'm stuck with the last one.

The array is created from an RSS feed from Yahoo weather.

string[,] myarray1 = new string[5, 3];

The name of the database's columns are: Date, Templow, Temphigh

I'm trying now to insert the elements of this array into a SQL Server database. I've struggled with that for hours, and I can't figure out how to do it. I looked at many solutions on this website without succeeding.

I tried:

foreach (string str2 in myarray1)
{
    var mycommand = new SqlCommand("INSERT INTO RSS2 VALUES(@Date, @Templow, @Temphigh)", myConnection);
    mycommand.Parameters.AddWithValue("@Date", str2);
    mycommand.Parameters.AddWithValue("@Templow", str2);
    mycommand.Parameters.AddWithValue("@Temphigh", str2);
    mycommand.ExecuteNonQuery();
}

and

for (k = 0; k < 5; k++)
{
    SqlCommand myCommand = new SqlCommand("INSERT INTO RSS2 (Date, Templow, Temphigh)" +
            "Values ('myarray1[k,0]','myarray1[k,1]','myarray1[k,2]')", myConnection);
    myCommand.ExecuteNonQuery();
}

and many others...

None of those solutions are corrects. This is my first code in c# (I'm used to Basic), so be clement ;-)

Answer 1

Supposing that your database table requires a date and two double values for low and high temp then you could build your command and its parameters outside the loop and then just update the parameter values inside the loop.

var mycommand = new SqlCommand("INSERT INTO RSS2 VALUES(@Date, @Templow, @Temphigh)", 
                               myConnection);

mycommand.Parameters.AddWithValue("@Date", DateTime.MinValue);
mycommand.Parameters.AddWithValue("@Templow", Double.MinValue);
mycommand.Parameters.AddWithValue("@Temphigh", Double.MinValue);
for (i = 0; i < 5; i++)
{
    mycommand.Parameters["@Date"].Value = Convert.ToDateTime(myArray[i,0]);   
    mycommand.Parameters["@Templow"].Value = Convert.ToDouble(myArray[i,1]);   
    mycommand.Parameters["@Temphigh"].Value = Convert.ToDouble(myArray[i,2]);    
    mycommand.ExecuteNonQuery();
}

Another interesting option could be the use of a Table Valued Parameter but it is not a big gain for only five rows. Instead It could be a difference maker if you have many rows to add

In your Sql Server db create the datatype for the parameter and a stored procedure that inserts the records

CREATE TYPE dbo.WeatherInfo AS TABLE
( infoDate smalldatetime, minTemp float, maxTemp float )

CREATE PROCEDURE usp_InsertWeatherInfo
(@tbpWeatherInfo dbo.WeatherInfo READONLY)
AS
    INSERT INTO dbo.RSS2 (dateInfo, minTemp, maxTemp)
    SELECT wi.dateInfo, wi.minTemp, wi.maxTemp FROM @tbpWeatherInfo AS wi

in your C# code instead of the array create a datatable with the date, min and max temp and pass it to the stored procedure

using (connection)
{
      DataTable wiInfo = GetWeatherInfo();
      SqlCommand insertCommand = new SqlCommand("usp_InsertWeatherInfo", connection);
      insertCommand.CommandType = CommandType.StoredProcedure;
      SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tbpWeatherInfo", wiInfo);
      tvpParam.SqlDbType = SqlDbType.Structured;
      insertCommand.ExecuteNonQuery();
}

private DataTable GetWeatherInfo()
{
    DataTable wi = new DataTable();
    wi.Columns.Add("infoDate", typeof(DateTime));
    wi.Columns.Add("minTemp", typeof(double));
    wi.Columns.Add("maxTemp", typeof(double));

    ... loop reading aline of weather info ....
        DataRow row = wi.NewRow();
        wi["infoDate"] = ... datetime from line ....
        wi["minTemp"] = ... minTemp from line ....
        wi["maxTemp"] = ... maxTemp from line ....
        wi.Rows.Add(row);
    ... next line
    return wi;
}

Answer 2

Both solutions are close, but not quite right.

However, I would suggest creating a simple class, call it WeatherInfo,

public class WeatherInfo
{
    public WeatherInfo(string date, string tempLow, string tempHigh)
    {
        this.Date = date;
        this.TempLow = tempLow;
        this.TempHigh = tempHigh;
    }

    public string Date { get; private set; }
    public string TempLow { get; private set; }
    public string TempHigh { get; private set; }
}

You can initialize it like this,

WeatherInfo weather = new WeatherInfo("01/01/2014", "56F", "89F");

Then you can use an array of these, WeatherInfo[],

WeatherInfo[] infos = new WeatherInfo[5];

You can still access it using indices, infos[0] to get to the WeatherInfo object. Then you can use your first solution a bit easier,

foreach (WeatherInfo info in infos)
{
    var mycommand = new SqlCommand("INSERT INTO RSS2 VALUES(@Date, @Templow, @Temphigh)", myConnection);
    mycommand.Parameters.AddWithValue("@Date", info.Date);
    mycommand.Parameters.AddWithValue("@Templow", info.TempLow);
    mycommand.Parameters.AddWithValue("@Temphigh", info.TempHigh);
    mycommand.ExecuteNonQuery();
}

Or your second solution,

for (i = 0; i < infos.Length; i++)
{
    SqlCommand myCommand = new SqlCommand(
        "INSERT INTO RSS2 (Date, Templow, Temphigh)" +
        "Values ('" + infos[i].Date + "','" + infos[i].TempLow + "','" + infos[i].TempHigh + "')", 
        myConnection);
    myCommand.ExecuteNonQuery();
}

However, this second solution suffers from a security vulnerability known as SQL Injection, where some attacker might insert unwanted SQL (like 1; SELECT * FROM table;) in the string values, which you then pass to the database without verifying it is content for these type of commands.