What are the limitations of T-SQL that can be executed by a System.Data.SqlClient.SqlCommand object?

Question

I have some Transact-SQL that lloks like this, can it be executed through a SqlCommand object, or do I need to start learning Sql Management Objects?

BEGIN TRANSACTION
BEGIN TRY

    IF NOT EXISTS
    (
        SELECT * 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_CATALOG = (SELECT DB_NAME())
        AND TABLE_NAME = 'SchemaVersion'
    )
        BEGIN
            Print 'Migrating up...'

            CREATE TABLE SchemaVersion (
                Id INT IDENTITY(1,1) NOT NULL,
                Version INT NOT NULL,
                CONSTRAINT PK_SchemaVersion PRIMARY KEY CLUSTERED (
                    Id ASC
                )
            )

            INSERT INTO SchemaVersion (Version) VALUES(1)

            PRINT 'Migrated from 0 to 1'
        END
    ELSE IF (SELECT Version FROM SchemaVersion) = 1
        BEGIN
            Print 'Migrating down...'

            DROP TABLE Dia_SchemaVersion

            PRINT 'Migrated from 1 to 0'
        END
     ELSE
        PRINT 'Not migrating...'

    COMMIT TRANSACTION;

END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

Answer 1

Yes, this can be executed by SqlCommand - the easiest way would be to put this in a stored procedure and execute that.

What issues are you having?

As for learning SSMS - if you develop on SQL Server, that's not a bad idea.

Answer 2

Just wrap that in a Stored Procedure and call it using SqlCommand's .ExecuteNonQuery() method. You can "listen" to the print messages from your .Net code by handling the SqlConnection's InfoMessage event. It also helps to set the connection's FireInfoMessageEventOnUserErrors property to true.