How to insert data into SQL Server Database Table using PowerShell?

Overview:

We implemented a PowerShell script for a Construction Engineering Company having headquarters in Boston, Massachusetts, United States; We came across a scenario was to insert records in MS SQL Server Database from the PowerShell script. In this blog, we will see how to enter data into the SQL Server - Database Table using PowerShell script. Let’s take an example of one real-life business use case.

Here we will have one table named “Employee” in SQL Server Database. We have two different columns named “EmpName” and “Designation”. We want to enter some Employee Information into this table. So, how can we achieve this using PowerShell?

Let’s get started! 

1. Let’s define some variables to insert the values to SQL Server - Database Table. Here we have 5 variables for 'Server', 'Database', 'TableName', ‘EmpName’, and ‘Designation’.
   

 $EmpName = 'Tejal','Khyati','Anikesh','Harsh'  
 $Designation = 'Developer'  
 $server = "Dev220"  
 $Database = "PoCDatabase"  
 $TableName = "dbo.Employee"  



2. Establish a connection for the SQL Server database using the below code snippet. Here we use the ‘Server’ and ‘Database’ variables to generate connection string.

 $Connection = New-Object System.Data.SQLClient.SQLConnection  
 $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"  
 $Connection.Open()  
 $Command = New-Object System.Data.SQLClient.SQLCommand  
 $Command.Connection = $Connection  



3. We will apply a loop for each employee's name and execute the command for inset into the table.
   

 foreach($Name in $EmpName){  
   $insertquery="   
   INSERT INTO $TableName  
       ([EmpName],[Designation])  
     VALUES   
       ('$Name','$Designation')"   
   $Command.CommandText = $insertquery  
   $Command.ExecuteNonQuery()  
 }  



Here we use the Insert into query command and execute the command. This query will insert the Employee Name and Designation field values in the table.


4. Close the connection of SQL. Use the following code snippet for the same.

    $Connection.Close();  
   

   
    Here is the complete code snippet to insert the data into the table.
   

 $EmpName = 'Tejal','Khyati','Anikesh','Harsh'  
 $Designation = 'Developer'  
 $server = "Dev220"  
 $Database = "KDC"  
 $TableName = "dbo.Employee"  
 $Connection = New-Object System.Data.SQLClient.SQLConnection  
 $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"  
 $Connection.Open()  
 $Command = New-Object System.Data.SQLClient.SQLCommand  
 $Command.Connection = $Connection  
 foreach($Name in $EmpName){  
   $insertquery="   
   INSERT INTO $TableName  
       ([EmpName],[Designation])  
     VALUES   
       ('$Name','$Designation')"   
   $Command.CommandText = $insertquery  
   $Command.ExecuteNonQuery()  
 }  
 $Connection.Close();  



5. Let’s execute our PowerShell script. This will insert the following code to SQL Server Database Table.
   
   
   
   

   Conclusion:

   This is how we can insert the data to SQL Server using PowerShell Script, hope this helps. Happy Scripting!!!

 If you have any questions you can reach out our SharePoint Consulting team here.

Configure OneDrive Sync Client to sync SharePoint Document Library to Local File System using PowerShell

Introduction:

We implemented an automated Document Library Sync mechanism with OneDrive Sync Client using PowerShell for Construction Engineering Company based out of Washington. Normally, we can sync a SharePoint Document Library with OneDrive Sync Client manually from the user interface very easily. But, here the requirement was to sync Document Library in a dynamic and automated manner with the combination of OneDrive Sync Client & PowerShell.

Scenario:

We were getting a CSV file with URLs of multiple document libraries generated every 24 hours. The requirement was to configure OneDrive Sync Client for URLs of Document Libraries received in the CSV file in an automated manner.  So, we implemented a PowerShell script that will read Document Library URLs from the CSV file and configure OneDrive Sync Client for all the Document Libraries. This PowerShell script then was configured in Windows Task Scheduler for automated execution every 24 hours. Let's see the step-by-step process for this implementation with an example scenario.

