How can I insert multiple rows into SQL Server using ADO.NET?

Question

I want to make different entries for this same query but with hours and dates I ran into an error:

Parameters must be unique

Is there any way around it?

List<int> hoursList = new List<int>{1,2,3,4,5,6,7};

string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;

using (var con = new SqlConnection(connectionString))
{
    var query = @"INSERT INTO EmployeeTable (EmployeeID, ProjectID, CategoryID, SubCategoryID, Location, Date, Hours)
                  VALUES (@EmployeeID, @ProjectID, @CategoryID, @SubCategoryID, @Location, @Date, @Hours,)";

    using(var cmd = new SqlCommand(query,con))
    {
        cmd.Parameters.AddWithValue("@EmployeeID",obj.EmployeeID);
        cmd.Parameters.AddWithValue("@ProjectID", obj.ProjectID);
        cmd.Parameters.AddWithValue("@CategoryID", obj.CategoryID);
        cmd.Parameters.AddWithValue("@SubCategoryID", obj.SubCategoryID);
        cmd.Parameters.AddWithValue("@Location", obj.Location);

        for(int j = 0; j < hoursList.Count; j++)
        {
            cmd.Parameters.AddWithValue("@Hours", hoursList[j]);
            cmd.Parameters.AddWithValue("@Date", DateTime.Now.AddDays(j).ToString("yyyy/MM/dd"));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

marc_s · Accepted Answer · 2020-05-10 11:41:20Z

You cannot call the .AddParameter inside your loop - that'll keep trying to add the same parameter (same name) over and over again and that causes the problems you're seeing.

Put the declaration of the parameter outside the loop - and inside the loop just se the values - like this:

// define the parameters **ONCE**, outside the loop
cmd.Parameters.Add("@Hours", SqlDbType.Int);
cmd.Parameters.Add("@Date", SqlDbType.DateTime);

for (int j = 0; j < hoursList.Count; j++)
{
    // inside the loop, just set the **values** - not define the same
    // parameters over and over again .....
    cmd.Parameters["@Hours"].Value = hoursList[j];
    cmd.Parameters["@Date"].Value = DateTime.Now.AddDays(j);

    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
}

Also - since @Date quite clearly is a date - you should treat it as such and pass it into the query as a DateTime value - don't convert everything to string without really needing to!!

And overall: this will create multiple rows - but most of the columns are the same over and over again. This smells like it's a bad database design - I would check if the date and hours shouldn't be separated out into their own tables, so that you'd have one entry in the EmployeeTable, and a second table which holds 0-n entries for that employee, with just date and hours.

Collectives™ on Stack Overflow

How can I insert multiple rows into SQL Server using ADO.NET?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related