Inserting rows to SQL Server database with multiple loops

Question

I'm gathering a list of users and their information that I would like to store in a database table. I have this database set up in Microsoft SQL Server Management Studio. The primary key of the database is set to auto-incrementing. I need the loop to end when there are no more users in the list. I have two valid users in the test account however what I currently have only inserts a single user. Would I be better off to use a sqlDataAdapter?

List<User> result = ims.ListUsers(req).ListUsersResult.Users;

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString);

for (int i = 1; i < result.Count(); i++)
{
    foreach (User user in result.Where(x => x.UserName.Contains('@')))
    {
        string sql = @"INSERT INTO UserInfo (UserID, UserName) VALUES (@UserID, @UserName)";

        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            command.Parameters.AddWithValue("@UserID", i);
            command.Parameters.AddWithValue("@UserName", user.UserName);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
    }
}

since PK UserID is auto-increase, why you insert it with value? the second time you run this will generate exception. — urlreader
– urlreader, Commented Dec 7, 2012 at 15:49
I don't understand why you have the for loop. It seems that the foreach is doing everything you need. — Malcolm O'Hare
– Malcolm O'Hare, Commented Dec 7, 2012 at 15:58

Tim Schmelter · Accepted Answer · 2012-12-07 15:57:38Z

3

I don't understand why you need the outer for-loop to create the ID since you should not set the primary key column manually. It will increment automatically. You should also always close a connection when you're finished

Here is the complete code:

string sql = @"INSERT INTO UserInfo (UserName) VALUES (@UserName)";
using(var con = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString))
using(var cmd = new SqlCommand(sql, con))
{
    con.Open();
    foreach(var user in result.Where(x => x.UserName.Contains('@')))
    {
        // you don't need to set the UserID when you've set made it an Identity column
        cmd.Parameters.AddWithValue("@UserName", user.UserName);
        cmd.ExecuteNonQuery();
    }
}

edited Dec 7, 2012 at 15:57

answered Dec 7, 2012 at 15:45

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

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

5 Comments

SergeyS Over a year ago

Your first answer is incorrect, this will not help until there are two loops and he is updating auto-incrementing field.

EFeit Over a year ago

This did the trick for me. I appreciate the help. Just one of those Fridays! Thanks again!

Tim Schmelter Over a year ago

@SergeyS: Yes, you're right. The for loop is just redundant. Edited my answer.

SergeyS Over a year ago

Also remove UserId part from the INSERT construction

EFeit Over a year ago

I did that as well. Thank you.

Barry Kaye · Accepted Answer · 2012-12-07 15:46:37Z

0

I think you need to replace:

for (int i = 1; i < result.Count(); i++)

With:

for (int i = 0; i < result.Count(); i++)

answered Dec 7, 2012 at 15:46

Barry Kaye

7,7696 gold badges46 silver badges65 bronze badges

Comments

Malcolm O'Hare · Accepted Answer · 2012-12-07 15:47:34Z

0

Your problem is that you have 2 loops. You need to remove the first for loop and instead increment a int variable each time you execute through your foreach()

You are adding multiple users with the same UserID

int userID = 1

foreach (User user in result.Where(x => x.UserName.Contains('@')))
{
    // your code

    userID++;
}

answered Dec 7, 2012 at 15:47

Malcolm O'Hare

5,0393 gold badges36 silver badges56 bronze badges

Comments

SergeyS · Accepted Answer · 2012-12-07 15:55:09Z

You do not need to update auto-incrementing field, it will count automatically by SQL server. Try this code:

        List<User> result = ims.ListUsers(req).ListUsersResult.Users;

        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString);

        foreach (User user in result.Where(x => x.UserName.Contains('@')))
        {
            string sql = @"INSERT INTO UserInfo (UserName) VALUES (@UserName)";

            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@UserName", user.UserName);
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
        }

Collectives™ on Stack Overflow

Inserting rows to SQL Server database with multiple loops

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related