Hugoware

The product of a web developer with a little too much caffeine

Posts Tagged ‘Database

Anonymous Types As Object Templates

with 6 comments

I’ve got a bit of an addiction to intellisense. Dynamic types are interesting but I still prefer being able to see a nice neat list of properties on each of my objects.

Since anonymous types are ‘real’ classes then you can actually create them at runtime. This means you can use them as templates for other objects if you can provide all of the required parameters.

Below is a simple class that allows you to perform queries against a database and use an anonymous type to provide a template for the records to return.

/// <summary>
/// Helps working with database connections
/// </summary>
public class DataConnection {

    #region Constructors

    /// <summary>
    /// Creates a new DataConnection for the connection string
    /// </summary>
    public DataConnection(string connection) {
        this.ConnectionString = connection;
    }

    #endregion

    #region Properties

    /// <summary>
    /// The connection string to use with this DataConnection
    /// </summary>
    public string ConnectionString { get; private set; }

    #endregion

    #region Performing Queries

    /// <summary>
    /// Performs a query for the connection without any parameters
    /// </summary>
    public IEnumerable<T> Query<T>(string query, T template)
        where T : class {
        return this.Query(query, (object)null, template);
    }

    /// <summary>
    /// Performs a query for the connection with the provided paramters
    /// </summary>
    public IEnumerable<T> Query<U, T>(string query, U parameters, T template)
        where T : class 
        where U : class {

        //prepare the connection and command
        Type type = template.GetType();

        //create the connection - (thanks @johnsheehan about the 'using' tip)
        using (SqlConnection connection = new SqlConnection(this.ConnectionString)) {
            using (SqlCommand command = new SqlCommand(query, connection)) {

                //assign each of the properties
                if (parameters != null) {
                    this._UsingProperties(
                        parameters,
                        (name, value) => command.Parameters.AddWithValue(name, value));
                }

                //execute the query
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                //start reading each of the records
                List<T> results = new List<T>();
                Dictionary<string, int> fields = null;
                while (reader.Read()) {

                    //prepare the fields for the first time if needed
                    fields = fields == null ? this._ExtractFields(reader) : fields;

                    //generate the record
                    T record = this._CreateAnonymousResult(reader, fields, type, template);
                    results.Add(record);
                }

                //return the results for the query
                return results.AsEnumerable();
            }

        }

    }

    #endregion

    #region Helpers

    //creates a new anonymous type from 
    private T _CreateAnonymousResult<T>(SqlDataReader reader, Dictionary<string, int> fields, Type templateType, T template)
        where T : class {

        //create a container to queue up each record
        List<object> values = new List<object>();

        //use the template to find values
        this._UsingProperties(template, (name, @default) => {

            //try and find the field name
            if (fields.ContainsKey(name)) {

                //check if this is a value
                int index = fields[name];
                object value = reader[index];
                Type convert = @default.GetType();

                //try and copy the va;ue
                if (value != null) {
                    values.Add(Convert.ChangeType(value, convert));
                }
                //not the same type, use the default
                else {
                    value.Equals(@default);
                }

            }
            //no field was found, just use the default
            else {
                values.Add(@default);
            }

        });

        //with each of the values, invoke the anonymous constructor
        //which accepts each of the values on the template
        try {
            return Activator.CreateInstance(templateType, values.ToArray()) as T;
        }
        catch (Exception e) {
            Console.WriteLine(e.Message);
            return template;
        }

    }

    //reads a set of records to determine the field names and positions
    private Dictionary<string, int> _ExtractFields(SqlDataReader reader) {
        Dictionary<string, int> fields = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
        for (int i = 0; i < reader.FieldCount; i++) {
            fields.Add(reader.GetName(i), i);
        }
        return fields;
    }

    //performs an action with each of the properties on an object
    private void _UsingProperties(object obj, Action<string, object> with) {
        
        //if there isn't anything to work with, just cancel
        if (obj == null) { return; }

        //get the type and peform an action for each property
        Type type = obj.GetType();
        foreach (PropertyInfo prop in type.GetProperties()) {
            with(prop.Name, prop.GetValue(obj, null));
        }

    }

    #endregion

}

