• Download KyneticORM.WCF.Code.zip - 96.55 KB

Introduction

KyneticORM library is a configuration-less, self-adaptive and dynamic ORM library that, with a creative use of C# 4.0 dynamics, generics and reflection, provides an easy and powerful mechanism to deal with the contents of your database, straight from you C# code, using a natural SQL-like syntax, not having to write in advance or maintaining any mapping or configuration files, and without the need to write any interface classes or, in any way, having to alter your business objects.

Previous articles have focused in the usage for "direct" operations: those where you can have a direct connection against your database (for instance by using a given connection string). This article shows how the KyneticORM library has been extended to use WCF to support remote client-server scenarios, which can be summarized as in the next drawing:

In essence, we want to handle is an scenario where you client application should not have a direct connection with your database. Some examples are: because security reasons, because the communication channel is not reliable enough, because it does not support your preferred protocolos, or just because logging purposes.

But regardless what your actual needs might be, you still want to maintain the configuration-less nature KyneticORM offers you, its simplicity, and its support for a natural SQL-like syntax. And, talking about these scenarios, conceptually you want to export some of your database capabilities as a service to the outside world.

This is where KyneticORM.WCF comes in. It is built to export the ability of using your database capabilities through a proxy that sits in whatever server you would like, typically in a different one than your database server, using WCF as its communication mechanism. The client application builds its queries and commands using KyneticORM.WCF with a syntax that is the same as the one used in its KyneticORM.Direct counterpart.

Note: this a 1.0.1 maintenance version that includes the correction of a bug that have might appeared in some border case scenarios that has been solved.

The basics

We are going to use the same scenario as we used before: we are going to assume that we are dealing with a (minimalist) HR system composed by three tables, Employees, Countries and Regions, as appears in the following SQL model:

Each employee is assigned to a given country through its CountryId column; each country is assigned to a given region through its RegionId column; and to make things a bit more interesting, each employee can be assigned to a manager if the ManagerId field is not null, and each region can be assigned to a parent super-region if its ParentId field is not null.

Note that each table has an "Id" column, something that will force us to use aliases when dealing with queries that involve several tables (... and something that has created some interesting challenges to code an appropriate generic way to return records that fetches some of these "Id" columns from several tables at once).

If you have read the first article on KyneticORM you'll see that we are going to follow the same approach: using examples to go from the basic usage towards a more complex one. Indeed, I have used mostly the same examples to show you how the WCF extensions can be used basically in the same way and with the same simplicity we have used the "direct" counterpart.

But when dealing with WCF there will some differences: mostly about how we set up the WCF specifics. So let’s start with the server part.

Starting the KyneticORM.WCF server

The server is nothing more than an instance of a derived class of the abstract KServerWCF one. Your derived class should override the abstract method GetDirectLink(), as in the following example:

public class MyServer : KServerWCF
{
  public MyServer() : base() { }
  public override void GetDirectLink( out KLinkDirect link, out bool disposable )
  {
    var cnstr = SQLHelper.BuildConnectionString( "win2008dev", "KyneticORM" ); // Your connection string
    link = new KLinkSQL( cnstr ) {
      DbCaseSensitiveNames = true, NumericCulture = CultureInfo.InvariantCulture
    };
    disposable = true;
  }
}

It has two "out" parameters. The first one is used to obtain an instance of your preferred KLinkDirect object, the one that will be used to speak with your database. The second one is used to indicate KyneticORM to automatically dispose, or not, this object when the server is destroyed or disposed. Note that an instance of the server class is created per each connection, so using the WCF facilities you can handle your security an authorization needs.

Note: this article does not focus on the "direct" version of KyneticORM, but it is worth to mention that we have set two properties in the constructor of the "proxied" direct object: DbCaseSensitiveNames, which is used to specify if the identifiers in your database are case sensitive or not, and NumericCulture, which is used to specify what CultureInfo to use when formatting numbers to be written into your database (this is to support globalization scenarios).

Now, to start your service, you have to write something like this:

KLinkWCF.InitializeKnowTypes();
ServiceHost host = new ServiceHost( typeof( MyServer ) ); // Use your KServerWCF derived class
host.Open();

