CSharp: How do I make a SQL declare/set command persist in a SQLConnection?

I have a class that contains all of my SQL objects, including the methods to make a database connection and execute a query.

From another class, I create an instance of that "database" object and execute a query to set the value for a variable that I want to remain set throughout the remainder of my queries on that connection:

DECLARE @InstitutionId NVARCHAR(MAX);
SET @InstitutionId ='15';

After executing the above, us开发者_JAVA百科ing the same connection, I then execute another query that would make use of that variable, but I get an error returned stating that InstituionId must be set.

So it would appear that once I executed the second query, the results of the first were lost. Is there anyway I can have the above persist, or must I include the Declare and Set commands with each query I execute? When I do this in Microsoft SQL Server Management Studio, I only need to run this once and it sticks for all the queries in my query window which is why I thought (naively perhaps) the results would stick.

Thanks,

Jason

---

Short of storing it in the db, you cant. The statement holds state, not the database connection. As soon as it goes out of scope you have nothing. Imagine a 'go' command at the end the statement, its essentially equivalent.

---

No, the scope of the query will only be valid for the duration of the query. Remember for instance that most SQL conection drivers will reuse connections from a connection pool, so it would be impossible (for the SQL and for your app) to keep track what query comes from what previous context.

Depending on the exact requirements of your app you, I would add InstitutionId as a member/property of your database class. The database class is then responsible for sending the value of that member as a query parameter on each request:

eg.

   command.Parameters.AddWithValue("@InstitutionId",this.InstitutionId);