Joseph Michael Pesch
VP Programming

C# Draw Thermometer Goal

by 14. March 2012 07:59

This sample shows how to take a generic thermometer image (with transparent background and empty contents, i.e. outline only) and draw in the content using C#.  The base thermometer image is attached and full code sample below. 

Here are samples of the base image and a test output using the URL shown below:

DrawThermometer.aspx?colorString=%230A58BC&goalHeadText=Southwest Funding&goalBodyText=November Goal&goalAmt=75&actualAmt=55&amtSuffix=MM

Here is the sample code:

using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Drawing;

public partial class DrawThermometer : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    // Sample call:
    // DrawThermometer.aspx?colorString=%230A58BC&goalHeadText=Southwest Funding&goalBodyText=November Goal&goalAmt=75&actualAmt=55&amtSuffix=MM
    string colorString = Request.Params["colorString"];
    string goalHeadText = Request.Params["goalHeadText"];
    string goalBodyText = Request.Params["goalBodyText"];
    decimal goalAmt = Convert.ToDecimal(Request.Params["goalAmt"]);
    string amtSuffix = Request.Params["amtSuffix"];
    decimal actualAmt = 0;
    // ActualAmt is optional input parameter, when not provided we run a SQL query.
    if (Request.Params["actualAmt"] != null)
    {
      actualAmt = Convert.ToDecimal(Request.Params["actualAmt"]);
    }
    else
    {
      string sql = "select sum(SomeField) as actualAmt from SomeTable where Something = true";
      using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCN"].ConnectionString))
      {
        using (SqlCommand cmd = new SqlCommand(sql, cn))
        {
          cn.Open();
          actualAmt = Convert.ToInt32(cmd.ExecuteScalar());
          actualAmt = Convert.ToDecimal(actualAmt * Convert.ToDecimal(.000001));
          cn.Close();
        }
      }
    }
    DrawImage(colorString, goalHeadText, goalBodyText, goalAmt, actualAmt, amtSuffix);
  }
  private void DrawImage(string colorString, string goalHeadText, string goalBodyText, decimal goalAmt, decimal actualAmt, string amtSuffix)
  {
    // Locals
    int width = 325;
    int height = 100;
    int barLeft = 83;
    int barTop = 44;
    int barWidth = 220;
    int barHeight = 29;
    int actualWidth = 0;
    int intervalWidth = 20;
    string val = string.Empty;
    ColorConverter converter = new ColorConverter();
    Color fillColor = (Color)converter.ConvertFromString(colorString);
    string imagePath = Server.MapPath("~/images/Thermometer.png");
    using (Image image = Image.FromFile(imagePath))
    {
      using (Graphics g = Graphics.FromImage(image))
      {
        Color color = fillColor;
        SolidBrush fillBrush = new SolidBrush(color);
        g.FillEllipse(fillBrush, new Rectangle(8, 23, 70, 70));
        g.FillRectangle(fillBrush, new Rectangle(barLeft - 10, barTop, 10, barHeight));
        actualWidth = Convert.ToInt32((Convert.ToDecimal(actualAmt) / Convert.ToDecimal(goalAmt)) * Convert.ToDecimal(barWidth));
        // Adjust for width of markers between bars...
        actualWidth = actualWidth - Convert.ToInt32((Convert.ToDecimal(actualAmt) / Convert.ToDecimal(goalAmt)) * 2);
        if (actualWidth > barWidth) actualWidth = barWidth;
        // TALL: Rectangle rect = new Rectangle(45, 135, 29, 145);
        // WIDE: Rectangle rect = new Rectangle(83, 44, 221, 29);
        Rectangle rect = new Rectangle(barLeft, barTop, actualWidth, barHeight);
        g.FillRectangle(fillBrush, rect);
        g.TextRenderingHint = System.Drawing.Text.TextRenderingHint.AntiAlias;
        g.DrawString(goalBodyText, new Font("Tahoma", 14, FontStyle.Bold), fillBrush, barLeft, barTop - 40); // use 28 when printing the goalHeadText...
        val = "$" + goalAmt.ToString() + amtSuffix;
        g.DrawString(val, new Font("Tahoma", 14, FontStyle.Bold), fillBrush, width - val.Length * 18, barTop - 40); // use 28 when printing the goalHeadText...
        // Draw the current actual number
        // TALL: g.DrawString("55.55", new Font("Tahoma", 10, FontStyle.Bold), Brushes.White, 40, 275);
        // WIDE: g.DrawString("55.55", new Font("Tahoma", 10, FontStyle.Bold), Brushes.White, 16, 39);
        val = "$" + actualAmt.ToString("#.00") + amtSuffix;
        g.DrawString(val, new Font("Tahoma", 12, FontStyle.Bold), Brushes.White
          , width - (barWidth - actualWidth) - 5 - (val.Length * 14), 47);
        // Draw the intervals
        bool target = false;
        for (int i = 1; i <= 10; i++)
        {
          Font font = new Font("Arial Narrow", 8, FontStyle.Bold);
          Brush brush = fillBrush;
          int interval = ((goalAmt > Convert.ToInt32(actualAmt)) ? Convert.ToInt32(goalAmt) : Convert.ToInt32(actualAmt)) / 10;
          int mark = i * interval;
          val = "" + mark.ToString();
          if (!target && (i.Equals(10) || ((mark >= goalAmt) || goalAmt - mark < interval / 2)))
          {
            target = true;
            font = new Font("Arial Narrow", 8, FontStyle.Bold);
            brush = Brushes.Red;
          }
          g.DrawRectangle(new Pen(Brushes.Black), barLeft - 2 + (intervalWidth * i), height - 60, 1, 8);
          g.DrawRectangle(new Pen(Brushes.Black), barLeft - 2 + (intervalWidth * i), height - 33, 1, 8);
          g.DrawString(val, font, brush, barLeft + (intervalWidth * i) - (val.Length * 4), height - 18);
        }
      }
      System.IO.MemoryStream stream = new System.IO.MemoryStream();
      image.Save(stream, System.Drawing.Imaging.ImageFormat.Png);
      Context.Response.BinaryWrite(stream.ToArray());
    }

  }

}

