Use only one petition to server instead multiple

Question

I have a list of CheckBoxes:

List<CheckBox> checkBoxes = new List<CheckBox>();

I want to update it via a stored procedure, so I have:

private void btnSave_Click(object sender, EventArgs e)
{
    SQLConnMgr db = new SQLConnMgr();

    foreach (var c in checkBoxes)
    {
        db.ExeSQL($"exec test @CheckBoxName = {c.Name}, @CheckBoxValue = {c.Checked} ");
    }
}

Stored procedure:

CREATE OR ALTER PROCEDURE test 
    -- Add the parameters for the stored procedure here
    @CheckBoxName VARCHAR(255), 
    @CheckBoxValue BIT
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    UPDATE MyTable SET @CheckBoxName = @CheckBoxValue
END

My question is: is there another way to do this? Like sending multiple petitions in the foreach statement instead of only one at a time?

UPDATE

So to be more clear every bool is a column so I need something like:

 DECLARE @CurrentCheckboxName VARCHAR(255) = (SELECT
                                                        [CheckBoxName]
                                                        FROM @CheckBoxList)

               UPDATE [m]
                SET
                    @CurrentCheckboxName = [c].[CheckBoxValue]
                    FROM [RedMarkItems] [m]
                        JOIN @CheckBoxList [c] ON [c].[CheckBoxName] = @CurrentCheckboxName

but how can iterate on each checkboxName in my DECLARE?

maybe you can try concat the SQL statements? Btw what is your SQL language? MySQL? Oracle? or what? — Smankusors, Jan 02 '19 at 18:52
you can use UDT as parameter in stored procedure and send a datable with respect to UDT — Ehsan Sajjad, Jan 02 '19 at 18:55
Technically it works, but from an architecture point of view it is not a good solution because you are gluing your GUI front end into the database! your database should not be dependent on any front end gui element names. I would use logical unique IDs for the checkbox meaning and use that ID in the database, this way the database is not dependent on any GUI element. — Siraf, Jan 02 '19 at 18:57
Did you mean to put `exec test` where you have `exec MyTable`? The example code in its current form won't work unless you also have a `MyTable` stored procedure not shown here... — Lews Therin, Jan 02 '19 at 18:57
I can create UDT and use like: `MyTableType [Helper].[BitIdTableType] READONLY`, but how can I do update using it? @EhsanSajjad — Jonathan, Jan 02 '19 at 18:59
Use it in a table-valued parameter - https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017 — stuartd, Jan 02 '19 at 19:02
I have my table type created as: `CREATE TYPE dbo.MyCheckBoxValues AS TABLE( CheckBoxName VARCHAR(255) NOT NULL, CheckBoxValue BIT NOT NULL )`, then in c# I create datatable like: `DataTable MyTable = new DataTable();` but now, what should be my datatable column and datatable rows to get data in stored procedure? @stuartd — Jonathan, Jan 02 '19 at 19:27
Are you trying to `insert` or `update`? Comment says `insert`, statement is `update`. In one case you want to insert two values, in the other you want to modify one value based on the value of another. (As Mary points out you have no `where` clause.) Or is this a case of inserting if the row doesn't already exist? — HABO, Jan 02 '19 at 21:17
So to be more clear every bool is a column so I need something like: `DECLARE @CurrentCheckboxName VARCHAR(255) = (SELECT CheckBoxName FROM @CheckBoxList) UPDATE m SET @CurrentCheckboxName= c.CheckBoxValue FROM MyTable m JOIN @CheckBoxList c ON c.CheckBoxName= @CurrentCheckboxName` but how can Iterate on each checkboxName in my DECLARE? — Jonathan, Jan 02 '19 at 21:42
So your table is a single row with one column per checkbox: `TodayIsWednesday as Bit, AutoleanBootPumpOn as Bit, OverripePumpkin as Bit, ...`. To have a "variable" column name you'll need to use _dynamic SQL_. [This](https://stackoverflow.com/a/52913847/92546) answer, though it deals with table names rather than column names, ought to help. Aside: IMHO it would make far more sense to have a row per `CheckboxName`/`Checked`. — HABO, Jan 03 '19 at 03:23

Danielle Paquette-Harvey · Answer 1 · 2019-01-03T13:44:34.560

3

Depending on the version of SQL Server you are using, you could use a TABLE parameter type for your stored proc and call it only once.

