Joseph Michael Pesch
VP Programming

ASP.Net C# Export GridView Control Data to Excel File

by 22. October 2012 08:44

This is a simple method to export data directly from an ASP.Net GridView control into an Excel file (CSV formatted).

protected void ExportGridViewDataButton_Click(object sender, EventArgs e)
{
  StringBuilder sb = new StringBuilder();
  foreach (TableCell cel in this.GridViewControl.HeaderRow.Cells)
  {
    sb.Append("\"").Append(cel.Text.Replace(" ", "")).Append("\",");
  }
  sb.Append(((Char)10).ToString());
  foreach (GridViewRow row in this.GridViewControl.Rows)
  {
    foreach (TableCell cel in row.Cells)
    {
      sb.Append("\"").Append(cel.Text.Replace(" ", "")).Append("\",");
    }
    sb.Append(((Char)10).ToString());
  }
  Response.Clear();
  Response.AddHeader("Content-Disposition"
    , "attachment;filename=GridViewExport.csv");
  Response.ContentType = "text/csv";
  Response.Write(sb.ToString());
  Response.Flush();
  Response.End();
}

Tags:

C# | Excel

Combine Binary Files Using C#

by 19. October 2012 09:07
using (var fs = File.OpenWrite("combined.bin"))
{
  var buffer = File.ReadAllBytes("file1.bin");
  fs.Write(buffer, 0, buffer.Length);
  buffer = File.ReadAllBytes("file2.bin");
  fs.Write(buffer, 0, buffer.Length);
  fs.Flush();
}

Tags:

C#

Sys Undefined Exception in ASP.Net Web Form Menu Control Generated JS with URL Routing

by 16. October 2012 08:07

