Fun With PowerShell Objects – Creating Objects from C#


This is the next installment in my series on creating objects in PowerShell. In the first installment we showed how to create an object using the class type introduced in PowerShell 5. That covered the basics, and in the last installment of this series we’ll cover some advanced techniques.

In the last two installment of this series, I covered the various ways to create objects using the PSCustomObject. We saw how to create it using the New-Object cmdlet, then how to add your custom properties to it using the Add-Member cmdlet. In the subsequent post we saw how to add new methods to it.

In this post, we’ll cover something new, creating an object based on C# code!

For all of the examples we’ll display the code, then (when applicable) under it the result of our code. In this article I’ll be using PowerShell Core, 7.2.1, and VSCode. The examples should work in PowerShell 5.1 in the PowerShell IDE, although they’ve not been tested there.

Additionally, be on the lookout for the backtick ` , PowerShell’s line continuation character, at the end of many lines in the code samples. The blog formatting has a limited width, so using the line continuation character makes the examples much easier to read. My post Fun With PowerShell Pipelined Functions dedicates a section to the line continuation character if you want to learn more.

To run a snippet of code highlight the lines you want to execute, then in VSCode press F8 or in the IDE F5. You can display the contents of any variable by highlighting it and using F8/F5.

Why C#

You may be asking yourself, why integrate C# code into PowerShell? Why not just define our objects using PowerShell? There are several reasons, especially if you are working with a team of C# developers, or are a C# developer yourself.

First, it can make testing your C# classes easy. You can code the class and save it in a .CS file. Then run some simple PowerShell to create a new object from your class and test its various properties and methods. This lets you make changes easily and test again. All without having to create a full blown C# project just to test.

It also allows for code reuse. Let’s say your C# developers have created a library to do some calculations, and you need to use one of the methods from that library, but that library expects you to pass in an object based on a class.

Let’s further the example, perhaps you have the task of reading in a CSV file, doing a calculation for each row, then outputting another CSV file, or maybe even a JSON file. This is a one time use, so you don’t want to go to the effort of creating a full blown C# project.

Using the techniques in this demo, you could simply access the C# file in which the class was defined, and generate an object from it in PowerShell. Then all you’d have to do is populate the object and pass it into the library to do the calculation, and output the result.

These are just a few simple examples, I’m sure you’ll come up with many more as the need arises. I’ll be honest, this isn’t something you will need to do a great deal, but when you do you’ll appreciate knowing how.

Embedding a C# Class in your PowerShell

In this first method, we’ll define a standard C# class within a here string then add it as a new data type in PowerShell. Once it exists as a type, we can then generate new objects from it. Let’s take a look at a very simple class definition.

$code = @"
using System;

public class SchemaTable
  public string DatabaseName;

  public string SchemaTableName(string pSchema, string pTable)
    string retVal = "";  // Setup a return variable

    retVal = pSchema + "." + pTable;

    return retVal;

  } // public SchemaTableName

  public string FullName(string pSchema, string pTable)
    string retVal = "";  // Setup a return variable

    retVal = this.DatabaseName + "." + pSchema + "." + pTable;

    return retVal;

  } // public FullName

} // class SchemaTable


If you’ve read this far I’m going to assume you are familiar with C#, at least the basics of it, so I’ll keep this explanation at a high level.

We start by declaring a class named SchemaTable. The next line declares a variable (which will become a property) called DatabaseName.

I then create the first of two functions (which will become our methods). The SchemaTableName simply takes the two passed in values of pSchema and pTable and concatenates them together with a period between them.

The second, FullName, takes the value in DatabaseName and concatenates them with the schema and table name parameters, again using a period as a separator in the return string.

Next, we need to add this class as a new data type in PowerShell. You are already familiar with many data types, such as int, string, and more. We simply want to add a new data type to our PowerShell environment.

To do so, we will use the Add-Type cmdlet.

Add-Type -TypeDefinition $code `
         -Language CSharp

The first parameter, TypeDefinition, takes the C# code we defined in the here string. The second, Language, is pretty obvious. We simply need to let PowerShell know what language this code was written in.

As of right now, the only supported language is C#. Because of this, if you leave off the -Language CSharp parameter, it will default to C# as the language. I included it here for completeness, but will omit it for future examples in this post.

So now we’ve defined a class in C#, and have added it as a new data type in PowerShell. How then do we create an object from it?

We’ll turn to our old friend, New-Object.

$result = New-Object -TypeName SchemaTable

That’s it, that simple one line will create our new object based on the C# code we defined in the $code variable. You can even use Get-Member to display its properties and methods like you would with any other object.

$result | Get-Member


Name            MemberType Definition
----            ---------- ----------
Equals          Method     bool Equals(System.Object obj)
FullName        Method     string FullName(string pSchema, string pTable)
GetHashCode     Method     int GetHashCode()
GetType         Method     type GetType()
SchemaTableName Method     string SchemaTableName(string pSchema, string pTable)
ToString        Method     string ToString()
DatabaseName    Property   string DatabaseName {get;set;}

It contains the standard methods and properties built into all objects, but it also has the three we defined: FullName, SchemaTableName, and DatabaseName.

We can use these properties and methods just like ones in any other object. Let’s set the DatabaseName property, then display it.

$result.DatabaseName = 'MyDB'



Likewise, we can access the methods we created. Here’s the SchemaTableName method.

$result.SchemaTableName('ASchema', 'ATable')



And for completeness, the FullName method.

$result.FullName('ASchema', 'ATable')



C# Classes with Static Methods

In the original post in this series on basic PowerShell classes, I mentioned the concept of static methods and properties. As static method or property is simply one that can be called without having to generate a new object.

While we’ll circle back around to discuss implementing static methods and properties in PowerShell classes in the final post in this series, it is likely you’ll encounter C# classes with static methods and properties. As such we’ll go ahead and cover them here, while we are talking C#.

In the code below, I’ve defined a class with one method, and labeled it as static. In reality you will encounter many classes that have a mix of static and non-static members, but for this post we’ll keep the example simple.

$code = @"
using System;

public class StaticSchemaTable
  public static string FullName(string pSchema, string pTable)
    string retVal = "";

    retVal = pSchema + "." + pTable;

    return retVal;

  } // public static FullName
} // class StaticSchemaTable

