Open & Close a VPN Connection with C#

I've thrown together this simple class to check if a windows VPN connection is open and with it you can also connect and disconnect. It's not massively robust but does the trick. It's makes use of the rasdial command and is inspired by this post on SO.

Here's how to use it:

//Get a VPN connector ready
VPNConnector vpn = new VPNConnector("VPN_NAME", "USER_NAME", "USER_PASSWORD");

//Connect
Tuple<bool, IList<string>> response = vpn.Connect();

if (response.Item1)
{
    //Successfully connected
    Console.WriteLine("Connected!");
}
else
{
    //Failed to connect
    Console.WriteLine("Failed to connect :(");
    foreach (string s in response.Item2)
    {
        Console.WriteLine(s);
    }
}

//Disconnect
vpn.Disconnect();

You can optionally check if the connection is already open by using vpn.IsOpen.

The code:

/// <summary>
/// Gives access to Windows VPN connections
/// </summary>
public class VPNConnector
{

	#region Properties

	#region VPNName
	/// <summary>
	/// The name of the VPN connection
	/// </summary>
	public string VPNName { get; set; }
	#endregion

	#region Username
	/// <summary>
	/// The username to use for the connection
	/// </summary>
	public string Username { get; set; }
	#endregion

	#region Password
	/// <summary>
	/// The password to use for the connection
	/// </summary>
	public string Password { get; set; }
	#endregion

	#region IsOpen
	/// <summary>
	/// Whether the connection is currently open or not
	/// </summary>
	public bool IsOpen
	{
		get
		{
			if (String.IsNullOrWhiteSpace(this.VPNName))
				throw new ArgumentException("No VPN name (VPNName) has been set");
			
			Process p = this.getNewProcess;
			p.Start();

			bool bOpen = false;
			while (!p.StandardOutput.EndOfStream)
			{
				string sOutput = p.StandardOutput.ReadLine();
				if (sOutput.ToLower() != "no connections")
				{
					if (sOutput == this.VPNName)
					{
						bOpen = true;
						break;
					}
				}
				else
					break;
			}
			p.WaitForExit();

			return bOpen;
		}
	}
	#endregion

	#region parametersAreASet
	/// <summary>
	/// Determines if all the necessary parameters are set to make a VPN connection
	/// </summary>
	private bool parametersAreASet { get { return (!String.IsNullOrWhiteSpace(this.VPNName) && !String.IsNullOrWhiteSpace(this.Username) && !String.IsNullOrWhiteSpace(this.Password)); } }
	#endregion

	#region getNewProcess
	/// <summary>
	/// Get a new process ready to start a rasdial call
	/// </summary>
	private Process getNewProcess
	{
		get
		{
			return new Process
			{
				StartInfo = new ProcessStartInfo
				{
					FileName = "rasdial.exe",
					UseShellExecute = false,
					RedirectStandardOutput = true,
					CreateNoWindow = true
				}
			};
		}
	} 
	#endregion

	#endregion

	#region Cosntructor
	public VPNConnector() { }

	public VPNConnector(string name, string username, string password)
	{
		this.VPNName = name;
		this.Username = username;
		this.Password = password;

		if (!this.parametersAreASet)
			throw new ArgumentException("All arguments must have a value");
	}
	#endregion

	#region Public Methods

	#region Instance

	#region Connect
	/// <summary>
	/// Make a connection to the VPN. Item1 of Tuple indicates success or failure. Item2 of Tuple is the output of the dial call
	/// </summary>
	public Tuple<bool, IList<string>> Connect()
	{
		if (!this.IsOpen)
		{
			if (!this.parametersAreASet)
				throw new ArgumentException("All arguments must have a value");

			Process p = this.getNewProcess;
			p.StartInfo.Arguments = $"\"{this.VPNName}\" \"{this.Username}\" \"{ this.Password}\"";
			p.Start();

			bool bSuccess = false;
			IList<string> outputs = new List<string&gt();
			while (!p.StandardOutput.EndOfStream)
			{
				string sOutput = p.StandardOutput.ReadLine();
				outputs.Add(sOutput);

				if (sOutput.ToLower().StartsWith("success"))
					bSuccess = true;
			}
			p.WaitForExit();

			return new Tuple<bool, IList<string>>(bSuccess, outputs);
		}
		else
			return new Tuple<bool, IList<string>>(false, new List<string>() { "Connection already open" } );
	}
	#endregion

