Using MS Access stored procedures in vb.net-Part II

access| stored procedures Use MS Access stored procedures in vb.net-Part II
Author: David Wasserman, MCP
4/18/2002
Translator: Qianqian
(thousandvb@yeah.net) 5/10/2002

Article source code:Msaccess_sp2.zip
introduce
Welcome to the second part of the MS Access stored procedure discussion. The first section describes in detail how to create a stored procedure in Access using Ado.net and Visual Basic.NET. The second section will demonstrate how to access the stored procedures that have been created in the first section through the database access layer, and you can emulate it and use it in your own application. This article describes in detail how to implement the access layer of the database using Visual Basic.NET.
The primary purpose of the database layer is to provide a gateway to the database through the class module. This class module will act as a binder between the database and the application. There are 2 advantages to accessing a database using the database access layer: You can have the ability to change your background database technology (from Access to SQL Server) without impacting the application system. You can also ensure that data transmitted past is "pure" by adding a control layer between the application and the database access layer. In. NET, the database access layer typically includes a class module that follows the object-oriented specification, whereas an earlier version of Visual Basic is handled using a standard module.
Database access Layer-code
Now it's time for us to roll up our sleeves and look at some code. The first thing to do after adding an empty class is to list the things you need to use here. NET class library, as follows:
Imports System
Imports System.Data
Imports System.Data.OleDb
SystemThe library is standard for most programs, but I use it as a custom to include this class library in all the code. and System.DataA library is a library that is required for most database access programs. System.Data.OleDbwill be used to access the OLE DB Provider that you need. If we need to use SQL Server, we'd better use a custom SQL Provider System.Data.SqlClient.
The next line begins the definition of the class
Public Class Dbtier
Here we define the class name as Dbtier, and give him Publicmodifier, so it can 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"
Only one string attribute is declared here, connectionString. This variable holds the connection string for the Northwind Access database. Declaring this variable to be shared means that it is a "class Variable", a class Variable is associated with a class, and two is not associated with each object that the class produces. (Translator: VB.net's shared modifier is equivalent to a static modifier for C + + or C #)
After the connection string is defined, you can see that there are 3 processes and one function. function returns a dataset that contains a list of all products. It calls the stored procedure procproductslist that was created in the first part.
Then you can see 3 processes. They correspond to each stored procedure to add, delete, and modify products; they all have a similar structure; each uses a command and declares the connection object and the required parameters. As an example, we'll discuss the Productsdeleteitem process separately. Understanding the process, the other 2 are easy to digest.
At first, the process uses a parameter, ProductID, that represents the ID of the product that needs to be deleted.
Sub Productsdeleteitem (ByVal ProductID as Integer)
Then, all the variables are declared. Used separately for the Connection,command and parameter that the stored procedure will use. 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 ()
Command and Connection initialization:
con = New OleDbConnection (connectionString)
Cmd. Connection = Con
The properties of the Paramproductid parameter are confirmed, and this parameter is added to the Command object. In this example, the name of the parameter to use in the stored procedure is Inproductid, which is an integer variable and is assigned with the parameters of the function.
With Paramproductid
. ParameterName = "Inproductid"
. OleDbType = OleDbType.Integer
. Size = 4
. Value = ProductID
End With
Cmd. Parameters.Add (Paramproductid)
The final step is to actually invoke the stored procedure.
Cmd.commandtext = "EXECUTE Procproductsdeleteitem"
Con. Open ()
Cmd. ExecuteNonQuery ()
Con. Close ()
Note that the connection object is reserved here only when the stored procedure needs to be executed, and then closes immediately. This will reduce the possible resource footprint.
Although the Dbtier class used in this example has clearly described how to use an Access stored procedure, its functionality still needs to be enhanced to reach the level of the product level. Because there is no error handling. He still needs more reinforcement.
The source code for this article includes Dbtier.vb, which contains a few simple form to test the implementation of the class.
All in all, I want you to get at least 2 messages through these articles: one is that stored procedures exist in Microsoft Access and are good, though not enough. The second one needs to understand the need to decompose the application's database access into separate classes, functions, and processes, which makes it easier to maintain and upgrade the software.
Complete Dbtier.vb:
Imports System
Imports System.Data
Imports System.Data.OleDb

' Functions and subroutines for executing Stored procedures in Access.
Public Class Dbtier

' Change Data Source to the location's 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 adds a record to the Products table.
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
. ParameterName = "Inproductname"
. OleDbType = OleDbType.VarChar
. Size = 40
. Value = ProductName
End With
Cmd. Parameters.Add (Paramproductname)

With Paramsupplierid
. ParameterName = "Insupplierid"
. OleDbType = OleDbType.Integer
. Size = 4
. Value = SupplierID
End With
Cmd. Parameters.Add (Paramsupplierid)

With Paramcategoryid
. ParameterName = "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 the 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 Paramproductid
. ParameterName = "Inproductid"
. OleDbType = OleDbType.Integer
. Size = 4
. Value = ProductID
End With
Cmd. Parameters.Add (Paramproductid)

With Paramproductname
. ParameterName = "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 a 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 Paramproductid
. 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