March 17, 2005

LLBLGen Foreign Key problem

If you have a problem with FOREIGN KEY(s) in LLBLGen application, you can follow this way:
LLBLGen creates a big table of your database information with one ALTER PROCEDURE that can you find it on their codes. The "IsForeignKey" field is showing the "FOREIGN KEY" that it might be wrong. To solve this problem you can go the source code and correct the "PROCEDURE" just with putting one "TOP 1" in before every "SELECT" like this:
string sSQL = "SELECT INFORMATION_SCHEMA.COLUMNS.*, (SELECT COLUMNPROPERTY (OBJECT_ID (@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsComputed')" + ") AS IsComputed,(SELECT COL_LENGTH(@sTableName, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME )) AS ColumnLength, (SELECT " +"COLUMNPROPERTY (OBJECT_ID(@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity')) AS IsIdentity,(SELECT" + "COLUMNPROPERTY(OBJECT_ID(@sTableName), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsRowGuidCol')) AS IsRowGuidColumn,(ISNULL(" + "(SELECT TOP 1 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=@sTableName AND TABLE_SCHEMA='dbo' AND COLUMN_NAME=" + "INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME=@sTableName " + "AND TABLE_SCHEMA='dbo' AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME)" + "), 0)) AS IsPrimaryKey,(ISNULL((SELECT TOP 1 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @sTableName AND TABLE_SCHEMA='dbo'" + "AND COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = TABLE_NAME=@sTableName AND TABLE_SCHEMA = 'dbo'" + "AND COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE " + "TABLE_NAME = @sTableName AND TABLE_SCHEMA='dbo' AND CONSTRAINT_TYPE = 'UNIQUE' AND CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME)" + "), 0)) AS HasUniqueConstraint FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @sTableName AND TABLE_SCHEMA='dbo'";

داستان اینه که با یه "TOP 1" در وسط سورس کد برنامة LLBLGen مشکلش با 'FOREIGN KEY' ها حل می شه. پیشنهاد می کنم قبل از Copy/Paste تکه کد بالا رو با اصلش مقایسه کنید.

1 comment:

Anonymous said...

putting to waste some good money that could have been used in other areas of development for the business involved.