Tuesday, February 19, 2013

ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe)


The ASP.NET SQL Server Registration tool is used to create a Microsoft SQL Server database for use by the SQL Server providers in ASP.NET, or to add or remove options from an existing database. The Aspnet_regsql.exe file is located in the [drive:]\%windir%\Microsoft.NET\Framework\version folder on your Web server.
You can run Aspnet_regsql.exe without any command-line arguments to run a wizard that will walk you through specifying connection information for your SQL Server installation, and installing or removing the database elements for the membership, role management, profile, Web Parts personalization, and health monitoring features. (Setting session state and SQL cache dependency are not covered by the wizard.) You can also run Aspnet_regsql.exe as a command-line tool to specify database elements for individual features to add or remove, using the options listed in the table below.
Aspnet_regsql.exe <options>
OptionDescription
-?
Prints Aspnet_regsql.exe Help text in the command window.
-W
Runs the tool in wizard mode. This is the default if no command-line arguments are specified.
-C <connection string>
Specifies the connection string to the computer running SQL Server where the database will be installed, or is already installed. This option is not necessary if you specify only the server (-S) and login (-U and -P, or -E) information.
-S <server>
Specifies the name of the computer running SQL Server where the database will be installed, or is already installed.
-U <login ID>
The SQL Server user ID to log in with. This option also requires the password (-P) option. This option is not necessary if you are authenticating using Windows credentials (-E).
-P <password>
The SQL Server password to log in with. This option also requires the user ID (-U) option. This option is not necessary if you are authenticating using Windows credentials (-E).
-E
Authenticates using the Windows credentials of the currently logged-on user.
-sqlexportlonly <filename>
Generates a SQL script file that can be used to add or remove the specified features. The specified actions are not performed.
OptionDescription
-A all|m|r|p|c|w
Adds support for one or more ASP.NET application services. Service identifiers can be specified together or separately. The following identifiers are used for ASP.NET application services:
all - All services, including common tables and stored procedures shared by the services
m - Membership
r - Role Manager
p - Profile
c - Web Parts Personalization
w - Web Events
-R all|m|r|p|c|w
Removes support for one or more application services. Service identifiers can be specified together or separately. The following identifiers are used for ASP.NET application services:
all - All services, including common tables and stored procedures shared by the services
m - Membership
r - Role Manager
p - Profile
c - Web Parts Personalization
w - Web Events
-Q
Runs the tool in quiet mode and does not confirm before removing an application service.
-d <database>
Specifies the name of the database to create or modify for use with application services. If the database is not specified, the default database name of "aspnetdb" is used.
NoteNote
The database elements that are installed in the application services database will always be owned by the SQL Server database owner account (dbo). You do not need to be a system administrator for the computer running SQL Server to install the application services database. To install the application services database, a SQL Server login must be permitted to the db_ddladmin and dd_securityadmin roles for the SQL Server database.
OptionDescription
-d <database>
Specifies the name of the database to use with SQL cache dependency. The database can optionally be specified using the connection string option, -C.
-ed
Enables a database for SQL cache dependency.
-dd
Disables a database for SQL cache dependency.
-et
Enables a table for SQL cache dependency. The -t option must also be included in the parameter string.
-dt
Disables a table for SQL cache dependency. The -t option must also be included in the parameter string.
-t <table>
Specifies the table name to enable or disable for use with SQL cache dependency. This option must be used with the -et or -dtoptions.
-lt
Lists all tables enabled for SQL cache dependency.
OptionDescription
-d <database>
Specifies the name of the database to store session state. This option must be used if -sstype is set to c.
-ssadd
Adds support for SQL Server mode session state.
-ssremove
Removes support for SQL Server mode session state.
-sstype t|p|c
Specifies the type of session state to use:
t - Temporary. Session state data is stored in the SQL Server tempdb database. Stored procedures for managing session state are installed in the SQL Server ASPState database. Data is not persisted if you restart SQL. This is the default.
p - Persisted. Both session state data and stored procedures are stored in the SQL Server ASPState database.
c - Custom. Both session state data and stored procedures are stored in a custom database. The database name must be specified using the -d option.
You can set several types of options with the ASP.NET SQL Server Registration tool. You can specify a SQL connection, specify which ASP.NET application services use SQL Server to manage information, indicate which database or table is used for SQL cache dependency, and add or remove support for using SQL Server to store procedures and session state.
Several ASP.NET application services rely on a provider to manage storing and retrieving data from a data source. Each provider is specific to the data source. ASP.NET includes a SQL Server provider for the following ASP.NET features:
When you install ASP.NET, the Machine.config file for your server includes configuration elements that specify SQL Server providers for each of the ASP.NET features that rely on a provider. These providers are configured, by default, to connect to a local user instance of SQL Server Express 2005. If you change the default connection string used by the providers, then before you can use any of the ASP.NET features configured in the machine configuration, you must install the SQL Server database and the database elements for your chosen feature using Aspnet_regsql.exe. If the database that you specify with Aspnet_regsql.exe does not already exist (aspnetdb will be the default database if one is not specified on the command line), then the current user must have rights to create databases in SQL Server as well as to create schema elements within a database.
Each SQL Server provider can use the same SQL Server database to store the data for that particular feature. Each feature can be used individually, or in conjunction with other features. For example, you could use role management by itself or in conjunction with the user information managed by membership.
For more information about SQL Server providers and ASP.NET, see Implementing a Membership ProviderImplementing a Profile Provider, andImplementing a Role Provider.