Thermometer.png (5.78 kb)

Tags:

C#

Microsoft Word VBA Macro to Run Mail Merge on SQL DSN

by 8. March 2012 13:07

This is a sample macro to generate a dynamic SQL connection and SQL select statement using VBA in Microsoft Word.  The only item I was not able to resolve is getting the connection string to be DSN-less (i.e. it seems to always require and actual ODBC DSN).

Private Sub Document_Open()
  
  Dim msg As String
  msg = "Select ""Yes"" to run the standard half month interval query." & _
        vbCrLf & vbCrLf & _
        "For a custom query select ""No"" " & _
        "and go to Mailings > Edit Recipient List > Filter"
  RunQuery (MsgBox(msg, vbYesNo, "Run Standard Query") = vbYes)
  
End Sub

Private Function GetConnection() As String
  
  GetConnection = "DSN=DWSQL;" & _
       "Driver={SQL Server};" & _
       "Server=servername\instancename,portnumber;" & _
       "Database=databasename;" & _
       "Trusted_Connection=yes;" & _
       "UID=" & Environ("username") & ";" & _
       "WSID=" & Environ("computername")

End Function

Private Sub RunQuery(Standard As Boolean)
  
  Dim doc As Document
  Set doc = ActiveDocument

  Dim sql As String
  If Standard Then
    sql = "select * from TestMailView where StandardCriteria = 'Y'"
  Else
    sql = "select * from TestMailView"
  End If

  ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
  ActiveDocument.MailMerge.OpenDataSource Name:="", ConfirmConversions:=False, _
    ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:=GetConnection() _
    , SQLStatement:=sql, _
     SQLStatement1:="", SubType:=wdMergeSubTypeOther
     
  If Standard Then
    On Error GoTo MergeError
    With ActiveDocument.MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      .Execute Pause:=False
    End With
    'Close the main doc since we already generated the merged doc.
    doc.Close (False)
  End If
  
MergeError:
     If Err.Number <> 0 Then MsgBox Err.Description
  
End Sub

Tags:

Word Mail Merge Error: "Record 'number' contained too few data fields"

by 7. March 2012 10:44

Error message when you use an .odc file to connect to a specific table in a mail merge data source in Microsoft Word: "Record 'number' contained too few data fields"

This appears to be an issue when you use a SQL datasource and the table/view you are selecting is not in the default schema (i.e. dbo).  The Microsoft Support Article (link below) contains information on this.  Appears there is a registry setting (shown below) that can enable a fix for this.

Excerpt from the Microsoft Support Article:

How to enable the hotfix

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

To enable this hotfix, follow these steps:

  1. Exit all Office programs.
  2. Click Start, click Run, type regedit, and then click OK.
  3. If you are using a 2007 Office suite, locate and then click the following registry subkey:
    HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common
    If you are using Office 2003, locate and then click the following registry subkey:
    HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Common
  4. On the Edit menu, point to New, and then click DWORD Value.
  5. Type UseQualifiedTableNameForMailMerge, and then press ENTER.
  6. Right-click UseQualifiedTableNameForMailMerge, and then click Modify.
  7. In the Value data box, type 1, and then click OK.
  8. On the File menu, click Exit to exit Registry Editor.


