Wednesday, November 11, 2020

Delete a certain number of items SharePoint List Powershell Script

Due to the closure of "the blog TechNet" I transfer all my articles in the blog :)

To delete a fixed number of items in the list of WSS, SharePoint server without putting them in the Recycle Bin, this is very convenient, since the cleaning of the Recycle Bin takes a certain amount of time for the Site Collection Administrator.
This script is easy to use after the migration or backup of the lists / libraries in which you need to delete a certain number of items.

Pre-requisites:
1.PowerShell 2.0 and higher
2.WSS or SharePoint server 2007, 2010, 2013, 2016, 2019.

Powershell script:
#Load System.Reflection.Assembly WSS or SharePoint server 2007 only
[System.Reflection.Assembly]::Load("Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("Microsoft.SharePoint.Publishing, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")
#PSSnapin SharePoint server 2010, 2013, 2016, 2019
if(!(Get-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction:SilentlyContinue)) 
{ 
    Add-PsSnapin Microsoft.SharePoint.PowerShell 
}
#Enter your parametrs here site, listname, count elements
$siteUrl = "http://sharepoint/sites/Department"
$listName = "Main"
$batchSize = 1000
$site = new-object Microsoft.SharePoint.SPSite($siteUrl)
$web = $site.OpenWeb()
write-host "Web is: $($web.Title)"
$list = $web.Lists[$listName];
write-host "List is: $($list.Title)"
while ($list.ItemCount -gt 0)
{
  write-host "Item count: $($list.ItemCount)"
  $batch = "<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>"
  $i = 0
  foreach ($item in $list.Items)
  {
    $i++
    write-host "`rProcessing ID: $($item.ID) ($i of $batchSize)" -nonewline
    $batch += "<Method><SetList Scope=`"Request`">$($list.ID)</SetList><SetVar Name=`"ID`">$($item.ID)</SetVar><SetVar Name=`"Cmd`">Delete</SetVar><SetVar Name=`"owsfileref`">$($item.File.ServerRelativeUrl)</SetVar></Method>"
    if ($i -ge $batchSize) { break }
  }
  $batch += "</Batch>"
  $result = $web.ProcessBatchData($batch)
  write-host "Emptying Recycle Bin..."
  $web.RecycleBin.DeleteAll()
  write-host
  $list.Update()
}

Happy Coding!

Check Out and In Document Library in SharePoint using ECMAScript, C#

Due to the closure of "the blog TechNet" I transfer all my articles in the blog :)

Checking out — and its counterpart, checking in — is just good document library etiquette. What better way to let others know that you’re making changes to a document than by checking it out? Checking out a document sets the Checked Out flag to Yes and stores the name of the person who checked out the document.
In this article add code how to check out and check in file from a document library in SharePoint server using ECMAScript or C#. I have a document library named "Shared Documents" which has the following document.

Pre-requisites:
1. SharePoint server 2007 and higher,
2. Visual studio (only using C#).

Check Out


Check In

JavaScript:
<script type="text/javascript">
var list;
var item;
var file;
//Check Out document
  function fileCheckOut() {
   var clientContext = SP.ClientContext.get_current();
   if (clientContext != undefined && clientContext != null) {
   var webSite = clientContext.get_web();
   this.list = webSite.get_lists().getByTitle("Shared Documents");
   this.item = list.getItemById(1);
   this.file = this.item.get_file();
   this.file.checkOut();
   clientContext.load(this.file)
   clientContext.executeQueryAsync(Function.createDelegate(this, this.OnLoadSuccess), Function.createDelegate(this, this.OnLoadFailed));
    }
}
//Check In document
  function fileCheckIn() {
   var clientContext = SP.ClientContext.get_current();
   if (clientContext != undefined && clientContext != null) {
   var webSite = clientContext.get_web();
   this.list = webSite.get_lists().getByTitle("Shared Documents");
   this.item = list.getItemById(1);
   this.file = this.item.get_file();
   this.file.checkIn();
   clientContext.load(this.file)
   clientContext.executeQueryAsync(Function.createDelegate(this, this.OnLoadSuccess), Function.createDelegate(this, this.OnLoadFailed));
    }
}
  function OnLoadSuccess(sender, args) {
   alert("Successfully operation!");
   window.location = window.location.pathname;
}
  function OnLoadFailed(sender, args) {
    alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}
</script>
<input id="btnFileCheckOut" onclick="fileCheckOut()" type="button" value="File Check Out" />
<input id="btnFileCheckIn" onclick="fileCheckIn()" type="button" value="File Check In" />

C#:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace CheckinCheckoutDemo
{
class MyDemo
{
static void Main(string[] args)
{
	using (SPSite site = new SPSite("http://SPSite"))
	{
		using (SPWeb web = site.OpenWeb())
		{
			SPDocumentLibrary docs = (SPDocumentLibrary)web.Lists["Shared Documents"];

			foreach (SPFile file in docs.RootFolder.Files)
			{
				if (file.CheckOutType == SPFile.SPCheckOutType.None)
				{
				file.CheckOut();
				}
			}
			// Getting the above Checked Out file.
			foreach (SPCheckedOutFile file in docs.CheckedOutFiles)
			{
				Console.WriteLine(file.LeafName); 
			}
			// Check in and add a comment.
			foreach (SPFile file in docs.RootFolder.Files)
			{
				if (file.CheckOutType != SPFile.SPCheckOutType.None)
				{
				file.CheckIn("Programmatically Checked In"); 
			}
		}
	}
}

Happy Coding!

Export List Items (MS SharePoint) to MS SQL server Database table using C#(CSOM)

Due to the closure of "the blog TechNet" I transfer all my articles in the blog :)

When developing a new project, I needed to download the data from the MS SharePoint list to the MS SQL Server table, where I used C#(CSOM). This code is preferable for me, flexible and not too resource-intensive, as a rule I use it for events by Custom Timer Job, but I am also sure that he is approaching the example and for Event Reciever and other opportunities. To work with C#(CSOM), use namespace (reference) Microsoft.SharePoint.Client.
ClientContext context = new ClientContext("http://sp/sites/test");
Web web = context.Web;
List list = context.Web.Lists.GetByTitle("DataList");
context.Load(list);
context.ExecuteQuery();
CamlQuery query = new CamlQuery();
query.ViewXml = "";
ListItemCollection allitems = list.GetItems(query);
context.Load(allitems);
context.ExecuteQuery();
SqlConnection cn = new SqlConnection();
DataSet EmpDataSet = new DataSet();
SqlDataAdapter da;
cn.ConnectionString = @"Data Source=SQLServer;Initial Catalog=DataTable;Integrated Security=SSPI";
cn.Open();
da = new SqlDataAdapter("select * from [dbo].[Table_Data]", cn);
SqlCommandBuilder builder = new SqlCommandBuilder(da);
da.Fill(EmpDataSet, "Table_Data");
DataTable table = EmpDataSet.Tables[0];
foreach (ListItem listitem in allitems)
{
    DataRow row = table.NewRow();
    row["Date"] = System.TimeZone.CurrentTimeZone.ToLocalTime(Convert.ToDateTime(listitem["Created"].ToString()));
    row["Column1"] = listitem["Title"].ToString();
    row["Column2"] = listitem["Event1"].ToString();
    row["Author"] = listitem["AuthorPosition"].ToString();
    row["DateToday"] = System.TimeZone.CurrentTimeZone.ToLocalTime(Convert.ToDateTime(listitem["Today"].ToString()));
    table.Rows.Add(row);
    builder.GetInsertCommand();
}
da.Update(EmpDataSet, "Table_Data");

Happy Coding!

Show file size documents library SharePoint in Kb, Mb (JQuery)

Due to the closure of "the blog TechNet" I transfer all my articles in the blog :)

You could add JQuery to obtain and display document size into your custom search display template. The code below is about how to show document size beside the document name of all documents uploaded into a SharePoint document library.
You could use the function in the code that obtaining the file size depend on the URL of the document.
You must have the rights to Design web part.

Pre-requisites
SharePoint server 2010, 2013, 2016, 2019.
<script type="text/javascript" src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
<script type="text/javascript">
function hdrDetails(i, elm, cl) { 
    cl = cl/1024;  //divide content-length by 1024 (KB) 
    var sz = cl>1024?"MB":"KB";  //if cl is still big, set to MB 
    cl = cl>1024?cl/1024:cl;  //if cl is still big, divide again 
    var len = $(elm).eq(i).text().length;  //check the link's text length 
    if(len > 0) { 
        //add a file size 
        $(elm).eq(i).after(" (" + cl.toFixed(2) + " " + sz + ")"); 
    } 
} 
$(function() { 
    var elm="a[href$='.pdf'],"+ //only the file types we want 
	"a[href$='.doc'],"+ 
	"a[href$='.ppt'],"+ 
	"a[href$='.xls'],"+ 
	"a[href$='.docx'],"+
	"a[href$='.pptx'],"+
	"a[href$='.mht'],"+
	"a[href$='.xlsx']"; 
    $(elm).each(function(i, e) { 
        if (e.hostname && e.hostname == location.hostname) { 
            $.ajax({ 
                type: "get", 
                url: $(this).attr("href"), 
                complete: function(xhr, textStatus) { 
                    var cl=xhr.getResponseHeader("content-length"); 
                    hdrDetails(i, elm, cl); //call the calculation fn 			
                } 
            }); 
        } 
    }); 
}); 
</script>

Happy Coding!

Export File (txt,csv) to SharePoint List using C#

Due to the closure of "the blog TechNet" I transfer all my articles in the blog :)

There are always non-standard tasks in MS SharePoint server, one of such tasks is the export of data from the file (txt, csv) to the SharePoint list using C# to the example in the Event Receiver. Do not forget, when using the data array 'split' string [] arr = line.Split (';') specify a character that separates the values of your array.
Demonstration, download the link above:

1.CSV-file:

2.Code C#:
using Microsoft.SharePoint;
using System;

namespace EventDeployTest2.EventReceiver2
{
    public class EventReceiver2 : SPItemEventReceiver
    {
        public override void ItemUpdated(SPItemEventProperties properties)
        {
            using (SPSite oSPsite = new SPSite("http://sharepoint/sites/test"))
            {
                using (SPWeb oSPWeb = oSPsite.OpenWeb())
                {
                    oSPWeb.AllowUnsafeUpdates = true;
                    SPList list = oSPWeb.Lists["Test"];
                    string[] lines = System.IO.File.ReadAllLines(@"\\sharepoint\sites\test\SiteAssets\user.csv");
                    SPListItem itemToAdd = null;
                    foreach (string line in lines)
                    {
                        string[] arr = line.Split(';');
                        itemToAdd = list.Items.Add();
                        itemToAdd["Name"] = Convert.ToString(arr[0]);
                        itemToAdd["Surname"] = Convert.ToString(arr[1]);
                        itemToAdd["Department"] = Convert.ToString(arr[2]);
                        itemToAdd["Post"] = Convert.ToString(arr[3]);
                        itemToAdd["Position"] = Convert.ToString(arr[4]);
                        itemToAdd.Update();
                    }
                    oSPWeb.AllowUnsafeUpdates = false;
                }
            }
        }
    }
}

3.Result (Export to SP List):

Happy Coding!

Get last value "Check In Comment" document library SharePoint REST API

Due to the closure of "the blog TechNet" I transfer all my articles in the blog :)

When you check in a document, you can add a brief comment about the changes you made. This is a great way for others to see a summary of your edits. If you ever need to edit a comment you can check out the document again, immediately check it back in, select Overwrite the current version, and then add the new comment in the Comment box.
This article shows how to use the REST API to load a last value from the "Check In comment" field in the document library.

Pre-requisites
1.SharePoint server 2013, 2016, 2019.
2.Editor for working with Javascript files.
window.onload= function(){
    // getting the GUID of list.
    var listGUID = _spPageContextInfo.pageListId;
    // getting the item id
    var itemId = '1';
    // clearing the value inside the tdCheckInComment
    $("#tdCheckInComment").html('');
    // checking if list guid found or not.
    if(listGUID)
    {
        //removing {} from the variable.
        listGUID = listGUID.replace(/[{}]/g, "");
        // checkin if item id is found or not.
        if(itemId)
        {
            var comment = GetLastCheckInComment(listGUID,itemId);
            if(comment)
            {
                $("#tdCheckInComment").html(comment);
            }
        }
    }
}
function GetLastCheckInComment(listGUID,itemId) {
    var returnValue;
    try {
        jQuery.ajax({
            url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists(guid'" + listGUID + "')/items(" + itemId + ")/File/CheckInComment",
            type: "GET",
            async: false,
            headers: { "Accept": "application/json;odata=verbose" },
            success: function (data, textStatus, xhr) {
                returnValue = data.d.CheckInComment;
            },
            error: function (data, textStatus, xhr) {
                console.error('Error while getting the last check in comment.');
            }
        });
    }
    catch (ex) {
    }
    return returnValue;
}
<table>
<tr>
    <th><label id="lblComment">Comment:  </label></th>
    <td id="tdCheckInComment"></td>
</tr>
</table>

Happy Coding!

Reset ID to 1 SharePoint list\library

Due to the closure of "the blog TechNet" I transfer all my articles in the blog :)

If you need to reset the list item ID, for example to the number "1" or the library in SharePoint, then you can use PowerShell and access the WSS_Content database to the required lists or library, for this we need to know the GUID list or library, example through SharePoint Designer.

Pre-requisites:
1. PowerShell 2.0 and higher,
2. WSS or SharePoint server 2007, 2010, 2013, 2016, 2019,
3. SharePoint Designer,
4. SQL Management studio.

Powershell script:
[System.Reflection.Assembly]::LoadWithPartialName("System.Data")
$conn = New-Object "System.Data.SqlClient.SqlConnection" -ArgumentList "server=SQLSERVER-01; database=USERLOCAL; integrated security=SSPI"
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "UPDATE WSS_Content.dbo.AllListsAux set NextAvailableId=1 where ListID='D4D2CF11-1F16-4122-BA50-05A6A9CFD27E'"
$cmd.ExecuteNonQuery()

Transact-SQL:
UPDATE WSS_Content.dbo.AllListsAux set NextAvailableId=1 where ListID='D4D2CF11-1F16-4122-BA50-05A6A9CFD27E'

Happy Coding!