Writing to an output text file and finding out extra characters or lines in it?

If you’re using File.OpenWrite() to create the output file, make sure the output file does not exist.  This MSDN article explains what happens if output file exists:

For an existing file, it does not append the new text to the existing text. Instead, it overwrites the existing characters with the new characters. If you overwrite a longer string (such as “This is a test of the OpenWrite method”) with a shorter string (such as “Second run”), the file will contain a mix of the strings (“Second runtest of the OpenWrite method”).


How Many Ways Can You Add Entity Framework Model To Your Project?

Let me count the ways.  3?  Well there are at least 3 ways you can add Entity Framework (EF) model to your .NET project, that I know of.  One is Code First, second is Reverse Engineer Code First, and third is the ADO.NET Entity Data Model (EDMX).

Code First, as the name suggests, requires you to code your EF model.  Yep, this requires more coding but gives you  a lot more control.  Usually you go this route if you don’t have an existing database to model or you want to create a fresh new database for your project.  I have not tried this approach as mostly the projects I worked on already has existing database. 

In Code First, basically you create the classes that model your database,  optionally configure your classes using Data Annotations (via class / property attributes) and/or Fluent API, and then create the database based on that model.  When you update the model, you can use Code First Migrations to update the database.  Microsoft’s Code First to a New Database has a video and a step-by-step walkthrough on how to go about this approach.

Reverse Engineer Code First uses EF Power Tools to generate the code for you containing the DbContext class, POCO classes, and Code First mapping classes, based on an existing database.  EF Power Tools Summary of Commands shows you a step-by-step walkthrough on how to do this plus some more, including customizing the default reverse engineer T4 templates and generating pre-compiled views to improve start-up performance. 

If you need to edit the t4 templates, I suggest getting a t4 editor such as tangible T4 Editor from the Visual Studio Gallery so you can get syntax highlighting at least.  When it comes to tweaking performance, Performance Considerations for Entity Framework 4, 5, and 6 has some tips.  One way is to generate pre-compiled views.  Pre-Generated Mapping Views shows you how to do this using EF Power Tools and also via APIs provided in EF6 onwards.  How to: Pre-Generate Views to Improve Query Performance shows you how to generate pre-compiled views using EDM generator command line tool.

Code First and Reverse Engineer Code First does not automatically generate an ADO.NET Entity Data Model (EDMX) file.  Same goes too for EDMX, it does not automatically generate any code.  To generate the code you get from Code First in EDMX, you need to add a code generation item, basically download a t4 template and generate the code based on it.  Database First shows you how to create an EDMX from an existing database, view and edit it in EF Designer and generate the code.  How to: Create a New .edmx File (Entity Data Model Tools) has a section on adding EDMX from an existing database and contains tons of links to more information, definitely a good resource.

It can get confusing what these code first and database first mean.  It all lies down to how you synchronize your model changes between the database and code or more accurately where you make your model changes, is it in code or database?  Although in reverse engineer code first, you can change your model in the database, and re-generate the code, essentially doing a database first.

Calling .Net Assembly from VBA

Stuck in VBA?  You don’t have to be.  You can move all your business logic code from VBA to a .Net assembly.  It’s easier than you might think and this post will show you how.

First you need to create a new Class Library project and below is the basic structure of a COM-callable wrapper for your .Net assembly.

// need this so we can decorate our classes with ClassInterface
//  and ComVisible attributes
using System.Runtime.InteropServices;