	#region Disconnect
	/// <summary>
	/// Disconnect from the VPN. Item1 of Tuple indicates success or failure. Item2 of Tuple is the output of the dial call
	/// </summary>
	public Tuple<bool, IList<string>> Disconnect()
	{
		if (this.IsOpen)
		{
			Process p = this.getNewProcess;
			p.StartInfo.Arguments = $"\"{this.VPNName}\" /d";
			p.Start();

			bool bSuccess = false;
			IList<string> outputs = new List<string>();
			while (!p.StandardOutput.EndOfStream)
			{
				string sOutput = p.StandardOutput.ReadLine();
				outputs.Add(sOutput);

				if (sOutput.ToLower().Contains("success"))
					bSuccess = true;
			}
			p.WaitForExit();

			return new Tuple<bool, IList<string>>(bSuccess, outputs);
		}
		else
			return new Tuple<bool, IList<string>>(false, new List<string&gt() { "Connection already closed" });
	}
	#endregion

	#endregion

	#endregion

}

signalR - Error during WebSocket handshake: Unexpected response code: 500

Having recently started playing with signalR, I got the following error:

WebSocket connection to 'ws://localhost/../signalr/connect?transport=webSockets&clientProtocol=1.4&connectionToken=DJquvX4%2B7kH0EzXZcCYSRPL66R3ytyc204DflljN1%2BgkHqi6Y5llDAAeUZTDKkDFFBQm6rdJooh8ck7LcejXVFZvhxE0iUJVD%2BZadN%2Ff5s13rc%2FMZD4WxsMSlToyvYid&connectionData=%5B%7B%22name%22%3A%22myapp%22%7D%5D&tid=4' failed: Error during WebSocket handshake: Unexpected response code: 500

This was odd as it was working fine on another development machine I was using. After serious amounts of hunting around and mucking about, I got it working by adding the following to the web.config:

<system.web>
    <httpRuntime targetFramework="4.5"/>
</system.web>

Interestingly, I found the answer in the Application Event Logs :)

It's also worth checking that the application target framework is also set to 4.5 in case there is a conflict there too.

Set Many Properties in One Go

If you're looking for a way to set the value of many properties who's name starts with the same string, then look no further!

Here's a useful extension you can add to an object.

public static class Extensions
{
    public static void SetPropertiesToValue(this object obj, string propertyNameStartsWith, Type propertyType, object val)
    {
        foreach (PropertyInfo prop in obj.GetType().GetProperties())
        {
            if (prop.Name.StartsWith(propertyNameStartsWith) && prop.PropertyType == propertyType && prop.CanWrite)
                prop.SetValue(obj, val);
        }
    }
}

And here's how to use it:

myObj.SetPropertiesToValue("Name", typeof(string), "Jim");

This will set any property of myObj that has a name starting with Name to Jim.

SQL Server Remove Command from Replication

I managed to create a script on a replicated database that when run on the live server errored - oops! I've had this before and it stops any other transactions from being replicated so I needed to get rid of the failing command and deal with the script another way.

If you go into the distribution database there is a table called MSrepl_commands. This has all the commands that have been and need to be executed on subscribers. You can literally delete whichever commands you don't want!

If you go into the Replication Monitor and navigate your way through to where the error is, it kindly tells us what the error is and also which command it is. The transaction sequence number relates to
MSrepl_commands.xact_seqno and the command id to MSrepl_commands.command_id.

With this, you can create a DELETE statement to get rid of the command and within a minute it had sorted itself out. Obviously I had to rectify the problem with the original script!

This bit of SQL will do it...

DELETE
FROM MSrepl_commands
WHERE xact_seqno = 0x0002434800000DC8000100000000
AND command_id = 3

Be extra cautious here as deleting the wrong thing could have a catastrophic impact!!!

Filter an Enum By String Value in C#