Microsoft Support Article

Tags:

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

Excel to PDF Conversion Using Office Automation

by 31. January 2012 08:17

I wrote a simple C# method to open and Excel file and save it as PDF using basic Microsoft Office automation via the Excel COM.  In my case this method was inside a Windows Service that was scheduled to refresh the external data links (to datawarehouse and SSAS cube).  The initial code was problematic in that after the initial execution the output PDF file would often be locked causing the subsequent executions to fail.  Restarting the service would release the file, so it was apparent that the method was not cleanly releasing it's resources at end of execution. After some research and testing I now have a cleanup sequence that seems to reliably releasing all the resources so the service can now run indefinitely without conflict.  Below is a subset of the C# Excel COM code that illustrates the basic steps.

As a side note, while researching this issue I found an interesting article on using OpenOffice to perform a similar operation.  In my case I'm not sure it would work (at least not without further research and extending the method), as I need to refresh external data connections (which was not a feature covered in the sample I found).  However, it did sound like an interesting option as it only requires a few DLL's and not the full application install that the Office COM automation seems to require.  I included a simple version of the OpenOffice code (along with a reference link to the original article I read) below as well.  I will try to research this option later and update my post with the findings. 

Important side note:  Running this code on a Windows 2008 Server will fail with generic file access error if you don't have the folders shown below (i.e. create empty folder to resolve the issue):

  • Windows 2008 Server x64: C:\Windows\SysWOW64\config\systemprofile\Desktop
  • Windows 2008 Server x86: C:\Windows\System32\config\systemprofile\Desktop

 

Microsoft Excel Sample Code:

// Start application
Application excel = new ApplicationClass();
// Turn off message boxes
excel.DisplayAlerts = false;

string sourceFilePath = @"C:\SourceFile.xls"; 
string targetFilePath = @"C:\TargetFile.pdf";  

// 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(sourceFilePath, false, true);
Workbooks wkbs = excel.Workbooks;
Workbook wkb = wkbs.Open(sourceFilePath, false, true);

// Refresh data connections and do some formatting
wkb.EnableConnections();
wkb.RefreshAll();
Worksheet sht = wkb.Worksheets["Summary"] as Worksheet;
sht.get_Range("A1").EntireColumn.ColumnWidth = 21;
sht.get_Range("B1", "E1").EntireColumn.AutoFit();
sht.get_Range("A1").Select();

// Delete target file
File.Delete(targetFilePath);

// Export to PDF
sht.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF
, targetFilePath, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard
, true, false, Type.Missing, Type.Missing, false, Type.Missing);

