Executing SQL queries on SQL CE 4 through Entity Framework
In my previous blog, I wrote about how to use inheritance through entity framework on SQL CE database. In this blog however, I’ll explore how you can run a typical SQL query statement on the database table and read/write the values. I will build on the application that I used in the last article.
Let’s start by running an insert statement on our Person table.
1: public ActionResult Create2()
2: {
3: using (PersonEntities context = new PersonEntities())
4: {
5: const string sql =
6: @"insert into Person (Name, IsStudent, UniversityName, CompanyName)
7: values (@Name, @IsStudent, @UniversityName, @CompanyName)";
8: List<SqlCeParameter> args = new List<SqlCeParameter>
9: {
10: new SqlCeParameter {ParameterName = "Name", Value = "Madan Kumar"},
11: new SqlCeParameter {ParameterName = "IsStudent", Value = 1},
12: new SqlCeParameter {ParameterName = "UniversityName", Value = "Foo Bar University"},
13: new SqlCeParameter {ParameterName = "CompanyName", Value = DBNull.Value},
14: };
15: int rowCount = context.ExecuteStoreCommand(sql, args.ToArray());
16:
17: args = new List<SqlCeParameter>
18: {
19: new SqlCeParameter {ParameterName = "Name", Value = "Kiran Kumar"},
20: new SqlCeParameter {ParameterName = "IsStudent", Value = 0},
21: new SqlCeParameter {ParameterName = "UniversityName", Value = DBNull.Value},
22: new SqlCeParameter {ParameterName = "CompanyName", Value = "Foo Bar Inc"},
23: };
24: rowCount = context.ExecuteStoreCommand(sql, args.ToArray());
25:
26: return RedirectToAction("Index2");
27: }
28: }
Line 5 is where you see the parameterized query for the insert. Before I talk about the query parameters declared on line 8, I’d like to jump to line 15 where the insert statement will get executed. Normally we would pass an array of System.Data.SqlClient.SqlParameter to this statement. But since we’re using a SQL CE database, we need to switch to using System.Data.SqlServerCe.SqlCeParameter. Failing to do this will end up in a runtime error:
“InvalidCastException was unhandled by user code - The SqlCeParameterCollection only accepts non-null SqlCeParameter type objects, not SqlParameter objects.”
Now coming back to line 8 of the code snippet, we have a collection of SqlCeParameter objects which sets the values for the parameters given in the insert statement. One thing to note here is that when I had to pass a null to CompanyName parameter, I need to set it to DBNull.Value (line 13). The ExecuteStoreCommand returns the number of rows that were affected – just 1 in our case.
A quick look at the Person table in the database shows the two new records that got added.
Look at that! Once the two records are added, the application gets redirected to the Index2 action method. This method reads the records from the database - using SQL Select statements and then displays them on the browser.
1: public ActionResult Index2()
2: {
3: List<Person> people = new List<Person>();
4:
5: using (var context = new PersonEntities())
6: {
7: const string sql = "select * from Person where IsStudent = @IsStudent";
8: List<SqlCeParameter> args = new List<SqlCeParameter>
9: {
10: new SqlCeParameter {ParameterName = "IsStudent", Value = 1},
11: };
12:
13: ObjectResult<Student> students = context.ExecuteStoreQuery<Student>(sql, args.ToArray());
14: // if you don't have any parameters you can run the below query the fetch the results
15: //ObjectResult<Student> students = context.ExecuteStoreQuery<Student>(sql, null, null, null);
16: people.AddRange(students);
17:
18: args.Clear();
19: args.Add(new SqlCeParameter { ParameterName = "IsStudent", Value = 0 });
20:
21: ObjectResult<Employee> employees = context.ExecuteStoreQuery<Employee>(sql, args.ToArray());
22: people.AddRange(employees);
23: }
24: return View("Index", people);
25: }
Nothing new in the select statement there and we continue to use the SqlCeParameter to set the values for the parameters. The change this time is that we use ExecuteStoreQuery instead. I first read all the Student records and then the Employee records from the database table.
The view itself remains unchanged from the previous demo application, but now we have more data, so the output will get rendered as:
Source code for this post can be seen here.
Verdict: SQL CE – give it a shot. It’s worth it!