Inserting Multiple Records into SQL Server database using for loop

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.

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.

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.

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:

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

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

3. 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