using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Diagnostics;
using System.Configuration;
using System.Configuration.Provider;
using System.Web.Security;


namespace Progetto_Prova4c
{
    public class AccessRoleProvider : RoleProvider
    {

        //---for database access use---
        private string connStr = @"ProviderMicrosoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Gege\Documenti\Visual Studio 2008\Projects\Progetto_Prova4c\Progetto_Prova4c\App_Data\Members.mdb;Persist Security Info=False";

        private OleDbCommand comm = new OleDbCommand();
        private bool _requiresQuestionAndAnswer;

        private int _minRequiredPasswordLength;


        private string pApplicationName;


        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            //===retrives the attribute values set in 
            //web.config and assign to local variables===

            if (config["requiresQuestionAndAnswer"] == "true")
                _requiresQuestionAndAnswer = true;

                // connStr = config["connectionString"];
            base.Initialize(name, config);
        }






        public override void AddUsersToRoles(string[] usernames, string[] roleNames)
        {
            throw new NotImplementedException();
        }



        public override string ApplicationName
        {
            get { return pApplicationName; }
            set { pApplicationName = value; }
        }

        public override void CreateRole(string rolename)
        {
            if (rolename == null || rolename == "")
                throw new ProviderException("Role name cannot be empty or null.");
            if (rolename.IndexOf(',') > 0)
                throw new ArgumentException("Role names cannot contain commas.");
            if (RoleExists(rolename))
                throw new ProviderException("Role name already exists.");
            if (rolename.Length > 255)
                throw new ProviderException("Role name cannot exceed 255 characters.");

            OdbcConnection conn = new OdbcConnection(connStr);

            OdbcCommand id_max = new OdbcCommand("SELECT Max(ID) From Ruoli");
            conn.Open();

            OdbcDataReader reader = id_max.ExecuteReader();
            String max = reader.ToString();
            int max1 = Int32.Parse(max);
            max1 += 1;

            OdbcCommand cmd = new OdbcCommand("INSERT INTO [" + "Ruoli" + "]" +
                                              " (ID, Nome_Ruolo) " +
                                              " Values(max1, ?)", conn);

            cmd.Parameters.Add("@ID", OdbcType.Int).Value = max1;
            cmd.Parameters.Add("@rolename", OdbcType.VarChar, 255).Value = rolename;


            try
            {
                conn.Open();

                cmd.ExecuteNonQuery();
            }
            catch (OdbcException)
            {
                // Handle exception.
            }
            finally
            {
                conn.Close();
            }
        }

        public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
        {
            throw new NotImplementedException();
        }

        public override string[] FindUsersInRole(string roleName, string usernameToMatch)
        {
            throw new NotImplementedException();
        }

        public override string[] GetAllRoles()
        {
            throw new NotImplementedException();
        }

        public override string[] GetRolesForUser(string username)
        {
            if (username == null || username == "")
                throw new ProviderException("User name cannot be empty or null.");

            string tmpRoleNames = "";

            OdbcConnection conn = new OdbcConnection(connStr);
            OdbcCommand cmd = new OdbcCommand("SELECT Ruoli.Nome_Ruolo FROM ((Membership INNER JOIN" +
                    "RuoliMembri ON Membership.username = RuoliMembri.Id_Membership) INNER JOIN" +
                    "Ruoli ON RuoliMembri.Id_Ruolo = Ruoli.ID)" +
                    "WHERE username=@username", conn);

            cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
            cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;

            OdbcDataReader reader = null;

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    tmpRoleNames += reader.GetString(0) + ",";
                }
            }
            catch (OdbcException)
            {
                // Handle exception.
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                conn.Close();
            }

            if (tmpRoleNames.Length > 0)
            {
                // Remove trailing comma.
                tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
                return tmpRoleNames.Split(',');
            }


            Console.WriteLine(tmpRoleNames);
            return new string[0];
        }




        public override string[] GetUsersInRole(string roleName)
        {
            throw new NotImplementedException();
        }


        public override bool IsUserInRole(string username, string rolename)
        {
            if (username == null || username == "")
                throw new ProviderException("User name cannot be empty or null.");
            if (rolename == null || rolename == "")
                throw new ProviderException("Role name cannot be empty or null.");

            bool userIsInRole = false;

            OdbcConnection conn = new OdbcConnection(connStr);
            OdbcCommand cmd = new OdbcCommand("SELECT Ruoli.Nome_Ruolo, Membership.username FROM ((Membership INNER JOIN" +
                                "RuoliMembri ON Membership.username = RuoliMembri.Id_Membership) INNER JOIN" +
                                "Ruoli ON RuoliMembri.Id_Ruolo = Ruoli.ID)" +
                                "WHERE username=@username AND Nome_Ruolo=@roleName", conn);

            cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
            cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename;
            cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;

            try
            {
                conn.Open();

                int numRecs = (int)cmd.ExecuteScalar();

                if (numRecs > 0)
                {
                    userIsInRole = true;
                }
            }
            catch (OdbcException)
            {
                // Handle exception.
            }
            finally
            {
                conn.Close();
            }

            return userIsInRole;
        }

        public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
            throw new NotImplementedException();
        }

        public override bool RoleExists(string roleName)
        {
            throw new NotImplementedException();
        }
    }
}

<configuration>
    <appSettings/>
    <connectionStrings/>
    <system.web>
      <authorization>
        <allow roles="admin"/>
        <deny roles="docente"/>   
      </authorization>
    </system.web>
</configuration>

    <!-- API MembershipProvider -->
    <membership defaultProvider="AccessMembershipProvider" >
      <providers>
        <add name="AccessMembershipProvider"
          type="Progetto_Prova4c.AccessMembershipProvider"
          requiresQuestionAndAnswer="true"
          connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Gege\Documenti\Visual Studio 2008\Projects\Progetto_Prova4c\Progetto_Prova4c\App_Data\Members.mdb;Persist Security Info=False"
          />
      </providers>
    </membership>

     <!-- API RoleProvider -->
    <roleManager enabled="true" defaultProvider="AccessRoleProvider">
      <providers>
        <clear/>
        <add name="AccessRoleProvider"
             type="Progetto_Prova4c.AccessRoleProvider"
             connectionStringName="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Gege\Documenti\Visual Studio 2008\Projects\Progetto_Prova4c\Progetto_Prova4c\App_Data\Members.mdb;Persist Security Info=False"/>
      </providers>
    </roleManager>