Executing SELECT Statements and TableDirect Commands phần 2

Chia sẻ: Tuan Nghia | Ngày: | Loại File: PDF | Số trang:11

Thêm vào BST

Báo xấu

75
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Listing 8.3: SCHEMAONLYCOMMANDBEHAVIOR.CS /* SchemaOnlyCommandBehavior.cs illustrates how to read a table schema */ using System; using System.Data; using System

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Executing SELECT Statements and TableDirect Commands phần 2

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
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. Table 8.7: FOR XML KEYWORDS 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 element. Column values become attributes of the 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 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 Specifies that any binary data returned by the query is encoded in base BASE64 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: Notice that each of the 5 rows from the Products table is returned as XML.