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

Chloromycetin

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.

Shoran

No particular issues, I just wanted to avoid going down the wrong path. – Chloromycetin

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.

Hereupon

That's a great tip about InfoMessage, thanks! Too bad I'm using System.Data.Common classes. – Chloromycetin

Answer 3

You need to understand ADO.NET and how the objects work, primarily

1. Connection
2. Command
3. Transaction
4. DataReader
5. Dataset
6. DataDapter

The Command object can take any SQL (ANSI SQL) compliant query. If you are going to have transactions, then I suggest you handle transactions

1. Via ADO.NET Transaction...here is some reading
2. Call a stored procedure instead, from the command object, instead of passing it as part of the SQL.

Ryley