Although the exception only seems to occur when using URL routing it is not consistent (i.e. doesn't always happen).

The page generates this line at the end of the Form tag:

new Sys.WebForms.Menu({ element: 'Navigation Menu', disappearAfter: 500, orientation: 'horizontal', tabIndex: 0, disabled: false });

Throws JS runtime exception:

'Sys' is undefined

Solution:

void Application_Start(object sender, EventArgs e)
{
  RouteTable.Routes.Ignore("{resource}.axd");
}

Or:

  
<system.web>
  <pages controlRenderingCompatibilityVersion="3.5"/>
</system.web>

Tags:

ASP.Net | C# | JavaScript

WCF Web Service XML Sample

by 24. September 2012 11:29
This is some sample code demonstrating a WCF web service with XML based class as an input parameter.
// Shared class...

public class MySampleData
{
  public string UserId { get; set; }
  public Guid RecordId { get; set; }
  public string RecordDesc { get; set; }
  public decimal DataAmount { get; set; }
  public DateTime EffectiveDate { get; set; }
}

// Sample usage...

using System.IO;
using System.Net;
using System.Xml;
using System.Xml.Serialization;

private void SampleUsage()
{
  MySampleData data = new MySampleData()
  {
    UserId = "myuser",
    RecordId = Guid.NewGuid(),
    RecordDesc = "This is a test",
    DataAmount = 100.99,
    EffectiveDate = System.DateTime.Now.AddDays(5)
  };
  string msg = SaveMySampleData(data);
  System.Diagnostics.Debug.WriteLine(msg);
}

// Caller initiating web service transaction...
private void SaveMySampleData(MySampleData data)
{
  XmlSerializer serializer = new XmlSerializer(data.GetType());
  MemoryStream ms = new MemoryStream();
  serializer.Serialize(ms, data);
  StreamReader rdr = new StreamReader(ms);
  ms.Position = 0;
  HttpWebRequest req = (HttpWebRequest)HttpWebRequest
    .Create("http://localhost:43653/MySampleService.svc/SaveMySampleData");
  req.Method = "POST";
  req.ContentType = "application/xml; charset=utf-8";
  byte[] reqBodyBytes = ms.ToArray();
  Stream reqStream = req.GetRequestStream();
  reqStream.Write(reqBodyBytes, 0, reqBodyBytes.Length);
  reqStream.Close();
  HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
  XmlDocument xml = new XmlDocument();
  xml.LoadXml(new StreamReader(resp.GetResponseStream()).ReadToEnd());
  resp.Close();
}

// Web service code (i.e. MySampleService.svc file)

using System;
using System.IO;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Xml;
using System.Xml.Serialization;

[ServiceContract]
public class MySampleService
{
  [OperationContract(Name = "SaveMySampleData")]
  [WebInvoke(Method = "POST", UriTemplate = "SaveMySampleData",
              RequestFormat = WebMessageFormat.Xml,
              ResponseFormat = WebMessageFormat.Xml,
              BodyStyle = WebMessageBodyStyle.Bare)]
  public string SaveMySampleData(XmlElement input)
  {
    XmlSerializer serializer = new XmlSerializer(typeof(MySampleData));
    MySampleData data = (MySampleData)serializer.Deserialize
      (new MemoryStream(System.Text.Encoding.ASCII.GetBytes(input.OuterXml)));
    // Save to db here... just echoing data back for the sample...
    StringBuilder msg = new StringBuilder();
    msg.Append("You sent the following data.").Append(Environment.NewLine);
    msg.Append("UserId: ").Append(data.UserId).Append(Environment.NewLine);
    msg.Append("RecordId: ").Append(data.RecordId).Append(Environment.NewLine);
    msg.Append("RecordDesc: ").Append(data.RecordDesc).Append(Environment.NewLine);
    msg.Append("DataAmount: ").Append(data.DataAmount).Append(Environment.NewLine);
    msg.Append("EffectiveDate: ").Append(data.EffectiveDate.ToString()).Append(Environment.NewLine);
    return msg.ToString();
  }
}

Tags:

C# | Web Service | Windows Communication Foundation

AppFog Setup Application with MySQL Database and Connect to Database with MySQL Workbench

by 15. September 2012 18:01

This entry will walk through creating an AppFog application, linking a MySQL database to it and then connecting to the database with MySQL Workbench.  I will walk through creating the AppFog application and MySQL service, installing Ruby and the DevKit (toolkit that makes it easy to build and use native C/C++ extensions for Ruby on Windows).  In this case the extension we will be installing that requires the DevKit is Caldecott (which is used to create the MySQL TCP tunnel from the AppFog server to your Localhost).  My installation will focus on Windows 7 64bit operating system.

Step 0) Install MySQL Workbench from: http://www.mysql.com/downloads/workbench/ (the version I installed was: mysql-workbench-gpl-5.2.42-win32.msi).

Step 1) Create your AppFog account.  If you don't already have an AppFog account, signing up is quick an painless.  At the time of this post all they require to establish a new free account is an email address, password and company name.  My free account came with: Unlimited Apps (Java, .Net, Ruby, Node, Python, PHP, etc.) within 2GB RAM, Database services (MySQL, Mongo, PostgreSQL, etc.) with 1GB limit, 50 GB data transfer limit and Free Custom Domains.

Step 2) Create your AppFog application.  Simple click to create and deploy, pick you application framework and associated database service.  It's so easy I won't get into those details here, other than to say my first application was Node.js with MySQL database.

Step 3) Install Ruby from http://rubyinstaller.org (the version I installed was: rubyinstaller-1.9.3-p194.exe).  This is a pretty basic Windows application install, so not much to say here other than the installation path I chose which was: C:\Programs\Ruby193.

Step 4) Install DevKit from http://rubyinstaller.org/downloads (the version I installed was: DevKit-tdm-32-4.5.2-20111229-1559-sfx.exe).  This was a self extracting EXE that simply extracted the contents to a directory.  I selected the extract to be inside my existing Ruby installtion folder: C:\Programs\Ruby193\DevKit.  After extraction was complete you need to run a couple commands to complete the installation:  1) ruby dk.rb init to generate the config.yml file. 2) ruby dk.rb install

