Shawn Weisfeld [MVP]

Using ROW_NUMBER() to eliminate the multiplicative effects of a join

Problem:

It is a common situation to have a header/detail tables in SQL Server. Additionally sometimes you include a calculated value on the header table to make reporting easier. For Example lets say you had an SalesPerson table that had a column for total sales. Now you also have a SalesDetails table that lists every sale that the company made. The purpose of having the total sales column is that it eliminates the need to touch the larger SalesDetails table when you just need to get the total by sales person. This is a good thing until the user asks for a report that has a column for both the total sales of the sales person and a line for each of the SalesDetails. Maybe a picture will help explain. 

So here is the setup. As you can see George has sold 5 items for $100 each for a total of $500.

Now when we are asked to produce the report that joins these two together we get the following result:

Now if someone who gets this report sums up the total sales column they get $5,000, the wrong answer. What we need to do is zero out the total sales on each row except for the first row. So our results will look something like this.

Now the user of this report will get the same answer if they sum up the total sales column and sale amount column. So now we know what we want how do we get there in SQL Server.

Well if we could number the SalesPerson Records by EmplID, then we would have the ability to zero out the records on all rows except for the first one. For example something like this. . .

Solution:

ROW_NUMBER() to the rescue. MSDN defines ROW_NUMBER() as “Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.” (http://msdn.microsoft.com/en-us/library/ms186734.aspx).

Here is my starting point, a standard join to put the two tables together.

As you can see the ROW_NUMBER function takes 2 arguments. The second is the ORDER BY, that argument tells ROW_NUMBER what order we want the results in. In this application this is irrelevant since each SalesPerson record will be the same. The first argument PARTITION BY tells SQL Server to restart the count every time it sees a change in a column or set of columns. In our case we want to restart the count for each SalesPerson.

So the next step is to zero out the TotalSales on all the rows except for the first one.

Yes! Now our users can sum up the TotalSales column or the SaleAmt column and get the same answer! Another happy report user = another happy developer!

Extension Method to Resize an Image

I wrote this for an INETA project that I have been working on, but thought it would be great to share with everyone. We had a need to take an image and change its size. Below is an implementation of a Resize Extension Method on the .NET Image object.

 I borrowed some ideas from this post by Mark McDonnell (http://weblogs.asp.net/markmcdonnell/archive/2008/03/09/resize-image-before-uploading-to-server.aspx)

   1:  using System;

   2:  using System.Collections.Generic;

   3:  using System.Linq;

   4:  using System.Text;

   5:  using System.Drawing;

   6:  using System.Drawing.Drawing2D;

   7:   

   8:  namespace Demo

   9:  {

  10:      public enum ResizeMode

  11:      { 

  12:          Width,

  13:          Height,

  14:          Bigger

  15:      }

  16:   

  17:      public static class ImageHelper

  18:      {

  19:          public static Image Resize(this Image source, int size, ResizeMode mode)

  20:          {

  21:              //Convert the source image into a bitmap so we can work with it

  22:              Bitmap originalBMP = new Bitmap(source);

  23:              Image result;

  24:   

  25:              // Calculate the new image dimensions

  26:              int origWidth = originalBMP.Width;

  27:              int origHeight = originalBMP.Height;

  28:              int newWidth = 0;

  29:              int newHeight = 0;

  30:   

  31:              //If the size mode is set to bigger then find the widest side

  32:              if (mode == ResizeMode.Bigger)

  33:              {

  34:                  if (origHeight > origWidth)

  35:                      mode = ResizeMode.Height;

  36:                  else

  37:                      mode = ResizeMode.Width;

  38:              }

  39:   

  40:              //keeping the aspect ratio constant, calculate the new size

  41:              if (mode == ResizeMode.Width)

  42:              {

  43:                  newWidth = size;

  44:                  newHeight = (int)(size / ((double)origWidth / (double)origHeight));

  45:              }

  46:              else

  47:              {

  48:                  newHeight = size;

  49:                  newWidth = (int)(size / ((double)origHeight / (double)origWidth));

  50:              }

  51:   

  52:   

  53:              // Create a new bitmap which will hold the previous resized bitmap

  54:              Bitmap newBMP = new Bitmap(originalBMP, newWidth, newHeight);

  55:   

  56:              // Create a graphic based on the new bitmap

  57:              using (Graphics oGraphics = Graphics.FromImage(newBMP))

  58:              {

  59:                  // Set the properties for the new graphic file

  60:                  oGraphics.SmoothingMode = SmoothingMode.AntiAlias;

  61:                  oGraphics.InterpolationMode = InterpolationMode.HighQualityBicubic;

  62:   

  63:                  // Draw the new graphic based on the resized bitmap

  64:                  oGraphics.DrawImage(originalBMP, 0, 0, newWidth, newHeight);

  65:   

  66:                  // Save the new graphic file to the server

  67:                  result = newBMP.Clone() as Image;

  68:   

  69:                  // Once finished with the bitmap objects, we deallocate them.

  70:                  originalBMP.Dispose();

  71:                  newBMP.Dispose();

  72:                  oGraphics.Dispose();

  73:              }

  74:   

  75:              return result;

  76:          }

  77:      }

  78:  }

Using an extension method makes it really easy to use the new “resize” functionality:

   1:              Image img = Image.FromFile("img.jpg");

   2:              Image resized = img.Resize(100, ResizeMode.Bigger);

   3:              resized.Save("resized.jpg");

So we went from a source size of 2304x1728 and 782 KB

to 100x75 and 23.3 KB

Now you to can shrink the size of your memories. :)

Perspectives on Technology from Tech Ed North America – Shawn Weisfeld and Allen White

Check out this recording I did for the folks at the MVP Program at TechEd 2009

http://blogs.msdn.com/mvpawardprogram/archive/2009/06/19/perspectives-on-technology-from-tech-ed-north-america-shawn-weisfeld-and-allen-white.aspx

MVP Shawn Weisfeld_Visual C#_MV_Andy Malone_Setup & Deployment

MVP Shawn Weisfeld_Visual C#_MVP_Allen White_SQL Server

Get an Email from TFS on Check-in

Want to get emails every time anything is checked into TFS? Open VS.NET, find the “Team” Menu and the “Project Alerts . . .” Option.

Once there you can select what you want alerts by and enter your email address.

Silverlight & no 64 bit

When attempting to use a Silverlight application in the 64-bit version of IE you will get this message. “Microsoft Silverlight cannot be used in browsers running in 64 bit mode.”

At first I was disappointed because I would like to see more of the industry move to 64 bit. Heck they are selling it at Best Buy now. However does Silverlight really need 64 bit? 64 bit gives you more throughput on the processor and more access to memory. But it is defiantly not typical for Silverlight applications to need that much memory and/or do highly processor intensive stuff. My guess MSFT had to make a choice between 64 bit and additional features in the runtime, like them I would have put my money in the additional features.

The Gu gave an interview with Ars Technica where he talks about the plans that Microsoft has.

Query SharePoint with .NET

I was attending the SharePoint Developers Group Meeting (http://www.dspdg.org) last week and the presenter, Ty Anderson, of http://www.cogentcompany.com & http://www.officedeveloper.com demoed using MS Access as a shim to bridge the communication between a VSTO application and a SharePoint List. I thought the idea was cool so I threw together this quick sample for your consideration. Note: I have not tested this in a multi-user environment, nor would I recommend this when performance is a high priority. Regardless it is very cool!

Step 1: Create your list in SharePoint: Here you can see my TestList, it has one column called title.

Step 2: Create the MS Access shim. Click Actions then “Open with Access”.

This will create an “.accdb” file.

Step 3: Write .NET code to query the MS Access database

First thing I will need is a connection string, in my app.config file:

   1:  <connectionStrings>

   2:  <add name="ConnectionKey"

   3:       connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=TestList.accdb;"

   4:       providerName="System.Data.OleDb"

   5:       />

   6:  </connectionStrings>

Now some simple DataAccess code to query the database:

   1:  DataSet ds = new DataSet();

   2:   

   3:  //get the config info from the app.config file

   4:  ConnectionStringSettings myConnectionSettings = ConfigurationManager.ConnectionStrings["ConnectionKey"];

   5:  DbProviderFactory myProvider = DbProviderFactories.GetFactory(myConnectionSettings.ProviderName);

   6:   

   7:  using (DbConnection myConnection = myProvider.CreateConnection())

   8:  using (DbCommand myCommand = myProvider.CreateCommand())

   9:  using (DbDataAdapter da = myProvider.CreateDataAdapter())

  10:  {

  11:       myConnection.ConnectionString = myConnectionSettings.ConnectionString;

  12:       myCommand.Connection = myConnection;

  13:       myCommand.CommandType = System.Data.CommandType.Text;

  14:       myCommand.CommandText = "SELECT * FROM [TestList]";

  15:       da.SelectCommand = myCommand;

  16:       da.Fill(ds);

  17:  }

  18:  ds.WriteXml("results.xml");

I can even Insert New records into the list:

   1:  using (DbConnection myConnection = myProvider.CreateConnection())

   2:  using (DbCommand myCommand = myProvider.CreateCommand())

   3:  {

   4:       myConnection.ConnectionString = myConnectionSettings.ConnectionString;

   5:       myCommand.Connection = myConnection;

   6:       myCommand.CommandType = System.Data.CommandType.Text;

   7:       myCommand.CommandText = "INSERT INTO [TestList] (Title) VALUES ('This is a test')";

   8:       myCommand.Connection.Open();

   9:       myCommand.ExecuteNonQuery();

  10:  }

Moreover, I can even do Updates:

   1:  using (DbConnection myConnection = myProvider.CreateConnection())

   2:  using (DbCommand myCommand = myProvider.CreateCommand())

   3:  {

   4:       myConnection.ConnectionString = myConnectionSettings.ConnectionString;

   5:       myCommand.Connection = myConnection;

   6:       myCommand.CommandType = System.Data.CommandType.Text;

   7:       myCommand.CommandText = "UPDATE [TestList] SET Title = 'This is a update test' WHERE Title = 'This is a test'";

   8:       myCommand.Connection.Open();

   9:       myCommand.ExecuteNonQuery();

  10:  }

and I can even do Deletes:

   1:  using (DbConnection myConnection = myProvider.CreateConnection())

   2:  using (DbCommand myCommand = myProvider.CreateCommand())

   3:  {

   4:       myConnection.ConnectionString = myConnectionSettings.ConnectionString;

   5:       myCommand.Connection = myConnection;

   6:       myCommand.CommandType = System.Data.CommandType.Text;

   7:       myCommand.CommandText = "DELETE FROM [TestList] WHERE Title = 'This is a update test'";

   8:       myCommand.Connection.Open();

   9:       myCommand.ExecuteNonQuery();

  10:  }

That is it, now your .NET code can work with SharePoint data, and best of all NO CAML to write!!!

Property Causing a Stack Dump

Got a call today, someone was getting a stack dump every time they tried to assign a value to a property. Here is a screen print of the problem they were having. can you spot the problem?

If you said that the property is calling itself you get a gold star.  What was happening is that every time we tried to set the Name property, it calls the Name property to set it, and that called the Name property, and that called the Name property, over and over again, till .NET stack dumped. We are missing a backing variable.

By backing the property with a private variable, it removes the infinite recursion. Since the variable itself is private nobody can see it, or can use it outside the class, they are forced to use the property, and all is right with the world again! 

Populating a Generic List in VB.NET

Got a question on how to populate a generic list in VB.NET, and while I am a C# guy, I figured I would show some VB.NET love.

For this example I will be using a customer class, he is real simple just 2 properties. One for first name and one for last name.

   1:  Public Class Customer

   2:   

   3:      Private _first_name As String

   4:      Public Property FirstName() As String

   5:          Get

   6:              Return _first_name

   7:          End Get

   8:          Set(ByVal value As String)

   9:              _first_name = value

  10:          End Set

  11:      End Property

  12:   

  13:      Private _last_name As String

  14:      Public Property LastName() As String

  15:          Get

  16:              Return _last_name

  17:          End Get

  18:          Set(ByVal value As String)

  19:              _last_name = value

  20:          End Set

  21:      End Property

  22:   

  23:  End Class

Now that I have a class I can new up a list of that type:

   1:  Dim customers As New List(Of Customer)

Now I can create an object of type customer set its properties and add it to the list:

   1:          Dim c As New Customer

   2:          c.FirstName = "Shawn"

   3:          c.LastName = "Weisfeld"

   4:          customers.Add(c)

That is a bit tedious, lets simplify using the with statement:

   1:          Dim c2 As New Customer

   2:          With c2

   3:              .FirstName = "Shawn"

   4:              .LastName = "Weisfeld"

   5:          End With

   6:          customers.Add(c2)

Still too long, how about we create a constructor by adding the following code to our customer object: (NOTE: that I create 2 constructors, the first one that has no parameters is created by us automatically by VB.NET, however when we specify our own constructor VB.NET doesn’t give us the default one for free, so we have to code him up, assuming we wanted to have a parameter-less constructor)

   1:      Public Sub New()

   2:   

   3:      End Sub

   4:   

   5:      Public Sub New(ByVal first_name As String, ByVal last_name As String)

   6:          Me.FirstName = first_name

   7:          Me.LastName = last_name

   8:      End Sub

Now that I have the constructor I can now new up the customer and add him to my list in one line of code:

   1:          customers.Add(New Customer("Shawn", "Weisfeld"))

   1:          Dim c3 = New Customer() With {.FirstName = "Shawn", .LastName = "Weisfeld"}

   2:          customers.Add(c3)

That is better and we can even get it to a 1 line-er:

   1:          customers.Add(New Customer() With {.FirstName = "Shawn", .LastName = "Weisfeld"})

Now your objects can go forth an multiply!

SQL CLR: Query the file system to get a list of folders

Today was a good day for questions, got one asking how to query the file system from sql server. See they have a list of network shares that they want to find out what folders are in each. SQL CLR to the rescue. . .

First lets write a Table Valued Function to get a list of folders. I chose a Table Valued Function so I can join it to a list of paths to search in sql server. Remember that since we are leaving the confines of the SQL Server to get data from the disk we need to elevate our permission, we are also impersonating a domain account as they have access to external resources.

here is the code for the Table Valued Function:

   1:  using System;

   2:  using System.Data;

   3:  using System.Data.SqlClient;

   4:  using System.Data.SqlTypes;

   5:  using Microsoft.SqlServer.Server;

   6:  using System.Collections;

   7:  using System.Collections.Generic;

   8:  using System.Security.Principal;

   9:   

  10:  public partial class UserDefinedFunctions

  11:  {

  12:      /// <summary>

  13:      /// Get a list of all the folders in a given path, for a given number of levels deep

  14:      /// </summary>

  15:      /// <param name="path"></param>

  16:      /// <param name="levels"></param>

  17:      /// <returns></returns>

  18:      [SqlFunction(FillRowMethodName = "FillRow", TableDefinition = "directory nvarchar(4000)", DataAccess=DataAccessKind.Read)]

  19:      public static IEnumerable Folders(String path, int levels)

  20:      {

  21:          WindowsImpersonationContext impersonatedIdentity = null;

  22:          List<string> results = new List<string>();

  23:   

  24:          try

  25:          {

  26:              //impersonate the user that is executing me in sql server

  27:              impersonatedIdentity = SqlContext.WindowsIdentity.Impersonate();

  28:   

  29:              //Get the data

  30:              GetDirectories(results, path, levels);

  31:          }

  32:          finally

  33:          {

  34:              //undo the impersonation

  35:              if (impersonatedIdentity != null)

  36:              {

  37:                  impersonatedIdentity.Undo();

  38:              }

  39:          }

  40:   

  41:          return results;

  42:      }

  43:   

  44:      /// <summary>

  45:      /// Recursivly dig through the file system getting data

  46:      /// </summary>

  47:      /// <param name="results">List of the results</param>

  48:      /// <param name="path">Current Path we are looking into</param>

  49:      /// <param name="levels">Number of levels left to dig down</param>

  50:      private static void GetDirectories(List<string> results, string path, int levels)

  51:      {

  52:          //are we at the bottom of the query

  53:          if (levels > 0)

  54:          {

  55:              //append on the slash if we dont have it

  56:              if (!path.EndsWith("\\"))

  57:              {

  58:                  path = path + "\\";

  59:              }

  60:   

  61:              try

  62:              {

  63:                  //get a list of all the folders under the current path

  64:                  foreach (string folder in System.IO.Directory.GetDirectories(path, "*", System.IO.SearchOption.TopDirectoryOnly))

  65:                  {

  66:                      //add each to the results, then dig down one more level

  67:                      results.Add(folder);

  68:                      GetDirectories(results, folder, levels - 1);

  69:                  }

  70:              }

  71:              catch (UnauthorizedAccessException ex)

  72:              {

  73:                  //If we dont have access to a folder, just report that back in the result set

  74:                  results.Add(ex.Message);

  75:              }

  76:          }

  77:      }

  78:   

  79:      /// <summary>

  80:      /// convert one of our Enumerable objects into a data row

  81:      /// </summary>

  82:      /// <param name="obj"></param>

  83:      /// <param name="directory"></param>

  84:      public static void FillRow(Object obj, out SqlChars directory)

  85:      {

  86:          directory = new SqlChars(obj.ToString());

  87:      }

  88:  };

I created a table (SearchPaths) that contains a list of all the paths that I want to search:

Now I can just join the list of paths to search with the function and I have everything I was looking for:

If you are getting security exceptions and the like take a look at the slides from the CLR talk I gave a PASS Summit 2008, they should get you moving in the right direction.

http://drowningintechnicaldebt.com/blogs/shawnweisfeld/archive/2008/11/23/sql-pass-2008-talk.aspx

SQL Server: Who am I?

Got a question today. How do I tell what user my sql server query is running as? Well you just ask.

Using the USER_NAME() function, without passing it an id, finds the name of the current user.

http://msdn.microsoft.com/en-us/library/ms188014.aspx

Using the SUSER_NAME() function, gets me the login identification name of the user.

http://msdn.microsoft.com/en-us/library/ms187934.aspx

Here is an example of each, using both a regular sql server login (left) and windows authentication (right)

Now that I know who I am, why not be someone else. . . In this example I change the context that I am running under with the “EXECUTE AS LOGIN” statement, then I revert back using . . . wait for it. . . the “REVERT” statement.

EXECUTE AS: http://msdn.microsoft.com/en-us/library/ms181362.aspx

REVERT: http://msdn.microsoft.com/en-us/library/ms178632.aspx

and they say getting to know ones self is hard. . .

SQL Server 2008 Access is Denied Error During Install

Problem: when installing SQL Server 2008 you get an Access is Denied Error.

Here is the error you get during install. . . .

Fix: Check your Local Security Policy, you need to ensure that Administrators have “Debug programs” permission (Security Settings | Local Policies | User Rights Assignment)

More information can be found in the following articles:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=390424 (If you have had this error please follow this link and vote that it gets fixed in connect)

http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/ed4514fa-8d1d-4383-bbdf-fb06bfbad106/

Austin Code Camp 2009

Coming from Florida where I attended every code camp and spoke at every code camp except for one, well until I moved it is nice to be getting into the swing of things out here in Texas. Today I will be giving my ASP.NET Dynamic Data talk at the Austin Code Camp and I cannot wait. I will be repeating the presentation at the Dallas Tech Fest on June 19 if you cannot make it today.

Austin Code Camp: http://www.adnug.org/AustinCodeCamp09

Presentation Download

Dallas Tech Fest: http://dallastechfest.com/ (while the early bird discount is over, you can still use the discount code DOTNET to get $25 off admission)

VIPRE from Sunbelt Software

UPDATED:

While I was at Tech Ed I was eating breakfast with a salesman from Sunbelt. Yea I know what you are thinking another salesman, but this guy wasn’t pushy, actually I did not even get his name, but long story short he told me about their product, VIPRE. There are a couple of cool things about there product. 1) it is supposed to be very fast, that is good in an antivirus. 2) it works on Server OS’s (like win 2k3 and win 2k8), and 3) they have an unlimited site license for home users. Since I run many Virtual Servers in my house this was the perfect option for me (i.e. $50 to get antivirus for all my computers).

Only time will tell if it will work as advertised, I will be sure to report back if I have any problems. . . . 

One downside is that they don't say if you can use VIPRE on Win 7. . . . (I am emailing their sales department to find out.)

I spoke to the team at VIPRE and they said that they are working on Win 7 compatibility and they should be ready when Win 7 goes to RTM.

Check them out at http://www.sunbeltsoftware.com/home-home-office/vipre/

BTW this doesn’t replace how much I like OneCare (http://onecare.live.com) for the typical home user, however that doesn't work on Server OS-es and only supports up to 3 computers.

Couldn't attend MIX 09?

We are bringing MIX to the DFW area, but instead of repeating the MIX conference, we are boiling down the best of MIX into a 1-day hands on lab. This community event, called MIX Notes, will provide hands-on examples presented by industry leaders.

Computers will be provided, but participants are welcome to bring their own laptops.

Where:                                 Tekfocus (in the InfoMart)
When:                                  June 20, 9 AM - 4 PM
Registration Fee:   $25

* Lunch will be provided

Sign up today at http://www.mixnotes.net

Are you Coming to the Dallas Tech Fest 2009?

I will be speaking at the Dallas Tech Fest, but that should not prevent you from coming. . .