Sending DataTable to a stored procedure from C#

Question

Need some help with DatagridView & DataTable.

• Basically I have a DatagridView which OnLoad populates with the data from a table in SQL Server
• When I click a button on UI, this DataGridView adds a new column to the front of grid "Update" which is a checkbox column
• Now when the user ticks all those rows which needs updating and clicks Update...
• I want to update all the rows which are ticked (for example: I wish to set the owner of these rows from Person A to Person B)
• I've looked at DataTable but I'm confused

My logic is to add all the selected columns to a DataTable and send this to a stored procedure in SQL Server which would update the values.

If I'm not mistaken, I shall be sending a DataTable with just an ID column followed by From & To (owners) to the stored procedure.

Please guide me if I'm wrong, any help would be immensely appreciated.

Answer 1

private DataTable getDataGridID()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("ID");
    foreach (DataGridViewRow row in dgTeamDashboard.Rows)
    {
        if (Convert.ToBoolean(row.Cells["Update"].Value) == true)
        dt.Rows.Add(row.Cells["ID"].Value);
    }
    return dt;
}

I've now progressed upto this point where I have a DataTable with all those ID's whose update column is ticked.

I'm hopeful, I'm heading in the right direction. Comment if I'm not

Further Update:

I've now create a stored procedure which accepts UserDefinedTableType and a destinationOwnerID as parameter and updates the actual table with the supplied OwnerID for all those leads whose ID matches the records from DataTable.

Create Procedure [activity].[udpUpdateActivityLead]
@ActivityLeadTable  ActivityLeadType READONLY,
@OwnerTo            int
AS
BEGIN
  UPDATE [activity].[tblActivity] 
  set [activity].[tblActivity].[IDOwner]= @OwnerTo
  from @ActivityLeadTable
  where [activity].[tblActivity].[ID]=[@ActivityLeadTable].[ID];

  END

Finally I got this function in my UI which works like a GEM. Happy ending...I can go to sleep now...

public void updateActivityLead()
        {
            SqlConnection con = new SqlConnection(OpSupLib.MyConnectionString);
            SqlCommand cmd = new SqlCommand();
            if (con.State == System.Data.ConnectionState.Closed)
                con.Open();

            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "[activity].[udpUpdateActivityLead]";

            SqlParameter p1 = new SqlParameter();
            p1.ParameterName = "@ActivityLeadTable";
            p1.Value = getDataGridID();
            cmd.Parameters.Add(p1);

            SqlParameter p2 = new SqlParameter();
            p2.ParameterName = "@OwnerTo";
            p2.Value = ((ComboBoxItem)cmbUpdateTo.SelectedItem).HiddenValue;
            cmd.Parameters.Add(p2);

            cmd.Connection = con;
            cmd.ExecuteNonQuery();
            if (con.State == System.Data.ConnectionState.Open)
                con.Close();
        }