SQL Cache Dependency

An advanced feature of ASP.NET output caching is SQL cache dependency. SQL cache dependency supports two different modes of operation: one that uses an ASP.NET implementation of table polling and a second mode that uses the query notification features of SQL Server 2005. Aspnet_regsql.exe can be used to configure the table-polling mode of operation. SQL cache dependency enables you to cache pages that are dependent on data from SQL Server tables. You can configure SQL Server and ASP.NET to cache page requests, reducing server workload, until the data on which the page depends has been updated in SQL Server. SQL cache dependency is useful for data such as product catalogs or customer registration information that remains comparatively static. Note that you must configure SQL Server to provide proper notification to ASP.NET about changes in dependent data when you use the ASP.NET table-polling mode of SQL cache dependency. Thus, you will need administrative privileges to configure the server. For more information about SQL cache dependency, see Walkthrough: Using ASP.NET Output Caching with SQL Server.

Session State

ASP.NET session state is designed to enable you to easily store user session data in different sources for your ASP.NET applications. By default, session state values and information are stored in memory within the ASP.NET process. Alternatively, you can store session data in a SQL Server database, where it can be shared by multiple Web servers. For more information about session state, see Implementing a Session-State Store Provider and Session-State Modes.
If the database that you specify for session state with Aspnet_regsql.exe does not already exist, then the current user must have rights to create databases in SQL Server as well as to create schema elements within a database. If the database does exist, then the current user must have rights to create schema elements in the existing database.
To install the session state database on SQL Server, run Aspnet_regsql.exe tool supply the following information with the command:
  • The name of the SQL Server instance, using the -S option.
  • The logon credentials for an account that has permission to create a database on a computer running SQL Server. Use the -E option to use the currently logged-on user, or use the -U option to specify a user ID along with the -P option to specify a password.
  • The -ssadd command-line option to add the session state database.
By default, you cannot use Aspnet_regsql.exe to install the session state database on a computer running SQL Server 2005 Express Edition. For more information about using SQL Server 2005 Express Edition to store session state, see Session-State Modes.
You can run Aspnet_regsql.exe without any command-line arguments to run a wizard that will walk you through specifying connection information for your SQL Server database and installing or removing the database elements for supported features. You can also run Aspnet_regsql.exe as a command-line tool to specify database elements for individual features to add or remove. To specify SQL cache dependency settings or set up session state, you must use the command-line tool.
To run the wizard, run Aspnet_regsql.exe without any command-line arguments, as shown in the following example.
C:\%windir%\Microsoft.NET\Framework\<versionNumber>\aspnet_regsql.exe
The ASP.NET SQL Registration tool is commonly used with the -A or -R option to specify which features use a SQL Server provider. The -A option allows you to add support for one or more features, whereas the -R option allows you to remove a feature. The following command installs the database elements for membership and role management on the local computer running SQL Server using Windows authentication.
aspnet_regsql.exe -E -S localhost -A mr
To set up SQL cache dependency, you need administrative privileges or the administrative account and password. The following command enables SQL cache dependency for the Employees table in the Northwind database.
aspnet_regsql.exe -S <Server> -U <Username> -P <Password> -ed -d Northwind -et -t Employees
The following command creates a database named ASPState on a SQL Server instance named "SampleSqlServer" and specifies that session data is also stored in the ASPState database.
aspnet_regsql.exe -S SampleSqlServer -E -ssadd -sstype p

