Bulk Insert into SQL Server 2008

Question

for (int i = 0; i < myClass.Length; i++)
{
        string upSql = "UPDATE CumulativeTable SET EngPosFT = @EngPosFT,EngFTAv=@EngFTAv WHERE RegNumber =@RegNumber AND Session=@Session AND Form=@Form AND Class=@Class";
        SqlCommand cmdB = new SqlCommand(upSql, connection);

        cmdB.CommandTimeout = 980000;

        cmdB.Parameters.AddWithValue("@EngPosFT", Convert.ToInt32(Pos.GetValue(i)));
        cmdB.Parameters.AddWithValue("@RegNumber", myClass.GetValue(i));
        cmdB.Parameters.AddWithValue("@EngFTAv", Math.Round((engtot / arrayCount), 2));
        cmdB.Parameters.AddWithValue("@Session", drpSess.SelectedValue);
        cmdB.Parameters.AddWithValue("@Form", drpForm.SelectedValue);
        cmdB.Parameters.AddWithValue("@Class", drpClass.SelectedValue);


        int idd = Convert.ToInt32(cmdB.ExecuteScalar());
}

assuming myClass.Length is 60. This does 60 update statements. How can I limit it to 1 update statement. Please code example using the above code will be appreciated. Thanks

Tried using this

StringBuilder command = new StringBuilder();

            SqlCommand cmdB = null;
            for (int i = 0; i < myClass.Length; i++)
            {
                command.Append("UPDATE CumulativeTable SET" + " EngPosFT = " + Convert.ToInt32(Pos.GetValue(i)) + "," + " EngFTAv = " + Math.Round((engtot / arrayCount), 2) +
        " WHERE RegNumber = " + myClass.GetValue(i) + " AND Session= " + drpSess.SelectedValue + " AND Form= " + drpForm.SelectedValue + " AND Class= " + drpClass.SelectedValue + ";");

               //or command.AppendFormat("UPDATE CumulativeTable SET EngPosFT = {0},EngFTAv={1} WHERE RegNumber ={2} AND Session={3} AND Form={4} AND Class={5};", Convert.ToInt32(Pos.GetValue(i)), Math.Round((engtot / arrayCount), 2), myClass.GetValue(i), drpSess.SelectedValue, drpForm.SelectedValue, drpClass.SelectedValue);



            }//max length is 128 error is encountered

Answer 1

Look at the BULK INSERT T-SQL command. But since I don't have a lot of personal experience with that command, I do see some immediate opportunity to improve this code using the same sql by creating the command and parameters outside of the loop, and only making the necessary changes inside the loop:

string upSql = "UPDATE CumulativeTable SET EngPosFT = @EngPosFT,EngFTAv=@EngFTAv WHERE RegNumber =@RegNumber AND Session=@Session AND Form=@Form AND Class=@Class";
SqlCommand cmdB = new SqlCommand(upSql, connection);

cmdB.CommandTimeout = 980000;

//I had to guess at the sql types you used here. 
//Adjust this to match your actual column data types
cmdB.Parameters.Add("@EngPosFT", SqlDbType.Int);  
cmdB.Parameters.Add("@RegNumber", SqlDbType.Int); 

//It's really better to use explicit types here, too.
//I'll just update the first parameter as an example of how it looks:
cmdB.Parameters.Add("@EngFTAv", SqlDbType.Decimal).Value = Math.Round((engtot / arrayCount), 2));
cmdB.Parameters.AddWithValue("@Session", drpSess.SelectedValue);
cmdB.Parameters.AddWithValue("@Form", drpForm.SelectedValue);
cmdB.Parameters.AddWithValue("@Class", SqlDbTypedrpClass.SelectedValue);

for (int i = 0; i < myClass.Length; i++)
{
    cmdB.Parameters[0].Value = Convert.ToInt32(Pos.GetValue(i)));
    cmdB.Parameters[1].Value = myClass.GetValue(i));

    int idd = Convert.ToInt32(cmdB.ExecuteScalar());
}

Answer 2

It would be better in this case to create a stored procedure that accepts a Table Valued Parameter. On the .NET side of things, you create a DataTable object containing a row for each set of values you want to use.

On the SQL Server side of things, you can treat the parameter as another table in a query. So inside the stored proc, you'd have:

UPDATE a
SET
    EngPosFT = b.EngPosFT,
    EngFTAv=b.EngFTAv
FROM
    CumulativeTable a
       inner join
    @MyParm b
       on
           a.RegNumber =b.RegNumber AND
           a.Session=b.Session AND
           a.Form=b.Form AND
           a.Class=b.Class

Where @MyParm is your table valued parameter.

This will then be processed as a single round-trip to the server.