CODEWITHSUNDEEP
c#sql-server
EFeit
EFeit

Reputation: 2132

Inserting rows to SQL Server database with multiple loops

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();
        }
    }
}

Upvotes: 0

Views: 2429

Answers (4)

Tim Schmelter
Tim Schmelter

Reputation: 460128

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();
    }
}

Upvotes: 3

SergeyS
SergeyS

Reputation: 3553

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();
            }
        }

Upvotes: 0

Malcolm O&#39;Hare
Malcolm O&#39;Hare

Reputation: 4999

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++;
}

Upvotes: 0

Barry Kaye
Barry Kaye

Reputation: 7759

I think you need to replace:

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

With:

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

Upvotes: 0

Related Questions