C# SqlCommand

SqlCommand deals with databases.

It executes SQL commands on a database. It sends an SQL command to a database that is specified by an SqlConnection object. We then call instance methods to physically apply the command to the database.

Example. First, this program demonstrates the usage of the SqlCommand data object in the C# language. We use SqlCommand in the context of a complete but trivial program that reads data from a specific database on the disk.

Info: The SqlCommand type has a public parameterized constructor and it can be used with one or more arguments.

And: The first parameter specifies the SQL statement. The second parameter is the SqlConnection. The third parameter is the SqlTransaction.

C# program that uses SqlCommand instance

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
	//
	// First, the program accesses the connection string and uses it on a connection.
	//
	string conString = ConsoleApplication1.Properties.Settings.Default.ConnectionString;
	using (SqlConnection connection = new SqlConnection(conString))
	{
	    connection.Open();
	    //
	    // The SqlCommand should be created inside a using statement.
	    // ... It receives the SQL statement as the first argument.
	    // ... It receives the connection object as the second argument.
	    // ... The SQL text only works with a specific database.
	    //
	    using (SqlCommand command = new SqlCommand(
		"SELECT TOP 3 * FROM Dogs1 ORDER BY Weight",
		connection))
	    {
		//
		// Instance methods can be used on the SqlCommand instance.
		// ... These read data from executing the command.
		//
		using (SqlDataReader reader = command.ExecuteReader())
		{
		    while (reader.Read())
		    {
			for (int i = 0; i < reader.FieldCount; i++)
			{
			    Console.WriteLine(reader.GetValue(i));
			}
			Console.WriteLine();
		    }
		}
	    }
	}
    }
}

Output

7
Candy
Yorkshire Terrier

25
Charles
Cavalier King Charles Spaniel

57
Koko
Shar Pei

This program interfaces with the SQL Server engine. It uses some project-specific external data. It will not work for you unless you change the connection string reference and the SQL command text for your environment.

Note: It shows the use of the SqlCommand in the context of a standard program, which also uses other data objects.

The SQL data objects are wrapped in a resource acquisition statement. To do this in the C# language, please use the using-statement. This ensures that resources are properly acquired and disposed of when no longer needed.

Using Statement: Dispose and IDisposable

Constructor. The call to the SqlCommand constructor uses two parameters. The first parameter is the actual SQL source text. This string uses SQL and is not compiled by the C# compiler. The second parameter is the SqlConnection object.

Overview: You must manually specify the connection to use on the SqlCommand. This can be done with a parameter on the constructor.

Tip: More SQL tutorials are available in the data category on this site. SQL is hard to get set up, but works well.

Summary. We looked at the SqlCommand type in the C# language. We wrote a complete program that reads from a database on the local disk. The SqlCommand has a constructor and this you can specify its connection and the SQL text.

Also: It has instance methods that let you execute the command and receive data from the database.

Related Links