CODEWITHSUNDEEP
c#sql-serverdatagridviewdatatable
Shallo
Shallo

Reputation: 53

Sending DataTable to a stored procedure from C#

Need some help with DatagridView & DataTable.

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.

Upvotes: 0

Views: 1569

Answers (2)

netDevMark
netDevMark

Reputation: 11

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.

Upvotes: -1

Shallo
Shallo

Reputation: 53

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

Upvotes: 1

Related Questions