In the
previous article, we introduced something about Sql CE, currently we used Sql CE4.
Here we create an unit test for the Sql CE connection:
using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data.SqlServerCe;
namespace TestConnection
{
public class Connection
{
public static string conStatus;
public static string GetConStatus()
{
string connString = @"data source=C:\Test.sdf;password=111111";
SqlCeConnection connection = new SqlCeConnection(connString);
//SqlCeEngine engine = new SqlCeEngine(connString);
//engine.Upgrade();
try
{
connection.Open();
conStatus = "Con Opened";
}
catch(Exception e)
{
conStatus = e.Message;
}
return conStatus;
}
}
[TestClass]
public class UnitTest1
{
[TestMethod]
public void TestMethod1()
{
string conStatus = Connection.GetConStatus();
string conStatusExpected = "Con Opened";
//Assert.Inconclusive(conStatus);
Assert.AreEqual(conStatus, conStatusExpected);
}
}
}
Notice that some Sql CE database it may has different version, for example we use Sql Management Studio create a 3.5 version, we may need to use the code below to upgrade:
SqlCeEngine engine = new SqlCeEngine(connString);
engine.Upgrade();
In this article, we are going to use
Microsoft ADO.NET Entity Framework Feature Community Technology Preview 5 for database manipulation.
Some of the tutorial of EF CPT5 may be useful:
http://blogs.msdn.com/b/adonet/archive/2010/12/14/ef-feature-ctp5-released.aspx
http://blogs.msdn.com/b/adonet/
In
One of my old articles, I write some notes on how to use DataContext object in Linq to Create Database, manipulate tables.
Here EF CPT5 is a wonderful technique which get the manipulation of database easier.
There are
numerous articles on use DbContext to create or maniplate the databases.
I just wrote some sample code below, notice about how to write the class like Category, Product, Person please check the articles related to
POCO.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;
namespace CodeFirstSample
{
class Program
{
static void Main(string[] args)
{
using (var db = new MyContentext())
{
//var cate = new Category { CategoryId = "Drinks", Name = "Drinks" };
//db.Categories.Add(cate);
var person = new Person { PersonId = 1, Name = new Name { Title = "A", FirstName = "Tom", LastName = "Cat" } };
db.Persons.Add(person);
int recordsAffected = db.SaveChanges();
Console.WriteLine(
"Saved {0} entities to the database, press andy key to exit",
recordsAffected
);
Console.ReadKey();
}
}
}
public class Category
{
public string CategoryId { get; set; }
public string Name { get; set; }
public virtual ICollection
Products { get; set; }
}
public class Product
{
public int ProductId { get; set; }
public string Name { get; set; }
public string CategoryId { get; set; }
public virtual Category Cagtegory { get; set; }
}
public class Person
{
[Key]
public int PersonId { get; set; }
public Name Name { get; set; }
}
public class Name
{
public string Title { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
public class MyContentext : DbContext
{
public DbSet Categories { get; set; }
public DbSet Products { get; set; }
public DbSet Persons { get; set; }
}
}
When the above code run at the first time, There will have a ProjectName.MyContext database created on the sql express server : localhost\SQLEXPRESS with the tables Categories, Products and Persons. Please follow this link as a first walkthrough.
And
this article talks about the keys for the tables. And msdn has detailful annotation of the attribute which will define the table columns data types. And this article talks about using Fuent API doing some operations such as primary key, composite keys et al.
I am not going to talk about this indetails. But just one thing, Let's right click our project, and add an App.config file.
Change our app.config file to add connection strings:
< add name="MyContentext"
connectionString="data source=|DataDirectory|MyDb.sdf;password=1111"
providerName="System.Data.SqlServerCe.4.0" />
See the picture below:
Notice that in the connectionstring setting, the add name="...", the value should be the same as our DbContext class Name.
Run the application, now there will have a SqlCE database, MyDB.sdf with the openning password 1111 in our solution bin folder. See the picture below:
Now let's return to our MVC3 project:
In Visual Studio, View->Other Windows->Package Manager Console
Let's type: install-package EFCodeFirst, this is the same as reference the dll: EntityFramework.
In the Models folder, add a code file and let's write the POCO model classes and DbContext class:
The code is pretty much the following:
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
namespace MyFirstMVCProject.Models
{
public class Product
{
[Key]
public int ProductID { get; set; }
[StringLength(400)]
public string ProductName { get; set; }
[StringLength(400)]
public string Category { get; set; }
}
public class MyDbContext : DbContext
{
public DbSet Products { get; set; }
}
}
And in the Controller folder, let's add Products Controller:
using System.Linq;
using System.Web.Mvc;
using MyFirstMVCProject.Models;
namespace MyFirstMVCProject.Controllers
{
public class ProductsController : Controller
{
//
// GET: /Products/
MyDbContext mydb = new MyDbContext();
public ActionResult Display()
{
var products = from d in mydb.Products
select d;
return View(products.ToList());
}
}
}
And Let's create a view in shared folder:
And let's add the code in Display.cshtml file:
Now in web.config, let's add the following ConnectionString:
< add name="MyDbContext"
connectionString="Data Source=|DataDirectory|Products.sdf"
providerName="System.Data.SqlServerCe.4.0" />
Like the following picture:
Now Let's compile and run our project. We will see there is Products.sdf file under App_Data folder. This database is created in the controller when MyDbContext is called. This is really wonderful, right?
Let's include Products.sdf file in the project.
Let's double click Products database, and Tables for the Products Table->Show Table Data.
Let's add some records in Products table:
Compile and run our site again.
From the Global.asax file, we can see the default route setting is "{controller}/{action}/{id}", so we goes to the http://localhost:1414/Products/Display, we see the picture below:
Here we build a basic MVC3 site use EF CTP5.