Steps & PowerShell Script:

Here, we have taken a CSV file, where we are storing the URLs of the Document Libraries. Please check the below screenshot for the same.

Let’s consider one thing:

• URL: https://spsite.sharepoint.com/sites/SpaceDemo/A1
  Here - 
• SpaceDemo = Name of the Site Collection
• A1 = Document Library Name

To configure the OneDrive Sync Client for a Document Library folder, we need to use the following command in PowerShell.

 odopen://sync/?siteId={” + $siteId + “}&webId={” + $webId + “}&listId={” + $listId + “}&listTitle=” + $listName + “&userEmail=” + $UPN + “&webUrl=” + $siteURL + "&webTitle=" +$webTitle   

Where - 

• siteId = SharePoint site collection siteId GUID, enclosed in curly brackets. We can get this GUID visiting https://<TenantName>.sharepoint.com/sites/<SiteName>/_api/site/id 
• webId = SharePoint site webId GUID, enclosed in curly brackets. We can get this GUID visiting https://<TenantName>.sharepoint.com/sites/<SiteName>/_api/web/id 
• webUrl = SharePoint site URL.      
• listId =  SharePoint site documents library GUID, enclosed in curly brackets. We can get this GUID visiting the document library in the browser, click in the gear icon and choosing "Library Settings". The URL will show the listId GUID at the end of URL, i.e. https://<tenant>.sharepoint.com/sites/<SiteName>/_layouts/15/listedit.aspx?List=%7Bxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx%7D  (a GUID with escaped curly brackets).
• userEmail =OneDrive's user email address used to sign in into OneDrive.
• <webTitle> and <listTitle> are used to compose the name of the local folder where the OneDrive content is synchronized. By default, when we use the "Sync" button when in the browser to synchronize a document library, OneDrive uses the SharePoint site name and the document library name to compose the local folder name, in the form of %userprofile%\<TenantName>\<SiteName> - <DocumentLibraryName>. We can use any other values. If we do not use these parameters, the local folder will be named " - Documents", despite of site and library names.

 

Here, in this sample we will find all above parameters dynamically. Please check the below script for the same.

 Write-Host “Please Enter your UserName” -ForegroundColor Yellow  
 $Username = Read-Host;Write-Host  
 Write-Host “Please Enter your Password” -ForegroundColor Yellow  
 $Password = Read-Host -AsSecureString;Write-Host  
 Import-Csv C:\DOCLIB.csv | ForEach-Object {  
 Write-Host "$($_.DocLibURL)"  
 #Write-Host “Input URL of the SharePoint Site with DOC Library Name and press Enter”  
 $siteURL1 = "$($_.DocLibURL)" #Read-Host;Write-Host  
 $listName = $siteURL1.Substring($siteURL1.LastIndexOf("/")+1)  
 $siteURL = $siteURL1.Substring(0,$siteURL1.LastIndexOf("/"))  
 $webTitle = $siteURL.Substring($siteURL.LastIndexOf("/")+1)  
  #Read-Host;Write-Host  
 $UPN = $Username  
 $creds = (New-Object System.Management.Automation.PSCredential $Username,(ConvertTo-SecureString $Password -AsPlainText -Force))  
 if($siteURL, $libName, $UPN -ne $null)  
 {  
 Connect-PnPOnline -url $siteURL -Credentials $cred  
 #Grabbing Site, Web, and List ID’s  
 $site = Get-PnPSite -Includes Id, URL  
 $siteIDtmp = $site.ID.toString()  
 #Adding some encoding here#  
 $siteID = “%7B” + $siteIDtmp + “%7D”  
 $web = Get-PnPWeb -includes Id, URL  
 $webIDtmp = $web.ID.toString()  
 #Adding some encoding here#  
 $webID = “%7B” + $webIDtmp + “%7D”  
 $list = Get-PnPList -Identity $listName -includes Id  
 $listIDtmp = $list.ID.toString()  
 #Adding some encoding here#  
 $listID = “%7B” + $listIDtmp + “%7D”  
 $resultTMP1 = “odopen://sync/?siteId={” + $siteIDtmp + “}&webId={” + $webIDtmp + “}&listId={” + $listIDtmp + “}&listTitle=” + $listName + “&userEmail=” + $UPN + “&webUrl=” + $siteURL + "&webTitle=" +$webTitle  
 Write-Host $resultTMP1  
 Start $resultTMP1  
 Write-Host "Completed for"+ $siteURL1 -ForegroundColor Green  
 }  
 else  
 {Write-Host “Missing one of the requested values! Please run script again and insert correct values”;return}  
 }   

