Adding a comma separated list to SQL Server as individual rows-CodePudding

I am not the best at using SQL yet, but I am trying to get a comma separated list to convert into multiple rows in my database. For example, I am using Winforms and in my textbox I will put "a,b,c"

I want my table to look like this:

but so far I am only achieving :

Here is some sample code for what I am going for:

 private void button2_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < listBox1.Items.Count; i  )
            {
                listBox1.SetSelected(i, true);
            }
            string items = "";
            StringBuilder sb = new StringBuilder();
            foreach (var currentItem in listBox1.SelectedItems)
            {
                sb.Append(currentItem   ",");
                          
            }
            items = sb.ToString();

            textBox1.Text = items;
        }

SQL

string strsql;        
strsql = @"UPDATE [table].[dbo].[Departments]
           SET [dep_Department] = @department                           
           WHERE [dep_Username] = @username;

           IF @@ROWCOUNT = 0
               INSERT INTO [table].[dbo].[Departments] ([dep_Username],[dep_Department]) 
               VALUES (@username, @department);";

SqlConnection conn = new SqlConnection(@"coneectionstring;");
var cmd = new SqlCommand(strsql, conn);  

cmd.Parameters.Add("@department", SqlDbType.NVarChar, 40).Value = department;
cmd.Parameters.Add("@username", SqlDbType.NVarChar, 60).Value = username;          

conn.Open();
cmd.ExecuteNonQuery();

CodePudding user response：

You just need to split the departmentname variable to extract the single departments and then loop over the extracted departments.
But first, if you need to write one record for each department/user pair, then you need to delete all records for that user and then readd them. All this inside a transaction. Of course I assume that departmentname contains all the departments to be added for the specific user.

So we have two sql command text, the first one deletes the records, the second one adds the record. The SqlCommand and its first parameter are built immediately and the command is used to remove the record, then we can change the command text and add the parameter for the department name outside the loop, and inside the loop you can just update the @department parameter with the current value.

try
{
    string clear_recs = @"DELETE FROM [Departments] 
                          WHERE [dep_Username] = @username";

    string strsql = @"INSERT INTO [table].[dbo].[Departments]  
                     ([dep_Username],[dep_Department]) 
                      VALUES (@username, @department);";
    
    using(SqlConnection conn = new SqlConnection(@"coneectionstring;"));
    conn.Open();
    using(SqlTransaction tr = conn.BeginTransaction());
    using(SqlCommand cmd = new SqlCommand(clear_recs, conn, tr));  
    cmd.Parameters.Add("@username", SqlDbType.NVarChar, 60).Value = username;
    cmd.ExecuteNonQuery();
    // Change the commandtext and create the parameter 
    // but do not set the value for now.
    cmd.CommandText = strsql;
    cmd.Parameters.Add("@department", SqlDbType.NVarChar, 40);

    // Split, then loop and update/insert 
    // using the same SqlCommand.
    string[] deps = department.Split(',');
    foreach(string dep in deps)
    {
        cmd.Paramenters["@department"].Value = dep;
        cmd.ExecuteNonQuery();
    }
    // All the write are effectively done here when we commit the transaction
    tr.Commit();
}
catch(Exception ex)
{
    // Log the errors. If we reach the exception handler, the  
    // transaction is automatically rolled back and you can try again.
}