Step 2. Setting up the database

Step 2. Setting up the database

Skip and go to the next step

Before you actually start to 'write' your application, you must create a database to put information into or take it from. On this step, you'll know how to create MSSQL database and populate it with data.

Here you have several variants:

  1. Create database and all the needed tables in the project manually;
  2. Create database and populate it with tables on the server (automatically) and then connect the database to the project.

Below we will describe the second variant.

Step 2.1. ASP.NET Membership

For authorization and roles management we will use .Net Membership and Roles (details). And to not create the related tables manually, we will use the build-in .NET Server Registration Tool.
So, all that was left to do is to run ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe, details). The tool comes with the .NET framework and generally can be found under: C:\WINDOWS\Microsoft.NET\Framework \%version%\aspnet_regsql.exe (the exact location you can know here).

Your action: Open command window and run the tool with the following keys:

aspnet_regsql.exe -E -S <servername> -d <databasename> -A mr

step 2.1

Back to top

Step 2.2. Tables

If you ran the tool successfully it will create 6 tables and database TaskManager (if it doesn’t exist yet):

  • aspnet_Applications
  • aspnet_Membership
  • aspnet_Roles
  • aspnet_SchemaVersions
  • aspnet_Users
  • aspnet_UsersInRoles

All these tables are used by ASP.NET Membership for providing its functionality. Leave them as they are and move to creating tables that will store information concerning tasks and their statuses, i.e. create manually 2 tables:

  • tasks - to store tasks;
  • statuses - to store possible values of task statuses.

Your action: in the database TaskManager, create 2 tables with the columns shown in the images below:

  • Tasks table

    tasks table
    Note, the id field must have the primary key and identity column properties enabled.

  • Statuses table

    statuses table
    Enable the identity column property for the id field as well.

So, in the end you must have 8 tables:

table creation

Back to top

Step 2.3. Web.config. Connecting to the server

To access the server database you should make the appropriate settings in the Web.config file. In the file you should rewrite the following configuration elements:

  • connectionStrings
  • authentication
  • membership
  • profile
  • roleManager

Your actions:

  1. Move to Solution Explorer and open the Web.config file.
  2. In the opened file rewrite configuration elements as follows:

    • connectionString:

          <add name="TaskManagerConnectionString" connectionString="Server=SQLEXPRESS\DHTMLX;Database=TaskManager;Integrated Security=SSPI;User Instance=true"
            providerName="System.Data.SqlClient" /> 
    • authentication:

      <authentication mode="Forms">
          <forms loginUrl="~/Account/LogOn" timeout="2880" />
    • membership:

      <membership defaultProvider="CustomMembershipProvider">
              <add name="CustomMembershipProvider" type="System.Web.Security.SqlMembershipProvider"
                   applicationName="/" />
    • profile:

              <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="TaskManagerConnectionString" applicationName="/" />
    • roleManager:

      <roleManager enabled="true">
              <add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="TaskManagerConnectionString" applicationName="/" />
              <add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/" />

      The remaining configuration elements leave as they are.

Back to top

Step 2.3. Connecting a project to server

We have already provided the connection settings. Now we are left to add the related connection, so you can use the database in your project.

Your actions:

  1. Move to Server Explorer and right click Data Connections. In the opened menu select Add Connection →Microsoft SQL Server and click the button Continue;
  2. In the opened window fill in the fields and click the button Ok.

 step 2.4

previous button step two next button

comments powered by Disqus