The first line registers the types of the classes that are used in the communication flows. The second line just creates an instance of your service host, and third line opens it. From now on, when a client tries to connect, the host creates a new instance of your proxy server class to service its demands. Take a look at the App.config file in the download to see how I have configured it.

Starting the KyneticORM.WCF client

Maintaining the same ease of use we have achieved in the KyneticORM.Direct version, to use this WCF extensions we just have to instantiate a KLinkWCF object that will maintain all the details of the connection with the server on your behalf:

KLinkWCF.InitializeKnowTypes();
string endpoint = "Service_OnTcp_Endpoint"; // Use the endpoint on you App.config file
DeepObject package = null; // Create your connection package

var link = new KLinkWCF( endpoint, package );

As we did before, the first line just register the types used in the communication messages.

The second line is a string that contains the endpoint that appears in your client's App.config file, and again, please, take a look at the download to see how I have configured it. One can wonder why, if KyneticORM uses a configuration-less approach, we have still to use the configuration files for the WCF specifics. The reason for doing it this way is that I want to interfere as less as possible with the WCF technicalities, firstly because KyneticORM is not about simplifying WCF, and secondly, because in this way you can adapt the WCF mechanism the way you want.

The third line creates what I have named a "connection package", in the form of a DeepObject dynamic object. It can be used to send to the server whatever information it may need to serve the connection demand. For instance, you can include here your database's user login and password to the database, or any other piece of information. If you don't need it, you can leave it null.

The forth line is where you instantiate you KLinkWCF object. It connects to your proxy server using the endpoint specified, send your connection package. In the server, the host invokes the virtual ProxyInitialize() method with this connection package to permit your proxy server to react accordingly (for instance, taking note of the above login and password to be used later, in the GetDirectLink() method, to build the connection string).

Then, finally, the client gets back a reference to this proxy server and, from now on, we can use this KLinkWCF object as if it were a direct link with your database, the same way we have used the KLinkDirect objects in the direct version of the library.

Not bad! One of the main objectives of KyneticORM was to provide a very easy usage. All of the above had to do with WCF specifics and nothing to do with your actual database needs. But you can basically copy & paste the above and forget about the details.

Your first query

Let's now move on and send our first query against the remote database. You just have to write the following lines:

var cmd = link.From( x => x.Employees ).Where( x => x.LastName >= "C" );
foreach( dynamic obj in cmd ) Console.WriteLine( "\n- Name: {0} {1}", obj.FirstName, obj.LastName );
cmd.Dispose();

Yes, to access your database, even through the intermediate proxy server, write your query and iterate through its results, you have not had to write any other code, not any external configuration or mapping file, not to do any modification in your business classes (that, by the way, are not even used in the example), and not had to write any interface or intermediate classes that implement in advance your database structure.