As you can see, I’ve simply used the static keyword as part of the FullName function declaration. I also changed the name of the class, otherwise the code is the same as the previous demo.

Now we need to add this as a new type in our current PowerShell session.

Add-Type -TypeDefinition $code

Calling our static method requires different syntax. First, it won’t be necessary to create an object from it. Second, we’ll need to use the full name of our class in brackets, followed by two colons. We then indicate the name of the static function to call, and pass in any parameters.

$result = [StaticSchemaTable]::FullName('MySchema', 'myTable')



Static methods and parameters aren’t something that’s used a great deal, but they are used so you should know how to handle them.

Creating an Object from A C# File

While it is certainly possible to embed C# code right in your PowerShell, this could lead to some issues. The moment a developer makes a change to their .cs file, you are now out of sync. You don’t want to have to keep cutting and pasting all the time.

It makes far more sense, then, to simply access the C# file with the class definition, and load it at run time.

First, you need to create a C# file with the sample code. Here is what I put in my .cs file:

using System;

public class StaticSchemaTableInFile
  public static string FullName(string pSchema, string pTable)
    string retVal = "";

    retVal = pSchema + "." + pTable;

    return retVal;

  } // public static FullName
} // class StaticSchemaTableInFile

This is the same static class you just saw, with the minor change to the class name.

In the next sample, I simply build the path to the file name, then use the Get-Content to read it.

$csPath = 'C:\Users\arcan\OneDrive\BlogPosts\Markdown\'
$file = "$($csPath)Fun-With-PowerShell-Objects-Part 3.cs"
$code = Get-Content $file | Out-String

Get-Content will read the contents of the file. By default Get-Content reads it as an array with each line in the file being an element, so we’ll have to pipe it through the Out-String cmdlet to convert it to a single string. This string is then stored in the $code variable. Of course you’ll need to update the path and file variables you used on your computer.

Now we do just like the previous demo, call Add-Type then run it.

Add-Type -TypeDefinition $code
$result = [StaticSchemaTableInFile]::FullName('mySchema', 'myTable')



Again, I reused the example from the static demo but we could also have used the first example, or any standard C# file containing class definitions.

Other Ways To Add Types

While outside the scope of this post, I did want to mention there are two other ways you can add new data types to your PowerShell scripts. First, Add-Type will let you load types stored in an assmebly, in other words a .dll file.

Second, if you are running on a Windows computer you can add types stored in native Windows APIs. If you want to learn more about these, I’ll refer you to the Add-Type Documentation at Microsoft.


One last thing you need to be aware of. When you add a type it does not persist between sessions. The moment you close your command window or VSCode, that type goes away. You’ll need to recreate it the next time you run your script.

This is probably a good thing, as it’s doubtful you will need to use these types on a daily basis. Should you need to, though, you’ll have to edit your PowerShell profile and have them added within it.


In this post we saw how to create custom objects from C# classes. They could be embedded in our PowerShell code, or stored in external files. The subject of static methods and properties were mentioned, along with a demo on how to use them.

In the next post we’ll see a very useful technique, and cover the ability to add our own custom methods and properties to existing objects that others created, including ones built right into PowerShell.

The demos in this series of blog posts were inspired by my Pluralsight course PowerShell 7 Quick Start for Developers on Linux, macOS and Windows, one of many PowerShell courses I have on Pluralsight. All of my courses are linked on my About Me page.

If you don’t have a Pluralsight subscription, just go to my list of courses on Pluralsight . At the top is a Try For Free button you can use to get a free 10 day subscription to Pluralsight, with which you can watch my courses, or any other course on the site.


Lookups in PowerPivot Calculated Columns – Using the RELATED Function

In my previous post we looked at how Calculated Columns work in PowerPivot. One limitation you may have noticed though was all of the calculations were done using values in that individual table. What if you wanted to lookup a value in second table, based on a value in the first table, and return a value from that second table. Yes Virginia, not only is there a Santa Clause but there’s also an answer for us in PowerPivot’s RELATED function.

In addition to the standard Excel functions, PowerPivot provides a set of its own functions for working with its data. These new functions are collectively known as Data Analysis eXpressions, or DAX for short. By now you’ve probably guessed that the first function from the DAX toolbox you’ll want to learn is the RELATED function.

Let’s start with the same Excel 2010 workbook we had at the end of the lesson Combining Data from Multiple Sources in PowerPivot. If you recall we had imported data from the AdventureWorksLT2008 database. To that we added data the CountryInfo table, which we’d typed in to an Excel spreadsheet.


At the time we used this to get the CountryAbbr column, and you may have wondered why we also included a DiscountRate column. It’s this lesson where this DiscountRate will come in to play.

If you recall from that post, we used PowerPivot’s Manage Relationships feature to create a link that ultimately connected the SalesOrderDetail table to the CountryInfo table. This groundwork enables us to lookup values very easily. Here is a simple example. Go to the Add Column column of SalesOrderDetail and enter this formula into the fx area:


When PowerPivot gets done, the abbreviation for each country to be shipped to appears in this column. This can be used to “flatten out” some of your data. However it’s much more useful when used as part of a calculation. Delete the column we just added (right click on the column header and pick Delete Column from the menu).

Looking in the CountryInfo table we see the DiscountRate. A value of 0.04 means our US customers get a discount of 4% off their LineTotal. So in our SalesOrderDetail table we want to take the LineTotal and calculate a new value based on the rate, which is stored in the CountryInfo table. Simple enough using the PowerPivot RELATED function.

