Добавление дополнительных столбцов DataTable (C#)

  • Статья

Скотт Митчелл

Загрузить PDF-файл

При использовании мастера TableAdapter для создания типизированного набора данных соответствующая таблица Данных содержит столбцы, возвращаемые запросом main базы данных. Но бывают случаи, когда в таблицу DataTable необходимо включить дополнительные столбцы. В этом руководстве мы узнаем, почему рекомендуется использовать хранимые процедуры, если требуются дополнительные столбцы DataTable.

Введение

При добавлении TableAdapter в типизированный набор данных соответствующая схема DataTable определяется запросом main TableAdapter. Например, если запрос main возвращает поля данных A, B и C, dataTable будет содержать три соответствующих столбца с именами A, B и C. Помимо main запроса, TableAdapter может включать дополнительные запросы, которые возвращают, возможно, подмножество данных на основе какого-то параметра. Например, в дополнение к запросу ProductsTableAdapter main, который возвращает сведения обо всех продуктах, он также содержит такие методы, как GetProductsByCategoryID(categoryID) и GetProductByProductID(productID), которые возвращают сведения о конкретном продукте на основе предоставленного параметра.

Модель того, что схема DataTable отражает запрос main TableAdapter, работает хорошо, если все методы TableAdapter возвращают те же или меньше полей данных, чем указанные в запросе main. Если метод TableAdapter должен возвращать дополнительные поля данных, необходимо соответствующим образом развернуть схему DataTable. В учебнике Master/Detail Using a Bulleted List of Master Records with a Details DataList (Использование маркированного списка главных записей с подробными данными) мы добавили метод в CategoriesTableAdapter , возвращающий CategoryIDполя данных , и Description , CategoryNameопределенные в запросе main плюс NumberOfProducts, дополнительное поле данных, которое сообщает количество продуктов, связанных с каждой категорией. Мы вручную добавили новый столбец в , CategoriesDataTable чтобы записать NumberOfProducts значение поля данных из этого нового метода.

Как описано в руководстве По отправке файлов, необходимо проявлять большую осторожность с TableAdapters, которые используют нерегламентированные инструкции SQL и имеют методы, поля данных которых не соответствуют main запросу. При повторном запуске мастера настройки TableAdapter он обновит все методы TableAdapter таким образом, чтобы их список полей данных соответствовал запросу main. Следовательно, любые методы с настраиваемыми списками столбцов будут отменить изменения в список столбцов запроса main и не возвращают ожидаемые данные. Эта проблема не возникает при использовании хранимых процедур.

В этом руководстве мы рассмотрим, как расширить схему DataTable для включения дополнительных столбцов. Из-за хрупкости TableAdapter при использовании нерегламентированных инструкций SQL в этом руководстве будут использоваться хранимые процедуры. Дополнительные сведения о настройке TableAdapter в TableAdapter см. в руководстве Создание новых хранимых процедур для типизированных наборов данных.

Шаг 1. Добавление столбцаPriceQuartileвProductsDataTable

В учебнике Создание новых хранимых процедур для адаптеров tableadapters typed DataSet мы создали типизированный набор данных с именем NorthwindWithSprocs. Этот набор данных в настоящее время содержит две dataTable: ProductsDataTable и EmployeesDataTable. Имеет ProductsTableAdapter следующие три метода:

  • GetProducts— запрос main, который возвращает все записи из таблицы.Products
  • GetProductsByCategoryID(categoryID) — возвращает все продукты с указанным идентификатором categoryID.
  • GetProductByProductID(productID) — возвращает конкретный продукт с указанным productID.

Запрос main и два дополнительных метода возвращают один и тот же набор полей данных, а именно все столбцы из Products таблицы. Нет коррелированных вложенных запросов или JOIN запросов, извлекающих связанные данные из Categories таблиц или Suppliers . Таким образом ProductsDataTable , имеет соответствующий столбец для каждого поля в Products таблице.

В этом руководстве мы добавим метод в ProductsTableAdapter с именем GetProductsWithPriceQuartile , который возвращает все продукты. В дополнение к стандартным полям данных о продукте также GetProductsWithPriceQuartile будет включать PriceQuartile поле данных, указывающее, под каким квартилем падает цена на продукт. Например, те продукты, цены которых находятся в самых дорогих 25%, будут иметь PriceQuartile значение 1, а те, чьи цены падают в нижние 25% будут иметь значение 4. Однако, прежде чем создавать хранимую процедуру для возврата этих сведений, необходимо сначала обновить ProductsDataTable , чтобы включить столбец для хранения PriceQuartile результатов при GetProductsWithPriceQuartile использовании метода.

NorthwindWithSprocs Откройте DataSet и щелкните правой ProductsDataTableкнопкой мыши . Выберите Добавить в контекстном меню и выберите Столбец.

Добавление нового столбца в ProductsDataTable

Рис. 1. Добавление нового столбца в ProductsDataTable (щелкните для просмотра полноразмерного изображения)

При этом в dataTable будет добавлен новый столбец с именем Column1 типа System.String. Необходимо обновить имя этого столбца на PriceQuartile, а его тип — на , System.Int32 так как он будет использоваться для хранения чисел в диапазоне от 1 до 4. Выберите только что добавленный столбец в ProductsDataTable и в окно свойств присвойте Name свойству значение PriceQuartile, а свойству DataType — значение System.Int32.

Задание свойств Name и DataType нового столбца

Рис. 2. Задание новых столбцов Name и DataType свойств (щелкните для просмотра полноразмерного изображения)

Как показано на рисунке 2, можно задать дополнительные свойства, например, должны ли значения в столбце быть уникальными, если столбец является столбцом с автоматическим приращением, разрешены ли значения базы данных NULL и т. д. Оставьте для этих значений значения по умолчанию.

Шаг 2. СозданиеGetProductsWithPriceQuartileметода

Теперь, когда ProductsDataTable объект был обновлен для включения столбца PriceQuartile , мы готовы создать GetProductsWithPriceQuartile метод . Для начала щелкните правой кнопкой мыши tableAdapter и выберите в контекстном меню пункт Добавить запрос. Откроется мастер настройки запросов TableAdapter, который сначала предлагает нам определить, нужно ли использовать нерегламентированные инструкции SQL или новую или существующую хранимую процедуру. Так как у нас еще нет хранимой процедуры, возвращающей данные квартиля цены, позвольте TableAdapter создать эту хранимую процедуру. Выберите параметр Создать хранимую процедуру и нажмите кнопку Далее.

Указание мастеру TableAdapter создать хранимую процедуру для нас

Рис. 3. Указание мастеру TableAdapter создать хранимую процедуру для нас (щелкните для просмотра полноразмерного изображения)

На следующем экране, показанном на рис. 4, мастер запрашивает тип запроса для добавления. GetProductsWithPriceQuartile Так как метод возвращает все столбцы и записи из Products таблицы, выберите параметр SELECT, возвращающий строки, и нажмите кнопку Далее.

Наш запрос будет инструкцией SELECT, которая возвращает несколько строк

Рис. 4. Наш запрос будет оператором, возвращающим SELECT несколько строк (щелкните для просмотра полноразмерного изображения)

Далее нам будет предложено ввести SELECT запрос. Введите следующий запрос в мастер:

SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued,
       NTILE(4) OVER (ORDER BY UnitPrice DESC) as PriceQuartile
FROM Products

В приведенном выше запросе используется новая NTILE функция SQL Server 2005 для разделения результатов на четыре группы, где группы определяются значениямиUnitPrice, отсортированы по убыванию.

К сожалению, построитель запросов не знает, как проанализировать OVER ключевое слово и отобразит ошибку при анализе приведенного выше запроса. Поэтому введите приведенный выше запрос непосредственно в текстовое поле мастера без использования построителя запросов.

Примечание

Дополнительные сведения о других функциях ранжирования NTILE и SQL Server 2005 см. в разделах ROW_NUMBER (Transact-SQL) и в разделе Ранжирующие функции электронной документации по SQL Server 2005 года.

После ввода SELECT запроса и нажатия кнопки Далее мастер просит указать имя создаваемой хранимой процедуры. Назовите новую хранимую процедуру Products_SelectWithPriceQuartile и нажмите кнопку Далее.

Назовите хранимую процедуру Products_SelectWithPriceQuartile

Рис. 5. Имя хранимой процедуры Products_SelectWithPriceQuartile (щелкните для просмотра полноразмерного изображения)

Наконец, нам будет предложено назвать методы TableAdapter. Оставьте флажки Fill a DataTable и Return a DataTable установлен и назовите методы FillWithPriceQuartile и GetProductsWithPriceQuartile.

Назовите методы TableAdapter и нажмите кнопку Готово.

Рис. 6. Назовите методы TableAdapter и нажмите кнопку Готово (щелкните для просмотра полноразмерного изображения)

SELECT Указав запрос и хранимую процедуру и методы TableAdapter с именем, нажмите кнопку Готово, чтобы завершить работу мастера. На этом этапе может возникнуть предупреждение мастера о том, что OVER конструкция ИЛИ инструкция SQL не поддерживаются. Эти предупреждения можно игнорировать.

После завершения работы мастера TableAdapter должен включать FillWithPriceQuartile методы и GetProductsWithPriceQuartile , а база данных должна содержать хранимую процедуру с именем Products_SelectWithPriceQuartile. Убедитесь, что TableAdapter действительно содержит этот новый метод и что хранимая процедура была правильно добавлена в базу данных. Если при проверке базы данных хранимая процедура не отображается, щелкните правой кнопкой мыши папку Хранимые процедуры и выберите Обновить.

Убедитесь, что в TableAdapter добавлен новый метод.

Рис. 7. Проверка добавления нового метода в TableAdapter

Убедитесь, что база данных содержит хранимую процедуру Products_SelectWithPriceQuartile.

Рис. 8. Убедитесь, что база данных содержит хранимую Products_SelectWithPriceQuartile процедуру (щелкните для просмотра полноразмерного изображения)

Примечание

Одним из преимуществ использования хранимых процедур вместо нерегламентированных инструкций SQL является то, что повторное выполнение мастера настройки TableAdapter не приведет к изменению списков столбцов хранимых процедур. Убедитесь в этом, щелкнув правой кнопкой мыши TableAdapter, выбрав в контекстном меню параметр Настроить, чтобы запустить мастер, а затем нажмите кнопку Готово, чтобы завершить работу. Затем перейдите к базе данных и просмотрите хранимую Products_SelectWithPriceQuartile процедуру. Обратите внимание, что его список столбцов не был изменен. Если бы мы использовали нерегламентированные инструкции SQL, повторное выполнение мастера настройки TableAdapter вернуло бы этот список столбцов запроса в соответствии со списком столбцов main запроса, тем самым удалив инструкцию NTILE из запроса, используемого GetProductsWithPriceQuartile методом .

При вызове метода уровня GetProductsWithPriceQuartile доступа к данным TableAdapter выполняет Products_SelectWithPriceQuartile хранимую процедуру и добавляет строку в ProductsDataTable для каждой возвращаемой записи. Поля данных, возвращаемые хранимой процедурой, сопоставляются со ProductsDataTable столбцами s. Так как из хранимой PriceQuartile процедуры возвращается поле данных, его значение присваивается столбцу ProductsDataTable s PriceQuartile .

Для методов TableAdapter, запросы которых не возвращают PriceQuartile поле данных, PriceQuartile значением столбца является значение, заданное его DefaultValue свойством . Как показано на рисунке 2, для этого значения задано значение DBNullпо умолчанию. Если вы предпочитаете другое значение по умолчанию, просто задайте DefaultValue свойство соответствующим образом. Просто убедитесь, что значение является допустимым DefaultValue для столбца s DataType (т. е. System.Int32 для столбца PriceQuartile ).

На этом этапе мы выполнили необходимые действия по добавлению дополнительного столбца в dataTable. Чтобы убедиться, что этот дополнительный столбец работает должным образом, создадим страницу ASP.NET, на котором отображаются название, цена и квартил каждого продукта. Прежде чем это сделать, сначала необходимо обновить уровень бизнес-логики, чтобы включить метод, который вызывает метод DAL s GetProductsWithPriceQuartile . Далее мы обновим BLL на шаге 3, а затем создадим страницу ASP.NET на шаге 4.

Шаг 3. Расширение уровня бизнес-логики

Прежде чем использовать новый GetProductsWithPriceQuartile метод из уровня представления, необходимо сначала добавить соответствующий метод в BLL. ProductsBLLWithSprocs Откройте файл класса и добавьте следующий код:

[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetProductsWithPriceQuartile()
{
    return Adapter.GetProductsWithPriceQuartile();
}

Как и другие методы извлечения данных в ProductsBLLWithSprocs, GetProductsWithPriceQuartile метод просто вызывает соответствующий GetProductsWithPriceQuartile метод DAL и возвращает его результаты.

Шаг 4. Отображение сведений о квартиле цены на веб-странице ASP.NET

Завершив добавление BLL, мы готовы создать страницу ASP.NET, на которую будет отображаться квартиль цен для каждого продукта. Откройте страницу AddingColumns.aspx в папке AdvancedDAL и перетащите элемент GridView с панели элементов на Designer, установив для его ID свойства значение Products. Из смарт-тега GridView привяжите его к новому объекту ObjectDataSource с именем ProductsDataSource. Настройте ObjectDataSource для использования ProductsBLLWithSprocs метода класса .GetProductsWithPriceQuartile Так как это будет сетка только для чтения, установите для раскрывающихся списков на вкладках UPDATE, INSERT и DELETE значение (Нет) .

Настройка ObjectDataSource для использования класса ProductsBLLWithSprocs

Рис. 9. Настройка ObjectDataSource для использования ProductsBLLWithSprocs класса (щелкните для просмотра полноразмерного изображения)

Получение сведений о продукте из метода GetProductsWithPriceQuartile

Рис. 10. Получение сведений о продукте GetProductsWithPriceQuartile из метода (щелкните для просмотра полноразмерного изображения)

После завершения работы мастера настройки источника данных Visual Studio автоматически добавит BoundField или CheckBoxField в GridView для каждого поля данных, возвращаемого методом . Одним из этих полей данных является PriceQuartile, который является столбцом, добавленным в на шаге ProductsDataTable 1.

Измените поля GridView, удалив все поля, кроме ProductName, UnitPriceи PriceQuartile BoundFields. UnitPrice Настройте BoundField для форматирования его значения в виде валюты и выравнивания BoundFields PriceQuartile по правому UnitPrice и центру соответственно. Наконец, обновите остальные свойства BoundFields HeaderText на Product, Price и Price Quartile соответственно. Кроме того, проверка флажок Включить сортировку из смарт-тега GridView.

После этих изменений декларативная разметка GridView и ObjectDataSource должна выглядеть следующим образом:

<asp:GridView ID="Products" runat="server" AllowSorting="True"
    AutoGenerateColumns="False" DataKeyNames="ProductID" 
    DataSourceID="ProductsDataSource">
    <Columns>
        <asp:BoundField DataField="ProductName" HeaderText="Product" 
            SortExpression="ProductName" />
        <asp:BoundField DataField="UnitPrice" DataFormatString="{0:c}" 
            HeaderText="Price" HtmlEncode="False" 
            SortExpression="UnitPrice">
            <ItemStyle HorizontalAlign="Right" />
        </asp:BoundField>
        <asp:BoundField DataField="PriceQuartile" HeaderText="Price Quartile" 
            SortExpression="PriceQuartile">
            <ItemStyle HorizontalAlign="Center" />
        </asp:BoundField>
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ProductsDataSource" runat="server" 
    OldValuesParameterFormatString="original_{0}"
    SelectMethod="GetProductsWithPriceQuartile" 
    TypeName="ProductsBLLWithSprocs">
</asp:ObjectDataSource>

На рисунке 11 показана эта страница при посещении через браузер. Обратите внимание, что изначально товары упорядочены по цене в порядке убывания, при этом каждому продукту присваивается соответствующее PriceQuartile значение. Конечно, эти данные можно отсортировать по другим критериям, при этом значение столбца Price Quartile по-прежнему отражает рейтинг продукта по отношению к цене (см. рис. 12).

Продукты упорядочены по их ценам

Рис. 11. Продукты упорядочены по их ценам (щелкните, чтобы просмотреть полноразмерное изображение)

Продукты упорядочены по их именам

Рис. 12. Продукты упорядочены по их именам (щелкните, чтобы просмотреть полноразмерное изображение)

Примечание

С помощью нескольких строк кода можно расширить GridView, чтобы он закрасывал строки продуктов на основе их PriceQuartile значения. Мы можем окрасить эти продукты в первом квартиле светло-зеленым цветом, во втором — светло-желтым и т. д. Я рекомендую вам воспользоваться моментом, чтобы добавить эту функцию. Если вам требуется освежить форматирование GridView, ознакомьтесь с руководством Пользовательское форматирование на основе данных .

Альтернативный подход — создание другого объекта TableAdapter

Как мы видели в этом руководстве, при добавлении метода в TableAdapter, который возвращает поля данных, отличные от указанных в запросе main, можно добавить соответствующие столбцы в таблицу DataTable. Однако такой подход хорошо работает только в том случае, если в TableAdapter имеется небольшое количество методов, возвращающих различные поля данных, и если эти альтернативные поля данных не слишком сильно отличаются от main запроса.

Вместо того, чтобы добавлять столбцы в таблицу DataTable, можно добавить в dataSet еще один Объект TableAdapter, содержащий методы из первого Объекта TableAdapter, возвращающие различные поля данных. В этом руководстве вместо того, чтобы добавлять PriceQuartile столбец в ProductsDataTable (где он используется только методом ), мы могли бы добавить дополнительный объект TableAdapter к набору данных с именем ProductsWithPriceQuartileTableAdapter , который использовал Products_SelectWithPriceQuartile хранимую GetProductsWithPriceQuartile процедуру в качестве main запроса. ASP.NET страницы, необходимые для получения сведений о продукте с квартилем цены, будут использовать ProductsWithPriceQuartileTableAdapter, в то время как те страницы, которые не могли продолжать использовать ProductsTableAdapter.

При добавлении нового объекта TableAdapter таблицы DataTable остаются незатарнированные, а их столбцы точно зеркало поля данных, возвращаемые их методами TableAdapter. Однако дополнительные свойства TableAdapters могут привести к повторяющимся задачам и функциям. Например, если эти ASP.NET страницы, на которых отображается PriceQuartile столбец, также требуется для поддержки вставки, обновления и удаления, ProductsWithPriceQuartileTableAdapter необходимо правильно настроить свойства InsertCommand, UpdateCommandи DeleteCommand . Хотя эти свойства будут зеркало ProductsTableAdapter , эта конфигурация представляет собой дополнительный шаг. Кроме того, теперь существует два способа обновления, удаления или добавления продукта в базу данных — с помощью ProductsTableAdapter классов и ProductsWithPriceQuartileTableAdapter .

Скачивание этого руководства включает ProductsWithPriceQuartileTableAdapter класс в NorthwindWithSprocs DataSet, который иллюстрирует этот альтернативный подход.

Сводка

В большинстве случаев все методы в TableAdapter будут возвращать один и тот же набор полей данных, но бывают случаи, когда конкретному методу или двум может потребоваться возврат дополнительного поля. Например, в учебнике Master/Detail Using a Bulleted List of Master Records with a Details DataList (Использование маркированного списка главных записей с подробным списком данных) мы добавили к методу CategoriesTableAdapter , который в дополнение к полям данных запроса main возвращал NumberOfProducts поле, сообщающее количество продуктов, связанных с каждой категорией. В этом руководстве мы рассмотрели добавление метода в ProductsTableAdapter , возвращающего PriceQuartile поле в дополнение к полям данных запроса main. Чтобы записать дополнительные поля данных, возвращаемые методами TableAdapter, необходимо добавить соответствующие столбцы в таблицу DataTable.

Если вы планируете добавить столбцы в таблицу DataTable вручную, рекомендуется использовать хранимые процедуры в TableAdapter. Если TableAdapter использует нерегламентированные инструкции SQL, при запуске мастера настройки TableAdapter все методы в полях данных перечисляются отменить изменения поля данных, возвращаемые запросом main. Эта проблема не распространяется на хранимые процедуры, поэтому они рекомендуются и используются в этом руководстве.

Счастливое программирование!

Об авторе

Скотт Митчелл (Scott Mitchell), автор семи книг ASP/ASP.NET и основатель 4GuysFromRolla.com, работает с Веб-технологиями Майкрософт с 1998 года. Скотт работает независимым консультантом, тренером и писателем. Его последняя книга Sams Teach Yourself ASP.NET 2.0 в 24 часа. Его можно связать по адресу mitchell@4GuysFromRolla.com. или через его блог, который можно найти по адресу http://ScottOnWriting.NET.

Отдельная благодарность

Эта серия учебников была проверена многими полезными рецензентами. Ведущие рецензенты этого руководства: Рэнди Шмидт, Джеки Гур, Бернадетт Ли и Хилтон Гисеноу. Хотите ознакомиться с моими предстоящими статьями MSDN? Если да, опустите мне строку в mitchell@4GuysFromRolla.com.

НазадВперед