Introduction In this last post I want to show some more complex queries. But I also want to remind you that you need to know when to quit. When queries get to complex you will have to jump through hoops to get them working with any ORM while it is all very “easy” to do in SQL. In other words use the right tool for the job. Setup Here is the code to set everything up.

In the prior PetaPoco post, I started to dig into many-to-one relationships a little. Chrissie followed up with yet more mapping behavior in his latest Simple.Data post, so I thought I would cover it in a bit more detail. Note: Chrissie has also covered one-to-many since I wrote this post the other night and has at least one more post following that Many flavors of Mapping Related Objects PetaPoco doesn’t offer the instrumentation for lazy loading, though it wouldn’t be too hard to add it to the T4 template that is provided to automatically generate POCOs from the database.

Introduction In my previous post I described how to deal with complex types and many to one dependencies now I will do the one to many relationships. For this I will add a collection to our Person. ```vbnet Public Class Person Public Property LastName As String Public Property FirstName As String Public Property Address As Address Public Property BadHabits As IList(Of BadHabit) End Class Public Class Address Public Property Street As String Public Property HouseNumber As String End Class Public Class BadHabit Public Property BadHabit As String End Class``` one to many For this we first have to add a table to our database.

Introduction After doing the blogposts about One to many and many to one it is now time to do one one about many to many with Simple.Data. Many to many It’s time to change our design. It is clear that our data is not normalized like it should. I need an intermediate table between Person and BadHabit. Here is the code to do that. Private Sub CreateTables() Using cn = New SqlCeConnection(ConnectionString) If cn.

Introduction In my previous blogpost about Simple.Data I added a table and made a reference to that in my previous table. In other words A person now has an address. I then went on to make a query to join the two together hereby using the explicit join syntax of Simple.Data. If however your database uses foreign keys (which I know it will) then you have no need for a join.

In yesterday’s post A couple of things to be aware of when working with tables in SQL Azure I showed you some things to be aware of in regards to tables. Today let’s talk about how to rebuild an index Let’s say you have a table with a clustered index on a uniqueidentifier column, you know that you have to use newid() instead of newsequentialid() since newsequentialid() is not supported. Clustering with newid() is not recommended because you will get page splits.

I was messing around with SQL Azure today and noticed a couple of things that are not supported compared to the regular version of SQL Server in regards to tables. I will list these in this post. All the code has been tested against the following version of SQL Azure as returned by @@version Microsoft SQL Azure (RTM) – 11.0.1892.4 Apr 24 2012 10:21:54 Copyright © Microsoft Corporation 1) Tables have to have a clustered index if you want to insert data into those tables There is actually no problem creating a table without a clustered index (heap)

You can search for data in your tables by using the LIKE operator. The LIKE operator determines whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters. Here is what Books On Line has on the use of wild cards Wildcard character Description Example % Any string of zero or more characters.

So yesterday Chrissie and I did posts on Simple.Data and PetaPoco. Today he followed up with more complex examples, including keys and multiple table queries. PetaPoco is built specifically with primary keys as a first class citizen, so it will be interesting to see how it compares. Adding a column Like Simple.Data, adding a column to our database table is no problem at all. First lets add the column to our database, then we’ll look at how that affects both our existing code and an updated POCO with a matching field.

Introduction So yesterday I did a post about Simple.Data and Eli did a post about Petapoco based on what I did. And Eli commented that I did not have a primary key in my table. In this post I will add that primary key and another table. Adding a column Adding a column should never be a big problem. But with Simple.Data it’s just no pain what so ever. Most ORM’s will require you to add a property to your model and then something to the mapping file.