What’s more, you wrote your query logic in a more natural way, so that you have been able to "compare" between two string objects (something that the C# compiler is supposed not to allow). And on top of that, of course, you have not had to worry about creating, opening, and closing the connection (a concept that, by the way, in this WCF world, is of no usage for your client application as it is handled by the proxy object you have created in the server).

KyneticORM returns, by default, a set of generic dynamic objects, which can be manipulated as your convenience. They have a "member" per each column returned, whose names are, obviously, the names of the column in the database, and their values are the actual contents of those columns. This dynamic objects are instances of the DeepObject class and, as it is heavily used in KyneticORM, I encourage you to take a look at it (see reference [1]).

Note that we have not specified in any place what columns to return, not in the query itself (which translates to a "SELECT *" clause), and not in any configuration file or interface or intermediate class.

Some explanations

The From() method is used to specify from what table you want to get the contents from (and you can use and chain as many From() methods as you need to specify several tables). It instantiates a command object for this Query operation. A command object is an specialized class that maintains all the information needed to build an execute a given command against your database, but you don't have to bother with its details if you don't want to.

The Where() method is used to write your query logic. As happens with mostly all the methods in the library, its argument is a delegate with the Func<dynamic,object> signature. Typically we use the so-called lambda syntax to specify it inline. This syntax mandates that you specify the argument you are dealing with by using a prefix (the "x" in the above example). If you have never used this syntax before don't worry, it does not take too long to get used to it.

By using this syntax and this delegate's signature it is how we have been able to write our query logic in such a way that resembles the SQL syntax. For instance, in the above example, you have specified a comparison between two string objects, something that your C# compiler is not suppose to allow.

• The first trick lies in the fact that the argument used in the delegate is a dynamic one, so the compiler will use a late-binding mechanism at run-time to resolve its properties and methods: we take benefit of this by having the ability to write whatever expression we would like, and the compiler won't comply.
• The second trick is that there is no need for this expression to be executed in a real sense. Rather, it is parsed and translated into something your specific database can understand. Actually, what it is sent across the wire is an abstract logic tree describing the logic of the query you have built. The proxy server takes this tree and translates it into something that the database engine behind it can understand.

Obviously, it you write something that the database engine cannot understand, you'll get an exception. KyneticORM.WCF sends this exception back to the client that has originated it, but the proxy operations continue without impact. This way, exceptions raised for one client do not impact any other clients that are using concurrently the same proxy server.

Using the fact that all KyneticORM command objects implements the IEnumerable interface, the third line of the example merely iterates through the records returned. As mentioned before, those records are themselves dynamic objects, by default instances of the DeepObject class. As they can host an arbitrary number of multi-level members, KyneticORM loads into them the tables and columns returned by your operation, and being dynamic, you can manipulate them using a very convenient syntax as it is shown in the example.

The beauty of this is that, in this way, KyneticORM does not have to know in advance the structure of your database or what columns are going to be returned in the specific query you are writing: it can host whatever results your commands are producing in an unified and consistent way for all of them. They are just serialized by the proxy server and sent back to the client.

Finally, note that if there are no records to return, the KyneticORM commands will just return NULL, but no exceptions are raised.

Your second query

We are now prepared to write more complex queries. Let's suppose that now you want to find all the employees whose birthdates are equal or bigger than a given year and, for each of them, return all fields of the country they belong to, along with just the Id and the BirthDate values. One possible way is as follows:

var cmd = link
  .From( x => x.Employees.As( x.Emp ) ).Where( x => x.Emp.BirthDate.Year() >= 1970 )
  .From( x => x.Countries, x => x.Ctry ).Where( x => x.Ctry.Id == x.Emp.CountryId )
  .SelectTable( x => x.Ctry )
  .Select( x => x.Emp.Id, x => x.Emp.BirthDate );

Console.WriteLine( cmd.GetTraceString() );
foreach( dynamic obj in cmd ) { ... }
cmd.Dispose();

Let's take a look at the From() methods. Accordingly to the specifications of the query we want to send, we will need to fetch contents from several tables, Employees and Countries. But it happens that both have an Id column, and so we need to use aliases to differentiate between them. The example shows to ways to achieve this: either by using the Alias() method appended to the name of the table, or either by specifying the alias as an optional argument of the From() method. Both produce the same results, so you choose which one you are more comfortable with.

Another thing to note is that we have used several Where() methods: they are combined by default using and AND logical operator. As an alternative, you could have used the OrWhere() method and, as its name implies, the expressions will be chained using an OR logical operator.

As you can see, the Year() extension method has been translated into its database equivalent. This method is among some of the SQL functions that are treated by the parser as special cases to permit you to write more fluent code. You'll see later what other SQL functions are treated this way, and how KyneticORM deals with other functions that are not considered in advance.

Note also that we have used a C# equality comparison operator "==". Even if in this particular case we could have used the "=" instead to be more close to the SQL syntax, and it will work, it is not recommended: as a general rule we need to use the regular C# logical operator instead of its SQL equivalents, if they exist.

Let's now move on to see how we have specified what columns to return. To tell KyneticORM to select all possible columns in a given table you use the SelectTable() method. It takes an argument that specified the name of the table, or its alias. To select specific columns you use instead the Select() method, where each argument specifies a given column. Note how in this example we have also specified what table (or alias) do this columns belong to by using the dot "." operator to qualify their names. If you don't use any Select command, it is equivalent to a "SELECT *" clause.

If you want to see what will be the actual command sent to the database you can use the GetTraceString() method.

Some more notes

KyneticORM does not try to intercept any errors in the SQL semantics of the code you have written. I decided to let those errors to be intercepted by the piece of software best suited to this job: your database engine. So be prepared: it will throw its own exceptions if, for instance, you specify columns or functions that don't exist, if you write logic expressions that have no sense, or if any error is generated by the database when executing your command.

Every KyneticORM object implements the IDisposable interface. In many circumstances you don't have to do it, but it is considered a good practice to dispose your commands and objects as soon as you are done with them. This way, they can free all the internal resources they might have acquired along the way. And this is of particular importance in this WCF scenarios because this way you dispose the connection you are holding against your proxy server.

Note that in this WCF version of KyneticORM there is no provision to obtain the list of parameters used in a given command and their values. Indeed, it had only a limited usage in the direct version, so it is not included.

Also, because security concerns, there is no support for Raw Commands. All the operations you send to the database through the proxy server are based upon the command objects provided by the library. This way, in the server, by overriding its virtual methods, you can intercept any query or piece of information a client might not be entitled to obtain.

Prunning the returned tree

Let's take again the scenario where we are obtaining columns from several tables. As we have seen before, the dynamic record returned has a first-level member per each table that have columns selected, whose name is the name of the table - and not any alias we might have used. This is important to emphasize because if you use the alias as the first-level member, being the record a dynamic object, what you are doing is creating an empty member, not returning any table's host member.

Once you have located the appropriate first-level table-member, each of them have their own members, which are the columns returned for each table, and whose values are the actual contents obtained for each of them. So, for instance, if you want to print the employee's Id along with the name of the country he belongs to, you'll use something like:

foreach( dynamic obj in cmd )
  Console.WriteLine( "Id:{0} Country:{1}", obj.Employees.Id, obj.Countries.Name );

So far so good. But what happens when you are obtaining columns from just one table? Using this mechanism would impose a heavy syntax for these cases. No worries, KyneticORM detects these scenarios and when they happen, instead of returning the original record (the one with just one first-level member for your table), it returns the actual member that has the columns returned.

This is why if, as an example, you write a query that is going to return columns from the Employees table only, you can write "obj.FirstName" instead of "obj.Employees.FirstName". If you don't like this behavior, and you want to use the latter form, use the KeepTreeSimple() method of your command with false as its argument, and then this behavior is disabled for this command.

The Select-As syntax

KyneticORM also accepts the Select-As syntax to provide an alternate name to any column or expression you are returning. This is achieved by appending the As() method to the column specification, and providing the new name as its argument. This is very handy to access nameless columnd produced by you database engine, for instance when invoking specific functions.

As an example, let's suppose we want to get the number of employees registered in our database. One way to achieve that is by using the COUNT SQL function:

cmd = link
  .From( x => x.Employees )
  .Select( x => x.Count( x.Id ).As( x.SumOfEmployees ) );

var obj = cmd.GetFirst();
Console.WriteLine( "Value: {0}", obj.SumOfEmployees );

As the COUNT function just returns a value, and not a column, by using the As() method we have transformed the result into a record we can manipulate. In this case, it will have a member (column if you wish) with the name we have provided, that contains the result of the COUNT function.

Sub-queries

Nested queries are also supported by KyneticORM. Let's assume now that you want to get a list of your employees that are based in a given country, identified by its Id column. Among other (possibly better) ways to obtain it, take a look at the following example:

var cmd = link.From(
      link.From( x => x.Countries.As( x.Ctry ) ).Where( x => x.Ctry.Id == "us" ), 
      x => x.Location )
  .From( x => x.Employees.As( x.Emp ) ).Where( x => x.Emp.CountryId == x.Location.Id )
  .SelectTable( x => x.Emp );

Console.WriteLine( ">> {0}\n", cmd.GetTraceString() );
foreach( dynamic obj in cmd ) { ... }

To specify the sub-query you just have passed a command object as the first argument in this overloaded version of the From() method. A temporary query command is then created on your behalf and used to produce the correct command string for the main command object as needed. Note that in this case it is mandatory to use an alias for this temporary command, alias that you specify in the second argument of the From() method.

Where-In and Where-Equals syntaxes

The "IN" and Equals "=" syntaxes are also allowed. As an example, let's assume now that you want to find all the employees that do not belong to the "Europe, Middle East, and Africa" super region. One possible way to achieve it is as follows:

var cmd = link
  .From( x => x.Employees ).Where( x => !x.CountryId.In(
    link.From( y => y.Countries ).Select( y => y.Id ).Where( y => y.RegionId.In(
      link.From( z => z.Regions ).Select( z => z.Id ).Where( z => z.ParentId =
        link.From( p => p.Regions ).Select( p => p.Id )
          .Where( p => p.Name == "Europe, Middle East & Africa" )
) ) ) ) );

Console.WriteLine( ">> {0}\n", cmd.GetTraceString() );
foreach( dynamic obj in cmd ) { ... }

The "x => x.Member.In( Expression )" construction is the entry for the IN syntax, translating it into the "Member IN ( Expression )" SQL statement. The same logic applies when using the assignment operator "=", so translating "x => x.Member = expression" into "Member = ( Expression )" (or into "NOT Member = ( Expression )" if, as in the example, we have used the "!" negation operator).

I assume you have noticed I have used several lambda "prefixes" in the above example. This is because the lambda syntax requires it in order to differentiate among the different lambda scopes. KyneticORM has nothing to do with it: it is just the way the lambda syntax works.

Chaining queries

The SQL syntax permits you to obtain the same results using different ways to code your query. As an example, you can obtain the same results as the previous example by using a "chaining queries" approach, as follows:

var cmd = link
  .From( x => x.Regions.As( x.Super ) ).Where( x => x.Super.Name == "Europe, Middle East & Africa" )
  .From( x => x.Regions.As( x.Reg ) ).Where( x => x.Reg.ParentId == x.Super.Id )
  .From( x => x.Countries.As( x.Ctry ) ).Where( x => x.Ctry.RegionId == x.Reg.Id )
  .From( x => x.Employees.As( x.Emp ) ).Where( x => x.Emp.CountryId == x.Ctry.Id )
  .SelectTable( x => x.Emp )
  .SelectTable( x => x.Reg )
  .OrderBy( x => x.Reg.Id ).OrderBy( x => x.Emp.Id, ascending: false );

(Yes, in this case I have selected all the employees that do belong to the given super-region, just for the sake of showing different examples). The thing to mention here is that it might be translated into more or less efficient SQL code, but definitely the C# code is much easier to read. Again, you choose!

The OrderBy method

Also note that I have introduced a new method: the OrderBy() one. Its first argument specifies the column you want to use for ordering the results and, as you see, you can use the aliased approach if applies. The second optional argument permits you to specify if the order is ascending (the default) or descending (by setting this argument to false).

Using Joins

Yes, of course, KyneticORM does support joins as well. For the above examples you may feel more comfortable writing the SQL statement using joins, and so here comes the Join() method to you:

var cmd = link
  .From( x => x.Employees.As( x.Emp ) )
  .Join( x => x.Countries.As( x.Ctry ), x => x.Ctry.Id == x.Emp.CountryId )
  .Join( x => x.Regions.As( x.Reg ), x => x.Reg.Id == x.Ctry.RegionId )
  .Join( x => x.Regions.As, x => x.Super, x => x.Super.Id == x.Reg.ParentId )
  .Where( x => x.Super.Name == "Europe, Middle East & Africa" )
  .SelectTable( x => x.Emp )
  .SelectTable( x => x.Reg )
  .OrderBy( x => x.Reg.Id ).OrderBy( x => x.Emp.Id );

The first argument is always the table to join to and, as you can see in the example, you can specify an alias by appending the As() method, or by using an optional alias argument (see the third Join). The next argument is the ON specification. The final optional argument is a string whose default value is "JOIN" and that, as you can imagine, it is used to specify the specific join type you want.

Note that because KyneticORM.WCF is agnostic with respect on what would be the actual database engine behind your proxy, it does not provide any specialized version to use for each specific database vendor. It means that, if you want to issue a LEFT JOIN command, for instance, you need to add as the latest argument the "LEFT JOIN" string.

GroupBy and Having

Yes, the WCF version supports as well the GroupBy and Having clauses.

The GroupBy() method just permits you to specify the column or columns you want your results to be grouped by. Once you have used it, you can use the WithCube() and WithRollup() ones, that takes no arguments, but that permit you to further qualify the GROUP BY command.

Similarly, you can use the Having() and the OrHaving() methods in the similar way you have used the Where() method.

Using generic SQL functions

The direct version does not treat all possible SQL functions, not just for one database engine but for all possible versions. So the approach it takes is to treat specifically only the most common functions, or those with a more convoluted syntax, and to provide a generic way to parse whatever other methods you write into a valid SQL syntax (without assuming they would be semantically valid). It does also provide a way to develop specialized versions to support specific database vendors and versions. Please refer to the original KyneticORM.Direct article for more details.

As mentioned before, the WCF version assume it won't have any information about the specific version of the database engine the proxy server is connected to. So KyneticORM.WCF let you to write any function you may like, and it is send to the proxy server for processing. If it happens that it is among the special cases it considers, it is treated as one of those cases. If not, it is trated using the default mechanism explained in the original article.

The GetXXX methods

On top of the enumeration facilities provided by KyneticORM, it does also include the following extension methods to return the results from your statements:

• The GetFirst() method just return the first record produced by the SQL statement.
• The GetLast() method returns the last one - but note that in order to locate it all the records produced are actually sent through the wire.
• The GetList() method produces a list of objects with all the records returned.
• The GetArray() method returns an array with all the records returned.
• The GetSkipTake( M, N ) returns a list by skipping the first M records and containing at most the N records afterwards.

Finally you have already seen the GetResult() method, that merely returns an integer with (typically) the result of the operation or with the number of records affected.

Converting your results

So far we have seen that KyneticORM returns its results in the form of dynamic DeepObject records by default. This is very convenient because, this way, we have been able to accommodate any possible structure of the results returned, in a generic and homogeneous way, and without having to write in advance any mapping or configuration file.

What’s more, this is a very resilient mechanism because, as far as the columns (members) you are using do not change their names in the database (and their types maintain their compatibility), KyneticORM is not harmed by any changes in the database. Another way to look at it is that you even don’t have to know in advance the specific types used in the database, assuming they can be converted to the ones used in your application.

But there are some other scenarios where you would like to have a strongly typed approach, returning actual instances of your business objects. KyneticORM provides two ways to achieve this objective: the first one is by using maps, that has been explained in the second article of this series, and that applies to the WCF extensions as well.

The second one is by using the ConvertBy() method that every enumerable command provides. This method permits you to specify the delegate to call just after the record is read from the database, using this dynamic DeepObject record as its argument. The delegate’s responsibility is to convert this record in any way you want, or to perform any other operation you would like, and then return an arbitrary object, typically the result of your conversion, which in turn will be the object returned by the read operation. Confused? Let’s see an example:

var cmd = link. ( ... whatever methods apply ... )

  .ConvertBy( ( x ) => { 
     Employee emp = new Employee(); 
     TypeHelper.SetMemberValue<employee />( emp, "Id", x.Id ); 
     emp.FirstName = x.FirstName; 
     emp.LastName = x.LastName; 
     emp.Active = x.Active; 
     TypeHelper.SetMemberValue<employee />( emp, "ManagerId", x.ManagerId ); 
     TypeHelper.SetMemberValue<employee />( emp, "CountryId", x.CountryId ); 
     return emp; 
  } );

In this example we have used an inline delegate, but nothing impedes you to write a function with the signature Func<dynamic,object> and use it as the actual argument of the ConvertBy() method: you decide what approach better fits in your needs.

In any case, its dynamic argument is the DeepObject record that is read from the database, which can be used for any purpose you want. In the example we have used it to feed with its contents a new Employee object that it is the one returned from the delegate. And remember, whatever object the delegate returns will become the result of this specific iteration.

As a side note, for my test bed I’m using an Employee class whose "Id", "ManagerId" and "CountryId" properties have public getters... but private setters. So to set their values I have to use either the functions provided by the class, or reflection. In the above code I have used the latter approach, facilitated by the TypeHelper.SetMemberValue() method. The TypeHelper class is not logically part of KyneticORM, but it is heavily used internally. Along with other helpers and utilities, it is also included in the download.

No IDbConnections in the WCF version

Obviously. IDbConnection objects are used by the direct version to connect on the WCF behalf to the database engine, but they are not visible to the client.

Transactions

Despite the transactional support you might be able to configure and use with for WCF, I found useful to provide a KyneticORM managed mechanism to utilize transactions regardless what WCF configuration is being used. Behind the scenes, in the server it creates an IDbTransaction object to be used by the command your proxied KLinkDirect object creates. But regardless the details the relevant methods to use in your client are:

• TransactionCreate(), that creates the transaction to be used by any command sent to the proxy while it is active. It takes an argument that permits you to specify the isolation level. As mentiones, behind the scenes it uses an IDbTransaction based mechanism, so the isolation levels you can use are the ones valid for this capability.
• TransactionRollback() and TransactionCommit() are used to rollback and to commit the active transaction, disposing along the way. It means that, if you want to continue to use transaction for more future commands, you need to recreate the active transaction by using the TransactionCreate() method.
• TransactionActive returns a bool telling you if there is an active transaction or not.

It is worth to mention that if the connection with an active transaction is disposed by any mean (for instance, because the connection is dropped or because you forget to commit or rollback it), an automatic rollback is issued.

The Update, Insert and Delete commands

Obviously, KyneticORM.WCF does support the Update, Delete and Insert operations.

Update

The Update command is instantiated using the Update() extension method of your KLinkWCF object, specifying the table you want to modify as its first argument – and remember that, if needed, you can use the As( alias ) ) method appended to this name. Let’s see an example:

