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

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

Скачать в формате PDF

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

Введение

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

Модель, при которой схема DataTable отражает основной запрос TableAdapter, хорошо работает в том случае, если все методы TableAdapter возвращают такие же или меньшее количество полей данных, чем указано в основном запросе. Если метод TableAdapter должен возвращать дополнительные поля данных, необходимо развернуть схему DataTable соответствующим образом. В руководстве «Мастер/Детали с использованием маркированного списка мастер-записей и DataList деталей» мы добавили метод в CategoriesTableAdapter, который возвращает CategoryID, а также поля данных CategoryName, Description, определенные в основном запросе, плюс NumberOfProducts — дополнительное поле данных, которое сообщает о количестве продуктов, связанных с каждой категорией. Мы вручную добавили новый столбец в CategoriesDataTable, чтобы записать NumberOfProducts значение поля данных из этого нового метода.

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

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

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

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

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

Основной запрос и два дополнительных метода возвращают один и тот же набор полей данных, а именно все столбцы из 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. Оставьте оба флажка "Fill a DataTable" и "Return a DataTable" установленными и назовите методы FillWithPriceQuartile и GetProductsWithPriceQuartile.

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

С указанным запросом SELECT и хранимой процедурой и методами TableAdapter нажмите кнопку "Готово", чтобы завершить работу мастера. На этом этапе вы можете получить одно или два предупреждения от мастера, заявляющего, что конструкция или оператор SQL не поддерживается. Эти предупреждения можно игнорировать.

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

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

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

Замечание

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

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

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

На этом этапе мы выполнили необходимые действия для добавления дополнительного столбца в DataTable. Чтобы убедиться, что этот дополнительный столбец работает должным образом, давайте создадим страницу ASP.NET, отображающую имя продукта, цену и ценовую квартиль. Перед тем как это сделать, необходимо сначала обновить слой бизнес-логики, чтобы включить метод, вызывающий метод DAL GetProductsWithPriceQuartile. Далее мы обновим BLL на шаге 3, а затем создадим страницу 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 из панели элементов в конструктор, присвоив свойству ID значение Products. Из смарт-тега GridView привязать его к новому объекту ObjectDataSource с именем ProductsDataSource. Настройте ObjectDataSource для использования ProductsBLLWithSprocs метода класса 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 BoundField, соответственно, по правому и по центру. Наконец, обновите оставшиеся свойства 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, который возвращает поля данных, отличные от полей данных, указанных в основном запросе, можно добавить соответствующие столбцы в DataTable. Такой подход, однако, хорошо работает только в том случае, если в TableAdapter есть небольшое количество методов, возвращающих разные поля данных, и если эти альтернативные поля данных не отличаются слишком много от основного запроса.

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

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

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

Сводка

В большинстве случаев все методы в TableAdapter возвращают один и тот же набор полей данных, но иногда требуется возвращать дополнительное поле. Например, в учебном пособии Master/Detail с использованием маркированного списка мастер-записей с DataList деталей мы добавили метод в CategoriesTableAdapter, который, помимо данных основного запроса, возвращал поле NumberOfProducts, указывающее количество продуктов, связанных с каждой категорией. В этом руководстве мы рассмотрели добавление метода в ProductsTableAdapter, который возвращает PriceQuartile в дополнение к полям данных основного запроса. Чтобы записать дополнительные поля данных, возвращаемые методами TableAdapter, необходимо добавить соответствующие столбцы в DataTable.

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

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

Сведения о авторе

Скотт Митчелл, автор семи книг ASP/ASP.NET и основатель 4GuysFromRolla.com, работает с технологиями Microsoft Web с 1998 года. Скотт работает независимым консультантом, тренером и писателем. Его последняя книга — Sams Teach Yourself ASP.NET 2.0 за 24 часа. С ним можно связаться по адресу mitchell@4GuysFromRolla.com.

Особое спасибо кому

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

НазадДалее