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;

Insert

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 */;
db.InsertBatch<MyTable>(newItems);

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 */;
db.Update<MyTable>(data);
db.Delete<MyTable>(data);

/* multiple items */
List<MyTable> list = /* get your data somewhere */;
db.Update<MyTable>(list);
db.Delete<MyTable>(list);

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");

Select

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",
        title.EscapeSphinxSpecialChar());
    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.

image.png

Advertisement

Working with Oracle in .NET – part I : SSMA

There are many ways to connect to an Oracle database from a .NET application. You can use ADO .NET directly, or use an ORM solution, such as NHibernate or the likes. In my workplace, previously we are using NHibernate. But for some reason, we stop using it and revert back to the old ADO .NET way.

However, after some experiments, I’ve found my own way to work with Oracle in a .NET environment. I’m using a library called BLToolkit, along with several other tools. After reading their documentation, I’m convinced enough to use it, and so far I’m very satisfied with the result.

Our development approach is database-first, and this article will only cover that. So if you are using code-first approach, this article would not be relevant to you.

The first thing we need is a data model class that maps to our Oracle database. Since we are already spending our precious time designing the database, we want the class to be auto-generated from the database. BLToolkit provides this kind of functionality using a T4 template. Unfortunately, they don’t support generating data model class directly from an Oracle schema. Since I’m not capable enough to write a T4 template to achieve this, I need a workaround.

My workaround here, is by using SQL Server and a utility called SSMA for Oracle. The idea here is by importing our Oracle database to an SQL Server database, and generate a data model from there. For this purpose, the SQL Server Express Edition is sufficient, so you don’t have to worry about licensing issues.

Importing the database

  1. Open SSMA for Oracle and create a new project.clip_image001
  2. Click Connect to Oracle, and provide the information to connect to the Oracle server.

    clip_image002

    Depends on what user you use to connect to Oracle, you might be shown this warning. In my case, I can safely ignore it and click Continue.

    clip_image003

  3. Click Connect to SQL Server, and provide the information to connect to the SQL Server.

    clip_image004

    If you are connecting to SQL Server Express, like me, you will get this warning. Just ignore it and click Continue.

    clip_image005

  4. Optionally, you can change the type mapping between Oracle and SQL Server. I usually change the mapping of number to decimal instead of the default float. The reason for this is because I usually use number as a primary key in Oracle. If it is mapped as float in SQL Server, then it would lost its primary key status because SSMA doesn’t seems to allow float to be used a primary key.

    clip_image006

  5. If you made a change to the Oracle schema after connecting to Oracle, you can refresh them by right-clicking the schema name and select Refresh from Database.

    clip_image007

  6. Tick the checkbox next to the schema(s) you want to import, and click Convert Schema.

    clip_image008

    Optionally, you could also change the target schema in SQL Server.

    While the process is running, you can inspect it through the Output window.

  7. On the SQL Server Metadata Explorer, you’ll see the target schema produced by the conversion process. Right click on it, and select Sync with Database. This process will actually update our SQL Server with the new definition.

    clip_image009

  8. Done. Now you should be able to see your shiny new database (or an up-to-date version of the existing ones) in SQL Server.

Handling the problems

You may encounter some issues while doing this conversion process (you can see them in the Error List Window). Once, I got an error setting a float column as a primary key. Changes the type mapping of number from float to decimal (as described in step 4 above) solve this.

However, some issues might not be so important to us. Keep in mind, the main reason we are doing this process are merely to enable us to auto-generate our data model class. So as long as the SQL Server tables has the same column name, column type, primary key, and foreign key specification as their counterpart in Oracle, then it should be good enough for us.

On the next article, I will cover on how to generate the data model class using the T4 template provided by BLToolkit.