LINQ query to CSV

The C# code below generates a CSV (comma separated value) string with the header row and data obtained from a LINQ query.

For example: A query like this:

dc.Users.Select(u => new
{
    user_id = u.UserId,
    username= u.Username,
    first_name = u.FirstName,
    last_name = u.LastName,
    creation_date = u.CreationDate
});

Would result in a CSV string like this one:

user_id,username,first_name,last_name,creation_date
"abf577a5-dc76-4719-9375-001a445d0c3d","jdoe","John","Doe",2011-03-29T14:50:28
"24b888cf-b02b-413e-8542-007e7ee572a6","jdoe2","John","Doe2",2009-05-29T19:52:32

And now to the code:

var users = dc.Users.Select(u => new
{
    user_id = u.UserId,
    username = u.Username,
    first_name = u.FirstName,
    last_name = u.LastName,
    creation_date = u.CreationDate
}).ToArray();

var usersCsv = GenerateCsv(users);

And make sure the following methods are delcared:

/// <summary>
/// This method generates CSV data out an array of anything. The array
/// elements could be of anonymous type
/// </summary>
private string GenerateCsv(Array items)
{

    var rowType = items.GetType().GetElementType();
    var colNames = rowType.GetProperties().Select(p => p.Name).ToArray();

    // create the header row
    var retVal = new StringBuilder();
    retVal.AppendLine(string.Join(",", colNames));

    // now create the body
    foreach (var row in items)
    {
        var rowItems = new string[colNames.Length];
        for (int i = 0; i < colNames.Length; i++)
            rowItems[i] = this.FormatCell(rowType.GetProperty(colNames[i]).GetValue(row, null));

        retVal.AppendLine(string.Join(",", rowItems));
    }

    return retVal.ToString();
}
private string FormatCell(object item)
{
    if (item == null)
        return "";

    if (item.GetType() == typeof(DateTime))
        return ((DateTime)item).ToUniversalTime().ToString("s");

    return "\"" + item.ToString().Replace("\"", "\"\"") + "\"";
}

Advertisements

Customizing the Contact form in Outlook 2010

Most of my Outlook contacts are personal contacts, not business contacts. So I don’t have their business phone or address. By default the business phone field is the first on the list of phones on the contact form. And the business address is the only one that shows by default.

With a lot more work that what it should be, I was able to change the fields from this:

image

To this:

image

I was only able to do this on New Contacts, I did not continue trying to figure out how to edit my existing contacts to use this new field format.

 

To do so go to Outlook 2010 and add the Developer Menu Item by going to “File > Options > Customize Ribbon“ and select the Developer item on the tree view:

image

Hit the OK button to save your changes.

Go to your contacts and hit the “New Contact” button:

image

 

Change the fields to your liking by hitting on the drop down arrow next to the field name:

image

Go to the Developer item on the Ribbon and select Publish Form As:

image

Give your new form a Display Name and a Form name and hit Publish.

image

The form name is the one you’ll look for when setting the form as the default form, the Display name is the one you’ll see on the “New Contact” button.

Close the “New Contact” window you have open, if it asks you if you want to save changes say no.

image

We now want to make our new published form the default Contact form. To do this , right click on the Contacts folder:

image

Hit properties. Then update the “when posting to this folder, use” field to your new form and hit OK to save your changes:

 

image

Your new contact form will now be used on all new Contacts.

Sadly I could not find a straight forward way to make existing contacts use the new Form. If you figure out how to do this, let me know!

C# controlled lights via USB port

At work, we thought it would be cool if we could turn on siren lights when a server goes down.

Step #1 to make this happen was to be able to control simple electronics from custom code. I love C# and the USB port is what is common now so I researched and found the FT245RL “USB to FIFO” chip. This chip made it really easy for me. It plays well with drivers known by Windows.

I bought the FT245RL from SparkFun on a breakout circuit for ease of soldering. For details about chip, take a look at this DataSheet.

When I plugged in the chip to the USB port, Windows 7 immediately recognized it and installed the drivers for it. You can use a couple of drivers the default is the simpler one to use where it turns the USB port into a virtual serial (COM) port.

Writing to serial ports is really standard so this is the code that outputs a byte through the chip:

private static void WriteByte(byte byteToWrite)
{
    using (SerialPort vcp = new SerialPort())
    {
        vcp.BaudRate = 9600;
        vcp.DataBits = 8;
        vcp.StopBits = StopBits.One;
        vcp.Parity = Parity.None;
        vcp.PortName = &quot;COM5&quot;;

        vcp.Open();

        vcp.Write(new byte[] { byteToWrite }, 0, 1);

        vcp.Close();

    }

}

Remote Desktop not remembering credentials

I’m not entirely sure why this was happening but when I used Remote Desktop (RDP) to connect to a remote machine running Windows Server 2008 and Terminal Services, my credentials were not being saved when I told RDP to save them.

They were always saved when I logged into servers running Windows Server 2003. In case you’re wondering my local machine runs Windows 7.

To fix this I did the following:

On the run window or on the start menu’s text box, type “gpedit.msc” (without the double quotes) and then hit enter.

You want to enable AND edit the server list for the following two parameters under “Local Computer Policy > Computer Configuration > Administrative Templates > System > Credentials Delegation”

  • Allow Delegating Default Credentials with NTLM-only Server Authentication
  • Allow Delegating Saved Credentials with NTLM-only Server Authentication

image

You must enable each setting and add “TERMSRV/*” to the server list:

image

Your credentials should now be saved when you use RDP.

Creating an Exchange 2010 Mailbox from a remote C# program

On this post, I’ll show you how to create an Exchange Mailbox from a C# program that is not running on the Exchange Server (a client program). What C# is really doing is remotely executing Exchange PowerShell cmdlets.

If this is the first time you’re attempting to run Exchange cmdlets from C#, you probably want to follow this blog post to make sure things are set up properly on the server and that C# can run Exchange cmdlets when running from the server.

This is the client’s code. But it doesn’t “just work”, you need to complete the setup instructions on this post.

using System;
using System.Security;
using System.Management.Automation;
using System.Management.Automation.Runspaces;

namespace PowerShellTest
{
    class Program
    {
        static void Main(string[] args)
        {

            // Prepare the credentials that will be used when connecting
            // to the server. More info on the user to use on the notes
            // below this code snippet.
            string runasUsername = @"username";
            string runasPassword = "password";
            SecureString ssRunasPassword = new SecureString();
            foreach (char x in runasPassword)
                ssRunasPassword.AppendChar(x);
            PSCredential credentials =
                new PSCredential(runasUsername, ssRunasPassword);

            // Prepare the connection
            var connInfo = new WSManConnectionInfo(
                new Uri("http://ServersIpAddress/PowerShell"),
                "http://schemas.microsoft.com/powershell/Microsoft.Exchange",
                credentials);
            connInfo.AuthenticationMechanism =
                AuthenticationMechanism.Basic;

            // Create the runspace where the command will be executed
            var runspace = RunspaceFactory.CreateRunspace(connInfo);

            // generate the command parameters
            var testNumber = 18;
            var firstName = "Test";
            var lastName = "User" + testNumber;
            var username = "tuser" + testNumber;
            var domainName = "pedro.test.local";
            var password = "ActiveDirectoryPassword1234";
            var ssPassword = new SecureString();
            foreach (char c in password)
                ssPassword.AppendChar(c);

            // create the PowerShell command
            var command = new Command("New-Mailbox");
            command.Parameters.Add("Name", firstName + " " + lastName);
            command.Parameters.Add("Alias", username);
            command.Parameters.Add(
                "UserPrincipalName", username + "@" + domainName);
            command.Parameters.Add("SamAccountName", username);
            command.Parameters.Add("FirstName", firstName);
            command.Parameters.Add("LastName", lastName);
            command.Parameters.Add("Password", ssPassword);
            command.Parameters.Add("ResetPasswordOnNextLogon", false);
            command.Parameters.Add(
                "OrganizationalUnit", "NeumontStudents");

            // Add the command to the runspace's pipeline
            runspace.Open();
            var pipeline = runspace.CreatePipeline();
            pipeline.Commands.Add(command);

            // Execute the command
            var results = pipeline.Invoke();

            runspace.Dispose();

            if (results.Count > 0)
                Console.WriteLine("SUCCESS");
            else
                Console.WriteLine("FAIL");

        }
    }
}

Some important things to notice on the code:

  • The “runas” user must:
    1. Belong to a Role Group that has Mail Recipient Creation rights. To do this, make the runas user belong to the “Recipient Management” Role Goup by going to “Exchange Management Console > Microsoft Exchange > Microsoft Exchange On-Premises > Toolbox > Role Based Access Control (RBAC) User Editor”.
    2. Must have Remote PowerShell rights. Do this by going to the Exchange Management Shell and running the following cmdlet: Set-User UserNameHere -RemotePowerShellEnabled:$true
  • It works using Basic authentication, which means that the “runas” credentials are being sent in clear text over the network. This is ok if you trust the network (which is my case because it never leaves the server room) or if you set up SSL.
  • Because of the parameters sent to the New-Mailbox cmdlet, besides creating an Exchange mailbox, I’m also creating an Active Directory user.

You need to configure WinRM on the client to:

  1. Allow unencrypted traffic
  2. Trust the remote machine

The easiest way to to this is via the Local Group Policy user interface. To access it, hit the windows start button, type run, run the “run” program, this opens the famous “run” window (which is wired to the Windows Key + R shortcut), on the run window enter gpedit.msc . You’re now looking at the Local Group Policy user interface. In the tree view go to “Local Computer Policy > Computer Configuration > Administrative Templates > Windows Components > Windows Remote Management (WinRM) > WinRM Client” and configure the previously listed items.

If you don’t configure the WinRM client, you’ll get these exceptions:

System.Management.Automation.Remoting.PSRemotingTransportException was unhandled
  Message=Connecting to remote server failed with the following error message : The WinRM client cannot process the request. Unencrypted traffic is currently disabled in the client configuration. Change the client configuration and try the request again. For more information, see the about_Remote_Troubleshooting Help topic.
  Source=System.Management.Automation
  WasThrownFromThrowStatement=false
  ErrorCode=-2144108322
  TransportMessage=The WinRM client cannot process the request. Unencrypted traffic is currently disabled in the client configuration. Change the client configuration and try the request again.
System.Management.Automation.Remoting.PSRemotingTransportException was unhandled
  Message=Connecting to remote server failed with the following error message : The WinRM client cannot process the request. If the authentication scheme is different from Kerberos, or if the client computer is not joined to a domain, then HTTPS transport must be used or the destination machine must be added to the TrustedHosts configuration setting. Use winrm.cmd to configure TrustedHosts. Note that computers in the TrustedHosts list might not be authenticated. You can get more information about that by running the following command: winrm help config. For more information, see the about_Remote_Troubleshooting Help topic.
  Source=System.Management.Automation
  WasThrownFromThrowStatement=false
  ErrorCode=-2144108316
  TransportMessage=The WinRM client cannot process the request. If the authentication scheme is different from Kerberos, or if the client computer is not joined to a domain, then HTTPS transport must be used or the destination machine must be added to the TrustedHosts configuration setting. Use winrm.cmd to configure TrustedHosts. Note that computers in the TrustedHosts list might not be authenticated. You can get more information about that by running the following command: winrm help config.

On the server, you must configure the PowerShell IIS virtual directory to:

  1. Not Require SSL
  2. Allow Basic Authentication

To not require SSL, you simply go to “IIS Manager > Sites > Default Website > Powershell”, then select the “SSL Settings” feature and make sure “Require SSL” is not checked.

To allow Basic Authentication you go again to “IIS Manager > Sites > Default Website > Powershell”, this time select the “Authentication” feature and enable “Basic Authentication”.

If Basic Authentication is not an option on the Authentication feature page, you need to install it by going to the Server Manager, select the Web Server role, say “Add Role Services”, under the Security node in the treeview, select Basic Authentication.

You’re all set! Running the C# code on a remote machine should work.

Running Exchange 2010 Management Shell Commands (PowerShell) with C#

The goal is to be able to run C# code on the Exchange server and show that we’ve have all the Microsoft Exchange 2010 PowerShell commands (cmdlets) available.

If what you want to do is execute the C# code on a client machine that executes cmdlets on the Exchange server, check out this post.

This is the code that we want to run on the server:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security;
using System.Management.Automation;
using System.Management.Automation.Runspaces;

namespace PowerShellTest
{
    class Program
    {
        static void Main(string[] args)
        {

            var rsConfig = RunspaceConfiguration.Create();
            PSSnapInException snapInException;

            // NOTE 1: The project's platform target must match the server's hardware architecture (x64 in my case)
            var snapinInfo = rsConfig.AddPSSnapIn("Microsoft.Exchange.Management.PowerShell.E2010", out snapInException);

            var runspace = RunspaceFactory.CreateRunspace(rsConfig);

            runspace.Open();
            var pipeline = runspace.CreatePipeline();
            var command = new Command("get-command");
            pipeline.Commands.Add(command);

            // NOTE 2: Your code cannot be running the .NET Framework 4 .  3.5 or lower is ok.
            var results = pipeline.Invoke();

            foreach (var cmd in results)
            {
                string cmdletName = cmd.Properties["Name"].Value.ToString();
                Console.WriteLine(cmdletName);
            }

            runspace.Dispose();

        }
    }
}

Output:

%
?
A:
ac
Add-ADPermission
Add-AvailabilityAddressSpace
Add-Computer
Add-Content
Add-ContentFilterPhrase
Add-DatabaseAvailabilityGroupServer
Add-DistributionGroupMember
Add-FederatedDomain
Add-History
...
Enable-Mailbox
New-Mailbox
...

Now to the details on how to make the code work:

You first must install the Windows PowerShell 2.0 SDK, mostly to be able to reference the System.Management.Automation.dll . For testing purposes, I installed Visual Studio on the server running Exchange (which is a test server). My ultimate goal (and maybe future post) is to run the C# code on a remote machine.

If you already have PowerShell 2.0 on your machine (which Windows 7 and Windows Sever 2008 R2 already do) I ran into some posts that said you could manually edit your .csproj file and add a reference to System.Management.Automation . This is how I initially did my tests but then hell broke loose (not sure if this was the cause of the problems) so I followed the formal rules and installed the SDK.

Then create the Console Application in Visual Studio and add a reference to the System.Management.Automation.dll . If you installed the SDK with the default options, you will find the dll here:

image

Copy paste the C# code above into your Program.cs file. If you build and run the code, you will most likely run into two strange errors; they will both happen right below my comment lines that start with NOTE. I use Visual Studio .NET 2010 which by default uses the .NET Framework 4 and for some reason the default project’s platform target is 32 bit (x86). Both of these defaults turned out to be a problem.

NOTE 1: 

I was getting the a “No snap-ins have been registered for Windows PowerShell version 2” error on that line:

System.Management.Automation.PSArgumentException was unhandled
  Message=No snap-ins have been registered for Windows PowerShell version 2.
  Source=System.Management.Automation
  ParamName=psVersion
  StackTrace:
       at System.Management.Automation.PSSnapInReader.GetMshSnapinRootKey(RegistryKey versionRootKey, String psVersion)
       at System.Management.Automation.PSSnapInReader.Read(String psVersion, String mshsnapinId)
       at System.Management.Automation.Runspaces.MshConsoleInfo.AddPSSnapIn(String mshSnapInID)
       at System.Management.Automation.Runspaces.RunspaceConfigForSingleShell.DoAddPSSnapIn(String name, PSSnapInException& warning)
       at System.Management.Automation.Runspaces.RunspaceConfiguration.AddPSSnapIn(String name, PSSnapInException& warning)
       at PowerShellTest.Program.Main(String[] args) in C:\Users\Administrator\Desktop\PowerShellTest\PowerShellTest\blog.cs:line 20
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

To fix it I just made the project’s Platform Target for All Configurations be x64.

image

NOTE 2:

I was getting a “Mixed mode assembly is built against version ‘v2.0.50727’ of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.” error on that line:

System.Management.Automation.CmdletInvocationException was unhandled
  Message=The type initializer for 'Microsoft.Exchange.Configuration.Tasks.Task' threw an exception.
  Source=System.Management.Automation
  WasThrownFromThrowStatement=false
  StackTrace:
       at System.Management.Automation.CommandProcessor.Init(CmdletInfo cmdletInformation)
       at System.Management.Automation.CommandInfo.GetMergedCommandParameterMetdata()
       at System.Management.Automation.CommandInfo.get_ParameterSets()
       at Microsoft.PowerShell.Commands.GetCommandCommand.AccumulateMatchingCommands(Collection`1 commandNames)
       at System.Management.Automation.CommandProcessor.ProcessRecord()
  InnerException: System.TypeInitializationException
       Message=The type initializer for 'Microsoft.Exchange.Configuration.Tasks.Task' threw an exception.
       Source=Microsoft.Exchange.Configuration.ObjectModel
       TypeName=Microsoft.Exchange.Configuration.Tasks.Task
       StackTrace:
            at Microsoft.Exchange.Configuration.Tasks.Task.AssemblyResolveEventHandler(Object sender, ResolveEventArgs args)
            at System.AppDomain.OnAssemblyResolveEvent(RuntimeAssembly assembly, String assemblyFullName)
       InnerException: System.IO.FileLoadException
            Message=Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.
            Source=Microsoft.Exchange.Data.Directory
            StackTrace:
                 at Microsoft.Exchange.Data.Directory.DSAccessTopologyProvider..ctor(String machineName)
                 at Microsoft.Exchange.Data.Directory.DSAccessTopologyProvider..ctor()
                 at Microsoft.Exchange.Data.Directory.DirectoryServicesTopologyProvider.DiscoverConfigDC()
                 at Microsoft.Exchange.Data.Directory.DirectoryServicesTopologyProvider..ctor()
                 at Microsoft.Exchange.Data.Directory.TopologyProvider.InitializeInstance()
                 at Microsoft.Exchange.Data.Directory.TopologyProvider.GetInstance()
                 at Microsoft.Exchange.Data.Directory.ADSession.GetConnection(String preferredServer, Boolean isWriteOperation, Boolean isNotifyOperation, String optionalBaseDN, ADObjectId& rootId, ADScope scope)
                 at Microsoft.Exchange.Data.Directory.ADSession.GetReadConnection(String preferredServer, String optionalBaseDN, ADObjectId& rootId, ADRawEntry scopeDeteriminingObject)
                 at Microsoft.Exchange.Data.Directory.ADSession.Find(ADObjectId rootId, String optionalBaseDN, ADObjectId readId, QueryScope scope, QueryFilter filter, SortBy sortBy, Int32 maxResults, IEnumerable`1 properties, CreateObjectDelegate objectCreator, CreateObjectsDelegate arrayCreator, Boolean includeDeletedObjects)
                 at Microsoft.Exchange.Data.Directory.ADSession.Find(ADObjectId rootId, QueryScope scope, QueryFilter filter, SortBy sortBy, Int32 maxResults, IEnumerable`1 properties, CreateObjectDelegate objectCtor, CreateObjectsDelegate arrayCtor)
                 at Microsoft.Exchange.Data.Directory.ADSession.Find[TResult](ADObjectId rootId, QueryScope scope, QueryFilter filter, SortBy sortBy, Int32 maxResults, IEnumerable`1 properties)
                 at Microsoft.Exchange.Data.Directory.SystemConfiguration.ADSystemConfigurationSession.Find[TResult](ADObjectId rootId, QueryScope scope, QueryFilter filter, SortBy sortBy, Int32 maxResults)
                 at Microsoft.Exchange.Data.Directory.SystemConfiguration.ADSystemConfigurationSession.FindServerByFqdn(String serverFqdn)
                 at Microsoft.Exchange.Data.Directory.SystemConfiguration.ADSystemConfigurationSession.FindLocalServer()
                 at Microsoft.Exchange.Configuration.SQM.CmdletSqmSession.GetOptInStatus()
                 at Microsoft.Exchange.Configuration.SQM.SqmSession.UpdateData(Boolean flushToDisk, Boolean closing)
                 at Microsoft.Exchange.Configuration.SQM.SqmSession.OnCreate()
                 at Microsoft.Exchange.Configuration.SQM.SqmSession.Open()
                 at Microsoft.Exchange.Configuration.SQM.CmdletSqmSession..ctor()
                 at Microsoft.Exchange.Configuration.SQM.CmdletSqmSession.get_Instance()
                 at Microsoft.Exchange.Configuration.Tasks.Task..cctor()
            InnerException:

To fix it I made the project’s Target Framework be the .NET Framework 3.5.

image

I mostly followed the instructions on this article, but they were made for Exchange 2007.

Adding the row number to an ASP.NET GridView

I often want my GridViews to have the first column just be a number identifying the row. I see this beneficial because:

  • The user can see the number of rows returned by scrolling to the end of the GridView
  • If there are two people looking at the GridView, they can say “look at item #5 …”

image

To make this happen you need to add this element to the <columns> element of your GridView:

 
<asp:GridView ...>
    <Columns> 
        <asp:TemplateField> 
            <ItemTemplate> 
                <%# Container.DataItemIndex + 1 %>. 
            </ItemTemplate> 
            <ItemStyle HorizontalAlign="Right" /> 
        </asp:TemplateField> 
        ... 
    </Columns> 
    ... 
</asp:GridView>	

This looks weird if paging is on. But you can tweak the math to make it work right.