I have an enum declared and wanted to get all items in the enum that contained certain text. Well, this can be done using Linq and here's an example:

public enum Animals
{
    Dog,
    Cat,
    Mouse,
    Sheep
}

var filteredAnimals = Enum.GetValues(typeof(Animals))
    .Cast<Animals>()
    .Where(t => !t.ToString().Contains("e"))
    .ToList()

This gives a list of Mouse and Sheep.

Further to that, if wanted to get the maximum integer value of your filtered list, then you could use this:

public enum Animals
{
    Dog,
    Cat,
    Mouse,
    Sheep
}

var filteredAnimals = Enum.GetValues(typeof(Animals))
    .Cast<Animals>()
    .Where(t => !t.ToString().Contains("a"))
    .Cast<int>()
    .Max()

This gives us 1 as Cat is the highest enum that contains an "a"

Setting a Default Value on a C# Property

Came across this little beauty for setting a default value on a C# 5.0 and below property so you can condense this:

private int myInt = 3;
public int MyInt
{
    get { return this.myInt; }
    set { this.myInt = value; }
}

Into this:

[System.ComponentModel.DefaultValue(3)]
public int MyInt { get; set; }

Obviously there's an array of variable types that can be used.

C# 6.0 is due to have a further tweak to allow us to do this:

public int MyInt { get; set; } = 3;

Sweeet :)

Overload Resolution Failed Because No Accessible Is Most Specific For These Arguments

I have a method which has two overloads where I can pass one of two object types. In some cases, these objects may be Nothing or null. When this is the case, you will almost certainly get the Overload resolution failed because no accessible is most specific for these arguments compile error.

It's quite simple to sort as you you literally just cast the Nothing or null to the type you want it to be processed as. For example:

In C#

public void MyMethod(object1 o)
{
    //Some code...
}

public void MyMethod(object2 o)
{
    //Some code...
}

...

MyMethod((object1)null);

In VB.Net

Public Sub MyMethod(o As object1)
    'Some code...
End Sub

Public Sub MyMethod(o As object2)
    'Some code...
End Sub

...

MyMethod(Cast(Nothing, object1))

Get Google Calendar Event ID from the Page

If you need to find out the event ID from a Google calendar event, then luckily Google puts the ID for us on the page! I found this posting on stackoverflow which enlightened me to this fact.

The ID is stored in base64 so needs converting. Follow these steps to get it:

1. Go to/edit the event in your Google calendar
2. Use the browser debugger (I'm in Chrome) or view the source (I'm using the debugger)
3. Click the elements tab and press Ctrl+F to search the markup
4. Search for data-eid
5. Copy the value for data-eid

And there ya go! If you don't have means to decode the base64 value use this site to do it.

When it is decoded you will see a sequence of alphanumerics a space and the email address associated to the account. Obviously it is the first sequence you need!

Count the Rows of a Filtered Tablix

Been having some major ball aches with SSRS (SQL Server Reporting Services) recently -specifically around trying to hide elements and filter data.

In this instance, I needed to hide a row in a group if there were no rows in the filtered tablix. This is the process I followed:

1. Added a column to the tablix and set the column visibility to hidden

2. Added a text box to the header row and set the expression for it to: =CountRows()

3. Set the row visibility expression to this:

=(Int32.Parse(ReportItems!txtUsageDataRowCount.Value) <= 0)

An extra to note here is the use of ReportItems. It's a neat way of referring to a value in a text box. It does only work for text boxes

Error Applying Security - Failed to Enumerate Objects

I was recently trying to apply some security settings to a folder in Windows. When I clicked OK or Apply I had a Error Applying Security - Failed to enumerate objects in the container. Access is denied error for every single file in the folder. Very annoying!

I did some hunting around and came across this article. In this article, the author was unable to delete a folder, but the same process fixes the security/permissions error I was having. The commands are:

1. Open the command window running as administrator
2. Run the following commands on the folder in question:

takeown /f "C:\My Folder Path Here" /r /d y

icacls "C:\My Folder Path Here" /grant administrators:F /T

Sorted :) The permissions were then successfully applied.

C# & VB.NET Trim String To a Specific Length

