C# templates not retrieving all parameters in stored procedure

ChristianPena posted on Friday, May 25, 2007

Good morning,

I would like to first thank you for the CSLAContrib templates. They really make a big difference when generating partial classes.

The issue I am encountering occurs in the nightly build and release versions of the C# templates in CodeSmith 2.6 and 3.2 when interfacing with a SQL Express 2005 database. The issue occurs when generating a read only root list from a stored procedure accepting two parameters. The first parameter is incorporated into the template but the second parameter is not. As a result, the factory method accepts one parameter, the criteria class contains one parameter, and the dataportal fetch method only sends one parameter to the stored procedure being executed.

While I intend to debug the issue as best as I can when I have some time this weekend, I am wondering if anyone else has encountered the issue or can reproduce it.

Please let me know if you want me to provide sample input: table definitions, xml parameter sets, and the code generated as I have these in a zip file, but cannot upload them as this has been restricted. I will paste the contents here:

SQL Definitions:

CREATE TABLE [dbo].[Person](
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Group](
[GroupID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED
(
[GroupID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[GroupPerson](
[GroupID] [int] NOT NULL,
[PersonID] [int] NOT NULL,
CONSTRAINT [PK_GroupPerson] PRIMARY KEY CLUSTERED
(
[GroupID] ASC,
[PersonID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE PROCEDURE [dbo].[GetPersonInfo]
@LastName varchar(50) = '',
@GroupName varchar(50) = ''
AS
BEGIN
SET NOCOUNT ON;

SELECT Person.PersonID, FirstName, LastName,
  [Group].Name
FROM Person
  LEFT JOIN GroupPerson
   ON Person.PersonID = GroupPerson.PersonID
  LEFT JOIN [Group]
   ON GroupPerson.GroupID = [Group].GroupID
WHERE LastName LIKE @LastName
  AND Coalesce([Group].Name, '') LIKE @GroupName
END

XML Parameter Set used to generate the class:

<?xml version="1.0" encoding="utf-8"?>
<codeSmith>
<propertySet>
  <property name="ResultSetIndex">0</property>
  <property name="ObjectName">PersonInfoList</property>
  <property name="ChildName">PersonInfo</property>
  <property name="RootCommand">
   <connectionString>Data Source=WORKSTATION\SQLEXPRESS;Initial Catalog=Sample;Integrated Security=true;Connection Timeout=1;</connectionString>
   <providerType>SchemaExplorer.SqlSchemaProvider,SchemaExplorer.SqlSchemaProvider</providerType>
   <command>
    <owner>dbo</owner>
    <name>GetPersonInfo</name>
   </command>
  </property>
  <property name="ClassNamespace" />
  <property name="PropertyAuthorization">Both</property>
  <property name="AuthorizationRules">True</property>
  <property name="TransactionalType">None</property>
  <property name="GenerationMethod">Single</property>
  <property name="ClassType">Generated</property>
</propertySet>
</codeSmith>

Thanks in advance,

Christian

C# templates not retrieving all parameters in stored procedure

ChristianPena posted on Friday, May 25, 2007

ChristianPena replied on Friday, May 25, 2007