none
How to get columm names in a SQL table using C# ? RRS feed

  • Question


  • I need to get the name from the first columm in a table.

    I tried to make a method to update a table where there is a flag of used. For the method I enter with the table name and the code of the row. For example, to update the Customer's table we need to enter with the tables's name and the customer's code and then the method will update the row flag.

    This is the main part of the method. Take a look at "WHERE CustomerCode = @Code". What's the problem ? The field name changes in each table. If there is another table called CustomersOders the field name will be OrderCode. I can't use relationship between these tables because the method must be a general method to use in any situation.

    public void UpdateRow (string TableName, int RowCode)

    {

      connectionSQL.Open();
      string queryUpdate = "UPDATE TOP (1) " + tablesName + " SET Used = @Used WHERE CustomerCode = @Code ";
       SqlCommand cmdUpdate = new SqlCommand(queryUpdate, connectionSQL);
       SqlParameter sqlParUsed= new SqlParameter("@Used", SqlDbType.Bit);
       sqlParUsed.Value = true;
       cmdUpdate.Parameters.Add(sqlParUsed);


    }






    Wednesday, March 12, 2008 2:17 PM

Answers

  • Do you want to get the table's primary key column or columns. First column in table doesn't mean that column is primary key column. For this kind of information you should use INFORMATION_SCHEMA views in order to find not only primary key columns, but also their type, in order to be able to create dinamic Update condition. Here is one query that will get all primary key columns for provided table name and their type:

    Code Snippet

    SELECT Col.COLUMN_NAME, Col.DATA_TYPE

    FROM INFORMATION_SCHEMA.COLUMNS AS Col

           LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS Usg ON Col.TABLE_NAME = Usg.TABLE_NAME AND Col.COLUMN_NAME = Usg.COLUMN_NAME

           LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS Con ON Usg.CONSTRAINT_NAME = Con.CONSTRAINT_NAME

    WHERE Col.TABLE_NAME = 'YourTableName' AND Con.Constraint_TYPE = 'PRIMARY KEY'

     

    Based on this query result you will be able to create dinamic update query and decide did you need to surround the value for the key column(s) with single quotes or not.

    Wednesday, March 12, 2008 3:42 PM

All replies

  • public void UpdateRow (string TableName, int RowCode, string RowName)
    {

      connectionSQL.Open();
      string queryUpdate = "UPDATE TOP (1) " + tablesName + " SET Used = @Used WHERE " + RowName + " = @Code ";
       SqlCommand cmdUpdate = new SqlCommand(queryUpdate, connectionSQL);
       SqlParameter sqlParUsed= new SqlParameter("@Used", SqlDbType.Bit);
       sqlParUsed.Value = true;
       cmdUpdate.Parameters.Add(sqlParUsed);


    }

    Wednesday, March 12, 2008 2:38 PM
  • Do you want to get the table's primary key column or columns. First column in table doesn't mean that column is primary key column. For this kind of information you should use INFORMATION_SCHEMA views in order to find not only primary key columns, but also their type, in order to be able to create dinamic Update condition. Here is one query that will get all primary key columns for provided table name and their type:

    Code Snippet

    SELECT Col.COLUMN_NAME, Col.DATA_TYPE

    FROM INFORMATION_SCHEMA.COLUMNS AS Col

           LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS Usg ON Col.TABLE_NAME = Usg.TABLE_NAME AND Col.COLUMN_NAME = Usg.COLUMN_NAME

           LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS Con ON Usg.CONSTRAINT_NAME = Con.CONSTRAINT_NAME

    WHERE Col.TABLE_NAME = 'YourTableName' AND Con.Constraint_TYPE = 'PRIMARY KEY'

     

    Based on this query result you will be able to create dinamic update query and decide did you need to surround the value for the key column(s) with single quotes or not.

    Wednesday, March 12, 2008 3:42 PM