C # MSSQL alter table, then change the values

Question

Although everything works fine in SQL Server Management Studio, in C # it just doesn't work:

DECLARE @PeriodID AS bigint;
SELECT TOP 1 @PeriodID = PeriodID FROM Periods ORDER BY PeriodID DESC;
IF NOT EXISTS(SELECT * FROM information_schema.columns WHERE COLUMN_NAME = N'PeriodID' AND TABLE_NAME = N'MobilePlans')
BEGIN
    BEGIN
    ALTER TABLE MobilePlans ADD PeriodId bigint NULL
    END
    BEGIN
    UPDATE MobilePlans SET PeriodID = @PeriodID
    END
    BEGIN
    ALTER TABLE MobilePlans ALTER COLUMN PeriodID bigint NOT NULL
    END
END

In C #, he continues to tell me, Invalid column name 'PeriodID'.and after spending a couple of hours of searching, I thought I would ask here.

While searching, I came across http://bytes.com/topic/c-sharp/answers/258520-problem-sqlcommand-t-sql-transaction , but I could not translate my conditional query to this.

Why can't C # work just like a management studio?

Is there any other way to do what the query does that works in C #? I need to run this on 400 databases, so I really like the script to do this for me.

Thanks in advance!

The SQL server version is 2008. The manager version is 2008 (10.0.2531). Version .NET Framework 2.0.

Answer 1

" " PeriodID ", Management Studio, PeriodID.

:

create table Periods (
    PeriodID bigint not null
)
go
insert into Periods(PeriodID) select 1
go
create table MobilePLans (
    BLah int not null
)
go
insert into MobilePLans(BLah) select 2
go
DECLARE @PeriodID AS bigint;
SELECT TOP 1 @PeriodID = PeriodID FROM Periods ORDER BY PeriodID DESC;
IF NOT EXISTS(SELECT * FROM information_schema.columns WHERE COLUMN_NAME = N'PeriodID' AND TABLE_NAME = N'MobilePlans')
BEGIN
    BEGIN
    ALTER TABLE MobilePlans ADD PeriodId bigint NULL
    END
    BEGIN
    UPDATE MobilePlans SET PeriodID = @PeriodID
    END
    BEGIN
    ALTER TABLE MobilePlans ALTER COLUMN PeriodID bigint NOT NULL
    END
END

: SQL Server . , UPDATE . , .

Exec:

EXEC('UPDATE MobilePlans SET PeriodID = ' + @PeriodID)

, .

Answer 2

, #.

PeriodeID, PeriodID.

Answer 3

:

1 - script ?

2 - Invalid column name "PeriodeID", I do not see the column named "PeriodeId", but "PeriodId", is this a typo?

3 - Can you try the same block without the main BEGIN / END block?

Answer 4

Is server sorting case insensitive ? Since you add Period Id and then update Period ID