Windows 2012 Hosting - MVC 6 and SQL 2014 BLOG

Tutorial and Articles about Windows Hosting, SQL Hosting, MVC Hosting, and Silverlight Hosting

ASP.NET Hosting :: Using ADO.NET Entity Framework 4.1 with SQL Server Compact 4.0

clock September 30, 2013 07:01 by author Mike

ADO.NET Entity Framework, an object-relational mapper (ORM) built into the .NET framework, provides an easy way to map your classes into a database tables and vice versa.

SQL Server Compact (CE)
Yet another useful small scale tool. Of course you can map your entities into a full robust database. There are a number of pre-built providers, not counting Microsoft SQL and SQL Express (the default). I've chosen to use MS SQL Server Compact as a first option (later to be replaced or give the user/administrator the option to set a different provider) mainly because it's super-lightweight, builds the database in a file, and does not require heavy installation and licenses. It's free and is supported by mobile devices. For small applications it may be sufficient. For larger scale apps perhaps not so much.

Prerequisites checklist

  • ADO.NET Entity Framework 4.1, available here.
  • Microsoft SQL Server Compact 4.0, available here.
  • MS SQL Server CE has no fancy management application (like MS SQL Server Management Studio for more robust SQL Server versions) and it is not natively browsable from Visual Studio. 
    However, in order to query your tables and see what's going on, I recommend using SQL Server Compact Toolbox Visual Studio extension, available here.
    Mind you that in order for it to run properly, you will need to have both SQL Server CE 4.0 (from the link above) and the SQL Server CE 3.5 SP2 runtime from here installed on your machine.
    If you are running a 64 bit version of Windows, you will have to download, extract and install both the 32 bit and the 64 bit versions of the SQL Server CE 3.5 SP2.

Project references
Once all prerequisites are in place, the following assembly references are to be added to the project:

  • EntityFramework
    Should reside in the folder where the Microsoft ADO.NET Entity Framework 4.1 has been installed. By default it would be something like:
    C:\Program Files (x86)\Microsoft ADO.NET Entity Framework 4.1\Binaries\
  • System.ComponentModel.DataAnnotations
    This is required in order to use data attributes for annotating your persisted class properties, such as [Key] to denote a primary key property.

Code first: Data classes
The approach I'm implementing here assumes you have the data classes created first, and wish to map those into database tables. There are ways to implement in the opposite direction, meaning you have your database and wish to create corresponding classes ("Model first"). I'll use a simple structure of a Person class and Company class.
The simpler of the two is the company class, which consists of 2 properties: an ID and a name.

public class Company
{
    public Company()
    {
 
    }
 
    public Company(Guid companyId, string companyName)
    {
        this.CompanyId = companyId;
        this.CompanyName = companyName;
    }
 
    [KeyColumn(Order =1)]
    public Guid CompanyId { getset; }
 
    [KeyColumn(Order = 2)]
    public string CompanyName { getset; }
}

A few points to note, though:

  • Notice it has an empty default constructor with no arguments. Even if this constructor does nothing, it is required for reconstructing the object once it's fetched back from the database.
  • For this example, I've defined  both public properties, CompanyId and CompanyName as parts of a compound primary key.  It is also necessary to order the key parts as demonstrated here.

For this demo, I've also included a simple enum. Enums were quite clumsy to use in previous CTP versions of the Entity Framework, but as of version 4.x they are supported more easily.
Hence the following enum is also defined:

public enum Prefix 
{ 
    Mr,
    Ms,
    Mrs,
    Dr
}


The Person class:

public class Person
{
    public Person()
    {
 
    }
 
    public Person(Guid personId, string name, int age, Prefix pref, Company workplace)
    {
        this.PersonId = personId;
        this.Name = name;
        this.Age = age;
        this.NamePrefix = pref;
        this.Workplace = workplace;
    }
 
    [Key]
    public Guid PersonId { getset; }
 
    public string Name { getset; }
 
    public int Age { getset; }
 
    public Company Workplace { getset; }
 
    [Required]
    public virtual Prefix NamePrefix { getset; }
 
    public virtual int NamePrefixId
    {
        get { return (int)NamePrefix; }
        set { NamePrefix = (Prefix)value; }
    }
}

 

  • A more complex class. It has a member of type Company.
  • It has 2 member properties: NamePrefix and NamePrefixId, which relate to the enum of type Prefix. The NamePrefixId member is a helper for translating the enum to/from int, which is the actual underlying type which will be stored in the database.
  • It also has an empty default non-argumentative constructor for the sake of reconstructing the retrieved object.

Database context
This class is required as a management context for our persisted objects against the framework. It inherits DbContext and its properties, which are of the generic DbSet type represent the queryable collections of the persisted objects.

public class PersonsContext : DbContext
{
    public PersonsContext()
        : base(PersonsContext.ConnectionString)
    {
    }
 
