.NET FOLLOWER

    One of the possible ways to pass a collection of values to a stored procedure is to use Associative Array (also known as Index-By Tables). For example, the declaration of the stored procedure accepting array of strings may resemble the following:

TYPE str_table_type IS TABLE OF VARCHAR2(255) INDEX BY PLS_INTEGER;

PROCEDURE Save_Something (
    p_str_array IN str_table_type,
    ...
    p_MSG_OUT OUT    VARCHAR2
);

To call such stored procedure we need to create and properly fill out the p_str_array parameter. The code doing that may look like shown below:

// create the parameter. 
// In case of Associative Array the passed OracleDbType.Varchar2 is a type of the elements
OracleParameter array = new OracleParameter("p_str_array", 
                                      OracleDbType.Varchar2, ParameterDirection.Input);

// Specify it as PL/SQL Associative Array
array.CollectionType  = OracleCollectionType.PLSQLAssociativeArray;

string[] values = new[] { "Value One", "Value Two", "Value Three" };

// Set the values for PL/SQL Associative Array
array.Value = values;

// Set the maximum number of elements in the PL/SQL Associative Array,
// in case of Input direction, Size usually equals to the number of passed values
array.Size = values.Length;

// Pass to the command and call one
using (var cmd = new OracleCommand("DNF.SOME_PKG.Save_Something")
                                   { CommandType = CommandType.StoredProcedure })
{
	cmd.Parameters.Add(array);
	cmd.Parameters.Add(new OracleParameter("p_MSG_OUT", OracleDbType.Varchar2, 
                                           ParameterDirection.Output) { Size = 2000 });
	
	cmd.ExecuteNonQuery();
	
	// process p_MSG_OUT somehow
	...
}

The use of the Associative Array is pretty straightforward unless we are trying to pass an empty array. I tried a few ideas coming to mind first (listed below), none of them doesn’t work though:

array.Value = null;
array.Size  = 0;
...
array.Value = new string[0];
array.Size  = 0;
...
array.Value = new string[1] { null };
array.Size  = 1;

The “OracleParameter.Value is invalid” was the most popular exception I was getting at that moment. Ultimately, however, I found a working variant:

// for array of strings
array.Value = new OracleString[1] { OracleString.Null };
array.Size  = 1;
...
// for array of Int32s, decimals and so on
array.Value = new OracleDecimal[1] { OracleDecimal.Null };
array.Size  = 1;

As the result, the following common method and a few accompanying ones have been born to create Associative Arrays of different types:

// Creates an Associative Array parameter, knows how to treat empty collections
// Note: specify the maxNumberOfElementsInArray as an expected number of returned elements
public OracleParameter CreateAssociativeArray<ValueType, OracleType>(
                      string name, List<ValueType> values, 
                      ParameterDirection direction, OracleDbType oracleDbType, 
                      OracleType nullValue, int? maxNumberOfElementsInArray)
{
	bool isArrayEmpty     = values == null || values.Count == 0;
	OracleParameter array = new OracleParameter(name, oracleDbType, direction);
	array.CollectionType  = OracleCollectionType.PLSQLAssociativeArray;
	array.Value           = !isArrayEmpty ? values.ToArray() :
                                            (object)new OracleType[1] { nullValue };
	array.Size            = !isArrayEmpty ? values.Count     : 1;

	// if it's Output/InputOutput parameter, set the maximum possible number of elements.            
	if (maxNumberOfElementsInArray != null && 
       (direction == ParameterDirection.Output || direction == ParameterDirection.InputOutput))
		array.Size = Math.Max(array.Size, maxNumberOfElementsInArray.Value);

	return array;
}

public OracleParameter CreateInt32AssociativeArray(string name, 
          List<int> values, ParameterDirection direction = ParameterDirection.Input, 
          int? maxNumberOfElementsInArray = null)
{
	return CreateAssociativeArray<int, OracleDecimal>(name, values, 
             direction, OracleDbType.Int32, OracleDecimal.Null, maxNumberOfElementsInArray);
}

