GP Modifications

Topics: General Discussion Forum
Feb 17, 2007 at 10:21 AM
Edited Feb 18, 2007 at 3:05 PM
GP Modifications for xml table fields (Part 1)

Background: The Table Structure

The SQL table structure shown below is a pretty simple here for demonstration purposes. It shows an XML field of the address, the main reason for holding this as XML field structure is that there are many different address formats depends on the country and this simplifies the contact entity address into single table.

This could be done differently like using a NTEXT field which is supported by Receipts. But when not using XML field, it’s not possible to use SQL XML indexing features that are necessary for address search stored procedures in performance respect.

CREATE TABLE dbo.Contact(
ContactIDint IDENTITY(1,1) NOT NULL,
FirstNamevarchar(50) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
LastNamevarchar(50) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
MaidenNamevarchar(50) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
NamePrefixvarchar(50) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
NameSuffixvarchar(50) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
AddressCulturevarchar(5) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
AddressXmlxml NULL,
CreatedOndatetime NULL CONSTRAINT DF_Contact_CreatedOn DEFAULT (getutcdate()),
CONSTRAINT PK_Contact PRIMARY KEY CLUSTERED
(
ContactID ASC
)WITH (PADINDEX = OFF, IGNOREDUP_KEY = OFF) ON PRIMARY
) ON PRIMARY

Modifying GP receipt to allow create Business objects from database that containing xml fields.

When creating a business objects by using receipt “Create Business Entities from Database”. I don’t get xml data field type selected.
I am currently adding manually for generated business classes the following code for example:

private System.String addressXMLField;
public System.String AddressXML
{
get { return this.addressXMLField; }
set { this.addressXMLField = value; }
}

Please note: AddressXml field is String type in business entity as WSF gets/returns that as string from Enterprise Library Database Provider. Also this is common generic type to return for all RDBMS that support xml fields.

So, GP question here is where should I modify this receipt to select xml field and generate the above code?

Modifying GP to allow generate CRUD stored procedures when table contains XML fields.

Currently it is not possible to use Create CRUD Stored Procedure receipt when table contains an xml fields. So I have used CodeSmith tools to generate stored procedures.

So, where should I modify this GP receipt to allow generate CRUD store procedures such as generated by CodeSmith below?

------------------------------------------------------------------------------------------------------------------------
-- Generated By: Alexander using CodeSmith 4.0.0.0
-- Template: StoredProcedures.cst
-- Procedure Name: dbo.SelectContactsByContactID
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.SelectContactsByContactID
@ContactID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
ContactID,
FirstName,
LastName,
MaidenName,
NamePrefix,
NameSuffix,
AddressCulture,
AddressXml,
CreatedOn
FROM
dbo.Contact
WHERE
ContactID = @ContactID

GO

Modifying GP Receipt “Create Data Repository Classes from Business Entities” to allow correct code generation?

You can select the above generated stored procedure and map fields but generated code is not correct. Currently the GP receipt does not understand the modified business entity and leaves the method out, see the sample below:

int addressCultureIndex = reader.GetOrdinal("AddressCulture");
if(!reader.IsDBNull(addressCultureIndex))
{
contact.AddressCulture= reader.GetString(addressCultureIndex);
// Above is correct as reader has GetString method
}

int addressXMLIndex = reader.GetOrdinal("AddressXml");
if(!reader.IsDBNull(addressXMLIndex))
{
contact.AddressXML= reader.(addressXMLIndex);
// Above is not correct as reader has not method at all.
// but I can add this manually and it should be the same as
// address culture.
}

So, where can I modify this receipt in order that it would generate the code correctly?

A bit long story for part 1, but I just wanted to get it right by examples.

Alexander