Joseph Michael Pesch
VP Programming

C# Upload Local Document to SharePoint Site

by 22. February 2012 10:17

Below is a sample windows service that will run daily.  This method is used to open a local Excel file, refresh it's data connection(s), save the updated file locally and finally, upload the local file to a SharePoint site.

Command prompts to Delete and Create the Windows Service:

sc delete ServiceNameHere
sc create ServiceNameHere binpath= "c:\pathtoexe\AutomationService.exe"

Sample App.Config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="FileDictionary" type="System.Configuration.DictionarySectionHandler" />
  </configSections>
  <FileDictionary>
    <add key="C:\Data\File1.xlsx" value="http://mysharepoint/sites/reports/File1.xlsx" />
    <add key="C:\Data\File2.xlsx" value="http://mysharepoint/sites/reports/File2.xlsx" />
  </FileDictionary>
</configuration>

Sample Code:

using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Net;
using System.Runtime.InteropServices;
using System.ServiceProcess;
using Microsoft.Office.Interop.Excel;

namespace AutomationService
{
  public partial class AutomationServiceInstance : ServiceBase
  {

    string path = ConfigurationManager.AppSettings["FilePath"];
    // Go back one day so we run on start...
    DateTime lastRun = DateTime.Now.AddDays(-1);

    public AutomationServiceInstance()
    {
      InitializeComponent();
    }

    protected override void OnStart(string[] args)
    {
      System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
        , Char.ToString('\r') + Char.ToString('\n') 
        + "Starting service ... " + DateTime.Now.ToString());
      if(System.IO.File.Exists(path + @"\LastRun.txt"))
      {
        // If last run time file exists load the date/time from the file
        lastRun = Convert.ToDateTime(System.IO.File.ReadAllText(path + @"\LastRun.txt").Trim());
      }
      BackgroundWorker wkr = new BackgroundWorker();
      wkr.DoWork += TimeCycle;
      wkr.RunWorkerAsync();
    }

    protected override void OnStop()
    {
      System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
        , Char.ToString('\r') + Char.ToString('\n')
        + "Stopping service ... " + DateTime.Now.ToString());
    }


    protected void TimeCycle(object sender, DoWorkEventArgs e)
    {
      do
      {
        lastRun = Convert.ToDateTime(System.IO.File.ReadAllText(path + @"\LastRun.txt").Trim());
        System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n')
          + "Checking time ... " + DateTime.Now.ToString()
          + " lastRun: " + lastRun + "; curTime: " + DateTime.Now.ToString());
        if (!lastRun.DayOfYear.Equals(DateTime.Now.DayOfYear) 
          && DateTime.Now.TimeOfDay >= new TimeSpan(8,15,0)) ProcessIt();
        System.Threading.Thread.Sleep(60 * 1000);
      } while (true);
    }

    protected void ProcessIt()
    {
      // Key is the local source file, Value is the target web URL
      Hashtable tbl = (Hashtable)ConfigurationManager.GetSection("FileDictionary");
      foreach (DictionaryEntry de in tbl)
      {
        try
        {
          Application excel = new ApplicationClass();
          excel.DisplayAlerts = false;
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "Begin processing ... " + DateTime.Now.ToString());
          string srcFile = de.Key.ToString();
          string outFile = de.Value.ToString();
          // Open the file 
          // Important, do not double up on objects in COM 
          // Notice the Workbooks variable used to open the workbook 
          // DO NOT DO THIS: Workbook wkb = excel.Workbooks.Open(srcFile, false, true); 
          Workbooks wkbs = excel.Workbooks;
          Workbook wkb = wkbs.Open(srcFile, false, true);
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "     workbook open: " + DateTime.Now.ToString());
          wkb.EnableConnections();
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "     connections enabled: " + DateTime.Now.ToString());
          wkb.RefreshAll();
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "     workbook refreshed: " + DateTime.Now.ToString());
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "Processing complete ... " + DateTime.Now.ToString());
          // Clean up to release resources 
          // IMPORTANT: This cleanup sequence was required when running 
          // this inside a service that remains open and recycles on a schedule 
          // otherwise multiple EXCEL processes would be running and resources 
          // left open causing file access problems and memory leaks. 
          GC.Collect();
          GC.WaitForPendingFinalizers();
          wkb.Close(false, Type.Missing, Type.Missing);
          Marshal.ReleaseComObject(wkb);
          Marshal.ReleaseComObject(wkbs);
          excel.Quit();
          Marshal.ReleaseComObject(excel);
          GC.Collect();
          GC.WaitForPendingFinalizers();
          if (WebClientUpload(srcFile, outFile, path))
          {
            lastRun = DateTime.Now;
            System.IO.File.WriteAllText(path + @"\LastRun.txt", lastRun.ToString());
          }
        }
        catch (Exception ex)
        {
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n')
            + "Exception occurred ... " + DateTime.Now.ToString());
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n') + ex.Message);
          System.IO.File.AppendAllText(path + @"\AutomationServiceLog.txt"
            , Char.ToString('\r') + Char.ToString('\n') + ex.StackTrace);
        }
      }
    }
    static bool WebClientUpload(string SourcePath, string TargetPath, string LogPath)
    {
      WebClient wc = new WebClient();
      wc.UseDefaultCredentials = true;
      try
      {
        byte[] response = wc.UploadFile(TargetPath, "PUT", SourcePath);
      }
      catch (WebException ex)
      {
        System.IO.File.AppendAllText(LogPath + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n')
          + "Exception occurred ... " + DateTime.Now.ToString());
        System.IO.File.AppendAllText(LogPath + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n') + ex.Message);
        System.IO.File.AppendAllText(LogPath + @"\AutomationServiceLog.txt"
          , Char.ToString('\r') + Char.ToString('\n') + ex.StackTrace);
        return false;
      }
      return true;
    }
  }
}

