• Download KyneticORM.Direct.Code.zip - 70.69 KB

Introduction

There are lots of scenarios where the time or efforts you need to devote to write and maintain the plethora of things most ORM solutions require don't worth too much. Just consider:

• All those external configuration and mapping files that most ORM solutions require you to write and maintain to connect your business classes with your database repository, probably using different languages and "coding logic" than the one you are using (in this case C#);
• That you will have to modify your business classes with database related stuff (assuming you have access to their code, which is not always the case), and so breaking the principle of separation of concerns;
• That, in any case, when you are writing your queries and database commands you will need to follow the syntax restrictions of the C# compiler, instead of using a more "natural" SQL syntax;
• And, finally, the hidden assumption that you have a stable and controlled environment, because as soon as any piece change, regardless it is controlled by you or not, you will need to recreate them all, and pray that nothing has broken your code.

This article introduces KyneticORM, a configuration-less, self-adaptive and dynamic ORM library that, with a creative use of C# 4.0 dynamics, generics and reflection, provides you with 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 and not 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.

What's new

This is the 4th version of the KyneticORM library. It is built to:

• To maintain the ease of use of previous versions.
• To provide support for serialization and WCF scenarios.
• To provide improved support for transactions. Previous versions did work well only when using the TransactionScope mechanism. This version provides improved support for IDbTransaction based scenarios.
• To solve some bugs, and to incorporate some improvements.
• And to accommodate some modifications in the names of the interfaces and classes: all of them now start with the prefix "K" (instead of the longer "Kynetic" used previously), and the namespace MB.KyneticORM is only used now for very basic objects and helpers. The namespace MB.KyneticORM.Direct is the one that has the objects to use in your "direct" connection scenarios against your database, and the MB.KyneticORM.WCF namespace has the objects used in the WCF scenarios.

KyneticORM basics

This article is basically a tutorial on how to use the direct non-mapped version of KyneticORM and its capabilities. The support of Maps (the facility of mapping your business objects against tables of your database) and the WCF scenarios are covered in the rest of the articles of this series. You can find them in the references at the end of this document.

The scenario

Let's first set the scenario for the following examples. Assume for a moment that you have 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).

The download includes the SQL script file I have used to build and to prepopulate the database and its tables.

Your first query

Using KyneticORM is extremely easy: it is designed to permit you to forget all those details and nuances you otherwise have to have in consideration when using an ORM. As the first example, let's suppose you want to get a list of those employees whose last name starts with "C" or bigger. Believe it or not, this is all the code you need:

var link = new KLinkSQL( "your connection string" );
var cmd = link.From( x => x.Employees ).Where( x => x.LastName >= "C" );

foreach( dynamic obj in cmd ) { // Do whatever you want with the records returned
  Console.WriteLine( "Id:{0}, Name:{1}", obj.Id, obj.FirstName );
}

Yes, to access your database, 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 to worry about creating, opening, and closing the connection.

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.

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

Ok, enough selling. Let's see line by line what we have done.

In the first line we have created an IKLink object. If you wish, it can be understood as the analogous of a regular connection object. Indeed, its role is to maintain the information needed to connect to the database, and to act as a factory to created specialized command objects. As you have noted, in its constructor we have used our old friend the connection string. Yes, I wanted not to reinvent the wheel, and to maintain its compatibility, so behind the scenes KyneticORM uses ADO.net for many purposes.

By the way, IKLink is just an interface. The object that you have created is an instance of a derived class of the KLinkDirect class. In this case, it is an instance of the specif class adapted for connections against MS SQL Server databases: KLinkSQL, which is the default version provided by KyneticORM. The library is built in such a way that it should not be a daunting task to created other derived versions for you favourite database vendor.

The second line uses the From() extension method to create a command object, in this case the one that reflects a Query operation (there are similar extension methods for the Update, Insert and Delete operations, as explained below). A command object is an instance of class designed to hold all the information it will need to build the actual statement to be sent to the database, along with some handy methods to build, and the infrastructure methods needed to execute it and return its results.

KyneticORM permits you to write your commands using a fluent syntax: see as an example how we have chaines the Where() method above. This is a pervasive approach across all the objects in the library: all of them permits you to use this natural syntax when writing your commands.

The Where() method is where you have written your query logic. As happens also with mostly all the methods in the library, its argument is a delegate with the signature Func<dynamic,object>, and it has been specified used the lambda syntax. This syntax requires that you use a prefix (as in the example the "x" one) to specify the argument object you are dealing with. 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 is how you have been able to write your query logic in such a way that resembles your SQL syntax. For instance, in the above example, you have specify a comparison between two string objects, something that your C# compiler is not suppose to allow.

• The first trick here 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 this expression is never executed in a real sense. Rather, it is parsed and translated into something your specific database can understand. Let me emphasize this concept: to a great extent KyneticORM can be understood as a translator of dynamic expressions into SQL code.

Using the fact that all KyneticORM command objects implements the IEnumerable interface, the third line of the example merely iterates throug the records returned. As mentioned before, those records are themselves dynamic objects and, in this case, by default, instances of the DeepObject class (see reference [1]). Instances of this class are specialized dynamic objects that 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 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: it can host whatever results your commands are producing in an unified and consistent way for all of them.

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 field of the country they belong to, along with just the Id and the BirthDate values. One possible way is as follows:

var link = new KLinkSQL( "your connection string", createConnection: false ) {
  DbCaseSensitiveNames = false, NumericCulture = CultureInfo.InvariantCulture
}
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();

I have used this example to introduce a second way to create your IKLink object. We have used a connection string as before, but we have also used a second optional argument that tells KyneticORM to create, or not, a IDbConnection on our behalf. If so, this object will be used in all the commands against your database instead of letting KyneticORM create, open, use and dispose a specific one per each command. This is a nice feature when, for instance, you are dealing with transactions.

Also, we have set two important properties in the constructor: DbCaseSensitiveNames, which is used to specify whether the identifiers in your database are case sensitive or not, and NumericCulture, which is used to specify what CultureInfo object to use when formatting numeric values before writting them into the command to be sent against the database. This is important to support globalization scenarios.

Now 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 alias 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.

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 need to 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 specified a given column. Note how in this example we have also specified what table (or alias) do they 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 that, in the above example, will produce the following SQL code:

SELECT Ctry.*, Emp.Id, Emp.BirthDate
FROM Employees AS Emp, Countries AS Ctry
WHERE ( DATEPART( YEAR, Emp.BirthDate ) >= 1970 ) AND ( Ctry.Id = Emp.CountryId )

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.

Some more notes

KyneticORM does not try to intercept errors in the SQL semantics of the code you have written. I decided to let the 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.

All SQL commands KyneticORM produce are parametrized. By default, they are identified by a "@p" prefix followed by an unique integer (don't assume that this number reflects any particular order). If you wish, you can change this prefix in the Prefix string property of the Parameters property of your specific command. The GetTraceString() method appends the list of parameters and their values to the actual command to be sent against the database, that you can obtain by using the GetCommandString() method if you wish.

More queries and methods

Raw Commands

KyneticORM provides you a way to write and send raw commands against the database without loosing the dynamic nature of the records returned. Raw commands are those that you write in pure text, without the help of a specialized command object to build the statements. The KCommandRawDirect class has a Text property where you can place the command to execute. For example:

var cmd = new KCommandRawDirect( link );

cmd.Text = "INSERT INTO Employees (Id,FirstName,LastName,CountryId) OUTPUT INSERTED.* VALUES ('007','James','Bond','uk')";
foreach( dynamic obj in cmd ) Console.WriteLine( "- {0}", obj );

cmd.Text = "DELETE FROM Employees WHERE Id = '007'";
int n = cmd.GetResult(); Console.WriteLine( "Deleted: {0}", n );
cmd.Dispose();

Note that if you want to return a set of results you need not to forget to add to your command the "OUTPUT INSERTED.*" or the "OUTPUT DELETED.*" clauses as appropriate. Then you can iterate using the foreach construction or any of the GetXXX() methods explained below. If it is just an scalar command, or if you just want to retrieve the number of record affected, use the GetResult() method instead.

Prunning the returned tree

Let's take again a 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 host member.

Once you have located the appropriate first-level table-member, each of them have their own members, that 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, for instance, you only return columns from the Employees table 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 an argument. This is very handy to access nameless columnd produced by you database engine, for instance when invoking specific functions.

For instance, 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, 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 pass a command object as the first argument in this overloaded version of the From() method. The query command is then created and used to produce the correct command string 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 optional 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). 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 here 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. The KCommandQuerySQL class also accepts the LeftJoin(), RightJoin(), and FullJoin() command for your convenience.

Using generic SQL functions

As you can imagine it is impossible to treat specifically all possible SQL functions, not just for one database engine but for all possible versions. So the approach KyneticORM 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).

