in C#,may I excute a query with data in sql server db and a DataTable in c#?

Question

Hi,

I'm going to excute a query like this: the problem is ,now for table "AAASalesReason", I want use a DataTable which is in memory in C#. Is it possible?

Thank you

MERGE INTO SalesReason AS tgt  
USING (
SELECT    [Name]
      ,[ReasonType]
  FROM AAASalesReason
)  
       as src (Name, ReasonType)  
ON tgt.Name = src.Name  
WHEN MATCHED and tgt.[ReasonType]!=src.ReasonType
THEN  
UPDATE SET ReasonType = src.ReasonType  
WHEN NOT MATCHED BY TARGET THEN  
INSERT (Name, ReasonType) VALUES (Name, ReasonType)  
WHEN NOT MATCHED BY SOURCE THEN
delete ;

Answer 1

I want use a DataTable which is in memory in C#. Is it possible?

That's a DML command/MERGE, which update data without returning a resultset; so what should be "in memory"?

Answer 2

No, you cannot. A DataTable in .NET and a table in an SQL Server database are living in two different universes, and there is no way they can interact directly.

However, you can just as Viorel suggests: pass the DataTable to a stored procedure or just a parameterised statement as a table-valued parameter. And this is in fact very easy to do.

Although, things can be even easier. Given the thread you had earlier this week, I can guess where that DataTable is coming from. But if you are simply filling that DataTable with data from the source server, you are taking up space in client memory without any good reason to. Instead you could read the source table with an SqlDataReader and pass the reader directly to the TVP, so it is being stream directly to the target server.

I have a primer on using table-valued parameters in .NET here: https://www.sommarskog.se/arrays-in-sql-2008.html There is no example of streaming a Reader object to a TVP, but there is another streaming example. However, I do I have some sample code where I stream a reader here: https://www.sommarskog.se/share_data.html#CLR.

I should hasten to add that if you are actually doing some processing in your DataTable, the DataTable may make sense, and you should keep it.