Test the Script Execution:

First, enter organizational email.

Enter Password and then it will ask for sync. Click on Sync now.

Once the PowerShell script is executed, this will show the following message.

Conclusion:

This is how we can sync SharePoint Document Library with OneDrive. Happy scripting!!

If you have any questions you can reach out our SharePoint Consulting team here.

How to maintain new Log Files for every execution using Log4net for .NET Application?

Overview:

We recently implemented a Console Application for a Construction Engineering Company based out of Washington, United States using C# for automated execution with the help of Windows Tasks Scheduler to read the data from SharePoint List and create files in the local file system as per the requirements from the client. Most of us have used “Log4net” as a logging tool for our .NET Application. We came across some different requirements wherein we need to maintain the log for each execution in a separate log file. With OOTB configuration the logs are appended in a single file. So, how can we customize this thing?

To achieve this, we will add one custom function of log configuration in our program file. We will append the current date and time after the file name. Using the current date and time we could create a new file with a unique name on every execution. Using this method will create a new file on every execution. So, now let’s get started!

Step 1: Add NuGet Package for "Log4net"

Let’s start with creating an application in Visual Studio. After that, we will use the “Log4net” NuGet package. We will add the “Log4net” library from the Manage NuGet Package. Follow the below steps to add “Log4net”.

1. In the "Solution Explorer Window," select and right-click on your project
2. Click "Manage NuGet Packages..."
3. Click "Online" and then type log4net in the search box
4. Select the log4net package you would like to install
5. Click "Install" to start the installation process

Step 2: Add custom method for configuration 

We will create a new method in our class (.cs) file. You can use any class(.cs) file to create this method. Here, we will use the same class (.cs) file available in our solution!

Add the below lines of code.

     public static void initLog4Net()  
     {  
       try  
       {  
         var hierarchy = (log4net.Repository.Hierarchy.Hierarchy)log4net.LogManager.GetRepository();  
         hierarchy.Configured = true;  
         var rollingAppender = new log4net.Appender.RollingFileAppender  
         {  
           File = Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName) + "\\" +  
           "LogFiles\\" + "C#LogFile_" + DateTime.Now.ToString("yyyyMMddTHHmm") + ".log",  
           AppendToFile = true,  
           LockingModel = new log4net.Appender.FileAppender.MinimalLock(),  
           Layout = new log4net.Layout.PatternLayout("%date [%thread] %level %logger - %message%newline")  
         };  
         var traceAppender = new log4net.Appender.TraceAppender()  
         {  
           Layout = new log4net.Layout.PatternLayout("%date [%thread] %level %logger - %message%newline")  
         };  
         hierarchy.Root.AddAppender(rollingAppender);  
         hierarchy.Root.AddAppender(traceAppender);  
         rollingAppender.ActivateOptions();  
         hierarchy.Root.Level = log4net.Core.Level.All;  
       }  
       catch (global::System.Exception ex)  
       {  
         new Exception(ex.Message);  
       }  
     }  

Using the above code, we can create a new log file on every execution in the same folder from where we execute this application.

As shown in the below code snippet we declare our filename with appended date-time format.

 File = Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName) + "\\" +  
           "LogFiles\\" + "CDMDepartment_" + DateTime.Now.ToString("yyyyMMddTHHmm") + ".log",  

