BusinessPrincipal.Login and Oracle

Rick Nash posted on Sunday, December 30, 2007

I have just about ported my database from SQL server to Oracle but have come up against this problem with:

BusinessPrincipal.Login(username, password);

throws exception:

CSLA.DataPortalException
DataPortal.Fetch failedDataPortal_Fetch method call failed
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

The conenction string is correctly setup in the App.Config as I have tested this using an Orcale Connection.

It seems by default CSLA uses SQL Server, do I have set something up in the App.Config.

The other problem is you have to pass a cursor to Orcale SPs to get the results out:-

***** Package *****
create or replace PACKAGE GLOBALPKG
AS
    TYPE RCT1 IS REF CURSOR;
    TRANCOUNT INTEGER := 0;
    IDENTITY INTEGER;
END;

***** Stored Proc ******

create or replace
PROCEDURE        Login
(
    User                       IN          VARCHAR2 DEFAULT NULL,
    pw                         IN          VARCHAR2 DEFAULT NULL,
    RCT1                        OUT     GLOBALPKG.RCT1
)
AS
        RCT2                        GLOBALPKG.RCT1;
BEGIN

    OPEN RCT2 FOR
    SELECT Username
    FROM Users
    WHERE    Username = Login.User
    AND    Password = Login.pw;

    OPEN RCT1 FOR
    SELECT R.Role
    FROM Users U,
        Roles R
    WHERE    R.UserName = U.UserName
    AND    (U.Username = Login.User
    AND    U.Password = Login.pw);

END;

Biceman replied on Sunday, December 30, 2007

CSLA references System.Data.SqlClient in its code. In order to access an Oracle DB, you need to switch to referencing either System.Data.OleDb or System.Data.OracleClient. OleDb is installed by default with VS 2005 but the OracleClient dll can be downloaded for free from Oracle. My understanding is the OracleClient dll provides better performance than OleDb dll. I googled and found this page: http://www.oracle.com/technology/software/tech/windows/odpnet/index.html

Also, you will need to find and replace references to “Sqlxxxxx” (e.g., “SqlConnection”) with the equivalent Oracle name (e.g., “OracleConnection”)

Rick Nash replied on Sunday, December 30, 2007

I have installed the Oracle client and I am using Oracle objects eg: OracleConnection. This is all working and I am getting data out of the Oracle database.

However the current code (which I have inherited) uses BusinessPrincipal.Login to login and this seems to rely on using SQL Server.

My question is do I need to set anything else up in order to use this function and what do I have to do to tell CSLA that I am using Oracle.

I have provided my own Login function to get around this problem but I assuming there must be something else I need to do.

JoeFallon1 replied on Sunday, December 30, 2007

Rick,

CSLA does not use SQL Server by default. The code in your Principal and Identity classes is telling it what DB to use. It would be easier to tell you how to fix the problem if you would post the code.

I support both SQL Server and Oracle and all of my code references a Data Access Layer which switches between the 2 based on a setting in the config file.

e.g.

mDBType = ConfigurationManager.AppSettings("DBType")

Then I have code like this in my DAL:

Public Shared Function CreateCommand() As IDbCommand
If mDBType = "SQL Server" Then
    Return New SqlCommand
ElseIf mDBType = "Oracle" Then
    Return New OracleCommand
Else
    Return New OleDbCommand
End If
End Function

So I always return the Interface for the Command or DataReader or ....

The code in my Identity class to fetch a dr looks like this:

dr = New SafeDataReader(DAL.ExecuteReader(Userlist.GetPermissions(mUID)))

This calls into my DAL which reads the connection string (mConnStr) from the config file:

Public Overloads Shared Function ExecuteReader(ByVal commandText As String) As IDataReader
Return ExecuteReader(mConnStr, CommandType.Text, commandText, CType(Nothing, IDataParameter()))
End Function

Joe

Rick Nash replied on Tuesday, January 01, 2008

DBType does not exist in my App.Config so I added the following in the app settings:

<add key="DBType" value="Oracle" />

I still get the same error, CSLA is still trying to use SQL Server.

I am using the following code to get CSLA to login
public void Login(string username, string password)
{
      if (password.Length < MinPasswordLength) {
        throw new LoginException();
      }

       // This is a static function which I assume gets CSLA to Login
      BusinessPrincipal.Login(username, password);

      }
}

I have provided my own Login function but I would prefer to get the framework to do it.

The other problem is Oracle requires cursors to be passed in to get data out. In the orignal SQL Server Sql SP for Login (attached) no cursors get passed in

JoeFallon1 replied on Tuesday, January 01, 2008

Rick,

If you want help with this problem you have to post the code for your Identity class.

Joe

Rick Nash replied on Thursday, January 03, 2008

Hi Joe

There are no identity classes in the code.

All it does is call the static funcion BuinessPrincipal.Login

namespace CSLA.Security
{
    [Serializable]
    public class BusinessPrincipal : IPrincipal
    {
        public static void Login(string username, string password);
    }
}

I thought I had worked around the problem doing this:

string role = ConnMgr.Login(username, password);
if (role.Length > 0)
{
                GenericIdentity id = new GenericIdentity(username, role);
                System.Threading.Thread.CurrentPrincipal = new GenericPrincipal(id, new string[] { role });
}

But this causes problems when I use the Web Data Portal

Best regards

Rick

Rick Nash replied on Thursday, January 03, 2008

I think I may have worked this out for myself.

I was only given the CSLA assemblies to work with as this work was subcontracted out. I assumed that the sub-contractor purchased this out of the box. I didn't realise that you could actually download the code yourself and modify it.

Hence BusinessPrinciple is only using the default implementation. It looks like I need to get the latest code and tweak it.

Got this out of reflector, which looks like the version 1 code.

public static void Login(string username, string password)
{
    new BusinessPrincipal(username, password);
}

private BusinessPrincipal(string username, string password)
{
    AppDomain domain = Thread.GetDomain();
    domain.SetPrincipalPolicy(PrincipalPolicy.UnauthenticatedPrincipal);
    IPrincipal currentPrincipal = Thread.CurrentPrincipal;
    Thread.CurrentPrincipal = this;
    try
    {
        if (currentPrincipal.GetType() != typeof(BusinessPrincipal))
        {
            domain.SetThreadPrincipal(this);
        }
    }
    catch
    {
    }
    this._identity = BusinessIdentity.LoadIdentity(username, password);
}

bool IPrincipal.IsInRole(string role)
{
    return this._identity.IsInRole(role);
}