Update numerous rows at once in MSSQL with an inner join to a DataTable handed in from C#!

In the "Object Explorer" beneath "Programmability" at your database, find "Types" and create a new entry in "User-Defined Table Types" like so:

USE [Whatever]
GO
CREATE TYPE [dbo].[YinYang] AS TABLE(
   [Yin] [INT] NOT NULL,
   [Yang] [INT] NOT NULL,
   PRIMARY KEY CLUSTERED
(
   [Yin] ASC,
   [Yang] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

 
 

Use the type in a stored procedure like this:

ALTER PROCEDURE [dbo].[MarkQuizesAsCompleted]
(
   @Pairs YinYang READONLY
)
AS
UPDATE q
SET q.IsCompleted=1
FROM dbo.Quiz q
INNER JOIN @Pairs p
ON q.InstructorId = p.Yin AND q.StudentId = p.Yang
RETURN @@ERROR
GO

 
 

This will work! On the C# side, you need to make a DataTable in the same shape as the one specified in your new type. An example:

var yinYang = new DataTable();
yinYang.Columns.Add("Yin", typeof(int));
yinYang.Columns.Add("Yang", typeof(int));
 
var foo = yinYang.NewRow();
foo["Yin"] = 1002;
foo["Yang"] = 1;
yinYang.Rows.Add(foo);
 
var bar = yinYang.NewRow();
bar["Yin"] = 2;
bar["Yang"] = 13;
yinYang.Rows.Add(bar);
 
var baz = yinYang.NewRow();
baz["Yin"] = 2;
baz["Yang"] = 22;
yinYang.Rows.Add(baz);
 
var qux = yinYang.NewRow();
qux["Yin"] = 2;
qux["Yang"] = 42;
yinYang.Rows.Add(qux);

 
 

When it comes time to actually call the sproc, do it more or less as suggested here with the exception that this line of code...

sqlCommand.Parameters.Add("@Item", SqlDbType.VarChar).Value = newEntry;

 
 

...needs to be replaced with a different line of code to handle the DataTable. I recommend something like this:

sqlCommand.Parameters.Add("@Pairs", SqlDbType.Structured).Value = yinYang;