Now that SQL Server Compact 3.5 SP1 Beta is released it's time to take the first step, making your first entity application using SQLCE as a backend.
In his previous post, Ambrish gave steps on how to install SQL Server Compact SP1 Beta, along with Ado.Net Entity Framework Beta 3 and writing a small program to query the Northwind Database via LINQ.
In this post, I will briefly walk you through what Entity Framework Beta 3 has offer to, and how can we leverage it to write different types of entity queries against SQL Server Compact.
Going by EF Definition at: http://msdn2.microsoft.com/en-us/library/bb399572.aspx
The most unique selling point in my opinion becomes the part where you can run the same program/application against any backend (assuming the database schema is same), you have to just modify connection strings. Let's say even if the schema is little different, you can get that easily fixed in your metadata files, and boom, your app works.
I am including few screenshots which will guide you on how to go by making your own application and the complete program.
Please do read the previous post (by Ambrish) carefully as I will assume that you are done with the setup and we are ready to go. For those who could get things set up I am posting some screen shots to help you with that.
First step is to include an Ado.Net Entity Data Model into your project, give it a name that makes sense, I gave it SSCNorthwind.
The screenshot below, shows the database file that we selected, its connection string that will go into your App.config file and also the name you want to give it. For simplicity I chose NorthwindEntities.
The screenshot below shows you how to include the database file that you placed in your project root for generating data model.
The wizard below allows you to choose the tables that you want to be part of entity set, as SQL CE does not support stored procedures and views as of now, we will ignore those two options.
The red box in below shows the change that we have to make in App.Config to make this working J i.e. renaming the provider:
provider=Microsoft.SqlServerCe.Client.3.5 to provider=System.Data.SqlServerCe.3.5
This is being fixed, so in RTM version you won't have to do this manual step.
You can see that the name of Entity set is NortwindEntities, so whenever we make connection to this set, the connection string provided in App.Config file will be used.
Before:
After:
This is how the solution explorer should look now:
If you face any error while adding Northwind.sdf file into your project, just ignore it for now.
I have tried to include some comments in my program below to help you understand what's going on.
Points to notice:
To connect to a Database using an Entity Connection, you can do it in two ways.
- First way is to specify your own connection string, like I have done at the start of program. Using that you can directly create an Entity Connection and work in the same way you used to work with SqlCeConnection and SqlCeCommand. That way can be seen in ESQL Query region.
- The second approach is using Contexts or what we call here NorthwindEntities, (remember at top we renamed Entities to this for better understanding). For using context, you can do that in multiple ways
- First, you can give a connection string and it will create the set.
- Second, you can also first create an Entity Connection like above and then pass it on the NorthwindEntities, which is what I have done in Object Query section.
- And the last approach for which we created App.Config in first place. You don't specify anything at all, the entity set (NorthwindEntities) picks up your connection string from App.Config and creates a connection. LINQ to Entites Query section is done using this approach.
Program (Contents of Program.cs):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.EntityClient;
using NorthwindModel;
using System.Data;
namespace MyFirstEntityApplicationForSSC
{
class Program
{
#region Static Declarations
//SQLCE Connection string for the sample database Northwind.sdf
static string _sqlCeConnnectionString = @"Data Source = Northwind.sdf";
//entityConnectionString: If your metadata(CSDL, SSDL and MSL, or in this case EDMX) files lie in the same place as application, you can use "."
static string _entityConnectionString =
String.Format(@"metadata=.;Provider=System.Data.SqlServerCe.3.5;provider connection string=""{0}""", _sqlCeConnnectionString);
//New Values that you might want to use for changing the data
static string _newFirstName = "AGENT";
static string _newLastName = "SMITH";
static string _newCountry = "MATRIX";
#endregion Static Declarations
#region Main
/// <summary>
/// MAIN: It calls the three different functions for following type of queries
/// Description in layman language
///
/// ESQL - This takes you back to old .Net 2.0 days where you used to set T-SQL statements and execute them, just that it here works on entities now instead of tables
/// Example Demonstrates: Simple Select Query using Entity SQL.
///
/// Object Query - Working on Data Objects was never so easy. This type of queries give you the power to work on data objects and most of all intellisense helps with things you can do
/// Example Demonstrates: Query and Updation of data and then Re-Querying
///
/// LINQ to Enity - LINQ means Language Integrated Query. This is a new and powerful way of writing queries that are checked for syntax at compile time itself. So, chances of failing in query logic at runtime become minimal
/// Example Demostrates: Query the data using default settings for connection in App.Config that was configured via EF Designer.
/// </summary>
/// <param name="args"></param>
static void Main(string[] args)
{
Console.WriteLine("SQL CE Northwind Entity App:");
try
{
EntityConnection entityConnection = new EntityConnection(_entityConnectionString); //Creating a new entity connection using the entity connection string
entityConnection.Open();
Console.WriteLine("\nESQL Query:");
ESQL_Query(entityConnection); //This does not use context, it's a simple esql command execution
Console.WriteLine("\nLINQ To Entity Query:");
LINQ_To_Entity_Query(); //This will make a new connection using the connection string in the App.Config file
Console.WriteLine("\nObject Query:");
ObjectQuery(entityConnection); //This will create the context with existing connection
entityConnection.Close();
}
catch (Exception ex)
{
Console.WriteLine("\nFAIL! Oops, it was not expected, an exception has been thrown, details below:\n");
Console.WriteLine(ex.Message);
}
Console.WriteLine("\nPress a Key to exit...");
Console.ReadKey();
}
#endregion Main
#region ESQL_Query
/// <summary>
/// ESQL_Query
/// (Executing a simple ESQL query against Northwind Entity Set)
/// Using the entity connection which is passed on as parameter
/// it executes a simple entity command on the Employees entity set in the Northwind Context.
/// The result of that query is later on printed on the screen using a data reader.
/// </summary>
/// <param name="entityConnection"></param>
private static void ESQL_Query(EntityConnection entityConnection)
{
EntityCommand entityCommand = entityConnection.CreateCommand();
entityCommand.CommandText = @"Select Emp.Employee_Id as Id, Emp.First_Name as Name from NorthwindEntities.Employees as Emp order by Emp.Employee_Id";
EntityDataReader entityDataReader = entityCommand.ExecuteReader(CommandBehavior.SequentialAccess);
//Note: You have to use CommandBehavior as SequentialAccess, otherwise an exception will be thrown
while (entityDataReader.Read())
{
for (int i = 0; i < entityDataReader.FieldCount; i++)
Console.Write(entityDataReader[i].ToString() + "\t");
Console.WriteLine();
}
}
#endregion ESQL_Query
#region LINQ_To_Entity_Query
/// <summary>
/// LINQ_To_Entity_Query
/// (Executing a simple LINQ query against Northwind Entity Set)
/// This function creates a new Northwind Context based on the setting provided for it in the App.Config File
/// It executes a simple LINQ query on the Employees entity set in the Northwind Context.
/// The result of that query is later on printed on the screen using the IQueryable structure.
/// </summary>
private static void LINQ_To_Entity_Query()
{
NorthwindEntities nwind = new NorthwindEntities(); //Uses the settings for connection string in App.Config File
IQueryable<string> emps = from e in nwind.Employees where (e.Employee_ID%2)==0 select e.First_Name; //Even Number Employee Ids
foreach (string e in emps)
{
Console.WriteLine(e);
}
}
#endregion LINQ_To_Entity_Query
#region ObjectQuery
/// <summary>
/// ObjectQuery
/// (Executing a simple Object query against Northwind Entity Set and Updating the data)
/// This function creates a new Northwind Context based on the entity connection thas has been passed to it as parameter
/// It executes a simple Object query on the Employees entity set in the Northwind Context.
/// The result of that query is later on printed on the screen and some changes are made to it.
/// The changes are then saved using SaveChanges on Context and then re-querying them to see if things went fine.
/// </summary>
/// <param name="entityConnection"></param>
private static void ObjectQuery(EntityConnection entityConnection)
{
NorthwindEntities nwind = new NorthwindEntities(entityConnection);
var emps = nwind.Employees.Where((delegate(Employees e)
{
return e.Employee_ID > 5;
}
));
//var emps = nwind.Employees.Where("it.Employee_ID > 5"); //This query is also equivalent to above one and will give same results
//var emps = nwind.Employees.Where("it.Employee_ID > @empid",new ObjectParameter("@empid",5)); //This query is also equivalent to above one, but it uses parameters for the values
foreach(Employees e in emps)
{
Console.WriteLine(e.Employee_ID + "\t" + e.First_Name + "\t" + e.Last_Name + "\t" + e.Country);
if (e.Employee_ID == 10) //As soon as we encounter employee id as 10, we want to change some data
{
Console.WriteLine("Changing Data");
e.First_Name = _newFirstName;
e.Last_Name = _newLastName;
e.Country = _newCountry;
}
}
Console.WriteLine("Saving Data Changes");
nwind.SaveChanges(); //Any changes made to the context are saved using this command
emps = nwind.Employees; //This is simplest way to select all employees. If you happen to choose particular data you may modify the query accordingly, like nwind.Employees.Where("it.Employee_ID = 10")
Console.WriteLine("Display Data Again:");
foreach (Employees e in emps)
{
Console.WriteLine(e.Employee_ID + "\t" + e.First_Name + "\t" + e.Last_Name + "\t" + e.Country);
}
}
#endregion ObjectQuery
}
}
The output of the program would look like this:
SQL CE Northwind Entity App:
ESQL Query:
1 Nancy
2 Andrew
3 Janet
4 Margaret
5 Steven
6 Michael
7 Robert
8 Laura
9 Anne
10 Albert
11 Tim
12 Caroline
13 Justin
14 Xavier
15 Laurent
LINQ To Entity Query:
Andrew
Margaret
Michael
Laura
Albert
Caroline
Xavier
Object Query:
6 Michael Suyama UK
7 Robert King UK
8 Laura Callahan USA
9 Anne Dodsworth UK
10 Albert Hellstern USA
Changing Data
11 Tim Smith USA
12 Caroline Patterson USA
13 Justin Brid France
14 Xavier Martin France
15 Laurent Pereira France
Saving Data Changes
Display Data Again:
1 Nancy Davolio USA
2 Andrew Fuller USA
3 Janet Leverling USA
4 Margaret Peacock USA
5 Steven Buchanan UK
6 Michael Suyama UK
7 Robert King UK
8 Laura Callahan USA
9 Anne Dodsworth UK
10 AGENT SMITH MATRIX
11 Tim Smith USA
12 Caroline Patterson USA
13 Justin Brid France
14 Xavier Martin France
15 Laurent Pereira France
Press a Key to exit...
If you want your changes to persist or not to persist, modify the properties for the Northwind.sdf file in your solution properties as per your desire. By default it is "copy if newer" which means the database file in your execution folder will retain the changes. By changing it to "copy always" you can refresh your database each time.
I hope now you can now go back and start writing advanced programs using various techniques available.
Ado.Net Entity Framework is a new weapon to the list of a current developer's arsenal, opening multiple ways in which he can write his programs without worrying about the under lying intricacies.
The shift to entity plane seems very lucrative, easy and promising. And what more, all of this comes with handy tools which make life easy. Writing your code from the word Go!
The above topic was in relation to LINQ to Entities, if you are looking for LINQ to SQL refer to this blog post: http://blogs.msdn.com/sqlservercompact/archive/2007/08/21/linq-with-sql-server-compact-a-ka-dlinq-over-sql-ce.aspx
Happy Programming!
Ravi Tandon
SDET, SQL Server Compact