AngularJS/MVC Cookbook CRUD Operations

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.

Stay Informed

Sign up for the latest blogs, events, and insights.

We deliver solutions that accelerate the value of Azure.
Ready to experience the full power of Microsoft Azure?

Atmosera is thrilled to announce that we have been named GitHub AI Partner of the Year.

X