Getting Started with Database.NET

Find working examples on GetHub


Database.NET is patterned after Entity Framework. It uses DbContexts, DbSets and POCO classes just like entity does. The implementation is a little different but a project starts with creating a class that inherits from DbContext.

public class ShopContext : DbContext
{
    public ShopContext(string filePath)
    : base(filePath, TextEncodingType.ASCII)
    {
    }
}
The DbContext can also be passed a read/writable stream if you want to embed your database into another file or keep it in memory.
Besides the file path or stream the DbContext requires that you select a TextEncodingType. This is so that if you need to use languages or characters other than those in standard English you will not be limited. Some encoding types perform better than others and it is best to pick one that has a consistant number of bytes per character.

Once the context is created you will need a POCO class. Again this is patterned after Entity Framework using thier code first methodology.

public class Item
{
    [Key]
    [AutoGenerated]
    public int ItemId { get; set; }

    [Size(50)]
    public string Name { get; set; }

    public decimal Price { get; set; }
}

The most important part of the POCO class is the property that has the Key attribute. This defines the primary key that will be used to retrieve rows from the table.

Multiple keys can be used in your POCO class. They can also be used on many types of variables. This will be coverd more in another section.

You will also notice that the Name property has a Size attribute. This is not required, however if it is not supplied the string will be stored in a BLOB which is slower than if it is stored with the rest of the row. The size property can be used on byte arrays and strings. The AutoGenerated attribute is also applied to the ItemId property. This tells the database to generate the key value on creation. If the attribute is left off the database will expect you to provide your own unique value for this property. After the POCO class is created you need to add it to the DbContext class as seen below.

        
public class ShopContext : DbContext
{
    public ShopContext(string filePath)
        : base(filePath, TextEncodingType.ASCII)
    {
    }

    public DbSet<Item> Items { get; set; }
}
        
    

Now if you run your code and instantiate the ShopContext it will create the Items table. Adding rows to your table is again a little different from how Entity Framework does it. When instantiating a class your POCO class, use the CreateProxy method as seen below.

        
using (var shopContext = new ShopContext("c:\\Test\\test.db"))
{
    var item = shopContext.Items.CreateProxy();
    item.Name = "Pillow";
    item.Price = 45;

    shopContext.Items.Add(item);

    shopContext.SaveChanges();
}
        
    
The CreateProxy method may not be needed in future versions, but for now it was the best way to get the behavior we wanted.

After the proxy version of the item is created you assign values to your properties and then add it to the Items DbSet in the same way as in Entity Framework. Calling the save function on the shopContext stores the item in the database and closes the transaction. Retrieving rows from the database can be done using a standard Where, First or FirstOfDefault statement. If you want all rows you can simply call to List on the Items propery of the shopContext.

The example below does a full table scan because the Price property is not indexed;

        
using (var shopContext = new ShopContext("c:\\Test\\test.db"))
{
    //Does a full table scan looking looking for items wwith a price of 45 
    var items = shopContext.Items.Where(i => i.Price == 45); 
}
                
    

The next example will show how to retrieve an item using an index

        
using (var shopContext = new ShopContext("c:\\Test\\test.db"))
{
    //Uses the table index to find the item with the matching ItemId value
    var item = shopContext.Items.First(i => i.ItemId == 1); 
}
                
    
You can have more than one index on a table. This will be covered in another section.

To update values you first need to retrieve the row(s) you want to change. Update the properties and call the SaveChanges method. All changes will be committed as one transaction. Any new changes made after the SaveChanges method is called will be part of a new transaction.

        
using (var shopContext = new ShopContext("c:\\Test\\test.db"))
{
    var items = shopContext.Items.First(i => i.ItemId == 1);
    item.Price = 50;
    shopContext.SaveChanges();
}
                
    

To delete a row first you must retrieve it from the database, then remove it from the table. Look at the example below to see how this is done.

        
using (var shopContext = new ShopContext("c:\\Test\\test.db"))
{
    var items = shopContext.Items.First(i => i.ItemId == 1);
    shopContext.Items.Remove(item);
    shopContext.SaveChanges();
}
                
    

This should get you started with your first Database.NET database. Please read the other sections for additional instructions.