How to count columns of SQL Server 2008's table using C#?

Something like this ?

SELECT COUNT(*)
FROM sys.columns c 
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'yourTable'

See this page provided wy TaronPro to know how to retrieve the result.

If you are using SQLConnection object to connect to DB, use its GetSchema method to get list of all columns without querying.

    using (SqlConnection connection = new SqlConnection(connectionString))
   {
       // Connect to the database then retrieve the schema information.
       connection.Open();
       DataTable table = connection.GetSchema("Tables");
        ..
        ..
        ..

If you want to know columns for specific owner, table or table type, use restriction within GetSchema method.

    string[] restrictions = new string[4];
    restrictions[1] = "dbo";
    DataTable table = connection.GetSchema("Tables", restrictions);

for more information refer this link.

What I did in a similar situation is that when I executed the query I retrieved all the data into a DataSet.

When I got the DataSet I opened the first table (ds.Tables[0]). Obviously you check first for existance.

When you have the table then its as simple as performing a

dt.Columns.Count;

In summary DS.Tables[0].Columns.Count To find a specific column by name you loop through and find the one that

for (z=0; z < dt.Columns.Count; z++)
{
  // check to see if the column name is the required name passed in.
  if (dt.Columns[z].ColumnName == fieldName)
  {
    // If the column was found then retrieve it 
    //dc = dt.Columns[z];
    // and stop looking the rest of the columns
    requiredColumn = z;
    break;
 }

}

Then to find the data you need I would then loop through the rows of the table and get the field for that column... ie...

string return = dr.Field<string>(requiredColumn);

Probalby not the best way of doing it but it works. Obviously if the data contained in the field is not string then you need to pass the appropriate type...

dr.Field<decimal>(requiredColumn)
dr.Field<int>(requiredColumn) 

etc

Rgds George

The reader itself gives you the number of columns. This is useful when you don't want to know the number rows of a specific table or view but from an ad-hoc query.

You can dump the columns like this

string sql = "SELECT * FROM my query";
SqlCommand cmd = new SqlCommand(sql, connection);
using (SqlDataReader reader = cmd.ExecuteReader()) {
    while (reader.Read()) {
        for (int i = 0; i < reader.FieldCount; i++) {
            Console.WriteLine("{0} = {1}",
                              reader.GetName(i),
                              reader.IsDBNull(i) ? "NULL" : reader.GetValue(i));
        }
        Console.WriteLine("---------------");
    }
}

you can use Microsoft.SqlServer.Management.Smo namespace to get the number of columns in a specified table as follows 1 . add Microsoft.SqlServer.Management.Smo dll in your project and use the namespace Microsoft.SqlServer.Management.Smo 2 . write the follwing code

private int colCount()
{
      Server server=new Server(".\\SQLEXPRESS");
      Database database=Server.Databases["your database name"];
      Table table=database.Tables["your table name"];
      return (table.Columns.Count);
}