// 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();
Marshal.FinalReleaseComObject(sht);
wkb.Close(false, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(wkb);
Marshal.ReleaseComObject(wkbs);
excel.Quit();
Marshal.ReleaseComObject(excel);
GC.Collect();
GC.WaitForPendingFinalizers();

Here is a good article on cleaning up Excel COM objects: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c-sharp.

OpenOffice Sample Code:

/*
You need the following DLL's from OpenOffice and reference them in your project:

cli_basetypes.dll 
cli_cppuhelper.dll 
cli_oootypes.dll 
cli_uno.dll 
cli_ure.dll 
cli_uretypes.dll 
*/

//...
using System;
using uno.util; 
using unoidl.com.sun.star.lang; 
using unoidl.com.sun.star.uno; 
using unoidl.com.sun.star.bridge; 
using unoidl.com.sun.star.frame; 
using unoidl.com.sun.star.text; 
using unoidl.com.sun.star.beans; 
using unoidl.com.sun.star.util;  
//...
  
string sourceFilePath = "file:///C:/SourceFile.xls"; 
string targetFilePath = "file:///C:/TargetFile.pdf";  

// Start OpenOffce or get a reference to an existing session 
XComponentContext localContext = Bootstrap.bootstrap(); 
XMultiServiceFactory multiServiceFactory = (XMultiServiceFactory)localContext.getServiceManager(); 
XComponentLoader componentLoader = (XComponentLoader)multiServiceFactory.createInstance("com.sun.star.frame.Desktop");  

// Set the file open properties
PropertyValue[] openProperties = new PropertyValue[2]; 
// Hidden...
openProperties[1] = new PropertyValue(); 
openProperties[1].Name = "Hidden"; 
openProperties[1].Value = new uno.Any(true);  
// Read only...
openProperties[0] = new PropertyValue(); 
openProperties[0].Name = "ReadOnly"; 
openProperties[0].Value = new uno.Any(true); 
// Open the file 
XComponent sourceDocument = componentLoader.loadComponentFromURL(sourceFilePath, "_blank", 0, openProperties);  

// Conversion parameters - overwrite existing file, use PDF exporter 
PropertyValue[] conversionProperties = new PropertyValue[3]; 
conversionProperties[0] = new PropertyValue(); 
conversionProperties[0].Name = "Overwrite"; 
conversionProperties[0].Value = new uno.Any(true); 
conversionProperties[1] = new PropertyValue(); 
conversionProperties[1].Name = "FilterName"; 
conversionProperties[1].Value = new uno.Any("calc_pdf_Export");  

// Set PDF export parameters 
PropertyValue[] filterData = new PropertyValue[3];  
// JPEG compression quality 70 
filterData[0] = new PropertyValue(); 
filterData[0].Name = "Quality"; 
filterData[0].Value = new uno.Any(70); 
filterData[0].State = PropertyState.DIRECT_VALUE;  
// Max image resolution 300dpi 
filterData[1] = new PropertyValue(); 
filterData[1].Name = "ReduceImageResolution"; 
filterData[1].Value = new uno.Any(true); 
filterData[1].State = PropertyState.DIRECT_VALUE; 
filterData[2] = new PropertyValue(); 
filterData[2].Name = "MaxImageResolution"; 
filterData[2].Value = new uno.Any(300); 
filterData[2].State = PropertyState.DIRECT_VALUE;  
conversionProperties[2] = new PropertyValue(); 
conversionProperties[2].Name = "FilterData"; 
conversionProperties[2].Value = new uno.Any(filterData.GetType(), filterData);  
// Export the PDF 
XStorable xstorable = (XStorable)sourceDoc; 
xstorable.storeToURL(pdfFile, conversionProperties);  

// Close document 
((XCloseable)sourceDoc).close(false); 
((XCloseable)xstorable).close(false);

OpenOffice API documentation can be found here: http://www.openoffice.org/api/.  Here is a link to the artical I used for basis of OpenOffice version of code: http://stackoverflow.com/questions/769246/xls-to-pdf-conversion-inside-net).

Tags:

C# | Excel

Excel to PDF Conversion Using Office Automation and/or Open Office

by 30. January 2012 10:23

One option to converting Excel documents to PDF is using Microsoft Office automation.  However, this requires installing the full application on the server that will be performing the automation (see this link for more details on that: ).  Another alternate solution is to switch to OpenOffice which only requires installing a few DLL's on the automation server.  Another option that I will review in the future is using the "ExcelLibrary" http://code.google.com/p/excellibrary/downloads/list

Microsoft Excel Sample Code:

// Start application
Application excel = new ApplicationClass();
// Turn off message boxes
excel.DisplayAlerts = false;

string sourceFilePath = @"C:\SourceFile.xls"; 
string targetFilePath = @"C:\TargetFile.pdf";  

// 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(sourceFilePath, false, true);
Workbooks wkbs = excel.Workbooks;
Workbook wkb = wkbs.Open(sourceFilePath, false, true);

// Refresh data connections and do some formatting
wkb.EnableConnections();
wkb.RefreshAll();
Worksheet sht = wkb.Worksheets["Summary"] as Worksheet;
sht.get_Range("A1").EntireColumn.ColumnWidth = 21;
sht.get_Range("B1", "E1").EntireColumn.AutoFit();
sht.get_Range("A1").Select();

// Delete target file
File.Delete(targetFilePath);

// Export to PDF
sht.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF
, targetFilePath, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard
, true, false, Type.Missing, Type.Missing, false, Type.Missing);

