This project has moved. For the latest updates, please go here.

DB Schema and Primary Keys

Nov 21, 2010 at 8:57 AM

Hi, Great work. I am using clsa40 and c# with your generator connecting to sqlserver 2008. I discovered that the store procs generated are not aware of the db schemas. at least the "insert" sproc. Also when i create a editable root class, the primary key option is set to UserProvidedPK by default, althought the table column is PK with identity insert. it used to be working in old versions. It might be my fault though. Please let me know if i am missing a setting somewhere. Keep up the good work.  Thanks.

Coordinator
Nov 21, 2010 at 12:27 PM

Hi webeyeint,

1) PK column with IsIdentity = true generates UserProvidedPK

Just tested with SQL Server 2005 and couldn't reproduce it even trying strange settings like IsIdentity but not PK, several PK, etc. This could be an issue with SQL Server 2008.

On the Schema tab, Columns panel, there is a read-only property grid. Selecting just the PK column, how does it show IsIdentity and IsPrimaryKey?

Currently I don't have access to SQL Server 2008. Can you test this under SQL Server 2005?

2) Insert sproc is generated incorrectly

I coudn't test your specific use case but every day I generate and regenerate sprocs that are as they should otherwise Tortoise SVN would show red bullets.

Could this be related to ValueProperty settings of 00. Database - Data Access Behaviour?

Nov 24, 2010 at 3:47 PM

Hi tiago and sorry for responding this late. After several tests on sqlserver 2005/2008, different PCs and operating systems i regenerated the error.

1-  I firstly created a new fresh database. I immediately run this code to generate schema and the table.

EXEC sys.sp_executesql N'CREATE SCHEMA [msf]'

CREATE TABLE [msf].[Asset](
 [AssetID] [int] IDENTITY(1,1) NOT NULL,
 [Format] [nvarchar](50) NULL,
 CONSTRAINT [PK_Asset] PRIMARY KEY CLUSTERED
( [AssetID] ASC )ON [PRIMARY]) ON [PRIMARY]

Now i started cslagen4 connected to database and see the IsIdentity=False and IsPrimaryKey=True

Then i run this.(pay attention to schema)

CREATE TABLE [dbo].[Asset](
 [AssetID] [int] IDENTITY(1,1) NOT NULL,
 [Format] [nvarchar](50) NULL,
 CONSTRAINT [PK_Asset] PRIMARY KEY CLUSTERED
( [AssetID] ASC )ON [PRIMARY]) ON [PRIMARY]

Now we have 2 tables with the same name and different schemas. if i reload the first table in cslagen now i can see the  IsIdentity=True and IsPrimaryKey=True. So i assume it is something to do with matching the schemas to table names. Or something wrong with me :)

2) Insert sproc is generated incorrectly: when the IsIdentity=True then the insert sproc is generated correctly.

Nov 24, 2010 at 4:15 PM

Sorry, i missed a point for the the 2nd item.  The "insert sproc" is still not aware of the [msf] schema in any case described earlier. it writes "INSERT INTO [Asset]". Actually it only works for select statements.

Coordinator
Nov 24, 2010 at 9:30 PM
Edited Nov 25, 2010 at 6:14 AM

I would say the issue is

DBSchemaInfo doesn't handle non 'dbo' schemas correctly.

Do you agree?

If you agree, I think I got fixed. Some statement has getting the OBJECT_ID for TABLE_NAME but didn't specify the TABLE_SCHEMA. This way the table is search only in the default schema.

I'll commit the patch in a minute.

Coordinator
Nov 24, 2010 at 11:33 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.