Reverse-Engineering an Existing Database in your ASP.NET MVC Application Using Entity Framework Model/Database-First

Posted on October 20 2013 08:25 PM by jatten in ASP.Net, CodeProject, C#, Web, ASP.NET MVC, Database   ||   Comments (0)

gears-320Entity Framework's "Code-First" development model has become a highly popular approach to data modeling in ASP.NET MVC development in the last few years. The Code First approach saves the average developer the perceived pain of working with SQL, and also minimizes the amount of repetitive, "boiler plate" code we need to write as part of a more traditional data access layer.

However, quite frequently we will need to develop against an existing database, the schema of which may or may not be open for modification.

Image by Ian Britton | Some Rights Reserved

While it is possible to reverse-engineer an existing database and then switch to a code-first approach once the models have been created, for various reasons this approach may be impossible or impractical. In certain cases we must start with a pre-defined database schema and/or allow database changes to drive our model instead of the other way around. In this post we will take a quick look at how to reverse engineer an existing database to create your conceptual data model.

Within the context of an ASP.NET MVC application, we may find ourselves looking at the Database or Model First approach in a number of different situations, including:

  • Creating a new front end against an existing database
  • An organizational or DBA philosophy driven by the modeling the database first
  • Personal developer preference – some of us simply prefer to model the data first.

For Our Examples: The Database . . . First . . .

Under the premise that we must (for whatever reason) build out our application around an existing database, we will create an extremely complex SQL Server database and pretend it is what we have been given by the powers that be:

Example Database:

simple-example-database-diagram

As you can see, we have a complicated database with two related tables. Now, let's look at an example ASP.NET MVC project, into which we will reverse engineer this database to create our model classes and database context.

Create an ADO.NET Entity Data Model

In a standard ASP.NET project, we should find a pre-defined Models folder, in which there will (usually) already be an Account Model related to ASP.NET Forms Membership. Right-click on the Models folder in the VS Solution Explorer and select Add => ADO.NET Entity Data Model:

Adding an ADO.NET Entity Data Model:

add-ado-net-entity-data-model

After you do this, you will be prompted to provide a name for the data model you are about to create. The convention is something along the lines of "<ProjectName>Entities." In this case, I chose "ExampleEntities" and then proceed to the next step, where we are asked to choose what the model should contain. Obviously, we want to select "Generate from database" here, and then select Next in the Wizard.

In the next window, we are asked to choose a connection:

Choose Your Data Connection (or create a New One):

choose-connection1

Obviously, our database is not called "ChinookDb.sdf" as seen in the picture above, and  in fact we probably don't already have our database available as an existing connection, so we need to click the "New Connection" button and create one. Once that's done, you will notice the wizard automatically determined an appropriate name (in my case, "ExampleDbEntities" for our connection string in Web.config. You can change this if you like, but I recommend using the default for consistency. Hit "Next."

In the next window, we are asked to select the database objects we wish to include in our model. For our simple database (and in most cases) we just want the tables. In my case, there is an extra table "sysDiagrams" that is NOT part of our domain model. In your own project, select the tables which make sense as part of your model domain. The other default settings in this window are usually appropriate as well:

Select the Database Objects:

select-database-objects

Now, hit "Finish."

VS will take a few seconds to reverse engineer your database, and when finished, will present you with a diagram of the conceptual model of your database. As you can see, VS made fast work of our extremely complex data model:

The EDMX Diagram for ExampleEntities:

edmx-model-diagram

We can find our generated model classes in Solution Explorer under Models => ExampleEntities => ExampleEntities.tt:

The New Model Classes in VS Solution Explorer:

 

where-are-my-model-classes

 

Add Functionality to Generated Classes with Partial Classes

Now that we have our model classes, it may often be the case we need to add functionality to them to suit the requirements of our program. Under the model-first or Database First approach, there are some things to be aware of here. These classes are generated, and can be automagically updated to reflect changes in the underlying database. If and when you do this, they will be re-written, and any code you may have added will be gone. Therefore, when you need to add additional properties or methods to one of these classes, use partial classes and add them in the Models folder.

For a contrived example, let's say we absolutely needed a CountOfAlbums property defined on our Artist class. We COULD open up our Artist class and simply add a new method like so:

Modifying the Artist Class in the Generated File:
//------------------------------------------------------------------------------
// <auto-generated>
//    This code was generated from a template.
//
//    Manual changes to this file may cause unexpected behavior in your application.
//    Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
  
namespace AzureModelFirstExample.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class Artist
    {
        public Artist()
        {
            this.Albums = new HashSet<Album>();
        }
    
        public int Id { get; set; }
        public string Artist1 { get; set; }
    
        public virtual ICollection<Album> Albums { get; set; }
  
        // THIS IS OUR NEW METHOD:
        public int CountAlbums
        {
            get
            {
                return this.Albums.Count;
            }
        }
    }
}

 