// 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();
Marshal.FinalReleaseComObject(sht);
wkb.Close(false, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(wkb);
Marshal.ReleaseComObject(wkbs);
excel.Quit();
Marshal.ReleaseComObject(excel);
GC.Collect();
GC.WaitForPendingFinalizers();

Here is a good article on cleaning up Excel COM objects: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c-sharp.

OpenOffice Sample Code:

/*
You need the following DLL's from OpenOffice and reference them in your project:

cli_basetypes.dll 
cli_cppuhelper.dll 
cli_oootypes.dll 
cli_uno.dll 
cli_ure.dll 
cli_uretypes.dll 
*/

//...
using System;
using uno.util; 
using unoidl.com.sun.star.lang; 
using unoidl.com.sun.star.uno; 
using unoidl.com.sun.star.bridge; 
using unoidl.com.sun.star.frame; 
using unoidl.com.sun.star.text; 
using unoidl.com.sun.star.beans; 
using unoidl.com.sun.star.util;  
//...
  
string sourceFilePath = "file:///C:/SourceFile.xls"; 
string targetFilePath = "file:///C:/TargetFile.pdf";  

// Start OpenOffce or get a reference to an existing session 
XComponentContext localContext = Bootstrap.bootstrap(); 
XMultiServiceFactory multiServiceFactory = (XMultiServiceFactory)localContext.getServiceManager(); 
XComponentLoader componentLoader = (XComponentLoader)multiServiceFactory.createInstance("com.sun.star.frame.Desktop");  

// Set the file open properties
PropertyValue[] openProperties = new PropertyValue[2]; 
// Hidden...
openProperties[1] = new PropertyValue(); 
openProperties[1].Name = "Hidden"; 
openProperties[1].Value = new uno.Any(true);  
// Read only...
openProperties[0] = new PropertyValue(); 
openProperties[0].Name = "ReadOnly"; 
openProperties[0].Value = new uno.Any(true); 
// Open the file 
XComponent sourceDocument = componentLoader.loadComponentFromURL(sourceFilePath, "_blank", 0, openProperties);  

// Conversion parameters - overwrite existing file, use PDF exporter 
PropertyValue[] conversionProperties = new PropertyValue[3]; 
conversionProperties[0] = new PropertyValue(); 
conversionProperties[0].Name = "Overwrite"; 
conversionProperties[0].Value = new uno.Any(true); 
conversionProperties[1] = new PropertyValue(); 
conversionProperties[1].Name = "FilterName"; 
conversionProperties[1].Value = new uno.Any("calc_pdf_Export");  

// Set PDF export parameters 
PropertyValue[] filterData = new PropertyValue[3];  
// JPEG compression quality 70 
filterData[0] = new PropertyValue(); 
filterData[0].Name = "Quality"; 
filterData[0].Value = new uno.Any(70); 
filterData[0].State = PropertyState.DIRECT_VALUE;  
// Max image resolution 300dpi 
filterData[1] = new PropertyValue(); 
filterData[1].Name = "ReduceImageResolution"; 
filterData[1].Value = new uno.Any(true); 
filterData[1].State = PropertyState.DIRECT_VALUE; 
filterData[2] = new PropertyValue(); 
filterData[2].Name = "MaxImageResolution"; 
filterData[2].Value = new uno.Any(300); 
filterData[2].State = PropertyState.DIRECT_VALUE;  
conversionProperties[2] = new PropertyValue(); 
conversionProperties[2].Name = "FilterData"; 
conversionProperties[2].Value = new uno.Any(filterData.GetType(), filterData);  
// Export the PDF 
XStorable xstorable = (XStorable)sourceDoc; 
xstorable.storeToURL(pdfFile, conversionProperties);  

// Close document 
((XCloseable)sourceDoc).close(false); 
((XCloseable)xstorable).close(false);

OpenOffice API documentation can be found here: http://www.openoffice.org/api/.  Here is a link to the artical I used for basis of OpenOffice version of code: http://stackoverflow.com/questions/769246/xls-to-pdf-conversion-inside-net).

Tags:

C# | Excel

C# Determine if Folder is Empty

by 28. January 2012 21:25
if (!Directory.EnumerateFileSystemEntries(FolderPath).Any())
{ 
  System.Diagnostics.Debug.WriteLine("Folder is empty");
}

Tags:

C#

SQL Server Management Studio "Invalid Object Name ..." when Object Does Exist

by 25. January 2012 10:06

I was struggling to understand why the SSMS intellisense was highlighting certain objects in my query window and indicating that they were not valid; when in fact, they did exist.  I tried closing and re-opening the studio, refreshing the local cache (Edit -> IntelliSense -> Refresh Local Cache (CRTL+Shift+R)).  All to no avail.  I finally realized the issue was due to the fact that the top of my SQL script had statements to drop and recreate some of the objects dynamically (e.g. if exists(...) drop XXX go select * into XXX from YYY).  It turns out that even though the objects actively existed in the database the fact that the script had a conditional drop statement of the objects, the IntelliSense seemed to override reality with fiction (i.e. thinking they are or will be dropped, perhaps?).  Anyway, if I simply commented out the drop statements all the errors went away.  Strange...

Tags:

SQL Server

SharePoint 2010 Default Log File Location

by 24. January 2012 07:29

Since I always seem to forget the path to the SharePoint 2010 default log folder... Here it is: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS

Tags:

Sharepoint

Transform URL List into HTML Unordered List

by 23. January 2012 16:16
This method will take a flat list of fully qualified URL's and transform them into nested bullets (i.e. HTML unordered lists).  It is a simple method to convert the flat list of URL's into a psuedo tree view.  Here is the C# code to transform the list.
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Xml;
using System.Xml.Xsl;

namespace UrlListToSiteMap
{
  public class Program
  {
    public static void Main(string[] args)
    {
      /*
         This method accepts a text file of fully qualified URLs 
         and will convert into HTML unordered list. 
      */
      string path = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location);
      List urlRecords = File.ReadAllLines(path + @"\UrlList.txt").ToList();
      urlRecords.Sort();
      XmlDocument doc = new XmlDocument();
      doc.AppendChild(doc.CreateNode(XmlNodeType.Element, "SiteMap", ""));
      XmlNode rootNod = doc.ChildNodes[0];
      foreach (string url in urlRecords)
      {
        XmlNode pathNod = rootNod;
        int iNod = 0;
        int iNods = url.ToLower()
          .Replace(@"http://", "").Replace("'", "").Split('/').Count();
        foreach (string nod in url.ToLower()
          .Replace(@"http://", "").Replace("'", "").Split('/'))
        {
          iNod++;
          System.Diagnostics.Debug.WriteLine(nod);
          XmlNode subNod = pathNod.SelectSingleNode("Node[@URL='" + nod + "']");
          if (subNod == null)
          {
            System.Diagnostics.Debug.WriteLine(pathNod.Name);
            string nodType = iNod.Equals(iNods) ? "Leaf" : "Node";
            subNod = doc.CreateNode(XmlNodeType.Element, nodType, "");
            XmlAttribute atr = doc.CreateAttribute("URL");
            atr.Value = nod;
            subNod.Attributes.Append(atr);
            pathNod.AppendChild(subNod);
          }
          pathNod = subNod;
        }
      }
      doc.Save(path + @"\SiteMap.xml");
      XslTransform xslt;
      xslt = new XslTransform();
      xslt.Load(path + "SiteMap.xslt");
      xslt.Transform(path + "SiteMapNew.xml", path + "SiteMap.html");
    }
  }
}

