Let’s look at some fundamentals of a typical web application that performs CRUD (Create, Read, Update, and Delete) operations. For this, the CRUD Operations project was added to the AngularJS/MVC Cookbook.
I needed some test data, so I borrowed a few records from SQL Server’s AdventureWorks example database. This query gave me an XML document containing people, postal addresses, phone numbers, and email addresses.
SELECT
(
SELECT p.BusinessEntityID AS '@id',
(
SELECT p.Title AS '@title',
p.FirstName AS '@first',
p.MiddleName AS '@middle',
p.LastName AS '@last',
p.Suffix AS '@suffix'
FOR XML PATH('name'), TYPE
),
(
SELECT a.AddressLine1 AS '@addr1',
a.AddressLine2 AS '@addr2',
a.City AS '@city',
sp.Name AS '@stateProv',
cr.Name AS '@country',
a.PostalCode AS '@postal'
FROM Person.BusinessEntityAddress AS bea
LEFT OUTER JOIN Person.Address AS a ON a.AddressID = bea.AddressID
INNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID
INNER JOIN Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode
WHERE bea.BusinessEntityID = p.BusinessEntityID
FOR XML PATH('address'), TYPE
),
(
SELECT pp.PhoneNumber AS '@num',
pnt.Name AS '@type'
FROM Person.PersonPhone AS pp
LEFT OUTER JOIN Person.PhoneNumberType AS pnt ON pp.PhoneNumberTypeID = pnt.PhoneNumberTypeID
WHERE pp.BusinessEntityID = p.BusinessEntityID
FOR XML PATH('phone'), TYPE
),
(
SELECT ea.EmailAddress AS '@addr'
FROM Person.EmailAddress AS ea
WHERE ea.BusinessEntityID = p.BusinessEntityID
FOR XML PATH('email'), TYPE
)
FROM Person.Person AS p
FOR XML PATH('person'), TYPE
)
FOR XML PATH('people')
The query returns almost 20,000 sets of people information. This should help us to look at real-world scenarios.
Entity Framework and Code First
We need to take a quick detour into the mechanism used to deal with the database entities. For this example, I will be using Entity Framework with Code First configuration. I’m also using SQL Server Compact as the database engine.
Entity Framework and SQL Server Compact were added to the project using their respective NuGet packages.
A connection string was added to the web.config file in order to specify the name of the SQL Compact file (located in the App_Data directory of the web application).
<connectionStrings>
<add name="ExampleData" providerName="System.Data.SqlServerCe.4.0" connectionString="Data Source=|DataDirectory|ExampleData.sdf" />
</connectionStrings>
And Entity Framework configuration was added in order to use SQL Server Compact.
<system.data>
<DbProviderFactories>
<remove invariant="System.Data.SqlServerCe.4.0" />
<add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
</DbProviderFactories>
</system.data>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
<parameters>
<parameter value="System.Data.SqlServerCe.4.0" />
</parameters>
</defaultConnectionFactory>
</entityFramework>
There are four entities that have been defined to represent this data:
- Person
- PostalAddress
- PhoneNumber
- EmailAddress
There is a one-to-many relationship between Person and the other entities.
Code First configuration allows us to specify all of the database-specific configuration within a DbContext model. Our entities can then be simple POCO objects (but you could obviously add Data Annotations to the entities if you wish). For this example, all of the configuration is delegated to the DbContext OnModelCreating method. This method sets up all of the entities for our “people” domain model, including key definitions, field lengths, and relationships.
public DbSet<Person> People { get; set; }
public DbSet<PostalAddress> PostalAddresses { get; set; }
public DbSet<PhoneNumber> PhoneNumbers { get; set; }
public DbSet<EmailAddress> EmailAddresses { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
Configure<Person>(
modelBuilder,
entity =>
{
entity.ToTable("People");
entity
.Property(e => e.PersonId)
.HasColumnName("Id")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
entity.HasKey(e => e.PersonId);
entity.Property(e => e.FirstName).IsRequired().HasMaxLength(Person.FirstNameMaxLength);
entity.Property(e => e.LastName).IsRequired().HasMaxLength(Person.LastNameMaxLength);
entity.Property(e => e.Title).HasMaxLength(Person.TitleMaxLength);
entity.Property(e => e.MiddleName).HasMaxLength(Person.MiddleNameMaxLength);
entity.Property(e => e.Suffix).HasMaxLength(Person.SuffixMaxLength);
});
Configure<PostalAddress>(
modelBuilder,
entity =>
{
entity.ToTable("Postal");
entity
.Property(pa => pa.PostalAddressId)
.HasColumnName("Id")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
entity.HasKey(pa => pa.PostalAddressId);
entity.Property(pa => pa.LineOne).IsRequired().HasMaxLength(PostalAddress.AddressLineMaxLength);
entity.Property(pa => pa.LineTwo).HasMaxLength(PostalAddress.AddressLineMaxLength);
entity.Property(pa => pa.City).IsRequired().HasMaxLength(PostalAddress.CityMaxLength);
entity.Property(pa => pa.StateProvince).HasMaxLength(PostalAddress.StateProviceMaxLength);
entity.Property(pa => pa.Country).IsRequired().HasMaxLength(PostalAddress.CountryMaxLength);
entity.Property(pa => pa.PostalCode).IsRequired().HasMaxLength(PostalAddress.PostalCodeMaxLength);
entity
.HasRequired(pa => pa.Person)
.WithMany(p => p.PostalAddresses)
.HasForeignKey(pa => pa.PersonId);
});
Configure<PhoneNumber>(
modelBuilder,
entity =>
{
entity.ToTable("Phone");
entity
.Property(ph => ph.PhoneNumberId)
.HasColumnName("Id")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
entity.HasKey(ph => ph.PhoneNumberId);
entity.Property(ph => ph.Number).IsRequired().HasMaxLength(PhoneNumber.NumberMaxLength);
entity.Property(ph => ph.NumberType).IsRequired().HasMaxLength(PhoneNumber.NumberTypeMaxLength);
entity
.HasRequired(ph => ph.Person)
.WithMany(p => p.PhoneNumbers)
.HasForeignKey(ph => ph.PersonId);
});
Configure<EmailAddress>(
modelBuilder,
entity =>
{
entity.ToTable("Email");
entity
.Property(ea => ea.EmailAddressId)
.HasColumnName("Id")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
entity.HasKey(ea => ea.EmailAddressId);
entity.Property(ea => ea.Address).IsRequired().HasMaxLength(EmailAddress.AddressMaxLength);
entity
.HasRequired(ea => ea.Person)
.WithMany(p => p.EmailAddresses)
.HasForeignKey(ea => ea.PersonId);
});
}
private void Configure<T>(DbModelBuilder modelBuilder, Action<EntityTypeConfiguration<T>> configureEntityMethod)
where T : class
{
var entityConfig = modelBuilder.Entity<T>();
configureEntityMethod(entityConfig);
}
The Entity Framework NuGet package provides utility commands to create the database and migrate the database to different versions (more information here). The initial migration script (201303032041565_InitialCreate.cs) handles the actual creation of the database schema.
One last task is seeding the initial database with the data we borrowed from the AdventureWorks database. This is handled within the Seed method of the migrations Configuration class. For best performance, I used SqlCeUpdatableRecord objects to add the data to the database.
public class SeedData
{
public static void Seed(ExampleDbContext ctx)
{
if (!ctx.People.Any())
{
LoadPeopleData(ctx.Database.Connection.ConnectionString);
}
else
{
LogMsg("People records have already been added to the database.");
}
}
private static void LoadPeopleData(string connStr)
{
const string peopleXmlFile = "People.xml";
string basePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "../Repository");
string fileName = Path.Combine(basePath, peopleXmlFile);
var xdoc = XDocument.Load(fileName);
DateTime startTime = DateTime.Now;
int recordCount = 0, totalRecordCount = xdoc.Element("people").Elements("person").Count();
LogMsg("Adding {0:n0} people records.", totalRecordCount);
LogMsg("Opening database: {0}", connStr);
using (var sqlConn = new SqlCeConnection(connStr))
using (
TableHelper peopleTable = new TableHelper("People"),
postalTable = new TableHelper("Postal"),
phoneTable = new TableHelper("Phone"),
emailTable = new TableHelper("Email"))
{
sqlConn.Open();
peopleTable.Open(sqlConn);
postalTable.Open(sqlConn);
phoneTable.Open(sqlConn);
emailTable.Open(sqlConn);
var peopleElements = xdoc.Element("people").Elements("person");
foreach (var personElement in peopleElements)
{
var person =
personElement
.Elements("name")
.Select(
name =>
new Person
{
Title = (string)name.Attribute("title"),
FirstName = (string)name.Attribute("first"),
MiddleName = (string)name.Attribute("middle"),
LastName = (string)name.Attribute("last"),
Suffix = (string)name.Attribute("suffix"),
})
.Single();
person.PostalAddresses =
personElement
.Elements("address")
.Select(
addr =>
new PostalAddress
{
LineOne = (string)addr.Attribute("addr1"),
LineTwo = (string)addr.Attribute("addr2"),
City = (string)addr.Attribute("city"),
StateProvince = (string)addr.Attribute("stateProv"),
Country = (string)addr.Attribute("country"),
PostalCode = (string)addr.Attribute("postal"),
})
.ToList();
person.EmailAddresses =
personElement
.Elements("email")
.Select(
email =>
new EmailAddress
{
Address = (string)email.Attribute("addr"),
})
.ToList();
person.PhoneNumbers =
personElement
.Elements("phone")
.Select(
phone =>
new PhoneNumber
{
Number = (string)phone.Attribute("num"),
NumberType = (string)phone.Attribute("type"),
})
.ToList();
peopleTable.Record.SetValue(1, person.Title);
peopleTable.Record.SetValue(2, person.FirstName);
peopleTable.Record.SetValue(3, person.MiddleName);
peopleTable.Record.SetValue(4, person.LastName);
peopleTable.Record.SetValue(5, person.Suffix);
person.PersonId = peopleTable.Insert();
foreach (var postal in person.PostalAddresses)
{
postal.PersonId = person.PersonId;
postalTable.Record.SetValue(1, postal.PersonId);
postalTable.Record.SetValue(2, postal.LineOne);
postalTable.Record.SetValue(3, postal.LineTwo);
postalTable.Record.SetValue(4, postal.City);
postalTable.Record.SetValue(5, postal.StateProvince);
postalTable.Record.SetValue(6, postal.Country);
postalTable.Record.SetValue(7, postal.PostalCode);
postal.PostalAddressId = postalTable.Insert();
}
foreach (var phone in person.PhoneNumbers)
{
phone.PersonId = person.PersonId;
phoneTable.Record.SetValue(1, phone.PersonId);
phoneTable.Record.SetValue(2, phone.Number);
phoneTable.Record.SetValue(3, phone.NumberType);
phone.PhoneNumberId = phoneTable.Insert();
}
foreach (var email in person.EmailAddresses)
{
email.PersonId = person.PersonId;
emailTable.Record.SetValue(1, email.PersonId);
emailTable.Record.SetValue(2, email.Address);
email.EmailAddressId = emailTable.Insert();
}
if (++recordCount % 100 == 0)
{
LogMsg("Added {0:n0} records ({1}%)", recordCount, recordCount * 100 / totalRecordCount);
}
}
}
LogMsg("Finished, added {0:n0} people records.", recordCount);
LogMsg("Time: {0} ({1:n2} recs/sec)",
DateTime.Now.Subtract(startTime),
recordCount / DateTime.Now.Subtract(startTime).TotalSeconds);
}
private static void LogMsg(string msgFmt, params object[] msgArgs)
{
Console.WriteLine(msgFmt, msgArgs);
}
private class TableHelper : IDisposable
{
private readonly string _tableName;
private SqlCeCommand _sqlCmd;
private SqlCeResultSet _resultSet;
private SqlCeUpdatableRecord _record;
public TableHelper(string tableName)
{
_tableName = tableName;
}
public SqlCeResultSet ResultSet
{
get { return _resultSet; }
}
public SqlCeUpdatableRecord Record
{
get { return _record; }
}
public void Open(SqlCeConnection sqlConn)
{
_sqlCmd = sqlConn.CreateCommand();
_sqlCmd.CommandText = String.Concat("SELECT * FROM ", _tableName);
_resultSet = _sqlCmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
_record = _resultSet.CreateRecord();
}
public int Insert()
{
_resultSet.Insert(_record, DbInsertOptions.PositionOnInsertedRow);
return _resultSet.GetInt32(0);
}
public void Dispose()
{
if (_sqlCmd != null)
{
_resultSet.Dispose();
_sqlCmd.Dispose();
_record = null;
_resultSet = null;
_sqlCmd = null;
}
}
}
}
}
This sets up the framework for using the example data domain model. Next, let’s move on to the user interface.