How to call the stored procedure in Access under dotNET

You are familiar with the use of stored procedures in SQL Server. Recently, I made a small job, and the database uses the MS Access database. I also want to try to use the stored procedure. After all, the relationship between stored procedures and code is not very large, and some modifications to databases and stored procedures do not need to be modified. The following is my code. You will find that the stored procedure is exactly the same as calling SQL Server. The difference lies only in the name of the stored procedure.

Take deleting personnel information as an example:
1. Here is a piece of code at the data access layer, which directly calls the stored procedure.

Public class Person
{
/// Define the stored procedure. Note that the stored procedure is included in.
Private const string ASP_PERSON_INSERT = "[asp_Person_Insert]";
Private const string ASP_PERSON_UPDATE = "[asp_Person_Update]";
Private const string ASP_PERSON_DELETE = "[asp_Person_Delete]";
...................................
Private const string PARM_PERSON_PERSONID = "@ PersonID ";
..................................

Private OleDbParameter [] GetDeleteParam (int PersonID)
{
OleDbParameter [] param = new OleDbParameter []
{
New OleDbParameter (PARM_PERSON_PERSONID, OleDbType. Integer)
};
Param [0]. Value = PersonID;
Return param;
}

/// <Summary>
/// Delete personnel information
/// </Summary>
/// <Param name = "personID"> personnel ID </param>
/// <Returns> success returns True; Failure returns False </returns>
Public bool Delete (int personID)
{
// Name of the ASP_PERSON_DELETE stored procedure.

OleDbParameter [] param = this. GetDeleteParam (personID );
Try
{
/// The call method remains unchanged.
Int I = Utility. Tools. CreateOleDbCommandExecuteNonQuery (ASP_PERSON_DELETE, param );
If (I> 0)
{
Return true;
}
}
Catch
{
}
Return false;
}

2. Let's take a look at the differences between the Access database and SQL Server:

The stored procedure in the Access database is stored as "query". Open your Access database, select "query", select "new", and create a simple query first. Select the table, required fields, and required parameters. Then, select the SQL view in the toolbar. You can see the following statement:

PARAMETERS [PersonID] Short;
DELETE *
FROM PersonInfo
WHERE [PersonInfo]. [PersonID] = [PersonID];

For more PARAMETERS, see PARAMETERS [ID] Short, [ParentID] Short, [OrganName] Text (255), [Numbe] Text (255 ), [OtherName] Text (255), [CodeID] Text (255), [OrganSpec] Text (255), [OrganProperty] Text (255), [OutLayType] Text (255 ), [ManageDepart] Text (255), [SetUpTime] DateTime, [PassDepart] Text (255), [OrganNUM] Short, [PersonNUM] Short, [PassNumber] Text (255 ), [Address] Text (255), [JPerson] Text (255), [Tel] Text (255), YearCheck Text (255 );

Finally, save and run your code. In addition, there is no SQL Server queryer in Access, so it is more difficult to test your stored procedure in Access: Right-click your stored procedure and select "open, but do not enter an error in the parameter. Writing a stored procedure in Access is difficult because it does not support the stored procedure as SQL Server does. Therefore, we strongly recommend that you save the Stored Procedure frequently.