Using the MS Access Storage Procedure in VB.NET - Part
Author: DavidWasserman, MCP 2/27/2002
Translator: qianqian3 / 8/2002
Article source code: msaccess_ sp.zip
Introduction
In the latest release MS Access, Microsoft has tried to make this product a truly full-featured relational database system. Stored procedures, a functionality and corporate database system such as SQLServer, can now be found in Access. Since Access 2000, Access has a function of stored procedures, and it comes from Jet 4 Database Engine. If you are accustomed to using a stored procedure in SQL Server, you will be familiar with how they use it in Access. But to remember some restrictions. I will discuss it later.
This article is divided into two parts. The first part will describe how to create a stored procedure in Access in Access using ADO.NET and Visual Basic.net. The second part will demonstrate how to create a data access layer using the stored procedure created in the first part and use it in your own application. The code in this article has passed the Access 2002 test, even so, it should also be able to run in Access2000.
How does the stored procedure work in Access?
Unlike other objects in Access, the stored procedure does not have a user interface and cannot be created in the Access interface. The method to establish them is only encoded. I will demonstrate how to implement these code in ADO.NET.
Jet Engine converts the stored procedure to a query object when a stored procedure is added to the Access database. For an Access developer, this is unnecessary work like writing a simple query. However, it does have its advantages. Consideration, an application must be divided into two systems in order to separate Access version and SQL Server version. Using a stored procedure will make it easier for the application's database to access the layer, and the program is very small between different versions.
Create a stored procedure
For demonstration, I will first show how to use the SQL statement to create a stored procedure. At the end of the article I will give the code to list all the statements that need to be executed on the database. When using the Northwind database contained with Access, it creates 4 stored procedures. They all focus on the PRODCUT table, let us start from the most easily: Select the data in all rows in the table. In order to create this stored procedure, perform the following SQL statements in the database:
"Create Proc ProcproductSlist as Select * from products;"
The statement "create proc programterlist" is a part that actually creates a stored procedure. The portion below "AS" can be any valid SQL statement.
Regular, you will need to pass parameters to the stored procedure to use to query. For example, you may want to delete a record according to a productID. The following stored procedures show how to implement this:
"Create Proc ProcproductsDeleteItem (InproductSid Long" & _ "AS Delete from Products Where Productsid = InproductsId;"
At the first line, pay attention to the Create Proc declaration to the parentheses on the right. There is a parameter defined as a long integer. This is a variable you need to be entered for deleting records.
Two separate statements show how to create an Add and a UPDATE storage process for the Product table. Note that for the shortness of the article, there is no all fields in the process:
"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;
Note: When the number of parameters exceeds one, separated by commas.
limit
Here, you may encounter some restrictions, especially if you are very accustomed to SQL Server's powerful features.
No output parameters cannot be used. Do not use @ characters. @ Characters are usually used for Transaction SQL (SQL Server) representing a local variable. Access does not always convert this character, sometimes omitted. This problem may lead to a bug that will make you get rid of a few hair. Access cannot access temporary tables. I suspect that there are options in many Transaction SQLs in Access because it is not transaction SQL compatible.
in conclusion
I hope this article can provide some guidelines in an unapproved field of Access, and do not discuss much to the Jet engine. If you need to know how the ADO.NET code works in the CreateStoreProc subroutine, see Getting Started With ADO.NET written by GURNEET SINGH. Here is a complete list of all the code used in this article:
Imports SystemImports System.DataImports System.Data.OleDbModule 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'