Attached is the XSLT file for the transformation. NOTE: If you only want the folders and not the actual documents, remove the "Leaf" section from the XSLT.

SiteMap.xslt (583.00 bytes)

Tags:

C# | HTML

SharePoint Deploy Workflow Feature to Server Farm

by 11. January 2012 13:24

This post covers steps used to deploy the workflow feature to a SharePoint site (or sub-site as shown in this example URL: http://ServerNameHere/sites/SubSiteNameHere).  Attached is a sample SharePoint workflow feature that can configured to perform a daily refresh of an Excel file from a SharePoint site (i.e. refreshing the data connections to update data from external sources).

To setup the Visual Studio Solution, you will need to change the URL located in the three areas shown below:

  1. Project -> Site URL -> http://ServerNameHere/sites/SubSiteNameHere
  2. Web References -> ExcelWebService -> Web Reference URL -> http://ServerNameHere/sites/SubSiteNameHere/_vti_bin/excelservice.asmx
  3. Web References -> ListService -> Web Reference URL -> http://ServerNameHere/sites/SubSiteNameHere/_vti_bin/lists.asmx

When deploying the workflow feature on the server (either through Visual Studio right-click and “Deploy”; or, via Power Shell script shown below) I received the following error message:

Error occurred in deployment step 'Activate Features': Feature with Id '{GUID}' is not installed in this farm, and cannot be added to this scope.

Power Shell Deployment Script:

rem NOTE: Make sure the "Sharepoint 2010 Administration" service is NOT running.
rem We are using the execadmsvcjobs to execute rather than letting th Timer service run them.
rem ADDING...
cd "c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN"
stsadm -o addsolution -filename "C:\VSS\ExcelRefreshWorkflow\bin\Debug\ExcelRefreshWorkflow.wsp"
stsadm -o deploysolution -name "ExcelRefreshWorkflow.wsp" -immediate -allowgacdeployment -allowcaspolicies
stsadm -o execadmsvcjobs
stsadm -o activatefeature -name "ExcelRefreshWorkflow_Feature1" -url http://ServerNameHere/sites/SubSiteNameHere

NOTE: This feature is being deployed to “Scope = Site”, not Farm, Web or Web Application scope.

image

If you are running a single server instance of Sharepoint this should not happen (i.e. your deployment should be completed successfully). In the case of a Sharepoint server farm you will need to perform the following steps manually to complete the installation.

  1. From the “SharePoint 2010 Central Administration” site:
    • Navigate to: “System Settings” -> “Manage farm solutions”
    • Click on “excelrefreshworkflow.wsp”
    • Click on “Deploy Solution” and complete the deployment screen (schedule or run immediately).
    • Refresh the farm solutions screen until the solution management screen until the status column shows “Deployed” (note, the “Deployed To” column should show “Globally deployed.”).
    • NOTE: Make sure the “SharePoint 2010 Timer” service is running; otherwise, this step will not complete.
  2. Open the normal SharePoint site and from the top level site:
    • Navigate to: “Site Actions” -> “Site Settings” -> “Site Collection Administration” -> “Site collection features”
    • Click the “Activate” button associated with the “ExcelRefreshWorkflow” item in the list.
    • NOTE: Repeat this step on every sub-site that you want to use the workflow in.
  3. At this point the installation is complete. You can now navigate to the sub-site and configure the workflow on the appropriate Library and activate it on the appropriate documents in the library.
    • From the sub-site home page, navigate to: “Library Tools” -> “Library” -> “Library Settings” -> “Workflow Settings”.
    • From this screen you should see the “ExcelRefreshWorkflow” item in the “Select a workflow template:” list. Select that item, complete the remaining configuration settings and click “OK”.
    • For each document desired, click on the document dropdown and select “Workflows”.
    • Click on the “ExcelRefreshWorkflow”
    • Setup the appropriate configuration settings and click the “Start Workflow” button.
    • NOTE: All activity related to the workflow (including the execution history) will be stored in the associated “Workflow History”.

Attached is a sample workflow that will open an Excel file in document library, refresh the data connection and save the file as a new version in the document library on a scheduled interval.

ExcelRefreshWorkflow.zip (422.11 kb)

Tags:

Sharepoint | Workflow Foundation

Deploying SharePoint Workflow Feature to Server Farm

by 11. January 2012 11:52

 

Assume you are trying to deploy the workflow feature to a sub-site:

(http://ServerNameHere/sites/SubSiteNameHere)

 

To setup the Visual Studio Solution, change the URL located in three areas:

1.       Project -> Site URL -> http://ServerNameHere/sites/SubSiteNameHere

2.       Web References -> ExcelWebService -> Web Reference URL -> http://ServerNameHere/sites/SubSiteNameHere/_vti_bin/excelservice.asmx

3.       Web References -> ListService -> Web Reference URL -> http://ServerNameHere/sites/SubSiteNameHere/_vti_bin/lists.asmx

 

When deploying the workflow feature on the server (either through Visual Studio right-click and “Deploy”; or, via Power Shell script shown below) I received the following error message:

 

Error occurred in deployment step 'Activate Features': Feature with Id '{GUID}' is not installed in this farm, and cannot be added to this scope.

 

Power Shell Deployment Script:

rem NOTE: Make sure the "Sharepoint 2010 Administration" service is NOT running.

rem We are using the execadmsvcjobs to execute rather than letting th Timer service run them.

 

rem ADDING...

cd "c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN"

stsadm -o addsolution -filename "C:\VSS\ExcelRefreshWorkflow\bin\Debug\ExcelRefreshWorkflow.wsp"

stsadm -o deploysolution -name "ExcelRefreshWorkflow.wsp" -immediate -allowgacdeployment -allowcaspolicies

stsadm -o execadmsvcjobs

stsadm -o activatefeature -name "ExcelRefreshWorkflow_Feature1" -url http://ServerNameHere/sites/SubSiteNameHere

 

NOTE: This feature is being deployed to “Scope = Site”, not Farm, Web or Web Application scope.

 

If you are running a single server instance of Sharepoint this should not happen (i.e. your deployment should be completed successfully).  In the case of a Sharepoint server farm you will need to perform the following steps manually to complete the installation.

 

1.       From the “SharePoint 2010 Central Administration” site:

a.       Navigate to: “System Settings” -> “Manage farm solutions”

b.      Click on “excelrefreshworkflow.wsp”

c.       Click on “Deploy Solution” and complete the deployment screen (schedule or run immediately).

d.      Refresh the farm solutions screen until the solution management screen until the status column shows “Deployed” (note, the “Deployed To” column should show “Globally deployed.”).

e.      NOTE: Make sure the “SharePoint 2010 Timer” service is running; otherwise, this step will not complete.

2.       Open the normal SharePoint site and from the top level site:

a.       Navigate to: “Site Actions” -> “Site Settings” -> “Site Collection Administration” -> “Site collection features”

b.      Click the “Activate” button associated with the “ExcelRefreshWorkflow” item in the list.

c.       NOTE: Repeat this step on every sub-site that you want to use the workflow in.

3.       At this point the installation is complete.  You can now navigate to the sub-site and configure the workflow on the appropriate Library and activate it on the appropriate documents in the library.

a.       From the sub-site home page, navigate to: “Library Tools” -> “Library” -> “Library Settings” -> “Workflow Settings”.

b.      From this screen you should see the “ExcelRefreshWorkflow” item in the “Select a workflow template:” list.  Select that item, complete the remaining configuration settings and click “OK”.

c.       For each document desired, click on the document dropdown and select “Workflows”.

                                                   i.      Click on the “ExcelRefreshWorkflow”

                                                 ii.      Setup the appropriate configuration settings and click the “Start Workflow” button.

                                                iii.      NOTE: All activity related to the workflow (including the execution history) will be stored in the associated “Workflow History”.

Tags:

Sharepoint

SQL Server Get First and Last Day's of Previous, Current and Next Month

by 10. January 2012 09:55

 

select

  [First Day of Previous Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(getdate())-1)

  , dateadd(mm, -1, getdate()))

  , 101)