This will generate the new file in the “LogFiles” folder. The name of the log will start with “C#LogFile_” and with that, it will add the current date-time in (“yyyyMMddTHHmm”) format. We can change the name of the file as well as the date-time format.

Here we will make sure we would add the proper format of date and time based on how many times we execute this application in one day.

Step 3: Create an instance of the logger file

To create an instance of logger file add the below code within a class.

 public static class Program  
   {  
     private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);  
   }  

Step 4: Call the log file and use it

We need to call the method for creating a log. We need to call this method at the starting of the application so we can use it in our code.

 static void Main()  
     {  
         Console.Clear();  
         initLog4Net();  
         log.Info("Welcome to Application Program");  
     }  

As shown above, after a call of the “initLog4Net” function we used the “log.Info” to print this to the logger file.

We can also use other formats of the logging as following.

• Log.Error();
• Log.Debug();
• log.Warn();
• log.Fatal();

Now we can run our application and it will create a file as “C#LogFile_20201221T2238”.

Conclusion: 

This is how we can use the “Log4Net” library and we can create new a file on every execution of an application. Hope, this helps.

If you have any questions you can reach out our SharePoint Consulting team here.

Access Events from Shared Calendar Using Exchange Web Service and Store Events Data to SharePoint List with C# Console Application

Introduction:

We implemented the intranet solution for a consulting firm based out of Alpharetta, GA, USA. One of the requirements was to store events from Shared Outlook Calendar (Office 365) to SharePoint List for easier accessibility for the users. In this blog, we will learn how to get events from a Shared Outlook Calendar using Microsoft Exchange web service and store in SharePoint Online List with CSOM using C# console application.

So, now Let’s get started with the procedure to build the custom console application.

Step 1: Create Solution

1.  Open Visual Studio 2019, Click on Create New Project and then Choose Console App (.NET Framework).
   
2.  Give it a meaningful Project Name and in Framework select .NET Framework 4 and click "Create".
   

Step 2: Establish Connectivity with Microsoft Exchange Webservice

1. Install "Microsoft.Exchange.WebServices" NuGet package.
2. Add namespace “using Microsoft.Exchange.WebServices.Data;”
3. We will be using Exchange Service class to connect the Shared Calendar.
4. We can use the below piece of code to create the instance of ExchangeService and define the credentials & endpoint URL.
   

    ExchangeService service = new ExchangeService(ExchangeVersion.Exchange2010);  
    service.Credentials = new WebCredentials("userEmailAddress", "userPassword");  
    service.Url = new Uri("https://outlook.office365.com/ews/exchange.asmx");  
   

Step 3: Get Calendar Events

1. The calendar can contain some folder, with the use of the "FolderView" class we can retrieve the same. Constructor FolderView(100) Initializes a new instance of the FolderView class with the maximum number of returned folders specified.
   

    FolderView view = new FolderView(100);
   

   
   
2. Initialize a new instance of the FolderId class with the specified folder name and mailbox.
   

    FolderId folderToAccess = new FolderId(WellKnownFolderName.Calendar, "shared Email");  
    FolderId(WellKnownFolderName, Mailbox)  

   Use this constructor to link this folder ID to a well-known folder (for example, Inbox, Calendar or Contacts) in a specific mailbox.
   
   
3. FindFoldersResults Represents the results of a folder search operation.
   

    FindFoldersResults findFolderResults = service.FindFolders(WellKnownFolderName.Root, view);  
   

   FindFolders Obtains a list of folders by searching the subfolders of the specified folder.
   
   
4. CalendarFolder Represents a folder that contains appointments.
   

    var calendar2 = CalendarFolder.Bind(service,folderToAccess);  
   

   CalendarFolder.Bind(service,folderToAccess) binds to an existing calendar folder and loads its first-class properties. Calling this method results in a call to Exchange Web Services (EWS).
   
   
