/// <summary>
///
/// </summary>
/// <param name="list"></param>
/// <param name="evaluateRecordCount"></param>
/// <param name="includeDocumentContent">
/// For document libraries, when this parameter is true, returns an additionnal field containing the file content.
/// Ignored if the list is not a document library.</param>
/// <returns></returns>
public IDataReader GetListItemsAsReader(SPListDefinition list, bool evaluateRecordCount, bool includeDocumentContent)
{
// when returning a datareader, we do it on a separate connection so we do not
// exclusively lock the main connection. (Data Readers monopolize the connection until they are closed).
//
// In order to prevent the connection pool to grow too much, we set the autoCloseConnection option to true
// on the ExecuteReader method.
Data.DatabaseConnector oDB = _DB.Clone();
if (evaluateRecordCount)
{
return(oDB.ExecuteReader(this.GetListItemsQuery(list, includeDocumentContent, true) + ";" + this.GetListItemsQuery(list, includeDocumentContent, false), true));
}
else
{
return(oDB.ExecuteReader(this.GetListItemsQuery(list, includeDocumentContent, false), true));
}
}
public void Add(SPListDefinition item)
{
this.InnerList.Add(item);
}
private string GetListItemsQuery(SPListDefinition list, bool includeDocumentContent, bool countQuery)
{
StringBuilder sbSQL = new StringBuilder();
bool bFirst = true;
sbSQL.Append("SELECT ");
if (countQuery)
{
sbSQL.Append("COUNT(*)");
}
else
{
SPFieldDefinitionCollection oFields = list.Fields;
//Note: Beware, the includeDocumentContent works ONLY on document libraries.
// for custom lists we must use GetListItemAttachmentsList method.
if (includeDocumentContent)
{
oFields = new SPFieldDefinitionCollection();
oFields.AddRange(list.Fields);
oFields.Add(new SPFieldDefinition("DocContent", "DocContent", "Docs.Content"));
}
foreach (SPFieldDefinition oField in oFields)
{
if (bFirst)
{
bFirst = false;
}
else
{
sbSQL.Append(", ");
}
sbSQL.AppendFormat("{0} AS [{1}]", oField.GetCompletePhysicalName(), oField.DisplayName);
}
}
// Join between UserData and Docs occurs only for Document Libraries
// Docs.Type = 0 = File
// Docs.Type = 1 = Folder
sbSQL.Append(" FROM UserData ");
if (list.ListType == SharepointListType.DocumenLibrary)
{
// 2007/05/01: Bug Fix - Thanks to Merijn Boom
// This query was returning some invalid records (one per folder found in document libraries)
// that caused the exporter to crash.
//
// The fix is to perform an INNER JOIN instead of a LEFT JOIN to keep only records of type "File"
// (exluding entries of type "Folder") when processing a document library.
//
// 2009/01/29: Bug Fix - Thanks to Finn Olesen for discovering the issue.
// I have removed a condition in the INNER JOIN (on Doc.Version = UserData.tp_version)
// that have nothing to do here. The unique value of a document in a document library
// can be obtain with only the 2 following fields: ListId, DocLibRowId
sbSQL.Append("INNER JOIN Docs ON (Docs.ListId = UserData.tp_ListId AND Docs.DocLibRowId = UserData.tp_ID AND Docs.Type = 0) ");
}
sbSQL.Append("LEFT JOIN UserInfo Author ON (Author.tp_ID = UserData.tp_Author AND Author.tp_SiteID = UserData.tp_SiteID) ");
sbSQL.Append("LEFT JOIN UserInfo Editor ON (Editor.tp_ID = UserData.tp_Editor AND Editor.tp_SiteID = UserData.tp_SiteID) ");
sbSQL.AppendFormat("WHERE UserData.tp_ListId = '{0}' ", list.ID);
sbSQL.AppendFormat("AND UserData.tp_IsCurrent = CONVERT(bit, 1) ");
return(sbSQL.ToString());
}
public DataTable GetListItemsAsDataTable(SPListDefinition list)
{
return(_DB.ExecuteDataSet(this.GetListItemsQuery(list, false, false), "ListItems").Tables["ListItems"]);
}
public void Add(SPListDefinition item)
{
this.InnerList.Add(item);
}
private string GetListItemsQuery(SPListDefinition list, bool includeDocumentContent, bool countQuery)
{
StringBuilder sbSQL = new StringBuilder();
bool bFirst = true;
sbSQL.Append("SELECT ");
if (countQuery)
{
sbSQL.Append("COUNT(*)");
}
else
{
SPFieldDefinitionCollection oFields = list.Fields;
//Note: Beware, the includeDocumentContent works ONLY on document libraries.
// for custom lists we must use GetListItemAttachmentsList method.
if (includeDocumentContent)
{
oFields = new SPFieldDefinitionCollection();
oFields.AddRange(list.Fields);
oFields.Add(new SPFieldDefinition("DocContent", "DocContent", "Docs.Content"));
}
foreach (SPFieldDefinition oField in oFields)
{
if (bFirst)
bFirst = false;
else
sbSQL.Append(", ");
sbSQL.AppendFormat("{0} AS [{1}]", oField.GetCompletePhysicalName(), oField.DisplayName);
}
}
// Join between UserData and Docs occurs only for Document Libraries
// Docs.Type = 0 = File
// Docs.Type = 1 = Folder
sbSQL.Append(" FROM UserData ");
if (list.ListType == SharepointListType.DocumenLibrary)
{
// 2007/05/01: Bug Fix - Thanks to Merijn Boom
// This query was returning some invalid records (one per folder found in document libraries)
// that caused the exporter to crash.
//
// The fix is to perform an INNER JOIN instead of a LEFT JOIN to keep only records of type "File"
// (exluding entries of type "Folder") when processing a document library.
//
// 2009/01/29: Bug Fix - Thanks to Finn Olesen for discovering the issue.
// I have removed a condition in the INNER JOIN (on Doc.Version = UserData.tp_version)
// that have nothing to do here. The unique value of a document in a document library
// can be obtain with only the 2 following fields: ListId, DocLibRowId
sbSQL.Append("INNER JOIN Docs ON (Docs.ListId = UserData.tp_ListId AND Docs.DocLibRowId = UserData.tp_ID AND Docs.Type = 0) ");
}
sbSQL.Append("LEFT JOIN UserInfo Author ON (Author.tp_ID = UserData.tp_Author AND Author.tp_SiteID = UserData.tp_SiteID) ");
sbSQL.Append("LEFT JOIN UserInfo Editor ON (Editor.tp_ID = UserData.tp_Editor AND Editor.tp_SiteID = UserData.tp_SiteID) ");
sbSQL.AppendFormat("WHERE UserData.tp_ListId = '{0}' ", list.ID);
sbSQL.AppendFormat("AND UserData.tp_IsCurrent = CONVERT(bit, 1) ");
return (sbSQL.ToString());
}
/// <summary>
///
/// </summary>
/// <param name="list"></param>
/// <param name="evaluateRecordCount"></param>
/// <param name="includeDocumentContent">
/// For document libraries, when this parameter is true, returns an additionnal field containing the file content.
/// Ignored if the list is not a document library.</param>
/// <returns></returns>
public IDataReader GetListItemsAsReader(SPListDefinition list, bool evaluateRecordCount, bool includeDocumentContent)
{
// when returning a datareader, we do it on a separate connection so we do not
// exclusively lock the main connection. (Data Readers monopolize the connection until they are closed).
//
// In order to prevent the connection pool to grow too much, we set the autoCloseConnection option to true
// on the ExecuteReader method.
Data.DatabaseConnector oDB = _DB.Clone();
if (evaluateRecordCount)
return (oDB.ExecuteReader(this.GetListItemsQuery(list, includeDocumentContent, true) + ";" + this.GetListItemsQuery(list, includeDocumentContent, false), true));
else
return (oDB.ExecuteReader(this.GetListItemsQuery(list, includeDocumentContent, false), true));
}
public DataTable GetListItemsAsDataTable(SPListDefinition list)
{
return (_DB.ExecuteDataSet(this.GetListItemsQuery(list, false, false), "ListItems").Tables["ListItems"]);
}
