Inactive

Project Summary

  Analyzed 3 days ago based on code collected 3 days ago.

So let's say you have a Postgres database called "mysite" with these two tables:

blogs

blog_id blog_name 1 Deep Thoughts 2 Mostly Rants

posts

post_id blog_id post_title post_body 1 1 What are flowers, really? Can they be trusted? I don't… 2 1 I'm being watched I get a creepy feeling that…

One-line QueryingOften, raw SQL is the most straightforward way to talk to Postgres. PGMapper doesn't try to abstract away the database, it just makes interaction easier. First:

var db = new PostgresDatabase("server=localhost;port=5432;database=mysite");Now let's write a simple query.

var name = db.ExecuteScalar("select blog_name from blogs where blog_id = ?", 1);

Console.WriteLine(name);
//> "Deep Thoughts"Not only was that just one line of code, it also used a query argument, easily specified with the question mark. Internally, it's using "safe" command/parameter objects (courtesy of Npgsql).

Let's pull a list of objects.

List titles = db.ExecuteList("select post_title from posts");

Console.WriteLine(titles[1]);
//> "I'm being watched"Complex ObjectsWe like type-safety in C#, so let's make a class to hold our query results:

class Blog
{
public int BlogID { get; set; }
public string BlogName { get; set; }
}

List rants = db.ExecuteList("select * from blogs where blog_name ~~ ?", "%Rants%");

Console.WriteLine(rants[0].BlogName);
//> "Mostly Rants"No configuration required, just write your class and feed it to PGMapper.

Object MappingLet's take it one step further and tightly bind our Blog class to the blogs table using some attributes:

[Table("blogs")]
public class Blog : Mapped, IReference
{
[Column("blog_id", IsIdentity = true)]
public virtual int ID { get; set; }
[Column("blog_name")]
public virtual string Name { get; set; }
}Now PGMapper knows exactly how to interact with the blogs table in the database. You can use the familiar ActiveRecord pattern now to do things like an ORM:

// Lookup an existing blog by its IsIdentity column
Blog blog = Blog.Find(1);

Console.WriteLine(blog.Name);
//> "Deep Thoughts"

// Find all blogs using just a WHERE clause
List rants = Blog.FindAllWhere("blog_name ~~ ?", "%Rants%");

Console.WriteLine(rants[0].Name);
//> "Mostly Rants"

// Create a new blog
Blog coding = Blog.Create();
coding.Name = "Adventures in Coding";
coding.Save(); // it's in the database now…

// Delete it
coding.Delete(); // …not anymoreThere are lots of static method overloads in Mapper<> to help you do things more efficiently.

ReferencesLet's make a corresponding Post class for our posts table:

[Table("posts")]
public class Post : Mapped
{
[Column("post_id", IsIdentity = true)]
public virtual int ID { get; set; }
[Column("blog_id")]
public virtual Reference Blog { get; set; }
[Column("post_title")]
public virtual string Title { get; set; }
[Column("post_body")]
public virtual string Body { get; set; }
}Notice the Reference type. This lets you take a Post object and quickly lookup its associated Blog:

Post creepy = Post.FindWhere("post_body ~~ ?", "%creepy%");

Console.WriteLine(creepy.Blog);
//> ""

// this goes out to the database and fetches the Blog object
Blog blog = creepy.Blog.Find();

Console.WriteLine(blog.Name);
//> "Deep Thoughts"Change TrackingLet's say you want to make a small change to an existing object. We'll turn on SQL statement debugging to see what PGMapper does:

Post flowers = Post.Find(1);
flowers.Title = "I know what flowers are now!";

db.PrintSqlToConsole = true;

flowers.Save();
//> "update posts set post_title = 'I know what flowers are now!' where post_id = 1"PGMapper knew that the only property you changed was the Title, so it only updated the changed property.

How did PGMapper know? You may have noticed that we're not creating Blog/Post objects using the new operator, instead we're using only static methods on the Mapped<> base class. This is because those methods are actually giving you a dynamically-compiled subclass of Blog/Post that overrides your virtual properties and listens for changes.

Schema MigrationWe'd like to add a new property to Post called DatePosted. Which means we'll need a new column in the database. Let's start by adding the property to our class:

[Table("posts")]
public class Post : Mapped
{

[Column("date_posted")]
public virtual DateTime DatePosted { get; set; }
}Traditionally, you would now have to write a migration script of some kind such that this column is added both in your local development database, and the production database on your web server when you're ready to publish.

What a pain. Instead, since PGMapper knows about your classes, and knows how to query metadata from Postgres, why not let PGMapper do the work for you:

db.MigrateFromClasses();
//> "PGMapper: Adding column posts.date_posted::DateTime"
//> "alter table posts add column date_posted timestamp without time zone"

// Now you can go ahead and use it:
Post flowers = Post.Find(1);
flowers.DatePosted = DateTime.Now;
flowers.Save();

//> "update posts set date_posted = '7/29/2009 5:38:11 PM' where post_id = 1"PGMapper currently knows how to deal with adding/removing tables, columns, and fiddling with some basic column constraints like primary keys, foreign keys, unique, and serial.

GoalsClean code.

No "magic!" No pulling giant trees of data and caching them in memory behind your back. No "protecting" you from SQL with leaky abstractions. No runtime translation of LINQ queries into generic and complex SQL that's hard to debug.

Productivity. Make it easy to "play" with your data model by making changes in only one place: your typesafe compiled C# code.

Share

In a Nutshell, pgmapper...

Languages

XML
67%
C#
33%
 
 

Lines of Code

 

Activity

30 Day Summary

Jun 8 2014 — Jul 8 2014

12 Month Summary

Jul 8 2013 — Jul 8 2014

Community

Ratings

Be the first to rate this project
 
Click to add your rating
 
Review this Project!