Просмотр данных OLE DB средствами ADO.NET

Упражнение 6. Вычисляемые столбцы объектов DataTable

Данные, которые хранятся в БД в табличной форме, представляют собой базовую информацию. Но некоторые данные, интересующие пользователя, можно получить путем выполнения определенных действий над исходными данными и показать результат в отдельном столбце таблицы представления. Для этого существуют различные варианты SQL-запросов, включающие в себя различные функции и агрегатные выражения. Например, команда

SELECT EmployeeID, LastName + ', ' + FirstName AS FullName FROM Employees

формирует в кэше из таблицы Employees два столбца, первый из которых представляет столбец EmployeeID исходной таблицы, а второй - объединение данные двух исходных столбцов под псевдонимом FullName.

Команда

SELECT ProductID, ProductName, UnitPrice + UnitPrice * ? AS (Total Price)
    FROM Products

к двум исходным столбцам из таблицы Products добавляет вычислимый столбец TotalPrice, в котором содержится отпускная цена товара с учетом процента ставки налога, причем процент налога передается в команду как параметр.

Выборку данных выполняет поставщик на стороне базы данных и в кэш поступают уже готовые данные. Дополнительный столбец пересылается в открытом потоке в память компьютера, что может перегружать трафик при большом количестве запросов и большом объеме загружаемых данных. В ADO.NET существуют средства выполнения подобной работы на стороне клиента, когда в компьютер поступают базовые столбцы таблицы и на лету к ним присоединяются вычислимые сторбцы, нужные пользователю для работы.

В данном упражнении рассматриваются средства присоединения к загружаемой таблице дополнительных столбцов, вычисляемых в коде приложения. Упражнение построено на примере вычисления отпускной цены товара из таблицы Products. Структура данных таблицы Products приведена на рисунке

Вычислимые столбцы нужно добавлять к объекту таблицы до заполнения ее данными из поставщика. Отдельные столбцы представляются в объектной модели таблицы классом DataColumn. Новый объект DataColumn добавляется в таблицу методом Add() свойства-коллекции DataTable.Columns.

Добавляемый в таблицу объект столбца должен содержать имя столбца (свойство ColumnName ), тип данных (свойство DataType ) и выражение (свойство Expression ) при необходимости. Выражение используется для фильтрации строк, вычисления значений в столбцах на основании других столбцов или для создания агрегированного (объединенного) столбца. Эти данные передаются в конструктор при создании объекта столбца или сразу в одну из перегрузок метода Add() при добавлении в коллекцию столбцов таблицы.

Типы данных, которые могут быть значениями свойства DataColumn.DataType, ограничиваются следующим списком пространства имен System:

Свойство DataColumn.DefaultValue показывает, какое значение имеет по умолчанию данный столбец. Если столбцу не присвоено никакого значения, а значение по умолчанию не определено, то столбец имеет значение System.DBNull. Поскольку типы данных реляционной таблицы БД и виртуальной таблицы объектной модели могут быть разными, рекомендуется явно привести тип столбца к нужному вычислимому типу. И это нужно сделать до загрузки объектной таблицы!!!

Таблица DataTable, помимо прочего, имеет три коллекции в качестве свойств только для чтения:

• Columns - содержит объекты столбцов вместе с их именами и типом
• Rows - содержит фактические данные, упакованные в объектах строк
• Constraints - содержит экземпляры классов ForeignKeyConstraint или UniqueConstraint, задающих свойства в отношении ключевых или уникальных значений полей

После добавления вычислимого столбца в коллекцию Columns объекта таблицы к нему можно обращаться в Columns по имени или порядковому номеру. Для иллюстрации сказанного выполните данное упражнение в следующем порядке:

• Добавьте к решению проект оконного приложения с именем WinForms6 и назначьте его стартовым
• Настройте пользовательский интерфейс в соответствии с таблицей

• Заполните файл Form1.cs проекта WinForms6 следующим образом

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
    
// Дополнительные пространства имен
using System.Data.OleDb;
using System.Data.Common;
using System.Collections;
    
namespace WinForms6
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
    
            LoadProducts();
        }
    
        // Строка соединения к БД с абсолютным путем, определяемым сборкой
        String ConnectionString()
        {
            // Используем построитель строки подключения 
            OleDbConnectionStringBuilder objConnectionStringBuilder =
                new OleDbConnectionStringBuilder();
            objConnectionStringBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
            objConnectionStringBuilder.DataSource =
                Application.StartupPath.ToString() + @"\Data\Northwind.mdb";
    
            return objConnectionStringBuilder.ToString();
        }
    
        void LoadProducts()
        {
            // Формируем поставщик 
            OleDbCommand selectCommand = new OleDbCommand();
            selectCommand.CommandText = 
                "SELECT ProductID, ProductName, UnitPrice AS Price FROM Products";
            selectCommand.Connection = new OleDbConnection(ConnectionString());
            selectCommand.CommandType = CommandType.Text;
            OleDbDataAdapter adapter = new OleDbDataAdapter(selectCommand);
            
            // Создаем объект таблицы
            DataTable tableProducts = new DataTable();  
          
            // Задаем тип столбца, подлежащего заполнению данными из БД
            // (ДО ЕГО ЗАПОЛНЕНИЯ!!!)
            tableProducts.Columns.Add("Price");
            tableProducts.Columns["Price"].DataType = typeof(System.Decimal);
    
            // Добавляем и настраиваем вычислимый столбец для величины наценки
            tableProducts.Columns.Add("Margin", typeof(System.Decimal));
            tableProducts.Columns[1].Expression = "Price * 0.086";
    
            // Добавляем  и настраиваем вычислимый столбец для потребительской цены
            DataColumn totalPrice = new DataColumn("Total Price");
            totalPrice.DataType = System.Type.GetType("System.Decimal");
            totalPrice.Expression = "Price + Margin";
            tableProducts.Columns.Add(totalPrice);
            
            // Заполняем таблицу
            adapter.Fill(tableProducts);
    
            // Связываем таблицу с элементом отображения
            dataGridView.DataSource = tableProducts;
    
            Object result = tableProducts.Compute("Sum(Margin)", null);
            MessageBox.Show(String.Format("Суммарная наценка на товар\n"
                + "(по курсу 30 рублей за доллар): {0:C}",// Спецификатор денежного формата
                Convert.ToDecimal(result) * 30.0m));
    
            result = tableProducts.Compute("Count(Price)", "Price < 10");
            MessageBox.Show(String.Format("Количество товара с ценой меньше 10$: {0} шт.", result));
        }
    }
}

• Запустите приложение - результат будет таким