MVC - How to Use Role Manager


Overview

ASP.NET version 2.0 provides a new role manager feature that includes a roles management API that allows you to create and delete roles and assign and remove users from roles. The role manager stores its data in an underlying data store that it accesses through an appropriate role provider for that data store.
The main benefits of using role manager are that it allows you to look up users' roles without writing and maintaining code. Additionally, the role providers offer a consistent way for you to check the role membership of your users, regardless of the underlying data store. Therefore, if your role store were to change tomorrow, you would only need to change the configuration settings to make your code work.
Supplied role providers include:
  • SqlRoleProvider. This is used where the role store is kept in SQL Server.
  • WindowsTokenRoleProvider. This is a read-only provider that retrieves role information for a Windows user account based on the account's Windows security group membership. You cannot create, add to, or delete roles with this provider.
  • AuthorizationStoreRoleProvider. This is used if your application uses Authorization Manager (AzMan). It uses an AzMan policy store in an XML file, in Active Directory, or in Active Directory Application Mode (ADAM) as its role store. It is typically used in an intranet or extranet scenario where Windows authentication and Active Directory is used for authentication.
To perform role management, your ASP.NET application must be able to identify and authenticate its users in some way. For example, it might use Windows authentication or Forms authentication.
This How To shows you how to set up and configure a role store and a role provider and use role-based authorization in your ASP.NET applications. Additionally, it demonstrates some of the basic roles API calls available to work with roles programmatically.

Summary of Steps

To use the role manager in your application, perform the following steps:
  • Step 1. Configure your role store.
  • Step 2. Configure your role provider in Web.config.
  • Step 3. Create and assign roles.
  • Step 4. Perform role-based authorization.

Step 1. Configure Your Role Store

In this step, you prepare the role store used to maintain role details. The setup details vary according to the store and associated provider that you choose to use.

Using SqlRoleProvider

If you want to store roles in SQL Server, you use the SqlRoleProvider. By default, roles are stored in a database named Aspnetdb in a SQL Express database instance in the \app_data folder beneath your application's virtual directory root folder. You can also configure the SqlRoleProvider to use a local or remote instance of SQL Server.
To use a SQL Express database role store in the Website \app_data folder
You do not have to create or configure this database. The first time you perform an operation that uses the role management API, ASP.NET automatically creates a database named Aspnetdb, configures it, and sets appropriate permissions on it.
ASP.NET configures the SQL Express database with a database login for the default accounts used to run ASP.NET applications (Network Service on Windows Server 2003 and ASPNET on Windows 2000) and grants them full access to the Aspnetdb database.
If you have configured ASP.NET to run using a custom service account, you must create a SQL login for that account, and add the login to theaspnet_Roles_FullAccess role in the Aspnetdb database.
For more information about running ASP.NET with a custom identity, see How To: Create a Service Account for an ASP.NET 2.0 Application.
To install the role management database in SQL Server
  1. Log on to Windows using an account that has administration rights for your SQL Server instance.
  2. Open a command prompt window and execute the following command to install the role management database.
    Aspnet_regsql.exeES sqlinstanceA r
    Note that this program is located in the %windir%\Microsoft.NET\Framework\{version} folder.
    When you type the command, replace version with the version of the .NET Framework that you are using and sqlinstance with the name of the SQL Server instance you want to install the role management database in.
    You will see the following output:
    Start adding the following features:
    Rolemanager
    ............
    Finished.
      
    Note   For information about the switches for Aspnet_regsql.exe, type Aspnet_regsql.exe -? at the command prompt.
To grant permissions in the database
You need to create a database login for your Web application's process account; by default, this is the Network Service account. If you have configured a custom account to run ASP.NET, you must grant permissions to that account.
Note   If you have configured your ASP.NET application to use impersonation, your ASP.NET application executes using the security context of the original caller. To access data in a SQL Server database, you must usually create a database login for the original caller and grant access to the required data.
However, the role management system accesses the Aspnetdb database by using the process identity regardless of whether your application uses impersonation.
  1. Open SQL Server Enterprise Manager, expand Security, and then expand Logins for the server hosting the role management database. Create a new login for your Web application's process account (for example, NT AUTHORITY/Network Service).
  2. Set the default database to Aspnetdb.
  3. Under the Databases node in Enterprise Manager, expand the Aspnetdb database entry, and then click Roles. Right-click aspnet_Roles_FullAccess, and then click Properties. In the Database Role Properties dialog box, click Add, and then select the database login you created in the previous step to add the login to that role.
    Note   Aspnet_regsql.exe creates three database roles with differing levels of access to the role management database:
    • FullAccess gives rights to create and delete roles, and add and remove users from those roles.
    • BasicAccess allows role membership checks for the current user only.
    • ReportingAccess allows a page to check which roles exist and which users belong to them.

Using WindowsTokenRoleProvider

You use the WindowsTokenRoleProvider with ASP.NET applications that use Windows authentication. It is a read-only provider that retrieves role information for a Windows user based on Windows security groups. You cannot create, add to, or delete roles with this provider. Instead, you must use the Windows Computer Management or Active Directory Users and Computers administration tools.
The Windows security system acts as role store for this provider, so no additional configuration is required to set up the role store.

Using AuthorizationStoreRoleProvider

Use the AuthorizationStoreRoleProvider to store roles data in an AzMan policy store in an XML file, in Active Directory, or ADAM.
AzMan is supported on Windows Server 2003, Windows 2000 Server, and on Windows XP Professional. For more information about how to install AzMan and configure an AzMan policy store, see the following resources:

Step 2. Configure Your Role Provider in Web.config

In this step, you configure the appropriate role provider in your application's Web.config file.

Using SqlRoleProvider

To use the role store in the default SQL Express instance in a database in your Web site's \app_dir folder, add the following configuration to your application's Web.config file.
<system.web>
    <roleManager enabled="true" />
</system.web>
  
This configuration enables role management and causes your application to use the default provider named AspNetSqlRoleProvider defined in the Machine.config file. This uses the local SQL Express instance.
To use a role store in SQL Server, add a connection string to point to your role database and add a role provider definition in the Web.config file, as shown here.
<configuration>
  <connectionStrings>
    <add name="SqlRoleManagerConnection" 
         connectionString="Data Source=sqlinstance;
                          Initial Catalog=aspnetdb;Integrated Security=SSPI;">
    </add>
  </connectionStrings>
</configuration>

<roleManager enabled="true" defaultProvider="SqlRoleManager">
  <providers>
    <add name="SqlRoleManager" 
         type="System.Web.Security.SqlRoleProvider"
         connectionStringName="SqlRoleManagerConnection"
         applicationName="MyApplication" />
  </providers>
</roleManager>
  

Using WindowsTokenRoleProvider

The Windows security system acts as role store for this provider, and Windows groups represent roles. You can use this provider when you use Windows authentication. To do this, add the following configuration to your application's Web.config file.
<authentication mode="Windows" />
<roleManager enabled="true" 
             defaultProvider="AspNetWindowsTokenRoleProvider" />
  
This configuration enables role management and uses the role provider named AspNetWindowsTokenRoleProvider, which is defined in the Machine.config file.

Using AuthorizationStoreRoleProvider

The AuthorizationStoreRoleProvider is used to store roles data in an AzMan policy store in an XML file, in Active Directory, or in ADAM. To configure this provider, add a connection string to point to the relevant policy store and then add a role provider definition in the Web.config file.
The following connection string refers to an XML-based policy store contained in a file named Azmanstore.xml.
<configuration> 
  <connectionStrings> 
    <add name="AzManPolicyStoreConnectionString" 
         connectionString="msxml://c:/RolesData/azmanstore.xml" />
  </connectionStrings> 
</configuration>
  
The following connection string refers to an ADAM-based policy store.
<configuration> 
  <connectionStrings> 
    <add name="AzManPolicyStoreConnectionString" 
       connectionString= "msldap://servername:port/CN=AzManADAMStore,
        OU=SecNetPartition,O=SecNet,C=US"/>
  </connectionStrings> 