, [Last Day of Previous Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(getdate()))

  , getdate())

  ,101)

, [First Day of Current Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(getdate())-1)

  , getdate())

  , 101)

, [Last Day of Current Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(dateadd(mm, 1, getdate())))

  , dateadd(mm, 1, getdate()))

  , 101)

, [First Day of Next Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(dateadd(mm, 1, getdate()))-1)

  , dateadd(mm, 1, getdate()))

  , 101)

, [Last Day of Next Month] =

  convert(varchar(25)

  , dateadd(dd, -(day(dateadd(mm, 1, getdate())))

  , dateadd(mm, 2, getdate()))

  , 101)

 

Tags:

SQL Server

ASP.Net Compile File System Website

by 6. January 2012 16:10

Below is an example of command program to compile an ASP.Net file system web using .Net framework v4 (the rem comment lines show the value for compiling as v2 as well).

rem v2.0.50727
rem v4.0.30319
set frmwk=C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319

set src=C:\vss\MyWebSiteFolder
set dest=C:\vss\MyCompiledWebSiteFolder

del /F /Q %dest%\*.*

%frmwk%\aspnet_compiler -v /VwdSite -p %src% %dest% -c

Tags:

ASP.Net

ASP.Net Compile File System Website

by 6. January 2012 15:53

Below is an example of command program to compile an ASP.Net file system web using .Net framework v4 (the rem comment lines show the value for compiling as v2 as well).