Step 5) Launch Ruby and create the install the AppFog utility and the Caldecott utility.  Launch Ruby: Windows Button > All Programs > Ruby 1.9.3-p194 > Start Command Prompt with Ruby from the Ruby command window perform the following commands:

    gem update --system
    gem install af
    gem install caldecott

Step 6) Login to your AppFog account from the Ruby commandline: af login (this will prompt you for your email address and password).

Step 7) Create TCP tunnel between AppFog server and your localhost.  Caldecott is an extension that allows you to establish a tunnel by proxying TCP traffic on localhost via HTTPS.  With Caldecott, you can use the Ruby commandline (after you logged into your AppFog account) and create a tunnel that allows you to manage your MySQL database from your local desktop using MySQL Workbench.  From the Ruby commandline (after you are logged in using af login): af tunnel this will then prompt you with a list of options.  For example, in my case I had a MySQL and a MongoDB instance running on my AppFog account, so I got two options (as shown below), I selected option 2 which in my case was the MySQL database I was interested in.  Also, note the last prompt, I selected "None" which leaves the tunnel open for the MySQL Workbench UI to connect.  You will use the information shown in the username, password section to connect and the connection hostname/ip and port will be "localhost" on port "10000".

1: mongodb
2: mysql
Which service to tunnel to?: 2
Getting tunnel connection info: OK

Service connection info:
  username : xxxxxxxxxxxxx
  password : xxxxxxxxxxxxx
  name     : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
  infra    : hp

Starting tunnel to mysql on port 10000.
1: none
2: mysql
3: mysqldump

Which client would you like to start?: 1
Open another shell to run command-line clients or
use a UI tool to connect using the displayed information.
Press Ctrl-C to exit...

NOTE: If you get an error installing caldecott please update your path... type message it usually means that you did not install the DevKit properly.



Tags:

SQL DB Table Record History Template

by 12. September 2012 10:21

Here is a template script to create a standard set of objects to track changes to table data.

/*
  drop trigger SchemaName.TableName_IUX
  drop table SchemaName.TableName
  drop view SchemaName.vxTableName
  drop table SchemaName.TableNameHistory
  drop view SchemaName.vxTableNameHistory
*/

/*
  drop table SchemaName.TableName
*/
create table SchemaName.TableName
(
  /* single record per modification instance */
    Timestamp timestamp not null
  , TableNameId uniqueidentifier not null
  , LastUpdateDate datetime not null default getdate()
  , LastUpdateByUserId uniqueidentifier not null
  /* add columns here */
)
go
alter table SchemaName.TableName
add constraint
  PK_SchemaName_TableName primary key clustered
  (
    TableNameId
  ) with ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
on [PRIMARY]
go
/*
  drop view SchemaName.vxTableName
*/
create view SchemaName.vxTableName
as
select
    TableNameId
  , LastUpdateDate
  , LastUpdateByUserId
  /* add columns here */
from 
  SchemaName.TableName
go
/*
  drop table SchemaName.TableNameHistory
*/
create table SchemaName.TableNameHistory
(
  /* single record per modification instance */
    HistoryId uniqueidentifier not null default newid()
  , TableNameId uniqueidentifier not null
  , LastUpdateDate datetime not null default getdate()
  , LastUpdateByUserId uniqueidentifier not null
  /* add columns here */
)
go
alter table SchemaName.TableNameHistory
add constraint
  PK_SchemaName_TableNameHistory primary key clustered
  (
    HistoryID
  ) with ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
on [PRIMARY]
go
/*
  drop view SchemaName.vxTableNameHistory
*/
create view SchemaName.vxTableNameHistory
as
select
    TableNameId
  , LastUpdateDate
  , LastUpdateByUserId
  /* add columns here */
from 
  SchemaName.TableNameHistory
go
/*
  drop trigger SchemaName.TableName_IUX
*/
create trigger SchemaName.TableName_IUX on SchemaName.TableName for insert, update
as begin
  insert into SchemaName.vxTableNameHistory
    select t1.* 
    from SchemaName.vxTableName t1
    inner join inserted t2 on t1.TableNameID = t2.TableNameID
