Using anonymous types with ad-hoc SQL queries

[Update 2012.05.14] The information in the original article still applies fine, however I’ve found, on a blog, a way to run an ad-hoc SQL query when an Entity Framework model is already in place.

The code below displays this technique.

// NOTE: THE CODE BELOW USES #IFDEF STATEMENTS, WHICH IS ALL RIGHT FOR A RESEARCH/TUTORIAL
// SAMPLE CODE.
// DO NOT USE SUCH STATEMENTS IN PRODUCTION, INSTEAD GO FOR A FACTORY OR STRATEGY 
// DESIGN PATTERN!

// Uncomment one of the switches below to verify the two techniques in parallel.
#define WITH_ENTITY_FX
//#define WITH_DYNAMIC_KWD

using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
#if WITH_DYNAMIC_KWD
using System.Data;
#endif
// Use NuGet to reference EntityFramework 4.3.1.
// It's very likely this code will work fine with EF 5, however,
// when this code has been written (2012.05.14), EF 5 was still in beta.
#if WITH_ENTITY_FX
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Objects;
#endif

namespace DemoDynamicAndSql
{
    class Program
    {
        static void Main(string[] args)
        {
            var cnxString = GetCnxString();
            ValidateDbPhysicalSchema(cnxString);
        }

#if WITH_ENTITY_FX
        private static void ValidateDbPhysicalSchema( string cnxString )
        {
            using( var ctx = new ThisAppContext( cnxString ) )
            {
                var sql = @"select name, type from sys.database_files";
                var reader = ctx.ObjectContext.ExecuteStoreQuery<FileInfo>( sql );
                // Notice the "impedance mismatch" - LINQ defines ToList/ToArray only for IEnumerable.
                // The reader object only gives access to an IEnumerator object and an utility extension method
                // is needed to convert between IEnumerator and IEnumerable.
                var filesInfo = reader.GetEnumerator().ToEnumerable().ToList();
                // Validate the db has at least one data file, one log file and a FILESTREAM file.
                // See http://msdn.microsoft.com/en-us/library/ms174397.aspx
                var dataFile = filesInfo.FirstOrDefault(i => i.Type == 0);
                var logFile = filesInfo.FirstOrDefault(i => i.Type == 1);
                var fsFile = filesInfo.FirstOrDefault(i => i.Type == 2);

                var dbPhysicalSchemaIsValid = (dataFile != null) && (logFile != null) && (fsFile != null);
                // TODO: Report status here.
            }
        }
#endif

#if WITH_DYNAMIC_KWD
        private static void ValidateDbPhysicalSchema(string cnxString)
        {
            var filesInfo = new List<dynamic>();
            using (var cnx = new SqlConnection(cnxString))
            {
                cnx.Open();
                var cmd = cnx.CreateCommand();
                cmd.CommandText = "select name, type from sys.database_files";
                cmd.CommandType = CommandType.Text;
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var name = reader.GetString(0);
                        var type = reader.GetByte(1); // sys.database_files.type is tinyint
                        var fileInfo = new {Name = name, Type = type};
                        filesInfo.Add(fileInfo);
                    }
                }
            }
            // Validate the db has at least one data file, one log file and a FILESTREAM file.
            // See http://msdn.microsoft.com/en-us/library/ms174397.aspx
            var dataFile = filesInfo.FirstOrDefault(i => i.Type == 0);
            var logFile = filesInfo.FirstOrDefault(i => i.Type == 1);
            var fsFile = filesInfo.FirstOrDefault(i => i.Type == 2);

            var dbPhysicalSchemaIsValid = (dataFile != null) && (logFile != null) && (fsFile != null);
            // TODO: Report status here.
        }
#endif

        private static string GetCnxString()
        {
            var cnxStringBuilder = new SqlConnectionStringBuilder();

            cnxStringBuilder.ApplicationName = "C# dynamic demo";
            cnxStringBuilder.DataSource = "(local)";
            cnxStringBuilder.InitialCatalog = "NPS";
            cnxStringBuilder.IntegratedSecurity = true;
            cnxStringBuilder.Pooling = false;
            var cnxString = cnxStringBuilder.ConnectionString;

            return cnxString;
        }
    }

