none
Changing SQL Server tables in the run RRS feed

  • Question

  • I'm about to start a project where it would be beneficial for me to create databases and tables from C# code and I know that I can do it, however I want to add or remove columns from the above mentioned tables. I call it on the run but it is not exactly the case. I tried to do it on a different database and a different table using the SQL Server Management Studio and it failed. I wanted to add a column to already existing columns and I got an exception. Deleting columns is no problem. How can I add columns?

    Thanks, - MyCatAlex

    Saturday, September 22, 2018 11:37 PM

Answers

  • this might help.

    https://stackoverflow.com/questions/1351651/how-to-use-c-sharp-to-add-a-column-for-a-table-of-sql-server

    Thanks.

    • Marked as answer by MyCatAlex Sunday, September 23, 2018 1:44 PM
    Sunday, September 23, 2018 12:01 AM
  • Saying "I tried to do it on a different database and a different table using the SQL Server Management Studio and it failed" leaves us to guess why it "failed".

    When looking at if a table exist we can use

    -- this becomes a parameter for your CommandText for your Command object
    DECLARE @TableName NVARCHAR(50) = 'Customers';
    ( SELECT    COUNT(TABLE_NAME)
      FROM      INFORMATION_SCHEMA.TABLES
      WHERE     TABLE_NAME = @TableName
    );

    When looking to see if a column exists. (literals would become parameters)

    SELECT  COUNT(o.name) 
    FROM    sys.columns c
            INNER JOIN sys.objects o ON c.object_id = o.object_id
    WHERE   o.name = 'orders' AND c.name = 'OrderDate'

    Use ExecuteScalar for both and cast to an int, 0 means the table or column does not exists while 1 means it exists.

    Other things to consider, dependencies, if a column is a key then a rule may indicate it can not be removed, similar with records.

    Going back to SSMS, we need an exact error message and if the database is local or remote and who is the owner of the SQL-Server, server and database. 

    In closing also consider SMO which can be installed via NuGet which is an extension of SSMS. I have several MSDN code samples on SMO, might want to check this one out which does not exactly address your issue but does assist with getting more than started with SMO.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by MyCatAlex Sunday, September 23, 2018 1:44 PM
    Sunday, September 23, 2018 3:15 AM
    Moderator

All replies

  • this might help.

    https://stackoverflow.com/questions/1351651/how-to-use-c-sharp-to-add-a-column-for-a-table-of-sql-server

    Thanks.

    • Marked as answer by MyCatAlex Sunday, September 23, 2018 1:44 PM
    Sunday, September 23, 2018 12:01 AM
  • Saying "I tried to do it on a different database and a different table using the SQL Server Management Studio and it failed" leaves us to guess why it "failed".

    When looking at if a table exist we can use

    -- this becomes a parameter for your CommandText for your Command object
    DECLARE @TableName NVARCHAR(50) = 'Customers';
    ( SELECT    COUNT(TABLE_NAME)
      FROM      INFORMATION_SCHEMA.TABLES
      WHERE     TABLE_NAME = @TableName
    );

    When looking to see if a column exists. (literals would become parameters)

    SELECT  COUNT(o.name) 
    FROM    sys.columns c
            INNER JOIN sys.objects o ON c.object_id = o.object_id
    WHERE   o.name = 'orders' AND c.name = 'OrderDate'

    Use ExecuteScalar for both and cast to an int, 0 means the table or column does not exists while 1 means it exists.

    Other things to consider, dependencies, if a column is a key then a rule may indicate it can not be removed, similar with records.

    Going back to SSMS, we need an exact error message and if the database is local or remote and who is the owner of the SQL-Server, server and database. 

    In closing also consider SMO which can be installed via NuGet which is an extension of SSMS. I have several MSDN code samples on SMO, might want to check this one out which does not exactly address your issue but does assist with getting more than started with SMO.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by MyCatAlex Sunday, September 23, 2018 1:44 PM
    Sunday, September 23, 2018 3:15 AM
    Moderator
  • Well, I haven't tried it yea but  marked both answers as answers. I hope they will work, if not I will come back. Thank you very much, especially Karen.

    Sunday, September 23, 2018 1:46 PM