XML FeedsIntroduction
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.
- Private Sub InsertTestData(ByVal db As Object)
- db.Address.Insert(New With {.Street = "street1", .HouseNumber = "1"})
- db.Address.Insert(New With {.Street = "street2", .HouseNumber = "2"})
- db.Person.Insert(New With {.LastName = "lastname1", .FirstName = "firstname1", .AddressId = 1})
- db.Person.Insert(New With {.LastName = "lastname1", .FirstName = "firstname2", .AddressId = 1})
- db.Person.Insert(New With {.LastName = "lastname2", .FirstName = "firstname3", .AddressId = 2})
- db.BadHabit.Insert(New With {.BadHabit = "Drinks"})
- db.BadHabit.Insert(New With {.BadHabit = "Smokes"})
- db.BadHabit.Insert(New With {.BadHabit = "Eats to much"})
- db.BadHabitPerson.Insert(New With {.BadHabitId = 1, .PersonId = 1})
- db.BadHabitPerson.Insert(New With {.BadHabitId = 2, .PersonId = 1})
- db.BadHabitPerson.Insert(New With {.BadHabitId = 3, .PersonId = 2})
- db.BadHabitPerson.Insert(New With {.BadHabitId = 3, .PersonId = 1})
- db.BadHabitPerson.Insert(New With {.BadHabitId = 3, .PersonId = 3})
- End Sub
- Public Function ConnectionString() As String
- Return "DataSource=""test.sdf""; Password=""mypassword"""
- End Function
- Private Function CreateDatabase() As SqlCeEngine
- If File.Exists("test.sdf") Then File.Delete("test.sdf")
- Dim en = New SqlCeEngine(ConnectionString)
- en.CreateDatabase()
- Return en
- End Function
- Private Sub CreateTables()
- Using cn = New SqlCeConnection(ConnectionString)
- If cn.State = ConnectionState.Closed Then
- cn.Open()
- End If
- For Each SqlScript In CreateTableScripts()
- Using cmd = New SqlCeCommand(SqlScript, cn)
- cmd.ExecuteNonQuery()
- End Using
- Next
- End Using
- End Sub
- Private Function CreateTableScripts() As IList(Of String)
- Dim s As New List(Of String)
- s.Add("CREATE TABLE Address (Id int IDENTITY(1,1) PRIMARY KEY, Street nvarchar (40) NOT NULL, HouseNumber nvarchar (10))")
- s.Add("CREATE TABLE Person (Id int IDENTITY(1,1) PRIMARY KEY, LastName nvarchar (40) NOT NULL, FirstName nvarchar (40), AddressId int NOT NULL)")
- s.Add("ALTER TABLE Person ADD CONSTRAINT FK_Person_Address FOREIGN KEY (AddressId) REFERENCES Address(Id)")
- s.Add("CREATE TABLE BadHabit (Id int IDENTITY(1,1) PRIMARY KEY, BadHabit nvarchar (40) NOT NULL)")
- s.Add("CREATE TABLE BadHabitPerson (BadHabitId int NOT NULL, PersonId int NOT NULL)")
- s.Add("ALTER TABLE BadHabitPerson ADD PRIMARY KEY(BadHabitId, PersonId)")
- s.Add("ALTER TABLE BadHabitPerson ADD CONSTRAINT FK_BadHabitPerson_Person FOREIGN KEY (PersonId) REFERENCES Person(Id)")
- s.Add("ALTER TABLE BadHabitPerson ADD CONSTRAINT FK_BadHabitPerson_BadHabit FOREIGN KEY (BadHabitId) REFERENCES BadHabit(Id)")
- Return s
- End Function
queries
To get the person with the streetname "street1" you can should do this in C#
- db.Persons.FindAll(db.Persons.Address.Street == "street1");
The above however does not work in VB.Net.
But this does.
- db.Person.FindAll(db.Address.Street = "street1").WithAddress
- select [Person].[Id],[Person].[LastName],[Person].[FirstName],[Person].[AddressId],[Address].[Id] AS [__with1__Address__Id],[Address].[Street] AS [__with1__Address__Street],[Address].[HouseNumber] AS [__with1__Address__HouseNumber] from [Person] LEFT JOIN [Address] ON ([Address].[Id] = [Person].[AddressId]) WHERE [Address].[Street] = @p1
- @p1 (String) = street1
The above works but just because I use WithAddress and setup referential integrity.
I can also use like. Like this.
- db.Person.FindAll(db.Address.Street.Like("street%")).WithAddress
- select [Person].[Id],[Person].[LastName],[Person].[FirstName],[Person].[AddressId],[Address].[Id] AS [__with1__Address__Id],[Address].[Street] AS [__with1__Address__Street],[Address].[HouseNumber] AS [__with1__Address__HouseNumber] from [Person] LEFT JOIN [Address] ON ([Address].[Id] = [Person].[AddressId]) WHERE [Address].[Street] LIKE @p1
- @p1 (String) = street%
I can also select which columns I want to return.
- db.Person.All.Select(db.Person.LastName)
- select [Person].[LastName] from [Person]
I can also give my columns aliases.
- db.Person.All.Select(db.Person.LastName.As("Name"))
- select [Person].[LastName] AS [Name] from [Person]
And you can do much more.
But I think I have learned enough about Simple.Data now. And it's time to move on to something else.
Conclusion
I really like Simple.Data and I will surely use it again in the future. But if you want to keep your sanity I would recommend using C# and not VB.Net. The whole dynamic thing is very quirky in VB.Net.
You should however know the limits of the framework you are using and keep in mind that some things are just better solved another way. It will give you less headaches by trying to fit a square peg in a round hole and it will make for a more enjoyable experience all around. I see way to many people trying to use whatever they have to do whatever they need to do and then hit a brick wall. So stop doing that and use Simple.Data for what it was supposed to do quick and easy CRUD.
Chris is awesome.
I meant to ask, can Simple.Data do partial results? Like if I just wanted a few fields from a given table or JOIN instead of all the fields?
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.