Using existing table for simplemembership

Using existing table for simplemembership

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

Re: Using existing table for simplemembership

Postby 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.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Re: Using existing table for simplemembership

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

Re: Using existing table for simplemembership

Postby 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).
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Re: Using existing table for simplemembership

Postby 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

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

Re: Using existing table for simplemembership

Postby Shalex » Fri 06 Dec 2013 17:16

We are investigating the issue. We will notify you about the result as soon as possible.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Re: Using existing table for simplemembership

Postby 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
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Re: Using existing table for simplemembership

Postby 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/postgresql/download.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.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL