ADO NET C# how can I add collection to database?

Question

I don't have any problem when I add just only one record.

public int Add(Company Company)
        {
            var cmd = new SqlCommand("MD.CompanyAdd", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 0;

            try
            {
                conn.Open();

                cmd.Parameters.AddWithValue("@CompanyName", Company.CompanyName);
                cmd.Parameters.AddWithValue("@CompanyCode", Company.CompanyCode);
                cmd.Parameters.AddWithValue("@RateType", Company.RateType);

                var result = cmd.ExecuteScalar();

                //var result = cmd.ExecuteNonQuery();
                //var qwe = result.ToString();

                return int.Parse(result.ToString());
            }
            catch (Exception ex)
            {
                Debug.WriteLine("Exception: " + ex.Message);
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

But I don't understand how I can add collection. i think I should do something with DataTable, but I don't understand what exactly.

class CompanyRepository
    {
        private string _connectionString;
        private SqlConnection conn;

        public CompanyRepository(string ConnectionString)
        {
            _connectionString = ConnectionString;
            conn = new SqlConnection(_connectionString);
        }

        public void AddList(IList<Company> Companies)
        {
            try
            {
                conn.Open();

                var cmd = new SqlCommand("MD.CompanyAddList", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;

                DataTable dataTable = new DataTable();

                dataTable.Columns.Add("[CompanyCode]", typeof(string));
                dataTable.Columns.Add("[CompanyName]", typeof(string));
                dataTable.Columns.Add("[RateType]", typeof(string));


                foreach (var Company in Companies)
                {
                    dataTable.Rows.Add(Company);
                }

                cmd.Parameters.AddWithValue("@ImportTable", dataTable);

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Debug.WriteLine("Exception: " + ex.Message);
                throw;
            }
            finally
            {
                conn.Close();
            }
        }
    }

    public class Company 
    {
        public int CompanyId { get; set; } // CompanyID (Primary key)
        public string CompanyCode { get; set; } // CompanyCode (length: 10)
        public string CompanyName { get; set; } // CompanyName (length: 256)
        public int? RateType { get; set; } // RateType

    }

HELP me please!

P.S. Please sorry me for spam, but site requires more some text before I can create post. I hope this will be enough.

https://stackoverflow.com/questions/17150542/how-to-insert-a-c-sharp-list-to-database-using-dapper-net — mjwills, Apr 03 '20 at 08:12
You could simply loop around the command creation and generation for every item in the collection. Another approach could be the SqlBulkCopy class. — Ryan Thomas, Apr 03 '20 at 08:14

score 0 · Accepted Answer · answered Apr 03 '20 at 08:18

I think this may work.

DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("Id", typeof(string)));
dt.Columns.Add(new DataColumn("Name", typeof(string)));
foreach (Entry entry in entries)
   dt.Rows.Add(new string[] { entry.Id, entry.Name });

using (SqlBulkCopy bc = new SqlBulkCopy(connection))
{   // the following 3 lines might not be neccessary
    bc.DestinationTableName = "Entries";
    bc.ColumnMappings.Add("Id", "Id");
    bc.ColumnMappings.Add("Name", "Name");

    bc.WriteToServer(dt);
}

copied from here

ADO NET C# how can I add collection to database?

1 Answers1