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;
}