// namespace should be the same as assembly name
//  so when VBA calls it via New or CreateObject() it will use same reference name
//   and prog ID
//   ie. New YourNetAssembly.TestFunc or CreateObject("YourNetAssembly.TestFunc")
namespace YourNetAssembly
    // need to decorate class with following attributes
    //  so we can access its members using intellisense in VBA editor
    [ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
    public class TestFunc
        public int Add(int num1, int num2)
            return num1 + num2;


And when you are ready to compile or build, the next thing you need to do is check the Make assembly COM-Visible in your project’s Properties –> Application –> Assembly Information….

Now if you are developing your VBA application (be it MS Access or MS Excel) locally or on the same computer where you build your .Net assembly, you can have Visual Studio register your COM automatically by checking the Register for COM interop in your project’s Properties –> Build.

But if you are developing your VBA application remotely or on a different computer, then you will need to copy your .Net assembly to that computer and manually register it using regasm.exe.  You might want to create a batch file containing the following command so you don’t have to type it every time.

c:\Windows\Microsoft.NET\Framework\v4.0.30319\regasm.exe YourNetAssembly.dll /codebase /tlb

Note the correct .Net Framework version that your .Net assembly is created in.  Each version of the .Net Framework will have its own folder.  Also this command is run from the VBA application folder where the .Net assembly resides.  Yours might have a different setup so take note.

/codebase option adds the .Net assembly’s path on the disk to the Windows registry.  /tlb option generates the type library and saves it on the same folder as your .Net assembly.

Note that you need administrator privileges on the computer to generate a type library, be it via Visual Studio or regasm.exe.  If you already have an administrator privilege, you need to open Visual Studio or the command prompt as admin by right-clicking and selecting Run as administrator.

Once registered, you then add a reference to it from your VBA application.  From the VBA editor, go to Tools –> References… and look for your .Net assembly in the list of Available References.  Its name should be the assembly name.  You can check the assembly name in your project’s Properties –> Application under Assembly name.  Once added, you can now start calling your .Net assembly from VBA.

There are two ways you might want to call your .Net assembly.  Using the New keyword, or using CreateObject().

Dim objTestFunc As New YourNetAssembly.TestFunc
' OR ...
Dim objTestFunc As Object
Set objTestFunc = CreateObject("YourNetAssembly.TestFunc")

Dim intResult As Integer
intResult = objTestFunc.Add(1, 2)
MsgBox intResult


Great, but how about debugging or stepping into the .Net assembly?  Well if you have both Visual Studio and VBA development environments on one machine, then it would be easier.  You just set a breakpoint inside your .Net assembly in Visual Studio.  Then in your project’s Properties –> Debug, enter the full pathname to the program that runs your VBA application (maybe MS Access or MS Excel) in Start external program.  Then in the Command line arguments, enter the full pathname to your VBA application.  Save changes then press F5 to run.  This will run MS Access or MS Excel (whichever one you are using) which in turn runs your VBA application.  When your VBA application calls your .Net assembly, it will break into the Visual Studio debugging environment.

What about if your VBA development environment is on a different machine?  Well hope is not lost yet.  Assuming you have a debugger program installed on that machine, you can place a Debug.Assert(false); or Debugger.Break(); in your .Net assembly and this will force it to go into debugging mode when it hits that code and open the debugger program.  Be sure to copy the PDB (debug) file for your .Net assembly plus the source code so you can step through .Net code.

        public int Add(int num1, int num2)
            // OR ...

            // if Debugger.Break() does not work properly i.e. it does not return back
            //  to the VBA application after debugging, 
            //  then use Debug.Assert(false) instead

            return num1 + num2;


So that’s all there is to it.  Easy eh?  So start coding away then!


Actually you don’t need to check the Make assembly COM-Visible in your project’s Properties –> Application –> Assembly Information…. unless you want to expose all your public classes in the assembly.  And besides since you still need to decorate your public classes with COM attributes, might as well just use COM attributes to select only those ones you want to be COM-visible.

I found this great article that summarizes what you need to do: Best Practice in Writing a COM-Visible Assembly (C#).  I didn’t bother adding the ProgId attribute to my COM-visible classes though, but the rest I did.  Plus I created an interface for each, as was suggested in the article.  Nice thing having an interface is you can control which methods in your COM-visible classes you want to expose, giving you a more granular level of control.

New to Active Directory and need to use it in .NET?

Don’t worry, it’s easier than you think. 


AD Explorer

If you haven’t seen what Active Directory (AD) looks like, you can use a free AD viewer application, like the AD Explorer.  I would recommend downloading it because it’s a useful tool in navigating the tree structure of an AD and viewing the object properties or attributes that you want to use in your .NET program.


Logging In To AD

When using an AD viewer application, you will need to login to the AD and provide your Windows user login and password.  Usually the AD would be the domain name your computer is logged into.

Two ways to determine the domain your computer is logged into.  One is from Control Panel –> System and there will be a Domain: entry if you are logged into one.  Another way is looking at the environment variable USERDOMAIN.  From the command prompt, type set user and press ENTER.  Look at the USERDOMAIN= entry.  If it does not contain your computer name, then it should be the AD name.  For a more detailed instruction, click here.


Distinguished Names

Once you get into the AD, you will see the AD tree structure and each item in the tree structure is an object.  Each object can be uniquely identified by it’s distinguished name (DN) or path and contains a sequence of RDNs connected by commas.  RDNs are relative distinguished names and they are basically attributes with associated values.  You can find a list of typical RDNs here with some examples of distinguished names and a table listing the reserved characters that need to be escaped when used in attribute values.


AD Objects in .NET

To get starting coding AD in .NET, you will need to reference System.DirectoryServices in your program and add the following statement:

using System.DirectoryServices;

And the two objects that you need to use are: DirectoryEntry and DirectorySearcher.

You use DirectoryEntry in which to bind the object in the AD tree to.  You  need to supply the provider (usually it’s LDAP:// ) and the path which can include the AD name.  The example below is querying an AD user.

// sADName would be the AD you want to log into
string sADName = "addomain.com";

// sDN would be the distinguished name or path of an object in the AD tree
string sDN = "CN=Users,DC=addomain,DC=com";

// create an instance of DirectoryEntry supplying in the provider and path
// in example below, provider is LDAP:// and path is the combination of
// AD name and distinguished name
DirectoryEntry adEntry =
new DirectoryEntry(@"LDAP://" + sADName + "/" + sDN);

// read the property or attribute of an AD object,
// such as the user's display name

For a list of all attributes defined by AD, click here.  The list there does not show the attribute names to use in the Properties collection of the DirectoryEntry object.  When you click an attribute in the list, it will show the detailed information about the attribute.  The attribute name to use should be under the Ldap-Display-Name.

You use DirectorySearcher when you want to search AD, say for example users with Smith as their last names.

// you pass in an instance of the DirectoryEntry object containing the root 
// or path in AD as a starting point to search from, to DirectorySearcher
DirectoryEntry adEntry =
new DirectoryEntry(@"LDAP://addomain.com/DC=addomain,DC=com");
DirectorySearcher adSearch = new DirectorySearcher(adEntry);

// set the filter
adSearch.Filter = "(&(objectCLass=user)(sn=Smith))";

// then search
SearchResultCollection adResultCol = adSearch.FindAll();
listBoxResults.DataSource =
(from SearchResult r in adResultCol
select new
Value = r.GetDirectoryEntry().Properties["distinguishedName"]
Text = r.GetDirectoryEntry().Properties["displayName"]

For more details on the search filter syntax, click here.


Other Resources

WinForms: Using DevExpress, there is a quick way of making sure only one instance of a form is open

If you are lucky enough to be using DevExpress and you don’t like the idea of using the Singleton pattern in enforcing this rule, then look no further.

You can use DevExpress’ DocumentManager control to manage your opened forms.  My solution is to create a static FormManager class encapsulating the document manager control and providing the one important method ShowChildForm which the WinForms application can call, passing in the type of the form it wishes to open.  Very simple.

Below is the code for the FormManager class:

namespace WindowsFormsApplication1
    public static class FormManager
        private static DocumentManager documentManager;
        public static void InitializeFormManager(Form parentForm)
            documentManager = new DocumentManager();
            documentManager.MdiParent = parentForm;
            documentManager.View = new NativeMdiView();
        public static void ShowChildForm(Type type)
            if (documentManager == null)
            // check if child form is already open
            // if it's already open, then just activate it
            foreach (BaseDocument doc in documentManager.View.Documents)
                if (doc.Control.GetType() == type)
            // otherwise, create a new instance of the child form and display
            Form childForm = (Form)Activator.CreateInstance(type);
            childForm.MdiParent = documentManager.MdiParent;


And somewhere in your WinForms application UI code, you call this function: