IT Nota

How to Setup SQL Express LocalDB in ASP.NET

Microsoft SQL Server Express LocalDB should be used in place of the SQL Server Express user instance feature which is deprecated. It is more suitable for developers as it runs on-demand (instead as a service) with your Windows credential and without the need the need of complex configurations.

In Visual Studio 2013, if you want to have it setup a default database using a SQL Server LocalDb in your Web.Config, make sure you choose Individual User Account as your authentication mode.

New ASP.NET Project - Select a template

Then you can modify the parameters and commit Update-Database later.

<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, 
         visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=(LocalDb)v11.0;AttachDbFilename=|DataDirectory|aspnet-WebApplication1-20131218041336.mdf;Initial Catalog=aspnet-WebApplication1-20131218041336;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
  <appSettings>
    ...

Further Reading

SQL Server 2012 Express LocalDB
Introducing LocalDB, an improved SQL Express

How to Setup User Access and Roles in Production SQL Server

Setting up ASP.NET membership and roles in development is very easy to do since out of the box, it’s already configured to use the built-in SQL Server Express provider. To set it up, you just need to launch ASP.NET Web Site Administration Tool by clicking on ASP.NET Configuration icon in the Solution Explorer or choose the ASP.NET Configuration option from the Project menu.

ASP.NET Configuration icon

Once you complete the configuration, a SQL Server Express database named Aspnetdb.mdf is created locally and ready to use. While this is very practical and suitable for development, it’s definitely not the best practice for production. It is highly recommended that a full-blown SQL Server is used in the production environment instead.

For a more detailed instructions on how to setup the user and roles in localhost, refer to the step-by-step guide from Scott Guthrie, Walkthrough: Managing Web Site Users with Roles included in the Further Reading on the bottom of this post.

Setting Up Application Services in Production

Configuring ASP.NET 4.0 Application Services to Use SQL Server 2008 in production database can also be done quite painlessly. The first step is to find ASP.NET SQL Server Setup Wizard on your local ASP.NET installation. Open a command-line window by opening the Start Menu and typing ‘cmd’ (without quotes) in the search box.

Change to directory C:\Windows\Microsoft.NET\Framework\v4.0.30319
or C:\Windows\Microsoft.NET\Framework\v2.0.50727 if you’re still using .NET Framework 2.0 or 3.5.

Look for a file called aspnet_regsql.exe and run it. By default when run without any parameters, it actually uses -W (Wizard mode) argument. You can use -? argument to see the list of all available parameters.

aspnet_regsql.exe from Command Line Window

ASP.NET SQL Server Setup Wizard Welcome Screen

ASP.NET SQL Server Setup Wizard Setup Option

ASP.NET SQL Server Setup Wizard Select the Server and Database

Substitute SQLSERVERPROD with your SQL Server Production server name and YOUR_DB_NAME with the database name where you want your application services configured. Then click Next >.

ASP.NET SQL Server Setup Wizard Confirm Your Settings

Click Next > and the wizard will run for a while until you see this screen:

ASP.NET SQL Server Setup Wizard The database has been created or modified.

Click Finish and a new and empty Membership and Roles infrastructure is ready to use in your production database.

Setup Web.Config Parameters

The second step is to update the ConnectionStrings in your Web.config file. If you use Visual Studio 2010 and .NET Framework 4.0, it’s even better as you only need to add the settings for your PROD in Web.Release.config file and never need to worry about switching the parameters between the DEV and PROD afterwards.

Web.config

<connectionStrings>
  <add name="AspNetSqlRoleProvider" 
    connectionString="Server=.SQLEXPRESS;
    Database=YOUR_DB_NAME_DEV;
    Trusted_Connection=yes" 
    providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
  <roleManager enabled="true" 
    defaultProvider="LocalSqlServer">
    <providers>
      <clear />
      <add name="AspNetSqlRoleProvider"
        applicationName="/" 
        connectionStringName="LocalSqlServer" 
        type="System.Web.Security.SqlRoleProvider" />
    </providers>
  </roleManager>
</system.web>

Web.Release.config

<connectionStrings>
  <add name="AspNetSqlRoleProvider" 
    connectionString="Data Source=SQLSERVERPROD;
    Persist Security Info=True;
    Initial Catalog=YOUR_DB_NAME;
    User ID=myuserid;Password=dbpassword123"
    providerName="System.Data.SqlClient"
    xdt:Transform="SetAttributes" xdt:Locator="Match(name)" />
</connectionStrings>

The new database is going to be empty and this is what I like best as I don’t want to copy over various test user ids to production.

Further Reading:
Walkthrough: Managing Web Site Users with Roles
Configuring ASP.NET 2.0 Application Services to Use SQL Server 2000 or SQL Server 2005
Users and Roles On The Production Website (C#)

Set Default Start Page to Web Forms on a Hybrid MVC and Web Forms Application

As one of ASP.NET developers ever asked to upgrade an existing ASP.NET WebForms 3.5 application to ASP.NET 4 and added ASP.NET MVC pages to it, I found the tip, Integrating ASP.NET MVC 3 into existing upgraded ASP.NET 4 Web Forms Applications from Scott Hanselman very helpful.

After I followed all the steps provided, I was still baffled by the inconsistent behavior of the web app itself. When the app is run on ASP.NET Development Server locally, the default landing page is my default.aspx, which is the intended result. For some reason, every time I deploy it to production, the default changes to the Index action in MVC 3 HomeController.

It must be the combination of me being a novice in MVC 3 and ASP.NET routing altogether that I had to struggle with this issue for at least a few days before finding out that the solution actually was as simple as reversing the order of my ASP.NET routing.

Here’s what I originally had in my Global.asax.cs:

public static void RegisterRoutes(RouteCollection routes)
{
  routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
  routes.IgnoreRoute("{myWebForms}.aspx/{*pathInfo}");

  // MVC default
  routes.MapRoute(
    "Default",                          // Route name
    "{controller}/{action}/{id}",       // URL with parameters
    new { controller = "Home", 
          action = "Index",
          id = UrlParameter.Optional }  // Parameter defaults
  );

  // Web Forms default
  routes.MapPageRoute(
    "WebFormDefault",
    "",
    "~/default.aspx");
}

Here’s what I have now that fixes the issue on the production:

public static void RegisterRoutes(RouteCollection routes)
{
  routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
  routes.IgnoreRoute("{myWebForms}.aspx/{*pathInfo}");

  // Web Forms default
  routes.MapPageRoute(
    "WebFormDefault",
    "",
    "~/default.aspx");

  // MVC default
  routes.MapRoute(
    "Default",                          // Route name
    "{controller}/{action}/{id}",       // URL with parameters
    new { controller = "Home", 
          action = "Index",
          id = UrlParameter.Optional }  // Parameter defaults
  );
}

So that was the only thing I needed to do to fix the problem. The only unsolved mystery for me is I still have no answer on why the ASP.NET Development Server seems to default to the web forms automatically while the IIS 7.5 is more nitpicky about it.

Further Reading

ASP.NET Routing

Map Model Classes to Differently Named Tables in Entity Framework

For a few days, I kept running into this minor but annoying issue using EF 4.2 with MVC3 where the web application refused to map a class (Profile) to an existing table (tblProfile) in a database [CPDB] and instead created a brand new table with the same name on a different database [CPDB.Domain.Entities.ProfileEntities].

What confused me even more was I did this step before for another class (CarAssignment) and it didn’t cause any problems. It took me a while before I figured out the small difference on how I setup the two entities.

CarAssignment.cs

namespace CPDB.Domain.Entities
{
   [Table("tblCar")]
   public class CarAssignment
   {
      [Key]
      public int CarId { get; set; }
      [StringLength(25)]
      public string CompanyType { get; set; }
      [...]
   }
}

Profile.cs

namespace CPDB.Domain.Entities
{
   [Table("tblProfile")]
   public class Profile
   {
      [Key]
      public int ProfileId { get; set; }
      [Required]
      [StringLength(30), Display(Name = "First name")]
      public string FirstName { get; set; }
      [...]

SqlCarAssignmentRepository.cs

namespace CPDB.Domain.Concrete
{
   public class SqlCarAssignmentsRepository
       : ICarAssignmentsRepository
   {
      private CarEntities db = new CarEntities();
      [...]

SqlProfileRepository.cs

namespace CPDB.Domain.Concrete
{
   public class SqlProfilesRepository : IProfilesRepository
   {
      private ProfileEntities db = new ProfileEntities();

      private IQueryable<Profile> ProfilesTable;
      [...]

NinjectControllerFactory.cs

namespace CPDB.WebUI.Infrastructure
{
   public class NinjectControllerFactory : DefaultControllerFactory
   {
      private IKernel kernel =
          new StandardKernel(new AMPServices());

      protected override IController GetControllerInstance(
          RequestContext requestContext,
          Type controllerType)
      {
         if (controllerType == null)
            return null;
         return (IController)kernel.Get(controllerType);
      }

      private class AMPServices : NinjectModule
      {
         public override void Load()
         {
            Bind()
            .To()
            .WithConstructorArgument("connectionString",
            ConfigurationManager.ConnectionStrings["CarEntities"]
            .ConnectionString);

            Bind()
            .To()
            .WithConstructorArgument("connectionStrings",
            ConfigurationManager.ConnectionStrings["DifferentName"]
            .ConnectionString);
         }
      }
   }
}

ConnectionStrings in Web.config file:

<connectionStrings>
  <add name="DifferentName" connectionString="Server=.SQLEXPRESS;
       Database=CPDB;Trusted_Connection=yes"
       providerName="System.Data.SqlClient" />
</connectionStrings>

If we just stop here, the web application will create a new table dbo.tblProfile in a new database. In order to use an existing table and database, I had to add line 8-10 on ProfileEntities class.

CarEntities.cs

namespace CPDB.Domain.Entities
{
   public class CarEntities : DbContext
   {
      public DbSet<CarAssignment> CarAssignments { get; set; }
   }
}

ProfileEntities.cs

namespace CPDB.Domain.Entities
{
   public class ProfileEntities : DbContext
   {
      public ProfileEntities()
         : base("DifferentName")
      { }

      public DbSet<Profile> Profiles { get; set; }
   }
}

If you want to use a different name, you need to alter the constructor on the DbContext. You don’t have to necessarily specify the physical database name, instead, you can use your connection strings parameter in the Web.Config file.

Recent Posts

Tags

.NET .NET Core AdSense ASP.NET Cdonts Dll Classic ASP Code Editor ETL FSharp Genesis Framework Git Google HP Asset Manager HTML5 Hugo IIS Information Security Internet Internet Information Services iOS JAMStack Linux macOS Microsoft Microsoft SQL Server MVC PHP PowerShell Python Simple Mail Transfer Protocol Smtp Server SQL SQL Server SSIS SSMS SSRS Sublime Text Visual Studio Visual Studio Code VPN Windows Windows 8 Windows 10 Windows 2012 Windows Server