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

Solution 1

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.

Solution 2

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

Comments

[snaplemouton]

The problem is how you handle the multidimensional array of string. You need to use a for(int i = 0; i < array.length; i++) and set the parameters for array[i,x] x being the row for the parameter then ExecuteNonQuery inside the for

[Nagaraj Tantri]

Correct me if I am wrong, do I see that mycommand.Parameters["@Date"], Convert.ToDateTime(myArray[i,0])); is not with AddWithValue and ends with an ); ?

[snaplemouton]

You don't want an hardcoded '5' in the for though. Need to use array.length

[Steve]

The idea is to initialize the parameter collection outside the loop to avoid a constant reinitialization of the collection for every loop. Outside the loop set dummy initial values and inside the loop change just the value with the actual array data. You are right though because in the usual copy/paste error some invalid chars appeared in the answer. Should be fixed now.

[snaplemouton]

@LearningNeverStops You arn't wrong, the code inside the for won't work Edit: Now it would work. :)

[Dan Bracuk]

You also want to use query paramaters.

[snaplemouton]

Creating a simple class for that is a good thing. :) C# being Object-oriented, this is actually the best way to do it.

[rae1]

@snaplemouton True, changed.

[rae1]

@DanBracuk Thanks. I added a version for both OP's original solutions, including the one using query parameters. =)

[Yoan]

Thank you guys, the second solution without class is working. Now I need to figure out how does it work with the class.

[snaplemouton]

This answer is much better then the other one, I don't know why it lost upvotes... As for the class, C# is an object-oriented language. Creating a class for weather information is like making an object out of it. Having one dimensional array with WeatherInfo instead of a two dimensional array does 2 very good things. 1. It makes the code much more intuitive, meaning the more code you will have in your app/software/website, the less complicated it will be for someone else to understand it. And 2. Reuseability! The WeatherInfo class can then be used anytime you require it!

[Steve]

@snaplemouton not my downvote, but leaving an open Sql Injection example in the answer is really bad. For the remainder of the answer I concur that using a class is a better approach but while looking at implementing the class then I suggest to remove also the array and use a List<WeatherInfo>

[snaplemouton]

@Steve Agree 100% with you. Also, why are the set in WeatherInfo private?

[Steve]

Probably, given the context of the problem, @rae1n thought that only the constructor of WeatherInfo should set these values. Of course this could be changed depending on the requirements

[rae1]

@Steve Why a List<T> instead of an Array? Otherwise you are right, from a security stand point is perhaps to a good idea to concatenate strings; however, it was one of the OP original solutions so I wanted to provide a working version. I'll add a note regarding SQL injections to make the OP aware.

[Steve]

Because the array has a fixed length and you need to know that length before reading the file. With a List<T> there is more flexibility (suppose that one day you have more than 5 lines)

[snaplemouton]

You could also use the array as long as you type: WeatherInfo[] weatherInfo = new WeatherInfo[infos.length]; :)

[rae1]

@Steve When reading from a file I prefer to iterate and yield return an IEnumerable, leaving the decision for a List or an Array for the consumer of the function. Since OP originally used an Array I used the same thinking it must be good enough... =)