I needed to trim some strings that if over a certain length would trim nicely and add "..." to the end. I particularly like to use extension methods in .NET where possible so I created this little TrimTo extension. There are some optionals that can be sent through including the characters whack on the end and whether to swap out the HTML br tag (I needed this - you may not ;) )

C#

public static class Extensions
{
 /// <summary>
 /// <summary>
 /// Trim a string to a specific length
 /// </summary>
 /// <param name="size">The finishing size of the string</param>
 public static string TrimTo(this string s, int size)
 {
  return TrimTo(s, size, "...");
 }

 /// <summary>
 /// Trim a string to a specific length
 /// </summary>
 /// <param name="size">The finishing size of the string</param>
 /// <param name="chars">The characters to put at the end of the string. Defaults to "..."</param>
 public static string TrimTo(this string s, int size, string chars)
 {
  return TrimTo(s, size, chars, false);
 }

 /// <summary>
 /// Trim a string to a specific length
 /// </summary>
 /// <param name="size">The finishing size of the string</param>
 /// <param name="chars">The characters to put at the end of the string. Defaults to "..."</param>
 /// <param name="doBRs">Where to replace new lines with &lt;br /&gt;. Defaults to false</param>
 public static string TrimTo(this string s, int size, string chars, bool doBRs)
 {
  if (s.Length > size)
   s = String.Format("{0}{1}", s.Substring(0, size), chars);
  if (doBRs)
   s = s.Replace("\n", "<br />");
  return s;
 }
}

VB.NET

Public Shared Class Extensions
 ''' <summary>
 ''' <summary>
 ''' Trim a string to a specific length
 ''' </summary>
 ''' <param name="size">The finishing size of the string</param>
 Public Shared Function TrimTo(this string s, int size) As String
  Return TrimTo(s, size, "...")
 End Function

 ''' <summary>
 ''' Trim a string to a specific length
 ''' </summary>
 ''' <param name="size">The finishing size of the string</param>
 ''' <param name="chars">The characters to put at the end of the string. Defaults to "..."</param>
 Public Shared Function TrimTo(this string s, int size, string chars) As String
  Return TrimTo(s, size, chars, false)
 End Function

 ''' <summary>
 ''' Trim a string to a specific length
 ''' </summary>
 ''' <param name="size">The finishing size of the string</param>
 ''' <param name="chars">The characters to put at the end of the string. Defaults to "..."</param>
 ''' <param name="doBRs">Where to replace new lines with &lt;br /&gt;. Defaults to false</param>
 Public Shared Function TrimTo(this string s, int size, string chars, bool doBRs) As String
  If s.Length > size Then s = String.Format("{0}{1}", s.Substring(0, size), chars)
  If doBRs Then s = s.Replace("\n", "<br />")
  Return s
 End Function
End Class

C# Hex Colour to RGB

I have to do a lot of interop work with Word & Excel and generally struggle matching up colour pallets across the two applications and the only way I've found to consistently do this is by using RGB colours and the RGB function provided in VB(A).

Having recently started a new C# project that I am using hex colour codes with (e.g. #000000), I thought I would write a simple RGB colour handling class that converts the hex colour code into RGB codes for me. And here it is...

/// A helper class used to hold the RGB values of a hex colour e.g. #000000
/// </summary>
public class RGBColour
{
 private string _HexRef = String.Empty;
 public string HexRef
 {
  get { return this._HexRef; }
  set
  {
   if (!value.StartsWith("#"))
    throw new Exception("The hex colour reference must start with a #. e.g. #000000");
   if (value.Length < 7)
    throw new Exception("The hex colour reference must be 7 characters. e.g. #000000");
   this._HexRef = value;
   Color c = ColorTranslator.FromHtml(value);
   this._R = Convert.ToInt32(c.R);
   this._G = Convert.ToInt32(c.G);
   this._B = Convert.ToInt32(c.B);
  }
 }

 private int _R = 0;
 public int R { get { return this._R; } }

 private int _G = 0;
 public int G { get { return this._G; } }

 private int _B = 0;
 public int B { get { return this._B; } }

 public RGBColour()
 {
 }
 public RGBColour(string hexRef)
 {
  this.HexRef = hexRef;
 }
} 

