Using MS Access stored procedures in VB.NET -
Two parts
Author: David Wasserman, MCP4 / 18/2002 Translator: Qianqian5 / 10/2002
Article source code: msaccess_sp2.zip
Introduction
Welcome to the second part of the MS Access stored procedure. The first part describes how to create a stored procedure using ADO.NET and Visual Basic.net in Access. The second part will demonstrate how to access the stored procedure that has been created through the database access layer, you can imitate it and use In your own app. This article describes how to use Visual Basic.NET to implement the access layer of the database.
The main purpose of the database layer is to provide a gateway that access the database through a class module. This class module will act as an adhesive between the database and the application. With database access layers to access databases There are 2 advantages: You can change your background database technology (change from Access to SQL Server) without affecting the application system. You can also ensure that the passed data is "pure" by adding a control layer between the application and the database access layer. In .NET, the database access layer typically includes an object-oriented specification class module, while the earlier version of Visual Basic uses a standard module to handle it.
Database Access Layer - Code
It is time to roll up the sleeves to see some code. The first thing after adding an empty class is to list the .NET class libraries that need to be used here, as shown below:
Imports system
Imports system.data
Imports system.data.oledb
The System library is standard for most programs, but I use it as a habit and contain this class library in all the code. The System.Data library is a library that needs to be used for most database access programs. System.Data.OLDB will use the OLE DB Provider that accesses Access. If we need to use SQL Server, we'd better use custom SQL Provider System.Data.SqlClient.
The next line began the definition of the class:
Public Class DBTIER
Here we define class named DBTIER and give him a public modifier, so it will be accessed by other code modules. After the class definition, all the properties to be used are declared.
Shared connectionString as string = _
"Provider = microsoft.jet.Oledb.4.0; data source = c: / program" _
& "Files / Microsoft Office / Office10 / Samples / Northwind.mdb"
Here only declares a string property, Connectionstring. This variable saves the connection string of the Northwind Access database. Declare this variable for shared, indicating that it is a "class variable", a class variable is associated with classes, and the bis is not associated with each object generated by this class. (Translator: vb.net Shared modifier equivalent to C or C # Static modifier)
After the definition of the connection string, you can see that there are 3 processes and 1 function. The function returns a DataSet that contains all the lists. It calls the stored procedure procproductslist that has been created in the first part.
Then you can see 3 processes. They correspond to each stored procedure, used to increase, delete, modify products; they have similar structures; each use of Command and declares that the connection object and the necessary parameters are declared. As an example, let's discuss the ProductsDeleteItem process separately. Understand this process, the other 2 is easy to digest. First, this process uses a parameter, ProductID, indicating the ID of the product that needs to be deleted.
Sub ProductsDeleteItem (Byval ProductID AS Integer)
Then, all variables are declared. The connection, command, and parameter used to store procedures will be used. This parameter is the product ID that needs to be deleted.
DIM CON AS OLEDBCONNECTION
DIM CMD AS Oledbcommand = New OLEDBCommand ()
DIM paramproductid as new oledbparameter ()
Initialization of Command and Connection:
Con = New OLEDBConnection (Connectionstring)
cmd.connection = con
Confirmed the properties of the paramproductId parameter, and then this parameter is added to the Command object. In this example, the parameter name to use in the store is InproductID, which is an integer variable, and assigns the parameters of the function.
With paramproductididi
.ParameterName = "inproductid"
.Oledbtype = oledbtype.integer
.Size = 4
.Value = productID
End with
cmd.parameters.add (paramproductid)
The final step is to truly call the stored procedure.
cmd.commandtext = "Execute ProcproductsDeleteItem"
Con.open ()
cmd.executenonquery ()
Con. close ()
Note that the Connection object is only retained when you need to perform a stored procedure, and then turn it off immediately. This will reduce the possible resources.
Although the DBTier class used in this example has clearly introduced how to use Access stored procedures, its function still requires more enhancement to reach the product level level. Because there is no error handling. He still needs more strengthening.
The source code of this article includes DBTIER.vb, which contains some simple form to test the implementation of the class.
All in all, I hope that you have obtained 2 information at least through these articles: one is that the stored procedure in Microsoft Access exists and is good, although it is insufficient. The second one you need to understand simultaneous understanding of the application's database access to the independent class, function, process, which will make the software maintenance and upgrade easier.
Complete DBTIER.vb:
Imports system
Imports system.data
Imports system.data.oledb
'Functions and Subroutines for Executing Stored Procedures in Acution.
Public Class DBTIER
'Change Data Source to the location of northwind.mdb on Your Local
'System.
Shared connectionString as string = _
"Provider = microsoft.jet.Oledb.4.0; data source = c: / program" _
& "Files / Microsoft Office / Office10 / Samples / Northwind.mdb" 'this function returns a DataSet Containing All Records in
'The products table.
Function ProductsList () AS Dataset
DIM CON AS OLEDBCONNECTION
DIM DA AS OLEDBDataAdapter
DIM DS AS Dataset
DIM SSQL AS String
SSQL = "Execute ProcproductsList"
Con = New OLEDBConnection (Connectionstring)
Da = New OLEDBDataAdapter (SSQL, CON)
DS = new dataset ()
Da.fill (DS, "Products")
Return DS
END FUNCTION
'This function add one record to the produter.
Sub ProductsAdditem (Byval Productname As String, _
BYVAL Supplierid As Integer, Byval CategoryID AS INTEGER)
DIM CON AS OLEDBCONNECTION
DIM CMD AS Oledbcommand = New OLEDBCommand ()
DIM paramproductname as new oledbparameter ()
Dim paramsupplierid as new oledbparameter ()
DIM paramcategoryid as new oledbparameter ()
Con = New OLEDBConnection (Connectionstring)
cmd.connection = con
With paramproductname
.Parameeeeee = "inproductname"
.Oledbtype = oledbtype.varchar
.Size = 40
.Value = productname
End with
cmd.parameters.add (paramproductname)
With paramsupplierid
.ParametersAme = "insupplierid"
.Oledbtype = oledbtype.integer
.Size = 4
.Value = support
End with
Cmd.Parameters.Add (paramsupplierid)
With paramcategoryid
.ParametersAme = "incategoryid"
.Oledbtype = oledbtype.integer
.Size = 4
.Value = categoryid
End with
cmd.parameters.add (paramcategoryID)
cmd.commandtext = "Execute ProcproductsAddItem"
Con.open ()
cmd.executenonquery ()
Con. close ()
End Sub
'This function updates a specific jobtitle record with new data.
Sub ProductSupdateItem (Byval Productid AS Integer, _byval productname as string)
DIM CON AS OLEDBCONNECTION
DIM CMD AS Oledbcommand = New OLEDBCommand ()
DIM paramproductname as new oledbparameter ()
DIM paramproductid as new oledbparameter ()
Con = New OLEDBConnection (Connectionstring)
cmd.connection = con
With paramproductididi
.ParameterName = "inproductid"
.Oledbtype = oledbtype.integer
.Size = 4
.Value = productID
End with
cmd.parameters.add (paramproductid)
With paramproductname
.Parameeeeee = "inproductname"
.Oledbtype = oledbtype.varchar
.Size = 40
.Value = productname
End with
cmd.parameters.add (paramproductname)
cmd.commandtext = "Execute ProcproductsupdateItem"
Con.open ()
cmd.executenonquery ()
Con. close ()
End Sub
'This function deletes One Record from the products TABLE.
Sub ProductsDeleteItem (Byval ProductID AS Integer)
DIM CON AS OLEDBCONNECTION
DIM CMD AS Oledbcommand = New OLEDBCommand ()
DIM paramproductid as new oledbparameter ()
Con = New OLEDBConnection (Connectionstring)
cmd.connection = con
With paramproductididi
.ParameterName = "inproductid"
.Oledbtype = oledbtype.integer
.Size = 4
.Value = productID
End with
cmd.parameters.add (paramproductid)
cmd.commandtext = "Execute ProcproductsDeleteItem"
Con.open ()
cmd.executenonquery ()
Con. close ()
End Sub
END CLASS