public OracleParameter CreateDecimalAssociativeArray(string name, 
           List<decimal> values, ParameterDirection direction = ParameterDirection.Input, 
           int? maxNumberOfElementsInArray = null)
{
	return CreateAssociativeArray<decimal, OracleDecimal>(name, values, direction, 
             OracleDbType.Decimal, OracleDecimal.Null, maxNumberOfElementsInArray);
}

public OracleParameter CreateStringAssociativeArray(string name, 
            List<string> values, ParameterDirection direction = ParameterDirection.Input, 
            int? maxNumberOfElementsInArray = null, int maxLength = 255)
{
	var res = CreateAssociativeArray<string, OracleString>(name, values, direction, 
               OracleDbType.Varchar2, OracleString.Null, maxNumberOfElementsInArray);

	if(direction == ParameterDirection.Output || direction == ParameterDirection.InputOutput)
	{
		int curMaxLen = maxLength;
		if(values != null)
			values.ForEach(s => { if (curMaxLen < s.Length) curMaxLen = s.Length; });

		res.ArrayBindSize = new int[res.Size];
		for (int i = 0; i < res.Size; i++)
			res.ArrayBindSize[i] = curMaxLen;
	}

	return res;
}

There are two important things to note here. The first one, in case of Output or InputOutput direction, the Size has to be set to the maximum number of elements you expect to get from the stored procedure (see the CreateAssociativeArray method). Specify this maximum number in the maxNumberOfElementsInArray. For example, you want to pass 3 elements to the stored procedure and get 10 back (InputOutput direction). Set the maxNumberOfElementsInArray to 10, otherwise CreateAssociativeArray will set Size to 3.

The second thing is the special processing of array of strings (see the CreateStringAssociativeArray method). String is variable-length element type (Varchar2 and so on). So, for strings we need to define the ArrayBindSize property. ArrayBindSize is the collection each element of which specifies the length of the corresponding element in the Value property. Being longer than specified, the element will be truncated. Pass the maximum allowed length of elements in maxLength, otherwise it will be set to 255 (default) or the maximum length found in the Value property.

Use the following code to create arrays of strings, Int32s and decimals in case of Input direction (or create your own “accompanying” methods for other types):

OracleParameter intArray = CreateInt32AssociativeArray("p_int_array", 
               new List<int> { 1, 2, 3 });

// pass empty array to the stored procedure
OracleParameter decimalArray = CreateDecimalAssociativeArray("p_dec_array", null);

OracleParameter strArray = CreateStringAssociativeArray("p_str_array",  
              new List<string> { "Value One", "Value Two", "Value Three" });

If Associative Array should be an Output or InputOutput parameter, the code may look like:

// Output parameter
// we expect no more than 10 elements to be returned
OracleParameter intArray = CreateInt32AssociativeArray("p_int_array", null, 
     ParameterDirection.Output, 10);
...

// InputOutput parameter
// declare input values
string[] tmpAtrArray = new string[3] { "Value One", "Value Two", "Value Three" };
// we expect no more than 20 strings to be returned
// returned strings suppose to be no longer than 100
OracleParameter strArray = CreateStringAssociativeArray("p_str_array", 
     new List<string>(tmpAtrArray), ParameterDirection.InputOutput, 20, 100);

To read, for example, number values (presented as OracleDecimal) from the output Associative Array I use the following two utility methods:

public Nullable<T> GetValue<T>(OracleDecimal oracleDecimal, 
      Func<OracleDecimal, T> convert) where T : struct
{
	if (oracleDecimal == null || oracleDecimal.IsNull)
		return null;

	return convert(oracleDecimal);            
}

public List<T> GetValues<T>(OracleParameter parameter, 
     Func<OracleDecimal, T> convert) where T : struct
{
	List<T> res = new List<T>();

	if(parameter.Value != null)
	{
		OracleDecimal[] values = parameter.Value as OracleDecimal[];
		if(values != null)
			foreach(OracleDecimal oDecimal in values)
			{
				Nullable<T> val = GetValue(oDecimal, convert);
				if (val != null)
					res.Add(val.Value);
			}
	}

	return res;
}

So, the reading of integers looks as follows:

OracleParameter intArray = CreateInt32AssociativeArray("p_int_array", null, 
            ParameterDirection.Output, 10);
