Работа с хранимыми процедурами MS Access в VB.NET. Часть 1

В самых недавних релизах Microsoft Access большие усилия были затрачены для придания этому продукту полноты набора функций системы реляционной базы данных. Хранимые процедуры - функциональность, обычно связываемая с промышленными системами баз данных, такими как SQL Server, теперь могут быть найдены в Access. Хранимые процедуры стали доступны, начиная с Access 2000, и они являются естественными для Jet 4 Database Engine. Если вы знакомы с использованием хранимых процедур в SQL Server, то вы сможете применять их, оставаясь дома так же, как вы пользуетесь Access. Однако, нужно иметь в виду некоторые ограничения. Я буду обсуждать их позже.

Эта статья будет разделена на две части. Первая часть в деталях опишет, как создавать хранимые процедуры в Access с помощью ADO.NET и Visual Basic.NET. Вторая часть покажет, как использовать хранимые процедуры, созданные в первой части, настраивая уровень доступа к данным, который может быть смоделирован и использован в ваших приложениях. Код в этой статье был проверен в Access 2002, но он, так же, будет работать и в Access 2000.

Как хранимые процедуры работают в Access?

В отличие от других объектов в Access хранимые процедуры не имеют интерфейса и не могут быть созданы или использованы через Access User Interface (пользовательский интерфейс Access). Получить их в вашей базе данных можно только одним способом - просто кодировать их. Я покажу, как это делается в ADO.NET.
Когда хранимая процедура добавляется в базу данных Access, Jet Engine преобразовывает синтаксис хранимой процедуры в объект запроса. Для Access-разработчика это может прозвучать как ненужная работа в кодировании запроса. Однако у этого есть свои преимущества. Рассмотрим приложение, которое разделяется на две разные версии, одна из которых поддерживает базу данных Access, а другая - базу данных SQL Server. Применение хранимых процедур облегчит написание кода на уровне приложения базы данных, так как отличия между различными версиями программы очень малы.

Создание хранимых процедур

Для демонстрации сначала я покажу, как создать SQL-операторы для создания хранимых процедур. В конце статьи я покажу весь код, необходимый для выполнения этих операторов в базе данных. Используется база данных Northwind, которая поставляется вместе с Access, будут созданы четыре хранимых процедуры. Все они будут применены к таблице Products (Продукты), начнем с самой простой; выберем все данные из каждой строки таблицы. Чтобы создать хранимую процедуру, выполните следующий оператор в базе данных:

"CREATE PROC procProductsList AS SELECT * FROM Products;"

Оператор: "CREATE PROC procCustomerList" является частью, которая в действительности создает хранимую процедуру. Частью, которая следует за "AS" может быть любой действительный SQL-оператор.
Часто в хранимой процедуре вы хотите передать значение, которое будет использовано в запросе. Учитывайте, что вы можете захотеть удалить запись, основываясь на уникальном ProductID. Следующая хранимая процедура показывает, как просто это делается:

"CREATE PROC procProductsDeleteItem(inProductsID LONG)" & _
"AS DELETE FROM Products WHERE ProductsID = inProductsID;"

В первой строке обратите внимание на скобку сразу после объявления CREATE PROC. Это параметр, объявленный как значение Long. Вот куда вы добавляете переменную для удаления записи.
Следующие два оператора показывают, как создать хранимые процедуры для добавления и обновления данных в таблице Products. Обратите внимание, что для краткости включены не все поля:

"CREATE PROC procProductsAddItem(inProductName VARCHAR(40), " & _
"inSupplierID LONG, inCategoryID LONG) " & _
"AS INSERT INTO Products (ProductName, SupplierID, CategoryID) " & _
"Values (inProductName, inSupplierID, inCategoryID);"
"CREATE PROC procProductsUpdateItem(inProductID LONG, " & _
"                                   inProductName VARCHAR(40)) " & _
"AS UPDATE Products SET ProductName = inProductName " & _
"    WHERE ProductID = inProductID;"

Заметьте, что параметры разделены запятыми, если их определено более одного.

Ограничения

Здесь вы можете наткнуться на некоторые ограничения, особенно если использовали мощный SQL Server.

• Нельзя использовать исходящие параметры.
• Не используйте символ "@". Символ "@" часто применяется в Transact SQL (SQL Server), где он представляет локальные переменные. Access не всегда обрабатывает этот символ и иногда будет выбрасывать его. Это может послужить причиной скрытых ошибок, которые приведут к раннему облысению.
• В Access недоступны временные таблицы.
• Я подозреваю, что многие опции, доступные в Transact SQL не доступны в Access, так как он не совместим с Transact SQL.

Вывод

Надеюсь, эта статья станет руководством в несколько недокументированной области Access и Jet, которая исследована еще не всеми. Для получения дополнительной информации о том, как код ADO.NET работает в подпрограмме CreateStoredProc обратитесь к Getting Started with ADO.NET (Начало работы с ADO.NET) Гарнита Сингха. Далее следует полный листинг кода, представленного в этой статье:

Imports System
Imports System.Data
Imports System.Data.OleDb

Module CreateSP

    Sub Main()

        ProductsProcs()

    End Sub

    ' Products Stored Procs to be added to the db.
    Sub ProductsProcs()
        Dim sSQL As String

        ' procProductsList - Retrieves entire table
        sSQL = "CREATE PROC procProductsList AS SELECT * FROM Products;"
        CreateStoredProc(sSQL)

        ' procProductsDeleteItem - Returns the details (one record) from the 
        ' JobTitle table
        sSQL = "CREATE PROC procProductsDeleteItem(@ProductID LONG) AS " _
            & "DELETE FROM Products WHERE ProductID = @ProductID;"
        CreateStoredProc(sSQL)

        ' procProductsAddItem - Add one record to the JobTitle table
        sSQL = "CREATE PROC procProductsAddItem(inProductName VARCHAR(40), " _
            & "inSupplierID LONG, inCategoryID LONG) AS INSERT INTO " _
            & "Products (ProductName, SupplierID, CategoryID) Values " _
            & "(inProductName, inSupplierID,   CategoryID);"
        CreateStoredProc(sSQL)

        ' procProductsUpdateItem - Update one record on the JobTitle table
        sSQL = "CREATE PROC procProductsUpdateItem(inProductID LONG, " _
            & "inProductName VARCHAR(40)) AS UPDATE Products SET " _
            & "ProductName = inProductName WHERE ProductID = inProductID;"
        CreateStoredProc(sSQL)


    End Sub

    ' Execute the creation of Stored Procedures
    Sub CreateStoredProc(ByVal sSQL As String)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim da As OleDbDataAdapter

        ' Change Data Source to the location of Northwind.mdb on your local 
        ' system.
        Dim sConStr As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data " _
            & "Source=C:\Program Files\Microsoft " _
            & "Office\Office10\Samples\Northwind.mdb"

        con = New OleDbConnection(sConStr)

        cmd.Connection = con
        cmd.CommandText = sSQL

        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()

    End Sub

End Module