access| stored procedures Use MS Access stored procedures in vb.net-part I.
Author: DAVIDWASSERMAN,MCP
2/27/2002
Translator: Qianqian
(thousandvb@yeah.net) 3/8/2002
Article source code
: Msaccess_ Sp.zip
introduce
Microsoft has made every effort to make this product a truly fully functional relational database system in the latest release of MS Access. Stored procedures, a feature commonly associated with enterprise database systems such as SQL Server, can now be found in access. Since Access2000, access already has the functionality of a stored procedure, and it's from the Jet 4 Database engine. If you are accustomed to using stored procedures in SQL Server, you will be familiar with how they are used in access. But remember there are some limitations. I will make a discussion later.
This article is divided into two parts. The first part describes in detail how to create a stored procedure in Access using Ado.net and Visual Basic.NET. The second part will demonstrate how to create a data access layer using the stored procedures created in the first section and use it in your own application. The code in this article has been passed in the Access2002 test, and even so, it should be able to run in Access2000.
How stored procedures are
Access
work in
?
Unlike other objects in access, stored procedures do not have a user interface and are not created in the interface of access. The only way to build them is to encode. I'll demonstrate how to implement this code in Ado.net.
When a stored procedure is added to an Access database, JET engine converts the stored procedure to a query object. For an access developer, this is like writing a simple query, and it's unnecessary work. However, it does have its advantages. Consider that an application system must be divided into 2 systems for separate access and SQL Server editions. Using stored procedures makes it easier to write code for the application's database access layer, and the program differs very little between versions.
creating a stored procedure
To demonstrate, I'll first show how to use SQL statements to create stored procedures. At the end of the article I'll give you the code that lists all the statements that need to be executed in the database. When you use the Northwind database that is included with access, it creates 4 stored procedures. They all focus on the Prodcut table, let's start with the easiest: Select the data for all the rows in the table. In order to create this stored procedure, execute the following SQL statement in the database:
"CREATE PROC procproductslist as SELECT * from Products;"
The statement "Create PROC proccustomerlist" is the part that actually creates the stored procedure. The section under "as" can be any valid SQL statement.
Frequently, you will need to pass parameters to the stored procedure to use for the query. For example, you may want to delete a record based on a ProductID. The following stored procedures show how to accomplish this:
"CREATE PROC Procproductsdeleteitem (inproductsid LONG)" & _
"As DELETE from the products WHERE productsid = Inproductsid;"
In the first line, note the bracket to the right of the CREATE proc declaration. There is a parameter that is defined as a long integral type. This is the variable you need to enter to delete the record.
The next two separate statements show how to create an add and an update stored procedure for the product table. Note that for the brevity of the article, all fields are not included in the procedure:
"CREATE PROC Procproductsadditem (inproductname VARCHAR)," & _
"Insupplierid Long, Incategoryid long" & _
"As INSERT into the products (ProductName, SupplierID, CategoryID)" & _
"Values (Inproductname, Insupplierid, Incategoryid);"
"CREATE PROC Procproductsupdateitem (Inproductid LONG," & _
"Inproductname VARCHAR" & _
"As UPDATE products SET ProductName = Inproductname" & _
"WHERE ProductID = Inproductid;
Note: When the number of arguments is more than one, separate them with commas.
Limit
Here, you may encounter some limitations, especially if you are already very accustomed to the powerful features of SQL Server.
Output parameters cannot be used.
Do not use the @ character. The @ character is typically used for transaction SQL (SQL SERVER), representing a local variable. Access does not always convert this character, sometimes omitting it. This problem can lead to a very difficult bug, which will cause you to lose a few more hairs.
Access cannot access temporary tables.
I suspect that the options in many transaction SQL are not available in Access because it is not transaction SQL compatible.
Conclusions
Hopefully this article will provide some guidance in an undisclosed area of access, but not for the jet engine. If you need to know more about how the Ado.net code works in the Createstoredproc 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 System
Imports System.Data
Imports System.Data.OleDb
Module Createsp
Sub Main ()
Productsprocs ()
End Sub
' Products Stored procs to is 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 the 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) 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's 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