This behavior is architected inside the KParserDirect class where, by default, only three functions are treated: the root-level NOT(), and the tree-level IN() and AS() ones. Root-level functions are those appended directly to the dynamic argument (as in x.Not(...)), whereas tree-level ones are those appended to a given column (member) or a previous function one (as in x.BirthDate.Year()).

When you write a SQL function that it is not among the special cases (and notice that their names are NOT considered to be case sensitive), KyneticORM takes the approach of translating straight the name of you new function, and parsing in order whatever arguments it may have. So, for instance, "x => x.Alias.Member.Foo( x.Other, "BOO" )" is translated into the equivalent SQL code: "Alias.Member.Foo( Other, 'BOO' )".

Actually, the parser we are using in these examples is an instance of the KParserSQL class, the specialized version for MS SQL Server databases, instantiated through the factory capabilities of the KyneticORM classes. This specialized object adds several new special cases: the root-level Cast() one, the method-level Left(), Right(), Len(), Lower(), Upper(), Substring(), Trim() and similar ones, the Like() and Contains() ones, and the date related Year(), Month(), and similar ones.

Injecting your own string commands

Within your query expression, you can also "invoke" the lambda dynamic variable with an arbitrary number of parameters, as in "x => x( "SELECT * FROM Employees", " ", "WHERE Id = 'C'" )", and KyneticORM will merely translate in order each parameter and concatenate them - but without the commans in between. I have found this useful to "inject" text into the SQL command without getting it being parametrized.