=[LineTotal] * (1 – RELATED(‘CountryInfo'[DiscountRate]))

This is fairly simple math, we take the DiscountRate from CountryInfo (for US, 0.04) and subtract from 1 giving us 0.96, or 96%. This is then multiplied by the LineTotal giving us our new discounted LineTotal amount, which I renamed to DiscountedLineTotal.


Hopefully these simple examples will give you a glimpse of the immense power RELATED offers. It will allow you to combine data from multiple tables to create in-depth analysis that previously required a specialist in Business Intelligence to create.

TechMixer University – SSIS for Developers

In addition to help recruit speakers, I also had the privilege of speaking at TechMixer University 2009.

The slide deck and main demo can be found at my Code Gallery site:

The calling of SSIS from .Net demo can be found at:

Thanks to everyone who attended TechMixer University. I look forward to seeing you next year!

Accessing FILESTREAM Data From A Client .NET Application – Part 2 Downloading a File

In the previous entry we covered how to upload a file to SQL Server using the FILESTREAM, new to SQL Server 2008. In this post we will look at retrieving a file from SQL Server using FILESTREAM. If you missed yesterday’s installment, a simple front end was created, the full project can be found at the Code Gallery site .

The interface is very simple:


The grid is a Data View Grid that shows the ID and Document information from the table we previously created. (If you want to see the code to populate the grid see the project.) The user picks a row, then clicks on the Get File button.

    private void btnGetFile_Click(object sender, EventArgs e)


      // Reset in case it was used previously

      lblStatus.Text = "";


      if (dgvFiles.CurrentRow != null)


        // Grab the ID (Primary Key) for the current row

        int ID = (int)dgvFiles.CurrentRow.Cells[0].Value;

        // Now Save the file to the folder passed in the second

        // paramter.

        FileTransfer.GetFile2(ID, @"D:\Docs\Output\");

        // And let user know it’s OK

        lblStatus.Text = "File Retrieved";



The code is very simple, the heart of it is the FileTransfer.GetFile static method. Two values are passed in, the integer ID, which is the primary key from the database, and the path to save the file to. Here I simply hard coded a path, in a real life application you will want to give the user the ability to enter a path. Let’s take a look at the GetFile routine.

    public static void GetFile(int ID, string outputPath)


      // Setup database connection

      SqlConnection sqlConnection = new SqlConnection(

                "Integrated Security=true;server=(local)");


      SqlCommand sqlCommand = new SqlCommand();

      sqlCommand.Connection = sqlConnection;






        // Everything we do with FILESTREAM must always be in

        // the context of a transaction, so we’ll start with

        // creating one.

        SqlTransaction transaction

          = sqlConnection.BeginTransaction("mainTranaction");

        sqlCommand.Transaction = transaction;


        // The SQL gives us 3 values. First the PathName() method of

        // the Document field is called, we’ll need it to use the API

        // Second we call a special function that will tell us what

        // the context is for the current transaction, in this case

        // the "mainTransaction" we started above. Finally it gives

        // the name of the document, which the app will use when it

        // creates the document but is not strictly required as

        // part of the FILESTREAM.


          = "SELECT Document.PathName()"


          + ", DocumentName "

          + "FROM FileStreamFTS.dbo.DocumentRepository "

          + "WHERE ID=@theID ";



          "@theID", SqlDbType.Int).Value = ID;


        SqlDataReader reader = sqlCommand.ExecuteReader();

        if (reader.Read() == false)


          throw new Exception("Unable to get BLOB data");



        // OK we have some data, pull it out of the reader into locals

        string path = (string)reader[0];

        byte[] context = (byte[])reader[1];

        string outputFilename = (string)reader[2];

        int length = context.Length;



        // Now we need to use the API we declared at the top of this class

        // in order to get a handle.

        SafeFileHandle handle = OpenSqlFilestream(




          , context

          , (UInt32)length, 0);


        // Using the handle we just got, we can open up a stream from

        // the database.

        FileStream databaseStream = new FileStream(

          handle, FileAccess.Read);


        // This file stream will be used to copy the data to disk

        FileStream outputStream

          = File.Create(outputPath + outputFilename);


        // Setup a buffer to hold the streamed data

        int blockSize = 1024 * 512;

        byte[] buffer = new byte[blockSize];


        // There are two ways we could get the data. The simplest way

        // is to read the data, then immediately feed it to the output

        // stream using it’s Write feature (shown below, commented out.

        // The second way is to load the data into an array of bytes

        // (here implemented using the generic LIST). This would let

        // you manipulate the data in memory, then write it out (as

        // shown here), reupload it to another data stream, or do

        // something else entirely.

        // If you want to go the simple way, just remove all the

        // fileBytes lines and uncomment the outputStream line.

        List<byte> fileBytes = new List<byte>();

        int bytesRead = databaseStream.Read(buffer, 0, buffer.Length);

        while (bytesRead > 0)


          bytesRead = databaseStream.Read(buffer, 0, buffer.Length);

          //outputStream.Write(buffer, 0, buffer.Length);

          foreach (byte b in buffer)




        // Write out what is in the LIST to disk

        foreach (byte b in fileBytes)


          byte[] barr = new byte[1];

          barr[0] = b;

          outputStream.Write(barr, 0, 1);



        // Close the stream from the databaseStream



        // Write out the file



        // Finally we should commit the transaction.



      catch (System.Exception ex)











The routine kicks off by opening a connection, then establishing a transaction. Remember from the previous lesson that every time you work with a FILESTREAM it has to be in a transaction. Next we basically duplicate the SQL used in the previous lesson, returning the path name, transaction context, and document name. The only difference is we pass in the ID as a parameter. With that, just like with the previous example we call the OpenSqlFilestream API. Note a difference, in this example the second parameter is “DESIRED_ACCESS_READ” as opposed to the write access we indicated previosly.

Once we have the “handle” we can create a FileStream for reading from the database. In this example I loop through the file stream, loading the data into a LIST of bytes. Once in memory we are free to work with it as we need to. In this example I simply loop back through the generic List and write the data to the file stream we opened on the disk for writing. If all you are doing is writing, it would be somewhat more efficient to write the code like so:

        int bytesRead = databaseStream.Read(buffer, 0, buffer.Length);

        while (bytesRead > 0)


          bytesRead = databaseStream.Read(buffer, 0, buffer.Length);

          outputStream.Write(buffer, 0, buffer.Length);



        // Close the stream from the databaseStream


I simply eliminate the local byte array and write the buffer directly to the disk. Either way, the remainder is simple, just closing all the streams, commiting the transaction and closing the database connection.

This concludes the series on how to use FILESTREAM, in future posts we look into how to do Full Text Search with FILESTREAM stored objects.

Accessing FILESTREAM Data From A Client .NET Application – Part 1 Uploading a File

The best way to work with documents in a database is via a .Net application. I created a simple Windows forms project to access the table I created in previous lessons. I named the application FileLoader, you can the entire project at the Code Gallery site .

The interface is very simple:


As you can see there are two main functions, the upper half uploads a file to the SQL Server. The lower half displays the files already in the table, lets the user pick one and then click the GetFile button to save it locally. Today we’ll look at the Upload File functionality. Here is the code:

    private void btnUploadFile_Click(object sender, EventArgs e)


      // Reset in case it was used previously

      lblStatus.Text = "";


      // Make sure user entered something

      if (txtFile.Text.Length == 0)


        MessageBox.Show("Must supply a file name");

        lblStatus.Text = "Must supply file name";




      // Make sure what user entered is valid

      FileInfo fi = new FileInfo(txtFile.Text);

      if (!fi.Exists)


        MessageBox.Show("The file you entered does not exist.");

        lblStatus.Text = "The file you entered does not exist.";




      // Upload the file to the database



      // Refresh the datagrid to show the newly added file



      // Let user know it was uploaded

      lblStatus.Text = fi.Name + " Uploaded";


The real line of importance is the FileTransfer.UploadFile. This calls a static method in a class I named FileTransfer.cs. In order to use FILESTREAM there is an API call we have to make, so at the header area of the FileTransfer we have a lot of declarations. These are pretty much a straight copy from the MSDN help files.

    //These contants are passed to the OpenSqlFilestream()

    //API DesiredAccess parameter. They define the type

    //of BLOB access that is needed by the application.


    const UInt32 DESIRED_ACCESS_READ = 0x00000000;

    const UInt32 DESIRED_ACCESS_WRITE = 0x00000001;

    const UInt32 DESIRED_ACCESS_READWRITE = 0x00000002;


    //These contants are passed to the OpenSqlFilestream()

    //API OpenOptions parameter. They allow you to specify

    //how the application will access the FILESTREAM BLOB

    //data. If you do not want this ability, you can pass in

    //the value 0. In this code sample, the value 0 has

    //been defined as SQL_FILESTREAM_OPEN_NO_FLAGS.


    const UInt32 SQL_FILESTREAM_OPEN_NO_FLAGS = 0x00000000;

    const UInt32 SQL_FILESTREAM_OPEN_FLAG_ASYNC = 0x00000001;

    const UInt32 SQL_FILESTREAM_OPEN_FLAG_NO_BUFFERING = 0x00000002;





    //This structure defines the format of the final parameter to the

    //OpenSqlFilestream() API.


    //This statement imports the OpenSqlFilestream API so that it

    //can be called in the Main() method below.

    [DllImport("sqlncli10.dll", SetLastError = true, CharSet = CharSet.Unicode)]

    static extern SafeFileHandle OpenSqlFilestream(

                string Filestreamath,

                uint DesiredAccess,

                uint OpenOptions,

                byte[] FilestreamTransactionContext,

                uint FilestreamTransactionContextLength,

                Int64 AllocationSize);


    //This statement imports the Win32 API GetLastError().

    //This is necessary to check whether OpenSqlFilestream

    //succeeded in returning a valid / handle


    [DllImport("kernel32.dll", SetLastError = true)]

    static extern UInt32 GetLastError();

OK, with that out of the way, I’ve created a public, static method to upload the file. Here is the full routine:

    public static void UploadFile(string fileName)


      // Establish db connection

      SqlConnection sqlConnection = new SqlConnection(

                "Integrated Security=true;server=(local)");

      SqlTransaction transaction = null;


      // Create a File Info object so you can easily get the

      // name and extenstion. As an alternative you could

      // choose to pass them in,  or use some other way

      // to extract the extension and name.

      FileInfo fi = new FileInfo(fileName);




        // Open the file as a stream

        FileStream sourceFile = new FileStream(fileName

          , FileMode.OpenOrCreate, FileAccess.Read);


        // Create the row in the database



        SqlCommand cmd = new SqlCommand();

        cmd.Connection = sqlConnection;

        cmd.CommandText = "INSERT INTO "

          + "FileStreamFTS.dbo.DocumentRepository"

          + "(DocumentExtension, DocumentName) VALUES (‘"

          + fi.Extension + "’, ‘"

          + fi.Name + "’)";



        // Now upload the file. It must be done inside a transaction.

        transaction = sqlConnection.BeginTransaction("mainTranaction");

        cmd.Transaction = transaction;

        cmd.CommandText = "SELECT Document.PathName(), "


         + "FROM FileStreamFTS.dbo.DocumentRepository "

         + "WHERE ID=(select max(id) from FileStreamFTS.dbo.DocumentRepository)";

        SqlDataReader rdr = cmd.ExecuteReader();

        if (rdr.Read() == false)


          throw new Exception("Could not get file stream context");



        // Get the path

        string path = (string)rdr[0];

        // Get a file stream context

        byte[] context = (byte[])rdr[1];

        int length = context.Length;



        // Now use the API to get a reference (handle) to the filestream

        SafeFileHandle handle = OpenSqlFilestream(path



          , context, (UInt32)length, 0);


        // Now create a true .Net filestream to the database

        // using the handle we got in the step above

        FileStream dbStream = new FileStream(handle, FileAccess.Write);


        // Setup a buffer to hold the data we read from disk

        int blocksize = 1024 * 512;

        byte[] buffer = new byte[blocksize];


        // Read from file and write to DB

        int bytesRead = sourceFile.Read(buffer, 0, buffer.Length);

        while (bytesRead > 0)


          dbStream.Write(buffer, 0, buffer.Length);

          bytesRead = sourceFile.Read(buffer, 0, buffer.Length);



        // Done reading, close all of our streams and commit the file






      catch (Exception e)


        if (transaction != null)




        throw e;








First we open a connection to the SQL Server, then create a FileInfo object to make it simple to extract the file name and extension. Next a record is inserted into the database that will act as a place holder. It has the name of the file and the extension, but no file yet. I did go ahead and open a FileStream to the source file, located on the disk. We’ll need this later to upload the file.

Next you will see that I begin a transaction. Every time you work with a FILESTREAM it must always be in the context of a transaction. After that a SQL Data Reader is created that has three pieces of information. First, it calls the PathName() function for the Document field in our table. The PathName() will be needed later when we call the API. The second field is returned from the GET_FILESTREAM_TRANSACTION_CONTEXT function, and returns the transaction context for the transaction. Note this is not the name (in this example “mainTransaction”), but the context which is a special value. These two values are then copied into local variables which will be used in calling the OpenSqlFilestream API. In this example I also retrieve the DocumentName field, this is used by the code when it writes the file to the database, but is not strictly needed for the FILESTREAM.

Next you will see the call to the OpenSqlFilestream API, which returns a “handle”. This handle is then used to create a FileStream object. Using this newly created FileStream (here named dbStream) we can then upload the file. Now the main work begins. After setting up a buffer, we then simply read from the source file stream into the buffer, then write the exact same buffer to the database FileStream. The loop continues until there are no more bytes in the source.

At this point we are essentially done. We close the streams, commit the transaction, and in the finally block close the SQL database connection. The file should now be in the database. I do want to point out one thing. In the SQL to get the information to the row just uploaded, I use a subquery to get the max(id), essentially returning the last row just inserted. This is fine for this simple example, when the database has just one user. In your production systems where you are likely to have many users, however, you should use an alternate method to return the row you need. Otherwise two users could insert rows at the same time, and thus a conflict could occur with both of them getting back the same max(id). It will not happen often, but at some point it could happen and be very hard to debug.

This handled the uploading of files to the SQL Server via FILESTREAM, in the next installment we’ll look at how to retrieve the file we just uploaded.

Generating a PDF file from a Reporting Services Report Viewer Control

In yesterday’s post, I demonstrated how to generate a SQL Server Reporting Services report without having to have SQL Server Reporting Services. The sample application used the Microsoft Report Viewer control to display the report. A common need in business is to generate documents, such as PDFs, that will later be archived. It turns out if you are using a report viewer control, this is easy to do programmatically.

First, you need to add one using statement to the top of your class, in addition to the others that were added yesterday.

using System.IO;

Next, we only need a few lines of code to generate the PDF.

      Warning[] warnings;

      string[] streamids;

      string mimeType;

      string encoding;

      string extension;


      byte[] bytes = reportViewer1.LocalReport.Render(

        "PDF", null, out mimeType, out encoding, out extension,

        out streamids, out warnings);


      FileStream fs = new FileStream(@"D:\ReportOutput.pdf", FileMode.Create);

      fs.Write(bytes, 0, bytes.Length);


This code snippet came right from the MSDN Books on Line, and is pretty simple. I could have selected another format by changing the first value passed into the Render method, for example “EXCEL” would have rendered it as a Microsoft Excel document.

In the code samples I placed the above sample in it’s own button, but I could just have easily placed it under one of the other demo buttons.

This ability brings up some interesting possibilities. For example, the report viewer control does not have to be visible to the user in order for this to work. Thus you could create an application that every night generated a series of reports and saved them as PDFs to some central location, such as a web server or document control server. All the user (assuming one was around) would have to see is a progress bar, the reports themselves never get displayed.

Using SQL Server Reporting Services in Client Mode

Recently I did a presentation at the March BSDA meeting. I showed how to use SQL Server Reporting Services without a SQL Server, or more specifically a SQL Server running Reporting Services. It got an enthusiastic response so I thought I’d add to it here by adding some reminder documentation, as much for myself as for all of you wonderful readers.

Using Reporting Services in Client, or Local mode is a 4 step process. First, you will need an XSD schema file to create the report on. Once you have the XSD you will be able to move to the second step, creating the report. Third you will need to place a Report Viewer control on your windows form, WPF form, or ASP.NET page. Finally you will need to write some code that generates an ADO.NET dataset, loads the report in the report viewer control, then binds it all together. Lets look at this step by step.

Normally when you create a report you connect to a database, then base it off of some object like a query, view, or stored procedure. The report is then uploaded to a Reporting Services server, which takes care of hosting it, displaying it, and generating the data for it. With client mode you have no server available, so we have to instead create a surrogate. That’s where our XSD file comes in.

Right click in Solution Explorer and “Add a new item”, and from the list of goodies select “XML Schema”. Name it something appropriate, letting the default extension be XSD. For this example I will be getting customer order data, so I’ve given it the name CustomerOrders.xsd. Visual Studio will think about it then add it to the project, and even helpfully open it for you. I don’t know about you, but hand typing XML Schema’s isn’t my idea of fun, so you should glace at it, go “that’s nice” then close it.

Now right click on the XSD file in Solution Explorer, and pick “Open with….”. In the dialog that appears, select “Dataset Editor”. When you do, Visual Studio presents a big scary warning message letting you know that you could lose contents, and that this will forever be a dataset XSD file. We have nothing in the file, so we’re cool with this, just click OK.

You will now be presented with a big surface area. In the middle it tells you to drag items from the server explorer or right click. If you have a table, view, or stored procedure you are free to drag it in, but most of the time you’ll want to base this off of a SQL query. Right click on the surface, and select Add…., Table Adapter from the menu. The first screen asks you for the database connection. This is the only time you’ll actually need a connection, in this example I am using the good old Northwind database. I pointed at Northwind and clicked next.


Next we are asked how we are going to access the data. Since we have a SQL Statement just pick the default of “Use SQL statements” and click Next.


Now take your SQL Statement and paste it in, and click Next.


OK, click Finish to wrap up the addition of the XSD. By default the adapter has a generic name, we should give ours something more meaningful. Click in the top bar, then enter a new name. Since my example report is for customer order data, I’ll name it CustomerOrders. I then went to the bottom bar and renamed the TableAdapter1 to CustomerOrderTableAdapter. You should now see something like:


Note that this will become your Data Source for the report. The data source will have the name of the XSD followed by the name of the source, in this case it will read CustomerOrders_CutomerOrders. OK, now it’s time to create the report.

Go back to Solution Explorer, right click and pick Add New Item. Navigate to the Reporting area and pick Report Wizard. Note the file extension should end in RDLC. If you have used Reporting Services before, you will know that reports typically end in RDL. However, client mode reports have just a slightly different syntax to them, thus the RDLC extension to differentiate the two. While you can modify an RDL to become an RDLC and vice versa, you have to do so by hacking the XML behind the report.

Note you can also choose just Report, but then you’ll have to setup everything manually. For this simple example though, we’ll just use the Report Wizard.


Give your report a meaningful name and click Add. The report wizard then shows you a welcoming screen if you’ve never run it before, just click Next.

Now we need to pick the data source. In this example, you want the CustomerOrders branch, so select it and click Next.


The next screen asks if we want a Tabular or Matrix report. Select the one for you, in my example I picked Tabular and clicked Next. The next screen asks how we want to display the data. For my example, I opted to group by the customers company name and contact name, then the order data went into the details area. Fill out as appropriate for your report and click Next.


The next screen asks how we want things laid out. This affects the look and feel of the report. For my example I just took the default and clicked next, however you are free to play with this to experiment with the different looks and feels your reports might have.

Likewise the next screen is also a look and feel one, asking what colorings we want to apply. Pick one that makes you happy and click next. You can always change it later, many times I pick the Generic one (which adds no colors) then fix it up afterward.

The final screen is the wrap up. Give your report a meaningful name and click Finish.


OK, you have a report, now you need a container. Open up the user interface you want to place the report viewer control on. In my example I went with a very simple Windows Forms application.

In my toolbox, I navigated to the Reporting section, where I only found one control, the MicrosoftReportViewer control. (Note I am using Visual Studio 2008 SP1, if you are on an earlier version your names may differ slightly). Grab it and drop it onto your design surface. I also added a Button control to the form to kick off the report display process.


Now it’s time for the last step, adding some code. In this example I’ve used a Windows Form. Opening it, the first thing we find in the form load area is::


(Note I left my report viewer control named reportViewer1.) Delete it, we’ll have it refresh elsewhere.

Now we need to add some using statements to the top of our class.

//Add these to the standard list above

using System.Data.Sql;

using System.Data.SqlClient;

using Microsoft.Reporting.WinForms;

The first two will be used in accessing our Northwind database, you may need to use different libraries if you were going to another database. I’ve also included a referenced to the Reporting.WinForms library so we can manipulate the report programmatically.

Now let’s go to the code for the button click event. First, we need to reset the report viewer in case we’d been using it to host another report.

      // Reset in case report viewer was holding another reportViewer1


Next We need to set the report viewer to local mode. This tells it we’ll be supplying the report name from a local file, and binding the report to a local ADO.NET datasource.

      // Set the processing mode for the ReportViewer to Local

      reportViewer1.ProcessingMode = ProcessingMode.Local;

Our third step is to create a local report variable, and set it’s reference to the report viewer’s local report. This will make it easier to work with. Then we’ll set the location of the report we want to use.

      LocalReport localReport = reportViewer1.LocalReport;

      localReport.ReportPath = @"D:\Presentations\SQL Server\SSRS RDLC\SSRS_RDLC\Report2.rdlc";

Now we need to create an ADO.Net dataset, and populate it. I implemented most of that functionality in a method called GetCustomerOrders, which I’ll append at the bottom of these instructions. It’s very straight forward code.

      DataSet dataset = new DataSet("Northwind");


      // Get the sales order data

      GetCustomerOrders(ref dataset);

At this stage we have told it where our report is, and have created the dataset. Now we need to create a datasource for the report itself. We’ll use the ReportDataSource object. For the name, we’ll use the same name as the XSD schema, CustomerOrders_CustomerOrders. Then for the value we will give it the table from the dataset we created in code. It’s possible for a report to have multiple datasets, in the report we’d give each one it’s own name (based on the XSD) then here we’d bind the dataset table to the name we’d used in the report. Once done we will then add the new ReportDataSource to the local reports DataSources collection. Finally, we’ll referesh the report viewer to make it generate the report.

      // Create a report data source for the sales order data

      ReportDataSource dsCustomers = new ReportDataSource();

      dsCustomers.Name = "Customers_Customers";

      dsCustomers.Value = dataset.Tables["Customers"];




      // Refresh the report


You can download a copy of these instructions, along with the entire sample project including code and reports, at the Microsoft Code Gallery site . As promised, below is a copy of the GetCustomerOrders routine, for your reference.

    private void GetCustomerOrders(ref DataSet dsNorthwind)


      string sqlCustomerOrders = "SELECT c.[CustomerID]"

        + " ,c.[CompanyName]"

        + " ,c.[ContactName]"

        + " ,c.[ContactTitle]"

        + " ,c.[Address]"

        + " ,c.[City]"

        + " ,c.[Region]"

        + " ,c.[PostalCode]"

        + " ,c.[Country]"

        + " ,c.[Phone]"

        + " ,c.[Fax]"

        + " ,o.[OrderID]"

        + " ,o.[CustomerID]"

        + " ,o.[EmployeeID]"

        + " ,o.[OrderDate]"

        + " ,o.[RequiredDate]"

        + " ,o.[ShippedDate]"

        + " ,o.[ShipVia]"

        + " ,o.[Freight]"

        + " ,o.[ShipName]"

        + " ,o.[ShipAddress]"

        + " ,o.[ShipCity]"

        + " ,o.[ShipRegion]"

        + " ,o.[ShipPostalCode]"

        + " ,o.[ShipCountry]"

        + "  FROM [Northwind].[dbo].[Customers] c"

        + "  join [Northwind].[dbo].[Orders] o on c.CustomerID = o.CustomerID";


      SqlConnection connection = new

        SqlConnection("Data Source=(local); " +

                      "Initial Catalog=Northwind; " +

                      "Integrated Security=SSPI");


      SqlCommand command =

          new SqlCommand(sqlCustomerOrders, connection);


      SqlDataAdapter customerOrdersAdapter = new



      customerOrdersAdapter.Fill(dsNorthwind, "CustomerOrders");



Presenting Getting Started with SQL Server Compact Edition 3.5 at BUG.NET Meeting

Just wanted to let everyone know I’ll be doing a presentation this coming Tuesday night, August the 12th for the Birmingham .Net Users Group (BUG.NET). My topic, as you may have guessed from the title, will be using SQL Server Compact Edition.

While I will be using Visual Studio 2008, I will point out which pieces are 2005 compatible. I will also cover the use of both traditional coding techniques as well as how to use LinqToSQL to talk to the Compact Edition.

The meeting takes place at 6:30 pm at New Horizons Training Center in Homewood.

I also plan a new series of blog posts to start later this week on the subject, and will be creating a new Code Gallery site to hold my examples.

Also, don’t forget the regular BSDA meeting this coming Thursday night, the 14th. Also starting at 6:30 pm at New Horizons, Shannon Brooks-Hamilton, a software usability expert, will be there to talk about user interface design. Lots of good thought material on how we can make better UIs for our users.

SQL Server 2005 Full Text Searching at the Huntsville Alabama Code Camp

My third and final presentation for the Alabama Code Camp 6 is “Introduction to SQL Server Full Text Searching”. Here are the materials I’ll be using during the demo.

First, here is a PDF of the PowerPoint slides:

Full Text Search Power Points

Next, most of the demos used SQL statements. This PDF file has all of the SQL plus some associated notes.

Full Text Search Demo Scripts

Finally, I did a WPF project that demonstrated how to call a full text search query from a WPF Windows application. Annoyingly enough WordPress (who hosts my blog) won’t let me upload ZIP files, so I renamed the extension to pdf. After you download the file to your drive, remove the .pdf and put the zip extension back on, then it should expand all the source for you correctly. (Yes, I know, I really need to get a host server for binaries, one of these days I’ll get around to it, but for today…)

Source for WPF Demo

An Early Christmas from Redmond

They’re here! Today Microsoft released Visual Studio 2008 RTM. If you have an MSDN subscription you can download today and start producing all those new .Net 3.5 applications.

Also released today via the site is CTP 5 of SQL Server 2008. It’s my understanding in this CTP most of the features are working, except for clustering.

I can see I won’t be getting any sleep tonight.

Little Bobby Tables

I love this cartoon from xkcd, it really emphasizes why you need to screen your data inputs to protect against SQL Injection Attacks.

By the way, there’s a WebLog Awards going on right now, if you also enjoy xkcd give them a vote. Hurry though, voting ends November 8th.

Code Camp Samples

Tomorrow, Saturday October 6th I will be presenting “Getting Started with Full Text Searching”. Here are the materials I’ll be using during the demo.

First, here is a PDF of the PowerPoint slides:

Full Text Search Power Points

Next, most of the demos used SQL statements. This PDF file has all of the SQL plus some associated notes.

Full Text Search Demo Scripts

Finally, I did a WPF project that demonstrated how to call a full text search query from a WPF Windows application. Annoyingly enough WordPress (who hosts my blog) won’t let me upload ZIP files, so I renamed the extension to pdf. After you download the file to your drive, remove the .pdf and put the zip extension back on, then it should expand all the source for you correctly. (Yes, I know, I really need to get a host server for binaries, one of these days I’ll get around to it, but for tonight…)

Source for WPF Demo

Arcane Fun Fridays

WHEW! All of this WPF / XAML sure has been a lot of fun. But I think it’s time to come up for air and see what else is happing out there in Dot Net land.

Alabama Code Camp is coming up in just a little over a week, Saturday October 6th to be exact. Still plenty of time to register and even just a bit of time if you want to get in on the Silverlight programming contest. First prize for that is a Zune!

devLink, the large conference for a cheap price comes up right afterward in Nashville, Friday and Saturday October 12th and 13th. . You can tell I’ll be there, my name’s on the front page as a winner of a Barnes and Nobel gift card (look for the dude from AL !)

(By the way, anyone know of a good dog repellent? My nephew is coming to house sit and is bringing Marshmallow and Buttercup, his twin Dobermans along because I have a big back yard they can play in. Last time though they ate the garden hose, chewed the handle off my shovel, and bit through one of my lawnmower tires.)

There’s a new add-on for SQL Server Management Studio I’m eager to try out. It’s still in Beta but looks promising. It was blogged about at—an-add-in-for-SQL-Management-Studio.aspx or you can download it directly at .

If you are a fan of NUnit, you’ll appreciate the new xUnit. Read James’ announcement at .

In a recent Dot Net Rocks episode, Carl Franklin announced they would be taking over Shrinkster has been down due to spam abuse, as soon as Carl gets everything setup we’ll be able to go back to using short links again!

Speaking of Dot Net Rocks, I especially enjoyed show 274, where the new features of VB.Net and C# for the 2008 release were discussed. Entertaining and lots of good tidbits. I think my favorite feature so far has got to be C#’s extension methods.

During my long drive to the Tallahassee Code Camp last week, I put together a podcast theme session, and copied a bunch of related podcasts onto my cheapo SanDisk mp3 player. This time I went with a “Millenator” theme and got all the episodes of Dot Net Rocks that Mark Miller appeared on. Good stuff, lots of thoughtful material combined with some humor. Next time you go on a trip, copy a bunch of past episodes of your favorite podcast that are in the same theme and make that long drive go much quicker.

There have been several updates to the world’s greatest Visual Studio Add-In, CodeRush, over the last few weeks ( ). Apparently Mark Miller and the boys have been busy! If you’re not on 2.5.4 go update yours today.

Speaking of Mark Miller, I love his intro slide for his VSLive session coming up in LasVegas. Take a look, pure genius.

A final note, between getting ready for Alabama Code Camp and going to devLink my blogging may get spotty for the next few weeks, bear with me and I’ll have full reports from both code camps and lots of fun new stuff to share.

WPF Menus

The next control in the basic toolkit is the menu. Menus are much like a series of nested buttons in the way you deal with them. Let’s create a simple menu. I’ve added a DockPanel, so we could nest our menu at the top, a very common scenario.


    <Menu DockPanel.Dock=Top >

      <MenuItem Header=_File>

        <MenuItem Header=_Open />

        <MenuItem Header=_Save />

        <MenuItem Header=Save As… />

        <MenuItem Header=E_xit Click=mnuFileExit_Click />


      <MenuItem Header=_Edit>

        <MenuItem Header=_Cut />

        <MenuItem Header=C_opy />

        <MenuItem Header=_Paste />


      <MenuItem Header=_Help>

        <MenuItem Header=_About />

        <MenuItem Header=_Contents />

        <MenuItem Header=_Help />





The underscore acts as an indicator to underline in the menu, designating a “hot key”. When the user hits the ALT key, they are then able to combine with the hot key to activate the menu option. ALT+F opens the File menu, O will then trigger the open.

Responding to the users click is just like working with many other controls, simply add a Click=”” in the MenuItem. Above you will notice I did this for one item, the Exit menu choice under File. Here’s the code I created in C#:

    void mnuFileExit_Click(object sender, RoutedEventArgs e)




Since all the other menus work the same way I won’t bother to wire them up, but you get the idea.

It’s also possible to insert a separator, a visual line the separates one menu option from the rest. In this example, I think Exit should be separated from the other options.

  <!– …snip… –>

  <MenuItem Header=_File>

    <MenuItem Header=_Open />

    <MenuItem Header=_Save />

    <MenuItem Header=Save As… />


    <MenuItem Header=E_xit Click=mnuFileExit_Click />


  <!– …snip… –>

And now we have a nice clean line across the menu:


OK, I can hear you now, “Hey, this is WPF, what about all the pretty pictures?” No problem, the menus support the addition of icons to each item.

One thing I’ve found many developers don’t realize is that Visual Studio actually ships with a nice array of graphics for your use. If you are using Visual Studio 2005, navigate to the C:\Program Files\Microsoft Visual Studio 8\Common7\VS2005ImageLibrary folder. There you will find a zip file named

I extracted the icons folders to my D drive, as you’ll notice in the next code snippet. Adjust the sample according to where you decide to put them. I did find that on Vista, I needed to move them out of my Program Files folder or they didn’t render correctly, a security permissions issue.

      <MenuItem Header=_File>

        <MenuItem Header=_Open >


            <Image Height=16 Width=16

              Source=D:\Icons\WinXP\folderopen.ico />



        <MenuItem Header=_Save >


            <Image Height=16 Width=16

              Source=D:\Icons\Win9x\DISK06.ico />



        <MenuItem Header=Save As… />


        <MenuItem Header=E_xit Click=mnuFileExit_Click />


For the MenuItems I wanted to add icons to, I need to define the MenuItem.Icon tag, then inside place an Image. The Source property I set to files I’d mentioned earlier. Note also I explicitly set the Height and Width to 16×16, in order to make them fit nicely into the menu. However, you are free to make them any size you wish, the menu item height will adjust automatically to compensate.


Ah, a work of art even Leonardo da Vinci would be proud of. But our artistic menus are not limited to the the top of the Window. It’s also possible to attach a menu to nearly any control, in the form of a ContextMenu. ContextMenus appear when you Right Click with the mouse on the control.



    <!–Menu omittied for brevity, same as previous–>











      <TextBlock Grid.Row=0



        Enter some text, right click for menu options.


      <TextBox Grid.Row=1 Grid.Column=0>



            <MenuItem Header=_Cut />

            <MenuItem Header=C_opy />

            <MenuItem Header=_Paste />


            <MenuItem Header=_Save >


                <Image Height=16 Width=16

                  Source=D:\Icons\Win9x\DISK06.ico />








I’m adding a Grid to the body of the DockPanel. In the Grid I’m putting a TextBlock with some instructions, then a TextBox. Try right clicking on the TextBox, and take a look at what you get:


To get this to work, I first had to create a ContextMenu tag specific for the control, in this case TextBox.ContextMenu. Within that tag I was then able to place my ContextMenu. Inside it I treated it just like a normal Menu control, the code in there I simply copied from the other menus. I could even have the Click events route to the same code in the code behind module if I wished.

It’s worth pointing out that a menu may appear anywhere on the Window. Here I will create a menu to the right of the TextBox, in the next column of the Grid.


    <Menu DockPanel.Dock=Top >

      <!–Omitted, see previous–>



      <!–Omitted, see previous–>

      <TextBox Grid.Row=1 Grid.Column=0>

        <!–Omitted, see previous–>


      <Menu Grid.Row=1 Grid.Column=1>

        <MenuItem Header=Load>

          <MenuItem Header=From File></MenuItem>

          <MenuItem Header=From Database></MenuItem>


        <MenuItem Header=Reset>

          <MenuItem Header=This Item />

          <MenuItem Header=Entire Form />






The menu code is the same as before, except I located in the Grid instead of at the top of the Window.

Virtually any application of decent size will need to employ a menu structure of some kind. Getting to know the basics of menu controls will get you ready for that first big WPF app.

WPF Tab Control

Continuing the series on visual grouping controls in WPF, the Tab control is a common UI element that has been around for some time. It makes a convenient way to organize your window when there is more than could realistically fit and still be comprehensible.

In WPF, Tabs are very easy to implement. Create a new WPF Window, remove the default Grid tags, and add the following XAML:  


    <TabItem Header=Tab 1>Here’s Tab 1</TabItem>

    <TabItem Header=2nd Tab>A second Tab</TabItem>


Run the app, and you’ll see the two tabs on a window:


The TabItem, just like most controls, can hold a container control that can hold much more, thus making the tab really useful. In this example, let’s add a third tab item, with a grid. We’ll put in a few text boxes and a button.

  <TabControl Name=tabMyTabs >

    <TabItem Header=Tab 1>Here’s Tab 1</TabItem>

    <TabItem Header=2nd Tab>A second Tab</TabItem>

    <TabItem Header=Cool Tab>








          <ColumnDefinition Width=*></ColumnDefinition>

          <ColumnDefinition Width=2*></ColumnDefinition>


        <Label Grid.Column=0 Grid.Row=0


          First Name:


        <Label Grid.Column=0 Grid.Row=1


          Last Name:


        <TextBox Name=FirstName


                Grid.Row=0 />

        <TextBox Name=LastName


                Grid.Row=1 />

        <Button  Grid.Column=1


                Height=23 Width=75










Using techniques already described in my post on Grids ( , in case you missed it) I was able to create a tab with some useful content. Running it, you can see the results for yourself:


It’s also possible to determine the current tab via code. Let’s wire up a click event to that OK button you see. This requires we name the tab (I called it tabMyTabs), and the button, and add a Click=”CoolTabButton_Click” to the button declaration. Now all we have to do is add a little code:

    private void CoolTabButton_Click(object sender, RoutedEventArgs e)


      TabItem ti = tabMyTabs.SelectedItem as TabItem;

      MessageBox.Show(“Tab Index=” + ti.Header);



Running and clicking on the OK button will result in a message box with the words “Tab Index=Cool Tab”

Granted my design won’t win any awards, but it gives you the general idea of how to use a tab control in WPF.