View Single Post
  #4 (permalink)  
Old November 29th, 2008, 01:13 AM
Bob Bedell Bob Bedell is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

You can use the EXACT same approach to executing Access "stored procedures" as you can executing Sql Server stored procedures. Here is how its done. Since you are using Access 2007 you are using the Jet 4.0 engine whichh support Sql Server Compatible Syntax. But first you have to enable it. In your Access database do the following:

Tools --> Options --> Tables/Queries

In the Sql Server Compatible Syntax (ANSI 92) group check 'This database'

Now create a data definition query in the query designer SQL view:

Code:
CREATE PROCEDURE GetStudentById
(
[@studentId] int
)
AS
SELECT
   StudentID,
   FirstName,
   LastName
FROM
  Students
WHERE
  StudentID = [@studentId];
The barckets around the parameter a required!

Now run the query. You'll now see a saved, parameterized query named GetStudentById back in the Database Objects windows. Its SQL is:

Code:
PARAMETERS [@studentId] Long;
SELECT Students.StudentID, Students.FirstName, Students.LastName
FROM Students
WHERE (((Students.StudentID)=[@studentId]));
You now have a saved Access "stored procedure to work with just as you would work with a Sql Server Stored Procedure. The ADO.NET code to use it is IDENTICAL, except that you are of course using OleDb objects and the OleDb provider in your connection string. For example, here's a little Console app:

Code:
using System;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            GetStudentById(1);
        }

        public static void GetStudentById(int studentID)
        {
            using (OleDbConnection connection = new OleDbConnection
                 (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Students.mdb;Persist Security Info=True"))
            {
                connection.Open();
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "GetStudentById";

                    command.Parameters.AddWithValue("@studentID", studentID);

                    using (OleDbDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Trace.WriteLine(reader.GetInt32(0));
                                Trace.WriteLine(reader.GetString(1));
                                Trace.WriteLine(reader.GetString(2));
                            }
                        }
                    }
                }
            }
        }
    }
}
This approach allows you to essnetially reuse you Sql Server stored procedures and ADO.NET code when migrating between Sql Server and Access.

HTH,

Bob