Arthur Olcot has a great blog that covers SQL Server and focuses on optimization and SQLXML. He has a post that lists examples of using XML DML that I found to be incredibly useful. Specifically, the conditional insert example is something that I was able to borrow from for a task at work.
I needed to add some elements to XML in a bunch of rows of data, and I only wanted to add the elements if they didn't already exist. I was afraid that I might need to do a delete, and then an insert, to make sure that I would end up with duplicate values. Thankfully you can do conditional inserts, and his example made it very easy to understand how to use the conditional.
The following is an example of how to use the conditional insert.
DECLARE @xml XML
SET @xml = 'Cat 1Cat 2Cat 3'
SELECT 'before where Cat 3 already exists', @xml
SET @xml.modify('
insert (
if (//category[contains(., "Cat 3")]) then ()
else element category{"Cat 3"}
) as last into (categories[1])
')
SELECT 'after', @xml
SET @xml = 'Cat 1Cat 2'
SELECT 'before where Cat 3 does not exist', @xml
SET @xml.modify('
insert (
if (//category[contains(., "Cat 3")]) then ()
else element category{"Cat 3"}
) as last into (categories[1])
')
SELECT 'after', @xml
Line Endings Boy how I wish there weren't a difference between Unix and Windows line endings. I recently needed to search through a bunch of map reduce output (tons of reduce files in text format), so I decided to bulk insert them into a SQL database table. I wrote a bulk insert statement for each of the reduce files (well, some SQL code wrote the bulk insert statements based on a list of file names), and I quickly encountered errors when attempting to execute the statements. The bulk insert statements looked something like this:
The reduce files are rows of data that are tab separated. The frustrating part is that I had failed to remember that the map reduce job was done on a Linux machine, and therefore each file contained Unix style line endings. I had been viewing the files in Notepad++ (which will display different file formats without complaint), so it was confusing to me when I would see errors. I changed the ROWTERMINATOR value to '\r\n', and everything worked fine. Lesson - pay attention to the source system type when processing data files. I didn't spend a long time on this, but it was still frustrating. SQL Views I had created a view to insert into, because I had an identity column on my target table. It seemed like an easy way to avoid the problem of bulk insert trying to insert into the identity column. The problem occurred when I ran the SQL to do the inserts, and some of the columns from the source files had values that were too wide for the table columns. I changed the target table columns to use varchar(max), since some of the source data had values that were larger than 4000 chars. I ran the bulk insert again and I still had errors due to the data being too large for one of the target columns. What the heck? What I hadn't realized is that the view I created had information for the columns in the target table from when the target table was first created. The table had the column width changed to varchar(max), but the view thought the table column width was varchar(4000). I ran the SQL to recreate the view and then the bulk insert worked just fine.
Who doesn't love tools that make your life easier? Make a database connection and populate an object in just a few lines of code and one config setting? That's what FluentData can offer. Sign me up! 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.
Example Usage:
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.