var cmd = link.Update( x => x.Employees ) // or x => x.Employees.As( x.Alias )
  .Where( x => x.FirstName >= "F" )
  .ColumnValues(
    x => x.ManagerId = null,
    x => x.LastName = x.LastName + "_modif"
);

You can use one or many Where() and OrWhere() methods to specify what records to modify, as discussed for the Query command. You can also use the ConvertBy() method, iterate through its results, or use any of the GetXXX() methods as we have discussed above.

The interesting part comes with the ColumnValue() and ColumnValues() methods. They take a delegate (or a list of delegates separated by commas as in the example), each of them specifying a column and a value by using an assignment-like syntax. So, in the example, for each record found using the "where" condition, we are updating its ManagerId field, setting it to null, and its LastName field, setting it as the concatenation of its previous contents with the string given.

So the rule here is that the left part of the equal symbol specifies the name of the column to update, and the right part the value to set on it. Because this right part is basically translated into its equivalent SQL code, we can write very complex expressions here.

Insert

The insert commands are very similar to the update ones, except that they have not a Where method, and that they are instantiated by the Insert() extension method on the KLinkWCF derived classes.

Delete

Similarly, the delete command is very similar to the update one, except for the fact that it has not the ColumnValue or ColumnValues methods.

Notice that if you don’t use any condition to specify the records to delete, by using the Where() and/or OrWhere() methods, you will delete ALL the records in that table.

Maps

KyneticORM.WCF does provide support for the Maps mechanism used also in its direct counterpart. To not repeat it again, please refer to the second article of this series: KyneticORM (part 2). It will give you the capability to work against individual "records", which are instances of your business classes, but maintaining the feature of not altering them in any way.

References

• The original article where the direct version KyneticORM.Direct is discused can be found at: KyneticORM
• This article has focussed on the non-mapped approach. As mentioned before, KyneticORM does support a mapping mechanism to retrieve strongly typed instanced of your business classes instead of dynamic objects, without the need of specifying a ConvertBy() delegate in each command. This mechanism is known as maps, and can be found at: KyneticORM (part 2).
• DeepObject: "A multi-level C# 4.0 dynamic object"; describes how to create a dynamic multi-level object, using the dynamic features of C# 4.0. It can be found at: DeepObject.
• DelegateParser: Describes how to use C# dynamics to convert a dynamic expression into an expression tree, the functionality that is the core of the KyneticORM parsing and translating capabilities. It can be found at: DelegateParser.

History

• V1: January 2011. This is the first version of the extensions of KyneticORM to support WCF scenarios.