...
// read returned values
List<int> res = GetValues<int>(intArray, od => { return od.ToInt32(); });

All code has been written and tested with the ODAC 12c Release 2.

    In addition to the article SharePoint: SqlMembershipProvider – Get All Users In Role, here is one more method to extend the SqlMembershipProvider with. It’s found out that the SqlMembershipProvider doesn’t provide a method to lock user. By default a user can be automatically locked after several frequent and failed attempts to login. To unlock such users the SqlMembershipProvider supplies with the UnlockUser method. But what if administrator wants to temporarily lock user for some reason? Unfortunately, there is no such method out-of-box.

So, let’s try to implement our own LockUser method. Two obvious steps for that are as follows: to create a Stored Procedure in database; to extend a class derived from the SqlMembershipProvider with the proper method.

LockUser Stored Procedure

The stored procedure is very simple as we need just to update one field in the aspnet_Membership table for appropriate user. Below is the script to create such procedure. Run the script on MembershipProvider database, in my case it’s aspnetdb.

USE [aspnetdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      .Net Follower
-- Description:	Locks User
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_Membership_LockUser]
	@ApplicationName                         nvarchar(256),
    @UserName                                nvarchar(256)
AS
BEGIN
	DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId

    IF ( @UserId IS NULL )
        RETURN 1

    UPDATE dbo.aspnet_Membership 
    SET IsLockedOut = 1 WHERE @UserId = UserId

    RETURN 0
END

Custom Membership Provider

Now we can add the LockUser method to the custom Membership Provider called SqlMembershipProviderEx and shown in the article. The SqlMembershipProviderEx with the LockUser is listed below. Note that the methods mentioned in the previous article are skipped.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Reflection;
using System.Web.Security;
using Microsoft.SharePoint;

namespace dotNetFollower
{
    public class SqlMembershipProviderEx : SqlMembershipProvider
    {       
	    ...
		
        public bool LockUser(string username)
        {
            bool flag = false;
            CheckParameter(ref username, true, true, true, 0x100, "username");

            DoInSqlConnectionContext(delegate(SqlConnection connection)
            {
                //this.CheckSchemaVersion(connection.Connection);
                SqlCommand command = new SqlCommand("dbo.aspnet_Membership_LockUser", connection)
                {
                    CommandTimeout = CommandTimeout,
                    CommandType    = CommandType.StoredProcedure
                };
                command.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
                command.Parameters.Add(CreateInputParam("@UserName", SqlDbType.NVarChar, username));
                SqlParameter parameter = new SqlParameter("@ReturnValue", SqlDbType.Int)
                {
                    Direction = ParameterDirection.ReturnValue
                };
                command.Parameters.Add(parameter);

                command.ExecuteNonQuery();
                flag = ((parameter.Value != null) ? ((int)parameter.Value) : -1) == 0;
            });

            return flag;
        }

        protected internal static void CheckParameter(ref string param, bool checkForNull, bool checkIfEmpty, bool checkForCommas, int maxSize, string paramName)
        {
            if (param == null)
            {
                if (checkForNull)
                    throw new ArgumentNullException(paramName);
            }
            else
            {
                param = param.Trim();
                if (checkIfEmpty && (param.Length < 1))
                    throw new ArgumentException(string.Format("The parameter '{0}' must not be empty.", new object[] { paramName }), paramName);

                if ((maxSize > 0) && (param.Length > maxSize))
                    throw new ArgumentException(string.Format("The parameter '{0}' is too long: it must not exceed {1} chars in length.", new object[] { paramName, maxSize.ToString(CultureInfo.InvariantCulture) }), paramName);

                if (checkForCommas && param.Contains(","))
                    throw new ArgumentException(string.Format("The parameter '{0}' must not contain commas.", new object[] { paramName }), paramName);
            }
        }

        ...
        
    }    
}

The latest version of the SqlMembershipProviderEx along with all used additional classes are available to download here.

Related posts:

• SharePoint: SqlMembershipProvider – Get All Users In Role

    In the SharePoint application I’m currently working on, I configured Form Based Authentication (FBA) using the SqlMembershipProvider and SqlRoleProvider. Implementing some user management functionality, I run into the lack of a method to get the users in particular role by portions (so-called pagination). The SqlRoleProvider exposes the GetUsersInRole method which returns only names of users in the passed role and doesn’t support pagination. The direct way in this case is to get user names and then get appropriate users, calling the GetUser method of SqlMembershipProvider one time per name. This approach results in a bunch of requests to the database: one request is to get names of users in a role and a number of requests are to get each user by his name. In addition, we have somehow to implement pagination ourselves. The approach is acceptable, but let’s try to reduce requests to the database and borrow somewhere the pagination logic.

GetAllUsersInRole Stored Procedure

It’s interesting that the SqlMembershipProvider provides the GetAllUsers method that supports pagination. On the database level, every call of SqlMembershipProvider.GetUsersInRole and SqlMembershipProvider.GetAllUsers ends with executing such Stored Procedures as aspnet_UsersInRoles_GetUsersInRoles and aspnet_Membership_GetAllUsers respectively. So, we know that the aspnet_UsersInRoles_GetUsersInRoles searches for names of users in a role while the aspnet_Membership_GetAllUsers is able to return users by portions. Let’s combine these two Stored Procedures and create another one which would select users in a role and return a required portion of the result. The sql script below creates such Stored Procedure, I named it aspnet_Membership_GetAllUsersInRole. Note the script should be executed on MembershipProvider database, it’s aspnetdb in my case.

USE [aspnetdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		.Net Follower
-- Description:	Returns users in role by portions
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_Membership_GetAllUsersInRole]
    @ApplicationName       nvarchar(256),
    @PageIndex             int,
    @PageSize              int,
    @RoleName              nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	DECLARE @RoleId uniqueidentifier
    SELECT  @RoleId = NULL

    SELECT  @RoleId = RoleId
    FROM    dbo.aspnet_Roles
    WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId

    IF (@RoleId IS NULL)
		RETURN 0

    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
    SELECT u.UserId
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
    WHERE  u.ApplicationId = @ApplicationId AND u.UserId = m.UserId AND 
		   u.UserId = ur.UserId AND @RoleId = ur.RoleId
    ORDER BY u.UserName

    SELECT @TotalRecords = @@ROWCOUNT

    SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY u.UserName
    RETURN @TotalRecords
END

Custom Membership Provider

Now let’s extend our Membership Provider with a new method that deals with the aspnet_Membership_GetAllUsersInRole. I created a class SqlMembershipProviderEx derived from SqlMembershipProvider and containing the target GetAllUsersInRole method. The class is demonstrated below, but first of all a few remarks on the code:

• I had to use Reflection to get values of some important fields (like Connection String to the database, for example) as Microsoft makes everything private or internal;
• The SqlMembershipProvider elevates privileges when opening SqlConnection. Since the extended Membership Provider is going to be used in SharePoint application, I did the same by means of SPSecurity.RunWithElevatedPrivileges. Note however that if you want to use the extended Membership Provider in a pure ASP.Net application you will need to deal with such internal (of course) classes as SqlConnectionHolder and ApplicationImpersonationContext through Reflection;
• The code of GetAllUsersInRole method is mainly based on the GetAllUsers of the parent SqlMembershipProvider class.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Web.Security;
using Microsoft.SharePoint;

namespace dotNetFollower
{
    public class SqlMembershipProviderEx : SqlMembershipProvider
    {
        protected string _connectionString;
        protected int?   _sqlCommandTimeout;

        protected string ConnectionString
        {
            get 
            { 
                return _connectionString ?? 
                    (_connectionString = Convert.ToString(this.GetFieldValue("_sqlConnectionString"))); 
            }
        }

        protected int CommandTimeout
        {
            get
            {
                if (_sqlCommandTimeout == null)
                    _sqlCommandTimeout = Convert.ToInt32(this.GetFieldValue("_CommandTimeout"));
                return _sqlCommandTimeout.Value;
            }
        }

