locked
Add a alias column in business data list webpart. RRS feed

  • Question

  • Hello Folks,

    I am using Business Data List WebPart in one of my application and now its time to customize it. i have below requirement:

    My earlier query to database was:

    SELECT column1, column2, column3 FROM TABLE

    and i wish to change it to:

    SELECT *,(column1+ column2) as column3 from TABLE

    my question here is how to add column3(alias) column in Business Data List Webpart.

    I'm not sure if i need to make changes in ADF file , XSL of webpart OR both of them.

    Appreciate your inputs.

    Thanks,

    -N

     

    Thursday, December 1, 2011 2:48 PM

Answers

  • Hi Nilesh,

    I would suggest you do it as follows.

    Create a view (for example):

    CREATE VIEW [dbo].[viewTest]
    AS
    SELECT Column1, Column2, Column3, Column2 + Column3 AS NewColumn
    FROM dbo.TestTable
    


    Create an application definition file and name the columns using LocalizedDisplayNames as you wish (for example):

    <Entities>
      <Entity EstimatedInstanceCount="0" Name="dbo.viewTest">
        <Identifiers>
          <Identifier TypeName="System.Int32" Name="[Column1]" />
        </Identifiers>
        <Methods>
          <Method Name="Getdbo.[viewTest]">
            <Properties>
              <Property Name="RdbCommandText" Type="System.String">Select [Column1],[Column2],[Column3],[NewColumn] From dbo.[viewTest]</Property>
              <Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
            </Properties>
            <Parameters>
              <Parameter Direction="Return" Name="dbo.[viewTest]">
                <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[viewTest]DataReader">
                  <TypeDescriptors>
                    <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[viewTest]DataRecord">
                      <TypeDescriptors>
                        <TypeDescriptor TypeName="System.Int32" Name="Column1" IdentifierName="[Column1]">
                          <LocalizedDisplayNames>
                            <LocalizedDisplayName LCID="1033">First Column</LocalizedDisplayName>
                          </LocalizedDisplayNames>
                        </TypeDescriptor>
                        <TypeDescriptor TypeName="System.Int32" Name="Column2">
                          <LocalizedDisplayNames>
                            <LocalizedDisplayName LCID="1033">Second Column</LocalizedDisplayName>
                          </LocalizedDisplayNames>
                        </TypeDescriptor>
                        <TypeDescriptor TypeName="System.Int32" Name="Column3">
                          <LocalizedDisplayNames>
                            <LocalizedDisplayName LCID="1033">Third Column</LocalizedDisplayName>
                          </LocalizedDisplayNames>
                        </TypeDescriptor>
                        <TypeDescriptor TypeName="System.Int32" Name="NewColumn">
                          <LocalizedDisplayNames>
                            <LocalizedDisplayName LCID="1033">The last column</LocalizedDisplayName>
                          </LocalizedDisplayNames>
                        </TypeDescriptor>
                      </TypeDescriptors>
                    </TypeDescriptor>
                  </TypeDescriptors>
                </TypeDescriptor>
              </Parameter>
            </Parameters>
            <MethodInstances>
              <MethodInstance Name="dbo.[viewTest]Finder" Type="Finder" ReturnParameterName="dbo.[viewTest]" ReturnTypeDescriptorName="dbo.[viewTest]DataReader" ReturnTypeDescriptorLevel="0" />
            </MethodInstances>
          </Method>
          <Method Name="dbo.[viewTest]SpecificFinder">
            <Properties>
              <Property Name="RdbCommandText" Type="System.String">Select [Column1],[Column2],[Column3],[NewColumn] From dbo.[viewTest] Where ([Column1]=@Column1)</Property>
              <Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
            </Properties>
            <Parameters>
              <Parameter Direction="In" Name="@Column1">
                <TypeDescriptor TypeName="System.Int32" Name="[Column1]" IdentifierName="[Column1]" />
              </Parameter>
              <Parameter Direction="Return" Name="dbo.[viewTest]">
                <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[viewTest]DataReader">
                  <TypeDescriptors>
                    <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[viewTest]DataRecord">
                      <TypeDescriptors>
                        <TypeDescriptor TypeName="System.Int32" Name="Column1" IdentifierName="[Column1]" />
                        <TypeDescriptor TypeName="System.Int32" Name="Column2" />
                        <TypeDescriptor TypeName="System.Int32" Name="Column3" />
                        <TypeDescriptor TypeName="System.Int32" Name="NewColumn" />
                      </TypeDescriptors>
                    </TypeDescriptor>
                  </TypeDescriptors>
                </TypeDescriptor>
              </Parameter>
            </Parameters>
            <MethodInstances>
              <MethodInstance Name="dbo.[viewTest]SpecificFinder" Type="SpecificFinder" ReturnParameterName="dbo.[viewTest]" ReturnTypeDescriptorName="dbo.[viewTest]DataReader" ReturnTypeDescriptorLevel="0" />
            </MethodInstances>
          </Method>
          <Method Name="dbo.[viewTest]IdEnumerator">
            <Properties>
              <Property Name="RdbCommandText" Type="System.String">Select [Column1] From dbo.[viewTest]</Property>
              <Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
            </Properties>
            <Parameters>
              <Parameter Direction="Return" Name="dbo.[viewTest]IDs">
                <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[viewTest]IDs">
                  <TypeDescriptors>
                    <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[viewTest]">
                      <TypeDescriptors>
                        <TypeDescriptor TypeName="System.Int32" Name="Column1" IdentifierName="[Column1]" />
                      </TypeDescriptors>
                    </TypeDescriptor>
                  </TypeDescriptors>
                </TypeDescriptor>
              </Parameter>
            </Parameters>
            <MethodInstances>
              <MethodInstance Name="dbo.[viewTest]EnumeratorInstance" Type="IdEnumerator" ReturnParameterName="dbo.[viewTest]IDs" />
            </MethodInstances>
          </Method>
        </Methods>
      </Entity>
    </Entities>
    


    After that you will have:

     


    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts
    Friday, December 2, 2011 10:31 PM