However, if later we were to have EF update our model to reflect changes in the underlying database schema (We'll look at this in a moment), this addition would be over-written. A better approach would be to add a new class file named ArtistPartial to our Models folder. Once we have done this by right-clicking in Solution Explorer => Add => Class . . . , we get a code file which looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace AzureModelFirstExample.Models
{
    public class ArtistPartial
    {
    }
}

 

Now, we will leave the actual code file name as ArtistPartial.cs, but we can replace the class declaration using the partial keyword and the class name Artist to match our generated model class. Then we can put our custom property in this file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
  
namespace AzureModelFirstExample.Models
{
    public partial class Artist
    {
        // THIS IS OUR NEW METHOD:
        public int CountAlbums
        {
            get
            {
                return this.Albums.Count;
            }
        }
    }
}

 

Now, we can simply add the partial keyword to the class declaration for our EF-generated Artist model class (which we may need to do again if we regenerate the code as well, but it is easier to deal with than re-writing one or more potentially complex properties or methods . . .):

//------------------------------------------------------------------------------
// <auto-generated>
//    This code was generated from a template.
//
//    Manual changes to this file may cause unexpected behavior in your application.
//    Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
  
namespace AzureModelFirstExample.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class Artist
    {
        public Artist()
        {
            this.Albums = new HashSet<Album>();
        }
    
        public int Id { get; set; }
        public string Artist1 { get; set; }
    
        public virtual ICollection<Album> Albums { get; set; }
    }
}

 

Adding MetaData Annotations to Generated Classes using Partial Classes

ASP.NET MVC provides some powerful and time-saving validation and other features which rely on Data Annotations facilitated in System.ComponentModel.DataAnnotations. Important attribute decorations for our model classes such as [Required] and [DisplayName] allow us to let the MVC framework and JQuery provide basic data entry validations for our site right out of the box.

As generated, our simple model classes don't know anything about whether (for example) NULL values are allowed in the database. If we want to take advantage of MVC's out-of-the-box validation and other helpers with our generated classes, we need to find a way to implement the data annotations and other metadata attributes. As before, if we implement these annotations directly in the generated Artist (or any other EF-generated) code file, they would again be over-written any time the model is updated. However, we can do this in our partial class, and thus preserve our validations through successive model updates throughout the development process. As an example, we know that:

  • The Artist1 property of our Artist class is required, as in our database null values are not allowed (and we ALL KNOW that an empty string is NOT THE SAME AS NULL . . . RIGHT??).
  • Artist1 is a terrible name for what should be the "Name" property of the artist. However, in our database. we would like to be able to give this a [Display] name attribute so that in our views, MVC will use a more appropriate label (such as "Name" or simply "Artist").

We can achieve both objectives by adding the following to our ArtistPartial code file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
  
// This must be added to our using statements:
using System.ComponentModel.DataAnnotations;
  
namespace AzureModelFirstExample.Models
{
    // We have added a class-level MetaData attribute here:
    [MetadataType(typeof(ArtistMetadata))]
    public partial class Artist
    {
        // THIS IS OUR NEW METHOD:
        public int CountAlbums
        {
            get
            {
                return this.Albums.Count;
            }
        }
    }
  
