using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; using System.IO; using System.Xml; using System.Diagnostics; using System.Threading; using System.Globalization; /*===================================================================== File: ProductsDB.cs for Adventure Works Cycles Storefront Sample Summary: Middle tier component for browsing saleable items. Date: June 16, 2003 --------------------------------------------------------------------- This file is part of the Microsoft SQL Server Code Samples. Copyright (C) Microsoft Corporation. All rights reserved. This source code is intended only as a supplement to Microsoft Development Tools and/or on-line documentation. See these other materials for detailed information regarding Microsoft code samples. THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. ======================================================= */ namespace Microsoft.Samples.SqlServer { //******************************************************* // // ProductDetails Class // // A simple data class that encapsulates details about // a particular product inside the AdventureWorks // database. // // Note that it is bad style to expose public fields. So // instead we maintain private fields with corresponding // public properties. This helps avoid binary // incompatabilities if the public fields were to have to // be changed to properties later. // //******************************************************* public class ProductDetails { private String modelNumber; private String modelName; private String productImage; private String unitCost; private String description; // The fields above are exposed publically as properties // There is nothing fancy going on here, it is just better // practice to not expose public fields. public String ModelNumber { get { return modelNumber; } set { modelNumber = value; } } public String ModelName { get { return modelName; } set { modelName = value; } } public String ProductImage { get { return productImage; } set { productImage = value; } } public String UnitCost { get { return unitCost; } set { unitCost = value; } } public String Description { get { return description; } set { description = value; } } } //******************************************************* // // ProductsDB Class // // Business/Data Logic Class that encapsulates all data // logic necessary to query products within // the AdventureWorks database. // //******************************************************* public class ProductsDB { //******************************************************* // // ProductsDB.GetProductTree() Method <a name="GetProductTree"></a> // // The GetProductCategories method returns a string that exposes all // product categories and subcategories (and their CategoryIDs) within the Adventure Works Cycles // database. The string contains this information formatted as XML, which // is used in the menu tree control. // // Other relevant sources: // + <a href="usp_ProductCategoryList.htm" style="color:green">usp_ProductCategoryList Stored Procedure</a> // //******************************************************* public DataSet GetProductTree() { // Create Instance of Connection and Command Object SqlConnection myConnection = null; try { myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_ProductCategoryList", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Execute the command myConnection.Open(); DataSet productsDs = new DataSet("Products"); productsDs.Locale = CultureInfo.InvariantCulture; SqlDataAdapter productsDa = new SqlDataAdapter(myCommand); productsDa.Fill(productsDs, "ProductCategory"); productsDs.Tables[1].TableName = "ProductSubcategory"; //TODO: Switch sproc column name, then fix up relation column DataRelation categoryRel = new DataRelation("CategorySubCategories", productsDs.Tables["ProductCategory"].Columns["ProductCategoryID"], productsDs.Tables["ProductSubcategory"].Columns["ProductCategoryID"]); productsDs.Relations.Add(categoryRel); return productsDs; } finally { if (myConnection != null) myConnection.Close(); } } //******************************************************* // // ProductsDB.GetProducts() Method <a name="GetProducts"></a> // // The GetProducts method returns a DataSet which contains all products within a specified // product category. // // Other relevant sources: // + <a href="usp_ProductsBySubcategory.htm" style="color:green">usp_ProductsBySubcategory Stored Procedure</a> // //******************************************************* public SqlDataReader GetProducts(int subCategoryID) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString); SqlCommand myCommand = myConnection.CreateCommand(); myCommand.CommandText = "usp_ProductsBySubcategory"; // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterSubcategoryID = new SqlParameter("@SubcategoryID", SqlDbType.Int, 4); parameterSubcategoryID.Value = subCategoryID; myCommand.Parameters.Add(parameterSubcategoryID); SqlParameter parameterCurrencyCode = new SqlParameter("@Culture", SqlDbType.NVarChar, 10); parameterCurrencyCode.Value = CultureInfo.CurrentUICulture.Name; myCommand.Parameters.Add(parameterCurrencyCode); // Execute the stored procedure and Return the datareader result myConnection.Open(); return myCommand.ExecuteReader(CommandBehavior.CloseConnection); } //******************************************************* // // ProductsDB.GetProductDetails() Method <a name="GetProductDetails"></a> // // The GetProductDetails method returns a ProductDetails // struct containing specific details about a specified // product within the AdventureWorks Database. // // Other relevant sources: // + <a href="usp_ProductDetail.htm" style="color:green">usp_ProductDetail Stored Procedure</a> // //******************************************************* public ProductDetails GetProductDetails(int productID, String language) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_ProductDetail", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4); parameterProductID.Value = productID; myCommand.Parameters.Add(parameterProductID); SqlParameter parameterLanguage = new SqlParameter("@Language", SqlDbType.NChar, 2); parameterLanguage.Value = language; myCommand.Parameters.Add(parameterLanguage); SqlParameter parameterCurrencyCode = new SqlParameter("@Culture", SqlDbType.NVarChar, 10); parameterCurrencyCode.Value = CultureInfo.CurrentUICulture.Name; myCommand.Parameters.Add(parameterCurrencyCode); SqlParameter parameterModelNumber = new SqlParameter("@ModelNumber", SqlDbType.NVarChar, 25); parameterModelNumber.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterModelNumber); SqlParameter parameterModelName = new SqlParameter("@ModelName", SqlDbType.NVarChar, 50); parameterModelName.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterModelName); SqlParameter parameterProductImage = new SqlParameter("@ProductImage", SqlDbType.NVarChar, 50); parameterProductImage.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterProductImage); SqlParameter parameterUnitCost = new SqlParameter("@UnitCost", SqlDbType.NVarChar, 20); parameterUnitCost.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterUnitCost); SqlParameter parameterDescription = new SqlParameter("@Description", SqlDbType.NVarChar, 3800); parameterDescription.Direction = ParameterDirection.Output; myCommand.Parameters.Add(parameterDescription); // Open the connection and execute the Command try { myConnection.Open(); myCommand.ExecuteNonQuery(); } finally { if (myConnection.State != ConnectionState.Closed) myConnection.Close(); } //Fall back to US English descriptions if non-US English is not available if (parameterDescription.Value.Equals(DBNull.Value)) { if (language == "EN") parameterDescription.Value = ""; else return GetProductDetails(productID, "EN"); } // Create and Populate ProductDetails Struct using // Output Params from the SPROC ProductDetails myProductDetails = new ProductDetails(); myProductDetails.ModelNumber = (string)parameterModelNumber.Value; myProductDetails.ModelName = (string)parameterModelName.Value; if (parameterProductImage.Value.Equals(DBNull.Value)) myProductDetails.ProductImage = "unknown.gif"; else myProductDetails.ProductImage = ((string)parameterProductImage.Value).Trim(); myProductDetails.UnitCost = (String)parameterUnitCost.Value; myProductDetails.Description = ((string)parameterDescription.Value).Trim(); return myProductDetails; } //******************************************************* // // ProductsDB.GetMostPopularProductsOfWeek() Method <a name="GetMostPopularProductsOfWeek"></a> // // The GetMostPopularProductsOfWeek method returns a struct containing a // forward-only, read-only DataReader containing the most popular products // of the week within the AdventureWorks database. // The SQLDataReaderResult struct also returns the // SQL connection, which must be explicitly closed after the // data from the DataReader is bound into the controls. // // Other relevant sources: // + <a href="usp_ProductsMostPopular.htm" style="color:green">usp_ProductsMostPopular Stored Procedure</a> // //******************************************************* public SqlDataReader GetMostPopularProductsOfWeek() { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString); SqlCommand myCommand = new SqlCommand("usp_ProductsMostPopular", myConnection); // Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure; // Execute the command myConnection.Open(); SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection); // Return the datareader result return result; } //******************************************************* // // ProductsDB.SearchProductDescriptions() Method <a name="SearchProductDescriptions"></a> // // The SearchProductDescriptions method returns a data set containing // a list of all // products whose name and/or description contains the specified search // string. // // Other relevant sources: // + <a href="usp_ProductSearch.htm" style="color:green">usp_ProductSearch Stored Procedure</a> // //******************************************************* public DataSet SearchProductDescriptions(string searchText, string language) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString); SqlDataAdapter myCommand = new SqlDataAdapter("usp_ProductSearch", myConnection); // Mark the Command as a SPROC myCommand.SelectCommand.CommandType = CommandType.StoredProcedure; // Add Parameters to SPROC SqlParameter parameterSearch = new SqlParameter("@Search", SqlDbType.NVarChar, 255); parameterSearch.Value = searchText; myCommand.SelectCommand.Parameters.Add(parameterSearch); SqlParameter parameterLanguage = new SqlParameter("@Language", SqlDbType.NChar, 2); parameterLanguage.Value = language; myCommand.SelectCommand.Parameters.Add(parameterLanguage); SqlParameter parameterCurrencyCode = new SqlParameter("@Culture", SqlDbType.NVarChar, 10); parameterCurrencyCode.Value = CultureInfo.CurrentUICulture.Name; myCommand.SelectCommand.Parameters.Add(parameterCurrencyCode); // Execute the command DataSet result = new DataSet("SearchResults"); result.Locale = CultureInfo.InvariantCulture; myCommand.Fill(result); // If we get no hits, then try English if (result.Tables[0].Rows.Count == 0) { if (language != "EN") return SearchProductDescriptions(searchText, "EN"); } // Return the DataSet result return result; } } }