I want to add multiple values under a specific row for a user. It would look similar to this:

So far, I am having to add in these values individually and manually, is there a way to add all departments under on user in a single SQL statement?

Update Here is a snippet of code I have so far. With this, it creates a comma separated list for departments, but I would rather just have the user listed multiple times and a different department assigned to each of those instances.

 department = ","   department   ",";
            department = department.Replace(",,", ",");
            department = department.Replace(",,", ",");
            department = department.Replace(",,", ",");
            department = department.Replace(",,", ",");
            strsql = "UPDATE [TV_App].[dbo].[users] "  
                "SET "  
                 "[u_FirstName] = '"   firstname   "' "  
                    ",[u_LastName] = '"   lastname   "' "  
                    ",[u_Admin] = "   admrights  
                    ",[u_Departments] = '"   department   "'"  
                    ",[u_LastEditedOn] = '"   DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")   "'"  
                    ",[u_LastEditedBy] = '"   username   "' "  
                "WHERE [u_ADUN] = '"   username   "' "  
                "IF @@ROWCOUNT = 0 "  
                "INSERT INTO [TV_App].[dbo].[users] "  
                "( "  
                   "[u_ADUN] "   ",[u_FirstName] "   ",[u_LastName] "  
                      ",[u_Admin] "   ",[u_Departments] "  
                      ",[u_CreatedOn] "   ",[u_CreatedBy] "  
                      ",[u_LastEditedOn] "   ",[u_LastEditedBy] "  
                      ",[u_LastLoginOn] "   ",[u_LastLoginFrom] "  
                      ",[u_FirstLoginOn] "   ",[u_FirstLoginFrom] "  
                      ",[u_UsageCount] "  
                 ") VALUES( "  
                        "'"   username   "', "   "'"   firstname   "', "   "'"   lastname   "', "  
                             admrights   ", '"   department   "', "  
                        "'"   DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")   "', "   "'"   Environment.UserName.ToLower()   "', "  
                        "'"   DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")   "', "   "'"   Environment.UserName.ToLower()   "', "  
                        "'"   DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")   "', 'newuser', "  
                        "'"   DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")   "', 'newuser', "  
                        "0"  
                  ") ";
            SqlCommand cmd = new SqlCommand(strsql, conn);
            cmd.Connection.Open();
            cmd.ExecuteReader();
            cmd.Connection.Close();
            return;

Additional information: I am adding these items from a checked listbox.

CodePudding user response：

SQL Server allows you to use a Table-Value Constructor, like this:

INSERT INTO [table]
(User, Departments)
VALUES 
('John', 'Deli'),
('John', 'Frozen Goods'),
('John', 'Bakery')

Given the C# tag (and now code after the edit), it seems you also want to do this from client code. There are a few options here. For example, you can use the SqlLBulkCopy class, or you can use a Table-Value parameter. And you can also, of course, execute multiple insert queries in a loop, where the only thing that changes on each iteration is the value of the parameters (for a few rows, this isn't as bad as it seems).

Yikes. Now I can see the code it is apparent the issues run far deeper. I'll ignore the UPSERT pattern for now, but I need to show you better patterns for how to handle the rest of this:

strsql = @"
UPDATE [TV_App].[dbo].[users] 
   SET [u_FirstName] = @firstname, [u_LastName] = @lastname,
       [u_Admin] = @admrights, [u_Departements] = @department,
       [u_LastEditedOn] = current_timestamp,
       [u_LastEditedBy] = @username
WHERE [u_ADUN] = @username;
IF @@ROWCOUNT = 0
INSERT INTO [TV_App].[dbo].[users]
(
    [u_ADUN], [u_FirstName], [u_LastName], [u_Admin], 
    [u_Departments], [u_CreatedOn], [u_CreatedBy],
    [u_LastEditedOn], [u_LastEditedBy], [u_LastLoginOn],
    [u_LastLoginFrom], [u_FirstLoginOn], [u_FirstLoginFrom],
    [u_UsageCount]
) VALUES (
    @username, @firstname, @lastname, @admrights,
    @department, current_timestamp, @myusername,
    current_timestamp, @myusername, current_timestamp,
    'newuser', @curTime, 'newuser', 0
);";


// do NOT try to re-use the same connection object throughout your app!
using var conn = new SqlConnection("connection string here");
using var cmd = new SqlCommand(strsql, conn);

//use actual column types and lengths from the database for each of these parameters
cmd.Parameters.Add("@firstname", SqlDbType.NVarChar, 30).Value = firstname;
cmd.Parameters.Add("@lastname", SqlDbType.NVarChar, 40).Value = lastname;
cmd.Parameters.Add("@admRights", SqlDbType.Char, 5).Value = admrights; //looks more like a flag/code than arbitrary string
cmd.Parameters.Add("@department", SqlDbType.NVarChar, 40).Value = department;
cmd.Parameters.Add("@username", SqlDbType.NVarChar, 60).Value = username;
cmd.Parameters.Add("@myusername", SqlDbType.NVarChar, 60).Value = Environment.UserName.ToLower();
//this could also have used current_timestamp, but I wanted to demonstrate a DateTime parameter.
// I especially wanted to demonstrate you NEVER need to convert to a string value.
// And, in fact, even the string values you were using were the WRONG FORMAT for SQL.
cmd.Parameters.Add("@curTime", SqlDbType.DateTime).Value = DateTime.Now;


conn.Open();
cmd.ExecuteNonQuery(); //ExecuteReader? for an UPDATE/INSERT? No.
// No need to close the connection. The `using` directive takes care of it.

This won't fix the whole problem. It's still using the same department variable from above. Unfortunately, we don't see enough about how that value is built to correct that part of the issue. Additionally, while I commend you wanting to separate out the individual department values (csv values in a single column are really bad), this probably belongs with a separate table (called a junction or intersection table) to connect these together, rather than duplicating the entire users record.

CodePudding user response：

you can use multiple list of value with sql statement

INSERT INTO `s`(`username`, `role`) VALUES ('jone','Deli'),('jone','Frozen Goods') ,('jone','Bakery');