Using SMO to Create an Index with Included Columns

A question that came up on the SqlDownUnder mailing list today was how to create an index using SMO and specify an included column. The documentation is extremely scarce; I could find no mention of it in MSDN. Greg Low came to the rescue by noting that the IndexedColumn class has an IsIncluded property.

This is illustrated in the following C# snippet:

 

public Index CreateIndex

(

    string indexName,

    bool isClustered,

    IndexKeyType indexKeyType,

    string[] indexColumnList,  // in index column order

    bool[]   indexDescending, 

    string[] includedColumnList

)

{

    if (indexDescending != null && indexDescending.Length != indexColumnList.Length)

    {

        throw new ArgumentOutOfRangeException(

            “Either pass indexDescending as null, or with same length as indexColumnList.”);

    }

 

    Index index = new Index

                  {

                      Name = indexName,

                      IndexKeyType = indexKeyType,

                      IsClustered = isClustered

                  };

 

    int i = 0;

    foreach (string indexColumnName in indexColumnList)

    {

        bool descending = (indexDescending != null) ? indexDescending[i] : false;

        IndexedColumn indexedColumn = new IndexedColumn(index, indexColumnName, descending);

        index.IndexedColumns.Add(indexedColumn);

        i++;

    }

 

    // Only add included columns for none primary or unique indexes

    if (includedColumnList != null && indexKeyType == IndexKeyType.None)

    {

        foreach (string includedColumnName in includedColumnList)

        {

            IndexedColumn indexedColumn = new IndexedColumn(index, includedColumnName);

            indexedColumn.IsIncluded = true;

            index.IndexedColumns.Add(indexedColumn);

        }

    }

 

    return index;

}

Reminder: Perth .NET User Group Meeting: Thurs 27th May, 5:30pm: XNA Game Development – with Andrew Parsons

Join us at the Perth .NET User Group Thurs, May 27th where Andrew Parsons will build a simple 2D shooter game for Windows and Xbox 360 in an hour from start to finish, and hear how you can do the same thing with the tools already at your disposal not just for Windows and Xbox but also the upcoming Windows Phone 7.

  • TOPIC:   XNA Game Development – with Andrew Parsons
  • DATE:    Thursday, May 27th, 5:30pm – 7:00pm
  • VENUE:  Excom, Ground Floor, 23 Barrack Street, Perth
  • COST:    Free. All welcome

Andrew Parsons is the Academic Developer Evangelist for Microsoft Australia which means he gets to travel the country talking to students about cool technology – stuff like Xbox, Windows Phone, cool dev stuff, tips and tricks with Office, PhotoSynth, Pivot and more. Combining his 15 years as a professional developer and 8 years as a professional games journalist prior to joining Microsoft, his passion for video game development has seen an outlet through XNA.

Using CopySourceAsHTML 3.0 with VS 2010

Update: although this works, it makes VS2010 open really slowly…

There does not seem to be an installer for VS2010. You can install the VS2008 version of CopySourceAsHtml (from here), and then manually edit the CopySourceAsHtml.AddIn file located at C:\Users\\Documents\Visual Studio 2010\Addins so that both Version elements are set to 10.0:

<?xml version=1.0 encoding=utf-8 standalone=no?>

<Extensibility xmlns=http://schemas.microsoft.com/AutomationExtensibility>

<HostApplication>

<Name>Microsoft Visual Studio Macros</Name>

<Version>10.0</Version>

</HostApplication>

<HostApplication>

<Name>Microsoft Visual Studio</Name>

<Version>10.0</Version>

</HostApplication>

<Addin>

<FriendlyName>CopySourceAsHtml</FriendlyName>

<Description>Adds support to Microsoft Visual Studio 2008 for copying source code, syntax highlighting, and line numbers as HTML.</Description>

<Assembly>JTLeigh.Tools.Development.CopySourceAsHtml, Version=3.0.3215.1, Culture=neutral, PublicKeyToken=bb2a58bdc03d2e14, processorArchitecture=MSIL</Assembly>

<FullClassName>JTLeigh.Tools.Development.CopySourceAsHtml.Connect</FullClassName>

<LoadBehavior>1</LoadBehavior>

<CommandPreload>0</CommandPreload>

<CommandLineSafe>0</CommandLineSafe>

</Addin>

</Extensibility>

Recursively Enumerate Files

I recently needed to get all files in a folder hierarchy, so I quickly wrote the following C# snippet:

List<string> files = FindMatchingFilesRecurse(startFolder, "*.txt");

public List<string> FindMatchingFilesRecurse(string folder, string filePattern)
{
    List<string> fileList = new List<string>();
 
    if (!string.IsNullOrEmpty(folder))
    {
        FindMatchingFilesHelper(folder, filePattern, fileList);
    }
 
    return fileList;
}
 
private void FindMatchingFilesHelper(string folder, string filePattern, List<string> list)
{
    list.AddRange(Directory.GetFiles(folder, filePattern));
 
    foreach (string dir in Directory.GetDirectories(folder))
    {
        FindMatchingFilesHelper(dir, filePattern, list);
    }
}

But .NET 4.0 now contains the same functionality in a single method call (and as a bonus, it’s approximately 20-30% faster!):

List<string> files = Directory.EnumerateFiles(startFolder, "*.txt", SearchOption.AllDirectories).ToList();
 

Connect to SQL Server Using Windows Authentication

In a recent post, Securing your application. Part 1 – Securing your connection, Brian Madsen talks about encrypting your database connection string, but fails to mention that using this technique does not stop passing unencrypted passwords over the network to your database server.

Whenever possible, you should use Windows authentication instead of SQL authentication to connect from your ASP.NET application to your database server. You should still consider encrypting your connection string to protect server connection details, such as the server and database name.

For ASP.NET 2.0 applications, you should store connection strings in the <connectionStrings> section of your application’s web.config file. The connection string used with Windows authentication must include either the Trusted_Connection=Yes attribute, or the equivalent attribute Integrated Security=SSPI, as shown here.


<add name="MyDbConn1"
connectionString="Server=MyServer;Database=MyDb;Trusted_Connection=Yes;"/>
<add name="MyDbConn2"
connectionString="Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;"/>

The above two strings are equivalent and both result in Windows authentication to the database.

Ref.: How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0

Perth .NET User Group Meeting: Code Contracts in Framework 4.0 with Joe Albahari

Join us at the Perth .NET User Group Thurs, May 6th for a presentation on Code Contracts. With Framework 4.0, Code Contracts are now a reality. In this session, we’ll look first at what Code Contracts are, and then demonstrate how to use them in Framework 4.0 to write more robust, concise and better-documented code. We’ll also look under the covers on how they work, and see how to tailor contract behaviour for development, deployment, legacy and unit testing environments.

  • TOPIC:   Code Contracts in Framework 4.0 – with Joe Albahari
  • DATE:    Thursday, May 6th, 5:30pm – 7:00pm
  • VENUE:  Excom, Ground Floor, 23 Barrack Street, Perth
  • COST:    Free. All welcome

Joe Albahari is author of O’Reilly’s C# 4.0 in a Nutshell. He’s a regular user group speaker and has presented at TechEd and JAOO.

There will be door prizes of: 2 copies of ‘C# 4.0 in a Nutshell’ and a LINQPad license courtesy of Joe Albahari, plus a choice of license from JetBrains (one of ReSharper , TeamCity Build Agent, dotTrace Profiler, RubyMine, IntelliJ IDEA).

Arrive early if you want a seat!