CODEWITHSUNDEEP
c#sql-servert-sqlado.net
Heki
Heki

Reputation: 976

C# MSSQL alter table then modify values

While the following work fine in SQL Server Management Studio, it just won't work in C#:

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#, it keeps telling me Invalid column name 'PeriodID'. and after spending a couple of hours searching, I thought I'd ask here.

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

Why can't C# do the same as the Management studio?

Is there another way to do what the query does, that works in C#? I need to perform this on 400 databases, so I'd really like a script to do it for me.

Thanks in advance!

SQL server version is 2008. Manager version is 2008 (10.0.2531). .NET framework version is 2.0.

Upvotes: 3

Views: 1899

Answers (4)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

I get "invalid column name 'PeriodID'" running it in Management Studio, if the table doesn't already have the PeriodID column.

Repro:

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

The reason is simple - SQL Server tries to compile each batch completely. If the column already exists, then the UPDATE statement can be compiled. If not, you get the error.

If you put the update inside an Exec:

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

Then it will be compiled at the point that the column does exist.

Upvotes: 3

Pavel Urbančík
Pavel Urbančík

Reputation: 1496

Is the server collation Case Insensitive ? Because you're adding PeriodId and then updating PeriodID

Upvotes: 1

msuhash
msuhash

Reputation: 266

Clarification:

1 - You would like to push this script it against many databases?

2 - Invalid column name 'PeriodeID', I cannot see a column called "PeriodeId", but "PeriodId", is this a typo?

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

Upvotes: 2

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

I guess you have a spelling mistake in your c# code.

The error is saying PeriodeID whereas your column name is PeriodID.

Upvotes: 3

Related Questions