The Top method

The Top( N ) method just inserts a "TOP N" clause in the SQL statement. Of course you'd better not use 0 or negative numbers as its argument, but there is not much more to say about it.

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 it is explained in the next article, and the second one is by using the ConvertBy() method that every enumerable command provides.

The ConvertBy() 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.

Using your own connections

So far you have not had to pay attention to create, open, close and dispose any connection, because KyneticORM, by default, takes care of all of this on your behalf. But there are some scenarios where you would like to have more control, for instance if you want to use transactions, or if you want to share a connection object among some or all of your commands.

Specific connection per each command

The first way to achieve this is by specifying what connection object to use, passing it as the last argument of any of the GetXXX() methods. For instance:

var cmd = ...; // your command
IDbConnection conn = ...; // obtain it the way want
var obj = cmd.GetFirst( conn );

Similarly, to use your connection in a foreach loop, use the UseConnection( connection ) method as the enumerable argument of your loop. For instance:

var cmd = ...; // your command
IDbConnection conn = ...; // obtain it the way want
foreach( object obj in cmd.UseConnection( conn ) ) { ... }

This way of specifying a connection takes precedence over any other way to specify a connection, even over any connection your link object may already have.

A connection for all commands

The second way is by attaching a given connection object to your KLinkDirect object using one of its two constructors. You have seen already the first one, where on top of the connection string you can use an optional boolean argument that tells your KLinkDirect object to create an IDbConnection object on your behalf (that will be stored in its Connection property). When such an object exists, it is used by default in all your commands.

public KLinkDirect( string cnstr, bool createConnection = false );

The second overloaded constructor takes as its first argument a given IDbConnection object, instead of a connection string, and an optional second argument that tells your KLinkDirect object whether to dispose this connection object or not when the KLinkDirect is disposed. This is a convenient way to forget having to dispose your connection and let the IKLink object take care of it.

public KLinkDirect( IDbConnection conn, bool disposable = false );

Transactions

As mentioned before, transactions are one important thing to take into consideration. Previous versions of KyneticORM did work well when using the System.Transactions.TransactionScope mechanism. For instance:

var link = ...; // create your Klink object
IDbConnection conn = ...; // obtain it the way you want
conn.Open();

using( TransactionScope scope = new TransactionScope() ) {
  var cmd = ...; // create your commands and execute them
  scope.Complete();
}

This new version adds support for IDbTransaction based scenarios as well. The good news are that there is no need for any special syntax or method calls. Just create you IDbTransaction, execute the commands you want, and finally commit or abort your transaction. For instance:

KLinkSQL link = ...; // create your Link object
SqlConnection conn = ...; // for instance by: conn = link.Connection if it exists
conn.Open();

SqlTransaction tran = conn.BeginTransaction();
var cmd = ...; // create your commands and operate with them
tran.Commit();

The core of this support lies in the abstract TrySetTransaction() method of the KLinkDirect class and derived classes (so it should be overrided for each specific database version). It is called to set the current transaction on the temporal IDbCommand object that is created by KyneticORM to execute your command against the database. The version included for the KLinkSQL class utilizes reflection on the current connection to find the right transaction object to set on the temporal command object.

The Update, Insert and Delete commands

Obviously, having only Query capabilities is not enough to operate against our database. KyneticORM comes with the Update, Insert and Delete capabilities you are expecting, and the concept is very similar: for each of them there is a correspondent class that maintains the specifics of each command, generates the actual SQL code to execute, and provide the infrastructure methods you need to execute this statement and iterate through its results.

Update

The Update command is instantiated using the Update() extension method on your KLinkDirect 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 KLinkDirect 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.

What's next

• 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).

• This version of KyneticORM provides support for WCF scenarios. In this context you will have an intermediate function that will export, maintain its ease of use all the capabilities of KyneticORM, but without exposing your database to the outside world. This extensions can be found at: .
• 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.WCF.

References

[1] 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.

[2] 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

• V4: January 2011. This version adds support for serialization and WCF scenarios, an improved support for transactions, and correct some minor bugs.
• V3: October 2010. KyneticORM is the third version of the project that begun with the MetaQuery and the MetaDB libraries.
• V2: August 2010. MetaDB was the second version of this project. Even if this version is deprecated, it can still be found at codeproject. Some relevant improvements, and some bugs have been solved.
• V1: MetaQuery was the first version of this project. It focus was basically to send queries against a MS-SQL database, with no support of maps, and very primitive Insert, Delete and Update operations.