It quite simply uses the System.Drawing.ColorTranslator.FromHtml method to convert the hex value into a System.Drawing.Color object. The Color RGB properties are then converted to an int32.

This can then be used like this...

RGBColour rgb = new RGBColour("#000000");
xl.ActiveCell.Interior.Color = xl.RGB(rgb.R, rgb.G, rgb.B);

Return Dynamic Generic IList in C#

I had a situation where I had a SQL statement stored against a row in a database. Depending on the row would depend on what data was output. For example, row 1 would return all the industries and row 2 would  return all the age groups and so on. Each of my tables has it's own C# class/object associated to it. So I have an Industry class and an AgeGroup class and so on.

The data that has the SQL logged against it was a table and object called PersonalDetail and the SQL was stored in a column/property called LookUpStatement. I wanted to get an Ilist<> of the data results that LookUpStatement returns when executed. The key thing I wanted to achieve was that the new method would return the IList<> strongly typed. So in the case of the industry data I would have IList<Industry> returned by it or for AgeGroup an IList<AgeGroup>.

The whole passing and returning of dynamic types has been something I have wanted to master for some time. After some extensive searching and no single article telling me exactly what I needed I managed to pile it all together and ended with this...

Calling and using it:

//Get the personal detail row that is required
// The Load method loads a specifc row from the database
// In this instance, row 1 LookUpStatement will select all industires (e.g. SELECT * FROM [Industry]) 
PersonalDetail personalDetail = PersonalDetail.Load(1);

//Get the industries as an IList from the personal detail object
IList<Industry> industries = personalDetail.LookUpData<List<Indstry>>();

Note that the LookUpData call is made using a List<> and not an IList<>.The code itself:

public class PersonalDetail
{

 public LookUpStatement
 {
  get; set;
 }
 
 //Other properties, constructor, etc...
 
 public T LookUpData<T>()
 {
  if (!typeof(T).Name.StartsWith("List"))
   throw new ArgumentException("The generic pass type must be a List<>"); 
  
  //Create a typed instance of the required List
  T os = (T)Activator.CreateInstance(typeof(T));
  
  //Get the base object propertype. e.g. Industry
        Type propType = typeof(T).GetProperty("Item").PropertyType;
  
  //Get the data as a DataSet
  DataSet ds = _DB.GetDataSet(this.LookUpStatement);
  
  //Iterate the data
  for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
  {
   //Get the data row
   DataRow dr = ds.Tables[0].Rows[i];

   //Use relfection to execute the Add method of the dynmaic List
   //Then use relfection to execute the LogItem method of the base object
   // Logitem converts the data row into the base object. e.g. Industry
   os.GetType().GetMethod("Add").Invoke(
    os,
    new object[] {
     propType.GetMethod("LogItem").Invoke(null, new object[] { dr })
    }
   );
  }
 }

}

The comments in the code should hopefully give an indication of how it works. Working out the T os = (T)Activator.CreateInstance(typeof(T)); took a while as defining the os object as IList<object> just wasn't working. Got there in the end though :)

How To Use __doPostBack with an ASP.NET Button

Courtesy of Nikhil Vartak, I found I new way to able to perform a postback using an ASP.NET Button control. By default the button control does not fire the __doPostBack javascript event. This method is all good, but does make having a custom client click event a little more complicated.

