iterate an Ilist and insert to table

Question

I've an IList with 5 items. I need to insert these, looping one by one, from the list to a SQL Server table using ado.net. I am using IReader(enterprise library)

How can I do this?

Answer 1

Consider your List: IList<Customer> foo;

Using LINQ To SQL

Create a LINQ To SQL datacontext, and it's something like this:

using (var db = new CustomerDataContext())
{
   db.Customer.InsertAllOnSubmit(foo);
   db.SubmitChanges();
}

Using Stored Procedures

using (var conn = new SqlConnection())
{        
     using (var cmd = new SqlCommand(conn))
     {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "InsertCustomer";

        foreach (Customer c in foo)
        {
             cmd.Parameters.Clear();
             cmd.Parameters.Add(new SqlParameter("@CustomerName", c.Name);

             conn.Open(dbConnString);
             cmd.ExecuteNonQuery();
             conn.Close();
        }
    }
}

This code is perhaps not copy/paste ready, perhaps a syntax/name/property problem somewhere there, but it's definitely a starting point using ADO.NET.

Answer 2

foreach(object obj in IList)

{

// 1-Here your Data Access Code.Data type of obj can be replaced with the actual type of object which is used in the Ilist.

// 2- Now if your application uses DAL (Data Access Layer , you need to call DAL from here.

// If Not Point no 2 is correct than you can create a Datatable object before foreach statement and add rows in the Datatable.

// And Call DataAdapter.Update() ( SqlAdapter etc.. based upon your database , you can see ADO.NET for reference.

}

Answer 3

If basic sqlconnection class you are using then you can do as below:

IList<Record> records = new List<Record>();

  SqlConnection connection = new SqlConnection("Give connection string here");

  connection.Open();
  var insertCommand = connection.CreateCommand();

  foreach (var record in records)
  {
    insertCommand.CommandText = string.Format("Insert into table1 (field1, field2) values ({0}, {2})", record.field1,
                                              record.field2);
    insertCommand.ExecuteScalar();
  }

  connection.Close();