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.

Duplicate stackoverflow.com/questions/9410018/…

nobody
– nobody

2016-02-20 18:57:43 +00:00
Commented Feb 20, 2016 at 18:57 — nobody
– nobody, Commented Feb 20, 2016 at 18:57

mason · Accepted Answer · 2016-02-21 01:26:29Z

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

edited Feb 21, 2016 at 1:26

mason

32.8k12 gold badges83 silver badges127 bronze badges

answered Feb 20, 2016 at 21:53

Shallo

531 silver badge13 bronze badges

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

2 Comments

Solomon Rutzky Over a year ago

This code is rather dangerous in that it doesn't properly clean up its external resources. Both SqlConnection and SqlCommand objects are "disposable" and hence either need to be wrapped in the using() construct, or in a try/catch/finally where the Dispose() method is called on both in the finally block. Also, you can change the declaration of cmd to be SqlCommand cmd = con.CreateCommand(); and then remove cmd.Connection = con; :-).

Shallo Over a year ago

@srutzky I've amended the code accordingly. Many Thanks

netDevMark · Accepted Answer · 2023-05-13 22:17:38Z

Your logic is correct, you can send the IDs and new owners of the selected rows to a stored procedure using a DataTable. Here is a step-by-step guide:

Add a CheckBox column to your DataGridView. This will allow the user to select rows that they want to update. You can add this column when you populate the DataGridView, or later on a button click event. Here's an example of how you might do this:

DataGridViewCheckBoxColumn checkBoxColumn = new DataGridViewCheckBoxColumn();
checkBoxColumn.HeaderText = "Update";
checkBoxColumn.Name = "checkBoxColumn";
dataGridView1.Columns.Insert(0, checkBoxColumn);

Create a DataTable and add the selected rows to it. When the user clicks the "Update" button, you can iterate over the rows of the DataGridView, check if the checkbox is checked, and if so, add the ID and new owner to the DataTable. Here's an example:

DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Owner", typeof(string));

foreach (DataGridViewRow row in dataGridView1.Rows)
{
    if (Convert.ToBoolean(row.Cells["checkBoxColumn"].Value))
    {
        dt.Rows.Add(row.Cells["ID"].Value, "New Owner");
    }
}

Replace "ID" with the actual name of your ID column, and replace "New Owner" with the new owner that you want to set for the selected rows.

Pass the DataTable to a stored procedure. You can pass the DataTable to a stored procedure in SQL Server using a table-valued parameter, as I described in the previous answer. Here's an example:

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();

    using (SqlCommand cmd = new SqlCommand("UpdateOwner", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter param = cmd.Parameters.AddWithValue("@data", dt);
        param.SqlDbType = SqlDbType.Structured;
        param.TypeName = "dbo.MyTableType";

        cmd.ExecuteNonQuery();
    }
}

In this example, "UpdateOwner" is the name of your stored procedure, and "dbo.MyTableType" is a table type that you've defined in SQL Server with columns that match the structure of your DataTable.

Update the rows in SQL Server. Your stored procedure can use the table-valued parameter to update the rows in your table. Here's an example of how you might do this:

CREATE PROCEDURE UpdateOwner
    @data dbo.MyTableType READONLY
AS
BEGIN
    UPDATE MyTable
    SET Owner = newData.Owner
    FROM MyTable
    JOIN @data newData ON MyTable.ID = newData.ID;
END

In this example, "MyTable" is the name of your table, and "dbo.MyTableType" is the table type that matches the structure of your DataTable. Replace these with your actual table and type names.

Remember to replace the placeholders in the above examples with the actual names from your project.

Collectives™ on Stack Overflow

Sending DataTable to a stored procedure from C#

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related