Get the number of columns in a SQL table - C #

Question

I am very new to C #. I am trying to get the number of columns using:

SELECT count(*) FROM sys.columns 

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

Answer 1

 string connectionString = "Data Source=(local);Initial Catalog=Northwind;" + "Integrated Security=true"; // Provide the query string with a parameter placeholder. string queryString = "SELECT Count(*) from sys.columns"; // Specify the parameter value. int paramValue = 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 (SqlConnection connection = new SqlConnection(connectionString)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(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(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine("\t{0}", reader[0]); } reader.Close(); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.ReadLine(); } 

Answer 2

You can use the SqlConnection class to connect to the database, and then you can use the Execute Scalar function to get the Row Count value. 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 3

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

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

Alternatively, you can do worse than looking at the ANSI-standard INFORMATION_SCHEMA types to find the same information in the future, the path to an RDBMS.

Answer 4

use Executescalar () to get one item.

  using (SqlConnection con = new SqlConnection(ConnectionString)) //for connecting to database { con.Open(); try { using (SqlCommand getchild = new SqlCommand("select count(*) from table1 ", con)) //SQL queries { Int32 count = (Int32)getchild.ExecuteScalar(); } } } 

Answer 5

You need to use the command and return the scalar variable back:

 SqlCommand cmd = new SqlCommand(sql, conn); Int32 count = (Int32)cmd.ExecuteScalar(); 

Answer 6

Use ExecuteScalar

Executes the query and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

 Int32 colnumber = 0; string sql = "SELECT count(*) FROM sys.columns"; using (SqlConnection conn = new SqlConnection(connString)) { SqlCommand cmd = new SqlCommand(sql, conn); try { conn.Open(); colnumber = (Int32)cmd.ExecuteScalar(); } catch (Exception ex) { Console.WriteLine(ex.Message); } } 

Answer 7

You want to use the ADO.NET functions in the System.Data.SqlClient namespace. ExecuteScalar is an easy-to-use method when you want to get only one result. For multiple results, you can use SqlDataReader.

 using System.Data.SqlClient; string resultVar = String.Empty; string ServerName="localhost"; string DatabaseName="foo"; SqlConnection conn=new SqlConnection(String.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI",ServerName,DatabaseName)); SqlCommand cmd=new SqlCommand(Query,conn); try { conn.Open(); } catch (SqlException se) { throw new InvalidOperationException(String.Format( "Connection error: {0} Num:{1} State:{2}", se.Message,se.Number, se.State)); } resultVar = (string)cmd.ExecuteScalar().ToString(); conn.Close();