Executing SELECT Statements and TableDirect Commands
|
|
Executing SELECT Statements and TableDirect Commands
A TableDirect command is actually a SELECT statement that returns all the rows and columns for a specified table. A Command object has three methods you can use to execute a SELECT statement or TableDirect command. Table 8.4 shows these methods, which you'll learn how to use in the following sections.
| METHOD | RETURN TYPE | DESCRIPTION |
|---|---|---|
| ExecuteReader() | SqlDataReader | Used to execute SQL SELECT statements, TableDirect commands or stored procedure calls that return a result set. Returns the result set in a DataReader object. |
| ExecuteScalar() | object | Used to execute SQL SELECT statements that return a single value (any other values are ignored). Returns the single value as an object. |
| ExecuteXmlReader() | XmlReader | Used to execute SQL SELECT statements that return XML data. Returns the result set in an XmlReader object. Applies only to the SqlCommand class. |
Executing a SELECT Statement Using the ExecuteReader() Method
Let's take a look at an example that executes a SELECT statement using the ExecuteReader() method. This method returns the result set in a DataReader object, which you can then use to read the rows returned by the database. For example, the following code creates the required objects and executes a SELECT statement that retrieves the top five rows from the Customers table:
SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" ); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 5 CustomerID, CompanyName, ContactName, Address " + "FROM Customers " + "ORDER BY CustomerID"; mySqlConnection.Open(); SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
| Tip | You'll notice that I didn't call the Open() method of the SqlConnection object until just before calling the ExecuteReader() method of the SqlCommand object. This is intentional. By opening the connection at the very last moment, you minimize time spent connected to the database and therefore conserve database resources. |
The result set returned by mySqlCommand is stored in mySqlDataReader. You then read the rows from mySqlDataReader using the Read() method. This method returns the Boolean true value when there is another row to read, otherwise it returns false. You can read an individual column value in a row from mySqlDataReader by passing the name of the column in square brackets. For example, to read the CustomerID column, you use mySqlDataReader["CustomerID"].
| Note | You can also specify the column you want to get by passing a numeric value in brackets. For example, my SqlDataReader[0] also returns the CustomerID column value. 0 corresponds to the first column in the table, which in this example is the CustomerID column. |
You can use the Read() method in a while loop to read each row in turn, as shown in the following example:
while (mySqlDataReader.Read()) { Console.WriteLine("mySqlDataReader[\" CustomerID\"] = " + mySqlDataReader["CustomerID"]); Console.WriteLine("mySqlDataReader[\" CompanyName\"] = " + mySqlDataReader["CompanyName"]); Console.WriteLine("mySqlDataReader[\" ContactName\"] = " + mySqlDataReader["ContactName"]); Console.WriteLine("mySqlDataReader[\" Address\"] = " + mySqlDataReader["Address"]); } Listing 8.1 illustrates a complete program that uses the code examples shown in this section.
Listing 8.1: EXECUTESELECT.CS
 |