</configuration>
  
The following configuration shows how to enable role management and use the provider named RoleManagerAzManADAMProvider. This uses the connection string shown earlier to connect to an ADAM policy store.
<roleManager 
    enabled="true" 
    cacheRolesInCookie="true" 
    defaultProvider="RoleManagerAzManADAMProvider"
    cookieName=".ASPXROLES" 
    cookiePath="/" 
    cookieTimeout="30" 
    cookieRequireSSL="false" 
    cookieSlidingExpiration="true"
    createPersistentCookie="false" 
    cookieProtection="All">
    <providers>
        <add name="RoleManagerAzManADAMProvider"
     type="System.Web.Security.AuthorizationStoreRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, publicKeyToken=b03f5f7f11d50a3a"
             connectionStringName="AzManPolicyStoreConnectionString" 
             applicationName="AzManDemo"/>
    </providers>
</roleManager>
  

Step 3. Create and Assign Roles

In this step, you create roles for your application and assign users to those roles. There are several methods you can use to create and assign roles. Using them depends on how your application authenticates its users and which role provider it uses. The various methods for creating and assigning users to roles include:
  • At development time, you can use the ASP.NET configuration tool.
  • If you are using the AuthorizationStoreRoleProvider, you can use the AzMan administrator Microsoft Management Console (MMC) snap-in.
  • You can create roles programmatically by using either the role management APIs or, if you are using the SqlRoleProvider, by executing SQL scripts to add them to the database directly.
  • If you are using the WindowsTokenRoleProvider, you use the Windows Computer Management tool or Active Directory Users and Computers to create Windows groups which are used as roles.

Using the ASP.NET Web Site Configuration Tool

You can use the ASP.NET Web Site Configuration tool from Visual Studio .NET 2005 at development time if your application uses Forms authentication and you have configured your application to use the ASP.NET membership feature. If your application uses Windows authentication, you cannot use this tool and you must use either the Roles API or the AzMan administrator MMC snap-in as described in the next section.
To assign users to roles by using the ASP.NET Web Site Configuration tool
  1. Create roles by using the ASP.NET configuration tool by performing the following steps:
    1. Start Visual Studio .NET 2005.
    2. On the Website menu, click ASP.NET Configuration.
    3. On the Home tab, click Security.
    4. Click Create or Manage Roles.
    5. Use the form to add your application roles.
  2. Assign users to roles as follows:
    1. In Visual Studio .NET, click ASP.NET Configuration on the Website menu.
    2. On the Home tab, click Security.
    3. Click Create or Manage Roles.
    4. In the Add/Remove Users column, click Manage for a role you have created. This opens a form where you can add or remove users from the role.

Using the AzMan Administrator MMC Snap-In

This tool is provided for applications that use an AzMan store to manage its users and roles. It is particularly convenient for applications using Windows authentication that cannot use the ASP.NET configuration tool to assign users to roles.
Note   For more information about using the AzMan administrator MMC snap-in to create and manage roles, see How To: Use Authorization Manager (AzMan) with ASP.NET 2.0.

Using SQL Scripts

If you have suitable permissions on the role management database, you can run a SQL script to call the various stored procedures provided with the Aspnetdb database. For example, the following script creates a new role in an application and adds a user to it.
EXEC aspnet_Roles_CreateRole 'ThisApplication', 'NewRole'
EXEC aspnet_UsersInRoles_AddUsersToRoles 'ThisApplication', 'ThisUser', 'NewRole', 8 
  
There are 19 role-managing stored procedures created inside the role management database. Most of these stored procedures have purposes analogous to calls in the role management API.

Using the Role Management APIs

You can assign users to roles or remove users from roles by using methods of the System.Web.Security.Roles class. You can also check for the user's role membership and authorize as appropriate.
Note   Because the WindowsTokenRoleProvider is read-only, it supports only the IsUserInRole and GetRolesForUser methods.
The following code shows how to create new roles.
using System.Web.Security;

if (!Roles.RoleExists("TestRole"))
{
  Roles.CreateRole("TestRole");
}
  
Note   Role names are not case sensitive. If you attempt to create the same role twice, an exception is thrown.
The following code shows how to add uses to roles.
// Example 1 - Add one user to one role
Roles.AddUserToRole("TestOne", "ExampleRole1");

// Example 2 - Add one user to several roles
Roles.AddUserToRoles("TestTwo", 
  new string[] { "ExampleRole1", "ExampleRole2" });

// Example 3 - Add several users to one roles
Roles.AddUsersToRole(
  new string[] { "TestTwo", "TestThree" }, "ExampleRole3");

// Example 4 - Add several users to several roles
Roles.AddUsersToRoles(
  new string[] { "TestThree", "TestFour" }, 
  new string[] { "ExampleRole4" }); 
  
The following code shows how to remove users from roles.
// Example 1 - Add one user to one role
Roles.RemoveUserFromRole("TestOne", "ExampleRole1");

// Example 2 - Add one user to several roles
Roles.RemoveUserFromRoles("TestTwo", 
  new string[] { "ExampleRole1", "ExampleRole2" });

// Example 3 - Add several users to one roles
Roles.RemoveUsersFromRole(
  new string[] { "TestTwo", "TestThree" }, "ExampleRole3");

// Example 4 - Add several users to several roles
Roles.RemoveUsersFromRoles(
  new string[] { "TestThree", "TestFour" }, 
  new string[] { "ExampleRole4" }); 
  
Note   Both the AddUser and RemoveUser methods throw a TargetInvocationException if you specify a role that does not exist or if you specify an invalid Windows user account name. The inner exception gives the reason for the failure. None of the methods mentioned earlier can be used against a WindowsTokenRoleProvider.

Step 4. Perform Role-Based Authorization

In this step, you create test applications that use role management. This step provides two samples:
  • The first sample uses the SqlRoleProvider or AuthorizationStoreRoleProvider, in which the application assigns a role to a user, removes a role from a user, and tests for role membership.
  • The second sample uses the WindowsTokenRoleProvider to check which Windows groups (roles) the current user is a member of. TheWindowsTokenRoleProvider supports only the IsUserInRole and GetRolesForUser methods of the role management API, and it does not allow you to create, assign to, or remove users from Windows groups.
Both of these sample applications use Integrated Windows authentication in Microsoft Internet Information Services (IIS) to authenticate callers. This is a requirement when using the WindowsTokenRoleProvider, but if you are using the SqlRoleProvider or AuthorizationStoreRoleProvider, you could use alternative authentication such as forms authentication.

Sample: Using SqlRoleProvider or AuthorizationStoreRoleProvider

This sample uses the SqlRoleProvider or AuthorizationStoreRoleProvider.
To test role management with SqlRoleProvider or AuthorizationStoreRoleProvider
  1. Use Visual Studio.NET 2005 to create a Web site, add a Web.config file, and configure the role store and SqlRoleProvider orAuthorizationStoreRoleProvider as described in steps 1 and 2 of this How To.
  2. Using the Internet Information Services MMC snap-in, edit the properties of the Web site. Edit the Anonymous access and authentication control on theDirectory security tab. Clear the Anonymous access check box and select the Integrated Windows authentication check box.
  3. In the Web.config file, enable Windows authentication.
    <system.web>
        ...
        <authentication mode="Windows"/>
        ...
    </system.web>
      
  4. Add the following code to the Default.aspx file.
    <%@ Page Language="C#" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
    
    <script runat="server">
    
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Roles.RoleExists("TestRole"))
            {
                Roles.CreateRole("TestRole");
            }
            ShowRoleMembership();
        }
        private void ShowRoleMembership()
        {
            if (Roles.IsUserInRole("TestRole"))
            {
                Label1.Text = User.Identity.Name + " is in role TestRole";
            }
            else
            {
                Label1.Text = User.Identity.Name + " is NOT in role TestRole";
            }
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            Roles.AddUserToRole(User.Identity.Name, "TestRole");
            ShowRoleMembership();
        }
        protected void Button2_Click(object sender, EventArgs e)
        {
            Roles.RemoveUserFromRole(User.Identity.Name, "TestRole");
            ShowRoleMembership();
        }
    </script>
    
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
        <title>Untitled Page</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:Button ID="Button1" runat="server" Text="Add to role" 
                        OnClick="Button1_Click" /><br />
            <br />
            <asp:Button ID="Button2" runat="server" Text="Remove from role" 
                        OnClick="Button2_Click" /><br />
            <br />
            <asp:Label ID="Label1" runat="server" />
        </div>
        </form>
    </body>
    </html>
      
  5. Run the application. Note the following features about this application:
    1. When you browse to the application, the code in the Page_Load event handler creates the role TestRole if it does not already exist.
      • The text of Label1 shows whether the current authenticated user is a member of the TestRole role.
      • When you click the Add to role button, the code in the Button1_Click event handler uses the role management API to add the current authenticated user to the TestRole role.
      • If you click the Add to role button again before clicking the Remove from role button, the call to Roles.AddUserToRole throws an exception because the user is already in the role TestUser. You must code for this condition in your applications.
      • When you click the Remove from role button, the current authenticated user is removed from the role TestRole.
      • If you click the Remove from role button again before clicking the Add to role button, the call to Roles.RemoveUserFromRole throws an exception because the user is already not in the role TestUser and cannot be removed twice. You must code for this condition in your applications.
