Поделиться через

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

  • Статья

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

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

При использовании мастера адаптера таблиц для создания типизированного набора данных соответствующая таблица DataTable содержит столбцы, возвращаемые запросом 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 значение поля данных из этого нового метода.

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

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

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

В учебнике Создание новых хранимых процедур для tableAdapters типизированного набора данных мы создали типизированный набор данных с именем 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 Откройте набор данных и щелкните правой кнопкой ProductsDataTableмыши . В контекстном меню выберите Добавить, а затем — Столбец.

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

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

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

Задание свойств Имени и Типа данных нового столбца

Рис. 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. Оставьте флажки Заполнить dataTable и Вернуть 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 свойством. Как показано на DBNullрисунке 2, для этого значения задано значение по умолчанию. Если вы предпочитаете другое значение по умолчанию, просто задайте DefaultValue свойство соответствующим образом. Просто убедитесь, что значение является допустимым DefaultValue для столбца s DataType (т. е. System.Int32 для столбца PriceQuartile ).

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

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

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

<System.ComponentModel.DataObjectMethodAttribute_
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsWithPriceQuartile() As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetProductsWithPriceQuartile()
End Function

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

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

Завершив добавление BLL, мы готовы создать ASP.NET страницу с квартилем цены для каждого продукта. Откройте страницу AddingColumns.aspx в папке AdvancedDAL и перетащите GridView с панели элементов на Designer, установив для его ID свойства значение Products. Из смарт-тега GridView привяжите его к новому объекту ObjectDataSource с именем ProductsDataSource. Настройте ObjectDataSource для использования ProductsBLLWithSprocs метода класса s 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 для форматирования его значения в виде валюты и выравнивания по правому и центру UnitPricePriceQuartile соответственно. Наконец, обновите остальные свойства 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 значение. Конечно, эти данные можно отсортировать по другим критериям с значением в столбце "Ценовая квартиль", отражающим ранжирование продукта по цене (см. рис. 12).

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

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

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

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

Примечание

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

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

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

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

При добавлении нового объекта TableAdapter таблицы DataTable остаются незатарессанными, а их столбцы точно зеркало поля данных, возвращаемые методами TableAdapter. Однако дополнительные Адаптеры таблиц могут привести к повторяющимся задачам и функциям. Например, если эти 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.

НазадВперед