Inserting Multiple Records into SQL Server database using for loop

Question

I am working on a Windows form project in C#. I am trying to insert multiple records into the SQL Server database from an array.

After entering the first row I get an exception

@UserID has already been declared. Variable names must be unique within a query batch or stored procedure.

There is no issue with the primary key in the database as UserID is not the primary key.

This is what I am trying to do.

public static void featuresentry()
{
    SqlConnection connection = new SqlConnection(HandVeinPattern.Properties.Settings.Default.HandVeinPatternConnectionString);

    SqlCommand command = new SqlCommand();
    connection.Open();

    try
    {
        command = connection.CreateCommand();

        for (int i = 0; i < Details.modelKeyPoints.Size; i++)
        {
            command.CommandText = "INSERT INTO FEATURES(UserID, Angle, ClassID, Octave, PointX, PointY, Response, Size) VALUES(@UserID, @Angle, @ClassID, @Octave, @PointX, @PointY, @Response, @Size)";

            command.Parameters.AddWithValue("@UserID", Details.ID);
            command.Parameters.AddWithValue("@Angle", Convert.ToDouble(Details.modelKeyPoints[i].Angle));
            command.Parameters.AddWithValue("@ClassID", Convert.ToDouble(Details.modelKeyPoints[i].ClassId));
            command.Parameters.AddWithValue("@Octave", Convert.ToDouble(Details.modelKeyPoints[i].Octave));
            command.Parameters.AddWithValue("@PointX", Convert.ToDouble(Details.modelKeyPoints[i].Point.X));
            command.Parameters.AddWithValue("@PointY", Convert.ToDouble(Details.modelKeyPoints[i].Point.Y));
            command.Parameters.AddWithValue("@Response", Convert.ToDouble(Details.modelKeyPoints[i].Response));
            command.Parameters.AddWithValue("@Size", Convert.ToDouble(Details.modelKeyPoints[i].Size));

            command.ExecuteNonQuery();
        }
    }
    catch (Exception)
    {
        throw;
    }
    finally
    {
        if (connection.State == ConnectionState.Open)
        {
            connection.Close();
        }
    }
}

Answer 1

You should do this properly:

• define your parameters once outside the loop
• define the values of your parameters inside the loop for each iteration
• use using(...) { ... } blocks to get rid of the try ... catch ... finally (the using block will ensure proper and speedy disposal of your classes, when no longer needed)
• stop using a try...catch if you're not actually handling the exceptions - just rethrowing them (makes no sense)

Try this code:

public static void featuresentry()
{
    string connectionString = HandVeinPattern.Properties.Settings.Default.HandVeinPatternConnectionString;
    string insertQuery = "INSERT INTO FEATURES(UserID, Angle, ClassID, Octave, PointX, PointY, Response, Size) VALUES(@UserID, @Angle, @ClassID, @Octave, @PointX, @PointY, @Response, @Size)";

    using (SqlConnection connection = new SqlConnection(connectionString))
    using (SqlCommand command = new SqlCommand(insertQuery, connection))
    {
        // define your parameters ONCE outside the loop, and use EXPLICIT typing
        command.Parameters.Add("@UserID", SqlDbType.Int);
        command.Parameters.Add("@Angle", SqlDbType.Double);
        command.Parameters.Add("@ClassID", SqlDbType.Double);
        command.Parameters.Add("@Octave", SqlDbType.Double);
        command.Parameters.Add("@PointX", SqlDbType.Double);
        command.Parameters.Add("@PointY", SqlDbType.Double);
        command.Parameters.Add("@Response", SqlDbType.Double);
        command.Parameters.Add("@Size", SqlDbType.Double);

        connection.Open();

        for (int i = 0; i < Details.modelKeyPoints.Size; i++)
        {
            // now just SET the values
            command.Parameters["@UserID"].Value = Details.ID;
            command.Parameters["@Angle"].Value = Convert.ToDouble(Details.modelKeyPoints[i].Angle);
            command.Parameters["@ClassID"].Value = Convert.ToDouble(Details.modelKeyPoints[i].ClassId);
            command.Parameters["@Octave"].Value = Convert.ToDouble(Details.modelKeyPoints[i].Octave);
            command.Parameters["@PointX"].Value = Convert.ToDouble(Details.modelKeyPoints[i].Point.X);
            command.Parameters["@PointY"].Value = Convert.ToDouble(Details.modelKeyPoints[i].Point.Y);
            command.Parameters["@Response"].Value = Convert.ToDouble(Details.modelKeyPoints[i].Response);
            command.Parameters["@Size"].Value = Convert.ToDouble(Details.modelKeyPoints[i].Size);

            command.ExecuteNonQuery();
        }
    }
}