To control access to pages and folders using roles
A typical use for roles is to establish rules that allow or deny access to pages or folders. You can set up such access rules in the <authorization> section of the Web.config file. The following example allows users in the role of members to view pages in the folder named memberPages and denies access to anyone else.
<configuration>
   <location path="memberPages">
       <system.web>
            <authorization>
               <allow roles="Manager" />
               <deny users="*" />
            </authorization>
          </system.web>
        </location>
   <!-- other configuration settings here -->
<configuration>
  

Sample: Using WindowsTokenRoleProvider

This sample uses the WindowsTokenRoleProvider.
To test role management with WindowsTokenRoleProvider
  1. Use Visual Studio.NET 2005 to create a Web site, add a Web.config file, and configure the WindowsTokenRoleProvider as described in step 2 of this How To.
  2. Using the Internet Information Services MMC snap-in, edit the properties of the Web site. Edit the Anonymous access and authentication control on theDirectory security tab. Clear the Anonymous access check box and select the Integrated Windows Authentication check box.
  3. In the Web.config file, enable Windows authentication.
    <system.web>
        ...
        <authentication mode="Windows"/>
        ...
    </system.web>
      
  4. Add the following code to Default.aspx.
    <%@ Page Language="C#" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
    
    <script runat="server">
    
        protected void Page_Load(object sender, EventArgs e)
        {
            string[] members = Roles.GetRolesForUser();
            foreach (string role in members)
            {
                Label1.Text += role + "<br />";
            }
            
        } </script>
    
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
        <title>Untitled Page</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:Label ID="Label1" runat="server" />
        </div>
        </form>
    </body>
    </html>
      
  5. Run the application. A list of roles that correspond to the Windows groups that the authenticated user is a member of is displayed. Note the following features about this application:
    1. Because WindowsTokenRoleProvider is read-only, you cannot create, assign to, or remove users from roles. The WindowsTokenRoleProvidersupports only the IsUserInRole and GetRolesForUser methods of the role management API.
  • The text of Label1 shows which Windows groups the currently logged on user is a member of.
  • The WindowsPrincipal class supports an override of the IsInRole method that accepts a WindowsBuiltinRole enumeration. This method allows you to test against membership of common Windows groups. The WindowsBuiltInRole enumeration contains members representing common groups such as Administrators, Guest, PowerUser, and User as shown here.
    WindowsPrincipal User = new 
      WindowsPrincipal((WindowsIdentity)HttpContext.Current.User.Identity);
    if (User.IsInRole(WindowsBuiltInRole.PowerUser))
    {
      ...
    }
      
To control access to pages and folders using built-in roles with the WindowsTokenRoleProvider
You can control access to pages or folders to members of one of the built-in Windows groups by specifying the role in the format BUILTIN\groupName. The following example allows users in the built-in administrators group to view pages in the folder named memberPages and denies access to anyone else.
<configuration>
   <location path="memberPages">
       <system.web>
            <authorization>
               <allow roles="BUILTIN\Administrators" />
               <deny users="*" />
            </authorization>
          </system.web>
        </location>
   <!-- other configuration settings here -->
