The SqlDataSource is designed to query data from a Microsoft SQL database. Result can then be aggregated with data stored in SharePoint lists by means of AggregateDataSource or presented directly.

Properties

  • Scope – Not Used with SqlDatasource.
  • Lists - Although not directly used by the SqlDatasource, this field must contain a value so that the data source functions correctly. A default value of 100 will work here.
  • QueryRowLimit – Maximum number of items returned in the Data Source.
  • ID – Unique identifier for the SQL data source. This ID will also be used as a prefix to the connection string ID for the connection string defined in the web config file.
  • SqlQuery – Specifies the query that will be executed. Supports live fields
  • Fields
    • This node should contain FieldRef nodes that reference the fields returned in the data source. The field name should match the column names returned by the query you have defined.
  • Query - not used.

Web.Config configuration

In addition to the properties set in the datasource, a connection string must be defined in the web.config of web application where the web part is used. The name of the connection string must comply with the following format: PS.Webparts.Rollup.SqlDataSourceConnection.Data Source ID

Sample Configuration

<%@ Register TagPrefix="cc1" Namespace="PS.WebParts.Rollup" Assembly="PS.WebParts.Rollup, Version=12.0.0.0, Culture=neutral, PublicKeyToken=90e3045b123af1c3" %>


<cc1:SqlDataSource runat="server" ID="DSC1" Lists="100"  QueryRowLimit="100" Scope="Recursive" SqlQuery="SELECT ID, FIRSTNAME,  LASTNAME
FROM  CUSTOMERS">
<Fields>
     <FieldRef Name="ID" />
     <FieldRef Name="FIRSTNAME" />
     <FieldRef Name="LASTNAME" />
</Fields>
<Query>
</Query>
</cc1:SqlDataSource>

web.config
<connectionStrings>
<add name="PS.WebParts.Rollup.SqlDataSourceConnection.DSC1"  connectionString="Data Source=[database server name];  Initial Catalog=[database name]; Integrated Security=SSPI" providerName="System.Data.SqlClient"/>
</connectionStrings>

Last edited Apr 2, 2009 at 8:13 PM by ewilson, version 3

Comments

No comments yet.