5. Define a date range view of appointments in the calendar folder search operations.
   

    CalendarView cv = new CalendarView(StartDate, EndDate);  



6. FindAppointments obtains a list of appointments by searching the contents of a specified folder.
   

    FindItemsResults<Appointment> fapts = service.FindAppointments(folderToAccess, cv);  
   

   FindItemsResults<Appointment> a collection of appointments that represents the contents of the specified folder.
   
   Now with the use of appointment class property, we can retrieve Event Subject, Organizer, and other property.
   
   
7. Here, we will get the Event Title and Organizer.

Step 4: Store in SharePoint List.

1. Once we get data, we can store it in SharePoint List with CSOM. We will add the Event subject and Organizer Name, for that we create one Single Line of Text and one Person column in a SharePoint List.
2. Add namespace “using Microsoft.SharePoint.Client;” and “using System.Security;”.
3. Represents the context for SharePoint objects and operations.
   

    ClientContext clientContext = new ClientContext(siteUrl);  
   



4. Give Email and Password, Gets or sets the authentication information for the client context
   

    clientContext.Credentials = new SharePointOnlineCredentials("username", "password");  
   



5. Connect List.
   

    List appointmentsList = clientContext.Web.Lists.GetByTitle("Appointments");  
   



6. ListItemCreationInformation specifies the properties of the new list item.
   

    ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();  
   

   ListItem represents an item or row in a list.
   
   
7. List.AddItem method returns a ListItem instance representing the creation of a new list item in the list.
   

    ListItem newItem = appointmentsList.AddItem(itemCreateInfo);  
   



8.  As we know to add items in Person columns into the list, we need to provide Id, below code, will get Id of the organizer.
   

    var demo = Appoint.Organizer;  
    string testName = demo.ToString();  
    var demo2 = testName.Substring(0, testName.IndexOf("<") - 1);  
    User userTest = clientContext.Web.EnsureUser(demo2);  
    clientContext.Load(userTest);  
    clientContext.ExecuteQuery();  
   

   
   
9. Now, the final step to insert a record into the SharePoint list.
   

    newItem["Title"] = Appoint.Subject;  
    newItem["Users"] = userTest.Id;  
    newItem.Update();  
    clientContext.ExecuteQuery();  
   

Complete Code

 using System;  
 using Microsoft.Exchange.WebServices.Data;  
 using Microsoft.SharePoint.Client;  
 using System.Security;  
 using Folder = Microsoft.Exchange.WebServices.Data.Folder;  
 namespace SharedCalender  
 {  
 class Program  
 {  
 static void Main(string[] args)  
 {  
 ExchangeService service = new ExchangeService(ExchangeVersion.Exchange2010);  
 service.Credentials = new WebCredentials("userEmailAddress", "userPassword");  
 service.Url = new Uri("https://outlook.office365.com/ews/exchange.asmx");  
 FolderView view = new FolderView(100);  
 FolderId folderToAccess = new FolderId(WellKnownFolderName.Calendar, "shared Email");  
 FindFoldersResults findFolderResults = service.FindFolders(WellKnownFolderName.Root, view);  
 foreach (Folder f in findFolderResults)  
 {  
 var calendar2 = CalendarFolder.Bind(service,folderToAccess);  
 DateTime StartDate = DateTime.Today.AddMonths(-1);  
 DateTime EndDate = DateTime.Today.AddMonths(1);  
 CalendarView cv = new CalendarView(StartDate, EndDate);  
 FindItemsResults<Appointment> fapts = service.FindAppointments(folderToAccess, cv);  
 if (fapts.Items.Count > 0)  
 {  
 foreach (Appointment Appoint in fapts)  
 {  
 string siteUrl = "https://constoso.sharepoint.com/sites/SiteName";  
 ClientContext clientContext = new ClientContext(siteUrl);  
 SecureString passWord = new SecureString();  
 foreach (char c in "Password".ToCharArray()) passWord.AppendChar(c);  
 clientContext.Credentials = new SharePointOnlineCredentials("userEmailAddress", passWord);  
 List appointmentsList = clientContext.Web.Lists.GetByTitle("TestList");  
 ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();  
 ListItem newItem = appointmentsList.AddItem(itemCreateInfo);  
 //get user id  
 var demo = Appoint.Organizer;  
 string testName = demo.ToString();  
 var demo2 = testName.Substring(0, testName.IndexOf("<") - 1);  
 User userTest = clientContext.Web.EnsureUser(demo2);  
 clientContext.Load(userTest);  
 clientContext.ExecuteQuery();  
 newItem["Title"] = Appoint.Subject;  
 newItem["Users"] = userTest.Id;  
 newItem.Update();  
 clientContext.ExecuteQuery()  
 }  
 }  
 Console.ReadLine();  
 }  
 }  
 }  
 }  