This class allows us to perform basic queries against a database and then provide a template of the records to return. This means that if the information is found then the database value is used but if it is missing then the template value is used instead.

In order to create an anonymous type we need to know the type and each of the properties in the order they appear. After collecting this information we simply need to use the Activator.CreateInstance method to instantiate the class and we’re set! (method _CreateAnonymousResult)

This means we can write a ‘dynamic’ query with results that have intellisense!

//set up the connection
DataConnection data = new DataConnection(CONNECTION_DATA);

//perform the query
var results = data.Query(
    "select * from orders where orderId > @orderId",
    new { orderId = 11000 },
    new {
        orderId = -1,
        shipName = "missing",
        shipRegion = "none"
    });

//access properties
var record = results.First();
int id = record.orderId; //intellisense!

I’ve actually used this same approach before in CSMongo as a way to provide intellisense without knowing anything about the database in advance.

Of course, this is just some code I hacked out this evening. You aren’t going to be able to do a lot with it but it is a good example of how you can reuse anonymous types in your code.

Advertisements

Written by hugoware

August 30, 2010 at 12:19 am

CSMongo On GitHub

with 16 comments

I’ve been hammering away at the keyboard for a few weeks now and my beta version of CSMongo is now live on GitHub!

I’ve written a lot of documentation in for the project in the Wiki section on the site, so if you’re interested you can start reading the details of how the code works.

In this post I’ll just go over some of the more interesting things the CSMongo does to make working with Mongo easier than normal.

Anonymous Types and Dynamic Creation

Working with documents, including those with multiple levels, is a snap with CSMongo. You have many ways to make changes but each of these ways also allow you to create entirely new sections of your document.

MongoDocument document = new MongoDocument();

//add some new items
document += new {
    name = "Hugo",
    age = 30,
    settings = new {
        font = "arial",
        color = "orange"
    }
};

//set values for a lower level field
document.Set("browser.urls", new object [] {
    new { site = "google.com", favorite = false },
    new { site = "hugoware.net", favorite = true, icon = "blue-icon.ico" }
});

//or create new elements with an index path
document["blog.url"] = "https://somewebguy.wordpress.com";

//or use existing objects as a template
Uri address = new Uri("http://www.herdingcode.com");
document += address;

Nifty Anonymous Type Mapping Method TM

One thing that I didn’t like was always having to refer to elements using a string. I had a moment late one night … er… maybe it was early one moring… like around 1AM or so — Why can’t I use a return anonymous type value to act as a template for the values to use. Here is an example of what I put together…

//grab a document - Assume that the value returned
//looks like the JSON example below
MongoDocument document = database.Find("users").SelectOne();
//{
//    name : "Jimmy",
//    age : 50,
//    website : "http://www.hugoware.net"
//    settings : {
//        font : "arial",
//        color : "red"
//    }
//}

//provide an anonymous type as the template to return
//Notice the values provide match in some places but not 
//in other areas
var value = document.Get(new {
    name = "no name",
    age = -1,
    admin = false,
    favorite = Mongo.Null,
    settings = new {
        color = "black",
        version = 0
    }
});

//now we have an anonymous type with the values
//it could find and then the default values for the
//sections it couldn't find or was missing
value.name; // "Jimmy"
value.age; // 50
value.admin; // false (fallback)
value.favorite; // null (fallback)
value.settings.color; // "red"
value.settings.version; // 0

This means that we can refactor our code without needing to update a bunch of string values. You can even use this mapping option directly after a query so all of the values are converted into the new type automatically.

Lazy Collections

When you’re making changes to a MongoDatabase, for the most part all of the commands are immediate – meaning they connect to the database right away and perform their work. However, most commands inside of a MongoCollection don’t execute until you call the SubmitChanges method. This can allow you to queue up a few inserts and deletes or make changes to documents that have been loaded into memory and then update them all at once. The code below illustrates how it works.

//Inserting Documents
//=================================
MongoCollection collection = database.GetCollection("users");

//add a few documents
collection.InsertOnSubmit(new { name = "Hugo" });
collection.InsertOnSubmit(new { name = "Mary" });
collection.InsertOnSubmit(new { name = "Gwyn" });
collection.InsertOnSubmit(new { name = "Cassie" });

