Some Features:
- Supports a wide variety of RDBMs - MS SQL Server, MS SQL Azure, Oracle, MySQL, SQLite, etc.
- Auto map, or use custom mappers, for your POCOs (or dynamic type).
- Use SQL, or SQL builders, to insert, update, or delete data.
- Supports stored procedures.
- Uses indexed or named parameters.
- Supports paging.
- Available as assembly (download the DLL or use NuGET) and as a single source code file.
- Supports transactions, multiple resultsets, custom return collections, etc.
Pros:
- Setting up connection strings in a config file, and then passing the key value to a DbContext to establish a connection is such an easy way to do things. It made it very easy to have generic code point to various databases. I'm sure that is the intent. Needing to declare a connection object, set the connection string value for the connection object, and then calling the connection object's "Open" method seems undignified. :D It's really not that big of a deal, but I like that it seemed much more straight forward using FluentData.
- It's very easy to start using FluentData to select, add, update, or delete data from your database.
- It is easy to use stored procedures.
- Populating objects from selects, or creating objects and using them to insert new data into your database is almost seamless.
- Populating more complex objects from selects is fairly easy using custom mapper methods.
- The exceptions that are thrown by FluentData are actually helpful. The contributors to/creators of FluentData have been very thoughtful in how they return error information.
Cons:
- I had some slight difficulty setting a parameter for a SQL select when the parameter was used as part of a "like" for a varchar column. The string value in the SQL looked like this: '@DbName%'. I worked around the issue by changing the code to use this instead: '@DbName', and then set the value so that it included the %.
I originally thought that I couldn't automap when the resultsets return columns that don't map to properties of the objects (or are missing columns for properties in the target object) without using a custom mapping method. However, there is a way - you can call a method on the DB context to say that automapping failures should be ignored:
Important configurations
- IgnoreIfAutoMapFails - Calling this prevents automapper from throwing an exception if a column cannot be mapped to a corresponding property due to a name mismatch.
First, I created a MySQL database to use as a test. I created a database called ormtest, and then created a couple of tables for holding book information:
create table if not exists `authors` ( `authorid` int not null auto_increment, `firstname` varchar(100) not null, `middlename` varchar(100), `lastname` varchar(100) not null, primary key (`authorid` asc) ); create table if not exists `books` ( `bookid` int not null auto_increment, `title` varchar(200) not null, `authorid` int, `isbn` varchar(30), primary key (`bookid` asc) );
Next, I created a Visual Studio console app, added an application configuration file, and added a connection string for my database:
Then I created my entity types:
public class Author
{
public int AuthorID { get; set; }
public string FirstName { get; set; }
public string MiddleName { get; set; }
public string LastName { get; set; }
public string ToString()
{
if (string.IsNullOrEmpty(MiddleName))
{
return string.Format("{0} - {1} {2}", AuthorID, FirstName, LastName);
}
return string.Format("{0} - {1} {2} {3}", AuthorID, FirstName, MiddleName, LastName);
}
}
public class Book
{
public int BookID { get; set; }
public string Title { get; set; }
public string ISBN { get; set; }
public Author Author { get; set; }
public string ToString()
{
if (Author != null)
{
return string.Format("{0} - {1} \n\t({2} - {3})", BookID, Title, ISBN, Author.ToString());
}
return string.Format("{0} - {1} \n\t({2})", BookID, Title, ISBN);
}
}
I was then able to populate a list of books by selecting rows from the books table:
public static void PrintBooks()
{
IDbContext dbcontext = new DbContext().ConnectionStringName("mysql-inventory", new MySqlProvider());
const string sql = @"select b.bookid, b.title, b.isbn
from books as b;";
List<Book> books = dbcontext.Sql(sql).QueryMany<Book>();
Console.WriteLine("Books");
Console.WriteLine("------------------");
foreach (Book book in books)
{
Console.WriteLine(book.ToString());
}
}
Unfortunately I wasn't able to select columns from the table that didn't have matching attributes in the entity type. You'll need to create a custom mapping method in order to select extra columns that don't map to any attributes in the entity type. You can also use custom mapping methods to populate entity types that contain attributes of other entity types).
Here is an example:
public static void PrintBooksWithAuthors()
{
IDbContext dbcontext = new DbContext().ConnectionStringName("mysql-inventory", new MySqlProvider());
const string sql = @"select b.bookid, b.title, b.isbn, b.authorid, a.firstname, a.middlename, a.lastname, a.authorid
from authors as a
inner join books as b
on b.authorid = a.authorid
order by b.title asc, a.lastname asc;";
var books = new List<Book>();
dbcontext.Sql(sql).QueryComplexMany<Book>(books, MapComplexBook);
Console.WriteLine("Books with Authors");
Console.WriteLine("------------------");
foreach (Book book in books)
{
Console.WriteLine(book.ToString());
}
}
private static void MapComplexBook(IList<Book> books, IDataReader reader)
{
var book = new Book
{
BookID = reader.GetInt32("BookID"),
Title = reader.GetString("Title"),
ISBN = reader.GetString("ISBN"),
Author = new Author
{
AuthorID = reader.GetInt32("AuthorID"),
FirstName = reader.GetString("FirstName"),
MiddleName = reader.GetString("MiddleName"),
LastName = reader.GetString("LastName")
}
};
books.Add(book);
}
And here is an example of an insert, update, and delete:
public static void InsertBook(string title, string ISBN)
{
IDbContext dbcontext = new DbContext().ConnectionStringName("mysql-inventory", new MySqlProvider());
Book book = new Book
{
Title = title,
ISBN = ISBN
};
book.BookID = dbcontext.Insert("books")
.Column("Title", book.Title)
.Column("ISBN", book.ISBN)
.ExecuteReturnLastId<int>();
Console.WriteLine("Book ID : {0}", book.BookID);
}
public static void UpdateBook(Book book)
{
IDbContext dbcontext = new DbContext().ConnectionStringName("mysql-inventory", new MySqlProvider());
book.Title = string.Format("new - {0}", book.Title);
int rowsAffected = dbcontext.Update("books")
.Column("Title", book.Title)
.Where("BookId", book.BookID)
.Execute();
Console.WriteLine("{0} rows updated.", rowsAffected);
}
public static void DeleteBook(Book book)
{
IDbContext dbcontext = new DbContext().ConnectionStringName("mysql-inventory", new MySqlProvider());
int rowsAffected = dbcontext.Delete("books")
.Where("BookId", book.BookID)
.Execute();
Console.WriteLine("{0} rows deleted.", rowsAffected);
}
Summary:
FluentData has been fairly easy to use and there appears to be a way to accomplish whatever I want to do. If FluentData's documentation had more examples of how to populate entity types (POCOs), then it would have saved me a little bit of time. As it is, the documentation listed multiple ways to accomplish tasks, so it never took long to find a method that would work.
No comments:
Post a Comment