Dynamically add data to the database multiple times on button click

Question

I'm able to add two different rows at the same time into the database with the Date value changing. The code is redundant and doesn't look good. Not sure how I can make it dynamic.

SqlCommand cmd = new SqlCommand("insert into Incomings (AspNetUsersId,IncType,IncDate,IncCost,IncFrequency) values (@AspNetUsersId, @IncType, @IncDate, @IncCost, @IncFrequency)");
SqlCommand cmd2 = new SqlCommand("insert into Incomings (AspNetUsersId,IncType,IncDate,IncCost,IncFrequency) values (@AspNetUsersId, @IncType, @IncDate, @IncCost, @IncFrequency)");

if (frequencyIncoming.Text == "Weekly")
    {
       DateTime newDate = DateTime.ParseExact(lblCalendar.Text, "dd/MM/yyyy", null);


        cmd.Parameters.AddWithValue("@AspNetUsersId", userId);
        cmd.Parameters.AddWithValue("@IncType", typeIncoming.Text);
        cmd.Parameters.AddWithValue("@IncDate", lblCalendar.Text);
        cmd.Parameters.AddWithValue("@IncCost", costIncoming.Text);
        cmd.Parameters.AddWithValue("@IncFrequency", frequencyIncoming.Text);

        cmd2.Parameters.AddWithValue("@AspNetUsersId", userId);
        cmd2.Parameters.AddWithValue("@IncType", typeIncoming.Text);
        cmd2.Parameters.AddWithValue("@IncDate", newDate.AddDays(7).ToString(@"dd\/MM\/yyyy"));
        cmd2.Parameters.AddWithValue("@IncCost", costIncoming.Text);
        cmd2.Parameters.AddWithValue("@IncFrequency", frequencyIncoming.Text);
    }

loadDatabase(cmd);
loadDatabase(cmd2);

Response.Redirect(Request.Url.AbsoluteUri);

At the moment it works although the code isn't great and is redundant. It's a start. The only problem is if for e.g. I select the Daily Frequency and want to add it for 14 days, I would have to do 14 different select statements which is horrendous practice. Not sure how to tidy this up. Thanks in advance

Why don't you use a loop? Take days in a variable and run a for loop to insert the data. — unlimit
– unlimit, Commented Mar 12, 2016 at 19:23

Adnan Umer · Accepted Answer · 2016-03-12 19:57:35Z

1

You can simple use a for loop to iterate for 14 times and on each cycle create new sql cmd and execute that. Like this..

var connection = new SqlConnection("<your connection string here>");
connection.Open();
// do what ever else you want to do

DateTime newDate = DateTime.ParseExact(lblCalendar.Text, "dd/MM/yyyy", null);


try {
    for (var i = 0; i < 14; i++) {
         SqlCommand cmd = new SqlCommand("insert into Incomings (AspNetUsersId,IncType,IncDate,IncCost,IncFrequency) values (@AspNetUsersId, @IncType, @IncDate, @IncCost, @IncFrequency)", connection);

         cmd.Parameters.AddWithValue("@AspNetUsersId", userId);
         cmd.Parameters.AddWithValue("@IncType", typeIncoming.Text);
         cmd.Parameters.AddWithValue("@IncDate", lblCalendar.Text);
         cmd.Parameters.AddWithValue("@IncCost", newDate.AddDays(i).ToString(@"dd/MM/yyyy"));
         cmd.Parameters.AddWithValue("@IncFrequency", frequencyIncoming.Text);

         cmd.ExecuteNonQuery();
    }
} finally {
    connection.Close();
}

edited Mar 12, 2016 at 19:57

answered Mar 12, 2016 at 19:26

Adnan Umer

3,6892 gold badges20 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Stuart Over a year ago

thanks for this. I tried this and the data isn't going into the database at all. I put a break point at it and it's running through the code 7 times and it's hitting the loadDatabase each time although it isn't entering anything in

Adnan Umer Over a year ago

@Stuart I haven't modified the SQL queries that was actually in question. I guess issue is some where else in the code. Anyway is any exception is thrown ?

Stuart Over a year ago

Nope no error at all. It will add to the database when I try to add it once but whenever I fire it in a loop here's no errors it runs and doesn't add

Adnan Umer Over a year ago

Try to open connection once and execute all commands using same connection. I suppose that you are opening a new connection each time in loadDatabase. Further loadDatabase name is confusing. is that really calling ExecuteNonQuery() ?

Adnan Umer Over a year ago

@Stuart I've added the connection open/close logic and also fixed date format. Can you please try now ?

|

Collectives™ on Stack Overflow

Dynamically add data to the database multiple times on button click

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related