Saturday, June 25, 2005 12:15 AM bart

Adventures in Comega - part 5 tris (database access)

Introduction

In the previous two posts on database access in Comega I explained how to import a database schema using Sql2Comega.exe and how to use the select keyword in Comega to query the database using the imported data access assembly that contains the metadata information about the database. By using SQL statements in the language directly, you gain in strong-typing and compiler-level checking of statements sent to the server. Actually, the Comega database access stuff can be summarized as "wrapping" keywords around the underlying data provider (currently SQL Server only).

DML

It's fine to be able to access the database using some select construction. However, it's even nicer to have the ability to manipulate the data in the database using keywords, as queries are most typing-sensitive (think of parameterized DML statements which are sent to a database server). Let's start with insertion. Generally spoken, there are two different approaches to insert a new row in a table. First, you can use an implicitly constructed row, by using the following syntax:

int n = insert CustomerID="Test", ContactName="This", CompanyName="Too" into DB.Customers;

where n returns the number of affected rows. Remark that the string values are surrounded by the Comega string delimiters, thus double quotes. Alternatively, one can create the row explicitly (without a type specification, as shown in the previous posts too):

row = new {CustomerID="Test", ContactName="This", CompanyName="Too" into DB.Customers};
int n = insert row into DB.Customers;

In case of insertion conflicts and exceptions, e.g. because a required field value is needed or a constraint is violated, the classic try...catch structure in Comega can be used:

try
{
     row = new {CustomerID=someID, ContactName=someContact, CompanyName=someName into DB.Customers};
     int n = insert row into DB.Customers;
}
catch (SqlException ex) { ... }

As shown in the sample above, variables can be used to specify the field values. It's because of this that the string concatenation and statement parameterization stuff "classic approach" is vanishing due to the introduction of data access keywords in Comega. Note that it's possible too to nest select and insert statements, e.g. to write an insert statement that uses a query (in another table for example) to retrieve the row to be inserted in the table.

I guess that the update and delete statements are pretty straightforward once you get to know the idea of the insert statement. Basically, the structure looks as follows:

update DB.Table set Field1=value1, Field2=value2 ... where Field3 == value3 && Field4 == value4 || Field5 == value5 ...;

of course not formally formulated in BNF. But it gives the informal idea. Note it's also possible to use output parameters, by specifying an assignment to a local variable in the set portion of the statement, e.g.:

update DB.Table set local_variable_to_be_get=SomeFirstField, SomeSecondField=local_variable_to_be_set where ...;

Last but not least, the delete statement which is really as straightforward as possible:

delete from DB.Table where SomeField == somevalue;

Transactions

I hope all of my blog readers know the concept of transactions. If not, well, there's enough stuff to learn about these things. Briefly, a transaction has the ACID properties, what stands for atomicity, consistency, isolation, durability. Using this concept one can guarantee that multiple edit operations on some resource (for example a database) succeed or fail together in a all-or-nothing fashion. Now, one of the problems with transactions today is the lack of built-in block structure support to express transactions, commit actions and rollback actions. Well, honestly, the System.Transactions namespace in .NET v2.0 is a great enhancement, but not on the level of the language syntax. Comega changes this but introducing the keywords transact, commit and rollback. The structure looks like this:

transact(DB)
{
     //perform some database operations
}
commit
{
     //implicitly called when transact block succeeds
}
rollback
{
     //implicitly called when transact block has thrown an exception
}

If you want explicit control, e.g. based on conditional evaluation of return values of DML-statements or output parameters in update-statements, it's also possible to use the keyword rollback where needed:

transact(DB)
{
     //let's try something
     ...

     //let's check whether it's okay for us
     if (somevar != somerequiredvalue) rollback;
}

Put some breakpoints in your code and execute the sample. You'll see nothing about the DML operations using a tool like query-analyzer till the commit is executed (because of isolation).

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

Filed under:

Comments

No Comments