        public MembershipUserCollection GetAllUsersInRole(string role, int pageIndex, int pageSize, out int totalRecords)
        {
            if (pageIndex < 0)
                throw new ArgumentException("The pageIndex must be greater than or equal to zero.", "pageIndex");
            if (pageSize < 1)
                throw new ArgumentException("The pageSize must be greater than zero.", "pageSize");
            
            long num = ((pageIndex * pageSize) + pageSize) - 1;
            if (num > 0x7fffffff)
                throw new ArgumentException("The combination of pageIndex and pageSize cannot exceed the maximum value of System.Int32.", "pageIndex and pageSize");
            
            MembershipUserCollection users = new MembershipUserCollection();
            int recordsAmount = 0;

            DoInSqlConnectionContext(delegate(SqlConnection connection)
                {
                    //this.CheckSchemaVersion(connection.Connection);
                    SqlCommand command     = new SqlCommand("dbo.aspnet_Membership_GetAllUsersInRole", connection);
                    SqlDataReader reader   = null;
                    SqlParameter parameter = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    command.CommandTimeout = CommandTimeout;
                    command.CommandType    = CommandType.StoredProcedure;
                    command.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
                    command.Parameters.Add(CreateInputParam("@PageIndex", SqlDbType.Int, pageIndex));
                    command.Parameters.Add(CreateInputParam("@PageSize", SqlDbType.Int, pageSize));
                    command.Parameters.Add(CreateInputParam("@RoleName", SqlDbType.NVarChar, role));
                    parameter.Direction = ParameterDirection.ReturnValue;
                    command.Parameters.Add(parameter);
                    try
                    {
                        reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                        while (reader.Read())
                        {
                            string   nullableString          = GetNullableString(reader, 0);
                            string   email                   = GetNullableString(reader, 1);
                            string   passwordQuestion        = GetNullableString(reader, 2);
                            string   comment                 = GetNullableString(reader, 3);
                            bool     boolean                 = reader.GetBoolean(4);
                            DateTime creationDate            = reader.GetDateTime(5).ToLocalTime();
                            DateTime lastLoginDate           = reader.GetDateTime(6).ToLocalTime();
                            DateTime lastActivityDate        = reader.GetDateTime(7).ToLocalTime();
                            DateTime lastPasswordChangedDate = reader.GetDateTime(8).ToLocalTime();
                            Guid     providerUserKey         = reader.GetGuid(9);
                            bool     isLockedOut             = reader.GetBoolean(10);
                            DateTime lastLockoutDate         = reader.GetDateTime(11).ToLocalTime();
                            users.Add(new MembershipUser(Name, nullableString, providerUserKey, email, passwordQuestion,
                                                         comment, boolean, isLockedOut, creationDate, lastLoginDate,
                                                         lastActivityDate, lastPasswordChangedDate, lastLockoutDate));
                        }
                    }
                    catch (Exception ex)
                    {
                        EventLogger.WriteError(ex);
                        throw;
                    }
                    finally
                    {
                        if (reader != null)
                            reader.Close();
                        if (parameter.Value is int)
                            recordsAmount = (int)parameter.Value;
                    }
                });
            totalRecords = recordsAmount;
            return users;
        }

        protected void DoInSqlConnectionContext(Action<SqlConnection> action)
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnectionString);
                SPSecurity.RunWithElevatedPrivileges(connection.Open);
                action(connection);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

        protected SqlParameter CreateInputParam(string paramName, SqlDbType dbType, object objValue)
        {
            SqlParameter parameter = new SqlParameter(paramName, dbType);
            if (objValue == null)
            {
                parameter.IsNullable = true;
                parameter.Value      = DBNull.Value;
                return parameter;
            }
            parameter.Value = objValue;
            return parameter;
        }

        protected string GetNullableString(SqlDataReader reader, int col)
        {
            return !reader.IsDBNull(col) ? reader.GetString(col) : null;
        }
    }
}

Note the EventLogger class is described in the post SharePoint: Simple Event Logger while the GetFieldValue method is provided by ReflectionHelper described in the C#: How to set or get value of a private or internal field through the Reflection and C#: How to set or get value of a private or internal property through the Reflection.

The latest version of the SqlMembershipProviderEx along with all used additional classes are available to download here.

Related posts:

• SharePoint: SqlMembershipProvider – Lock User
• SharePoint: Simple Event Logger
• C#: How to set or get value of a private or internal field through the Reflection