by Njål

File Format/Extension CSV

 

imageHere is a CSV(comma separated values) list I put together with over 10500 file formats and descriptions.

I got the content from various websites – by making a small C# program that screenscraped the content that I wanted to collect.

Download the csv file here.

If you want to include this list directly in your c# code – then grab this this file.

by Njål

Windows Azure SQL – Entity Framework 6 Retry Logic

 

One of the biggest drawbacks of using Azure SQL is that the connection towards the database is a lot more fragile and unstable than when you’re using a database server on your LAN. Also – your Azure database is running on servers with several hundred other databases – so the database server might disconnect you at any given time to ensure fairness of service etc.

These are transient errors – meaning that a retry of the query most often will succeed and fix the error.

Microsoft has released several code blocks and guidelines to help developers use this retry logic – http://code.msdn.microsoft.com/windowsazure/SQL-Azure-Retry-Logic-2d0a8401

And luckily – the EntityFramework team has added support for this in version 6 as well.

Here’s how you can get started with EF6 (beta 1) – using the built in SqlAzureExecutionStrategy

 

1. Install EF6 beta by opening the NUGET console in Visual Studio and typing
     PM> Install-Package EntityFramework –Pre

2. Install the EF6 Template generator – http://visualstudiogallery.msdn.microsoft.com/18a7db90-6705-4d19-9dd1-0a6c23d0751f. This will create the C# code for you – which will handle the communication towards the database

3. Restart Visual Studio

4. Add New Item  -> EDMX file. Connect to Database and/or specify your datamodel. Just like older versions of Entity Framework.

5. Right click in the EDMX editor – and select Add Code Generation Item. Select EF 6.x DbContext Generator.

6.
Compile. You should now have EF6 up and running. Only one crucial part missing – the Azure Retry Logic.

7. Add a class inside your Project with the following code. It will get picked up upon Application Start – and make sure that all the EF Database queries will be retried if they fail.

 

using System;
using System.Data.Entity.Config;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.SqlServer;

namespace Www.Business.Database
{
    public class SqlAzureDbConfiguration : DbConfiguration
    {
        public SqlAzureDbConfiguration()
        {
            AddExecutionStrategy(() => new SqlAzureExecutionStrategy(), null);
        }

        protected void AddExecutionStrategy(Func<IExecutionStrategy> getExecutionStrategy, string serverName)
        {
            AddDependencyResolver(new ExecutionStrategyResolver<IExecutionStrategy>("SqlAzureExecutionStrategy", serverName, getExecutionStrategy));
        }
    }
}

(The example provided here: http://entityframework.codeplex.com/wikipage?title=Connection%20Resiliency%20Spec does not work/compile)

by Njål

Quick row count from Microsoft SQL Database

 

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

by Stian

Missing relations in EntityFramework model when generating from database

Talk to the handWe 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.

by Andreas

TransactionScope – atomic transactions in C#

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:

image

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.

Tags: ,
by Andreas

Generisk databaseklient

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;

public OLEDBClient(string connectionString)
{
_connectionString = connectionString;
}

private void connect()
{
// throws exception if connection fails
if (_conn == null)
{
_conn = new OleDbConnection(_connectionString);
}
}

public bool testConnection()
{
try
{
if (_conn == null)
connect();

return true;
}
catch (Exception e)
{
_log.Fatal("testConnection() error: '" + e.Message + "'");
return false;
}
}

public DataSet runGenericSQL(string sql)
{
try
{
if (_conn == null)
connect();

OleDbDataAdapter adapter = new OleDbDataAdapter(sql, _conn);
DataSet resultSet = new DataSet();
adapter.Fill(resultSet);
return resultSet;

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

// ** SQL QUERY DEFINED HERE ** //
DataSet result = client.runGenericSQL("SELECT * FROM someTable WHERE someField IS NOT NULL");

if (result == null)
throw new Exception("Unable to retrieve data from database connection '" + _DBConnectionString + "'");

// loop through all records
foreach (DataRow row in result.Tables[0].Rows)
{
if (row["someField"] != null && !row["someField"].Equals(""))
{
// do something

}

......