ADO.NET in a Nutshell

Creating and Executing a Command

When creating a Command object, you have the choice of several constructors. The most useful accepts a CommandText value and a Connection. Here’s an example with the SqlCommand class:

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(commandText, con);

For standard providers, there are three ways to execute a command: ExecuteNonQuery( ) , ExecuteReader( ), and ExecuteScalar( ). You choose one of these methods, depending on the type of command you are executing. For example, ExecuteReader( ) returns a DataReader and provides read-only access to query results. We examine the DataReader in Chapter 5.

Some providers include additional members. For example, the ADO.NET SQL Server provider includes an ExecuteXmlReader( ) method that retrieves data as an XML document. We’ll examine this specialized version in Chapter 17, which considers ADO.NET’s support for XML.

Executing a Command That Doesn’t Return Rows

The SQL language includes several nonquery commands. The best known include UPDATE, DELETE, and INSERT. You can also use other commands to create, alter, or drop tables, constraints, relations, and so on. To execute any of these commands, just set the CommandText property with the full SQL statement, open a connection, and invoke the ExecuteNonQuery( ) method. The next sections consider examples that update, delete, and insert records.

Updating a record

The UPDATE statement, at its simplest, uses the following syntax: ...