Retrieve Number Of Columns In Sql Table - C#

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 variabl

Solution 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;";
Int32count= (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

Solution 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.

SELECTcount(*) 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.

Solution 3:

You have to use a command and retrieve back the scalar variable :

SqlCommandcmd=newSqlCommand(sql, conn);
Int32count= (Int32)cmd.ExecuteScalar();

Solution 4:

stringconnectionString="Data Source=(local);Initial Catalog=Northwind;"
            + "Integrated Security=true";

        // Provide the query string with a parameter placeholder.stringqueryString="SELECT Count(*) from sys.columns";

        // Specify the parameter value.intparamValue=5;

        // Create and open the connection in a using block. This// ensures that all resources will be closed and disposed// when the code exits.
        using (SqlConnectionconnection=newSqlConnection(connectionString))
        {
            // Create the Command and Parameter objects.SqlCommandcommand=newSqlCommand(queryString, connection);

            // Open the connection in a try/catch block. // Create and execute the DataReader, writing the result// set to the console window.try
            {
                connection.Open();
                SqlDataReaderreader= command.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("\t{0}",
                        reader[0]);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadLine();
        }

Solution 5:

use Executescalar() for getting a single element.

 using (SqlConnectioncon=newSqlConnection(ConnectionString)) //for connecting to database
            {
                con.Open();
                try
                {
                    using (SqlCommandgetchild=newSqlCommand("select count(*) from table1 ", con)) //SQL queries
                    {
                        Int32count= (Int32)getchild.ExecuteScalar();
                     }
                }
             }