Using existing table for simplemembership

[garbgdev]

I have simplemembership for postgres on my application and it seems connection between application and postgres works fine. However, I want to use existing userTable with simplemembership provided by dotconnect.
I have following line to connect simplemembership with my existing table.

Code: Select all

 WebSecurity.InitializeDatabaseConnection("DefaultConnectionPostGres", "Contacts", "Id", "UserName", autoCreateTables: true);

This line works without a problem if I change my connection string to localsql server. But If I change my connection string to postgres server, it automatically generates a table called 'contacts', not the "Contacts".

So I changed "Contacts" to "contacts" like below line.

Code: Select all

 WebSecurity.InitializeDatabaseConnection("DefaultConnectionPostGres", "contacts", "Id", "UserName", autoCreateTables: true);

now, It returining error that says

Code: Select all

Devart.Data.PostgreSql.PgSqlException: column "id" does not exist

followings are query for both table. Please help me with my problem.

Code: Select all

CREATE TABLE "Contacts"
(
  "Id" serial NOT NULL,
  "UserName" character varying NOT NULL,
  "Age" integer,
  CONSTRAINT "PK_Contacts" PRIMARY KEY ("Id")
)


CREATE TABLE contacts
(
  id serial NOT NULL,
  username character varying(240) NOT NULL,
  CONSTRAINT contacts_pkey PRIMARY KEY (id),
  CONSTRAINT contacts_username_key UNIQUE (username)
)

[Shalex]

This table exists in your PostgreSQL database, doesn't it?

Code: Select all

CREATE TABLE contacts
(
  id serial NOT NULL,
  username character varying(240) NOT NULL,
  CONSTRAINT contacts_pkey PRIMARY KEY (id),
  CONSTRAINT contacts_username_key UNIQUE (username)
)

Please run this code with enabled dbMonitor tool:

Code: Select all

    new Devart.Data.PostgreSql.PgSqlMonitor() { IsActive = true };
    WebSecurity.InitializeDatabaseConnection("DefaultConnectionPostGres", "contacts", "Id", "UserName", autoCreateTables: true);

Specify the exact SQL query which fails with the "Devart.Data.PostgreSql.PgSqlException: column "id" does not exist" error.

[garbgdev]

Code: Select all

CREATE TABLE "Contacts"
(
  "Id" serial NOT NULL,
  "UserName" character varying NOT NULL,
  "Age" integer,
  CONSTRAINT "PK_Contacts" PRIMARY KEY ("Id")
)

above table is existing table that I want to cope with simplemembership.

Code: Select all

CREATE TABLE contacts
(
  id serial NOT NULL,
  username character varying(240) NOT NULL,
  CONSTRAINT contacts_pkey PRIMARY KEY (id),
  CONSTRAINT contacts_username_key UNIQUE (username)
)

and this table is generated by WebSecurity.InitializeDatabaseConnection() which means it does not found existing table.
However the weird part is that generated table has same name as existing table but it has lowercase table name. Thus I changed existing table name to lowercase for the test reason. This occurring column "id" does not exist" error. And I really don't want to change my table name to lowercase. I'm lost now, any suggestions?

[Shalex]

generated table has same name as existing table but it has lowercase table name

Lowercase is a default case in PostgreSQL Server. So you should quote the names of your table and its columns:

Code: Select all

    new Devart.Data.PostgreSql.PgSqlMonitor() { IsActive = true };
    WebSecurity.InitializeDatabaseConnection("DefaultConnectionPostGres", "\"Contacts\"", "\"Id\"", "\"UserName\"", autoCreateTables: true);

If this doesn't help, specify the text of the error and the exact SQL statement which fails to execute (for this, check dbMonitor logs).

[garbgdev]

Thanks for your respond. I've apply above code to my InitializeDatabaseConnection() method.
Now I have following error.

Code: Select all

Exception Details: Devart.Data.PostgreSql.PgSqlException: relation "Contacts" already exists

And from the dbMonitor error is detected from following line.

Code: Select all

CREATE TABLE "Contacts" ("Id" SERIAL, "UserName" VARCHAR(240) NOT NULL UNIQUE, PRIMARY KEY ("Id") );

As I know if table is already exists, InitializeDatabaseConnection() does not create a new table. But it seems not from above result.

So I have change autoCreateTables: true to autoCreateTables: false.
Then I have this error.

Code: Select all

Devart.Data.PostgreSql.PgSqlException: relation "webpages_membership" does not exist

thanks in advance for your help

[garbgdev]

I finally manage to work it. But it leads to another problem.

When I register a new user, I wanted to add a value to my column "Age" with createUserAndAccount() method. "Age" column is already existing in my Contacts table.

Code: Select all

 WebSecurity.CreateUserAndAccount(model.UserName, model.Password, new { Age = 21});

However I'm getting following error.

Code: Select all

System.Configuration.Provider.ProviderException: Invalid column name Age

[Shalex]

We are investigating the issue. We will notify you about the result as soon as possible.

[Shalex]

The bug with inserting quoted user-defined fields via WebSecurity.CreateUserAndAccount method of SimpleMembership provider is fixed. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.

[Shalex]

New build of dotConnect for PostgreSQL 7.2.55 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=3&t=28503.