An introduction to ADO.NET - The SqlConnection class
(Page 4 of 4 )
Before you can access the data in a database, you have to create a connection object that defines the connection to the database. To do that, you use the SqlConnection class presented in figure 2-4.
The most important property of the SqlConnection class is ConnectionString. A connection string is a text string that provides the information necessary to establish a connection to a database. That means it includes information such as the name of the database you want to access and the database server that contains it. It can also contain authentication information such as a user-id and password. You’ll learn more about coding connection strings in chapter 6.
The two methods of the SqlConnection class shown in this figure let you open and close the connection. In general, you should leave a connection open only while data is being retrieved or updated. That’s why when you use a data adapter, the connection is opened and closed for you. In that case, you don’t need to use the Open and Close methods.
The SqlCommand class
To execute a SQL statement against a SQL Server database, you create a SqlCommand object that contains the statement. Figure 2-4 presents the SqlCommand class you use to create this object. Notice that the Connection property of this class associates the command with a SqlConnection object, and the CommandText property contains the SQL statement to be executed.
The CommandType property indicates how the command object should interpret the value of the CommandText property. Instead of specifying a SQL statement for the CommandText property, for example, you can specify the name of a stored procedure, which consists of one or more SQL statements that have been compiled and stored with the database (see chapter 8 for details). Or you can specify the name of a table. If you specify a SQL statement, you set the value of the CommandType property to CommandType.Text. If you specify the name of a stored procedure, you set it to CommandType.StoredProcedure. And if you specify the name of a table, you set it to CommandType.TableDirect. Then, a Select * statement will be executed on the table.
Earlier in this chapter, you learned that you can use a data adapter to execute command objects. In addition, you can execute a command object directly using one of the three Execute methods shown in this figure. If the command contains a Select statement, for example, you can execute it using either ExecuteReader or ExecuteScalar. If you use ExecuteReader, the results are returned as a DataReader object. If you use ExecuteScalar, only the value in the first column and row of the query results is returned. You’re most likely to use this method with a Select statement that returns a single summary value.
If the command contains an Insert, Update, or Delete statement, you’ll use the ExecuteNonQuery method to execute it. This method returns an integer value that indicates the number of rows that were affected by the command. For example, if the command deletes a single row, the ExecuteNonQuery method returns 1.
Figure 2-4. The SqlConnection and SqlCommand classes
Common properties and methods of the SqlConnection class
Common properties and methods of the SqlCommand class
Description - Each command object is associated with a connection object through the command’s Connection property. When a command is executed, the information in the ConnectionString property of the connection object is used to connect to the database.
- When you use a data adapter to work with a database, the connection is opened and closed automatically. If that’s not what you want, you can use the Open and Close methods of the connection object to open and close the connection.
- You can use the three Execute methods of a command object to execute the SQL statement it contains. You can also execute the SQL statement in a command object using methods of the data adapter. See figure 2-5 for more information.
/ 12 