end
go

Tags:

SQL Server

WCF Service X.509 Certificate CryptographicException: Keyset does not exist or Access is denied

by 3. August 2012 17:14

When I tried running my WCF service I received a System.Security.Cryptography.CryptographicException: Keyset does not exist or Access is denied. In my case the issue was that the user my IIS application pool was running under did not have access to the private key. There are a couple ways to grant the user access and make the X.509 certificate accessible to WCF.

Option 1: Grant read access to the file that contains the private key to the account that WCF is running under, using a tool such as cacls.exe. See this link for full topic: http://msdn.microsoft.com/en-us/library/aa702621.aspx

The following code example edits (/E) the access control list (ACL) for the specified file to grant (/G) the NETWORK SERVICE account read (:R) access to the file.

cacls.exe "C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys\8aeda5eb81555f14f8f9960745b5a40d_38f7de48-5ee9-452d-8a5a-92789d7110b1" /E /G "NETWORK SERVICE":R

Option 2: Use the Microsoft Management Console (MMC)
1) Start > Run > MMC
2) File > Add/Remove Snap-in > Certificates, Add, Computer Account, Local
3) Open > Certificates > Personal > Certificates
4) Right Click Certificate > All Tasks > Manage Private Keys
5) Add Network Service (or whatever login the application pool is running under) with Read access

Option 3: Use Windows HTTP Services Certificate Configuration Tool (WinHttpCertCfg.exe) located here: http://www.microsoft.com/en-us/download/details.aspx?id=19801
"C:\Program Files (x86)\Windows Resource Kits\Tools\winhttpcertcfg" -g -c LOCAL_MACHINE\My -s MyCertificate -a DefaultAppPool

The installation folder of this program contains an HTML instruction file on the command line parameters.

Tags:

WCF Service X.509 Certificate CryptographicException: Keyset does not exist or Access is denied

by 3. August 2012 15:19

When I tried running my WCF service is received a System.Security.Cryptography.CryptographicException: Keyset does not exist or Access is denied.  In my case the issue was that the user my IIS application pool was running under did not have access to the private key.  There are a couple ways to grant the user access and make the X.509 certificate accessible to WCF.

Option 1: Grant read access to the file that contains the private key to the account that WCF is running under, using a tool such as cacls.exe.  See this link for full topic: http://msdn.microsoft.com/en-us/library/aa702621.aspx
The following code example edits (/E) the access control list (ACL) for the specified file to grant (/G) the NETWORK SERVICE account read (:R) access to the file.
cacls.exe "C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys\8aeda5eb81555f14f8f9960745b5a40d_38f7de48-5ee9-452d-8a5a-92789d7110b1" /E /G "NETWORK SERVICE":R

Option 2:  Use the Microsoft Management Console (MMC)
1) Start > Run > MMC
2) File > Add/Remove Snap-in > Certificates, Add, Computer Account, Local
3) Open > Certificates > Personal > Certificates
4) Right Click Certificate > All Tasks > Manage Private Keys
5) Add Network Service (or whatever login the application pool is running under) with Read access

Option 3:  Use Windows HTTP Services Certificate Configuration Tool (WinHttpCertCfg.exe) located here: http://www.microsoft.com/en-us/download/details.aspx?id=19801
Typical installation path: C:\Program Files (x86)\Windows Resource Kits\Tools\
This is another command line option and the installation folder contains an HTML instruction file on the command line parameters.

Tags:

Sharepoint Powershell Script to Remove Missing Feature References from Sharepoint Site Content Configuration

by 2. August 2012 11:55

Our Sharepoint 2010 Central Administration portal Health Analyzer shows a "Missing server side dependencies." entry.  When clicking into the details of that entry we see the error shown below:

[MissingFeature] Database [WSS_CONTENT_MySite] has reference(s) to a missing feature: Id = [00000000-0000-0000-0000-000000000000]. The feature with Id 00000000-0000-0000-0000-000000000000 is referenced in the database [WSS_CONTENT_MySite], but is not installed on the current farm

