Добавление дополнительных столбцов DataTable (VB)
При использовании мастера адаптера таблиц для создания типизированного набора данных соответствующая таблица 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
мыши . В контекстном меню выберите Добавить, а затем — Столбец.
Рис. 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 создать эту хранимую процедуру. Выберите параметр Создать хранимую процедуру и нажмите кнопку Далее.
Рис. 3. Указание мастеру tableAdapter создать хранимую процедуру для нас (щелкните, чтобы просмотреть полноразмерное изображение)
На следующем экране, показанном на рис. 4, мастер запрашивает тип добавляемого запроса. GetProductsWithPriceQuartile
Так как метод возвращает все столбцы и записи из Products
таблицы, выберите параметр 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
и нажмите кнопку Далее.
Рис. 5. Имя хранимой процедуры Products_SelectWithPriceQuartile
(щелкните, чтобы просмотреть полноразмерное изображение)
Наконец, нам будет предложено присвоить имена методам TableAdapter. Оставьте флажки Заполнить dataTable и Вернуть dataTable и назовите методы FillWithPriceQuartile
и GetProductsWithPriceQuartile
.
Рис. 6. Назовите методы TableAdapter и нажмите кнопку Готово (щелкните для просмотра полноразмерного изображения)
SELECT
Указав запрос и хранимую процедуру и методы TableAdapter с именами, нажмите кнопку Готово, чтобы завершить работу мастера. На этом этапе может возникнуть предупреждение мастера о том, что OVER
конструкция или инструкция SQL не поддерживаются. Эти предупреждения можно игнорировать.
После завершения работы мастера TableAdapter должен включать FillWithPriceQuartile
методы и GetProductsWithPriceQuartile
, а база данных — хранимую процедуру с именем Products_SelectWithPriceQuartile
. Убедитесь, что TableAdapter действительно содержит этот новый метод и что хранимая процедура правильно добавлена в базу данных. Если при проверке базы данных хранимая процедура не отображается, попробуйте щелкнуть правой кнопкой мыши папку Хранимые процедуры и выбрать Команду Обновить.
Рис. 7. Проверка добавления нового метода в TableAdapter
Рис. 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 значение (Нет) .
Рис. 9. Настройка ObjectDataSource для использования ProductsBLLWithSprocs
класса (щелкните для просмотра полноразмерного изображения)
Рис. 10. Получение сведений о продукте GetProductsWithPriceQuartile
из метода (щелкните для просмотра полноразмерного изображения)
После завершения работы мастера настройки источника данных Visual Studio автоматически добавит BoundField или CheckBoxField в GridView для каждого поля данных, возвращаемого методом . Одним из этих полей данных является PriceQuartile
, который является столбцом, добавленным в на шаге ProductsDataTable
1.
Измените поля GridView, удалив все поля, кроме ProductName
, UnitPrice
и PriceQuartile
BoundFields. UnitPrice
Настройте BoundField для форматирования его значения в виде валюты и выравнивания по правому и центру UnitPrice
PriceQuartile
соответственно. Наконец, обновите остальные свойства 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
, который использовал хранимую GetProductsWithPriceQuartile
Products_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.