//nothing has been inserted yet, do it now
collection.SubmitChanges();

//Performing Updates
//=================================
MongoCollection collection = database.GetCollection("users");
var documents = collection.Find()
    .Greater("age", 50)
	.Select();
	
//make each of the changes
foreach(MongoDocument document in documents) {
    document += { isOld = true };
}

//documents are only changed in memory
//send the update to the server
collection.SubmitChanges();

//Deleting Documents
//=================================
MongoCollection collection = database.GetCollection("users");
var documents = collection.Find()
    .Greater("age", 50)
	.Select();
	
//mark each for deletion
foreach(MongoDocument document in documents) {
    collection.DeleteOnSubmit(document);
}

//documents are only changed in memory
//send the update to the server
collection.SubmitChanges();

Of course, you can perform a blanket update statement using a query from the MongoDatabase object itself, but this is a good example of how a MongoCollection can do it in a lazy way.

It is also worth mentioning that if you use the GetCollection method to work with a collection then it is also monitored by the database. This means that if you call the MongoDatabase method SubmitChanges then it will check each of the collections it is tracking and submit their changes automatically.

Try It Out!

If you’ve haven’t been sure if you want to try out MongoDB yet then now is a great time! The links below can help you get started!

Getting Started
MongoDB Website
Using MongoDB With Visual Studio

Source Code
CSMongo on GitHub
CSMongo Wiki Help on GitHub

Written by hugoware

March 1, 2010 at 12:35 am

jLinq in MongoDB (Oh snap!!)

with 9 comments

My new project CSMongo, a Mongo driver for .NET, is now online! Check it out!

Yeah, you read that right – jLinq in MongoDB!

Lately I’ve been working on a Mongo database driver and I got to thinking — Since I can use Javascript with MongoDB then I wonder if I could use jLinq to write queries? As it turns out the answer is yes!

… and if you don’t know what jLinq is… – jLinq is a javascript query language, similar to the LINQ project created by Microsoft, for JSON data which was designed to run within web pages using Javascript

Getting Started

I’m not really sure how you can get a Javascript library to load directly into MongoDB but for now I was simply copying and pasting the existing jLinq packed library into the command line and allowing it to evaluate the script — and that is it! You can use jLinq right away!

You should be aware though that you can’t just evaluate results directly. You actually need to call the toArray() function on each of them to see the results. So for example, your query would look something like…

jLinq.from(db.users.find().toArray()).startsWith("name", "h").select();

You don’t need to actually do anything with the results since they are automatically displayed on the screen for you.

So far everything works. I could do standard queries, use the OR operator, reorder records, join different databases, perform comparisons against sub-properties — really everything I’ve tried has worked just the way I’d expect it to!

Granted, database and collection names are going to be different, here are some interesting queries you can try with your database.

//Selects records where the name starts with a, b or c (case-insensitive)
jLinq.from(db.users.find().toArray())
    .startsWith("name", "a")
    .or("b")
    .or("c")
    .select();

//selects users older than 50 and are administrators then orders them by their names
jLinq.from(db.users.find().toArray())
    .greater("age", 50)
    .is("admin")
    .orderBy("name")
    .select();

//performs a join against another database to get locations
//and then queries the location to find users that live in 'texas'
//and then selects only the name of the person and location
jLinq.from(db.users.find().toArray())
    .join(db.locations.find().toArray(), "location", "locationId", "id")
    .equals("location.name", "texas")
    .select(function(rec) {
        return {
            person: rec.name,
            location: rec.location.name
        };
    });

More On jLinq

Unfortunately, it isn’t possible to go over all of the cool stuff you can do with jLinq – but here are some screencasts and blog posts that you can read that might help you get started.

jLinq Project Page
Screencast 1 – Getting Started
Screencast 2 – Extending jLinq (Custom commands)
Screencast 3 – Modifying Live Data In A Query (Joins, Assignment)
Screencast 4 – jLinq 2.2.1 (Updates)

Anyways, for now I’m not sure how to integrate jLinq into Mongo queries but keep an eye for future blog posts as I find out more.

Written by hugoware

February 14, 2010 at 11:45 pm

Using MongoDB With Visual Studio

with 6 comments

