Executing Commands Against a Data Store
| I l @ ve RuBoard |
| Once you've connected to a data store, you can execute commands against it. In this section, we'll look at how you can build command objects and execute these against a connection. Specifically, we'll look at how to invoke commands that return the following:
Building CommandsThe IDbCommand interface defines the functionality of a command class. Its two implementations , SqlCommand and OleDbCommand , expose the same properties and methods , with the exception that SqlCommand provides support for reading XML. (We'll discuss this functionality later in this chapter). However, you use all command objects in the same way: You create an instance of the command class, and then you call one of its execute methods to invoke a stored procedure or execute a SQL statement. The command classes' constructors are overloaded, so there are a number of ways you can create a new instance of a command. The following code shows the four constructor signatures for a SqlCommand : publicSqlCommand() publicSqlCommand(Stringquery) publicSqlCommand(Stringquery,SqlConnectionconn) publicSqlCommand(Stringquery,SqlConnectionconn,SqlTransactiontran) As you can see, the fourth constructer accepts a third parameter of an SqlTransaction , which we'll discuss later in the chapter. Once you create a command class instance, each of the constructer parameters is exposed as a public property. For example, the following code is taken from the SimpleSqlCommand.jsl sample file. It shows how you can set the CommandText (an SQL statement) and the Connection properties of the command after it is instantiated as an alternative to passing them into the constructor: //Createacommandobject SqlCommandcommand=newSqlCommand(); //AssociatewithaConnection command.set_Connection(connection); //Setthecommand'stext command.set_CommandText("SELECTEmployeeIDFROMEmployees"); Once you've created a command object, you can use one of its execute methods to invoke a query on the underlying data store. Table 7-3 shows the execute methods exposed by the command classes. In the remainder of this chapter, we'll examine how to use these methods and how to work with the data they return. The sample file from which the preceding code is taken, SimpleSqlCommand.jsl, uses the ExecuteScalar method to retrieve a single value. Table 7-3. SqlCommand and OleDbCommand Execute Methods
Using Parameters in StatementsEarlier, you saw how to create a command that contains an SQL statement. The statement was really a one-shot deal because you couldn't modify it for reuse with different values. But in reality, you'll of course want to reuse statements because values are frequently derived at run time and you don't want to write hundreds of SQL statements that perform similar operations. ADO.NET supports parameterized statements, which allow you to create this type of reusable statement. To create a parameterized statement, you use a placeholder within the statement to represent the location in which a parameter value can later be substituted. If you want to substitute multiple values in your statement, you can use multiple instances or forms of the placeholder in the statement. For SQL statements, the placeholder takes the form of a named parameter of the type @parameterName ; for OLE DB, you simply use a ? (question mark) as a placeholder. For example //SQLVersion DELETEFROMEmployeesWHEREEmployeeID=@EmployeeID //OLEDBVersion DELETEFROMEmployeesWHEREEmployeeID=? Once you define the statements with placeholders, you can create the parameters for use with the statements. To do this, you add the parameter name and data type to the collection of parameters associated with the command object. Parameters can be added through the Add method of the Sql ParameterCollection object or the OleDbParameterCollection object. To get a parameter collection, you invoke the get_Parameters method of a command object. For example, the following code fragment, taken from the sample file SimpleParameterizedDelete.jsl, sets the parameters for an SqlCommand object: //CreatetheSQLstatement StringstatementStr= "DELETEFROMEmployeesWHEREEmployeeID=@EmployeeID"; //Createacommandobject SqlCommandcommand=newSqlCommand(statementStr,connection); //Gettheparameterscollection SqlParameterCollectionparameters=command.get_Parameters(); //Addtheparameters SqlParameterparameter=parameters.Add("@EmployeeID",SqlDbType.Int); //Setthevalueoftheparameter parameter.set_Value((System.Int32)num); introwsAffected=command.ExecuteNonQuery(); Console.WriteLine("Deleted " +rowsAffected+ " rows"); When we added the parameter, we used the Int member of the SqlDbType enumeration, which specifies the data type of the parameter and is also specific to the underlying data provider. The data types are different for the SQL data provider and for the OLE DB data provider. Tables 7-4A through 7-4C show the permitted data types for each of these providers and shows how they map to the System.Data.DbType types and .NET Framework types. Table 7-4. Data Type Mappings Between SqlDbType and .NET Framework
Table 7-5. Data Type Mappings Between SqlDbType and OleDbType
Table 7-6. Data Type Mappings between OleDbType and System.Data.DbType
Note The Value property of the SqlParameter and OleDbParameter classes is an object, so you must coerce any primitive types into objects to use them to set this value. A parameter object exposes a number of public properties, which you can get and set by using the accessor methods it exposes. For a complete reference to these properties, you should see the product documentation, but for now we'll look at three of the most useful properties:
The SourceColumn is the name of the column from which the value for a parameter will be loaded or retrieved. The SourceVersion indicates which version of this value to use because several versions might coexist simultaneously . For example, you might update the value of an entry within a DataSet , so the entry will have an original value that differs from its current value. There are four possible versions of any given data, and these are defined in the System.Data.DataRowVersion enumeration. Table 7-5 shows the members of this enumeration. Table 7-7. System.Data.DataRowVersion Enumeration Members
The Direction property of the parameter object allows you to specify whether the parameter is used for input, for output, or as a return value. The output parameter is a special type of parameter that is used to transfer the value returned from a stored procedure or user -defined function. A return value is the result of some calculation or selection that is performed in the database. The System.Data.ParameterDirection enumeration defines the four possible values for this property, as shown in Table 7-6. Table 7-8. System.Data.ParameterDirection Enumeration Members
For example, the following code fragment creates an SQL delete command, creates a parameter object, and then sets this object's Direction and SourceVersion properties: //CreatetheSQLstatement StringstatementStr= "DELETEFROMEmployeesWHEREEmployeeID=@EmployeeID"; //Createacommandobject SqlCommandcommand=newSqlCommand(statementStr,connection); //Gettheparameterscollection SqlParameterCollectionparameters=command.get_Parameters(); //Addtheparameters SqlParameterparameter=parameters.Add("@EmployeeID",SqlDbType.Int); //Setthedirectionandversion parameter.set_Direction(ParameterDirection.Input); parameter.set_SourceVersion(DataRowVersion.Current); //Setthevalueoftheparameter parameter.set_Value((Int32)num); introwsAffected=command.ExecuteNonQuery(); System.Console.WriteLine("Deleted " +rowsAffected+ " rows"); Invoking Stored ProceduresTypically, a command is a string that contains an SQL statement, but a command can also invoke a stored procedure. In the context of ADO.NET, a stored procedure is a unit of SQL logic, or operations, encapsulated in a single command and stored within the underlying database. You can invoke a stored procedure simply by setting the CommandType property of a command object to StoredProcedure (a member of the System.Data.CommandType enumeration) and then using the ParametersCollection object to define input and output parameters for the stored procedure. For more information on stored procedures and parameters, see the product documentation. Retrieving a Single Record from a Data SourceSometimes when you execute a query against a database, you expect to receive only a single result ”for example, when you use aggregate functions. In such situations, you use the ExecuteScalar method of a command object. This method returns a single value of the type System.Object . If the database returns more than one value, only the first value in the result set is returned by the ExecuteScalar method. The RetrieveSingleValue.jsl sample file shows how to use the ExecuteScalar method to return the aggregate value returned by the SQL Count function: com.set_CommandText("SELECTCount(*)FROMEmployees"); //ExecuteScalarreturntypeSystem.Object count=com.ExecuteScalar(); Using a DataReader Object for Read-Only Data RetrievalYou often need to retrieve data on a read-only basis ”for example, when you're listing product details in an e-commerce application. In such situations, you do not need the ability to update the contents of a data source, and you definitely do not want to consume the resources associated with a read/write operation. The .NET Framework provides the System.Data.IDataReader interface for efficient, fast, read-only data access. Implementations of IDataReader are provided for both SQL Server and OLE DB providers. In addition to being read-only, a data reader provides forward-only navigation ”in other words, you cannot navigate back and forth through data. To create an implementation of IDataReader , you call the ExecuteReader method of a command object. For example SqlDataReadersdr=myCommand.ExecuteReader(); If the Command object has an associated SQL Select statement, the IDataReader instance contains a table-like structure of data returned by the underlying data source. You can reference individual columns within this table, but the records (or rows) must be read sequentially, one at a time ”you cannot move to a record at a given index. Before we look at how you can use the IDataReader interface to read data, we'll look at the properties you can access through the IDataReader interface, which allow you to explore the structure and format of the data. Table 7-7 shows some of the most useful properties and methods a data reader object exposes. Table 7-9. Useful DataReader Properties and Methods
When an IDataReader instance is created, it is positioned directly before the first row in the retrieved data. To scroll through the rows, you call its Read method, which advances the data reader forward one record and returns true if there are further records: while(sdr.Read()) { //Readtherecords } A data reader provides a number of methods to retrieve the contents of a particular column in the current record. If you want to get a value of a specific data type, you can invoke one of the GetXXX methods, where XXX is one of the .NET Framework data types (shown previously in Table 7-4). The GetXXX methods all take an integer as a parameter that represents the index of the column from which to retrieve the data. You can invoke the GetValue method, which gets the value of a record in its native format, but to use this value you must still convert it into a valid type within the .NET Framework. Two useful methods that you can use in conjunction with the Get methods are IsDBNull and GetDataTypeName . The IsDBNull method returns true if the given column contains a nonexistent or missing value (in other words, if it is Null in database terms as represented by the System.DBNull class). The GetDataTypeName method returns the underlying SQL Server data type of the data. For example, the following code checks whether the record values for an employee's first name and last name are null, and if they are not, it writes their values to the standard output: //Column0istheforename,column1isthe //middleinitial,andcolumn2isthesurname if(!sdr.IsDBNull(0)&&!sdr.IsDBNull(2)) { Console.WriteLine(sdr.getValue(0).ToString()+ ", "); Console.WriteLine(sdr.getValue(2).ToString()+ "\n"); } If your data consists of a large number of columns, retrieving individual column values can quickly result in verbose code. Fortunately, a data reader exposes a GetValues method, which returns the values of all the columns within the IDataReader instance. For example, the code shown in the DataReader.jsl sample uses the GetValues method to retrieve all the columns for a record, checks whether they are null, and if they are not it prints their values to the standard output. |
| I l @ ve RuBoard |
EAN: 2147483647
Pages: 128