    public DbSet<Person> Persons { getset; }
    public DbSet<Company> Companies { getset; }
 
    public static string ConnectionString
    {
        get
        {
            return @"Data Source=" + 
                System.Reflection
                    .Assembly
                    .GetExecutingAssembly()
                    .Location
                    .Substring(0, 
                        System
                        .Reflection
                        .Assembly
                        .GetExecutingAssembly()
                        .Location
                        .LastIndexOf("\\") + 1) 
                + @"\\people.sdf";
        }
    }
}

Furthermore:

  • Notice the constructor.
    It invokes the base class constructor, providing a simple connection string to our database.
    It could have also provided the name of a connection string from the App.Config file, or it could provide no connection string at all (in which case our database would be created on the local instance of SQL Express, with a name similar to our executing application.
  • The connection string provided here corresponds with MS SQL Compact, and refers to a database file, people.sdf, which is expected to be created/found on the same folder of our application's assembly (If debug/run the solution from Visual Studio, the folder would be Bin\Debug by default)

Showing the seeds
Many times when we run a newly built database-oriented application, we'd want to start with some predefined data already stored in the database. If we generate the database manually with some SQL script, we could easily include some INSERT statements in order to generate and store the initial data. In the case of Entity Framework, we can generate and store our initial data by creating the following class:

public class DatabaseInitializer : CreateDatabaseIfNotExists<PersonsContext>
{
    protected override void Seed(PersonsContext context)
    {
        Company telerik = new Company(Guid.NewGuid(), "Telerik");;
        Person alon = new Person(Guid.NewGuid(), "Alon", 38, Prefix.Mr, telerik);
 
        context.Companies.Add(telerik);
        context.Persons.Add(alon);
 
        context.SaveChanges();
    }
}

Notice:

  • This class inherits the generic CreateDatabaseIfNotExists class. This is the default policy of the Entity Framework (to create the database if it does not already exist). Other options are listed on the MSDN library.
  • We override the Seed method, and populate the context argument with our predefined objects. Once the new database is created, this data should be automatically inserted into the underlying tables. In this case, one company (telerik) and one person (alon) are created and seeded.

Helper classes

Now almost everything is in its right place. We will need to tie everything together, though. I love helper classes. They are just so damn helpful. I've created this helper class in order to simplify and wrap the interaction with the data context nicely.

public class DataManager
{
    public static PersonsContext DataContext
    {
        get
        {
            if (DataManager.dataContext == null)
            {
                Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");
                DataManager.dataContext = new PersonsContext();
                bool dbExists = DataManager.dataContext.Database.Exists();
                if (dbExists)
                {
                    //UpgradeScript();
                }
                if (!dbExists)
                {
                    Database.SetInitializer(new DatabaseInitializer());
                }
                else
                    Database.SetInitializer<PersonsContext>(null);
            }
            return DataManager.dataContext;
        }
    }
    private static PersonsContext dataContext = null;
}

About this class:

  • The main purpose here is to statically cache (in memory) and manage our PersonsContext object. A few important settings are also applied here:Setting the default database factory to SQL Server Compact (SqlServerCe).
  • If the database does not exist, an initializer of type DatabaseInitializer is assigned. That is, the seeding class defined above will kick in and create the persisted entities in the database.
  • If the database already exists, no initializer is assigned, but I'm leaving the option to run upgrade scripts (i.e. check the version of our data and apply changes in the database schema according to changes in our persisted classes and/or seeded data to match the current product version) if they are needed.
    Those should manipulate tables, and need to be run directly against the database.

Manipulations
To demonstrate the ease of use of this shameless production and manipulation through basic CRUD operations, I've constructed a small console application. Here is its main function:

class Program
{
    static void Main(string[] args)
    {
        //Check seeding:
        Console.WriteLine("{0} persons are in your database.\n"DataManager.DataContext.Persons.Count());
 
        //Basic CRUD operations:
 
        //C(reate)
        if (DataManager.DataContext.Companies.FirstOrDefault(
        c => c.CompanyName == "Microsoft") == null)
        {
            DataModel.Company microsoft = new DataModel.Company(Guid.NewGuid(), "Microsoft");
            DataManager.DataContext.Companies.Add(microsoft);
            DataManager.DataContext.Persons.Add(new DataModel.Person(
                Guid.NewGuid(), 
                "John doe", 
                20, 
                DataModel.Prefix.Dr, 
                microsoft));
            DataManager.DataContext.Persons.Add(new DataModel.Person(
                Guid.NewGuid(), 
                "Klark Kent", 
                30, 
                DataModel.Prefix.Dr, 
                microsoft));
            DataManager.DataContext.SaveChanges();
        }
 
        //R(ead)
        IQueryable<DataModel.Person> microsoftWorkers = DataManager.DataContext.Persons.Where(
            person => person.Workplace.CompanyName == "Microsoft");
        Console.WriteLine("{0} persons are working in Microsoft.\n", microsoftWorkers.Count());
 
        //U(pdate)
        DataModel.Person johnDoe = DataManager.DataContext.Persons.FirstOrDefault(
            p => p.Name == "John Doe");
        if (johnDoe != null)
        {
            johnDoe.Name = "Changed Name";
            DataManager.DataContext.SaveChanges();
        }
 
        //D(elete)
        DataModel.Person klark = DataManager.DataContext.Persons.FirstOrDefault(
            p => p.Name == "Klark Kent");
        if (klark != null)
        {
            DataManager.DataContext.Persons.Remove(klark);
            DataManager.DataContext.SaveChanges();
        }
            
        //Done.
        Console.ReadKey();
    }
}

 

Where is my data?
As written in the prerequisites section above, I'm using the very comfortable SQL Server Compact Toolbox Visual Studio extension.
Once installed, it can be accessed through the Tools menu:

Connecting to your database file is then extremely simple:

In the dialog, browse to select your created SDF database file, the connection string will be automatically created:

And you can now browse through your database and run queries like any civilized respectful person would do, if they were you:

A query execution window will open



ASP.NET MVC 4 Hosting - Razor 2.0 Features in ASP.NET MVC 4

clock February 19, 2013 12:26 by author andy_yo

First version of razor is shipped with ASP.NET MVC 3. ASP.NET MVC 4 come with Razor V2.0. Razor V2.0 includes some new features.

About ASPHostPortal.com
ASPHostPortal.com is Microsoft No #1 Recommended Windows and ASP.NET Spotlight Hosting Partner in United States. Microsoft presents this award to ASPHostPortal.com for ability to support the latest Microsoft and ASP.NET technology, such as: WebMatrix, WebDeploy, Visual Studio 2012, ASP.NET 4.5, ASP.NET MVC 4.0, Silverlight 5 and Visual Studio Lightswitch. Click here for more information

 

URL Resolution Enhancements:

We use the relative URL for any resources (images, scripts, css) in code , for example :

<script src=”~/Scripts/jquery-1.8.2.js”></script>

But in runtime we should resolve the full path of the resource (absolute URL), to do this in ASP.NET MVC 3 we use Content() method of UrlHelper class.

<script src=’@Url.Content(“~/Scripts/jquery-1.8.2.js”)’></script>

In Razor V2.0 no need to use @Url.Content() method, razor v2.0 now resolves (absolute path/URL) ~/ (tilde-slash) within all standard HTML attributes, razor v2.0 now allows you to just write  :

<script src=”~/Scripts/jquery-1.8.2.js”></script>

Conditional Attribute Enhancements:

Hers is a classic example for conditional attributes, I have a <div> tag with class attribute. The class name is a dynamic value, It will be resolved at runtime based on some condition in razor code. If the condition is satisfied  class attribute will have some value, else it will be null. When there is a value no issues, but when value is null  we should not apply it for class attribute, to be strict when there is no value for class attribute we should not render it. This scenario involves with writing some ugly code with if condition & <text> tag, in Razor1.0 (ASP.NET MVC 3) code snippet as follows.

 

In Razor 2.0 (ASP.NET MVC 4) now we can write:

 

 

Above code returns the same results as earlier, for example if the @ViewBag.UseRoundCorners value is true, then myClass value is roundCorners, then  razor will render :

<div class=”roundCorners ”></div>

If @ViewBag.UseRoundCorners value is false, then myClass value is null, then razor will render <div> tag with out class attribute:

<div></div>

Razor V2.0 can handle multiple values in conditional attributes:

For example class attribute has multiple values <div class=”@myClass heading”></div>, now razor will render <div class=”roundCorners  heading”></div> if @myClass is not null, if @myClass is null, then razor will simply render <div class=”heading”></div>.

Razor V2.0  can handle Boolean values in conditional attributes:

Not only null values & multiple values, razor can handle boolean values also in conditional attributes.

<input type=”checkbox”  checked=”@ViewBag.Checked” />

If @ViewBag.Checked value is true then razor will render:

<input type=”checkbox”  checked=”checked” />

If @ViewBag.Checked value is false then razor will render:

<input type=”checkbox”  />



About ASPHostPortal.com

We’re a company that works differently to most. Value is what we output and help our customers achieve, not how much money we put in the bank. It’s not because we are altruistic. It’s based on an even simpler principle. "Do good things, and good things will come to you".

Success for us is something that is continually experienced, not something that is reached. For us it is all about the experience – more than the journey. Life is a continual experience. We see the Internet as being an incredible amplifier to the experience of life for all of us. It can help humanity come together to explode in knowledge exploration and discussion. It is continual enlightenment of new ideas, experiences, and passions


Author Link


Corporate Address (Location)

ASPHostPortal
170 W 56th Street, Suite 121
New York, NY 10019
United States

Sign in