Below is code to find and or delete the reference to the missing feature.  Thanks to Phil Childs blog entry: http://get-spscripts.com/2011/06/removing-features-from-content-database.html for providing the powershell script.

Code (note remove the -ReportOnly parameter to actually delete the reference):

function Remove-SPFeatureFromContentDB($ContentDb, $FeatureId, [switch]$ReportOnly)
{
    $db = Get-SPDatabase | where { $_.Name -eq $ContentDb }
    [bool]$report = $false
    if ($ReportOnly) { $report = $true }
    
    $db.Sites | ForEach-Object {
        
        Remove-SPFeature -obj $_ -objName "site collection" -featId $FeatureId -report $report
                
        $_ | Get-SPWeb -Limit all | ForEach-Object {
            
            Remove-SPFeature -obj $_ -objName "site" -featId $FeatureId -report $report
        }
    }
}

function Remove-SPFeature($obj, $objName, $featId, [bool]$report)
{
    $feature = $obj.Features[$featId]
    
    if ($feature -ne $null) {
        if ($report) {
            write-host "Feature found in" $objName ":" $obj.Url -foregroundcolor Red
        }
        else
        {
            try {
                $obj.Features.Remove($feature.DefinitionId, $true)
                write-host "Feature successfully removed from" $objName ":" $obj.Url -foregroundcolor Red
            }
            catch {
                write-host "There has been an error trying to remove the feature:" $_
            }
        }
    }
    else {
        #write-host "Feature ID specified does not exist in" $objName ":" $obj.Url
    }
}

Remove-SPFeatureFromContentDB -ContentDB "WSS_CONTENT_MySite" -FeatureId "00000000-0000-0000-0000-000000000000" -ReportOnly

Tags:

Google Chart Image (Deprecated)

by 23. July 2012 15:14

Deprecated image based charting: https://developers.google.com/chart/image/

Here is a url based example of the deprecated Google chart image: 

https://chart.googleapis.com/chart?chf=bg,s,EFEFEF&chtt=Sample%20Chart&chco=9999CC&cht=pc&chd=t:60,30,10&chl=60|30|10&chs=300x150

For current charting methods visit the google coding playground here: https://code.google.com/apis/ajax/playground/

NOTE: The playgound didn't work on my IE8 browser, worked fine on Firefox.

Tags:

Open PDF in Sharepoint 2010

by 16. July 2012 17:02

We added an editable PDF document to our Sharepoint 2010 site; however, under the sites default settings it forced the user to save the document (i.e. it would not open the document directly from the site).  Below is a PowerShell script that will add the PDF MIME type to the list of allowable inline download types.  NOTE: Save the script with .PS1 extension to run in PowerShell.

$webapp = Get-SPWebApplication http://sharepointsitehere
If ($webapp.AllowedInlineDownloadedMimeTypes -notcontains "application/pdf")
{
  Write-Host -ForegroundColor White "Adding Pdf MIME Type..."
  $webapp.AllowedInlineDownloadedMimeTypes.Add("application/pdf")
  $webapp.Update()
  Write-Host -ForegroundColor White "Added and saved."
}
 Else
{
  Write-Host -ForegroundColor White "Pdf MIME type is already added."
}

Tags:

Sharepoint | PowerShell

HttpRuntime MaxRequestLength vs. Security RequestFiltering RequestLimits MaxAllowedContentLength

by 24. May 2012 15:11

Below is illustration of setting the maximum file upload size to 150MB in IIS7.  It requires two separate settings, one specifically for ASP.Net and the other specifically for IIS (as outlined below).

This section applies to the maximum allowed file size that ASP.Net will allow.  The value is specified in KBytes.

<system.web>
  <httpRuntime maxRequestLength="153600" executionTimeout="900" />
</system.web>

This section applies to the maximum length of content that IIS will allow.  The value is specified in Bytes.