#if WITH_ENTITY_FX
    class ThisAppContext: DbContext
    {
        public ThisAppContext( string cnxString )
            :
            base( cnxString)
        {
            
        }

        // See http://thedatafarm.com/blog/data-access/accessing-objectcontext-features-from-ef-4-1-dbcontext/
        // This is needed to use methods available in the ObjectContext class, but not in DbContext.
        public ObjectContext ObjectContext
        {
            get
            {
                return (this as IObjectContextAdapter).ObjectContext;
            }
        }
    }

    class FileInfo
    {
        public string Name { get; set; }
        public byte Type { get; set; }
    }

    internal static class EnumeratorToEnumerable
    {
        public static IEnumerable<T> ToEnumerable<T>(this IEnumerator<T> enumerator)
        {
            while (enumerator.MoveNext())
                yield return enumerator.Current;
        }
    }
#endif
}

[Beginning of original article]
We had to implement a code sequence to validate the physical schema of a database. For simplicity, let us assume we install an application on a computer and we have to create an application database. The database data file is named A.mdf. The database must contain at least one data file, at least one log file and at least one FILESTREAM file.

During setup, the code detects the file A.mdf already exists.

There are multiple strategies in handling such a situation, but our customer always asks for the following: verify it is a valid application database and then ask the user what to do (if possible, use existing file, if not, tell him the file A.mdf should be deleted and ask for permission to do so).

The code to check the database physical schema should retrieve the list of database files.

The easiest way to do this is via sys.database_files.
The code sequence below reads data from sys.database_files. It uses an SqlDataReader and it stores the read data in  anonymous types.
Then it uses the dynamic C# feature to access this data.
Without having the possibility to define anonymous types and the dynamic keyword, the code would have had  to define an SqlDataFileInfo type and use it.
By using the anonymous type, less code has to be written.
Of course, you have to make sure similar anonymous types are not used elsewhere. If they are,  you should define the new type to encapsulate the needed information.

If you decide to write such code, make sure you have good unit tests coverage, because the compiler no longer can detect a member name mistype; for example, the simplest mistake I did was to type ‘type’ instead of ‘Type’; the code compiled fine and the error showed at runtime.

Also, such code should be written only in very special scenarios where the ORM cannot be used.

using System.Collections.Generic;
 using System.Data;
 using System.Data.SqlClient;
 using System.Linq;
 namespace DemoDynamicAndSql
 {
 class Program
 {
 static void Main(string[] args)
 {
 var cnxString = GetCnxString();
 var filesInfo = new List<dynamic>();
 using( var cnx = new SqlConnection( cnxString))
 {
 cnx.Open();
 var cmd = cnx.CreateCommand();
 cmd.CommandText = "select name, type from sys.database_files";
 cmd.CommandType = CommandType.Text;
 using( var reader = cmd.ExecuteReader())
 {
 while( reader.Read())
 {
 var name = reader.GetString(0);
 var type = reader.GetByte(1); // sys.database_files.type is tinyint
 var fileInfo = new {Name = name, Type = type};
 filesInfo.Add( fileInfo );
 }
 }
 }

// Validate the db has at least one data file, one log file and a FILESTREAM file.
 // See http://msdn.microsoft.com/en-us/library/ms174397.aspx
 var dataFile = filesInfo.FirstOrDefault(i => i.Type == 0);
 var logFile = filesInfo.FirstOrDefault(i => i.Type == 1);
 var fsFile = filesInfo.FirstOrDefault(i => i.Type == 2);

var dbPhysicalSchemaIsValid = (dataFile != null) && (logFile != null) && (fsFile != null);

// Report status here.

}

private static string GetCnxString()
 {
 var cnxStringBuilder = new SqlConnectionStringBuilder();

cnxStringBuilder.ApplicationName = "C# dynamic demo";
 cnxStringBuilder.DataSource = "(local)";
 cnxStringBuilder.InitialCatalog = "NPS";
 cnxStringBuilder.IntegratedSecurity = true;
 cnxStringBuilder.Pooling = false;
 var cnxString = cnxStringBuilder.ConnectionString;

return cnxString;
 }
 }
 }
About these ads

3 thoughts on “Using anonymous types with ad-hoc SQL queries

  1. Aw, this was a very nice post. In idea I want to put in writing like this additionally – taking time and actual effort to make an excellent article… but what can I say… I procrastinate alot and not at all appear to get something done.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s