CODEWITHSUNDEEP
c#sql-server-2008
shyam
shyam

Reputation: 23

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

I am developing an application in C# in Visual Studio 2008. I connected a SQL Server 2008 database with it.

I want to count the number of columns so that I can loop around them to get the particular data.

I can figure it out columns by going to the database but I am joing 4-5 tables in my programs so I want to know if I can count the columns.

Can anyone help me in this?

Thank you Shyam

Upvotes: 1

Views: 5258

Answers (6)

Manohar Bomma
Manohar Bomma

Reputation: 321

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);
}

Upvotes: 0

Ankit Rathod
Ankit Rathod

Reputation: 9

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.

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112772

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("---------------");
    }
}

Upvotes: 0

xlecoustillier
xlecoustillier

Reputation: 16361

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.

Upvotes: 1

McHaroni
McHaroni

Reputation: 606

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

Upvotes: 0

Peter Henell
Peter Henell

Reputation: 2466

select count(*) from INFORMATION_SCHEMA.columns where TABLE_NAME = 'YourTableName'

Upvotes: 4

Related Questions