CREATE TYPE dbo.MyCheckBoxValues AS TABLE(
CheckBoxName VARCHAR(255) NOT NULL,
CheckBoxValue BIT NOT NULL )

Then you modify your stored proc to use the type.

CREATE OR ALTER PROCEDURE test 
-- Add the parameters for the stored procedure here
@CheckBoxList MyCheckBoxValues READONLY
AS
BEGIN
SET NOCOUNT ON;

    -- Insert statements for procedure here
    UPDATE m SET CheckBoxValue=c.CheckBoxValue
    FROM MyTable m
    JOIN @CheckBoxList c ON c.CheckBoxName=m.CheckBoxName
END

You can also use Dynamic SQL in your stored proc. For Each checkboxValues :

DECLARE @Query nvarchar(max); 
SET @Query = 'UPDATE Table SET ' + @CheckboxName + '='+ @CheckBoxValue; 
exec sp_executeSql @Query

Then you only have to get the values in your code. Something like this should do it.

StringBuilder builder = new StringBuilder();

builder.Append("DECLARE @MyCheckboxes MyCheckBoxValues; ");

foreach (Guid id in _equipmentToMerge)
      {
        builder.Append(String.Format("INSERT INTO @MyCheckboxes (CheckBoxName, CheckBoxValue) VALUES ('{0}',{1}); ", name, ischecked));
      }

builder.Append("exec dbo.test @MyCheckboxes ");

edited Jan 03 '19 at 13:44

answered Jan 02 '19 at 19:03

Danielle Paquette-Harvey

1,691
1
16
31

2

You can also squeeze in a `DataTable`: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters#passing ~ Prevents SQL injection – Caramiriel Jan 02 '19 at 19:06
How can I do in Datatable, I first need to create tableas: `DataTable MyTable = new DataTable();`, then what should be their column or their rows? @Caramiriel – Jonathan Jan 02 '19 at 19:17
Yeah something like this, using Danielle's UDT and stored procedure: https://dotnetfiddle.net/APe7n5 - typed it freehand, so there might be some errors still – Caramiriel Jan 02 '19 at 19:30
All right but now, in stored procedure, I don't know `m.CheckboxName` in `JOIN`, because it's a column name and it can be wathever and same for `SET [CheckBoxValue] ` @Caramiriel – Jonathan Jan 02 '19 at 19:52
@Jonathan m.CheckboxName should be the name of your checkbox in your table "MyTable". Since you didn't provide with the actual structure of your table, I guessed that you probably have a column which contains the check box name. (Based on the example you provided) Or I am not sure if I understand it right. You have one column name for each checkbox? – Danielle Paquette-Harvey Jan 02 '19 at 20:47
@Jonathan CheckBoxValue is a BIT so it can be either 0 or 1. – Danielle Paquette-Harvey Jan 02 '19 at 20:55
Yes, I have one column foreach checkBoxValue, so each CheckboxValue hav a checkboxName – Jonathan Jan 02 '19 at 20:59
So to be more clear I need something like: `DECLARE @CurrentCheckboxName VARCHAR(255) = (SELECT CheckBoxName FROM @CheckBoxList) UPDATE m SET @CurrentCheckboxName= c.CheckBoxValue FROM MyTable m JOIN @CheckBoxList c ON c.CheckBoxName= @CurrentCheckboxName` but how can Iterate on each checkboxName in my DECLARE? – Jonathan Jan 02 '19 at 21:38
Why do you need the declare and not work from the `@CheckBoxList` directly? – Caramiriel Jan 03 '19 at 10:22
I edited the answer to put an example of Dynamic SQL. For each values in @CheckBoxList, you could do dynamic SQL in the stored procedure. – Danielle Paquette-Harvey Jan 03 '19 at 13:41

Mary · Answer 2 · 2019-01-02T21:09:52.387

I think there is a problem with your Update statement in your stored procedure. Could you change it thusly?

    Update MyCheckBoxValues SET CheckBoxValue = @CheckBoxValue Where CheckBoxName = @CheckBoxName

I don't know why you want to complicate things. Use a single connection for the loop and I can't imagine that you could have a prohibitive number of check boxes on your form. If it is still too slow, get rid of entity framework or whatever orm you are using and try dealing with the server directly.

Use only one petition to server instead multiple

2 Answers2