Parameter Discovery on Ms Access and SqlServer. using Microsoft Patterns and Practices DataBlock version 3.0 final

• Download DataBlockTesterV30.zip - 348.1 KB

Parameter Discovery on Ms Access and SqlServer. Using Microsoft Patterns and Practices DataBlock April Ver 3.0 Final

This is an update to my last article:

Added another Tab page to Defenition Tool, this will correctly change attributes for program to compile.

This project contacts Ms Access Jet 4.00 Database or SqlServer, gathers schema information usefull to the building of command parameters for stored procedures and Sql query commands. "Generics.cs" , which is the Class that Data.dll uses for Ms Access, throws an exception if you try to discover parameters as it can not be done. I added my schema collection classes to this method, therefore main abstract class, can now continue. Adds command parameters to internal cache (at next usage of this command, it uses cache version) and executes a "NonQuery" command to database.

Background

I came accross quite a few problems while trying to update:

When Using Enterprise Library Configuration tool, the tool references strongly named versions of Dll's.
This makes my DataModified.dll useless, you must change the attribute on app.config to point to my
Dll after using Enterprise Library Configuration tool

Temporary Solution
Added another Tab page to Defenition Tool, this will correctly change attributes for program to compile.

Once Again:
In order to use Enterprise Library Configuration tool, the reference should read as follows:

"dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

For My Dll to compile the attribute needs to read as follows:.

"dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.DataModified, Version=3.0.0.0, Culture=neutral, PublicKeyToken=null" />

The Tool which builds the defenitions consists of Tab pages, the last tabPage will change the attribute between the two above values.

Please see documentation for more information.

Using the code

Choice of XML document, either App.config or any Xml document of your choosing.

Optional, 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, this is heavily commented can look a little bit daunting until you remove the comments.

Please Note:

If you do not want to use XML option.

A custom section added by me into App.config pointing to which Xml Doc to look for needs to have a value or null.

Examples

This is an example of one of my overloads, 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. On both of these examples, the default Database is over ridden 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 Stored Procedure, two new overloads used here,
// Command Type was declared at 
// form level and passed to this method only
// to be used on InsertCommands on Ms 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

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

Class Borowed - Profile Dll:

I borrowed this Dll from this site.
Title : AMS.profile
page title : Read/Write XML files, Config files, INI files, or the Registry
Author : Alvaro Mendez.
page : <a href="http://www.codeproject.com/csharp/readwritexmlini.asp">http://www.codeproject.com/csharp/readwritexmlini.asp</a>
it can write to App.config, XMl,Registry or InI all under using same
profile. Quite cool, but only add/edits key values (in any section).</a />

History

08/04/07:

Updated To Final Version 3.0

01/03/07:

Added a choice of Xml document to accept DataBase Defenition, I added this because I like this method best.
It does not need to contact Database, and does not need to use hashtables.

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, 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 procces them. I made a small program, which will modify an existing XML document and add the schema from Database. Problem, if you make changes to Database structure, it needs to be updated so be carefull, you could find that a Stored procedure for no reason will not build, now you know why.

If you're gonna use it, keep on top of it, otherwise deleted from folder, the value in Appconfig "DabaseDefenitionFile" to equal null 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 where added for use with MS Access but could also be used by SQL Server, but only if you chose to use it. All overloads have clear and concise commenting indicating "Custom method Targeted at Ms Access". Added overload to Data.Dll, and an over ride 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, overall 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.