<system.webServer>
   <security>
     <requestFiltering>
       <requestLimits maxAllowedContentLength="157286400" />
    </requestFiltering>
  </security>
</system.webServer>

Tags:

ASP.Net

Change Owner of SSRS Subscription on SQL Server 2008 R2

by 9. April 2012 10:26

Recently a DBA left the company and before his final day we disabled his login account to make sure that nothing broke (i.e. no jobs were running under his account, etc.).  The next couple days everything seemed fine.  Then after he left we actually deleted his account.  After deleting his account every SSRS report subscription that was originally created by him stopped working.  Some of them gave error messages that indicated some type of problem with the user account while others had very vague error messages.  The bottom line is that they ran fine when the AD account existed (regarless of the state of the account); but, the fail once the account is removed.  The initial fix was simply to create a new version of the subscription under a different user account; however, there were so many that this would be a very tedious and time consuming process. 

As an alternative the attached script can be used to change the subscription owner of all subscriptions belonging to a specific user. To execute the Script, please follow these steps:

1. Open Command line and Change Directory to this location “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn”
2. Execute the following command:

  • rs -i <Path to ChangeSubscriptionOwner.rss Script> -s <ReportServer URL> -e Mgmt2010 -v varOwner="<CurrentSubscriptionOwner>" -v varNewOwner="<NewSubscriptionOwner>"

Example:

  • rs -i D:\ChangeSubscriptionOwner.rss -s http://myserver/reportserver -e Mgmt2010 -v varOwner="mydomain\origuserid" -v varNewOwner="mydomain\newuserid"

This will change all the Subscriptions owned by "mydomain\originaluserid" to "mydomain\newuserid".

Note: Please Execute this script on the Reporting Services Server.

Note 2: This script and should be used only when Good ReportServer and ReportServerTempDB backups are available. As a good practice, you should also take a backup of the Reporting Services EncryptionKey.

Tags:

SQL Server | SSRS

C# LDAP Query - Get User Properties by User Name

by 5. April 2012 08:44

This code allows you to search for an Active Directory user by name and return a dynamic list of properties with optional line feed.  NOTE: There is a hardcoded userid and password that needs to be changed to a valid AD user login.

using System;
using System.DirectoryServices;
using System.Text;
using System.Text.RegularExpressions;

namespace LDAP_Query
{
  class Program
  {
    static void Main(string[] args)
    {
      Console.Write(GetLDAPUserByName("Joseph Pesch"
        , new string[] { "department", "title", "givenName", "displayName" }
        , ((char)10).ToString()));
    }

    private static string GetLDAPUserByName(string userName, string[] properties, string lineFeed)
    {
      StringBuilder sb = new StringBuilder();
      userName = Regex.Replace(userName, @"[\(\)\*\\]", (match) =>
      {
        // escape reserved chars 
        return "\\" + ((int)match.Value[0]).ToString("x");
      }, RegexOptions.Compiled);
      string query = "(&"
        + "(objectCategory=person)" + "(objectClass=user)"
        + "(|" + "(&" + "(sn={0}*)" + "(givenName={1}*)" + ")"
        + "(displayName={2})" + ")" + ")";
      query = String.Format(query, userName.Split(' ')[1]
        , userName.Split(' ')[0], userName);
      using (DirectoryEntry entry =
        new DirectoryEntry("LDAP://" + Environment.UserDomainName
        , "userid", "password", AuthenticationTypes.Secure))
      {
        using (DirectorySearcher ds =
          new DirectorySearcher(entry, query, null, SearchScope.Subtree))
        {
          SearchResultCollection res = ds.FindAll(); // all matches 
          if (res == null) return "CANNOT LOCATE USER";
          foreach (SearchResult r in res)
          {
            foreach (string prop in properties)
            {
              foreach (object property in r.Properties[prop])
              {
                sb.Append(prop).Append("=").Append(property.ToString()).Append(lineFeed);
              }
            }
          }
        }
      }
      return sb.ToString();
    }

  }

}

Tags:

C#

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