Working with Oracle in .NET – part IV : CRUD

So far, this is what we have done :

  1. Creating a schema in SQL Server, identical with our Oracle schema using SSMA.
  2. Generate the DAL using T4 template and the identical schema in SQL Server.
  3. Provide a parameter-less constructor to simplify the usage of the DAL.

Now it is time to actually using the DAL to work with our Oracle database. For examples in this article to work, you will need to include the BLToolkit.Data.Linq namespace in your using clause.

using BLToolkit.Data.Linq;


Use the following method if your table have an auto-increment column as its primary key, and you want to retrieve the newly inserted identity :

MyTable newData = /* get your new data somewhere */;
var newId = Convert.ToDecimal(db.InsertWithIdentity<MyTable>(newData));

Change the conversion part to match the type of your identity column.

Please note though, the above method generates an SQL statement that sets the value of the primary key column too (while actually we want the RDBMS to generate the value for us). Depends on your database design, this may lead to an undesirable result.

For my case, the above method works fine because in my database, the primary key will always auto-generated by Oracle, effectively discarded the value provided by the user (it explained here). If you don’t have an equivalent setup in your database, you better use the following alternative :

var newId = Convert.ToDecimal(db.MyTable.InsertWithIdentity(() => new MyTable { 
    /* populate all the column here, excluding the identity column */
    Description = "new item",
    Price = 2000

The disadvantage of the above approach is you must populate the new object once again inside the object initializer, skipping the identity column. I’ve seen someone complaining about this, and apparently it can be avoided by decorating the identity column with NonUpdatable attribute. However, I haven’t tried that approach yet.

Bulk Insert

If you need to insert multiple items at once, use the InsertBatch method :

List<MyTable> newItems = /* get your new items somewhere */;

Update and Delete

Previously, I’ve been using Linq to Sql (L2S) as my ORM. In L2S, as far as I know, you need to select the data before you can update or delete them. The similar pattern is possible using BLToolkit :

/* single item */
MyTable data = /* get your data somewhere */;

/* multiple items */
List<MyTable> list = /* get your data somewhere */;

However, for some cases, this pattern is far from efficient (you can read more about it here or here). Fortunately, BLToolkit has provided update / delete operation using predicate, effectively providing us with an efficient bulk operations :

/* update the status of all expired items */
db.MyTable.Update(p => p.ExpiredDate.Date < DateTime.Now.Date, p => new MyTable { Status = "Expired" });/* delete all expired items */
db.MyTable.Delete(p => p.Status == "Expired");


IMHO, BLToolkit have an excellent LINQ support. Standard every day operations like filtering, sorting, and paging is quite straightforward :

/* fluent style */
var data1 = db.MyTable.Where(p => p.Description.Contains("keyword")).OrderBy(p => p.Description).Skip(10).Take(10);                /* query style */
var data2 = (from p in db.MyTable where p.Description.Contains("keyword") orderby p.Description select p).Skip(10).Take(10);

BLToolkit also has association feature that provide a neat alternative to LINQ’s join. You can read more about it here. Of course, if you really need to, you can also doing the join operation using LINQ too. For more information of other possibilities, you can read here or here.

You can also use other library for LINQ like LinqKit and their PredicateBuilder for more filtering options, or Dynamite to provide a dynamic sorting capabilities.

Custom Query

Apart from generating the SQL for us, BLToolkit also allowed us to execute a custom query. I personally use this feature when I was working with Sphinx.

using (var db = new SphinxDbManager())
    var query = string.Format(
        "select catalogid from catalog where match('@value \"{0}\" @tag 245 @subruas a') group by catalogid limit 0, 1000000 option max_matches = 1000000",
    return db.SetCommand(query).ExecuteList<CatalogDocument>().Select(q => q.CatalogID).ToList();

Stored Procedure

Frankly speaking, I don’t use the keep-it-in-stored-procedure development style. But from what this article says, it seems good enough. If you need stored procedure support, maybe you can use the article as a starting point, besides the BLToolkit documentation itself.

Debugging the SQL

Ever wondered what kind of SQL produced by BLToolkit? The DbManager class has a property called LastQuery. This property contains the last query sent to the database server. As an Oracle newbie, I learned a lot of PL/SQL from this property 🙂

The SQL in DbManager’s LastQuery is still in a parameterized form. If you want to know the value of the parameters, you should store the IQueryable in a variable. The IQueryable has a SqlText property, where you can see the value assigned to the parameters (in a commented form), beside the query itself. This way, you can get a deeper insight of what happened under the hood.