Recently, I’ve been involved in a cloud provider project where I had to do a row count on a Microsoft SQL Database table with more than 50.000.000 rows in it.

Doing

SELECT COUNT(*) FROM myTable

took about 10 seconds – since it performs a full table scan.

Here’s an alternative way (still accurate) of doing it that takes under 1 sec:

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('myTable')   
AND (index_id=0 or index_id=1);

You can run this from Microsoft Entity Framework as well:

long total = dbContext.Database.SqlQuery<long>("SELECT SUM (row_count) FROM sys.dm_db_partition_stats WHERE object_id=OBJECT_ID('myTable') AND (index_id=0 or index_id=1);").First();

We recently had a problem where the generated model in Entity Framework 4 would not create all the relationships we had in our database.

We were moving from a Linq2Sql model to Entity Framework and tried to automatically generate the model from the database, but we kept missing a few of the relationships. After hours of looking at the missing relationships in the database, comparing them to see why these would go missing while all others were ok, we got a hint at StackOverflow to check the indexes.

Having a Unique Non-Clustered Index on the primary key caused a problem for the EF model and changing these to a Unique Clustered Index fixed the problem.

From the world of databases we are familier with the term "atomic operation" : If not all operations succeed, don’t do any of them. An example is a banking system, where you move funds between accounts. If you deduct an amount from account #1 in operation 1, you definately want the amount to be added to account #2 in the next operation. If the second operation fails, roll back all previous changes.

Another example is if you are performing complex database operations that involve multiple queries run from a variety of classes and methods. You’ll eventually face the challenge of cleaning up any changes you might have made before an exception is thrown or an unexpected event occurs. This is where the System.Transactions.TransactionScope namespace comes in handy.

The System.Transactions assembly needs to be added to your project manually. Just add a reference like normal – you’ll find it under the .NET tab:

It requires your project to run on the .NET 4.0 platform, so change this in the project properties if necessary. Once referenced you can run your code in a transactional block, calling methods in different classes and commit it all (or roll back) in the end. My example below shows how two methods in different classes can do database operations within the same scope, and if an exception is thrown Complete() is not invoked and changes are rolled back:

static void RunInTransactionScope()
{
    using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required))
    {
        try
        {
            SomeClass1.DBOperation1();
            AnotherClass.DBOperation2();

            // commit
            trans.Complete();
        }
        catch (Exception e)
        {
            // all changes will be rolled back
        }

    } // transaction scope end
}

Take note of the different parameter options for the TransactionScope constructor. One in particular is the scope timeout, which is useful if your processing takes a long time or when you’re debugging.

Also check out a great tip for how to build dynamic WHERE clauses with LINQ in another post on this blog.

Nylig oppstod et behov for å hente ut informasjon fra et sett med .DBF-filer som stammet fra en kundes eldre backendsystem. Dette dreide seg om en databasestrukur hvor tabellene var lagret i separate filer, og sannsynligvis lå det nok en relasjonsoversikt godt gjemt inne i det gamle backendsystemet et sted. Kort oppsummert: Kunden trengte en daglig dump av disse dataene, i et format som passet et nyere 3-partssystem.

Kjappeste vei til mål var å lage en generisk OLEDB-klient som kunne hente ut det jeg trengte fra de tabellene jeg var interessert i. Gjenbruk var derfor av høy pri, og løsningen jeg kom frem til er noe som lett kan pakkes i en assembly og brukes mot nyere eller eldre databasesystemer når behovene er enkle og tiden er knapp.

Logging og utvidet feilhåndtering er fjernet for å øke lesbarhet. OleDbConnection kan byttes ut med for eksempel SqlConnection om måldatabasen er SQL Server:

OLEDBClient.cs

public class OLEDBClient
{
private OleDbConnection _conn = null;
private string _connectionString = null;

}
catch (Exception e)
{
return null;
}
}
}


Denne klassen kan da kalles med en connection string som peker på en .dbf, i dette formatet:
“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\TEST\\DB.DBF”:

SomeApp.cs

private void GetSomeDataFromOleDB()
{
OLEDBClient client = new OLEDBClient(_DBConnectionString);

......