CODEWITHSUNDEEP
c#asp.netlinq-to-sql
jmasterx
jmasterx

Reputation: 54113

Using ExecuteCommand in LINQ

I want to execute the statements on my DB:

/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; DELETE ?'

/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

I have my LINQ datacontext:

   public static bool ClearDatabase()
      {
          GetContext().ExecuteCommand("");
      }

The part that was not clear in MSDN docs is... should I be running each statement as a separate command or make a big long string to execute?

Should I also be keeping the exec statements?

Also, if anyone knows a better way to clear a database with LINQ, let me know.

Thanks

Upvotes: 3

Views: 2265

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062770

Execute simply executes it; the inside is entirely opaque (apart from {0} etc, which is used to denote parameters - i.e. {0} becomes something like @_p0, with values supplied from the params array). As such, as long as your TSQL is valid (and doesn't include GO etc) then it should work fine. It certainly doesn't care that the TSQL is long. But: try it.

However, personally I'd say that running this from a data-context is risky, as the data-context may have cached state which could really confuse things. Personally I'd run it from ADO.NET directly (or via a helper like "dapper"). But I suppose it'll work fine if you dispose+discard (and perhaps re-create) the data-context immediately after doing this.

Upvotes: 6

Related Questions