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

I think if you put `command = connection.CreateCommand();` inside your for loop, it will work. The problem is you are looping over the command parameters only, so its trying to add more parameters to your existing command, but theyre already in there. So you need to make a new command every loop instead. — Unicorno Marley, Apr 23 '16 at 20:21
@UnicornoMarley, Yes that's the issue. Nice catch, post that as answer. — Rahul, Apr 23 '16 at 20:25
The other option would be to move all of your command and parameter creation outside of the loop, and just update the values and execute within the loop. This way you aren't continuously creating new instances of objects. — gmiley, Apr 23 '16 at 20:26

score 9 · Accepted Answer · edited Feb 15 '17 at 11:21

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

Or even better, use a table valued parameter and execute just a single statement... — Zohar Peled, Apr 25 '16 at 04:46
@ZoharPeled it's a shame you didn't provide an answer along those lines! — Mr. Boy, May 28 '20 at 15:40

score 2 · Answer 2 · answered Apr 23 '16 at 20:26

If you put command = connection.CreateCommand(); inside your for loop, it will work. The problem is you are looping over the command parameters only, so its trying to add more parameters to your existing command, but theyre already in there. So you need to make a new command every loop instead.

score 1 · Answer 3 · answered Apr 23 '16 at 21:03

In order to obtain the maximum performance, you may consider a BulkInsert. This ensures that your insert are done as fast as possible, as any issued query has some overhead (a large query will generally execute faster than many small ones). It should look something like the following:

1) define AsDataTable extension method from here:

   public static DataTable AsDataTable<T>(this IEnumerable<T> data)
   {
       PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
       var table = new DataTable();
       foreach (PropertyDescriptor prop in properties)
           table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
       foreach (T item in data)
       {
           DataRow row = table.NewRow();
           foreach (PropertyDescriptor prop in properties)
               row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
           table.Rows.Add(row);
       }
       return table;
   }

2) execute the actual BulkInsert like this (not tested):

using (SqlConnection connection = new SqlConnection(connectionString))
{
     connection.Open();
     SqlTransaction transaction = connection.BeginTransaction();

     using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
     {
        bulkCopy.BatchSize = 100;
        bulkCopy.DestinationTableName = "dbo.FEATURES";
        try
        {
            // define mappings for columns, as property names / generated data table column names
            // is different from destination table column name
            bulkCopy.ColumnMappings.Add("ID","UserID");
            bulkCopy.ColumnMappings.Add("Angle","Angle");
            // the other mappings come here

            bulkCopy.WriteToServer(Details.modelKeyPoints.AsDataTable());
        }
        catch (Exception)
        {
            transaction.Rollback();
            connection.Close();
        }
      }

      transaction.Commit();
}

Of course, if convention over configuration would be used (object properties names would match exactly destination table column names), no mapping would be required.

score 0 · Answer 4 · answered Apr 23 '16 at 20:24

You need to add Command parameters outside the loop or declare Command inside the loop.

In the first case you will need to update each parameter's value like this:

oleDbCommand1.Parameters["@UserID"].Value = Details.ID;

And execute the command once new values are set.

score 0 · Answer 5 · edited May 24 '17 at 06:24

You can do this by sending your data as an xml string and convert in into table in a stored procedure in sql. For example: suppose I am sending multiple rows to add/update in an sql table then here are the steps:

Convert your class or list of class into an xml string using following method:

public static string SerializeObjectToXmlString(object value)

          {
          var emptyNamepsaces = new XmlSerializerNamespaces(new[] { 
                                    XmlQualifiedName.Empty });

    var serializer = new XmlSerializer(value.GetType());
    var settings = new XmlWriterSettings();
    settings.Indent = true;
    settings.OmitXmlDeclaration = true;

    using (var stream = new StringWriter())
    using (var writer = XmlWriter.Create(stream, settings))
    {
        serializer.Serialize(writer, value, emptyNamepsaces);
        return stream.ToString();
    }
}

Now while sending data to the database convert your class object into xml string (Here I am using entity framework in my code, you can do this without using it as well):

bool AddUpdateData(List<MyClass> data)
{
    bool returnResult = false;
    string datatXml = Helper.SerializeObjectToXmlString(data);
    var sqlparam = new List<SqlParameter>()
                 {
   new SqlParameter() { ParameterName = "dataXml", Value = datatXml}

                 };
    var result = this.myEntity.Repository<SQL_StoredProc_ComplexType>().ExecuteStoredProc("SQL_StoredProc", sqlparam);
    if (result != null && result.Count() > 0)
    {
        returnResult = result[0].Status == 1 ? true : false;
    }
    return returnResult;
}

Now your SQL Code:

3.1 Declare a table variable:

DECLARE @tableVariableName TABLE
(
    ID INT, Name VARCHAR(20)
)

3.2 Insert Your xml string into Table variable

INSERT INTO @tableVariableName
SELECT 
    Finaldata.R.value ('(ID/text())[1]', 'INT') AS ID, 
    Finaldata.R.value ('(Name/text())[1]', 'VARCHAR(20)') AS Name
FROM @MyInputXmlString.nodes ('//ArrayMyClass/MyClass') AS Finaldata (R)

3.3 Finally insert this table value into your sql table

INSERT INTO MyTable (ID, Name)                  
SELECT ID, Name          
FROM @tableVariableName

This will save your effort of hitting database again and again using a for loop.

Hope it will help you

Inserting Multiple Records into SQL Server database using for loop

5 Answers5

Linked

Related