How to implement a Login service that uses the DA Schema/Database to store user info & permissions?

Data Abstract
1

Hi there,

I’m trying to find my way into DA, and one of the first (and most common tasks I would assume) that I need to do is to use “real world” data for the login services, and using the DataAbstract facilities, there is no point of using the DB driver directly (and potentially having to implement a different call for each of the supported drivers, that’s what DA is all about isn’t it?). The examples that I have found always use the simple “userId == password”.

I found in this wiki entry: http://wiki.remobjects.com/wiki/Authentication_and_application_security_(.NET) some info about how to implement a db-enabled login service, but it’s far from a complete example, and I just can’t make it work.

To begin with: it says to add a new Table to validate against the user and password. Ok, that’s clear. But in the next part, where it says about the implementation (it mentions the ExecuteLogin, I assume it means the LoginService_Impl’ Login function?), it doesn’t mention how to pass the parameters of the user and password.

Even with that, the NewDataReader is already deprecated, and the info on the IntelliSense tip says it’s better to use NewCommand/ExecuteReader. So, I did that.

I actually wrote a complete SELECT statement as the NewCommand parameter, but then I find the next issue: there is no connection available for the LoginService implementation.

So, I guess there is something missing somewhere? Am I doing the right things reinterpreting that wiki entry, or I went somewhere far off? I have some other issue regarding the connection, but that will be in another question/bug.

I also tried using a LocalDataAdapter, from what I found in this site regarding that, but then I hit the wall of not having a session, and I haven’t found a way around that.

So, I’m stuck not being able to even implement a real login service… things don’t look fine.

2

[filler]

3

Thank you andreyt!

I went with the creating a new CustomDataService, and it works fine. I guess my other problem (the login service being called over and over) was because of my implementation without the new CustomDataService.

4

I forgot, andreyt: this complete example should be on the wiki, so one can implement totally the db-aware login from what comes on the documentation. It’s specially important when starting with such a complex tool as DA to have full documentation and examples, IMHO.

5

Hello Rodrigo.

Thank you for the report. Appropriate issue is registered as #54541.

Thanks.

6

Hi Andreyt.

I follow steps you suggested in example above.
When q.toLict().count is executed then query is sent to SQL without parameter values and SQL raises exception.

In SQL profiler i see:
Select UserID, UserName, UserPassword From User Where UserName = @P1 and Password = @P2

Linq expression:

var q := from u in lda.GetTable() Where (u.UserName = userID) AND (u.Password = Password) Select u;

In other words - parameter values are not defined !

What have i missed ?

Thanks for advance.

Edvin

7

Hi again.

In addition to my question:

I think that problem is in DB Connection driver.
I try with relativity too and work perfect if i use sqllite.

When try to connect to MS SQL 2008 server Linq is not working.

I can get data to client (.net and Delphi), but local and remote linq is not working.

Can you explain me:

  1. how linq and drivers are related
  2. what are differences between odbc.net, mssql.net, ado.net and ado drivers ?
  3. Some of them not work with MS SQL. For example: Why i can connect to
    MS SQL 2008 with mssql.net
  4. What is aux driver ? sqloledb ?

Please advice what is best driver combination to connect to MS SQL 2008+

Regards.

Edvin

8

Additional information to solve (maybe) problem:

  1. I’m using Visual Studio 2012 Professional on Windows 8 and last (winter RODA builds - 7.0.65.1067) and Oxygen (5.2.40.1141)

  2. Linq expression:

    var q := from u in fDataModule.remoteDataAdapter,GetTable()
    where (u.loginName = ‘user’) AND (u.loginpassword = ‘pass’) select u;

  3. Selected database Drivers: ODBC.NET?AuxDriver= {SQL Server}
    raises error: Must declare the scalar variable “@P1p0”.

    In SQL profiler:

    exec sp_executesql N’SELECT [t0].[AccesRoles], [t0].[Administrator],
    [t0].[AuthorID], [t0].[BrokerID], [t0].[CanSelect], [t0].[Changed], [t0].[Description],
    [t0].[EkspozituraID], [t0].[Kind], [t0].[Level], [t0].[LoginName],
    [t0].[LoginPassword], [t0].[Name], [t0].[ParentID], [t0].[ShortName],
    [t0].[SystemUser], [t0].[Type], [t0].[UserID] FROM [ASLogin].[vMainUser] [t0]
    WHERE (([t0].[LoginName] = @P1p0) AND ([t0].[LoginPassword] =
    @P2p1))’,N’@P1 nvarchar(5),@P2 nvarchar(7)’,N’user’,N’pass’

    As you can see: parameter’s names not match

  4. Selected database Drivers: OLEDB.NET?AuxDriver=sqloledb
    raises error: Must declare the scalar variable “@p0”.

    In SQL profiler:

    SELECT [t0].[AccesRoles], [t0].[Administrator], [t0].[AuthorID], [t0].[BrokerID],
    [t0].[CanSelect], [t0].[Changed], [t0].[Description], [t0].[EkspozituraID], [t0].[Kind],
    [t0].[Level], [t0].[LoginName], [t0].[LoginPassword], [t0].[Name], [t0].[ParentID],
    [t0].[ShortName], [t0].[SystemUser], [t0].[Type], [t0].[UserID] FROM [ASLogin].
    [vMainUser] [t0] WHERE (([t0].[LoginName] = @p0) AND ([t0].[LoginPassword] =
    @p1))

    As you can see there is only select expression without sp_executesql

Please. Any suggestion ?

Edvin

9

I found a solution.

Using drivers OLEDB.NET or ODBC.NET is not OK and is not working with LINQ.
After Marc explanation in post

http://connect.remobjects.com/discussion/2869/database-driver-connection-string-provider-and-net-platform#latest

i have changed driver to MSSQL2005.NET and LINQ start working like expected.

I suggest to examine why OLEDB.NET and ODBC.NET not work with LINQ.
I think this could be some bug.

Regards.

Edvin

10

Weird, indeed. The driver used in the server should not affect how LINQ works on the client. Sounds like a bug, a detailed report would be appreciated.

11

Marc,

maybe this problem is not only limited to LINQ.
Because LINQ is convertet to DA Query on client side then i believe problem could
be in DAQuery to SQL expression conversion - on server side.

Some more details:

  • i create test case as simple as possible
  • only one table in schema
  • auto generated sql
  • no custom bussines processor
  • error exists with remotedataadapter and localdataadapter
12

Hello Edwin.

OLEDB.NET and ODBC.NET use sequential command parameter handling which is not fully supported by Data Abstract for .NET in the current release. Sequential parameter handling support will be one of the features added to the upcoming release. Please contact us via support@ and we will provide you a pre-beta build.

Thanks.

13

Thanks Andrey.

After changing to MSSQL2005.NET driver problem is solved.
So there is no need for pre build release.

Edvin