Retrieve number of columns in SQL Table - C#

Question

I'm very new to C#. I'm trying to retrieve the number of columns using:

SELECT count(*) FROM sys.columns 

Could you please explain how to use the command and put it into a variable.

Answer 1

To connect to the database you can use the SqlConnection class and then to retrieve the Row Count you can use the Execute Scalar function. An example from MSDN:

cmd.CommandText = "SELECT count(*) FROM sys.columns;";
Int32 count = (Int32) cmd.ExecuteScalar();

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection

Answer 2

You will need to use ExecuteScalar as the others have said. Also, you will need to filter your SELECT on the object_id column to get the columns in a particular table.

SELECT count(*) FROM sys.columns WHERE object_id = OBJECT_ID(N'table_name')

Alternatively, you could do worse than familiarise yourself with the ANSI-standard INFORMATION_SCHEMA views to find the same information in a future-proof, cross-RDBMS way.