Sample Image - ParametersDiscoveryXML.jpg

Parameter Discovery on Microsoft Access Using Microsoft Patterns and Practices DataBlock Jan 2006 version

This project contacts the Microsoft Access Jet 4.0 Database and gathers schema information that is used to build command parameters for stored procedures and SQL query commands.

"Generics.cs" , which is the class that Data.dll uses for Microsoft Access, throws an exception if you try to discover parameters since this cannot be done.

I added my schema collection classes to this method thereby enabling the main abstract class to continue. It adds command parameters to the internal cache (at next usage of this command, it uses cache version) and executes a "NonQuery" command to the database.

Background

I first took an interest in this subject after viewing an article on this site: Simulating Stored Procedures in Microsoft Access using Enterprise Library Application Blocks.

The trouble was, it was designed with an earlier version of the library. I searched everywhere for an update, but could not find one.

Using the code

Optionally, there is a small program that modifies an existing XML document adding database schema. Otherwise, just use this version of Data.dll instead of the standard version. A data layer is provided. It is heavily commented and can look a bit daunting till you remove the comments.

This is an example of one of my overloads and is the SQL Query version.

public int ExecuteQueryCommand(string SqlQuery, Object[] ObjParameters)
{
  Database database = DatabaseFactory.CreateDatabase("AccessPhotoAlbum");
  DbCommand Command = database.GetStoredProcCommand(CommandType.Text, 
                        SqlQuery, ObjParameters);
  int nRowsAffected = database.ExecuteNonQuery(Command);
  return nRowsAffected;
}

I use this one most of the time. In both these examples, the default database is overridden with a second database named "AccessPhotoAlbum".

public int ExecuteQueryCommand(string SqlQuery, Object[] ObjParameters)
{
  Database database = DatabaseFactory.CreateDatabase("AccessPhotoAlbum");
  DbCommand Command = database.GetStoredProcCommand
        (CommandType.StoredProcedure, SqlQuery, ObjParameters);
  int nRowsAffected = database.ExecuteNonQuery(Command);
  return nRowsAffected;
}

This is an example of auto number retrieval.

Please note: this command uses an out parameter to return both "rows affected" and "newID". Using a Stored Procedure, two new overloads are used here. CommandType was declared at form level and passed to this method only to be used on InsertCommands on Microsoft Access.

public int InsertGetIdentity(string strSpName, CommandType Ctype, 
            Object[] ObjParameters, out int nReturnValue)
{
  Database database = DatabaseFactory.CreateDatabase("AccessPhotoAlbum");
  DbCommand Command = database.GetStoredProcCommand(Ctype, 
                        strSpName, ObjParameters);
  // new overloaded method.
  int nRowsAffected = database.ExecuteNonQuery(Command, out nReturnValue);
  Debug.Print("");
  Debug.Print("--------------------------");
  string DebugMsg = String.Format("Returned Identity: {0} ", 
                        nReturnValue.ToString());
  Debug.Print(DebugMsg);
  return nRowsAffected;
}

Classes Added

CustomCommandBuilder.cs
JetCommandBuilder.cs
SqlQueryCommandBuilder.cs

These can be found in a new directory named "CustomMethods", inside Data.dll

Points of Interest

I figured out why they left out this code. It can drag SQL server code down if you're not careful. I have been re-writing this code now for 4 months and it's been hard. I guess they could just not see the advantages. It was always going to be left to someone like me.

Because SQL Server has return parameters, all connections could be closed within the data.dll, that's why it is hard to retrieve the Auto Increment number.

I realise that some people would wonder why I even bothered with SqlQuery options, as it goes agaist coding pratices, but I must admit that if I start writing a project, then I always use Queries, and only if I like it do I write stored procedures. I would like to mention "MyGeneration" the free program (builds common stored procedures for all databases) is the first thing I use when designing a database.

History

Coming up: I am testing the new version of Datablock (just released in Dec 2006) and expect to upload it in a week or two.
27/12/06: After a conversation or two between me and other programmers (Hobyists), I decided to add an option. If you place an XML file in the Executable directory, then the class will read it and use this to build the commands. It presents the Field names and values in the best manner for the class to process them. I made a small program which will modify an existing XML document and add the schema from the database. The problem is that if you make changes to the database structure, it needs to be updated, so be careful. You could find that for no reason, a Stored procedure will not build, now you know why. If you're going to use it, keep on top of it, otherwise delete it from the folder and the class will just continue anyway.
04/12/06: In order to maintain the high degree of code targeted at SQL Server, I was forced to add two overloads to data.dll. These were added for use with Microsoft Access but could also be used by SQL Server, but only if you chose to use it. All overloads have clear and concise comments indicating "Custom method Targeted at Microsoft Access". I added overload to Data.Dll, and an override on "Generics.cs" to aid in retrieving "SELECT @@IDENTITY".
29/11/06: Added support for DataSet Parameters Discovery. I have had a mixture of pros and cons with this, but overall, I am very happy. I am still researching a few more points on this one, other updates could follow.
23/11/06: Added support for SQL Server Query Parameters Discovery. I added this purely for early design help and it did keep my datalayer identical for both types of database.

Parameter Discovery on Microsoft Access With XML option. Using Microsoft Patterns and Practices