Tags:

C# | Excel | Sharepoint

Microsoft SQL Server Stop Script Execution

by 14. February 2012 14:11

I have two basic styles of scripting to stop a Microsoft SQL Server TSQL script execution through SQL Management Studio.  The first is a soft stop where I will throw an exception message for the user to read and take action of (i.e. manually cancel the script execution at that point if necessary).  It will wait with a predefined arbitrary time delay and if the user takes no action within the time limit the script will continue.  The second is a hard stop where we do not leave it up to the user.  For the hard stop the script must be run in SQLCMD mode.  The second style involves one of two possible methods.  First would be simply setting the severity level of the raiserror statment to level 20.  However, this is only allowed if the user running the script is a member of sysadmin role. So, the fallback to that (i.e. if the user is not a member of the sysadmin role) is to first check to see if the user is running the script in SQLCMD mode, and, if they are not throw an error message telling them that they must run the script in SQLCMD mode or as a user who is member of sysadmin role.  Immediately after that set a maximum waitfor delay (24 hours is the max delay).  NOTE: The fallback script will still execute if the user leaves the connection open and running for the full 24 hour delay so this is still tecnically not a hard stop in all cases.

Style 1 - Soft Stop

declare @errMsg varchar(350)
select @errMsg = 'IMPORTANT: Read this message! '
  + ' Make sure you are running this script on the desired server. '
  + ' If this is not the desired server please cancel the script execution now! '
  + ' Otherwise, this script will execute in 30 seconds. '
  + ' The current server is: ' + @@servername
select @errMsg
print @errMsg
raiserror(@errMsg, 16, 1) with nowait
-- wait for the user to read the message, and terminate the script manually if need be
waitfor delay '00:00:30';

Style 2 - Hard Stop

:setvar SqlCmdMode "DbNameHere"
go
declare @errMsg varchar(350)
select @errMsg = 'IMPORTANT: Read this message! This script MUST be run in SQLCMD mode!  Please cancel script now and re-run in SQLCMD mode.'
if ('$(SqlCmdMode)' = '$' + '(SqlCmdMode)')     
if is_srvrolemember('sysadmins') = 1
  raiserror(@errMsg, 20, -1) with log 
else begin
    select @errMsg
    print @errMsg
    raiserror(@errMsg, 16, 1) with nowait
    -- wait for the user to read the message, and terminate the script.
    waitfor delay '23:59:59'; 
end
go

Tags:

Microsoft SQL Server Halt Script Execution On Error

by 14. February 2012 09:41

I have two basic styles of scripting to perform a halt during a Microsoft SQL Server TSQL script execution through SQL Management Studio.  The first is a soft halt where I will throw an exception message for the user to read and take action of (i.e. manually cancel the script execution at that point if necessary).  It will wait with a predefined arbitrary time delay and if the user takes no action within the time limit the script will continue.  The second is a hard halt where we do not leave it up to the user.  For the hard halt the script must be run in SQLCMD mode.  The second style involves one of two possible methods.  First would be simply setting the severity level of the raiserror statment to level 20.  However, this is only allowed if the user running the script is a member of sysadmin role.  So, the fallback to that (i.e. if the user is not a member of the sysadmin role) is to first check to see if the user is running the script in SQLCMD mode, and, if they are not throw an error message telling them that they must run the script in SQLCMD mode or as a user who is member of sysadmin role.  Immediately after that set a maximum waitfor delay (24 hours is the max delay).  NOTE: The fallback script will still execute if the user leaves the connection open and running for the full 24 hour delay so this is still tecnically not a hard stop in all cases.

Style 1 - Soft Halt

declare @errMsg varchar(350)
select @errMsg = 'IMPORTANT: Read this message! '
  + ' Make sure you are running this script on the desired server. '
  + ' If this is not the desired server please cancel the script execution now! '
  + ' Otherwise, this script will execute in 30 seconds. '
  + ' The current server is: ' + @@servername
select @errMsg
print @errMsg
raiserror(@errMsg, 16, 1) with nowait
-- wait for the user to read the message, and terminate the script manually if need be
waitfor delay '00:00:30'; 

Style 2 - Hard Halt

:setvar SqlCmdMode "DbNameHere"
go
declare @errMsg varchar(350)
select @errMsg = 'IMPORTANT: Read this message! This script MUST be run in SQLCMD mode!  Please cancel script now and re-run in SQLCMD mode.'
if ('$(SqlCmdMode)' = '$' + '(SqlCmdMode)')     
if is_srvrolemember('sysadmins') = 1
  raiserror(@errMsg, 20, -1) with log 
else begin
    select @errMsg
    print @errMsg
    raiserror(@errMsg, 16, 1) with nowait
    -- wait for the user to read the message, and terminate the script.
    waitfor delay '23:59:59'; 
end
go

Tags:

SQL Server