/* ExecuteSelect.cs illustrates how to execute a SELECT statement using a SqlCommand object */ using System; using System.Data; using System.Data.SqlClient; class ExecuteSelect { public static void Main() { // create a SqlConnection object to connect to the database SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" ); // create a SqlCommand object SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); // set the CommandText property of the SqlCommand object to // the SELECT statement mySqlCommand.CommandText = "SELECT TOP 5 CustomerID, CompanyName, ContactName, Address " + "FROM Customers " + "ORDER BY CustomerID"; // open the database connection using the // Open() method of the SqlConnection object mySqlConnection.Open(); // create a SqlDataReader object and call the ExecuteReader() // method of the SqlCommand object to run the SQL SELECT statement SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); // read the rows from the SqlDataReader object using // the Read() method while (mySqlDataReader.Read()) { Console.WriteLine("mySqlDataReader[\" CustomerID\"] = " + mySqlDataReader["CustomerID"]); Console.WriteLine("mySqlDataReader[\" CompanyName\"] = " + mySqlDataReader["CompanyName"]); Console.WriteLine("mySqlDataReader[\" ContactName\"] = " + mySqlDataReader["ContactName"]); Console.WriteLine("mySqlDataReader[\" Address\"] = " + mySqlDataReader["Address"]); } // close the SqlDataReader object using the Close() method mySqlDataReader.Close(); // close the SqlConnection object using the Close() method mySqlConnection.Close(); } } |
The output from this program is as follows:
mySqlDataReader["CustomerID"] = ALFKI mySqlDataReader["CompanyName"] = Alfreds Futterkiste mySqlDataReader["ContactName"] = Maria Anders mySqlDataReader["Address"] = Obere Str. 57 mySqlDataReader["CustomerID"] = ANATR mySqlDataReader["CompanyName"] = Ana Trujillo3 Emparedados y helados mySqlDataReader["ContactName"] = Ana Trujillo mySqlDataReader["Address"] = Avda. de la Constitución 2222 mySqlDataReader["CustomerID"] = ANTON mySqlDataReader["CompanyName"] = Antonio Moreno Taquería mySqlDataReader["ContactName"] = Antonio Moreno mySqlDataReader["Address"] = Mataderos 2312 mySqlDataReader["CustomerID"] = AROUT mySqlDataReader["CompanyName"] = Around the Horn mySqlDataReader["ContactName"] = Thomas Hardy mySqlDataReader["Address"] = 120 Hanover Sq. mySqlDataReader["CustomerID"] = BERGS mySqlDataReader["CompanyName"] = Berglunds snabbköp mySqlDataReader["ContactName"] = Christina Berglund mySqlDataReader["Address"] = Berguvsvägen 8
Controlling the Command Behavior Using the ExecuteReader() Method
The ExecuteReader() method accepts an optional parameter that controls the command behavior. The values for this parameter come from the System.Data.CommandBehavior enumeration, for which values are shown in Table 8.5.
| VALUE | DESCRIPTION |
|---|---|
| CloseConnection | Specifies that when the associated DataReader object is closed, the Connection object is also closed. |
| Default | Indicates the Command object may return multiple result sets. |
| KeyInfo | Specifies the Command object returns information about the primary key columns in the result set. |
| SchemaOnly | Indicates the Command object returns information only about the columns. |
| SequentialAccess | Enables a DataReader object to read rows that have columns containing large binary values. SequentialAccess causes the DataReader to read the data as a stream. You then use the GetBytes() or GetChars() methods of the DataReader to read the stream. Note: you'll learn the details of DataReader objects in the next chapter. |
| SingleResult | Specifies the Command object returns a single result set. |
| SingleRow | Indicates the Command object returns a single row. |
You'll see how to use the SingleRow and SchemaOnly command behaviors next.
Using the SingleRow Command Behavior
You use the SingleRow command behavior to indicate that your Command object returns a single row. For example, let's say you have a Command object named mySqlCommand with the CommandText property set as follows:
mySqlCommand.CommandText = "SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice " + "FROM Products";
Next, the following example passes the CommandBehavior.SingleRow value to the ExecuteReader() method, indicating that the Command object retrieves only the first row:
SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SingleRow);
Even though the earlier SELECT statement indicates that all the rows are to be retrieved from the Products table, the mySqlDataReader object will be able to read only the first row.
Listing 8.2 illustrates the effect of using CommandBehavior.SingleRow.
Listing 8.2: SINGLEROWCOMMANDBEHAVIOR.CS
|
/* SingleRowCommandBehavior.cs illustrates how to control the command behavior to return a single row */ using System; using System.Data; using System.Data.SqlClient; class SingleRowCommandBehavior { public static void Main() { SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" ); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice " + "FROM Products"; mySqlConnection.Open(); // pass the CommandBehavior.SingleRow value to the // ExecuteReader() method, indicating that the Command object // only returns a single row SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SingleRow); while (mySqlDataReader.Read()) { Console.WriteLine("mySqlDataReader[\" ProductID\"] = " + mySqlDataReader["ProductID"]); Console.WriteLine("mySqlDataReader[\" ProductName\"] = " + mySqlDataReader["ProductName"]); Console.WriteLine("mySqlDataReader[\" QuantityPerUnit\"] = " + mySqlDataReader["QuantityPerUnit"]); Console.WriteLine("mySqlDataReader[\" UnitPrice\"] = " + mySqlDataReader["UnitPrice"]); } mySqlDataReader.Close(); mySqlConnection.Close(); } } |
The output from this program is as follows:
mySqlDataReader["ProductID"] = 1 mySqlDataReader["ProductName"] = Chai mySqlDataReader["QuantityPerUnit"] = 10 boxes x 20 bags mySqlDataReader["UnitPrice"] = 18
Using the SchemaOnly Command Behavior
You use the SchemaOnly command behavior to indicate that your Command object returns information only about the columns retrieved by a SELECT statement, or all the columns when you use a TableDirect command.
For example, let's say you have a Command object named mySqlCommand with the CommandText property set as follows:
mySqlCommand.CommandText = "SELECT ProductID, ProductName, UnitPrice " + "FROM Products " + "WHERE ProductID = 1";
Next, the following example passes the CommandBehavior.SchemaOnly value to the ExecuteReader() method, indicating that the Command object returns information about the schema:
SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SchemaOnly);
In this example, since the ProductID, ProductName, and UnitPrice columns of the Products table were used in the earlier SELECT statement, information about those columns is retrieved instead of the column values.
You get the information about the columns using the GetSchemaTable() method of your SqlData-Reader object. The GetSchemaTable() method returns a DataTable object with columns that contain the details of the retrieved database columns:
DataTable myDataTable = productsSqlDataReader.GetSchemaTable();
To display the values in the DataTable object, you can use the following loop that displays the DataTable column names and the contents of each DataTable column:
foreach (DataRow myDataRow in myDataTable.Rows) { foreach (DataColumn myDataColumn in myDataTable.Columns) { Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn]); if (myDataColumn.ToString() == "ProviderType") { Console.WriteLine(myDataColumn + "= " + ((System.Data.SqlDbType) myDataRow[myDataColumn])); } } } Notice that this code features two foreach loops. The outer loop iterates over the DataRow objects in myDataTable, and the inner loop iterates over the DataColumn objects in the current DataRow. Don't worry too much about the details of accessing a DataTable just yet: you'll learn the details in Chapter 10, "Using DataSet Objects to Store Data."
The if statement in the inner foreach loop requires a little explanation. What I'm doing is examining the myDataColumn to see if it contains the ProviderType. ProviderType contains a number value that indicates the SQL Server type of the database column. I cast this number to System.Data.SqlDbType, which is an enumeration that defines the SQL Server column types, as you'll see later in the "Supplying Parameters to Commands" section. Table 8.9 in that section shows the SqlDbType enumeration values. By casting the ProviderType number to SqlDbType, you can see the actual name of the SQL Server column type.
The first iteration of the outer loop displays all the DataColumn object values for the first DataRow object. This cause the following output to be produced and shows the schema details for the ProductID column; notice the ProviderType number and name that indicate ProductID is a SQL Server int:
ColumnName = ProductID ColumnOrdinal = 0 ColumnSize = 4 NumericPrecision = 0 NumericScale = 0 IsUnique = IsKey = BaseCatalogName = BaseColumnName = ProductID BaseSchemaName = BaseTableName = DataType = System.Int32 AllowDBNull = False ProviderType = 8 ProviderType = Int IsAliased = IsExpression = IsIdentity = True IsAutoIncrement = True IsRowVersion = IsHidden = IsLong = False IsReadOnly = True
The meanings of these results are shown in Table 8.6.
| VALUE | DESCRIPTION |
|---|---|
| ColumnName | Name of the column. |
| ColumnOrdinal | Ordinal of the column. |
| ColumnSize | Maximum length (in characters) of a column value. For fixed-length SQL Server types such as int, the ColumnSize is the length of that type. |
| NumericPrecision | Total number of digits used to represent a floating-point type. An example of a floating-point type is the SQL Server float type. The total number of digits includes the digits to the left and right of the decimal point. |
| NumericScale | Total number of digits to the right of the decimal point in a floating-point type. |
| IsUnique | Boolean true/false value that indicates whether two rows can have the same value in the current column. |
| IsKey | Boolean true/false value that indicates whether the column is part of the primary key. |
| BaseCatalogName | Name of the catalog in the database that contains the column. BaseCatalogName defaults to null. |
| BaseColumnName | Name of the column in the database. This will differ from the ColumnName if you use an alias for the column. BaseColumnName defaults to null. |
| BaseSchemaName | Name of the schema in the database that contains the column. BaseSchemaName defaults to null. |
| BaseTableName | Name of the table or view in the database that contains the column. BaseTableName defaults to null. |
| DataType | .NET type used to represent the column. You'll learn about the .NET types in the next chapter. |
| AllowDBNull | Boolean true/false value that indicates whether the column can accept a database null value. |
| ProviderType | Indicates the column's database type. |
| IsAliased | Boolean true/false value that indicates whether the column is an alias. |
| IsExpression | Boolean true/false value that indicates whether the column is an expression. |
| IsIdentity | Boolean true/false value that indicates whether the column is an identity. |
| IsAutoIncrement | Boolean true/false value that indicates whether the column is automatically assigned a value for a new row and that value is automatically incremented. |
| IsRowVersion | Boolean true/false value that indicates whether the column contains a persistent row identifier that cannot be written to. |
| IsHidden | Boolean true/false value that indicates whether the column is hidden. |
| IsLong | Boolean true/false value that indicates whether the column contains a binary long object (BLOB). A BLOB contains a long string of binary data. |
| IsReadOnly | Boolean true/false value that indicates whether the column can be modified. |
Listing 8.3 illustrates the effect of using CommandBehavior.SchemaOnly and displays the schema details for the ProductID, ProductName, and UnitPrice columns.
Listing 8.3: SCHEMAONLYCOMMANDBEHAVIOR.CS
|
/* SchemaOnlyCommandBehavior.cs illustrates how to read a table schema */ using System; using System.Data; using System.Data.SqlClient; class SchemaOnlyCommandBehavior { public static void Main() { SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" ); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT ProductID, ProductName, UnitPrice " + "FROM Products " + "WHERE ProductID = 1"; mySqlConnection.Open(); // pass the CommandBehavior.SchemaOnly constant to the // ExecuteReader() method to get the schema SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SchemaOnly); // read the DataTable containing the schema from the DataReader DataTable myDataTable = productsSqlDataReader.GetSchemaTable(); // display the rows and columns in the DataTable foreach (DataRow myDataRow in myDataTable.Rows) { Console.WriteLine("\nNew column details follow:"); foreach (DataColumn myDataColumn in myDataTable.Columns) { Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn]); if (myDataColumn.ToString() == "ProviderType") { Console.WriteLine(myDataColumn + "= " + ((System.Data.SqlDbType) myDataRow[myDataColumn])); } } } productsSqlDataReader.Close(); mySqlConnection.Close(); } } |
You should notice the different details for the ProductID, ProductName, and UnitPrice columns in the output that follows:
New column details follow: ColumnName = ProductID ColumnOrdinal = 0 ColumnSize = 4 NumericPrecision = 0 NumericScale = 0 IsUnique = IsKey = BaseCatalogName = BaseColumnName = ProductID BaseSchemaName = BaseTableName = DataType = System.Int32 AllowDBNull = False ProviderType = 8 ProviderType = Int IsAliased = IsExpression = IsIdentity = True IsAutoIncrement = True IsRowVersion = IsHidden = IsLong = False IsReadOnly = True New column details follow: ColumnName = ProductName ColumnOrdinal = 1 ColumnSize = 40 NumericPrecision = 0 NumericScale = 0 IsUnique = IsKey = BaseCatalogName = BaseColumnName = ProductName BaseSchemaName = BaseTableName = DataType = System.String AllowDBNull = False ProviderType = 12 ProviderType = NVarChar IsAliased = IsExpression = IsIdentity = False IsAutoIncrement = False IsRowVersion = IsHidden = IsLong = False IsReadOnly = False New column details follow: ColumnName = UnitPrice ColumnOrdinal = 2 ColumnSize = 8 NumericPrecision = 0 NumericScale = 0 IsUnique = IsKey = BaseCatalogName = BaseColumnName = UnitPrice BaseSchemaName = BaseTableName = DataType = System.Decimal AllowDBNull = True ProviderType = 9 ProviderType = Money IsAliased = IsExpression = IsIdentity = False IsAutoIncrement = False IsRowVersion = IsHidden = IsLong = False IsReadOnly = False
Executing a TableDirect Statement Using the ExecuteReader() Method
When you set the CommandType property of a Command object to TableDirect, you specify that you want to retrieve all the rows and columns of a particular table. You specify the name of the table to retrieve from in the CommandText property.
| Warning | SqlCommand objects don't support the CommandType of TableDirect. The example in this section will use an OleDbCommand object instead. |
As you know, you can use a SqlConnection object to connect to SQL Server. You can also use an OleDbConnection object to connect to SQL Server. You simply set the provider to SQLOLEDB in the connection string passed to the OleDbConnection constructor. For example:
OleDbConnection myOleDbConnection = new OleDbConnection( "Provider=SQLOLEDB;server=localhost;database=Northwind;" + "uid=sa;pwd=sa" );
Next, you create an OleDbConnection object:
OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();
You then set the CommandType of myOleDbConnection to CommandType.TableDirect:
myOleDbCommand.CommandType = CommandType.TableDirect;
Next, you specify the name of the table to retrieve from using the CommandText property. The following example sets the CommandText property of myOleDbCommand to Products:
myOleDbCommand.CommandText = "Products";
You next open the database connection:
myOleDbConnection.Open();
Finally, you execute myOleDbCommand using the ExecuteReader() method:
OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();
The SQL statement actually executed is SELECT * FROM Products, which retrieves all the rows and columns from the Products table.
Listing 8.4 illustrates the code shown in this section.
Listing 8.4: EXECUTETABLEDIRECT.CS
|
/* ExecuteTableDirect.cs illustrates how to execute a TableDirect command */ using System; using System.Data; using System.Data.OleDb; class ExecuteTableDirect { public static void Main() { OleDbConnection myOleDbConnection = new OleDbConnection( "Provider=SQLOLEDB;server=localhost;database=Northwind;" + "uid=sa;pwd=sa" ); OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand(); // set the CommandType property of the OleDbCommand object to // TableDirect myOleDbCommand.CommandType = CommandType.TableDirect; // set the CommandText property of the OleDbCommand object to // the name of the table to retrieve from myOleDbCommand.CommandText = "Products"; myOleDbConnection.Open(); OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader(); // only read the first 5 rows from the OleDbDataReader object for (int count = 1; count <= 5; count++) { myOleDbDataReader.Read(); Console.WriteLine("myOleDbDataReader[\" ProductID\"] = " + myOleDbDataReader["ProductID"]); Console.WriteLine("myOleDbDataReader[\" ProductName\"] = " + myOleDbDataReader["ProductName"]); Console.WriteLine("myOleDbDataReader[\" QuantityPerUnit\"] = " + myOleDbDataReader["QuantityPerUnit"]); Console.WriteLine("myOleDbDataReader[\" UnitPrice\"] = " + myOleDbDataReader["UnitPrice"]); } myOleDbDataReader.Close(); myOleDbConnection.Close(); } } |
You'll notice that this program displays only the first five rows from the Products table, even though all the rows are retrieved.
The output from this program is as follows:
myOleDbDataReader["ProductID"] = 1 myOleDbDataReader["ProductName"] = Chai myOleDbDataReader["QuantityPerUnit"] = 10 boxes x 20 bags myOleDbDataReader["UnitPrice"] = 18 myOleDbDataReader["ProductID"] = 2 myOleDbDataReader["ProductName"] = Chang myOleDbDataReader["QuantityPerUnit"] = 24 - 12 oz bottles myOleDbDataReader["UnitPrice"] = 19 myOleDbDataReader["ProductID"] = 3 myOleDbDataReader["ProductName"] = Aniseed Syrup myOleDbDataReader["QuantityPerUnit"] = 12 - 550 ml bottles myOleDbDataReader["UnitPrice"] = 10 myOleDbDataReader["ProductID"] = 4 myOleDbDataReader["ProductName"] = Chef Anton's Cajun Seasoning myOleDbDataReader["QuantityPerUnit"] = 48 - 6 oz jars myOleDbDataReader["UnitPrice"] = 22 myOleDbDataReader["ProductID"] = 5 myOleDbDataReader["ProductName"] = Chef Anton's Gumbo Mix myOleDbDataReader["QuantityPerUnit"] = 36 boxes myOleDbDataReader["UnitPrice"] = 21.35
Executing a SELECT Statement Using the ExecuteScalar() Method
You use the ExecuteScalar() method to execute SQL SELECT statements that return a single value; any other values are ignored. The ExecuteScalar() method returns the single result as an object of the System.Object class. One use for the ExecuteScalar() method is to execute a SELECT statement that uses an aggregate function such as COUNT() to get the number of rows in a table. Aggregate functions are covered in Chapter 4, "Introduction to Transact-SQL Programming."
For example, the following statement sets the CommandText property of the mySqlCommand object to a SELECT that uses the COUNT() function. This SELECT returns the number of rows in the Products table:
mySqlCommand.CommandText = "SELECT COUNT(*) " + "FROM Products";
Next, the following example executes the SELECT statement using the ExecuteScalar() method:
int returnValue = (int) mySqlCommand.ExecuteScalar();
You'll notice I cast the generic object returned by ExecuteScalar() to an int before storing the result in the int returnValue variable.
Listing 8.5 illustrates the use of the ExecuteScalar() method.
Listing 8.5: EXECUTESCALAR.CS
|
/* ExecuteScalar.cs illustrates how to use the ExecuteScalar() method to run a SELECT statement that returns a single value */ using System; using System.Data; using System.Data.SqlClient; class ExecuteScalar { public static void Main() { SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" ); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT COUNT(*) " + "FROM Products"; mySqlConnection.Open(); // call the ExecuteScalar() method of the SqlCommand object // to run the SELECT statement int returnValue = (int) mySqlCommand.ExecuteScalar(); Console.WriteLine("mySqlCommand.ExecuteScalar() = " + returnValue); mySqlConnection.Close(); } } |
The output from this program is as follows:
mySqlCommand.ExecuteScalar() = 79
Of course, your output might vary depending on the number of rows in your Products table.
Executing a Command that Retrieves Data as XML Using the ExecuteXMLReader() Method
You use the ExecuteXmlReader() method to execute a SQL SELECT statement that returns XML data. The ExecuteXmlReader() method returns the results in an XmlReader object, which you then use to read the retrieved XML data.
| Note | The ExecuteXmlReader() method applies only to the SqlCommand class. |
SQL Server extends standard SQL to allow you to query the database and get results back as XML. Specifically, you can add a FOR XML clause to the end of a SELECT statement. The FOR XML clause has the following syntax:
FOR XML {RAW | AUTO | EXPLICIT} [, XMLDATA] [, ELEMENTS] [, BINARY BASE64] Table 8.7 shows the description of the keywords used in the FOR XML clause.
| KEYWORD | DESCRIPTION |
|---|---|
| FOR XML | Specifies that SQL Server is to return results as XML. |
| RAW | Indicates that each row in the result set is returned as an XML <row> element. Column values become attributes of the <row> element. |
| AUTO | Specifies that each row in the result set is returned as an XML element with the name of table used in place of the generic <row> element. |
| EXPLICIT | Indicates that your SELECT statement specifies the parent-child relationship, which is then used by SQL Server to create XML with the appropriate nesting structure. |
| XMLDATA | Specifies that the Document Type Definition is to be included in the returned XML. |
| ELEMENTS | Indicates that the columns are returned as subelements of the row. Otherwise, the columns are returned as attributes of the row. You can use this option only with AUTO. |
| BINARY BASE64 | Specifies that any binary data returned by the query is encoded in base 64. If you want to retrieve binary data using RAW and EXPLICIT mode, then you must use BINARY BASE64. In AUTO mode, binary data is returned as a reference by default. |
You'll see a simple example of the FOR XML clause here, and you'll learn the full details of this clause in Chapter 16, "Using SQL Server's XML Support."
The following example sets the CommandText property of mySqlCommand to a SELECT statement that uses the FOR XML AUTO clause. This SELECT statement returns the first five rows from the Products table as XML.
mySqlCommand.CommandText = "SELECT TOP 5 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID " + "FOR XML AUTO";
Next, the following statement executes the SELECT using the ExecuteXmlReader() method:
XmlReader myXmlReader = mySqlCommand.ExecuteXmlReader();
| Note | The XmlReader class is defined in the System.Xml namespace. |
To start reading the XML from the XmlReader object, you use the Read() method. You then check to make sure you're not at the end of the rows using the EOF property of the XmlReader object. EOF returns true if there are no more rows to read, otherwise it returns false. You use the ReadOuterXml() method to read the actual XML from the XmlReader object. The following example illustrates how to read XML from myXmlReader:
myXmlReader.Read(); while (!myXmlReader.EOF) { Console.WriteLine(myXmlReader.ReadOuterXml()); } Listing 8.6 illustrates the use of the ExecuteXmlReader() method.
Listing 8.6: EXECUTEXMLREADER.CS
|
/* ExecuteXmlReader.cs illustrates how to use the ExecuteXmlReader() method to run a SELECT statement that returns XML */ using System; using System.Data; using System.Data.SqlClient; using System.Xml; class ExecuteXmlReader { public static void Main() { SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" ); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); // set the CommandText property of the SqlCommand object to // a SELECT statement that retrieves XML mySqlCommand.CommandText = "SELECT TOP 5 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID " + "FOR XML AUTO"; mySqlConnection.Open(); // create a SqlDataReader object and call the ExecuteReader() // method of the SqlCommand object to run the SELECT statement XmlReader myXmlReader = mySqlCommand.ExecuteXmlReader(); // read the rows from the XmlReader object using the Read() method myXmlReader.Read(); while (!myXmlReader.EOF) { Console.WriteLine(myXmlReader.ReadOuterXml()); } myXmlReader.Close(); mySqlConnection.Close(); } } |
You'll notice I imported the System.Xml namespace near the beginning of this program.
The output from this program is as follows:
<Products Product ProductName="Chai" UnitPrice="18.0000"/> <Products Product ProductName="Chang" UnitPrice="19.0000"/> <Products Product ProductName="Aniseed Syrup" UnitPrice="10.0000"/> <Products Product ProductName="Chef Anton's Cajun Seasoning" UnitPrice="22.0000"/> <Products Product ProductName="Chef Anton's Gumbo Mix" UnitPrice="21. 3500"/>
Notice that each of the 5 rows from the Products table is returned as XML.
|
|
EAN: 2147483647
Pages: 181