Abstract
Data management being a common requirement, yet implementations being various depending on the database system and the query in question, leave us with a large number of different cases we are left to cover during the process of software development. We shall now take to remedy this problem by introducing a Generic Database Access model which will generalize the interaction with any particular database system in question.
The Problem in Question
1.1 Problem
During the Software Development process, a considerable amount of time is spent dealing with the Data Access Layer. This layer represents the functionality which is concerned with the interaction between our main application and a certain database system. This layer should be able to both send and receive data from the database in question.
The development of such a layer is becoming more difficult if our application needs to support different database systems. Furthermore, the commands themselves are defined in a way so that they need to receive a different number of parameters. Thus, our code needs to change accordingly.
This would imply that for every slight change and nonconformity, we need to duplicate a large amount of code which is going to solve the newly created issues. Thus, the code will increase as the number of commands we intend to execute increases.
In order to find a general solution to this problem, we shall present the following solution.
1.2 Solution
Since we are dealing with a solution geared towards the .NET platform, we shall take as our starting point the ADO.NET framework, which is the base framework for the .NET platform which is used in order to interact with different Database Systems[1].
Furthermore, we will also take into account that every database system comes with its own assembly based on the ADO.NET framework, which is used by our application to interact with that specific database. Thus it follows that our solution will require of us to have the database-specific assembly in order for our solution to function properly.
The next step will now be to take into account all the possible instances of commands we could possibly execute. Let us now divide all the possible SQL commands into two types. These are the Query and Non Query types of commands.
The Query type command is any type that will query the database in order to return the data stored in the database itself. On the other hand, a Non Query type of command will be all other types of commands which do not query the database for data, but will modify the data within the database.
Furthermore, since we can either execute simple text based SQL commands with the ADO.NET framework, or execute SQL Stored Procedures stored in the database, we can further split the possible commands to the following ones. These are the Select, Insert, Update, and Delete commands.
Let us mention that the Select command is a Query type command, while the rest are all Non Query types of commands. Finally we can say that we shall use the Select, Insert, Update, and Delete names to designate simple text-based SQL commands, while the Query and Non Query names will be used to designate the respective types of SQL Stored Procedures.
Thus, our goal will now be to construct a .NET assembly, which will be able to execute the above mentioned commands. Let us now observe how this will be achieved.
Examining the Solution
2.1 Requirements
The above mentioned solution will need to fulfill the following set of five prerequisites:
- Interact with any database
- Execute any SQL command
- Work directly with business objects
- Configuration without recompiling
- Execute queries in a single line of code
Requirement 1:
The first requirement is clearly here for us to be able to interact with any database system. Should we fail to do so, our solution would not be generic in principle.
Requirement 2:
The second requirement is to make sure that the data inside the database is fully extractable and modifiable. In other words, we must be able to query any data from the database and also change any data within it.
Requirement 3:
The third requirement is for us to keep the simplest form for our calling classes. If our classes were directly based on the types and the number of parameters a SQL command could possibly take, we would then need to create a possibly infinite number of classes in order to execute all possible SQL commands. Thus, we shall instead use a Business object as a single parameter, from which the data will be extracted.
Requirement 4:
The requirement number four is here to assure us that the first and the second requirement is fulfilled properly. The assembly would in and of itself not be generic by definition if it would require of us to recompile the assembly, should we need to work with a different Database system, which was not taken into account upfront. The same goes for the execution of different SQL commands.
Requirement 5:
The fifth and last requirement is here to make sure that our solution is simple and that we are able to execute the commands efficiently without any further modification to the assembly classes themselves.
Before we begin with presenting The Generic Model which will serve as our solution to the problem, let us take a look at one possible candidate that would be able to solve all the possible requirements listed above.
This solution comes from the Microsoft Enterprise Library 6.0[2] which is a set of application blocks, consisting of several assemblies. These assemblies are used to solve recurring problems the developers face during the software development process.
One of the blocks within this library is the Data Access Application block. This block is used for interacting with different Database systems. Let us now take a closer look at the following code which is present within the library itself.
private static SqlCommand CheckIfSqlCommand(DbCommand command)
{
SqlCommand sqlCommand = command as SqlCommand;
if (sqlCommand == null)
throw new ArgumentException(Resources.ExceptionCommandNotSqlCommand, "command");
return sqlCommand;
}
Unfortunately, as we can see from the previous code example, the Data Access Application block fails the first prerequisite in order for it to serve as a Generic Database Access. Notion of a Generic Model is falsified by the explicit use of a Database-specific type, namely the SqlCommand type.
This type is used for representing the SQL commands for the Microsoft SQL Server Database system only. Should we need to use another Database, we would then also need to specify another Database-specific type. This will not allow us to use the Databases which currently do not exist but we would like to use in the future when they become available, without actually recompiling the code.
Thus the Data Access Application block is not suitable for the solution to the problem.
2.2 The Generic Model
Let us now turn to The Generic Model itself. The Model in question can simply be described as an assembly which will intercept the calls from the main calling application, select the appropriate Database-specific assemblies and types in order to execute the predefined SQL command, and then execute the command in question on the correct Database.
This operation can be represented by the following diagram.
Image 1. – Diagram of the Generic Database Access Model.
We can observe six different nodes, representing points in the program flow in our Model. The first node, representing the point in the code where the Generic Database Access assembly is called, is the main application which will be used to send the data to the assembly, and receive it from the assembly once the assembly has executed the required command.
The second node is the Generic Database Access assembly itself, which gets called by the Caller application, which is indicated by the solid line with an arrow. This assembly is now going to make two calls, which we can see by observing the lower part of the diagram.
The call will branch out to the Database-specific setup XML file, and the SQL Command XML file. This information will then be sent to a Database-specific ADO.NET assembly, which was defined in the Database-specific setup XML file, and a command, which was defined in the SQL Command XML file will be executed.
This command will then be executed on a predefined Database system, which represents our final node, to which the Generic Database Access assembly makes the call.
2.3 Achieving the Requirements
In order for us to achieve the five requirements defined earlier we will need to utilize a system with which we can modify our working assembly in the easiest way possible. This can be achieved by using text based, that is, XML configuration files to change the workings of our assembly depending on the circumstance we are dealing with.
To appreciate this notion in greater depth, let us present the solution to the first prerequisite which is to allow our assembly to work with any Database system. Before we do so, let us note that the assembly has to, at least in theory be able to handle currently non existent Database systems, in order to truly be considered generic.
In other words, it should work with Databases that do not yet exist, but once created, and once the appropriate ADO.NET assembly has been produced for this Database, our assembly will need to integrate with it seamlessly without recompiling.
Therefore, let us now start presenting the solutions to the requirements presented earlier.
Solution 1:
We begin by presenting the XML file which will be used in order to achieve this requirement.
// (1.2)
="1.0"="utf-8"
<root>
<Connection>Data Source=XE; User Id=SYSTEM; Password=mypassword;</Connection>
<Prefix>:</Prefix>
<ParameterAssembly>Oracle.DataAccess.dll</ParameterAssembly>
<ParameterType>Oracle.DataAccess.Client.OracleParameter</ParameterType>
<Property>
<Name>Adapter</Name>
<Assembly>Oracle.DataAccess.dll</Assembly>
<Type>Oracle.DataAccess.Client.OracleDataAdapter</Type>
</Property>
<Property>
<Name>Connection</Name>
<Assembly>Oracle.DataAccess.dll</Assembly>
<Type>Oracle.DataAccess.Client.OracleConnection</Type>
</Property>
<Property>
<Name>Command</Name>
<Assembly>Oracle.DataAccess.dll</Assembly>
<Type>Oracle.DataAccess.Client.OracleCommand</Type>
<Data>
<Name>BindByName</Name>
<Value>True</Value>
</Data>
</Property>
<Property>
<Name>Builder</Name>
<Assembly>Oracle.DataAccess.dll</Assembly>
<Type>Oracle.DataAccess.Client.OracleCommandBuilder</Type>
</Property>
</root>
We are now being presented with the Database-specific setup XML file, which is located in the GenericDataBaseAccess folder within the folder that will be used to store our assembly. This XML file is named GenericDataBase.xml which is a requirement and as such will be used as the main setup file.
Furthermore let us notice that this is an Oracle Database-specific setup, but to understand just how exactly this XML file will be useful to us, let us start by observing its elements.
The first element, is the Connection element and it will be used to specify the Connection string using which our assembly is going to connect to a Database system. The second element is the Prefix element and this element will be used to define a Database-specific character which will represent a prefix that is used in a Database environment to denote an argument in an SQL command.
The following ParameterAssembly element will be used to define the name of an assembly from which we will use types that will be used to create parameters for our SQL commands.
The ParameterType element is where the name of the previously mentioned types will be defined.
We can also notice that we have four complex Property elements also. These elements contain values which will be used by the assembly in order to interact with the Database. Each Property element has as its sub elements the Name, Assembly and Type elements, with the optional Data element.
The Name element will indicate which property, defined in our assembly will be loaded by an instance at run time. The name of the property in our assembly corresponds to the name of the Name element of the Property element. The assembly from which the type will be extracted is defined in the Assembly element, while the type which will be instantiated at run time is defined in the Type element.
To be more specific, let us just mention that the Adapter Property is used to define a Data Adapter which will be used, the Connection Property will define an instance which will be used to represent the connection to our Database while the Command Property will represent a type used to store either a text-based SQL command or a name of an SQL Stored Procedure which will be executed.
Lastly the Builder Property will define the type that will be used to derive the parameters from Stored Procedures.
We can also notice that there is an optional Data element defined in the Command Property. We should mention that the number of Data elements is unlimited. These elements are defined as required by the Database system in question. They are used to further modify our types which are instantiated at run time in order for them to execute the command properly.
The Name element of the Data element represents the property which needs to be set, while the Value element represents the value to be set. Since these represent Boolean values, the only possible values are either True or False.
Some further Database-specific information is now in order.
Since the Microsoft Access Database lacks the support for Stored Procedures, the Builder Property, should be left completely blank. Furthermore, the Data element defined in the above shown Oracle setup should be removed if the Builder Property is not used.
Finally, if we use the PostgreSQL Database System, we should leave the Builder Property completely empty since this Database system is unable to properly derive the parameters from Stored Procedures. This solution is now in theory capable of interacting with any Database system.
Its only prerequisite is that there exists an appropriate ADO.NET assembly from which we can use the above mentioned types to interact with the database, thus we have successfully fulfilled the first requirement.
Solution 2:
By moving on to the second requirement, which is the ability to execute any SQL command, we shall present the following XML example which will serve as our solution.
// (1.3)
="1.0"="utf-8"
<root>
<Command>
SELECT * FROM Songs
WHERE Artist = 'Tina Turner'
OR Artist = 'Manu Chao';
</Command>
</root>
This simple XML structure will be used to execute either a simple text based SQL command or an SQL Stored Procedure. Should it be a simple SQL command, as we can see from the example above, the Command element should hold the text of the command itself.
Should this not be the case, and if we were to instead need to execute an SQL Stored Procedure, it will simply suffice to fill the Command element with the name of the Stored Procedure we need to execute.
By doing this, we have also successfully completed the second prerequisite, since we will easily be able to load any SQL command either by text or through a Stored Procedure, using an XML file, and execute them from our assembly.
Solution 3:
The third prerequisite is that we can work directly with Business objects. This prerequisite is actually going to allow us to use Business objects to represent our SQL commands, by defining the properties within them to represent the parameters used in Stored Procedures.
Thus, in order for us not to need to add every single parameter by hand within our code, we will simply use Business objects which will hold the values for the parameters. Since the Business objects will interact with the assembly itself, the mechanism of Reflection will be used to extract every single property within the Business object and add it as a parameter to our SQL command.
In order for this to work properly, the properties need to be named with the same name as the parameters, and thus, this will allow us to successfully solve the third prerequisite in order for us to construct the Generic Database Access Model.
Also, we should mention that the Business object and the Command XML files should have the same name. This convention is the same one that has already been used in the Data Exchange Mechanism[3].
Solution 4:
The fourth requirement is that we do not need to recompile our assembly if we should need to interact with a different Database system or execute a different SQL command. This is easily achieved by observing the previous two solutions.
Clearly the XML files are easily modifiable by hand, without any need to recompile the assembly. Should we need to work on another Database system, we can easily modify the Database-specific setup XML file with the appropriate data, which will be, using Reflection, loaded at run time.
In the same way the different SQL commands can be added to the XML file that is used to store the SQL commands, also without the need to recompile the main assembly. This will in theory allow us to work in the future with even currently non-existing Database systems.
Solution 5:
The fifth and final requirement is to execute every SQL command in a single line of code. This will allow our assembly to be used with minimal additional complexity that needs to be added to the program.
We can also say that by observing the solution for the third requirement, by using Business objects and using Reflection to extract them and add them as parameters to SQL commands, we do not need to add parameters by hands manually.
Since two different commands can have a different number of parameters, we would not be able to define a single class which would hold the parameters in question. This is true even more since some commands have no parameters at all.
Thus, we have decided to represent all possible SQL commands with six different classes. Each of which will take as its generic parameter the type of the Business object, and as its single parameter the instance of the Business object itself.
This will be presented in the form of the following definition.
new GenericCommand<BusinessObject>(new BusinessObject() { Property = ... } );
This would clearly imply that any such command can be easily executed with a single line of code. Since it is the case that the command can be instantiated easily, with the type of the Business object as its generic definition, and with an instance of the Business object as its container for the parameters in the form of its containing properties.
Thus, with all five requirements being achieved, by five different solutions that have been presented, it easily follows that we have in fact presented a Generic Database Access Model. Case in point being stated, therefore it will now only be required of us to translate the Model into a working mechanism, to which we shall proceed accordingly.
The Generic Database Access Model
3.1 Stages of The Execution Process
The process of execution of an SQL command by the Generic Database Access is divided into four stages. Let us list the stages in question, and then go about explaining them in more detail.
1. - Query Type Determination
2. - Database-Specific Type Determination
3. - Query Execution
4. - Parameter Creation
Stage 1:The first stage is concerned with determining the type which will be used in order to execute the SQL command in question.
Stage 2:The second stage is concerned with determining the Database-specific types which will actually be used to execute the command.
Stage 3:Third stage is the most complex stage where we actually start the process of SQL command execution, once the relevant types, which will execute the SQL command, have been provided.
Stage 4:The fourth and final stage is the parameter creation process and is executed within the third stage itself. It is also where we need to determine the types, and create the instances of parameters which will be used by our assembly.
The above presented stages in the process of SQL command execution within the Model, will be detailed out one by one in the following paragraphs within the current chapter.
3.2 Query Type Determination
Let us now turn to the first stage of the execution by observing a simple Select statement which is used to query a database. We shall now examine how the Generic Database Access handles the execution of such a command. In other words, we are going to go step by step through the program flow, describing the inner workings of each class used in order to execute a Generic Select command.
Thus to start our exposition, we turn to the
Select<T> class defined in our Generic Database assembly.
public class Select<T> : AbstractProcedure<DataSet, ISelect>
{
public Select(T c)
{
Results = Procedure.Invoke<T>(c);
}
}
The Select<T> class is one of the entry points to our assembly. It is used for executing simple SQL Select commands. The generic type T represents the type of our Business object and this type will also be used to get the appropriate XML file which contains the text of the SQL command that needs to be executed. Furthermore the parameter c of type T is an instance of our Business object which contains the parameters used in the SQL command that will be executed.
We can also notice that our class derives from the AbstractProcedure<T, K> class, which has its generic types closed with the DataSet and ISelect types.
The constructor of the current class definition is, as we can clearly see, only concerned with calling the Invoke<T>(T) method defined in the Procedure property and adding the results from that method to the Results property.
Since the Procedure property is an instance of a class used to execute an SQL command, and we perform its execution by calling the Invoke<T>(T) method and passing to it the Business object of type T, what we have actually done is that we have executed a SQL Select command, returned the data that has been selected and placed it into the Results property.
The AbstractProcedure<T, K> class is the one that is concerned with the details of the previous operation. By closing its generic types with DataSet and ISelect type, we have determined that the return type of our executing command is going to be a DataSet type and a class used to execute this command is going to derive from the ISelect interface.
In order to examine this process in detail, let us take closer look at the AbstractProcedure<T, K> class which is defined in the following way.
public abstract class AbstractProcedure<T, K>
{
public T Results { get; set; }
protected K Procedure { get; set; }
public AbstractProcedure()
{
Procedure = new Activate<K>(true).Instance;
}
It is easy to see that this class definition is going to add an instance of type K to the Procedure property which represents the class used to execute a SQL command. The instance that is going to be added to the property is going to be produced by the Activate<T> class and according to the generic type K.
To see how this is performed let us examine the Activate<T> class.
class Activate<T> : ActivateSource<T>
{
public Activate()
{
Instance = (T)Activator.CreateInstance(new TypeFor<T>().Type);
}
public Activate(params object[] parameters)
{
Instance = (T)Activator.CreateInstance(new TypeFrom<GenericDataBase>().Type, parameters);
}
public Activate(bool value)
{
Instance = (T)Activator.CreateInstance(new TypeFromResource<T>().Type);
}
The Activate<T> class has three overloaded constructors and derives its Instance property from the ActivateSource<T> class. The generic type T from the parent class is in charge of determining the type of the Instance property.
While on the other hand the type that will be instantiated and added to the Instance property is determined according to the type T. We should now note that the mechanism for providing us with types has been taken and modified from a core feature found in the Data Exchange Mechanism[3].
Since we have previously invoked the constructor with a single bool parameter, we shall only be concerned with this constructor for now. So, as we can see, the constructor shall, when invoked create a new instance of a type which will be provided by the TypeFromResource<T> class and set its value to the Instance property.
In order for us to understand this mechanism, let us examine the TypeFromResource<T> class.
class TypeFromResource<T> : AbstractResourceType<T>
{
public TypeFromResource() : base(null, null) { }
protected override void GetDataFromElements(string assembly, string type)
{
Type = Assembly.GetExecutingAssembly().GetType(GetConfig("Type"));
}
}
The TypeFromResource<T> class is used for extracting the names of the types from the resources that will be instantiated at run time.
We can also see the overridden method, which this class derives from its parent class, named GetDataFromElements(string, string). Here the type is provided directly from the executing assembly. The specific type that we need is determined by the GetConfig(string) method.
In order to understand this method, we shall take a look at the AbstractResourceType<T> class.
abstract class AbstractResourceType<T> : Config<ResourceConfig<T>>
{
public Type Type { get; set; }
public AbstractResourceType(string assembly, string type)
{
GetDataFromElements(assembly, type);
}
protected virtual void GetDataFromElements(string assembly, string type)
{
Type = new TypeFromAssembly(GetConfig(assembly), GetConfig(type)).Type;
}
}
The AbstractResourceType<T> class is used for providing us with types that need to be instantiated at run time. The names of these types are located in the resources and therefore need to be extracted. We can again see the GetConfig(string) method used here.
This time it is used to provide the name of the assembly to be loaded at run time, and the type to be instantiated. This method is defined in the parent class named Config<T> which we shall observe next.
abstract class Config<T> where T : IConfigurable, new()
{
XElement XmlConfig { get; set; }
public Config()
{
XmlConfig = new T().GetConfig();
}
protected string GetConfig(string source)
{
return XmlConfig.Element(source).Value;
}
The Config<T> class will provide us with a configuration class that will be used to get types from the resources or from XML files located outside of the Generic Database Access assembly.
Since the generic type T derives from the IConfigurable interface, it represents a class that holds the data required to instantiate a type at run time. To facilitate this, the appropriate action will take place in the constructor. The GetConfig() method will be called from the new instance of the generic type T which will return an XML file contained within the XElement type.
We can also see the GetConfig(string) method which, as we now understand returns the XML file that was acquired in the constructor and returns the value from one of its elements, whose name corresponds to the name of the parameter source.
We should also keep in mind that in the current case, we are interested in types located within the assembly resources.
The above mentioned is the reason why in the previous definition the generic type T in the Config<T> class has been closed by the ResourceConfig<T> class which we are going to examine next.
class ResourceConfig<T> : IConfigurable
{
public XElement GetConfig()
{
var reader = new GetStream<T>().Reader;
var config = XDocument.Parse(reader.ReadToEnd()).Root;
reader.Close();
return config;
}
}
The ResourceConfig<T> class which will be used for the closing of the generic type T in the previous class definition is used to get the specified resource stream, which is an XML file, parse its content and return it as an instance of XElement type.
To see just how the correct stream in provided, let us examine the GetStream<T> class.
class GetStream<T>
{
public StreamReader Reader { get; set; }
public GetStream()
{
Reader = new StreamReader(GetType().Assembly.GetManifestResourceStream(new LastType<T>().Name));
}
}
A simple glance at the current class definition is sufficient to tell us that the stream will be provided by the
Reader property which is loaded by accessing the current type and returning the stream from the assembly. The specific stream is determined by the
LastType<T> class and its property
Name.
To understand how the name of the stream that we require is provided, we shall need to further inspect the
LastType<T> class definition, which is as follows.
class LastType<T>
{
public string Name { get; set; }
public LastType()
{
var name = typeof(T).FullName;
var index = name.IndexOf(".");
Name = name.Remove(index, name.IndexOf(".", index) - 1)
.Insert(index, ".Resources.") + ".xml";
}
}
Similarly to the workings of the Data Exchange Mechanism[3], the relevant XML file will be provided from the resources by transforming the FullName property, i.e the namespaces and the class name of the generic type T, and it will be transformed to point in the direction of the resources folder within the assembly.
Furthermore a suffix ".xml" will be added to the path to indicate that we are opening an XML file. So with this mechanism now in place, we are able to load XML files from the resources, according to the generic type T.
Since, the type T in this question has been defined in the definition (1.5) to be of type ISelect, we shall be opening the ISelect.xml file from the resources.
The ISelect.xml file is defined in the following way.
<?xml version="1.0" encoding="utf-8"?>
<root>
<Type>GenericDataBaseAccess.Generic.Commands.GenericSelect</Type>
</root>
The definition is very simple and contains a single Type XML element which contains the full name of the type we need to instantiate at run time in order to execute the Select SQL command. And so, with this final definition, we have concluded the first part of the exposition.
We have successfully acquired the name of the type that will be used to execute the SQL command. The next part will be concerned with the Database-specific types and their roles in executing the SQL command.
3.3 Database-Specific Type Determination
We are now entering the second stage of the execution process. This is the core part of the inner workings of the Generic Database Access. The second stage is concerned with determining the Database-specific types needed to execute the SQL command.
Let us therefore take a closer look at the
GenericSelect class.
class GenericSelect : CoreQuery, ISelect
{
public DataSet Invoke<T>(T c)
{
SetParameters<T>(c, CommandType.Text);
Execute(() => new ExecuteQuery(Adapter, Command, Data));
return Data;
}
The GenericSelect class is instantiated at run time and used to execute Select SQL commands.
We can see that our class derives both from the CoreQuery class and the ISelect interface. The reason for it deriving from the CoreQuery class is because Select SQL commands are Query types of SQL commands and the CoreQuery class is the one that contains all the functionality required in order to execute such commands.
Let us now notice that the Invoke<T>(T) method is the one that is called in the definition (1.1). And it now follows that its generic type T represents our Business object and the parameter c its instance.
The derived method SetParameters<T>(T, CommandType) is used to set up the parameters used in our SQL command. By passing the CommandType.Text enum to the method, we indicate that the command will be in the textual form, and will not be a Stored Procedure.
On the other hand the Execute(Action) method is used to execute the SQL command. We add a new instance of ExecuteQuery class, and add to it all the required parameters needed to execute the SQL command.
In the end, the method will return the Data field which will contain all the data selected from the Database.
The ExecuteQuery class will now take our attention as we shall present its definition next.
class ExecuteQuery
{
public ExecuteQuery(DbDataAdapter adapter, DbCommand command, DataSet data)
{
adapter.SelectCommand = command;
adapter.Fill(data);
}
}
As we can see, this class is only concerned with setting the adapter parameter with the value contained in the command parameter, which will have the effect of setting the adapter to execute a specific Select command, contained in the command parameter. Once the command is executed the parameter data is filled with the data selected from the Database.
Let us now observe in just what context this class will be executed. For this we shall need to observe the CoreQuery class.
abstract class CoreQuery : GenericDataBase
{
protected DataSet Data = new DataSet();
protected void Execute(Action method)
{
try
{
method();
}
catch (Exception e)
{
new ActivateException(e);
}
}
}
This class definition is used as a template for all classes that will be used to execute Query types of SQL commands. We can see that it contains a field definition of type DataSet named Data. This is where the selected data from the Database will be located.
The Execute(Action) method will be invoked in order to execute the specified Query type command. It is executed in a safe context within a try-catch block. Within the try block we try to execute a delegate which contains the execution logic for the SQL command. Should the execution fail, we are instantly transferred to the catch block where we instantiate a class of type ActivateException and pass to it the parameter e which contains the exception.
Since there are two possible outcomes in this particular flow of execution, namely the successful and an unsuccessful one, we shall need to explain both flows in detail.
Let us first take the unsuccessful path, i.e. the one that would raise an exception, and after we have explained it, we shall simply proceed where we left off, with the successful path.
In order to observe the unsuccessful path, a condition of a raised Exception needs to be fulfilled. This is a situation where our Generic Database Access needs to provide us with generic Exception logging solution. And just such a solution is provided by the ActivateException class.
Therefore let us now observe the inner workings of this class.
class ActivateException
{
public ActivateException(Exception e)
{
new TypeOf<ActivateException>().Type.Activate(e);
}
}
Let us start out by immediately pointing out the fact that the mechanism used to provide us with Exception logging is also based on the mechanism used in the Data Exchange Mechanism[3] and that its goal is to load an outside assembly at run time and provide us with a type which contains the Exception logging logic and is going to be instantiated at run time in order to handle the exception.
Therefore, the single line of code we observe in the constructor of this class will load our required assembly, return the required type and instantiate it while passing to it the raised exception contained in the parameter e.
The assembly and the type needed to perform the previous operation is provided by the TypeOf<T> class which is defined in the following way.
class TypeOf<T> : AbstractType<T>
{
public TypeOf() : base("Assembly", "Type") { }
}
The TypeOf<T> class will simply invoke the base constructor of its parent class and pass to it two string values which will indicate that we need to access the XML elements named Assembly and Type.
The parent class, namely the AbstractType<T> class performs the same functionality as the class defined in (1.9) that is the AbstractResource<T> with the only difference being that this class extracts its values from an XML which is not located in the resources, thus we shall not go into details. Rather we shall examine the workings of the OuterConfig<T> class which is the one that will provide us with XML files that are not contained in the resources.
class OuterConfig<T> : IConfigurable
{
public XElement GetConfig()
{
return XDocument.Load(new PathFor<T>().Path).Root;
}
}
It should be easy to understand that this class will return an instance of XElement type, which is a representation of specific XML elements loaded from an XML file, with the path to the file being provided by the PathFor<T> class and its Path property, according to the type T.
To understand how we come to the possession of the path we shall need to observe the PathFor<T> class.
class PathFor<T>
{
public string Path { get; set; }
public PathFor()
{
Path = new CodebasePath().Value + @"\" +
typeof(T).FullName.Replace(".", @"\") + ".xml";
}
}
The PathFor<T> class is simply going to use the value contained in the Value property of the CodebasePath and add to it the relative path constructed from the name of the generic type T. For a detailed exposition of this mechanism the documentation on the Data Exchange Mechanism[3] should be studied.
The part which is not covered in the previously mentioned documentation will be covered next. This concerns the workings of the CodebasePath class.
class CodebasePath : GetPathFor<CodeBase> { }
This class definition will provide us with the path to the currently executing assembly, which in our case is the Generic Database Access assembly. We can see that the parent class in question is the GetPathFor<T> class and its generic type T is closed with CodeBase class.
Let us first take a look at the GetPathFor<T> class and how it interacts with the CodeBase class.
abstract class GetPathFor<T> where T : IPath, new()
{
public string Value { get; set; }
public GetPathFor()
{
Value = Path.GetDirectoryName(new T().Name);
}
}
Clearly the class definition will provide us with the name of a specified directory, which an instance of the generic type T will produce through its Name property. We should keep in mind that in our case the generic type T is actually the CodeBase class therefore let us see what kind of information does it provide for us.
class CodeBase : IPath
{
public string Name { get; set; }
public CodeBase()
{
Name = GetType().Assembly
.GetName()
.CodeBase;
}
}
The CodeBase class will simply provide us with the path to the currently executing assembly. This is done so that all the relevant XML files which are used to define the Exception handling types are located in our main application's starting folder, with its sub folder structure being GenericDataBaseAccess/Exceptions.
We should also point out that this mechanism will allow us to put the Generic Database Access assembly into the main application folder, should we use an ASP.NET project.
Likewise, if we are using a WCF service to make the calls to the appropriate classes which will invoke our assembly, the assembly will be able to be located in the folder where the WCF assembly is located.
Furthermore since the generic type T in the definition (2.8) was closed with the class named ActivateException, the corresponding XML file will have the same name and will be named ActivateException.xml. This XML file has the following structure.
<?xml version="1.0" encoding="utf-8"?>
<root>
<Assembly>ExceptionHandling.dll</Assembly>
<Type>ExceptionHandling.Handler</Type>
</root>
It should be easy to understand that the Assembly element contains the name of the assembly in which a type is defined that will be used to handle the Exception while the Type element contains its name.
So to return to the definition (2.8) once we have all the required data to construct the path to the required XML file, the file will be opened, and values from its Assembly and Type elements returned. Once this is performed, the Exception will be handled.
With this operation completed we have fully described the mechanism of Exception handling used by the Generic Database Access and thus finished the description we started in the definition (2.7) which describes the unsuccessful execution of a SQL command.
We now return to the definition (2.7) where we shall continue with the successful execution of the SQL command.
Once the method delegate has been invoked in the definition (2.7) and the Select command successfully executed, the Data field will be filled with the data selected from the Database.
3.4 Query Execution
To understand the intricate processes that were required to take place within the assembly, we will now have to observe the core class definition which is highest in the class definition hierarchy and represents the template for all SQL commands and Stored Procedures, namely the GenericDataBase class.
Let us thus proceed further with the third stage of the execution process and the GenericDataBase class.
abstract class GenericDataBase
{
protected DbDataAdapter Adapter { get; set; }
protected DbConnection Connection { get; set; }
protected DbCommand Command { get; set; }
protected DbCommandBuilder Builder { get; set; }
protected void SetParameters<T>(T c, CommandType type)
{
new ActivateProperties<GenericDataBase>(this);
new SetupProperties<T>(c, Command, Connection, Builder, type);
}
}
While taking a more detailed look at the class definition, we can observe that it declares four properties, which are all abstract classes. These properties represent the instances which will be used for communicating with the Database and executing the required SQL commands. These are as follows.
Adapter property of type DbDataAdapter represents an instance which will execute the specified SQL commands.
Next, the Connection property of type DbConnection represents an instance which will hold the Connection string to the Database.
The Command property of type DbCommand represents an instance which will determine whether we are executing a text based SQL command or a SQL Stored Procedure, and will accordingly store the text of the command or the name of the Stored Procedure.
The Builder property of type DbCommandBuilder represents an instance which will be used to derive the parameters from Stored Procedures.
We can also further notice that none of the properties in question are instantiated within the constructor explicitly. This will be performed by the ActivateProperties<T> class. After the properties are filled with non-abstract Database-specific types, they will be used in the SetupProperties<T> class in order to create parameters for the required SQL command.
Therefore let us now turn to the ActivateProperties<T> class.
class ActivateProperties<T>
{
public ActivateProperties(T source)
{
var properties = source.ProtectedProperties();
var dictionary = new FillDictionary<T>(properties).Dictionary;
new CreateProperties<T>(source, properties, dictionary);
}
}
The current class under exposition here, is in charge of adding object references to properties defined in the previous class definition. In other words this class needs to create instances of types at run time and add them to the corresponding properties.
We should first notice since we have used the GenericDataBase class to close the generic T type we are dealing with here, the source parameter is the instance of the GenericDataBase class and its properties will be extracted to the properties variable.
Then the FillDictionary<T> class will be used to extract the relevant Database-specific values needed to successfully execute a SQL command and match them with the corresponding properties.
Lastly the CreateProperties<T> class will be used to create new instances of extracted types and add them to the corresponding properties.
To understand the exact process of Database-specific type extraction, we shall present the FillDictionary<T> class.
class FillDictionary<T>
{
public Dictionary<string, Entry> Dictionary = new Dictionary<string, Entry>();
public FillDictionary(List<PropertyInfo> properties)
{
new SetupDictionary<T>(Dictionary, properties, new NoValue(), new NoData());
new SetupDictionary<T>(Dictionary, properties, new HasValue(), new HasData());
}
}
We can notice that the class definition contains the single field named Dictionary of type Dictionary<string, Entry> which is used to match the property names defined in the GenericDataBase class defined in (3.6) and Property XML elements from the Database-specific setup XML file.
The constructor on the other hand will provide us with a list of properties extracted from the class definition (3.6). These are the properties that need to be instantiated with the Database-specific types. This process will be executed in the SetupDictionary<T> class. We can see this class instantiated twice in the constructor. The first line of code will be used to match the properties which do not have any additional data in the Data XML elements, while the second one is used to set the properties that have some additional values defined.
In order to fully understand how this process takes place we will need to take a brief look at all the class definitions used, and then we shall move on to the definition of the SetupDictionary<T> class.
Thus we turn to the brief description of the Entry class.
class Entry
{
public string Assembly { get; set; }
public string Type { get; set; }
public List<NameValue> NameValues = new List<NameValue>();
public Entry(XElement element)
{
Assembly = element.Element("Assembly").Value;
Type = element.Element("Type").Value;
}
}
It is easy to see that this is a container type class which will only provide us with simple values extracted from the Database-specific setup XML file. The Assembly property will hold the name of the Database-specific assembly name which needs to be loaded. The Type property will hold the Database-specific type that needs to be instantiated.
On the other hand the NameValues list will contain a collection of all the values provided by the Database-specific XML file, which are contained in the Data XML elements. The NameValues type being a simple container type, contains only the Name property that will hold the name of the Database-specific Boolean property defined in one of the Database-specific types that will be instantiated in order for the instance to be configured further.
It will also contain a Value property which will simply contain either the True or False string which will be parsed and used as a bool value for the property specified by the Name property.
The next class to observe is the NoValue class.
class NoValue : IValue
{
public bool Exists(string name, XElement element, List<PropertyInfo> properties)
{
return new PropertyElement().Exists(name, element, properties) &&
!new DataElement().Exists(element);
}
}
This class will test whether there exists a property in the properties parameter with the same name specified by the name parameter in one of the elements of the element parameter. Furthermore the class will test whether the element parameter is free of any Data XML elements.
If this is true then this would imply that the Database-specific setup XML file contains elements which are a match for the properties defined in the definition (3.6), and that there are no additional values that need to be considered while instantiating the Database-specific types.
Therefore now we can present the NoData class which is used if the above conditions are met.
class NoData : IData
{
public void SetValues(Dictionary<string, Entry> dictionary, XElement e)
{
dictionary.Add(e.Element("Name").Value, new Entry(e));
}
}
It should be easy to understand that the dictionary parameter will take an instance of the Dictionary<string, Entry> type, which will be provided in the definition (3.8).
The parameter e of the type XElement will then have its values extracted, that its, the elements which correspond to the names of the properties of the Entry class will be filled by the values from the matching XML elements from the parameter e. Once this is done, the new instance will be placed within the dictionary as a new entry.
In the case of there being some Data XML elements defined in the Database-specific setup XML file, we shall need to take a look at the rest of the definitions which are as follows.
class HasData : IData
{
public void SetValues(Dictionary<string, Entry> dictionary, XElement e)
{
var entry = new Entry(e);
e.Elements("Data")
.Elements("Name")
.Zip(e.Elements("Data").Elements("Value"), (n, v) => new NameValue(n.Value, v.Value))
.ToList()
.ForEach(x => entry.NameValues.Add(x));
dictionary.Add(e.Element("Name").Value, entry);
}
}
We should notice that this class produces the same functionality as the previous class definition, with a further addition of extracting values from the Name and Value elements which are sub elements of the Data element.
These values will be used to further modify the Database-specific type which will be instantiated at run time. Also the definition of the HasValue class will not be presented here because the only difference this class exhibits, as opposed to the NoValue class is that the HasValue lacks the negation of the second bool value. Thus this class tests for the existence of any Data XML elements.
We are now able to proceed to the SetupDictionary<T> class definition.
class SetupDictionary<T>
{
public SetupDictionary(Dictionary<string, Entry> dictionary,
List<PropertyInfo> properties, IValue value, IData data)
{
new XmlData<T>().XmlValues
.Elements("Property")
.Where(element => value.Exists("Name", element, properties))
.ToList()
.ForEach(element => data.SetValues(dictionary, element));
}
}
The SetupDictionary<T> class will, once it takes the appropriate values passed to it from the parameters as shown in the definition (3.8), load the Database-specific setup XML file and extract its Property elements.
Once this is done, it will test for the existence of specific properties within the Name XML element. And should those specific properties exist, they will then be matched with the corresponding values from the loaded XML file.
The loaded XML file is provided by the XmlData<T> class which is defined as follows.
class XmlData<T>
{
public XElement XmlValues { get; set; }
public XmlData()
{
XmlValues = XDocument.Load(new PathFor<T>().Path).Root;
}
}
It should be quite easy to notice that this class will provide us with an XML file whose path will be determined by the PathFor<T> class defined in (3.1).
Since the generic type T in this case is of type GenericDataBase, the XML file that will be loaded and presented will be found in the folder containing the Generic Database Access assembly within the GenericDataBaseAccess folder. While the file name of the XML file will be GenericDataBase.xml.
Once this operation is produced all the properties that will be used to execute the SQL commands will be matched with the corresponding values from the loaded Database-specific setup XML file.
And thus, with this mechanism now in place, we move on to the next part of the presentation, namely the creation of the above mentioned properties. Let us therefore continue with the definition (3.7) and explore the CreateProperties<T> class invoked inside the definition.
class CreateProperties<T>
{
public CreateProperties(T control, List<PropertyInfo> properties, Dictionary<string, Entry> dictionary)
{
properties.Where(property => dictionary.Keys.Contains(property.Name))
.ToList()
.ForEach(property => new SetValue<T>(control, property, dictionary));
}
}
This class is now used to instantiate all the selected properties from the previous steps. First, all the properties that have their name contained in the dictionary parameter are selected and then, for all such properties, their values are set by the SetValue<T> class which is defined as follows.
class SetValue<T>
{
public SetValue(T control, PropertyInfo info, Dictionary<string, Entry> dictionary)
{
var item = dictionary[info.Name];
if (!string.IsNullOrEmpty(item.Type))
{
info.SetValue(control, new ActivateDictionary(item).Instance, null);
}
}
}
We can outright see that a single property will have its name matched within the dictionary parameter and then its returning value will be returned into the item variable and should the Type property of the item variable not be empty, we shall set the value to the corresponding property.
This functionality will of course be provided by the ActivateDictionary class so let us further proceed with examining its definition.
class ActivateDictionary : ActivateSource<object>
{
public ActivateDictionary(Entry entry)
{
Instance = new TypeFromAssembly(entry).Type.Activate();
new SetValueToInstance(entry, Instance);
}
}
The class in question will take as its single parameter of type Entry all the needed values to derive the needed types for our property to be instantiated. The derived property Instance of type object is instantiated from the Type property exposed by the TypeFromAssembly class while invoking its Extension method Activate().
We should now remember the definition (4.2) where we mentioned the further modification of Database-specific types. This will be executed in the SetValueToInstance class.
Let us first take a look at the TypeFromAssembly class and how it will help us to derive the needed type.
class TypeFromAssembly
{
public Type Type { get; set; }
public TypeFromAssembly(Entry entry)
{
Type = new LoadType(entry.Assembly, entry.Type).Type;
}
public TypeFromAssembly(string assembly, string type)
{
Type = new LoadType(assembly, type).Type;
}
}
This quite a simple class definition will load an appropriate type using the LoadType class and store it in the Type property. In our case we shall invoke the constructor with the simple parameter of type Entry.
We should now proceed to the LoadType class.
class LoadType
{
public Type Type { get; set; }
public LoadType(string assembly, string type)
{
Type = new LoadAssembly(assembly).Assembly.GetType(type);
}
}
We can see that the LoadType class, in order for us to acquire the type in question, will instantiate the LoadAssembly class and add to it the previously mentioned values. This will have the effect of loading an assembly, and extracting from it the specified type.
Just how an assembly is exactly loaded, we shall explore in the definition of the LoadAssembly class.
class LoadAssembly
{
public Assembly Assembly { get; set; }
public LoadAssembly(string source)
{
Assembly = Assembly.LoadFrom(new UriPathForAssembly(source).Value);
}
}
The assembly in question is loaded by providing a path to its destination using the UriPathForAssembly class. This class will transform a relative path to an URI path, in order for our assembly to be located.
To see this, let us take a look at the following class definition.
class UriPathForAssembly
{
public string Value { get; set; }
public UriPathForAssembly(string assembly)
{
Value = Path.Combine(new CodebasePath().Value, assembly).ToUri();
}
}
This class will simply take the name of the assembly we specified earlier, located in the assembly parameter, together with the value provided by the CodebasePath, presented in the definition (3.3) and combine them to form a relative path to the assembly in question. Once this path is produced, we simply invoke the ToUri() Extension method which will simply produce a local URI path to the said assembly.
Once this action is completed, we are back to the definition (4.7) and we have filled our Instance property with the appropriate value of the specified type.
So let us now proceed to the SetValueToInstance class.
class SetValueToInstance
{
public SetValueToInstance(Entry entry, object instance)
{
foreach (var nameValue in entry.NameValues)
{
instance.GetType()
.GetProperty(nameValue.Name)
.SetValue(instance, bool.Parse(nameValue.Value), null);
}
}
}
The SetValueToInstance class will simply perform the following operation for each item in the NameValues property of the entry parameter..
From the instance parameter, a property with the name corresponding to the Name property from the nameValue variable will be extracted and loaded according to the value located in the Value property from the nameValue variable.
We should note that we will only be able to set Boolean values to further modify our instance. When this process is at its end, we are in a possession of all the required Database-specific types, which are instantiated, modified accordingly, and set to the appropriate properties from the GenericDataBase class.
In going back to the definition (3.6) where an instance of SetupProperties<T> class is instantiated, we will proceed by examining this class definition next and we should also mention that with this last definition now in place, all prerequisites have been put in place to finally start with the execution of the SQL command.
To see how this is done, let us observe the following class definition.
class SetupProperties<T>
{
public SetupProperties(T c, DbCommand command,
DbConnection connection, DbCommandBuilder builder, CommandType type)
{
connection.ConnectionString = new GenericConnection().Value;
command.CommandType = type;
command.CommandText = new GenericCommand<T>().Value;
command.Connection = connection;
new GenericParameters<T>(c, command, connection, builder);
}
}
The SetupProperties<T> class, as we can see, will contain as its first parameter the parameter of type T which represents our Business object containing properties which correspond to the parameters in our SQL command.
The rest of the parameters will be used to specify just how exactly our command will be executed. The ConnectionString property of the connection parameter, which represents the Connection string that we will use to connect to the Database is filled using the GenericConnection class.
Next the CommandType property of the command parameter, which we use to represent the actual SQL command is filled by the type parameter. This will let us indicate whether we are dealing with a simple SQL command or a SQL Stored Procedure.
The CommandText property of the command parameter will be loaded by the GenericCommand<T> class. This class will provide us with either the text based command itself, or the name of an SQL Stored Procedure. Since we are executing a simple Select command, we are here dealing with a textual representation of an SQL command.
Next the Connection property of the command parameter is set to the connection parameter. This will simply let the command property use the Connection string we decided to use earlier.
In the end, we see that we have instantiated the GenericParameters<T> class which will create our parameters that we shall use in our SQL command.
In order for us to be able to proceed further, let us first explore the GenericConnection class definition which takes the following form.
class GenericConnection : AbstractDataElement<GenericDataBase>
{
public GenericConnection() : base("Connection") { }
}
The GenericConnection class is one of the classes from the AbstractDataElement<T> child series classes. These particular classes derive from the AbstractDataElement<T> and pass to its base constructor a single string parameter which represents the name of a specific XML element within the Database-specific setup XML file. This will allow the parent class to access the value from the specified element.
In the present case we are accessing the Connection XML element and retrieving the Connection string, by which we are going to connect to the Database.
To understand in finer detail the process behind this XML manipulation, we shall need to observe the AbstractDataElement<T> class definition which is defined in the following manner.
abstract class AbstractDataElement<T>
{
public string Value { get; set; }
public AbstractDataElement(string element)
{
Value = new ValueFor<T>().From(element);
}
}
This very simple class will take as its single parameter the name of a specific XML element and return its value by storing it in the
Value property. Clearly, the location of the XML file which is to be opened is determined according to the generic type
T.
Since the class in charge of determining the location, and retrieving the data in question is the
ValueFor<T> class, we shall take up its examination next.
class ValueFor<T>
{
public string From(string element)
{
return XDocument.Load(new PathFor<T>().Path)
.Root
.Element(element)
.Value;
}
}
We should have no trouble in understanding the intricate process currently presented at hand, since it is simply the case that the XDocument class is used to open an XML document, located by the PathFor<T> class which we have already defined in (3.1).
Once the document is retrieved the single string parameter named element will be used to extract and return the value from the XML element it will specify for us.
We are now going back to the definition (5.3) and continuing where we left off. The next class that is used in the process is the GenericCommand<T> class. Since this is also one of the AbstractDataElement<T> series of classes, we shall not present its definition here.
Suffice it to say that this class will extract the SQL command text from the Command XML element which will then be used to execute the SQL command in question. The XML file will be selected according to the generic type T.
3.5 Parameter Creation
The fourth and final stage execution process starts with one of the more complex class definitions, namely the
GenericParameters<T> class.
class GenericParameters<T>
{
public GenericParameters(T data, DbCommand command, DbConnection connection, DbCommandBuilder builder)
{
var prefix = new ParameterPrefix().Value;
if (command.CommandType == CommandType.StoredProcedure)
{
if (builder == null)
{
command.Parameters.AddRange(new WithDirection<T>(data, prefix).Parameters.ToArray());
}
else
{
new TryConnection(() => new DeriveParameters(builder, command), connection);
new SetupParameters<T>(data, command);
}
}
else
{
var names = new ParameterNames(command, prefix).Names;
command.Parameters.AddRange(
new WithoutDirection<T>(data, prefix, names).Parameters.ToArray());
}
}
}
This class definition is placed in charge of handling the parameter creation process. Since our SQL commands will in certain cases use parameters, we will need to be able to create a different number of different types of parameters to faithfully execute the SQL command. Furthermore, the parameters will need to be of a Database-specific type.
To see just how this is accomplished, let us proceed by observing the
ParameterPrefix class. This class will simply return the prespecified prefix, used to denote arguments in SQL commands from the Prefix XML element in the Database-specific XML file and since this class is one of the classes from the
AbstractDataElement<T> class series we shall not discuss its details any further.
The next step in our parameter creation process is the branching between two different cases. In the first case, should the
if command evaluate to true, we know that we are executing a SQL Stored Procedure. Should this not be the case, we will be executing a simple SQL text based command. Since it is in fact the case that we are executing a simple Select command, let us proceed with the
else part of the code block.
Within the variable
names we shall store the list of parameter names that will be used in our SQL command. The process of extraction of these parameters from the command itself is performed by the
ParameterNames class which we shall present next.
class ParameterNames
{
public List<string> Names { get; set; }
public ParameterNames(DbCommand command, string prefix)
{
Names = Regex.Matches(command.CommandText, @"\" + prefix + @"\w+")
.Cast<Match>()
.Select(x => x.Value.Replace(prefix, ""))
.ToList();
}
}
As it might have been expected, this simple class is going to use the
Regex class to extract all the words, that start with a specific character. In our case this character is the Database-specific prefix character, used to denote the parameters.
Once the parameters are extracted, they will be stripped off the prefix and returned by the
Names property.
After the names of the parameters have been provided, we create a collection of Database-specific parameters, using the
WithoutDirection<T> class and add them to the
command parameter using the
AddRange(Array) method. This class in question will be discussed next.
class WithoutDirection<T> : SetDirection
{
public WithoutDirection(T data, string prefix, List<string> names)
{
new NoDirection<T>(data, Parameters, prefix, names);
}
}
Let us first point out the fact that this class derives from the SetDirection class and it inherits the Parameters field of type List<DbParameter>. The process of parameter creation is performed in the SetDirection class, while the values to the parameters are added in the NoDirection<T> class.
Let us now take a closer look at how the parameters are created and then we shall come to understand how they are set up to be used later.
abstract class SetDirection
{
public List<DbParameter> Parameters = new ActivateListOf<DbParameter>().Instance;
}
We should try and understand the composition of this class as follows. Since the number of SQL command parameters can range between zero and any number the Database allows for its command, we would need to create a list that can store any number of parameters.
Furthermore since we do not know upfront the specific Database system we are dealing with, we shall need to make this list, a list of
DbParameter types, since all the Database-specific parameters derive from this type.
We can now use the
ActivateListOf<T> class to create the Database-specific parameters and store them in the
Parameters field. Once we close the generic type
T with the
DbParameter type, we have indicated that our newly created list will derive from this specific type.
To see just how this will be accomplished, let us look further into the
ActivateListOf<T> class.
class ActivateListOf<T>
{
public List<T> Instance { get; set; }
public ActivateListOf()
{
var listType = typeof(List<>).MakeGenericType(new TypeFrom<GenericDataBase>().Type);
Instance = Enumerable.ToList<T>((IEnumerable<T>)Activator.CreateInstance(listType));
}
}
This class definition will be easy to understand since there are only two lines of code which will perform the complete operation of creating the parameters in question.
The first line will simply take the type of a generic list, which is a
List<> type. Then we invoke its
MakeGenericType(params Type[]) method, and pass to it the type provided by the
TypeFrom<T> class. This will have the effect of creating a generic list type from the specified type provided within the parameter.
The second line of code, will quite simply cast the newly created instance provided by the
Activator class and transform it into a generic list, where the generic type is the type defined in the previous line of code.
Let us remember that we have now created a generic list of Database-specific parameters. To complete this process we shall need to understand how it was determined which type of parameters need to be instantiated. Let us thus proceed to the
TypeFrom<T> class definition.
class TypeFrom<T> : AbstractType<T>
{
public TypeFrom() : base("ParameterAssembly", "ParameterType") { }
}
This class will simply specify the two XML elements from which the relevant data needs to be extracted. It should be easy to understand that the string "ParameterAssembly" will return the assembly from which the type needs to be extracted, and the "ParameterType" will return the type of the parameter which will be instantiated.
Also since this class derives from the AbstractType<T> which is virtually identical to the AbstractResourceType<T> class that was already defined in (1.9) we shall not go any further into its exposition.
We shall instead, without the loss of generality proceed with the definition of the next class in line, which is the NoDirection<T> class.
class NoDirection<T>
{
public NoDirection(T data, List<DbParameter> parameters, string prefix, List<string> names)
{
data.PublicProperties()
.Where(property => new Name().Exists(property, names))
.ToList()
.ForEach(property => parameters.Add(new Parameter<T>(data, property, prefix).Instance));
}
Let us now take into consideration the data parameter of type T.
This parameter is our Business object containing all the properties which represent the parameters in our SQL command. What we now need to do, is to take the values from those properties and place them in the previously created parameters.
In order to do this, we first take all the public properties from our Business object. Next we select only those properties whose name matches the name of the parameter. Once this is done, we simply add all the new parameters to the parameters constructor parameter of type List<DbParameter>.
By doing so, we have successfully matched all the properties in the Business object with the parameters created earlier and we have all the values set up in the parameters which will pass the relevant values to our SQL command that will be executed.
We are now left only with two class definitions of which the Name class is the first one to be taken into consideration.
class Name
{
public bool Exists(PropertyInfo property, List<string> names)
{
return names.ConvertAll(x => x.ToLower())
.Contains(property.Name.ToLower());
}
}
This class is the one in charge of determining which properties will be matched to which parameters. The very simple idea behind this process is to simply convert all the elements representing the names of properties and parameters to lower case letters and then match them by name.
The last class in question is the Parameter<T> class which is used to create the new Database-specific parameter. The class takes the following form.
class Parameter<T> : AbstractParameter<T>
{
public Parameter(T data, PropertyInfo property, string prefix) : base(data, property, prefix) { }
public Parameter(T data, PropertyInfo property, string prefix, ParameterDirection direction)
: base(data, property, prefix)
{
Instance.Direction = direction;
}
}
In our specific case, we instantiate the first constructor which adds no direction to the parameter, unlike the second one. Thus this constructor will simply pass on the control to its base constructor which is defined in the
AbstractParameter<T> class which we shall observe next.
Here we consider the
AbstractParameter<T> class definition in more detail.
abstract class AbstractParameter<T>
{
public DbParameter Instance { get; set; }
public AbstractParameter(T data, PropertyInfo property, string prefix)
{
Instance = new Activate<DbParameter>(prefix + property.Name, property.GetValue(data, null)).Instance;
}
}
The class, being quite simple to understand, is simply in charge of creating a new instance of a Database-specific type and storing it to the Instance property which is of type DbParameter. We should keep in mind that the reason for using the DbParameter type is that all the Database-specific types in the ADO.NET framework, which are used to represent parameters, derive from the DbParameter type.
The way we are going to instantiate our parameter is the following one. We simply create a new instance of the Activate<T> class and pass to it the relevant parameters.
The first parameter will simply be the name of the parameter to which we add a prefix which will denote the Database-specific prefix used to denote parameters. The second parameter will extract the value from the specific property, from our Business object, which is contained in the data parameter.
We should note that the property being extracted for value is the one whose name matches the name of the parameter in question.
And so with this last piece of mechanism now in place, we have successfully constructed the relevant parameters and placed them inside the SQL command to be executed. Thus the complete process of executing a Select SQL command has been described in detail.
We shall next turn to explaining the remaining mechanisms which are used to execute different types of commands which were not needed in this specific context.
3.6 Non Query Type Determination
For the Insert SQL command, being a different type of command as opposed to a Select command, we use different classes in order to execute it. We shall now present the program flow that is traversed in order for an Insert command to be executed while leaving aside the class definitions shared with the previous execution context.
Let us therefore start with the exposition of the Insert<T> class which will be the entry point to our assembly in order for us to execute this specific type of command.
public class Insert<T> : AbstractProcedure<int, IInsert>
{
public Insert(T c)
{
Results = Procedure.Invoke<T>(c);
}
}
We can readily observe the similarity between the Insert<T> and Select<T> class definitions. The only marked difference that should be noticed in this instance is that since an Insert command is a Non Query type command, instead of queried data, we need to return an indicator which will notify us of how our command has been executed.
Therefore, instead of the DataSet used in the definition (1.5) we use the int type in order to determine the type for the Result property that our command will return.
Once the Insert SQL command is executed, two possible outcomes exist, with three possible value ranges. Depending on the outcome of the executed command, there is a successful and an unsuccessful outcome.
In one case the command can execute successfully and return the number of rows affected, that is, the number of rows inserted. Thus our result is the integer value representing the number of rows inserted which is larger than zero.
The next case is where we successfully execute the command yet no rows are inserted. In this case we return an integer value of zero.
While the last possible outcome is an unsuccessful execution, in which case we need to return an integer value of -1.
We can easily conclude that the code flow is the same as is the case with the Select<T> class up until the GenericInsert class definition which we shall present next.
class GenericInsert : CoreNonQuery, IInsert
{
public int Invoke<T>(T c)
{
SetParameters<T>(c, CommandType.Text);
Adapter.InsertCommand = Command;
return Execute(Adapter.InsertCommand.ExecuteNonQuery);
}
}
Again, we see a similar class definition to the definition (2.5). The only marked difference is the return type, this time being an int instead of a DataSet type. Clearly when the SQL command executes, the success of the execution will be returned from the Invoke<T>(T) method.
Another difference is the fact that this class derives from the CoreNonQuery class which is only natural since an Insert type command is a Non Query type command. Let us therefore look at the CoreNonQuery class definition next.
abstract class CoreNonQuery : GenericDataBase
{
protected int RowsAffected { get; set; }
protected int Execute(Func<int> method)
{
try
{
Connection.Open();
RowsAffected = method();
}
catch (Exception e)
{
RowsAffected = -1;
new ActivateException(e);
}
finally
{
Connection.Close();
}
return RowsAffected;
}
We can notice the difference between this class and the one defined in (2.5) by observing the parameters.
In this case, we have a delegate of Func<int> type. The reason for this is that once the SQL command is executed it will need to return an indicator of type int which will notify us of how the command was executed. This value is conveniently stored in the RowsAffected property.
We can also notice the try-catch-finally block in the constructor itself. Within the try block we first open the connection to the Database and then execute the Insert SQL command by invoking the method delegate. Should the command finish successfully, the number of affected rows will be stored in the RowsAffected property.
On the other hand, should an Exception occur, we will enter the catch block where we set the RowsAffected property to the value of -1, in order to indicate a failed execution. We then proceed to handle the Exception in the way defined in (2.8).
In any case, within the finally block, the connection will be closed.
With this last class definition presented, we have explained all the relevant differences between the Query type SQL command execution process and the Non Query type. We next turn to executing the Query type SQL Stored Procedures.
3.7 Stored Procedure Determination
Aside from simple text based SQL commands, we will need to be able to execute SQL Stored Procedures. Since there are two types of Stored Procedures, those being the Query and Non Query types, we shall also need to have two different class definitions to represent them.
Let us therefore start with the Query type Stored procedure.
public class Query<T> : AbstractProcedure<DataSet, IQuery>
{
public Query(T c)
{
Results = Procedure.Invoke<T>(c);
}
The Query<T> class definition, which is used to execute Query type Stored Procedures is, as can be expected, quite similar to the Select<T> class definition. Thus we shall waste no further time concerning ourselves with it any further. Instead we shall simply point out the further fact that the command flow between these two classes is also the same, up to and including the point which we shall discuss next.
This class in question is the the GenericQuery class defined in the following way.
class GenericQuery : CoreQuery, IQuery
{
public DataSet Invoke<T>(T c)
{
SetParameters<T>(c, CommandType.StoredProcedure);
Execute(() => new ExecuteQuery(Adapter, Command, Data));
new AddValuesToParameters<T>(c, Command);
return Data;
}
}
We can notice that, the SetParameter<T>(T, CommandType) method is invoked using the CommandType.StoredProcedure value for its second parameter. This is how we establish that we are indeed dealing with an SQL Stored Procedure, as opposed to an SQL text based command.
The only novelty we should point out in the current class definition is the instance of the AddValuesToParameters<T> class which will add values to specific properties which represent our parameters that return some values from the Stored Procedure.
The properties that will have values added to them are the ones that match any parameter which is defined as either Output, Return or InputOutput. These are the parameters that return specific values from a Stored Procedure.
To see how we can achieve this, we should continue to the AddValuesToParameters<T> class definition.
class AddValuesToParameters<T>
{
public AddValuesToParameters(T c, DbCommand command)
{
foreach (DbParameter parameter in command.Parameters)
{
foreach (var property in c.PublicProperties())
{
if (parameter.HasValueFor(property))
{
new Fill(parameter.IsDBNull()).Method.SetValue(c, parameter, property);
}
}
}
}
}
The class definition in question will perform the following operation.
It will extract all the parameters from the command parameter which represents our SQL Stored Procedure to be executed. Then, for each of those parameters, it will extract all the properties from the parameter
c, which is nothing more than our Business object.
During this process, we will simply test if a certain property is a match for a certain parameter, using the
HasValueFor(PropertyInfo) Extension Method. Should this evaluate to true, we then fill the corresponding property within our Business object, using the
Fill class, in order for it to contain the value returned by the parameter from the Stored Procedure.
To see just how this can be achieved, let us first start by observing the
HasValueFor(PropertyInfo) Extension method defined in the
ValueExtension class.
static class ValueExtension
{
public static bool HasAttributes(this PropertyInfo property)
{
var attributes = property.GetCustomAttributes(true);
return attributes.OfType<OutputAttribute>().Any() ||
attributes.OfType<InputOutputAttribute>().Any() ||
attributes.OfType<ReturnAttribute>().Any();
}
public static bool HasValueFor(this DbParameter parameter, PropertyInfo property)
{
return parameter.CompareName(property.Name) &&
parameter.IsNotNull() &&
property.HasAttributes();
}
}
This class in question defines the
HasValueFor(PropertyInfo) Extension method in which we are currently interested. This method will test three Truth Values, and should all three evaluate to true, it will return true also.
The first line, will determine if the name of the parameter matches the name of the property. The second line will test if the parameter contains any values. While the third line will invoke the Extension Method defined above which will simply determine if the property in question has been decorated by any custom attributes of either
OutputAttribute,
InputOutputAttribute or
ReturnAttribute type.
Should any of those statements return true, the
HasAttribute() method will return true as well.
Next we come to the
Fill class.
class Fill : Dictionary<bool, IParameterMethods>
{
public IParameterMethods Method { get; set; }
public Fill(bool value)
{
Add(true, new ParameterDBNull());
Add(false, new ParameterNotDBNull());
Method = this[value];
}
}
This class will have the task of determining how the property will be filled with the required value. Since this class derives from the
Dictionary<T, K> class, it is a Dictionary class itself. The types used to close its generic types are
bool and
IParameterMethods.
Within the constructor, we shall simply add two new key-value pairs, with the keys corresponding to all possible
Boolean values, those being true and false. While the values will represent instances of types which will be used in either the case where our parameter does or does not contain a null value.
These types derive from the
IParameterMethods interface and are used to fill the property with the required value.
Once a
Boolean value has been passed to this class instance it will return the corresponding class instance by storing it in the
Method property, to fill the property with values.
We shall now observe the
ParameterNotDBNull class definition.
class ParameterNotDBNull : IParameterMethods
{
public void SetValue(object c, DbParameter parameter, PropertyInfo property)
{
property.SetValue(c, parameter.Value, null);
}
}
Since the only prerequisite to implement the IParameterMethods interface is to implements its single method SetValue(object, DbParameter, PropertyInfo) we can see that the current class definition fully complies with the interface.
The single course of action its constructor will take is that it will set the value to the property in question and use the value from the corresponding parameter.
The same operation will be performed by the ParameterDBNull class, with the single exception where instead of the parameter value, we pass the null value to the property, indicating that no values will be returned.
Once this is completed we can continue to the next class definition which differs in the program flow taken by the Select<T> class. We thus come back to the definition (5.7) that is, the GenericParameters<T> class. This time, the first if command will evaluate to true since we are dealing with a SQL Stored Procedure.
The second if command will then test if the builder parameter is null. The builder parameter is of type DbCommandBuilder. This class is used to extract parameter definitions from the Stored Procedures located in the Database system itself.
Should the if statement evaluate to true, then the WithDirection<T> class will provide us with the required parameters, which we shall then add to the Parameters property of the command parameter.
On the other hand should the if command evaluate to false, we will then derive the parameters from the Stored Procedure itself, and then set them up accordingly.
Let us first take a look how the WithDirection<T> class would deal with the problem, should it be invoked.
class WithDirection<T> : SetDirection
{
public WithDirection(T data, string prefix)
{
new Direction<T, InputAttribute>(data, Parameters, prefix, ParameterDirection.Input);
new Direction<T, OutputAttribute>(data, Parameters, prefix, ParameterDirection.Output);
new Direction<T, InputOutputAttribute>(
data, Parameters, prefix, ParameterDirection.InputOutput);
new Direction<T, ReturnAttribute>(data, Parameters, prefix, ParameterDirection.ReturnValue);
}
In the case where we are not using the Builder property defined in (3.6) to derive the parameters from the Stored Procedure, we will need to decorate the properties in our Business object with attributes in order to denote their direction. These attributes are as follows.
InputAttribute is used to decorate properties which correspond to IN parameters in Stored Procedures.
OutputAttribute is used to decorate properties which correspond to OUT parameters in Stored Procedures. InputOutputAttribute is used to decorate properties which correspond to INOUT parameters in Stored Procedures. While the ReturnAttribute is used to represent Return parameters.
We can see quite clearly that just as the WithDirection<T> class, this one derives from the SetDirection class. The only difference is that this class instantiates four Direction<T, K> classes in order to set up the parameters and their directions.
Every instantiated Direction<T, K> class will have its generic type K closed by a different attribute type so that all the properties can be extracted and matched to the parameters accordingly.
class Direction<T, K>
{
public Direction(T data, List<DbParameter> parameters, string prefix, ParameterDirection direction)
{
data.PublicProperties()
.Where(property => new AttributeOf<K>().ExistsIn(property))
.ToList()
.ForEach(property => parameters.Add(new Parameter<T>(
data, property, prefix, direction).Instance));
}
}
}
The
Direction<T, K> class is quite similar to the
NoDirection<T> class with the difference being that the
direction parameter will take
ParameterDirection enum type in order to define the parameter direction.
With this last point, we can conclude the current chapter since the complete program flow for all types of SQL commands has been successfully explained.
Configuration Application
4.1 Database-specific Setup
Before we start with the description of the Configuration Application, we should point out the fact that this application is by its nature optional. In other words, the working of the Generic Database Access assembly is independent of the Configuration Application itself. The assembly can be used in any project without using the helper application.
The Configuration Application exists to provide easier use of the Generic Database Access assembly, but all the above mentioned XML files can be set up by hand without the helper application.
Let us therefore start with the Configuration Application itself.
The application consists of three main Tab Items. These represent the Setup Item, Commands Item and the Exceptions Item. We shall start with the Setup Item, which can be reached by clicking the Tools Menu Item and selecting Setup.
Image 2. – Setup Tab Item of the Configuration Application.
We can observe three rows of main Text Box controls. These controls represent the XML elements from the Database-specific setup XML file. In this particular case, we are presented with an Oracle-specific setup configuration.
Let us now look into the controls more closely.
Connection String:
This control is used to enter the Connection String which will be used to connect to the Database.
Prefix:
This control is used to define the prefix that will be used to denote the SQL command parameters. Since we are dealing with an Oracle-specific setup, we use the ":" character to denote the parameters.
Assembly:
This control is used to define the assembly file from which all the relevant Database-specific types will be extracted.
Parameter:
This control is used to define the type which will be used to create the parameters that will be used in SQL commands.
Builder:
This control is used to define the type which will be used to derive the parameters from an SQL Stored Procedure.
Adapter:
This control is used to define the type which will be in charge of executing the SQL commands or SQL Stored Procedures.
Connection:
This control will be used to define the type which will represent the connection to the Database.
Command:
This control is used to define the type which will represent our SQL command or an SQL Stored Procedure which we will execute. We can also notice that the Command control has two further controls defined. These are the controls that represent our Data XML elements. Since all three controls in the last row can be further modified, all of them have a "+" and a "-" sign at their top. These controls will be used to either add or remove the pairs of Name and Value controls.
Also worth mentioning is the fact that the easiest way to load a certain Database-specific setup XML file would be to simply Drag'n'Drop it on to the application window.
4.2 SQL Queries
This Tab Item control represents the Commands control which is used to handle our SQL commands and SQL Stored Procedures. The control is accessed by clicking the Tools Menu Item and then selecting Commands.
Image 3. – Commands Tab Item of the Configuration Application.
Here we can observe the Commands and the Query controls. The list on the left side represents all of our XML files which contain either the text based SQL Commands or names of an SQL Stored Procedure.
In this particular case we are presented by an instance of a text based SQL Command from an XML file named Cars. The text can easily be loaded, edited and saved back to the file.
It should be important to note that the easiest way to load all the relevant XML files which contain the commands in question is to simply select either a multiple number of files or folders and Drag'n'Drop them onto the list itself.
The selected folders may contain non-XML files, or XML files which do not conform to the Commands XML file defined earlier in the definition (1.3). Yet, the application will simply search for all the correct files and sift them out from the other files in order to display them on the Commands control.
One of the advantages of using the application over handling the XML files by hand is the fact that the text based SQL Commands will need to be saved and formatted to conform to the XML standard.
This means that certain characters like "<" or ">" will be transcribed as "<" or ">" which would clearly make it difficult to write such SQL queries. The application in question handles such transformations behind the scenes automatically and we are treated to an appropriate character, as can be seen from the image presented above.
4.3 Exception Handling
The last Tab Item is the one used for handling Exceptions. We can easily reach this control by clicking the Tools Menu Item and then selecting Exceptions.
Image 4. – Exception Handling Tab Item of the Configuration Application.
We can see that there are only two Text Box controls present here and that they are used to represent the name of the assembly from which the type will be loaded, which will be used to handle the exception and the name of the type itself.
The Edit item, just like it is the case with the Database-specific Edit item, allows the currently used XML file to be used as the main configuration file.
4.4 Shortcuts
The following table represents all the Shortcuts which can be used in our helper application in order to make the work as easy as possible.
We can see the Keys that need to be pressed and the description of their action in the Shortcuts column, while the Controls column on the right hand side represents the three controls described earlier.
Table 1. – List of Available Shortcuts for the Configuration Application.
Database Query Examples
5.1 Test Setup
We shall now take up to performing some query examples on certain Database systems in order to showcase the workings of our newly created Generic Database Access. Let us therefore take a brief overview of our testing setup, which consists of the following points.
- Main Testing Application
- Database Source Data
- Database-specific and SQL Command XML files
- Database-specific assemblies
- Exception Handling
Point 1:
Our Main Testing Application is a simple WPF application designed to execute SQL commands on various Database systems and present them to the user and is designed accordingly as shown by the following image.
Image 5. – Testing the Generic Database Access assembly.
In theory the Generic Database Access is able to interact with any Database system for which an ADO.NET assembly exists. Be that as it may, we shall only take to testing the assembly on several most popular Database Systems, which are currently in use[4].
Thus, we can see from the image above that the tests will be performed on the Microsoft SQL Server, Oracle, Microsoft Access, MySQL and PostgreSQL Database systems.
Point 2:
Our testing setup data is the Database source data. This is the data that will be queried by our assembly.
This data comes in the form of four SQL scripts which need to be executed on every single Database system mentioned above in order to create the appropriate tables and data. In case of the Access database, a sample Database file will be provided.
Point 3:
Our setup is the collection of Database-specific XML setup presets. The solution will contain five different presets for all the Databases mentioned above. The presets are located in the GenericTest\bin\Debug\GenericDataBaseAccess folder.
Likewise, the SQL commands will be supplied in the solution as well and will be located in the Solutions\Examples\Scripts folder.
Furthermore, our Configuration Application, named DataBaseConfig.exe will be located together in the same folder with our presets.
Point 4:
Included within our Example solution, will be the clear prerequisite for our Generic Model to work properly, the collection of all the relevant Database-specific ADO.NET assemblies. These are as follows.
- Microsoft SQL Server and Access – System.Data.dll
- Oracle – Oracle.DataAccess.dll
- MySQL – MySql.Data.dll
- PostgreSQL – Npgsql.dll and Mono.Security.dll
These assemblies will be located in the GenericTest\bin\Debug folder.
Point 5:
Last setup point is concerned with Exception handling. A sample assembly named ExceptionHandling.dll will be provided in the above mentioned folder as well.
Furthermore, the appropriate XML file which will be needed by our test assembly, will be located in the GenericTest\bin\Debug\GenericDataBaseAccess\Exceptions folder, and will be named ActivateException.xml
Now that we have fully defined all the relevant points in our testing setup, let us start to query the first Database system.
5.2 Querying the SQL Server Database
The first example is going to use the Microsoft SQL Server 2008 R2 database. And will show how we can use a single string parameter with a Stored Procedure. The code which will be used to execute all the relevant tests including the first one is located below.
public partial class TestWindow : Window
{
Database Database = new Database();
public TestWindow()
{
InitializeComponent();
}
void Students_Click(object sender, RoutedEventArgs e)
{
Grid.ItemsSource = Database.Query<Students>(new Students() { Major = "Architecture" });
}
void Countries_Click(object sender, RoutedEventArgs e)
{
Grid.ItemsSource = Database.Query<Countries>(new Countries() { p_values = OracleDbType.RefCursor });
}
void Cars_Click(object sender, RoutedEventArgs e)
{
Grid.ItemsSource = Database.Select<Cars>(new Cars() { Date = new DateTime(1990, 1, 1) });
}
void Songs_Click(object sender, RoutedEventArgs e)
{
Grid.ItemsSource = Database.Select<Songs>(new Songs());
}
void Movies_Click(object sender, RoutedEventArgs e)
{
var count = new MovieCount();
Database.Query<MovieCount>(count);
Database.Insert<Blockbuster>(new Blockbuster(count.Count, "Police Academy"));
Grid.ItemsSource = Database.Select<Movies>(new Movies());
}
}
The Database class is not particularly important to us and it will suffice for us to say that it is simply a wrapper around some of our classes that will be used to perform the examples, and to ensure that null data does not generate exceptions.
The first method we can see, is the one that will be used to get all the Students from the SQL Server Database, which do not have as their Major, the subject titled "Architecture".
As can be easily seen, we have executed a SQL Stored Procedure to query the Database and return the relevant data. The specific Students Business object is also used as a generic type, and its instance is used as the parameter.
Furthermore, its property Major will be filled with the "Architecture" string and will be used as the value in the parameter that will be created by our Generic Database Access assembly.
5.3 Querying the Oracle Database
For our second example, we are going to use the Oracle 11g Database system. We can see from the previous definition that we are using Oracle's Ref Cursor to fetch the relevant data, therefore, this example will show how we can use Stored Procedures, Ref Cursors, and output parameters, to execute queries on the Oracle Database.
Our Business object Countries will need to be defined in the following way.
class Countries
{
public OracleDbType p_values { get; set; }
}
The Stored procedure will accept an OUT parameter named p_values. Once the p_values property is filled with the OracleDbType.RefCursor, it will serve as the relevant parameter for our Stored Procedure.
5.4 Querying the Access Database
The next example is going to be executed using the Microsoft Access 2010 Database. This very simple test is going to use the DateTime type to indicate which cars should be selected from the Database. Only cars produced after the specified date will be shown.
Since the Access Database, does not support Stored procedures, this particular test will show how we can execute simple text based SQL commands.
5.5 Querying the MySQL Database
In selecting the next Database system to query, we have decided upon the version of MySQL 5.5.28. This particular test, will display all the relevant songs defined in the command XML. The test will show that even though the Business object has no properties, multiple arguments can be specified in text based SQL commands.
Thus, this SQL command will return all songs which are performed either by "Tina Turner" or "Manu Chao." These values are explicitly defined in the Songs.xml file in the SQL command itself.
5.6 Querying the PostgreSQL Database
For our last example, we have decided to showcase the querying of the PostgreSQL 9.2 Database system. This time, as can be seen from the final method, we are going to test multiple commands, among which is an Insert command, which is a Non Query type of command.
Furthermore, since the PostgreSQL database can not properly derive the parameters from its Stored Procedures we will leave the Builder element empty from the Database-specific preset.
The idea behind this method is to add a new movie to the Database every time its clicked, and then display all the Blockbuster movies, that is, those with the budget of 50.000.000 and over.
In order to perform this operation, the first line of code, creates an instance of our Business object. The next line will execute a Stored Procedure and retrieve the number of movies already present in the Database. The number should be stored in our Business object, which is defined in the following way.
class MovieCount
{
[Output]
public long Count { get; set; }
}
Clearly the Count property has been decorated by the OutputAttribute attribute in order to retrieve the desired count.
The next command is a simple text based SQL Insert command. This command will add the new movie to the Database. The Blockbuster Business object used in this command will be filled with the returned count value above and is defined in the following way.
class Blockbuster
{
public long ID { get; set; }
public string Title { get; set; }
public double Budget { get; set; }
public Blockbuster(long count, string title)
{
ID = count;
Title = title + " " + count;
Budget = new Random().Next(50000000, 90000000);
}
}
This definition is used to define a new blockbuster movie. The previously retrieved count will be used for the ID parameter. The title of the movie will be "Police Academy" together with the count number as well. Also, to indicate that it is a Blockbuster movie, a random budget will be generated ranging between 50.000. 000 and 90.000.000 in monetary value.
In the end, once this movie is inserted into the Database, all the movies will be selected and displayed and with this, we have concluded our exposition of the example queries that can be performed by our Generic Database Access assembly.
References
- David Talbot, Mahesh Chand: Applied ADO.NET Building Data-Driven Solutions; Apress (March 18, 2003)
- https://entlib.codeplex.com/
- Mario Stopfer: The Data Exchange Mechanism (August 29, 2012).
- http://db-engines.com/en/ranking