rem v2.0.50727
rem v4.0.30319
set frmwk=C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319

set src=C:\vss\MyWebSiteFolder
set dest=C:\vss\MyCompiledWebSiteFolder

del /F /Q %dest%\*.*

%frmwk%\aspnet_compiler -v /VwdSite -p %src% %dest% -c

Tags:

ASP.Net

Excel Calculated Cell Expecting 0 and Getting Scientific Exponential Notation Instead (e.g. 1.23E+10)

by 5. January 2012 14:38

When adding up several cells of positive and negative decimals (that total 0) I was instead getting a scientific exponential notation like 1.23E+10.  To solve this problem I changed the "Number Format" property on the calculated cell from "General" to "Number" with a fixed set of decimal places and the issue went away.

Tags:

Excel

CheckSum Utility

by 2. January 2012 16:32

Below is a C# implementation of two CheckSum utilities, the SHA256Managed uses the native .Net Security.Cryptography; while the Md5SumByProcess uses a windows implementation of Unix MD5Sum.exe (zip file attached to this blog can be downloaded from SourceForge at: http://sourceforge.net/projects/unxutils/files/unxutils/current/UnxUtils.zip/download). 

using System;
using System.Diagnostics;
using System.IO;
using System.Security.Cryptography;

  class CheckSumUtil
  {
    public static string Md5SumByProcess(string file)
    {
      var p = new Process();
      p.StartInfo.FileName = "md5sum.exe";
      p.StartInfo.Arguments = file;
      p.StartInfo.UseShellExecute = false;
      p.StartInfo.RedirectStandardOutput = true;
      p.Start();
      p.WaitForExit();
      string output = p.StandardOutput.ReadToEnd();
      return output.Split(' ')[0].Substring(1).ToUpper();
    }

    public static string SHA256Managed(string file)
    {
      using(var stream = new BufferedStream(File.OpenRead(file), 1200000))
      {     
        SHA256Managed sha = new SHA256Managed();
        byte[] checksum = sha.ComputeHash(stream);
        return BitConverter.ToString(checksum).Replace("-", String.Empty);
      }
    }

  }
}

UnxUtils.zip (3.21 mb)

Tags:

C#

LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

by 27. December 2011 15:54

LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

One workaround for this is to convert the LINQ to Entities object to a LINQ to Objects object by adding .AsEnumerable() as in the sample below.

var qry = from itm in obj.Booksheets.AsEnumerable()
          where bks.Purchases.FirstOrDefault() == null
          select new
          {
            FormattedPrice = bks.WebPrice.ToString("#,###"),
          };

Tags:

LINQ | LINQ to Entities