Another way I have of doing it is by adding a control to the page that does use the __doPostBack function (assuming there isn't one on the page already!).

Add a LinkButton to the page with a width and height of 0px. This will make sure the __doPostBack function is on the page. <asp:LinkButton ID="lb" runat="server" Width="0px" Height="0px" />

Your button can then have an OnClientClick function added to it. This function can have custom javascript in it and fire the __doPostBack function. The example is using jQuery:

__doPostBack($(this).attr('name').replace(/_/g, '$'), '');

Note that the _ is being replaced with a $ (the equivalent of using MyButton.UniqueID).

SSRS: Black Box When Exporting to PDF with Hidden Column

Had a funny with SQL Server Reporting Services where I had a column in a table being hidden based on an expression. That was all fine, the issue was when the column was hidden and the report was exported to PDF, there was a solid black box being shown - great!

The table had a border and all inner borders were also set. I removed all the table borders and instead applied the borders to the textboxes in each cell.

Problem solved!

This has apparently been addressed in RS2005.

How To Change the Value in the app.config File

If you need to be able to change values in your application's app.config file, then it is surprisingly easy :)

In VB

Dim config As Configuration = ConfigurationManager.OpenExeConfiguration( _
    ConfigurationUserLevel.None)
config.ConnectionStrings.ConnectionStrings("MyDB") _
    .ConnectionString = "A_DB_CONNECTION_STRING"
config.AppSettings.Settings("MySetting").Value = "A_SETTING_VALUE"
config.Save(ConfigurationSaveMode.Modified)
ConfigurationManager.RefreshSection("connectionStrings")
ConfigurationManager.RefreshSection("appStrings")

In C#

Configuration config = ConfigurationManager.OpenExeConfiguration(
     ConfigurationUserLevel.None);
config.ConnectionStrings.ConnectionStrings["MyDB"]
    .ConnectionString = "A_DB_CONNECTION_STRING";
config.AppSettings.Settings["MySetting"].Value = "A_SETTING_VALUE";
config.Save(ConfigurationSaveMode.Modified);
ConfigurationManager.RefreshSection("connectionStrings");
ConfigurationManager.RefreshSection("appStrings");

In these examples, I am changing a connection string value and an app setting value. Once the config has been saved the modified section(s) need to be refreshed to ensure the new mods are loaded back into memory.

Remember that if your app is re-installed, then the modified app.config will be overwritten.

Method VBProject of _workbook Failed

I ran recently ran an old bit of Excel VBA in Excel 2010 which automatically adds the Microsoft Scripting Runtime to the workbook for me. Rather annoyingly, I was getting a Method VBProject of _workbook Failed error.

I solved this by going into the Options > Trust Center > Trust Centre Settings... Choosing Macro Settings and then checking the Trust access to the VBA project object model checkbox.

The code to add the reference in case that helps too ;)

Public Sub SomeMethod()

    AddReference "C:\WINDOWS\system32\scrrun.dll"

End Sub

Private Sub AddReference(sFile As String)
Dim i As Integer
Dim bFound As Boolean

    bFound = False
    For i = 1 To ActiveWorkbook.VBProject.References.Count
        If ActiveWorkbook.VBProject.References.Item(i).FullPath = sFile Then
            bFound = True
            Exit For
        End If
    Next i
    If Not bFound Then
        ActiveWorkbook.VBProject.References.AddFromFile sFile
    End If
End Sub

OpenVPN Client: Import Error Cannot Read ca Directive

I was following instructions to import an OpenVPN connection and got the following error:

IMPORT_ERROR: Profile or its references could not be read: cannot process OpenVPN file: cannot read file for OpenVPN 'ca' directive: [Errno 2] No such file or directory:

Seemed odd! As when I re-read the error message the path it was using was C:\Program Files\OpenVPN Technologies\OpenVPN Client\config and not the path it was installed under (C:\Program Files (x86)\OpenVPN Technologies\OpenVPN Client\config).

I fixed this by creating he config folder in the Program Files folder and not the Program Files (x86) folder. The connection imported fine.

SSRS: Table KeepTogether Not Working

I was recently having a problem with SQL Server Reporting Services 2005 edition trying to stop a table control from breaking over a page. The obvious solution was to set the KeepTogether property to True. Wrong! Well, not exactly. It does keep the table together and not split it over a page, but only the detail! Headers and footers are not included.

I did some extensive trawling and couldn't find an easy solution - but there is one...

Drop the table into a list control and set the KeepTogether property to True on the list.

This works beautifully for me and is really easy. The only thing you need to do is make sure that the list is the same size the table (and that's optional). Thus making sure it doesn't exceed the page width and so on.

How To Schedule Sending an Email with GMail

If you need to send scheduled emails from GMail  or Goolge Apps, use Boomerang. It's mega easy and you are able to send 10 a month on their free subscription. There's a mobile version of it you can access here. I haven't used the mobile version yet, but almost certainly will be.