</configuration>
  

Additional Considerations

If a user's browser accepts cookies, you can store role information for that user in a cookie on the user's computer. On each page request, ASP.NET reads the role information for that user from the cookie. This can improve application performance by reducing the amount of communication required with the roles data store.
To configure and enable role caching, set cacheRolesInCookie = true as shown here.
<roleManager enabled="true" 
             cacheRolesInCookie="true" 
             cookieName=".ASPXROLES"                 
             cookieTimeout="30" 
             cookiePath="/" 
             cookieRequireSSL="false" 
             cookieSlidingExpiration="true"                 
             cookieProtection="All" 
             defaultProvider="AspNetSqlRoleProvider"       
             createPersistentCookie="false" 
             maxCachedResults="25"/>
  
If the role information for a user is too long to store in a cookie, ASP.NET stores only the most recently used role information in the cookie, and then it looks up additional role information in the data source as required.
To secure the role cookie:
  • Set cookieRequireSSL to true to ensure the cookie is only used over an SSL protected channel.
  • Set createPersistentCookie to false to prevent the cookie from being stored on the client computer, in which case the cookie is only used to protect the current session.
  • Set cookieTimeout to the number of minutes for which the cookie is valid.

Monday, February 18, 2013

MVC - Entity Framework update an object



public void UpdateEmployee(Employee objEmployee)
{
using (DBEntities context = new DBEntities())
{
    //Must attach first and change the state to modified
    context.Employees.Attach(objEmployee);
    //If you are using .Net 4.1 then you can use this line instead:
   //context.Entry
    context.ObjectStateManager.ChangeObjectState(objEmployee, EntityState.Modified);
    context.SaveChanges();
}
}
 (OR)
public void UpdateEmployee(Employee objUpdatedEmployee)
{
using (DBEntities model = new DBEntities())
{
  Employee objoriginalEmployee = model.Employees.Single(e => e.EmployeeID == objUpdatedEmployee.EmployeeID);
        model.Employees.ApplyCurrentValues(objUpdatedEmployee);
        model.SaveChanges();
    }
}

Friday, February 15, 2013

C# - Basic CRUD operation for SQL

//Basic SELECT method to populate a DataSet from a SqlDataAdapter
SqlConnection sqlConn = new SqlConnection(connection string here);
SqlDataAdapter sqlAdapt = new SqlDataAdapter(@"SELECT * FROM tableName WHERE conditionColumn='False'", sqlConn);
SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(sqlAdapt);
DataSet sqlSet = new DataSet();
sqlAdapt.Fill(sqlSet, "dataSetTableName");
sqlConn.Close();

//Basic INSERT method with Parameters
SqlConnection sqlConn = new SqlConnection(connection string here);
SqlCommand sqlComm = new SqlCommand();
sqlComm = sqlConn.CreateCommand();
sqlComm.CommandText = @"INSERT INTO tableName (paramColum) VALUES (@paramName)";
sqlComm.Parameters.Add("@paramName", SqlDbType.VarChar);
sqlComm.Parameters["@paramName"].Value = paramSource;
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();

//Basic UPDATE method with Parameters
SqlConnection sqlConn = new SqlConnection(connection string here);
SqlCommand sqlComm = new SqlCommand();
sqlComm = sqlConn.CreateCommand();
sqlComm.CommandText = @"UPDATE tableName SET paramColumn='@paramName' WHERE conditionColumn='@conditionName'";
sqlComm.Parameters.Add("@paramName", SqlDbType.VarChar);
sqlComm.Parameters["@paramName"].Value = paramSource;
sqlComm.Parameters.Add("@conditionName", SqlDbType.VarChar);
sqlComm.Parameters["@conditionName"].Value = conditionSource;
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();

//Basic DELETE method with Parameters
SqlConnection sqlConn = new SqlConnection(connection string here);
SqlCommand sqlComm = new SqlCommand();
sqlComm = sqlConn.CreateCommand();
sqlComm.CommandText = @"DELETE FROM tableName WHERE conditionColumn='@conditionName'";
sqlComm.Parameters.Add("@conditionName", SqlDbType.VarChar);
sqlComm.Parameters["@conditionName"].Value = conditionSource;
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();