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.