My new project CSMongo, a Mongo driver for .NET, is now online! Check it out!

I recently found out about MongoDB and decided to check it out and so far I’ve been really impressed. The most interesting thing about MongoDB that it doesn’t work like a normal database — there aren’t really any schemas for any of the “tables”. In fact, you can make changes to the structure of any record at any time without affecting the rest of the “table”.

Getting Started

Here are a few steps I used to get MongoDB running and testable from my Visual Studio. To keep my main computer clean I used a virtual instance of Ubuntu to host the “database server”.

This part might take a little bit of time but start by downloading VirtualBox and Ubuntu 9.10 (You don’t need to download Mongo just yet). Once everything is downloaded install and configure Ubuntu but don’t start it up right away (you need to configure some stuff).

You’re going to want to make sure that your PC can connect to the virtual instance on the standard MongoDB port (unless you change it of course). If not, open a command prompt (on the host system) and then run the following commands (from the VirtualBox directory).

VBoxManage setextradata UbuntuDev "VBoxInternal/Devices/pcnet/0/LUN#0/Config/MongoDB/HostPort" 27017
VBoxManage setextradata UbuntuDev "VBoxInternal/Devices/pcnet/0/LUN#0/Config/MongoDB/GuestPort" 27017
VBoxManage setextradata UbuntuDev "VBoxInternal/Devices/pcnet/0/LUN#0/Config/MongoDB/Protocol" TCP

This example uses the same name (UbuntuDev) that I used for the screenshot example above. Make sure you use the correct name when setting yours up.

It is also worth mentioning that I had to use a Bridged Connection for my Network connection so I’d get an IP address from my wireless. But, if you aren’t on a wireless network… say, like your in-laws house for several hours… you can use Host Only Adapter so you can keep working… just sayin’…

Inside Ubuntu

Once you’ve successfully setup your Ubuntu system go on and download the MongoDB binaries. I don’t know the correct location to install these files so I placed them inside /etc/mongodb.

If you’ve never used Linux before then warm up your typing fingers and open a terminal window (Applications > Accessories > Terminal). Start typing in the following commands…

sudo bash
mkdir /data
mkdir /data/db
mkdir /etc/mongodb
chmod a=rwx /etc/mongodb

Note: This is certainly not the recommended security setup for this folder but for our testing purposes it is sufficient.

At this point we can revert back to our lazy Windows ways and drag the contents of the .tgz file into the /etc/mongodb directory. Once we have the contents copied over switch back to the terminal window and then type…

sudo /etc/mongodb/bin/mongod

And you should see something like the screenshot below…

Once you see this message you should be ready to test from Visual Studio but you can always test it from Ubuntu by opening a new Terminal window and typing…

sudo /etc/mongodb/bin/mongo

Which allows you to enter commands and make changes to the database similar to the online demo on their website.

Connecting Via Visual Studio

I haven’t found much for C# code to connect to Mongo but there is currently a project hosted on GitHub that allows you to perform queries and edit documents. It is a mess of code – but it *does* at least work… mostly… (nothing personal guys) 😉 Here is a simple example of how to use the code…

//connect to the source (IP address of virtual)
Mongo mongo = new Mongo("100.0.0.1");
mongo.Connect();

//get the database and 'table'
Database website = mongo.getDB("website");
IMongoCollection users = website.GetCollection("users");

//make a new document of information
Document user = new Document();
user["name"] = "Hugoware";
user["age"] = 29;
user["isAdmin"] = false;

//then save the document
users.Insert(user);

If you still have your Terminal window up then you might have noticed messages listed in response to your update. If you still have the MongoDB command line up you can view your changes by entering in a few commands. For example, to see the database I just created in the sample above I would enter…

use website
db.users.find()

And I would get a response similar to this…

What Is Next?

Personally, I think MongoDB is going to end up being huge. The main problem I see for C# developers is that MongoDB really favors Dynamic Languages which isn’t really a strong suit of the language.

Right now I’m working on my own driver to talk to Mongo that heavily relies on my AnonymousType code (anybody remember that old stuff?). It is still early on in the project so if you’re interested in helping feel free to contact me.

Written by hugoware

February 9, 2010 at 11:04 am