Insert lists of different types with foreach loop into a SQL Server database

Question

I'm trying to add data into a database using a foreach loop to iterate through lists which contain values of different types.

List<string> productCodeList = new List<string>();
List<string> productNameList = new List<string>();
List<string> productPriceList = new List<string>();
List<int> competitorList = new List<int>();
List<DateTime> dateCreatedList = new List<DateTime>();

Is there a way to iterate through multiple lists with different types? Because at the moment I'm only able to insert one list to one column. What I want is to insert data to all the columns specified.

Or is there possibly a better way of doing this?

using (var con = new SqlConnection(connectionString))
{
    con.Open();

    using (var cmd = new SqlCommand(@"INSERT INTO OnlineProductsTemp$(CompetitorID, ProductCode, ProductName, Price, DateCreated)
                VALUES(@CompetitorID, @ProductCode, @ProductName, @Price, @DateCreated)", con))
    {
        cmd.Parameters.Add("@CompetitorID", SqlDbType.Int);
        cmd.Parameters.Add("@ProductCode", SqlDbType.VarChar);
        cmd.Parameters.Add("@ProductName", SqlDbType.VarChar);
        cmd.Parameters.Add("@Price", SqlDbType.Decimal);
        cmd.Parameters.Add("@DateCreated", SqlDbType.DateTime);

        foreach (var value in competitorList)
        {
             cmd.Parameters["@CompetitorID"].Value = value;
             int rowsAffected = cmd.ExecuteNonQuery();
        }

        competitorList.Clear();
    }
}

Thanks in advance!

WiSeeker · Accepted Answer · 2016-01-20 19:53:16Z

You have logically related properties like name, code etc split into multiple lists which would be hard to maintain and error prone like @Santiago pointed out.

If you have complete control over the code, you may consider creating a Product class and having a collection of Products.

public class Product
{
    public string Code { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int CompetitorId { get; set; }
    public DateTime DateCreated { get; set; }
}

public class DemoClass
{
    public static void Demo()
    {
        var products = new List<Product>();

        // fill the list here

        using (var con = new SqlConnection(connectionString))
        {
            con.Open();
            using (var cmd = new SqlCommand(@"INSERT INTO OnlineProductsTemp$(CompetitorID, ProductCode, ProductName, Price, DateCreated)
            VALUES(@CompetitorID, @ProductCode, @ProductName, @Price, @DateCreated)", con))
            {
                cmd.Parameters.Add("@CompetitorID", SqlDbType.Int);
                cmd.Parameters.Add("@ProductCode", SqlDbType.VarChar);
                cmd.Parameters.Add("@ProductName", SqlDbType.VarChar);
                cmd.Parameters.Add("@Price", SqlDbType.Decimal);
                cmd.Parameters.Add("@DateCreated", SqlDbType.DateTime);
                foreach (var p in products)
                {
                    cmd.Parameters["@CompetitorID"].Value = p.CompetitorId;
                    cmd.Parameters["@ProductCode"].Value = p.Code;
                    cmd.Parameters["@ProductName"].Value = p.Name;
                    cmd.Parameters["@Price"].Value = p.Price;
                    cmd.Parameters["@DateCreated"].Value = p.DateCreated;

                    var rowsAffected = cmd.ExecuteNonQuery();
                }
                products.Clear();
            }
        }
    }
}

Steve Wellens · Accepted Answer · 2016-01-20 19:20:26Z

1

I think you want something like this:

for (int i = 0; i < competitorList.Count; i++)
{
    cmd.Parameters["@CompetitorID"].Value = competitorList[i];
    cmd.Parameters["@ProductCode"].Value = productCodeList[i];
    cmd.Parameters["@ProductName"].Value = productNameList[i];
    // etc
    int rowsAffected = cmd.ExecuteNonQuery();
}

answered Jan 20, 2016 at 19:20

Steve Wellens

20.6k2 gold badges31 silver badges70 bronze badges

Comments

Santiago · Accepted Answer · 2016-01-20 19:38:10Z

Having them in a separate list like that, makes it really hard to identify which productCode goes with which productName.

I would suggest to build a dto to handle this. Something like:

public class Products
{
public string productCodeList {get; set;}
public string productNameList {get; set;}
public string productPriceList {get; set;}
public int competitorList {get; set;}
public DateTime dateCreatedList {get; set;}
}

than you can use:

List<Products> products = new List<Products>();

Alternatively you could just build the whole DataTable in your code and use SqlBulckCopy to insert it into your database.

DatTable

Quick Example of Datatables

SqlBulkCopy

Collectives™ on Stack Overflow

Insert lists of different types with foreach loop into a SQL Server database

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related