CODEWITHSUNDEEP
c#sqlsql-serverwinforms
Shlomi.K
Shlomi.K

Reputation: 71

SqlCommand.ExecuteNonQuery locks the table

I am using a C# winform application and I have this code using SQl server:

SqlCommand comm;
comm.CommandText = "alter table anyTable add someColumn int null";
comm.ExecuteNonQuery();

when I debug this code section, right after the comm.ExecuteNonQuery();, I want to go to SQL server management and do a "select * " on the relevant table to check on the changes.

Problem is that I can't touch this table, the SQL server "Executing query" for a long time and it wont do it until the connection will be refreshed, but I can accsess another tables.

This problem does not always exists and I cant figure out why it happens.

Thank you for your help!!!

Upvotes: 0

Views: 1632

Answers (3)

Shlomi.K
Shlomi.K

Reputation: 71

Thank you all for your help.

I had an open transaction in one of my SQL scripts...

Upvotes: 0

Yaugen Vlasau
Yaugen Vlasau

Reputation: 2218

Wouldn't it be better to create an extra table that references initial table by key?

CREATE Original(id int, value1 int PRIMARY KEY(Id))
CREATE Secondary(OriginalId int, ColumnName VARCHAR(50), value INT PRIMARY KEY(OriginalId, ColumnName))

ALTER TABLE Secondary  ADD  CONSTRAINT [FK_Secondary_Original] FOREIGN KEY([OriginalId] )
REFERENCES Original ([Id])
ON DELETE CASCADE

in this case you will be able to extent Original row content runtime row-wise just by simple INSERT into Secondary table

Upvotes: 0

tucaz
tucaz

Reputation: 6684

The target table will be locked until the ALTER TABLE statament is fully completed. The database needs to prevent access to that table in order to maintain integrity.

Upvotes: 2

Related Questions