Conclusion:

This is how we can access Events from Shared Calendar using Exchange web service and store data to SharePoint list.

If you have any questions you can reach out our SharePoint Consulting team here.

Explained: Behavior of Date and Time column value in SharePoint List/Library

Introduction:

We implemented Intranet Portal on SharePoint Online for a Real Estate Agency based out of Washington, United States. We had to tackle several questions regarding Date and Time in SharePoint from different business users after go-live. We conducted training sessions with the business users and explained the behavior of Date and Time in SharePoint. In this article, we will see how SharePoint shows the value of the Date and Time column in lists/libraries.

Many times, we observe, value of the Date and Time column is different for different users and when we try to get the value of the same Date and Time column using Rest API, it will return a different value.

So, in this blog, we will understand why the value of the Date and Time column is different at different places.

Which settings SharePoint considers to show the value of the Date and Time column?

The date-time value that SharePoint shows does not depend on the time zone of the user's computer.

SharePoint considers the below time zones to show the value of the Date and Time column:

1. Time zone selected by currently logged-in user in their profile.
2. Time zone selected in Regional settings of the site settings.

How SharePoint shows the value of Date Time column?

Now we will understand how SharePoint shows the value of the date time column.

• If the user has selected a time zone in his/her profile, then SharePoint will display date time in that particular time zone.
• If the user has not selected a time zone in his/her profile, then SharePoint will display date time in the time zone which is selected in regional settings of the specific SharePoint site.

Let's understand this with an example:

Here, we have created one list and added a Date and Time column to the list. In site settings of this site collection, we have selected (UTC-08:00) Pacific Time (US and Canada) time zone.

We will check with two different users, which has different regional settings in their user profile.

• User 1: This user has selected (UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi time zone in his user profile.
• User 2: This user has not selected any time zone in his user profile.

Now, we are creating one item in the list as "User 1" and add below values:

• Now for "User 1", it will display the date-time value which was selected while creating the item. So, for User 1 it will display as below:

• As the item created by "User 1" and time zone selected by User 1 is (UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi, SharePoint will display date time in selected (UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi time zone.
• But for "User 2", it will display a different date time because "User 2" has not selected time zone in his user profile. So, it will display date time in the time zone selected in the regional settings of the site collection. It means it will display date time in (UTC-08:00) Pacific Time (US and Canada) time zone.

How SharePoint returns the value of Date and Time column in REST API?

• If we try to get the Date and Time column value of the above item using REST API, it will return a different value.
• Here we have hit the REST API in the browser it returns the value as shown below screenshot:

REST API: /_api/web/lists/getbytitle('Sample')/items?$select=Title,Sample_x0020_Date_x0020_Field

• So the value returned is not in the time zone of site collection or time zone of the current user's user profile's time zone.
• It is in UTC time because SharePoint stores all the DateTime values in UTC time (REST API returns Date and Time column value in UTC).

Conclusion:

This is how SharePoint shows the value of the Date and Time column. Hope this blog will be helpful for you!

If you have any questions you can reach out to our SharePoint Consulting team here.