Using existing table for simplemembership

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
garbgdev
Posts: 13
Joined: Fri 08 Nov 2013 03:20

Using existing table for simplemembership

Post by garbgdev » Mon 02 Dec 2013 02:22

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Using existing table for simplemembership

Post by Shalex » Mon 02 Dec 2013 17:40

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
Posts: 13
Joined: Fri 08 Nov 2013 03:20

Re: Using existing table for simplemembership

Post by garbgdev » Mon 02 Dec 2013 20:27

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Using existing table for simplemembership

Post by Shalex » Tue 03 Dec 2013 17:35

garbgdev wrote: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
Posts: 13
Joined: Fri 08 Nov 2013 03:20

Re: Using existing table for simplemembership

Post by garbgdev » Tue 03 Dec 2013 20:17

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
Posts: 13
Joined: Fri 08 Nov 2013 03:20

Re: Using existing table for simplemembership

Post by garbgdev » Tue 03 Dec 2013 21:14

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Using existing table for simplemembership

Post by Shalex » Fri 06 Dec 2013 17:16

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

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Using existing table for simplemembership

Post by Shalex » Wed 11 Dec 2013 13:28

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Using existing table for simplemembership

Post by Shalex » Thu 12 Dec 2013 14:07

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.

Post Reply