Using Multiple T-SQL Statements in ADO.NET

Question

Scripting in management studio with T-SQL works very well, but I am having a question about ADO.NET.

I am able to code a simple select statement in ado.net.

How to use the set statement in ado.net ?

e.g.

SET @polygon = (SELECT GEOM FROM Polygons WHERE NAME = 'Area1');
SELECT NAME FROM Points WHERE @polygon.STDistance(GEOM) <= .4;

Answer 1

You can include multiple commands in a single ADO.NET SqlCommand simply by separating them with semicolons.

However, I would recommend using a stored procedure for this kind of thing instead.

Answer 2

If you put that in one big sql string that you assign to an SqlCommand object's command text and run in one function call, it should run fine: just like it would in management studio.

If you try to run it in two seperate SqlCommand objects or run over SqlCommand function calls, the 2nd command won't know anything about the first anymore.