    // We have defined and ArtistMetaData class which defines the 
    // meta-data attributes for various properties on the artist class:
    public class ArtistMetadata
    {
        [Required]
        [Display(Name = "Artist Id")]
        public string Id { get; set; }
  
        [Required]
        [Display(Name = "Name")]
        public string Artist1 { get; set; }
    }
}

 

Make sure to add the using System.ComponentModel.DataAnnotations to your using statements for the above to work properly.

As you can see, we added a new ArtistMetaData class to our ArtistPartial.cs code file, and defined the data annotations we wanted for each of the properties. To me, the one of greatest import is the [Required] attribute, which allows the MVC framework to perform JQuery validations right from the start.

When we work under a code-first development model, we would define these and other attributes on our classes, and EF would use this metadata while creating our database. In Model/Database first development, we need to decorate our classes manually. Doing this using partial classes allows us to preserve the annotations through successive automated updates to the model.

Updating the Model to Reflect Changes in the Underlying Database

Imagine the harsh, cruel DBA (aren't they all? – kidding, of course) at our company informs us that he has added a new table, RecordLabels to the database, and created a foreign key on the Albums table to reflect the label for each particular album.

No big deal, Entity Framework has us covered, right?

We can simply open our EDMX diagram, right-click anywhere in the viewing area, and select "Update model from database . . ." :

Updating the Model to Reflect Changes in the Database Schema:

update-model-from-database

 

Once we select this option, we see a familiar window. However, there are some differences. Once again, we will select the new tables to add. Again in my case sysDiagrams appears as an option, as well as the new RecordLabels table our cruel DBA added. We only want to select the RecordLabels table to add:

Select the RecordLabels Table to Add to the EF Model:

update-add-new-table-to-model

This time, however, we also see a tab named "Refresh." Here we find any database objects which have been changed since the last model update. In our case, we  have added a foreign key to the Albums table. In my specific case, the Artist table also appears, because I modified a few things in the course of preparing these examples.

Database Objects Modified Since the Last Model Update:

update-refresh-existing

Once we click the "Finish" button, VS will again take a moment to regenerate our model classes. Once we Rebuild the project, we find that we now have the new model class RecordLabel in our ExampleEntities.tt file. Further, if we open our Album class we see that EF has now added a LabelId field, and a RecordLabel class as a property.

Last, since we carefully defined any additional methods required for our classes, along with relevant meta-data annotations, in respective partial class files for each of our model classes, our custom code for each class is untouched. We may need to modify it to reflect some of the changes (for example, we have added a LabelId field to the Album class, which likely should be non-nullable in the database and hence [Required] in our model.

We may need to go back through and add the partial keyword to some of our classes, although in some cases EF can figure out that it needs this as well.

Also, we should probably create a similar partial class file for our new RecordLabel class, and add what annotations and additional code make sense. 

Why Model or Database First?

The Entity Framework Code-First approach seems to be the new shiny thing for use in developing database applications. From a strictly developer standpoint, the Code First approach may be attractive in that:

  • We can (to an extent) allow our database to be modeled upon our object domain, and we get to work and think in the familiar Object-Oriented realm we understand the best;
  • Changes to the underlying data base structure can be propagated automatically as our code evolves
  • We don't have to deal directly with SQL (for some, this is a strangely big deal).

However, there are plenty of times when the Code First approach is either not an option (remember the cruel DBA with the existing database we are required to use?), or when as a matter of preference we choose to work with the data model first, and build our code on top of that. In these situations, it is important to recognize the small but significant differences between the two approaches, and build out our application accordingly.

Additional Resources and Items of Interest

 

Posted on October 20 2013 08:25 PM by jatten     

Comments (0)

Use Cross-Platform/OSS ExcelDataReader to Read Excel Files with No Dependencies on Office or ACE

Posted on October 17 2013 07:28 PM by jatten in C#, CodeProject, Tools   ||   Comments (0)

Planet-Sun-Textures-500I recently posted an article about LinqToExcel,  a fantastic library I discovered while trying to solve a problem for a work-related project. The premise was that I wanted a library I could use to read data from MS Excel files which may be uploaded to a website, and LinqToExcel seemed to fit the bill nicely. Until, that is, I went to deploy to Windows Azure Websites (a requirement of the project in question).

Image by Elif Ayiter | Some Rights Reserved

My Problem with LinqToExcel

LinqToExcel takes a dependency on the ACE Database Engine (I knew this, but it didn't occur to me that this would be an issue until I attempted to deploy), a redistributable .exe which must be installed on the host machine, and which is not available under a standard Azure Website configuration (and likely not easily available under many shared hosting plans, either). In point of fact, one could not even use ADO.NET/OleDb to read Excel data under these circumstances, as the .NET OleDb provider also requires the ACE database engine in order to connect to Excel as a data source.

ExcelDataReader: Open Source, Cross-Platform, No Proprietary Dependencies

Enter ExcelDataReader, an Open Source project which facilitates reading data from Excel spreadsheets by working directly with the Binary Excel file (.xls files), and/or abstracting the OpenXml library to provide a more familiar Data Access paradigm (.xlsx files).

Also note that ExcelDataReader is Cross-Platform and will run on Windows, as well as (under Mono) OSX, and Linux.

I will admit, the API for using ExcelDataReader is not as intuitive or friendly as that exposed by LinqToExcel. That said, ExcelDataReader is considerably easier to use (but more limited in functionality) than the raw Office OpenXml interface, as well as other popular Open Source libraries such as NPOI. These two libraries offer significantly more functionality than does ExcelDataReader (or, for that matter, LinqToExcel). They are focused as much on document creation and content manipulation as they are on simply reading data. However they bring substantial complexity and are less intuitive.

ExcelDataReader, in keeping with its name, models the underlying workbook and worksheets using the somewhat dated ADO.NET DataSet and DataTables model.

Get ExcelDataReader from the Nuget Gallery

As with LinqToExcel, ExcelDataReader is available via Nuget. You can simply do:

Get ExcelDataReader via the Nuget Package Console:
PM> Install-Package ExcelDataReader

 

Or, you can right-click on your solution in the Visual Studio Solution Explorer, select "Manage Nuget Packages for Solution" and use the GUI. When the window opens, make sure the "Online" tab is selected in the left-hand pane, then search for "ExcelDataReader." Click Install:

Get ExcelDataReader via the Nuget Package Manager GUI:

get-excel-data-reader-via-nuget-package-manager

The Simplest Way to Access Data Using ExcelDataReader

Now that you have ExcelDataReader installed in your project, let's get right to some simple examples.

First off, as previously mentioned, ExcelDataReader works with no dependencies on either the ACE Database engine, or the Office Interop libraries. It starts with the Excel binary file, therefore, we need to provide a FileStream object to the ExcelReaderFactory.

It is also important to note that ExcelDataReader works with both .xls and .xlsx file formats. In doing so, the ExcelReaderFactory provides two methods, CreateBinaryReader() and CreateOpenXmlReader() to work with each format respectively. Both methods return an instance of the interface IExcelDataReader.

For our example, we will create a simple class, ExcelData, encapsulating the file and data access logic. We will pass a file path into the constructor, and build out some methods to get at our Excel data. So to being with, we create the new class, and implement the constructor and getExcelReader() method:

A Basic Class to provide access to ExcelDataReader Functionality
using Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
  
namespace ExcelDataReaderExamples
{
    public class ExcelData
    {
        string _path;
  
        public ExcelData(string path)
        {
            _path = path;
        }
  
  
        public IExcelDataReader getExcelReader()
        {
            // ExcelDataReader works with the binary Excel file, so it needs a FileStream
            // to get started. This is how we avoid dependencies on ACE or Interop:
            FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read);
  
            // We return the interface, so that 
            IExcelDataReader reader = null;
            try
            {
                if (_path.EndsWith(".xls"))
                {
                    reader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                if (_path.EndsWith(".xlsx"))
                {
                    reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }
                return reader;
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}

 

Note in the above we needed to add the using System.IO and using Excel to our using statements at the top of the file.

From here, we can add some more useful functionality to our class. Let's start with some Workbook metadata, and add a getWorksheetNames() method:

Adding a getWorksheetNames Method to the ExcelData Class:
public IEnumerable<string> getWorksheetNames()
{
    var reader = this.getExcelReader();
    var workbook = reader.AsDataSet();
    var sheets = from DataTable sheet in workbook.Tables select sheet.TableName;
    return sheets;
}

 

So now we begin to see the underlying DataSet and DataTable-based model employed by ExcelDataReader. The call to reader.AsDataSet() returns (wait for it . . .) a System.Data.DataSet object, which contains a DataTableCollection representing each worksheet within the workbook. In the code above, we grab the Name property of each using Linq and return a resulting IEnumerable.

Doing Real Work: Reading Spreadsheet Data Using ExcelDataReader

So, at its most basic, we now know that ExcelDataReader is going to hand us back our old favorite, the ADO.NET DataTable. For the purpose of our example, let's add a very simple getData() method. We're going to add two arguments here. The first is the file path to our Excel Workbook. The other is a Boolean argument used to tell ExcelDataReader whether or not the first row in our Worksheet represents column names. The IExcelDataReader interface conveniently defines a property for just this purpose.

Adding a getData() method to the ExcelData Class:
public IEnumerable<DataRow> getData(string sheet, bool firstRowIsColumnNames = true)
{
    var reader = this.getExcelReader();
    reader.IsFirstRowAsColumnNames = firstRowIsColumnNames;
    var workSheet = reader.AsDataSet().Tables[sheet];
    var rows = from DataRow a in workSheet.Rows select a;
    return rows;
}

 

We could then write some example client code as follows, just to see how it's all working. Say we had some sample spreadsheet data like this:

Sample Spreadsheet Data

sample-spreadsheet-data

We could write some (sadly) familiar-looking code to iterate over the returned IEnumerable<DataRow> and write the results out to the console:

Example Client Code Demonstrating the getData() Method:
void WriteData()
{
    string path = @"D:\Users\John\Documents\Albums.xlsx";
  
    var excelData = new ExcelData(path);
    var albums = excelData.getData("Sheet1");
    foreach (var row in albums)
    {
        Console.WriteLine(Environment.NewLine);
        for (int i = 0; i <= row.ItemArray.GetUpperBound(0); i++)
        {
            Console.WriteLine(row[i].ToString());
        }
    }
}

 

Materializing an Object from Excel Data

The code above is simple, and likely does not resemble what one would actually use in any real application. But you get the picture. It would be a simple matter to write a method mapping the Excel data into an existing POCO class, if were were confident the spreadsheet would contain the proper data and column names. On top of that, depending on our business case, we could implement some staging and validation which would inform the user that the spreadsheet in some way does not meet the requirements for import.

Materializing an Object from the Excel Data:
public class Album
{
    public string Name { get; set; }
    public string Artist { get; set; }
    public string Genre { get; set; }
}
  
  
IEnumerable<Album> getAlbums()
{
    List<Album> albumList = new List<Album>();
    string path = @"D:\Users\John\Documents\Albums.xlsx";
  
    var excelData = new ExcelData(path);
    var albums = excelData.getData("Sheet1");
    foreach (var row in albums)
    {
        var album = new Album()
        {
            Name = row["Name"].ToString(),
            Artist = row["Artist"].ToString(),
            Genre = row["Genre"].ToString()
        };
        albumList.Add(album);
    }
    return albumList;
}

 

Yeah. Remember all that DataTable/DataReader stuff? It's not pretty, but it works. We could re-write our WriteData() method as follows, and again output the data to the console:

void WriteData()
{
    foreach(var album in albums)
    {
        Console.WriteLine("{0} {1} - {2}", album.Name, album.Artist, album.Genre);
    }
}

 

ExcelDataReader is a Purpose-Driven Tool

ExcelDataReader is not as elegant as LinqToExcel, nor as functional as NPOI or the OpenXml libraries. However, unlike LinqToExcel, it has no dependencies on external database engines which may not be available on a remote server (including Windows Azure Websites!), and it affords an interface which is at least familiar. Unlike NPOI or OpenXml, there is very little learning curve.

In a perfect world, the LinqToExcel and ExcelDataReader libraries would be rolled into one. In fact, since they are both OSS, that may be something worth looking at . . .

If your use case involves deployment to Windows Azure Websites, or any other machine where the ACE Database engine is not available, ExcelDataReader will get the job done, and spare the increased complexity of OpenXml or NPOI if you need to import or otherwise read Excel data into your application. Also, ExcelDataReader is cross-platform and will run on Windows, OSX, and Linux (both of the latter using Mono).

If you expect to have (or be able to install) the ACE Database engine, and if cross-platform deployment is not a concern, then LinqToExcel is a better choice from a coding standpoint.

OpenXml and NPOI are both very flexible and widely supported libraries. However, for the simple reading/importing of Excel data, they are a little more difficult to learn and use, and you can count on a lot of ordinal index-based referencing of cells and such.

Additional Resources and Items of Interest

 

Posted on October 17 2013 07:28 PM by jatten     

Comments (0)

ASP.NET MVC: Configuring ASP.NET MVC 4 Membership with a SQL CE Database

Posted on October 6 2013 05:30 PM by jatten in ASP.Net, C#, CodeProject, Web   ||   Comments (0)

technology-of-biug-data-500Recently I needed to set up an ASP.NET MVC project to work with a SQL CE database. I also needed to use SQL CE as the backing store for the ASP.NET Membership feature. This was a simple, quick-and-dirty project for work, and the database requirements were very, very simple. I wanted to use SQL CE for all the data, and also use SQL CE for the membership function so that everything was nicely buttoned up within my site.

Turns out it was not 100% intuitive to make this work, but here is how I did it.

Image by Infocux Technologies | Some Rights Reserved

Why Use SQL CE?

SQL CE is a great choice for applications which are not data-intensive, and/or for which deployment with minimal or no external dependencies is important. SQL CE is an embedded database, and includes all required runtime dependencies within the application. In other words, no need to install and configure SQL Server or SQL Express.

SQL CE was an ideal choice for my little work project, for which storage requirements were minimal, but for which I needed some level of membership/authorization. Once I had the configuration described below in place, development and (especially) deployment were a breeze, with no SQL Server configuration hassle.

The default configuration is for SQL Server Express

Out of the box, an ASP.NET MVC Project is configured to use SQL Server Express as the membership provider. If you do File –> New Project and choose an MVC 4 project type, your  Web.config file will contain a <connectionStrings> element that looks something like this:

<connectionStrings>
<add name="DefaultConnection" 
     connectionString="Data Source=(LocalDb)\v11.0;Initial 
     Catalog=aspnet-ConfigureAspNetforSQLCeMembershipDb-20131006163451;Integrated 
     Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-YourProject-20131006163451.mdf" 
     providerName="System.Data.SqlClient" />
</connectionStrings>

 

If you run the out-of-the-box site and "register", a YourProject.mdf file and an accompanying .log file will be created in the App_Data folder in the VS solution.

If we want to use SQL CE instead of SQL Server Express, we need to change some things up.

Get the Microsoft.AspNet.Providers.SqlCe Package using Nuget

First off, we need to install the Microsoft Universal Provider for SQL CE. There is a handy Nuget package for doing just this. We can either open the Nuget Package Manager Console and do:

Install Microsoft.AspNet.Providers.SqlCE from the Package Manager Console:
PM> Install-Package Microsoft.AspNet.Providers.SqlCE

 

Or, we can go to the Solution Explorer, right-click on the solution, and select "Manage Nuget Packages for Solution. Then, select "Online" from the left-hand menu, and type Microsoft.AspNet.Providers.SqlCe in the search box:

Install Microsoft.AspNet.Providers.SqlCE from the Package Manager Console:

get-sqlCE-provider-with-nuget-package-manager

One you have done this, there will now be an extra entry in the <connectionStrings> element of your Web.config file:

Extra DefaultConnection in Web.config File:
<connectionStrings>
<add name="DefaultConnection" 
     connectionString="Data Source=(LocalDb)\v11.0;Initial 
     Catalog=aspnet-YourProject-20131006170652;Integrated 
     Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-YourProject-20131006170652.mdf" 
     providerName="System.Data.SqlClient" />
<add name="DefaultConnection" 
   connectionString="Data Source=|DataDirectory|\aspnet.sdf" 
   providerName="System.Data.SqlServerCe.4.0" />
</connectionStrings>

 

The new <add name= "DefaultConnection" . . . > node was added for us when we installed the SqlCe provider package. This will now conflict with the previous entry, so delete the original (where providerName = "System.Data.SqlClient" ).

That was easy. But we're not done yet.

Configure the Profile, Membership, and Role Providers in Web.config

In order for SQL CE to work as the back-end store for the membership features of our MVC site, we need to do some tuning on the Profile providers section of our Web.config file as well. If you scroll down from the connectionStrings section, you will find the Profile, Membership, and Role nodes, which looks something like this:

Default Profile, Membership, and Role Provider Configuration in Web.config:
<profile defaultProvider="DefaultProfileProvider">
    <providers>
      <add name="DefaultProfileProvider" type="System.Web.Providers.DefaultProfileProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" />
    </providers>
  </profile>
<membership defaultProvider="DefaultMembershipProvider">
    <providers>
       <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />
    </providers>
</membership>
<roleManager defaultProvider="DefaultRoleProvider">
    <providers>
       <add name="DefaultRoleProvider" type="System.Web.Providers.DefaultRoleProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" />
    </providers>
</roleManager>

 

In order to use SQL CE for our Membership provider, we need to replace the above with the following:

Modified Profile, Membership, and Role Provider Configuration in Web.config:
<profile defaultProvider="DefaultProfileProvider">
  <providers>
    <clear />
    <add name="SimpleRoleProvider" type="WebMatrix.WebData.SimpleRoleProvider, WebMatrix.WebData" />
  </providers>
</profile>
<membership defaultProvider="SimpleMembershipProvider">
  <providers>
    <clear />
    <add name="SimpleMembershipProvider" type="WebMatrix.WebData.SimpleMembershipProvider, WebMatrix.WebData" />
  </providers>
</membership>
<roleManager enabled="true" defaultProvider="SimpleRoleProvider">
  <providers>
    <clear />
    <add name="SimpleRoleProvider" type="WebMatrix.WebData.SimpleRoleProvider, WebMatrix.WebData" />
  </providers>
</roleManager>

 

Once this is done, go to the App_Data folder, and if you originally ran the project and created a SQL Express Database (.mdf and .ldf files), delete those.

Run the Project, and a SQL CE Membership Database will be Created

In theory, that should be it. Once you run the project again, an .sdf file will be created in your App_Data folder and you should be off an running.

In reality, I had to do some fussing around. If you are using IIS Express for running your application in VS (this is the default), you may need to go to the system tray and Exit IIS Express, or at least stop the site, before you run again.

If you have any other issues, please feel free to let me know in the comments section. I really want to provide complete information here, even for simple things like this.

Other Resources

If you are newer to ASP.NET MVC, you might find some of the following helpful:

 

Posted on October 6 2013 05:30 PM by jatten     

Comments (0)

About the author

My name is John Atten, and my username on many of my online accounts is xivSolutions. I am Fascinated by all things technology and software development. I work mostly with C#, Java, SQL Server 2012, learning ASP.NET MVC, html 5/CSS/Javascript. I am always looking for new information, and value your feedback (especially where I got something wrong!). You can email me at:

jatten@typecastexception.com

Web Hosting by