Rails-like Model Base Class for C# .NET Using an ODBC Connection

• Download source - 4.52 KB

Introduction

Rails is one of the fastest rising players in the apps game for good reason - it's fast, reliable, and easy to figure out. Unfortunately, its partner, Ruby, isn't as friendly. Most of us who grew up in a world of C-like languages will find it a little difficult to make the shift to a Basic-like language. But, because of all the perks that come along with it, we cave, and decide to invest time and effort into learning something almost totally alien to us.

The idea of a Rails-like MVC framework for C-like languages is nothing new. PHP has several options available - CakePHP, Symfony, and more. For C# developers, finding a solution is a little more challenging, so here's my attempt to provide one. Although it's not a full MVC framework, it has the gist of functions available to model classes in Rails.

Background

The idea behind the model class in Rails is to automatically map out columns in a database table to members of a class. Accessing and manipulating data from the table is made as easy as retrieving and setting the values of class members. The DDSModel base class provides some of these functionalities via simplified function calls and the indexer feature of C#.

This code may not be applicable for large-scale database tables. Also, it does not currently support compound primary keys, and, unlike Rails, does not provide functionalities for dealing with relationships.

Using the code

To make DDSModel useful, you must first create another class that inherits from it. The bare minimum that you'll need to do with this new class to get it to work is to set the name of the table to associate with it and the primary key for this table. Do this by passing the string values to the base constructor.

class MyDataClass : DDSModel
{
    public MyDataClass ()
        : base("my_table_name", "my_primary_key")
    {

    }
}

At this point, you may also decide to add more methods to your class for special functionalities.

To create a new row in your table, instantiate a new object from the class you created, and set the values of one or more columns in your table. Finally, call the save() function to finalize your new row and actually insert it into the database.

MyDataClass data = new MyDataClass(); 
data["column_1"] = "1";
data["column_2"] = "Hello World";
data.save();

Note that the indexer accepts string values, so casting/parsing is necessary if you intend to save from an integer value. Also, for columns that are not set, "NULL" is automatically passed.

To retrieve the value of a row, use either of the following methods:

MyDataClass data = new MyDataClass(); 

// Retrieving a specific record
data.find( 2 ); // Retrieves the record with the primary key value 2
int val = int.Parse( data["column_1"] );

// Retrieving a set of records based on a particular column's values
data.findBy( "name", "foobar", "", "", 0, 0 );
data.iterate(); // Move cursor to the first row
val = int.Parse( data["column_1"] );

// Retrieving a set of records matching an SQL condition
data.findAll( "name = 'foobar'", "", "", 0, 0 ); // Use exact SQL condition syntax
data.iterate(); // Move cursor to the first row
val = int.Parse( data["column_1"] );

The following describes the other parameters of the findBy and findAll functions, starting at parameter 3 for findBy and parameter 2 for findAll.

The iterate() function is used to move the cursor to the next record. It returns false if there are no more rows in the result set. Use this method if you want to display or process all records in the result set.

Updating a record is simply a combination of retrieving and inserting a record. First, retrieve the record you wish to update (using the previously mentioned methods). Then, set a value for each field you want to change. Finally, execute save() to update the row to the database.

Finally, to delete a row, retrieve the record you wish to delete using any of the methods mentioned above. Then, simply execute delete() to remove it from the database.

If your needs are not fulfilled by any of the provided functions, you can also execute query(), passing a SQL statement. This function will return an OdbcDataReader containing the result set for your query.

Points of interest

Although I would have wanted to completely emulate how Rails handles database tables, I figured that it was just impossible, what with all the "magic" going on behind Rails that I couldn't figure out in the month or so that I was able to use it. When I have more time to myself, I'll look into the Rails code and try to figure it out (again). But, for now, I suppose this should suffice. I hope someone finds this useful!

History

• Version 0.1 - Not completely tested yet. Only tested on an "in-development" project. I'd appreciate feedback on this first version.