Just geeks

SQL Server Resources

2012-01-19T15:24:00.001-07:00

Improving SQL Server Performance

This is just one chapter an online book called Improving .NET Application Performance and Scalability.

Locking and Row Versioning

Pay special attention to the links on the left navigation for information on these topics

The Curse and Blessing of Dynamic SQL

Very in-depth article on issues associated with Dynamic SQL including escaping quotes.

Arrays and Lists in SQL Server 2008

Very in-depth article on how to work with Arrays and Lists (or the lack thereof). It gives solutions for the different versions of SQL Server.

Using a Table Valued Function in a JOIN

Another solution for parsing delimited string. See the above link Arrays and Lists in SQL Server 2008 for more solutions.

Reorganizing and Rebuilding Indexes

SQL Server script to rebuild all indexes for all tables and all databases

This is a very handy script to rebuild all your indexes for a list of databases. I call this after a I import a bunch of data into tables. I also call sp_updatestats AFTER to update stats on the non-indexed columns. Or force all stats on individual tables (including indexes, columns, etc) to be updated by using UPDATE STATISTICS dbo.MyTableHere WITH FULLSCAN. Since the only time data changes when I import it, I have turned off automatic stats update at the database level so that the query optimizer doesn’t have to make sure the stats are up to date. BTW, rebuilding indexes and then calling sp_updatestats didn’t always update all the stats if it thought they didn’t need to be updated. I prefer to force the update after the import.

SQL Server 2008 R2 Books Online (from Microsoft)

This is an excellent resource for lots of topics on SQL Server.

Optimizing Bulk Import Performance

Check if application is running via command line (in Windows)

2012-01-10T13:47:00.001-07:00

You are probably familiar with Task Manager in Windows. It is very useful for seeing what processes are running. Wouldn’t it be great if there was a command line tool that did the same thing. As it turns out, there is one that is built into Windows. It is called tasklist. It is very easy to use.

In its simplest usage just type tasklist at the command prompt. You will get a list very much like the Processes tab in Task Manager.

The cool thing is it has filtering built in. For example, if you just wanted to see the notepad processes you could do something like: tasklist /FI "IMAGENAME eq notepad.exe"

Type tasklist /? to get a details on the other options you have. There are lots of them. You can specify a remote system, list all tasks currently using a exe or dll, display services hosted in each process, format output as a table, list, or csv, use other filters, turn off headers, etc.

Very nice little tool to have.

Zabbix Server Tips

2012-01-10T09:30:00.001-07:00

Basic installation information including default passwords are located here.

To connect to the Zabbix server I use putty.exe. This works well. The default username and password are root/zabbix.

Topics

Zabbix Server is down

The Zabbix Dashboard web page shows the status of things. In particular sometimes I see (under the section called Status of Zabbix) that the Parameter Zabbix server is running is No instead of Yes. This means that Zabbix is essentially down. This can be confusing since the Zabbix web page is showing the Dashboard. The important thing to understand is Zabbix has many components. For instance, the Dashboard is actually just a web application that reads data from a database. Since we are able to see the Dashboard that means that the database (MySQL) and the web server (Apache) are both still up and working. However, since the Zabbix server itself is not running according to the Dashboard you should also be able to see under Monitoring tab | Latest data that data has not been collected for a while (since Zabbix server went down). You can also confirm that the Zabbix server is not running by using putty.exe to go to the console and verify that the Zabbix server is not running using something similar to ps -e | grep "zabbix_server". You can always start it manually by executing the Zabbix server /usr/sbin/zabbix_server, but a better plan would be to use service zabbix_server start. Alternatively, you could bounce the box as a last resort using something like shutdown –r now.

Checking Log Files

The first step is to locate the log files. The best way to do that is probably go to the /etc/zabbix/zabbix_server.conf configuration file to see where it is. You can do that using less /etc/zabbix/zabbix_server.conf and then type / and then LogFile and then enter to highlight the file. You will likely see that the LogFile variable is set to /var/log/zabbix/zabbix_server.log. Type q to exit less. Now go to the /var/log/zabbix directory. Here you will see a server_problems.log and zabbix_server.log. Open these log files and see what you can see using less or something similar.

What ports is zabbix listening on

netstat -ntpl | grep 'zabbix'

Check built in items

From the machine you are monitoring, you can use the Zabbix agent to test a item by using the syntax zabbix_agentd.exe -t proc.num[notepad.exe]. In this example I am using the proc.num to determine how processes of notepad.exe are running, but you could replace proc.num with any of the built in items. You will get the result immediately on the command line. In this case I got [u|1] which means 1 instance of notepad.exe is running. I then add a new item for that server using the Zabbix web configuration screens. No client (the machine you are monitoring) side configuration needed.

Checking how many instances of an application are running

This can all be done using the Zabbix configuration screens. All you have to do is navigate to the host (or template if you want to perform this check on multiple machines) you want to check and add an item. Click the Items link and then the Create Item button on the top right. Now fill in the form for the new item. In particular, the key you select is the important piece. You need to select proc.num and then edit the key value so that it is proc.num[notepad.exe] if you are monitoring the number of instances of notepad.exe. You will need to change it as desired to you process name (use Task Manager’s process tab to view these).

Documentation of all the built in items

http://www.zabbix.com/documentation/1.8/manual/config/items

Adding Time Zone Support to Dynamic Data

2012-01-05T10:35:00.001-07:00

I love the ASP.NET Dynamic Data architecture. It really makes developing applications so much easier. It is quite easy to extend once you get used to how it all works. I always store my dates/times as UTC in the database. This means by default that is what users will see when the Dynamic Data application displays the date/time. Most people don’t think about UTC, so it is of little meaning to most people. In an ideal world all date/time fields would be displayed in the current users time zone of choice.

This can be done quite easily actually when you are using Dynamic Data. The reason is that all the date/time fields are displayed and edited using a common user controls that you have in your project. In particular if you open your ASP.NET Dynamic Data web site / project you will see there is a DynamicData directory and then a FieldTemplates directory. Here you will find two user controls: DateTime.ascx and DateTime_Edit. These are the controls that are used to display and edit respectively all data/time data in your DynamicData application. So, all we have to do is convert UTC to desired timezone before it is displayed, and on save we need to convert desired timezone to UTC. That way the user will see the date/time in a time zone they understand, but the date/time is stored in UTC in the database.

Enhancing DateTime.ascx

The key to extending the DateTime.ascx control is knowing where to hook into the architecture of Dynamic Data Field Templates. The answer for this control is to override the FieldValue property.

 public override object FieldValue
        {
            get
            {
                var dateUI =  Convert.ToDateTime(base.FieldValue).GetDateTimeForUI();
                return dateUI;
            }
            set
            {
                base.FieldValue = value;
            }
        }

That is all that is required. I’ll show the meat of the GetDateTimeForUI() extension below. Alternatively, you could do your own time zone conversion here. The key is that this is where you convert the base.FieldValue (which is UTC) to the time zone you desire.

Enhancing DateTime_Edit.ascx

To extending the DateTime_Edit.ascx control the key is again knowing where to hook into the architecture of Dynamic Data Field Templates. The answer for this control is to override the DataBind() method and the ConvertEditedValue() method.

public override void DataBind()
{
    base.DataBind();
 
    if (Mode == DataBoundControlMode.Edit && FieldValue != null)
    {
        TextBox1.Text = DateTime.Parse(FieldValueEditString).GetDateTimeForUI().ToString();
    }
}
 
protected override object ConvertEditedValue(string value)
{
    string valueToSave = DateTime.Parse(value).GetUtcDateTime().ToString();
 
    return base.ConvertEditedValue(valueToSave);
}

That is all that is required. I’ll show the meat of the GetUtcDateTime() extension below. Alternatively, you could do your own time zone conversion here. The key is that in the DataBind() method you convert the FieldValueEditString (which is in UTC) to the time zone you desire, and that in the ConvertEditedValue() method you convert value (which is in the desired time zone) to UTC.

Doing the actual Timezone Conversion

I implemented the conversion methods as an extensions to the DateTime class. This is just for ease of use, but you can do this however you see best. In .NET 3.5 Microsoft added a very nice class called TimeZoneInfo. It makes converting between time zones trivial. The best thing is that it takes Day light savings into consideration when it does the conversion. Below is my implementation for displaying date/times in Pacific Standard Time (PST). For simplicity and limiting scope, in this case I have hard coded the desired timezone, but you could pull it from the Profile of the current user if you collect that info from the user in some UI.

namespace MyApp.Helpers
{
    public static class DateTimeExtensions
    {
        // call when displaying value from db to the ui
        public static DateTime GetDateTimeForUI(this DateTime dateAsUtc)
        {
            DateTime dateForUI = TimeZoneInfo.ConvertTime(dateAsUtc, TimeZoneInfo.Utc, TimeZoneInfo.FindSystemTimeZoneById(Config.DefaultTimeZone));
            return dateForUI;
        }

        // call when saving value to db from the ui
        public static DateTime GetUtcDateTime(this DateTime dateFromUI)
        {
            DateTime dateAsUtc = TimeZoneInfo.ConvertTime(dateFromUI, TimeZoneInfo.FindSystemTimeZoneById(Config.DefaultTimeZone), TimeZoneInfo.Utc);
            return dateAsUtc;
        }
    }
}

Converting CLng in MS Access SQL to T-SQL (SQL Server)

2011-12-22T15:08:00.001-07:00

The problem I need to solve is how do I reproduce the exact functionality of the CLng (function built-in to MS Access SQL) in MS SQL Sever. The answer is not so difficult, but knowing what the question to ask was not so obvious.

After much frustration I finally realized that how CLng in MS Access SQL works is actually quite unintuitive. My situation can be explained quite simply. I have a query in MS Access that I need to convert to run completely on SQL Server. In parts of the MS Access queries the CLng function is used. CLng is similar to Round() in SQL Server, but is not the same particularly when it comes to values that end in .5. Initially I thought it would just truncate the decimals of a double. For example, I thought CLng(.5) should be 1, but MS Access says it is 0. Interestingly, CLng(3.5) is 4, not 3 and CLng(2.5) is 2, not 3. This behavior is very inconsistent to me, but I need to reproduce it on SQL Server using T-SQL so that I can compare the queries.

After some reading about issues related to rounding in general on Wikipedia I found out that there are many ways to round. After reading a technical article from Microsoft I found out that indeed VBA (and appears to be MS Access SQL) do not round the same as MS SQL Server. From this I learned the key to the solution; the CLng function in MS Access implements rounding using the Banker’s Rounding method described in both of the above sources. I did find an implementation of Banker’s Rounding once I knew what to search for. I found a good solution here. I have copy and pasted the solution below.

Here is that function you can execute on SQL Server that will do the same thing as Clng on MS Access SQL because it is an implementation of Banker’s Rounding.

create function MSAccessClng(@x as float)
returns int
as
BEGIN
Declare @DecimalPlaces as tinyint
Set @DecimalPlaces = 0

set @x = @x * power(10, @DecimalPlaces)

return
case when @x = floor(@x) then @x
else
    case sign(ceiling(@x) - 2*@x + floor(@x))
    when 1 then floor(@x)
    when -1 then ceiling(@x)
    else 2*round(@x/2,0) end
end / power(10, @DecimalPlaces)
END

Now you have a function you can use on SQL Server just like you have on MS Access. Keep in mind this is a Scalar-valued function and this means there is a performance penalty for using it, but if you need this functionality you need the functionality.

Below are different examples of what I used to test that the functionality is the same on MS Access and MS SQL Server (T-SQL). Notice, the ones that say SAME means that CLng() has the same functionality as cast(). The ones that say DIFFERENCE means that the special logic was needed to implement differences. You can also see the usage of the function.

-- SAME
select clng(-3.3) -- -3
select cast(-3.3 as int) -- -3
select Round(-3.3, 0) -- 3
select dbo.MSAccessClng(-3.3) -- -3

-- SAME
select clng(-0.3) -- 0
select cast(-0.3 as int) -- 0
select round(-.3,0) -- 0
select dbo.MSAccessClng(-0.3) -- 0

-- DIFFERENCE
select clng(-3.5) -- -4
select cast(-3.5 as int) -- -3
select Round(-3.5,0) -- -4
select dbo.MSAccessClng(-3.5) -- -4

-- SAME
select clng(-0.5) -- 0
select cast(-0.5 as int) -- 0
select round(-0.5, 0) -- error
select dbo.MSAccessClng(-0.5) -- -0

-- DIFFERENCE
select clng(-0.51) -- -1
select cast(-0.51 as int) -- 0
select round(-0.51, 0) -- error
select dbo.MSAccessClng(-0.51) -- -1

-- DIFFERENCE
select clng(-0.8) -- -1
select cast(-0.8 as int) -- 0
select round(-0.8, 0) -- error
select dbo.MSAccessClng(-0.8) -- -1

-- SAME
select clng(-1) -- -1
select cast(-1 as int) -- -1
select round(-1, 0) -- -1
select dbo.MSAccessClng(-1) -- -1

-- SAME
select clng(-100000) -- -10000
select cast(-100000 as int) -- -10000
select round(-100000, 0) -- -10000
select dbo.MSAccessClng(-100000) -- -10000

-- SAME
select clng(3.3) -- 3
select cast(3.3 as int) -- 3
select Round(3.3, 0) -- 3
select dbo.MSAccessClng(3.3) -- 3

-- SAME
select clng(0.3) -- 0
select cast(0.3 as int) -- 0
select round(0.3,0) -- 0
select dbo.MSAccessClng(0.3) -- 0

-- DIFFERENCE
select clng(3.5) -- 4
select cast(3.5 as int) -- 3
select Round(3.5,0) -- 4
select dbo.MSAccessClng(3.5) -- 4

-- SAME
select clng(0.5) -- 0
select cast(0.5 as int) -- 0
select round(0.5, 0) -- error
select dbo.MSAccessClng(0.5) -- 0

-- DIFFERENCE
select clng(0.51) -- 1
select cast(0.51 as int) -- 0
select round(0.51, 0) -- error
select dbo.MSAccessClng(0.51) -- 1

-- DIFFERENCE
select clng(0.8) -- 1
select cast(0.8 as int) -- 0
select round(0.8, 0) -- error
select dbo.MSAccessClng(0.8) -- 1

-- SAME
select clng(1) -- 1
select cast(1 as int) -- 1
select round(1, 0) -- 1
select dbo.MSAccessClng(1) – 1

-- DIFFERENCE
select clng(34.5) -- 34
select cast(34.5 as int) -- 34
select Round(34.5,0) -- 35
select dbo.MSAccessClng(34.5) -- 4

-- DIFFERENCE
select clng(0) -- 0
select cast(0 as int) -- 0
select round(0, 0) -- 0
select dbo.MSAccessClng(0) – 0

-- SAME
select clng(100000) -- 10000
select cast(100000 as int) -- 10000
select round(100000, 0) -- 10000
select dbo.MSAccessClng(100000) -- 10000

Get First or Second Word using T-SQL

2011-12-06T15:32:00.001-07:00

When working with SQL (T-SQL) I often need to work with words with-in a string (nvarchar or varchar). For instance I want to display only the first word (first name in this case) of a field (FullName in this case) or maybe the second word in other cases. Words are funny things, they can be separated by commas, spaces, hyphens, and other characters such as semi-colons, exclamation point, parenthesis, brackets perhaps.

Below is the code to create a Scalar-valued Functions in SQL Server (T-SQL) that get the first word or second word.

Get First Word

create function GetFirstWord(@Name as nvarchar(100))
returns nvarchar(100)
as
BEGIN

Declare @FirstWord as nvarchar(100)
Declare @SpaceIdx as int

-- convert word separators to space for simplicity
Set @Name = Replace(@Name, ',', ' ') -- comma to space
Set @Name = Replace(@Name, '.', ' ') -- period to space
Set @Name = Replace(@Name, '-', ' ') -- hyphen to space

Set @SpaceIdx = CHARINDEX(' ', @Name)

if (@SpaceIdx > 0)
SET @FirstWord = SUBSTRING(@Name, 0, @SpaceIdx)
else -- all one word
SET @FirstWord = @Name

return @FirstWord;
END;

Get Second Word

create function GetSecondWord(@Name as nvarchar(100))
returns nvarchar(100)
as
BEGIN

Declare @SecondWord as nvarchar(100)
Declare @AfterWord1 as nvarchar(100)
Declare @SpaceIdx as int
Declare @Space2Idx as int

Set @SpaceIdx = CHARINDEX(' ', @Name)

if (@SpaceIdx = 0) return ''

Set @AfterWord1 = SUBSTRING(@Name, @SpaceIdx+1, len(@Name) - @SpaceIdx)

Set @Space2Idx = CHARINDEX(' ', @AfterWord1)

if (@Space2Idx > 0)
SET @SecondWord = SUBSTRING(@AfterWord1, 0, @Space2Idx)
else -- remainder of word
SET @SecondWord = @AfterWord1

-- if second word is the same as the original, then we only have one word
if @SecondWord = @Name return ''

return @SecondWord;
END;

Usage

select GetFirstWord(‘Joe Smith’)

returns: Joe

select GetSecondWord(‘Joe Smith’)

returns: Smith

Extending the Code

In the example above was just concerned with with commas, spaces, and hyphens, but you can extend the code quite easily by adding additional replace statements.

Note on Performance

I have implemented these functions as Scalar-valued functions which are known to incur a noticeable performance hit when you use them in a select statement.

Efficiently Counting Spaces using T-SQL

2011-12-06T14:16:00.001-07:00

Let’s assume you have a table called Person and it has a column called FullName. As you might expect, the FullName is really just a concatenation of the persons first and last name separated by a space. If I want to get some stats on how many people have only one space (implying two words only) it is actually quite easy to figure this out. Consider the following SQL statement

select FullName from Person

Let pretend it returns something like

Ben Wheel
James Johnson Jr.
Jane Dela Cruz
Jeff Jones
Lance Strong

Exactly 1 Space

To get all rows that have exactly one space in them you could do the following

select FullName from Person
where len(FullName) - len(Replace(FullName, ' ', '')) = 1

Jeff Jones
Lance Strong
Ben Wheel

Exactly 2 Space

To get all rows that have exactly one space in them you could do the following

select FullName from Person
where len(FullName) - len(Replace(FullName, ' ', '')) = 2

James Johnson Jr.
Jane Dela Cruz

Less than or equal to 3 Space

To get all rows that have exactly one space in them you could do the following

select FullName from Person
where len(FullName) - len(Replace(FullName, ' ', '')) <= 3

Ben Wheel
James Johnson Jr.
Jane Dela Cruz
Jeff Jones
Lance Strong

As you can see it is relatively easy to count occurrences of a character using T-SQL. You can expand on these queries just by changing the stuff in red.

Open 2 Excel Files in separate windows

2011-11-02T13:58:00.001-07:00

WARNING: Changing your registry can be dangerous to the health of your computer. Do so at your own risk.

This change makes it so when you click an Excel file it will ALWAYS open in a new window (a different instance of Excel)

.XLSX

I highly recommend you back up your registry before making any changes.
Open regedit.exe
Navigate to HKEY_CLASSES_ROOT/Excel.Sheet.12/shell/Open/command
Open (Double-click) the key called (Default)
Change the value from
"C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE" /e
to "C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE" /e “%1”
Click the OK button
Below the (Default) key you will see a key with the name command.
Right click it and select Rename.
Change the name to something else (it doesn’t matter what)
Your screen should now look something like the following (I appended bv to the command key, but you should probably use something different)
Navigate to HKEY_CLASSES_ROOT/Excel.Sheet.12/shell/Open/ddeexec
Right click it and select Rename.
Change the name to something else (it doesn’t matter what)
Your screen should now look something like the following:
You have now successfully made it so that when you open an existing Excel file (that has the extension .xlsx) it will open in a new window. This allows you to easily compare or view two Excel file at the same time. This is particular helpful if you have two monitors. You can also ways revert back if you decide you don’t like the change by undoing the changes made here, that is why we renamed instead of deleted the keys.

.XLS

You can follow the same instructions for .XLS except you will want to amend the instruction so that where ever you see Excel.Sheet.12, replace it with Excel.Sheet.8.

BTW, I found this tip here.

PNG Compression Tools

2011-10-06T12:36:00.001-07:00

PNG is a great format, but not all applications that save them create the same size files. There are at least four major tools out there that do nothing more than optimize the size using lossless techniques. Some of these tools even work on other formats also.

I have not personally benchmarked them, but this person has. Below are some excerpts from their post.

punypng: the new kid of the block.
smush.it: Uses pngcrush as the main PNG optimizer. Currently, available in Yahoo’s YSlow Firefox plug-in. I believe it uses the -brute option for pngcrush.
OptiPNG: A slighlty better algorithm, compared to pngcrush. In case you’re wondering, Google’s PageSpeed plugin also uses OptiPNG for it’s compression library.
ImageOptim: The heavyweight contender. Available for OS X, ImageOptim uses every major library out there: advdef, pngcrush, optipng, pngcrush, jpegoptim, jpegtran, and optionally pngout. I ran this the benchmarks with pngout enabled.

If file size is critical I suggest trying all of them since it seems one does better than the other depending on the file.

I use Paint.NET a lot and there is a plug-in for it if you use OptiPNG.

If you want to use Visual Studio, there is a plug-in for it that uses punypng and smush.it.

A look at the topic of Artificial Intelligence (AI)

2011-09-23T17:06:00.001-07:00

General AI

Artificial Intelligence Wiki – a good place to start to understand what AI encompasses and its different areas.

Journal of Machine Learning Research – Amazingly complex scientific papers of AI algorithms. Not for light reading.

Machine Learning Open Source Software

Areas of interest for me:

Reinforcement Learning – concerned with how an agent ought to take actions in an environment so as to maximize some notion of cumulative reward.

Data Mining

Data Mining – good starting point for learning about data mining

Text Mining

Basically Text Mining is data mining on unstructured data like documents, web pages, etc. instead of a database.

Natural Language Processing – Imagine a machine being able to scour the internet and actually extract knowledge about what it read.

Text Mining – an area of Natural Language Processing, has many commercial uses even today. i.e. Bing

Semantic Web – a machine readable version of the web.

DBpedia – An effort to translate Wikipedia into machine understandable format to facilitate complex queries and meanings of words, not just matches.

Freebase – Similar to DBpedia, but it is hand crafted. DBpedia has lots of links to it as well. A database of “bar codes” for all entities on the web. Aliasing… Also powers Bing

What is Text Mining – describes how text mining is more complex than data mining since it involves natural language processing.

Carrot2 – text and search results clustering framework. Very cool way to browse search results and get to what you are looking for

Reading a MS Word 2007 Document in .docx format using C#

2011-09-21T14:08:00.001-07:00

If you want need to read (or write) from a MS Word 2007 Document that has been saved in the Open XML format (.docx) then you can use the Open XML SDK 2.0 for Microsoft Office to do just. The first thing you will need to do is download and install the SDK. In particular, you must download and install the OpenXMLSDKv2.msi. In addition, you can download the OpenXMLSDKTool.msi if you want. It has some VERY nice features like generating code from an existing .docx file.

Now that you have the files you need, open Visual Studio (2008 or 2010 works fine), open the project you want to use, and add a reference to the DocumentFormat.OpenXml (I had to browse to it in the adding references windows by going to C:\Program Files (x86)\Open XML SDK\V2.0\lib) and WindowsBase (mine was located in the list of .NET tab when adding references). Please note, this code does not require MS Word be installed and is safe to run on the server such as with ASP.NET.

Now that you have the api, the rest is just working with the document. To get a better understand on how to work with the parts (structure) of the Word Document, click here. For a list of “How do I…” code samples, click here.

Here is example code on how to get the body of the document.

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;

…

using (WordprocessingDocument wordDocument = WordprocessingDocument.Open(Filename, false))
{
var body = wordDocument.MainDocumentPart.Document.Body;
}

Here is an example of a more complex line of code that can be used to navigate the structure of the document using LINQ. In this case the document has a table in it and we are getting the first row and first cell of that row and the second element.

wordDocument.MainDocumentPart.Document.Body.ChildElements.First<Table>().ChildElements.First<TableRow>().ChildElements.First<TableCell>().ElementAt(2).InnerText

I hope this gives you an idea of how to get started. There are lots of good links, examples, etc here.

Convert a batch of .doc files to .docx using C# and Word 2007

2011-09-20T18:03:00.001-07:00

I recently inherited a bunch of Microsoft Word files that are in the .doc format. I want to convert them all to .docx so I can easily parse them later without needing MS Word installed (i.e. on a server). You can do the conversion with no code at all if you have the time. All you have to do is open the file up in MS Word 2007 and save the file as a .docx; Word will do the work for you. This is great, but I had hundred of files to convert, and I could not bear doing something that many times (I’m a programmer after all). Unbelievably there are products that cost $150 and more to do this. There are so trial editions that do 5 at a time, etc, and even some command line ones. Command line might work, but it still involves me figuring out where in a bunch of nested directories where the .doc files are and coming up with the command line arguments. That isn’t much better than opening Word, though I could script that solution at least.

In the end, I decided it really wasn’t that difficult to just sit down and write the code to do this. The code is very simple. I have put it in one class so that you can easily include it in your own project. It could be a command line or WPF or WinForms. It doesn’t really matter. All the code does is

Take the directory path that you pass it and recursively finds all the .doc files (even if they are in sub-directories of sub-directories)
Open MS Word in the background (You can see winword.exe in your Processes under Task Manager).
Loop through each file found
Open the current file
Tell MS Word 2007 to save the file as .docx
Close the File
Close MS Word when all files have been processed.

You will find all the new files right next to the .doc files. You can then search in Windows for .doc and delete them quickly once you have comfortable everything went smoothly.

Things you will need to use the class below.

Visual Studio
MS Word installed on the same machine as you run your program you create

When you create your project you will need to add a reference to your project for Microsoft.Office.Interop.Word. Besure you choose the version 12 and not version 11 like I did initially. If you do you will get a compiler error.

Below is the actual code you need.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Microsoft.Office.Interop.Word;

namespace ConvertDocToDocx
{
public class DocToDocxConverter
{

        List<string> AllWordFiles { get; set; }

        DirectoryInfo StartingDir { get; set; }

        public DocToDocxConverter(DirectoryInfo startingDir)
        {
            StartingDir = startingDir;
        }

        public void ConvertAll()
        {
            AllWordFiles = new List<string>();

            // NOTE: Since .xls is a also in .xlsx this search will find .xls and .xlsx files
            // If the extension is different then this can be called again to include them.
            FindWordFilesRecursively(StartingDir.FullName, "*.doc");

// only open and close Word once to maximize performance
Application word = new Application();

try
{

                foreach (string filename in AllWordFiles)
                {
                    // exclude the .docx (only include .doc) files as we don't need to convert them. :)
                    if (filename.ToLower().EndsWith(".doc"))
                    {
                        try
                        {
                            var srcFile = new FileInfo(filename);

                            // convert the source file
                            var doc = word.Documents.Open(srcFile.FullName);
                            string newFilename = srcFile.FullName.Replace(".doc", ".docx");

                            // Be sure to include the correct reference to Microsoft.Office.Interop.Word
                            // in the project refences. In this case we need version 12 of Office to get the new formats.
                            doc.SaveAs(FileName: newFilename, FileFormat: WdSaveFormat.wdFormatXMLDocument);
                        }
                        finally
                        {
                            // we want to make sure the document is always closed
                            word.ActiveDocument.Close();
                        }
                    }
                }
            }
            finally
            {

                word.Quit();
            }
        }

void FindWordFilesRecursively(string sDir, string filter)
{

            foreach (string d in Directory.GetDirectories(sDir))
            {
                foreach (string f in Directory.GetFiles(d, filter))
                {
                    AllWordFiles.Add(f);
                }
                FindWordFilesRecursively(d, filter);
            }
        }

    }
}

Email Aliases in Gmail

2011-09-14T16:01:00.001-07:00

Gmail doesn't offer traditional aliases, but you can receive messages sent to your.username+any.alias@gmail.com. For example, messages sent to jane.doe+notes@gmail.com are delivered to jane.doe@gmail.com.

You can set up filters to automatically direct these messages to Trash, apply a label or star, skip the inbox, or forward to another email account.

This is great for testing user registration in your app.

Free Options for reading and writing Excel Files

2011-08-25T11:55:00.001-07:00

In general there are three main categories of tools that allow you to interact with Excel files.

MS Excel Primary Interop Assembly (PIA) Based: These require MS Excel be installed and licensed. Not a good option of server environment since it basically spins up Excel.
OleDB Based: These are very fast a light weight solutions generally that are server friendly. No license or installation of Excel required. I highly recommend the ACE driver instead of JET driver. You can do read, write, update with this option. See OLE DB Article section below for details.
Other: This means they have coded their own solution typically using some kind of XML format, but sometimes using binary format.

I have not tested many of these solutions other than using PIA or OLE DB. The exception to that is CarlosAg Excel Xml Writer Library which I do like, but it is only for Writing Excel files, not reading them. It has good performance and works well for tabular and non-tabular spreadsheets.

I would love to add other solutions to the list so please let me know your favorite solutions.

Solution	Requires Excel / Uses PIA	Uses OleDB	Uses XML	Server Friendly	Supports Read	Supports Write	Supports .XLS	Supports .XLSX
MS Excel Interop (PIA)	Yes	No	No		Yes	Yes	Yes	Yes
.NET Framework Data Provider for OLE DB (using ACE OLEDB 12.0)	No	ACE or JET	No	Yes	Yes		Yes	Yes
Open XML SDK 2.0 for MS Office	No	No	Yes	Yes	Yes	Yes	No	Yes
Excel Data Reader	No	No	No	Yes	Yes	No	Yes	Yes
ExcelMapper for tabular data	No	ACE & JET	No	Yes	Yes	Yes	Yes	Yes
CarlosAg Excel Xml Writer Library – uses old xml spreadsheet format	No	No	Yes	Yes	No	Yes	No	No (only .xml)
GemBox (for small files it is free)	No	No?	No?	Yes	Yes	Yes	Yes	Yes
Koogra	No	No	No	Yes?	Yes	No?	Yes?	Yes
MyXLS	No	Yes	No	Yes	Yes	Yes	Yes	No
ExtremeML	No	No	Yes	Yes	Yes	Yes	No?	Yes
ClosedXML	No	No	Yes	Yes	Yes	Yes	No	Yes

Using Excel PIA Articles:

Loading and reading the Microsoft Excel file content using C#

How to export database to Excel file

C# Excel Tutorial

OLE DB Articles

OLE DB can use either JET or ACE. ACE is MUCH better than JET because it doesn’t have the same pitfalls with guessing data that JET does, etc. So when using OLE DB change the connection string to use ACE instead of JET driver for OleDB.

Connection strings for Excel 2007 - Connection Strings for Excel / ACE OLE DB 12.0

How to read from an Excel file using OLEDB

How to insert data to Excel file using OLEDB

How to update data in Excel file using OLEDB

My favorite Option is to use the Open XML format

Writing Large Excel File with the Open XML SDK

How to create a stylesheet in Excel Open XML

Configuring Subversion for MS Office files

2011-08-23T14:32:00.001-07:00

Subversion works well, and does a fair job of handling MS Office documents (or any other unmergeable file) out of the box, but it is not what I would call obvious. There are some tweaks I would recommend in order to make it work much better.

To accomplish this we need to do a few things. I am assuming you are using TortoiseSVN, but most subversion clients should have similar features if they implement the full set of subversion features.

I have three goals. I have listed them below and explained what is needed to achieve each of the goals.

Goal 1: Make subversion not automatically merge MS Office files

I think is met by default, but if nothing else will be handled by the time we are done.

Goal 2: Lock the file so others cannot make changes and commit them.

This is actually built into subversion. Before you edit a file you should always get an update (Right-click file | SVN Update) to it to make sure you have the most recent copy of it. You should make sure no one else has a lock on the file by right-clicking the file | TortoiseSVN | Check for Modification. Assuming the file is not locked by someone else, we need to lock the file (Right-click file | TortoiseSVN | Get Lock… OR Right-click file | SVN Get Lock…). We have now prevented people from ACCIDENTALLY overwriting our un-committed changes by committing first. I say accidentally because they can still break locks if they really feel they are more important. The problem is that unless they use the Check for Modifications… like we did, they won’t know the file is locked until they try to commit the file since the lock prevents commits, not editing. Which leads us to our next goal.

Goal 3: Automatically communicate to other users that the file is locked.

This is not the same thing as actually locking the file, but really is more of a communication tool to let the other users know that someone else is editing it. We want to be more proactive really so that we or someone else never starts editing a file that is already locked. Up to this point, everything has been under your control, but to meet this goal, it is client configuration, not a server configuration. This means that you can recommend this to your team members to add to their subversion configuration file, but you can’t force them. Luckily it is more about convenience that enforcement on this requirement.

To meet this goal, we need to open out config file and add the svn:needs-lock property to any MS Office file extension. You will need to add them if they are not already there (they probably aren’t unless you have a customized file already). You will also need to uncomment the line with enable-auto-props = yes. Also, be sure the [auto-props] line is uncommented. See the example config file below for examples. Once you do that once you get an update (Right-click file | SVN Update) from subversion the file will now be read-only if you don’t have a lock on it and it will also have a new icon ().

WARNING: The automatic properties are NOT applied except on Add or Import into subversion. This means that any if you are trying implement this on an existing repository that already has Office files in it that you need to manually add the svn:needs-lock property to the properties.

You can do that in TortoiseSVN and getting a lock for the file(s) then Right-Clicking the file | TortoiseSVN | Properties | New… button and then choose svn:needs-lock for the Property Name field and * for the Property value field.

You can also do the same thing at the directory level or for multiple files and even recursively by selecting a directory or multiple files then TortoiseSVN by Right-Clicking the file | TortoiseSVN | Properties | New… button and then choose svn:needs-lock for the Property Name field and * for the Property value field and then click Apply property recursively checkbox if applicable. Keep in mind this will apply the property to ALL files you select and those files may not be Office docs, so be aware of what you are selecting. If you have a lot of file mixed in with mergeable file to do this to you may want to automate the task. Here is a good explanation of how this can be done. After you set the property, you will need to Commit the change like you would any other changes.

In all cases, after you are done making you changes and have commited them, be sure to release the lock on the file(s).

Now when you get a lock (Right-click file | TortoiseSVN | Get Lock… OR Right-click file | SVN Get Lock…) on the file the icon changes to a yellow lock and the file is no longer read-only. You are now safe to make your changes.

Unlocking

There are two ways to unlock a file. The first is simply to commit your changes (assuming you made some). NOTE: The Tortoisesvn docs seem to indicate the lock is released even if you don’t make a change, but that isn’t how it seems to work on my version. The lock is only released when I make a change before committing. It definitely should release the lock if you make a change first though. So, I guess your mileage may vary. The other option is to manually release it by Right-clicking file | TortoiseSVN | Release Lock. Once the lock is removed other people can get a lock on the file.

Additional Resources

Download TortoiseSVN

To read up more on how to use locking in TortoiseSVN click here.

Here is a seemingly good online book on subversion – FREE

Automatic Lock - Modify – Unlock – scripts to help do this for existing files in subversion

Example Configuration File

The configuration file is located at (in Windows):

C:\Users\yourUserNameHere\AppData\Roaming\Subversion or maybe C:\Users\yourUserNameHere\AppData\Subversion (not sure for non-roaming profiles).

If you are using TortoiseSVN you can edit the config file by going to Settings | General tab | Edit button.

### This file configures various client-side behaviors.
###
### The commented-out examples below are intended to demonstrate
### how to use this file.

### Section for authentication and authorization customizations.
[auth]
### Set password stores used by Subversion. They should be
### delimited by spaces or commas. The order of values determines
### the order in which password stores are used.
### Valid password stores:
###   gnome-keyring        (Unix-like systems)
###   kwallet              (Unix-like systems)
###   keychain             (Mac OS X)
###   windows-cryptoapi    (Windows)
#password-stores = windows-cryptoapi
###
### The rest of this section in this file has been deprecated.
### Both 'store-passwords' and 'store-auth-creds' can now be
### specified in the 'servers' file in your config directory.
### Anything specified in this section is overridden by settings
### specified in the 'servers' file.
###
### Set store-passwords to 'no' to avoid storing passwords in the
### auth/ area of your config directory. It defaults to 'yes',
### but Subversion will never save your password to disk in
### plaintext unless you tell it to (see the 'servers' file).
### Note that this option only prevents saving of *new* passwords;
### it doesn't invalidate existing passwords. (To do that, remove
### the cache files by hand as described in the Subversion book.)
# store-passwords = no
### Set store-auth-creds to 'no' to avoid storing any subversion
### credentials in the auth/ area of your config directory.
### It defaults to 'yes'. Note that this option only prevents
### saving of *new* credentials; it doesn't invalidate existing
### caches. (To do that, remove the cache files by hand.)
# store-auth-creds = no

### Section for configuring external helper applications.
[helpers]
### Set editor-cmd to the command used to invoke your text editor.
###   This will override the environment variables that Subversion
###   examines by default to find this information ($EDITOR,
###   et al).
# editor-cmd = editor (vi, emacs, notepad, etc.)
### Set diff-cmd to the absolute path of your 'diff' program.
###   This will override the compile-time default, which is to use
###   Subversion's internal diff implementation.
# diff-cmd = diff_program (diff, gdiff, etc.)
### Set diff3-cmd to the absolute path of your 'diff3' program.
###   This will override the compile-time default, which is to use
###   Subversion's internal diff3 implementation.
# diff3-cmd = diff3_program (diff3, gdiff3, etc.)
### Set diff3-has-program-arg to 'yes' if your 'diff3' program
###   accepts the '--diff-program' option.
# diff3-has-program-arg = [yes | no]
### Set merge-tool-cmd to the command used to invoke your external
### merging tool of choice. Subversion will pass 4 arguments to
### the specified command: base theirs mine merged
# merge-tool-cmd = merge_command

### Section for configuring tunnel agents.
[tunnels]
### Configure svn protocol tunnel schemes here. By default, only
### the 'ssh' scheme is defined. You can define other schemes to
### be used with 'svn+scheme://hostname/path' URLs. A scheme
### definition is simply a command, optionally prefixed by an
### environment variable name which can override the command if it
### is defined. The command (or environment variable) may contain
### arguments, using standard shell quoting for arguments with
### spaces. The command will be invoked as:
### <command> <hostname> svnserve -t
### (If the URL includes a username, then the hostname will be
### passed to the tunnel agent as <user>@<hostname>.) If the
### built-in ssh scheme were not predefined, it could be defined
### as:
# ssh = $SVN_SSH ssh -q
### If you wanted to define a new 'rsh' scheme, to be used with
### 'svn+rsh:' URLs, you could do so as follows:
# rsh = rsh
### Or, if you wanted to specify a full path and arguments:
# rsh = /path/to/rsh -l myusername
### On Windows, if you are specifying a full path to a command,
### use a forward slash (/) or a paired backslash (\\) as the
### path separator. A single backslash will be treated as an
### escape for the following character.

### Section for configuring miscelleneous Subversion options.
[miscellany]
### Set global-ignores to a set of whitespace-delimited globs
### which Subversion will ignore in its 'status' output, and
### while importing or adding files and directories.
### '*' matches leading dots, e.g. '*.rej' matches '.foo.rej'.
# global-ignores = *.o *.lo *.la *.al .libs *.so *.so.[0-9]* *.a *.pyc *.pyo
# *.rej *~ #*# .#* .*.swp .DS_Store

global-ignores = *.o *.lo *.la *.al .libs *.so *.so.[0-9]* *.a *.pyc *.pyo ~* *~

### Set log-encoding to the default encoding for log messages
# log-encoding = latin1
### Set use-commit-times to make checkout/update/switch/revert
### put last-committed timestamps on every file touched.
# use-commit-times = yes
### Set no-unlock to prevent 'svn commit' from automatically
### releasing locks on files.
# no-unlock = yes
### Set mime-types-file to a MIME type registry file, used to
### provide hints to Subversion's MIME type auto-detection
### algorithm.
# mime-types-file = /path/to/mime.types
### Set preserved-conflict-file-exts to a whitespace-delimited
### list of patterns matching file extensions which should be
### preserved in generated conflict file names. By default,
### conflict files use custom extensions.
# preserved-conflict-file-exts = doc ppt xls od?
### Set enable-auto-props to 'yes' to enable automatic properties
### for 'svn add' and 'svn import', it defaults to 'no'.
### Automatic properties are defined in the section 'auto-props'.
enable-auto-props = yes
### Set interactive-conflicts to 'no' to disable interactive
### conflict resolution prompting. It defaults to 'yes'.
# interactive-conflicts = no
### Section for configuring automatic properties.
[auto-props]
### The format of the entries is:
###   file-name-pattern = propname[=value][;propname[=value]...]
### The file-name-pattern can contain wildcards (such as '*' and
### '?'). All entries which match (case-insensitively) will be
### applied to the file. Note that auto-props functionality
### must be enabled, which is typically done by setting the
### 'enable-auto-props' option.
*.awk      = svn:eol-style=native
*.bat      = svn:eol-style=native;svn:executable
*.c        = svn:eol-style=native
*.ccf      = svn:eol-style=native
*.cd       = svn:eol-style=native
*.cdl      = svn:eol-style=native
*.cpp      = svn:eol-style=native
*.cs      = svn:eol-style=native
*.csv      = svn:eol-style=native
*.ddl      = svn:eol-style=native
*.doc      = svn:needs-lock
*.docm     = svn:needs-lock
*.docx     = svn:needs-lock
*.dot      = svn:needs-lock
*.dotm     = svn:needs-lock
*.dotx     = svn:needs-lock
*.dsp      = svn:eol-style=CRLF
*.dsw      = svn:eol-style=CRLF
*.g        = svn:eol-style=native
*.gif      = svn:mime-type=image/gif
*.h        = svn:eol-style=native
*.hd       = svn:eol-style=native
*.hpp      = svn:eol-style=native
*.htm      = svn:eol-style=native;svn:mime-type=text/html
*.html     = svn:eol-style=native;svn:mime-type=text/html
*.ico      = svn:mime-type=image/ico
*.id       = svn:eol-style=native
*.idl      = svn:eol-style=native
*.include = svn:eol-style=native
*.inf      = svn:eol-style=native;svn:executable
*.ini      = svn:eol-style=native
*.java     = svn:eol-style=native
*.jpe?g    = svn:mime-type=image/jpeg
*.jpg      = svn:mime-type=image/jpeg
*.ld       = svn:eol-style=native
*.lk       = svn:eol-style=native
*.m3u      = svn:mime-type=audio/x-mpegurl
*.mdb      = svn:needs-lock
*.mk       = svn:eol-style=native
*.mmf      = svn:eol-style=native
*.pdf      = svn:needs-lock
*.pl       = svn:eol-style=native
*.pm       = svn:eol-style=native
*.png      = svn:mime-type=image/png
*.ppt*     = svn:needs-lock
*.py       = svn:eol-style=native
*.pyw      = svn:eol-style=native
*.qconf    = svn:eol-style=native
*.rgd      = svn:eol-style=native
*.rtf      = svn:needs-lock
*.s        = svn:eol-style=native
*.sh       = svn:eol-style=native;svn:executable
*.sldm     = svn:needs-lock
*.sldx     = svn:needs-lock
*.tex      = svn:eol-style=native
*.thmx     = svn:needs-lock
*.txt      = svn:eol-style=native
*.xlam     = svn:needs-lock
*.xls      = svn:needs-lock
*.xlsb     = svn:needs-lock
*.xlsm     = svn:needs-lock
*.xlsx     = svn:needs-lock
*.xltm     = svn:needs-lock
*.xltx     = svn:needs-lock
Makefile   = svn:eol-style=native
makefile   = svn:eol-style=native
SConscript = svn:eol-style=native
SConstruct = svn:eol-style=native

Change Default Paste to Unformatted Text in MS Word 2007

2011-08-18T12:18:00.001-07:00

Are you tired of MS Word 2007 and higher pasting formatted text your document when all you really want is unformatted text to be pasted? The temporary solution is to go to the Home ribbon | Paste (down arrow) | Paste Special… | Select Unformatted Text | OK. That is a lot of clicking for just a simple paste that I think should be the default.

Finally in MS Word 2007 they added some nice features to address this very issue. Go to the Word menu | Word Options | Advanced and then make the changes as noted by the four arrows below.

Now when you copy and paste you will always get text only unless you do like would have done before and to to Home ribbon | Paste (down arrow) | Paste Special… but now choose Formatted Text (RTF) or HTML Format depending on your source.

Thank you Microsoft!

How to add another user’s inbox to Outlook

2011-08-03T13:41:00.001-07:00

In Outlook you can open another users inbox if they make you a delegate / share it with you. You can also add it to your list of Mail Folders so that you can easily view it. Below are the instructions to do both of these things.

In Outlook 2007 (maybe similar for other versions) do the follow:

Open Outlook 2007 if you have not already
Go to the Tools menu and choose Account Settings…
Assuming you are now on the E-mail tab, click your default account.
Click the Change…
Click More Settings…
Click the Advanced tab
Click the Add… button
Type in the name of the user who’s mailbox or calendar, etc that you want to access
Click OK and OK until you are back on the screen titled Change E-mail Account.
Wait until the Next > button becomes active and then click it.
Click the Finish button.
Click the Close button.

You should now see your mailbox near your current inbox on the Navigation Pane (aka Mail Folder pane). This will bring over the calendar, tasks, inbox, etc.

If you don’t want to always have this person’s stuff in your Outlook, you can also access it by opening each time. To do this do the following:

Open Outlook 2007 if you have not already.
Go to the File menu and choose Open and then Other User’s Folder…
Type the name of the person in the Name… textbox.
Select what you want to access from the Folder type drop down list. For example Inbox to view the email or Calendar for Calendar.

NOTE: This opens it but does not keep it readily available to you. To access it again you can follow the same basic steps (Go to the File menu and choose Open and then choose the item you added – it should show at the end of the menu).

Screen-scraping / Automation Tools

2011-08-01T11:15:00.001-07:00

In general I don’t recommend screenscraping at all, and it should only be a last resort. I do recommend test automation of UI if you are developing software though. Screenscraping will consume a ton of time to build it, and then to maintain it when the screens change in very minor ways. The main reason is inevitably the thing you want access to on the screen is not easy to get to and interact with. In many cases a tool will get you 80% to 90% of the way there and then you will hit a block wall or at least an endless pit that sucks all your time and resources to find a solution.

Assuming you have decided that screen-scraping is worth it or you are doing UI test automation I do have a few recommendations if you use C# and are on the Windows platform. Some things to consider: What are you trying to scrap? What type of content are you trying to scrape? For example, is it a web page (does it have AJAX), Silverlight, or is it a desktop app (Is it Java or Native Windows). Will this be on a server and if so can you install FireFox (FF) or Internet Explorer (IE)? How will you can these tools. It also depends on how you will execute tool. For example, some tools can be called directly from C# while other are Java or even XML based. Some can be called from command line other can’t.

Before I recommend the tool I HIGHLY recommend commenting the heck out of your code with Page and Page element you are working with. It is easy enough to write it and figure out what page you are and what element on that page you are working with when you are writing it for the first time because you are looking at it. However, when something changes and you have to modify it, it will be very time consuming to try to debug and/or figure out what you were doing. The reason is that often code becomes very cryptic because you are navigating through arrays of arrays or arrays or in general trying to get to things that can difficult to do.

Ok, enough lecturing, here is what I recommend:

Tool	HTML (No AJAX)	HTML (w/ AJAX)	Silverlight	Windows Desktop	Java Application	Language	Comment
White	N	N	Y	Y	Y	C#	This is my tool of choice for Silverlight or Windows Desktop or even Java applications. It reminds me of Watin (see below) as far as programming style and being easy and intuitive. Requires IE/FF. Don’t use the mouse when running, but can “help script” if gets stuck by moving mouse.
Watin	Y	Y	N	N	N	C#	In general this is the tool of choice for HTML with or without AJAX. Easy and intuitive to work with.
WebHarvest	Y	N	N	N	N	Java or Command Line	This is nice because it simulates browser so no browser needed, but this also means no support for AJAX or javascript. It is XML based and you don’t really write java code, you write XML, so it is all declarative programming. Kind of different, but quick and effective for plain HTML sites.
Selenium	Y	Y	* See Silverlight-Selenium	N	N	C#/Java, Command Line, and most popular languages.	I would try Watin first in most cases, but don’t discount Selenium either. It is a scalable solution that is supported by many languages and platforms and browsers. Has a recorder that can be useful. Has complete IDE. XPath based which I find not as intuitive or easy to debug as Watin, but can be effective at navigating a page.
Silverlight-Selenium	N	N	Y	N	N	C#	This still uses Selenium is actually just an extension to Selenium. I think White is easier to use and a bit more robust, but if White doesn’t work for your Silverlight app try this.

Using command line to control Windows Scheduled Tasks

2011-07-28T13:58:00.001-07:00

You can control, monitor, change, etc Windows Scheduled Tasks using schtasks from the command line. You can use it on your local machine or a remote machine such as a server.

In the examples below let’s assume that the Windows Scheduled Task you are interested in is called EventLogImport.

IMPORTANT: For any of the commands or examples below, you can add a /S and then the servername to execute the command against a remote machine running Windows.

Monitoring

Basic Info on local machine

schtasks /QUERY /TN EventLogImport

Basic Info for remote machine

schtasks /QUERY /S serverNameHere /TN EventLogImport

Verbose Info

schtasks /QUERY /V /TN EventLogImport

Verbose Info with CSV output to screen

schtasks /QUERY /FO CSV /V /TN EventLogImport

NOTE: Other options are TABLE and LIST. The /V is for Verbose.

To remove the header row you can use the MORE command to start reading at the second line

schtasks /QUERY /FO CSV /V /TN EventLogImport | more +1

Verbose Info with CSV output to file

This will clear the status.csv if it exists, else it will create a new file.

schtasks /QUERY /FO CSV /V /TN EventLogImport > c:\temp\status.csv

To append to the file if you call two or more of these in a row, use the >> instead of >.

schtasks /QUERY /FO CSV /V /TN EventLogImport >> c:\temp\status.csv

To remove the header row you can use the MORE command to start reading at the second line

schtasks /QUERY /FO CSV /V /TN EventLogImport | more +1 >> EventLogImportScheduledJobStatus.csv

Controlling

Disable a Scheduled Task

schtasks /CHANGE /TN EventLogImport /DISABLE

Enable a Scheduled Task

schtasks /CHANGE /TN EventLogImport /ENABLE

Other Commands

You can also create, delete, run, or kill scheduled tasks as well. For information on how to do this, I recommend typing schtasks /? at any command prompt.

For help on a specific switch, try one of the following:

SCHTASKS
SCHTASKS /?
SCHTASKS /Run /?
SCHTASKS /End /?
SCHTASKS /Create /?
SCHTASKS /Delete /?
SCHTASKS /Query /?
SCHTASKS /Change /?
SCHTASKS /ShowSid /?

Getting Started with Microsoft Charting

2011-07-26T14:43:00.001-07:00

Microsoft has provided a very nice charting package that is available on the WinForms and ASP.NET and is included (built into) .NET 4. It is also available for download with .NET 3.5. It is a VERY robust charting package that is extremely customizable. In fact, you can even modify the images before they are sent to the user (in the case of ASP.NET). I am sure there is something this package can’t do, but I have not ran into it yet.

It is actually quite easy to use, but it is best if you read through some examples. The best way to get started in my opinion is to follow the first link below. If you go through the tutorial you will be in good shape. Once you have done that, you may want to check the second link out for additional references. Next check the official docs when you need more info on specific properties, etc. The final link is really just a cool snippet of code to get you started in the right direction if you need to generate your own gradients for your charts; for example in a bar or column chart.

Using Microsoft’s Chart Controls In An ASP.NET Application: Getting Started (by Scott Mitchell)

Built-in Charting Control (VS 2010 and .NET 4 Series) – ScottGu’s Blog

Official Documentation

Post that shows basic idea of how to create your own gradient image that can then be used as a background in a chart.

Easily Disable Row Selection for a Silverlight DataGrid

2011-07-15T11:28:00.001-07:00

This technique actually prevents the row from being selected and prevents an unwanted cell from being selected. The visual effect is that the row or cell in that row never appears to be selected.

I assume you know how to load you DataGrid with data, etc. In my example, I only have one column of data. The trick to this solution is almost all in code behind. Also, this all assumes that you have set IsReadOnly="True" either in the XAML or the code behind.

In this example my DataGrid is named dgAnnouncements. I handle the SelectionChanged event as show in the method below.

private void dgAnnouncements_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
dgAnnouncements.SelectedItem = null;
}

The above gets us most of the way there, however you will notice that the cell itself can still be selected. To handle this we have to do a trick.

In the XAML for the DataGrid you will need to add an invisible column at position 0 so that we can select it whenever someone tries to select another cell that they can see.

Here is an example of the XAML for the DataGrid.

<sdk:DataGrid x:Name="dgAnnouncements"
ItemsSource="{Binding Data, ElementName=dsAnnouncements}"
AutoGenerateColumns="False"
IsReadOnly="True"
CurrentCellChanged="DataGrid_CurrentCellChanged"
SelectionChanged="dgAnnouncements_SelectionChanged">

    <sdk:DataGrid.Columns>
        <sdk:DataGridTextColumn Binding="{Binding Path=Nothing}" MinWidth="0" MaxWidth="0" />
        <sdk:DataGridTextColumn Binding="{Binding Path=Title}" />
    </sdk:DataGrid.Columns>
</sdk:DataGrid>

Here is the code behind to redirect the current cell to our invisible cell.

private void DataGrid_CurrentCellChanged(object sender, EventArgs e)
{
    if (dgAnnouncements.CurrentColumn != null)
    {
        dgAnnouncements.CurrentColumn = dgAnnouncements.Columns[0];
    }
}

Creating your own MessageBox in Silverlight

2011-07-14T13:33:00.001-07:00

The MessageBox built into Silverlight is nice enough to use for simple OK, Cancel scenarios, but sometimes I want to change the text of the buttons in the MessageBox. For example, I may want Yes, No instead of OK, Cancel or maybe completely different text like Finished Now, Finish Later. The syntax of the MessageBox is simple enough so I decided to mirror it as closely as I could. This makes it as painless as possible to change from a MessageBox to my message box. The biggest difference in usage that I couldn’t figure out how to get around is that the MessageBox is a blocking call, but the UI thread still continues to render. When I tried to do the same, the UI thread did not continue to render. I looked at the code for MessageBox and it appears to be calling a Windows native MessageBox that is most likely different for OS that Silverlight is implemented on. So, I decided that in the Silverlight manor I would use asynchronous calls (event handlers) instead of a blocking call. When I use delegates the code is similar to using MessageBox.

Okay, enough description I think. Here is source code for my version of MessageBox called MessageBox2

Download Source Code for Control

MessageBox2.xaml.cs MessageBox2.xaml

Usage

You’ll notice I have 3 Show() methods instead of the standard 2 that MessageBox has. The reason is that I added one so that you can specify the text of OK and Cancel buttons. You’ll notice that I don’t return a MessageBoxResult and instead return the MessageBox itself. Below is how you would use the MessageBox versus MessageBox2.

MessageBox

MessageBoxResult result = MessageBox.Show("This is a choice test", "Some caption", MessageBoxButton.OKCancel);
if (result == MessageBoxResult.OK)
{
DoSomethingUsefulHere();
}
else if (result == MessageBoxResult.Cancel)
{
DoSomethingUsefulHere();
}

MessageBox2

var msgBox2 = MessageBox2.Show("This is a choice test", "Some caption", MessageBoxButton.OKCancel, "Yes", "No");
msgBox2.OKButton.Click += delegate(object sender2, RoutedEventArgs e2) { DoSomethingUsefulHere(); };
msgBox2.CancelButton.Click += delegate(object sender2, RoutedEventArgs e2) { DoSomethingUsefulHere(); };

You could also use the Closed event and check the DialogResult to see if it was accepted or cancelled. Also, notice I changed the OK button to Yes and the Cancel button to No.

Reliable Sending of Email for.Net/C# using SQL Server

2011-06-28T09:45:00.001-07:00

If you want more reliable sending of emails from your .Net code you may think it is too much work to implement logging, retrying if smtp server is unavailable, control returned to user while email is sending, etc. The truth is that if you try to do it all via ASP.NET this can involve a lot of work. Thankfully, there is an easy way to meet all these requirements and not have to do much work at all.

As it turns out SQL Server has a robust facility for sending emails called Database Mail. It meets all the above requirements and these requirements are configurable. I have posted many entries on Database Mail such as resending emails, reviewing logs, etc. Just search on my blog for them. Much of the configuration comes from the Profile and Account settings you configure when you setup this info for your app.

Below is a C# method that you can add to your project. Calling this will allow you to send email reliably from your app and not have to worry about the delivery of it. it assumes that you have a class called Config with two static methods YourConnectionString and AllowEmailsToBeSent. You can hard code those obviously. The two methods just pull the values from the Web.config, but it could be anywhere in theory. The first is just your database connection string and the second controls whether the contents of the email will be written to a hard coded path on your harddrive for testing or it will be connect to SQL Server and actually send the email. This is useful for development / testing when you don’t want to disturb real users.

/// <summary>
/// Calls SQL Server Send Mail procedure, or logs to log file
/// </summary>
/// <param name="fromEmailAddress"></param>
/// <param name="toEmailAddresses">semi-colon separated list of recipients</param>
/// <param name="subject"></param>
/// <param name="body"></param>
public void SendEmail(string fromEmailAddress, string toEmailAddresses, string subject, string body)
{
    // this should never happen, but just so I will know about it
    if (string.IsNullOrEmpty(toEmailAddresses))
    {
        toEmailAddresses = "test@yourdomain.com";
    }
    // this should never happen, but just so I will know about it
    if (string.IsNullOrEmpty(fromEmailAddress))
    {
        fromEmailAddress = "test@yourdomain.com";
    }

    if (Config.AllowEmailsToBeSent)
    {
        // send email here
        using (SqlConnection conn = new SqlConnection(Config.YourConnectionString))
        {
            conn.Open();

            // use sql server database mail instead of .net mail
            // so that we get retry, logging, automatic batching, etc for free. Better reliability and recover.
            //-- for all parameters: http://msdn2.microsoft.com/en-us/library/ms190307.aspx
            SqlCommand cmd = new SqlCommand("msdb.dbo.sp_send_dbmail", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@profile_name", SqlDbType.VarChar).Value = "Your Profile name here";
            cmd.Parameters.Add("@recipients", SqlDbType.VarChar).Value = toEmailAddresses;
            cmd.Parameters.Add("@subject", SqlDbType.VarChar).Value = subject;
            cmd.Parameters.Add("@body", SqlDbType.VarChar).Value = body;

cmd.ExecuteNonQuery();
}

    }
    else
    {

        StreamWriter writer = new StreamWriter(@"c:\Email.log", true);
        using (writer)
        {
            writer.WriteLine("\r\n\r\nDate/Time Stamp: " + DateTime.Now.ToLocalTime());
            writer.WriteLine("From: " + fromEmailAddress);
            writer.WriteLine("To: " + toEmailAddresses);
            writer.WriteLine("Subject: " + subject);
            writer.WriteLine("Body: " + body);
        }
    }
}

Show JavaScript alert() or execute other code from Code-Behind

2011-06-27T15:39:00.001-07:00

I find that sometimes I need to show the user a JavaScript alert for something I don’t want them to be able to miss and I only want to show it based on some server-side (Code-Behind) code. This will work for most any chunk of JavaScript code as well.

Below is the code that you can put in your Page_Load()

if (!Page.ClientScript.IsClientScriptBlockRegistered("MyMadeUpNameHere"))
{
string jscript = "alert('test here');";
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "MyMadeUpNameHere", jscript, true);
}

Notice that MyMadeUpNameHere is some unique string that allows you to not have multiple copies of your code on the page. This ensures that you will only have it execute once. If you don’t want that functionality then don’t do the check.

In this example, the user will see the ‘test here’ in a JavaScript alert (Message Box).

Note, that the RegisterClientScriptBlock puts the JavaScript immediately below the opening form tag. If your code references any form elements or any other elements on the page this is most likely going to be too early since the page has not finished loading. This makes this method good for simple things like I did here that doesn’t really care about the page, or you can use it to register JavaScript functions that is executed later in the page or in an event handler.

If you want your code to execute LATER then I recommend using the RegisterStartupScript and IsStartUpScriptRegistered methods instead. This code executes at the end of the page after all the items on the page have finished loading. Here is the same code as above, but with the StartUp versions.

if (!Page.ClientScript.IsStartupScriptRegistered("MyMadeUpNameHere"))
{
string jscript = "alert('test here');";
Page.ClientScript.RegisterStartupScript(this.GetType(), "MyMadeUpNameHere", jscript, true);
}

Like I said before, if you had more complex logic you could put it earlier in the page and just execute the function using the RegisterStartupScript. You can also put your complex logic in functions in JScript include and use the RegisterClientScriptInclude. See here for more details on that.

I don’t know why I can never remember which methods to use for showing a JavaScript alert or executing other JavaScript code when the page loads, so I am writing in down for me and everyone else to have.

Get usage for all columns in a SQL Server database

2011-06-21T17:29:00.001-07:00

First if you just want to know how one column is used you want to use the UI or check out my other entry. If however you want to scan your entire database and determine the dependencies (usage) of each and every column in the database then stay here. The code found in either of these places will only look at the database references (stored procedures, functions, views, triggers). This means if you are using something like SSIS, LINQ, Entity Framework, embedded SQL in your code, etc you will need to check these area on your own.

With that said, I really just continued the thought from my other entry (same as the one I noted above). The first step is to convert the stored procedure used there to one that just dumps the data to a table for later querying. I call this table ColumnUsage. I also add a TagName to the table so that you can easily identify your results among other results in that table. That way you can compare results over time, or just support multiple users. The table will be created the first time the stored procedure is executed.

Here is the new stored proc

/****** Object: StoredProcedure [dbo].[UTL_07_WriteColumnUsageToColumnUsageTable] Script Date: 06/21/2011 15:25:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[UTL_07_WriteColumnUsageToColumnUsageTable]
    @vcTableName varchar(100),
    @vcColumnName varchar(100),
    @tagName varchar(100)
AS
/************************************************************************************************
DESCRIPTION:    writes all stored procedures, views, triggers
        and user-defined functions that reference the
        table/column passed into this proc into the ColumnUsage table.

PARAMETERS:
        @vcTableName - table containing searched column
        @vcColumnName    - column being searched for
        @tagName - a name you make up to so you can later query for your results

This procedure must be installed in the database where it will
be run due to it's use of database system tables.

USAGE:

UTL_07_WriteColumnUsageToColumnUsageTable 'schema.tablename', 'columnname', 'tagName'

AUTHOR:    Karen Gayda

DATE: 07/19/2007

MODIFICATION HISTORY:
WHO        DATE        DESCRIPTION
---        ----------    -------------------------------------------
Brent Vermilion    06.21.2011    Recreated such that writes to a table instead of output
*************************************************************************************************/
SET NOCOUNT ON

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'ColumnUsage'))
BEGIN
    CREATE TABLE dbo.ColumnUsage
        (
        ID int NOT NULL IDENTITY (1, 1),
        TableName varchar(500) NOT NULL,
        ColumnName varchar(500) NOT NULL,
        UsedByType varchar(50) NOT NULL,
        UsedByName varchar(2000) NOT NULL,
        TagName varchar(100) NOT NULL
        ) ON [PRIMARY]

    ALTER TABLE dbo.ColumnUsage ADD CONSTRAINT
        PK_ColumnUsage PRIMARY KEY CLUSTERED
        (
        ID
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

END

insert into dbo.ColumnUsage(TableName, ColumnName, UsedByType, TagName, UsedByName)
SELECT DISTINCT @vcTableName, @vcColumnName, 'Stored Procedure', @tagName, SUBSTRING(o.NAME,1,60) AS [Procedure Name]
        FROM sysobjects o
        INNER JOIN syscomments c
            ON o.ID = c.ID
        WHERE     o.XTYPE = 'P'
            AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%'
    ORDER BY [Procedure Name]

insert into dbo.ColumnUsage(TableName, ColumnName, UsedByType, TagName, UsedByName)
SELECT DISTINCT @vcTableName, @vcColumnName, 'View', @tagName, SUBSTRING(o.NAME,1,60) AS [View Name]
        FROM sysobjects o
        INNER JOIN syscomments c
            ON o.ID = c.ID
        WHERE     o.XTYPE = 'V'
            AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%'
    ORDER BY [View Name]

insert into dbo.ColumnUsage(TableName, ColumnName, TagName, UsedByName, UsedByType)
SELECT DISTINCT @vcTableName, @vcColumnName, @tagName, SUBSTRING(o.NAME,1,60) AS [Function Name],
        CASE WHEN o.XTYPE = 'FN' THEN 'Scalar Function'
            WHEN o.XTYPE = 'IF' THEN 'Inline Function'
            WHEN o.XTYPE = 'TF' THEN 'Table Function'
            ELSE '? Function'
        END
        as [Function Type]
        FROM sysobjects o
        INNER JOIN syscomments c
            ON o.ID = c.ID
        WHERE     o.XTYPE IN ('FN','IF','TF')
            AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%'
    ORDER BY [Function Name]

insert into dbo.ColumnUsage(TableName, ColumnName, UsedByType, TagName, UsedByName)
SELECT DISTINCT @vcTableName, @vcColumnName, 'Trigger', @tagName, SUBSTRING(o.NAME,1,60) AS [Trigger Name]
        FROM sysobjects o
        INNER JOIN syscomments c
            ON o.ID = c.ID
        WHERE     o.XTYPE = 'TR'
            AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%'
    ORDER BY [Trigger Name]

Now that we have that we can write another stored proc that will loop over every column in our database and call the above stored procedure for each column. Please note, depending on how complex your schema, the number of stored procedures, number of columns, etc this query could take quite a long time. Here is the stored proc to scan your database.

/****** Object: StoredProcedure [dbo].[UTL_08_WriteAllColumnUsageToColumnUsageTable] Script Date: 06/21/2011 15:25:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[UTL_08_WriteAllColumnUsageToColumnUsageTable]
    @TagName varchar(100)
AS
/************************************************************************************************
DESCRIPTION:    writes all stored procedures, views, triggers
        and user-defined functions that reference the
        table/column passed into this proc into the ColumnUsage table.

PARAMETERS:
        @TagName - a name you make up to so you can later query for your results

This procedure must be installed in the database where it will
be run due to it's use of database system tables.

USAGE:

UTL_08_WriteAllColumnUsageToColumnUsageTable 'tagName'

AUTHOR:    Brent Vermilion

DATE: 06/21/2011
*************************************************************************************************/

Declare @TableName as nvarchar(512)
Declare @ColumnName as nvarchar(300)

Declare ColumnCursor CURSOR FAST_FORWARD FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS order by TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME

OPEN ColumnCursor
FETCH NEXT FROM ColumnCursor
into @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
-- do row specific stuff here
exec UTL_07_WriteColumnUsageToColumnUsageTable @TableName, @ColumnName, @TagName

FETCH NEXT FROM ColumnCursor
into @TableName, @ColumnName
END

CLOSE ColumnCursor
DEALLOCATE ColumnCursor

After executing the either of the stored procedure on your database you can answer some very useful questions.

What columns are not being used?

select distinct allcols.TABLE_SCHEMA + '.' + allcols.TABLE_NAME, COLUMN_NAME, UsedByName from
INFORMATION_SCHEMA.COLUMNS allcols
left outer join ColumnUsage usage
on (allcols.TABLE_SCHEMA + '.' + allcols.TABLE_NAME = usage.TableName and allcols.COLUMN_NAME = usage.ColumnName)
where UsedByName is null

What is using a particular column?
select * from ColumnUsage where TableName = 'MyTableHere' and ColumnName = 'MyColumnHere'

Deploying RIA Services seems a bit too complicated

2011-06-17T14:05:00.001-07:00

I have spent many hours troubleshooting deployments my Silverlight application (server side) just to figure out that I have the wrong version of a .dll. It seems that the easiest way to guarantee that you have the correct assemblies is to open your web project and select all the References and change the Copy Local property to True. I didn’t notice more than a second or two delay in build time and then when I deploy I know that I will get all the assemblies I need.

Generally, you don’t need to worry about the references changing except when you get a service pack, a new version, upgrade, etc so if you like you can keep the Copy Local set to False most of the time. Then when your references change be sure to change the Copy Local back to True.

I’m so lost as to why this is so difficult.

If anyone has a better way, please let me know. What price can you put on sanity?

Renaming a Visual Studio Project

2011-06-15T16:31:00.001-07:00

I had a project I wanted to rename from ProjectA to ProjectB. I wasn’t quite sure how to do this. I mean, I wanted the namespaces to change, the default namespaced (used for new files), project file names, filenames, etc. In my case, I had multiple projects in my solution. I wasn’t sure how this would work.

In the end, I used a search and replace tool to change every reference in every fiel from ProjectA to ProjectB. Then I renamed all the files. To find the files that need to be renamed, you might want to check here. Then I opened the solution up in Visual Studio 2010 (VS2010) and it just worked! So cool! Thanks to most files being text or XML this worked seamlessly.

Using Batch File or Command Line to recursively find all files that have a filename that contains a particular string

2011-06-15T14:49:00.001-07:00

It sounds like a tall order at first, but then it also sounds like something that should be built into Windows. In particular, I am using Windows 7, but I assume it works on XP as well.

The solution is actually quite simple and is indeed built into Windows 7.

dir /s /b *my string*

This will give you something similar to:

c:\temp\This is a test of my string.txt

c:\mydocs\My string could be found here.txt

As shown above it will search the current directory and all directories below the current one for files that contain ‘my string’. You can also specify something like this to specify the directory you want to start searching in regardless of your current directory.

dir /s /b “c:\mydocs\*my string*”

You can use all the standard wild card syntax such as the following to search only .html files.

dir /s /b “c:\mydocs\*my string*.html”

In case you are wondering the /s is for recursive and the /b is to return just the path\filename (no file info such as date, size, etc).

Easy Way to remove Visual Studio 2010 project from Visual SourceSafe

2011-06-15T13:17:00.001-07:00

Visual SourceSafe creates .scc files when you add a Visual Studio 2010 (VS2010) project to Visual SourceSafe. The easiest way I know to remove a project from Visual SourceSafe version control (while still leaving the actual files in SourceSafe) is to do the following. You might want to do this if you want to make a copy of your project to use as a starting point for your new project or maybe want to share the project with someone else or maybe you are changing version control systems. Regardless, below are the steps to do this.

Make a copy of your project using copy and paste in Windows Explorer.
Recursively Search the project directory for all files that end in .scc. You can do this using the Search in Windows 7 or Search Companion (I think it was called in XP).
Delete the results found.
Open solution in VS2010. It will complain and eventually ask if you want to permanently remove the bindings. Do so for each project in your solution. You can go to the File menu | Source Control | Change Source Control… If there are any projects that are still bound, you can unbind them.

That’s it. Relatively easy actually.

Compiler error with VS2010 SP1 and SL 4 with WCF RIA Services and Entity Framework 4

2011-06-15T11:52:00.001-07:00

Did you have a VS2010 project (before installing VS2010 SP1 or RIA Service Pack 1) that used Silverlight 4 and WCF RIA Services and Entity Framework 4 (EF4) and now that VS2010 Service Pack 1 is installed (or maybe some other framework) your Silverlight Project no longer compiles. Chances are if you are using RIA Services you may have created files that you want to share (called Shared Code)on the Server and the SL Client. This is done in RIA by using the .shared.cs file extension on a filename.

After I installed either VS2010 SP1 or RIA Services SP1 and then I tried to compile my project and got a compiler error similar to:

error CS0102: The type 'MyApp.Model.MyClassHere' already contains a definition for 'MyPropertyHere'

I learned that this may be a RIA Services SP1 regression bug from this forum. The beta had issues also.

It appears that this MAY be fixed now, but I’m not sure since I don’t have an issue exactly like what they had. I only have the issue if I have to shared files and one the classes (in one of the files) has a property or method that has a return type that is the other class type. If I get rid of that dependency and just return the object type instead then it compiles fine. While returning the type object, I have to cast every time I use it and that is silly and kind of a pain / ugly.

I hope that helps someone. If anyone else has this issue I’d love to know about it.

How to Download File from inside an UpdatePanel in ASP.NET

2011-06-03T18:57:00.001-07:00

PartialPageRendering when used with the UpdatePanel is a great thing in general. One problem comes when you want to mess with the Response and call things like End which is required when you return want to return a file instead of the page you were on. An example of this is a GridView with a Download button below it. If you are not using the UpdatePanel then there is no problem and clicking the Download button will all you to download the file as expected. There are lots of posts on the web on how to do this so I won’t go into the code. However, it involves calling things like Response.Clear(), Response.ContentType, Response.AddHeader(), Response.End().

Now let’s assume you put the GridView in an UpdatePanel. All those Response calls noted above are things that the PartialPageRendering and UpdatePanel don’t know what to do with because they were expected something like the page that made the request. So we need to work around the issue. I found some solutions on the internet, but didn’t really find them very easy or straight forward. So, I came up with my own.

Solution 1

One solution is to put the Download button outside the UpdatePanel. This tends to work best, but is not always possible.

Solution 2

If you can’t do solution 1, then consider faking it. What I mean is move the Download button which in my case is implemented as a LinkButton outside the UpdatePanel and hide it using css. Then add a Hyperlink inside the UpdatePanel. Now, on your page load you can set the hyperlink to have the same postback url as the LinkButton that is outside the UpdatePanel. That way when you click the hyperlink inside the UpdatePanel you are effectively doing the same thing as if you clicked the hidden LinkButton that is outside the UpdatePanel. Fairly simple.

Here is the snippets of code to help you see what I am talking about.

Here is the HyperLink that is inside the UpdatePanel and is also the button that you will be clicking in the Browser.

<asp:HyperLink ID="btnExportToExcelClicker" runat="server" Text="Export all rows to Excel" />

Here is the LinkButton that is invisible to the user and is outside the UpdatePanel

<asp:LinkButton ID="btnExportToExcel" runat="server" onclick="btnExportToExcel_Click" Text="Export all rows to Excel" style="display:none;" />

Here is the code-behind. In my code btnExportToExcelClicker is the button that is in the UpdatePanel and btnExportToExcel is the button that is outside the UpdatePanel. You will actually be clicking btnExportToExcelClicker because btnExportToExcel is hidden.

protected void Page_Load(object sender, EventArgs e)
{
btnExportToExcelClicker.NavigateUrl = Page.ClientScript.GetPostBackClientHyperlink(btnExportToExcel, null);
}

This technique uses GetPostBackClientHperlink and can be expanded for other purposes and other controls. In some cases you may need to consider using GetPostBackEventReferences, though I haven’t tried it.

Programmatically Setting Header on ASP.NET GridView

2011-05-26T15:49:00.001-07:00

It is actually very frustrating to change the header text on an ASP.NET GridView if you don’t do it in the right place. At first, I thought I would do it in the PreRender event as I figured that was plenty late in the cycle and should stick. Then I noticed it would not stick when I would sort for example.

In the end, it was very easy once I figured out what the proper event in the page lifecycle to use. You answer to this riddle is the RowCreated event. My GridView is called GridView1 in this example. I added onrowcreated=”GridView1_RowCreated” to my GridView in my .aspx file. In my code behind this is the code I used.

protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.Header && e.Row.Cells.Count > SOME_COLUMN_INDEX)
    {
        e.Row.Cells[SOME_COLUMN_INDEX].Text = "New Header Text here";
    }
}

Some things to know. You can’t reference the HeaderRow because it is still null at this point. It is there, but not accessible via the GridView1.HeaderRow property. You need to use e.Row instead. You can be guaranteed that this is not null since we are its event handler. You may also want to make sure the cell you are trying to set is not out of bounds. As long as you check to make sure you are in the header row you should be ok though. An example of when you would not have cells is when you get the EmptyData row which is displayed only when there are no rows to display. In that case, there are no headers. So, in general, I found it safe to always check to make sure the item I assume is in the array is actually in the array.

Programmatically adding Query Parameter to ASP.NET DomainDataSource

2011-05-18T17:01:00.001-07:00

I love the new ASP.NET DomainDataSource because it allows me to talk to my Domain Service.

You could do this declaratively like the following:

Declaratively

<asp:DomainDataSource ID="dsDetails" runat="server">
<QueryParameters>
<asp:ControlParameter ControlID="GridView1" PropertyName="SelectedValue" Name="id" Type="Int32" />
</QueryParameters>
</asp:DomainDataSource>

In this example, this a DomainDataSource that I have connect to a FormView. I want the FormView to update when a GridView called GridView1.SelectedValue changes. I am setting the QueryName and the DomainServiceTypeName in my Page_Init so you don’t see it in the declaration above, but you could put it there as well.

In this scenario, the Query in my Domain Service is expecting a parameter called id. This must the Name property of the ControlParameter control.

Programmatically (Good Option)

We can do the same thing with the parameters, but this time do it programmatically. The declaration would now look like this:

<asp:DomainDataSource ID="dsDetails" runat="server">
</asp:DomainDataSource>

Notice there is not QueryParameters declared, so we need to do this in code. The best place is in the Page_Init() method since it is very early in the page lifecycle.

Add the following line to the Page_Init() method:

dsDetails.QueryParameters.Add(new ControlParameter("id", TypeCode.Int32, "GridView1", "SelectedValue"));

This will do exactly the same as doing it declaratively, but now you nave more control of all the values passed since you are in code. One thing to point out is that when the GridView1.SelectedValue changes, the dsDetails datasource executes the query again automatically. That is why I like this method.

Programmatically (Okay Option)

The above is identical and great and my first choice. This approach here works best if you are getting your value from something that doesn’t change like the QueryString. If you need to set the current user, or some other value (like a querystring value) and don’t care that the dsDetails DomainDataSource will NOT be updated automatically and you will instead need to tell it to databind using something like SelectedItemChanged in the GridView1, you can use the Querying event on the DomainDataSource. In this case you would do the following.

<asp:DomainDataSource ID="dsEditor" runat="server"
onquerying="dsEditor_Querying" >
</asp:DomainDataSource>

protected void dsDetails_Querying(object sender, Microsoft.Web.UI.WebControls.DomainDataSourceQueryingEventArgs e)
{
e.QueryParameters.Clear();
e.QueryParameters.Add("id", GridView1.SelectedValue);
}

Why aren’t my ForeignKeys being loaded on my Custom Dynamic Data page?

2011-05-04T17:33:00.001-07:00

This is one entry in a series of blog entries:

If you look at Part I and Part II, you may have noticed that the Product Category is a textfield when it should be a Drop Down List of the Product Categories. If this were a page that was in the DynamicData\CustomPages directory then you would not be having this problem. The reason we are having this problem is because we are calling EnableDynamicData() on our FormView which is great to get basic dynamic data functionality, but it doesn’t know how to load all the metadata for the Product entity. If you debug you will see that the ProductCategory DynamicControl column is not of type MetaForeignKeyColumn. After futher debugging you will see that much of the other metadata from the model is actually missing as well. Thankfully, the fix is very easy.

Change from:

protected void Page_Init(object sender, EventArgs e)
{
FormView1.EnableDynamicData(typeof(MyDynamicDataExample.Models.Product));
}

to this:

protected void Page_Init(object sender, EventArgs e)
{
// to get all the goodness like foreign-keys, etc we need to tell it to use our model
FormView1.SetMetaTable(Global.DefaultModel.GetTable(typeof(MyDynamicDataExample.Models.Product)));
}

Also, from what I can tell the AutoLoadForeignKeys=”true” attribute of the DynamicDataManager has no bearing on whether the Drop Down List is populated or not.

You may have also noticed that the ProductCategory field when in ReadOnly mode shows a link with an number showing instead of the nice text description of the product category. Luckily, this is an easy fix as well. In the AWDomainService.cs find we need to change the GetProducts() method to include the ProductCategory navigation property. This will cause the SQL that is generated to bring in the ProductCategory table.

Change from:

public IQueryable<Product> GetProducts()
{
return this.ObjectContext.Products.OrderBy(o => o.Name);
}

to this:

public IQueryable<Product> GetProducts()
{
return this.ObjectContext.Products.Include("ProductCategory").OrderBy(o => o.Name);
}

The change above will do the trick. However, if you are using this Domain Service with a Silverlight Client, you should also update the AWDomainService.metadata.cs such that the ProductCategory property on the ProductMetadata class has the Include() attribute. If you don’t have both of these Silverlight will not populate the ComboBox (assuming that is what you are using in Silverlight).

Change from:

[Display(Name = "Product Category")]
public ProductCategory ProductCategory { get; set; }

to this:

[Include()]
[Display(Name = "Product Category")]
public ProductCategory ProductCategory { get; set; }

You can download the complete source here.

Create CRUD UI using ASP.NET 4.0, FormView, Dynamic Data, DomainDataSource, Entity Framework, WCF Domain Service, LINQ, and custom validation – Part II

2011-04-29T13:58:00.001-07:00

This is one entry in a series of blog entries:

Adding Labels based on MetaData from Model

In Part I we did some pretty neat stuff with Dynamic Data. In this blog entry I will show how to have your labels be set to what is set in the MetaData.

Let’s do some additional clean up here. Let’s remove the ProductCategoryID and ProductModelID fields since they will never be used.

Next we need to replace static text labels with a Label control so that we can set it via code. For example, replace ProductID with

<asp:Label ID="lblProductID" runat="server" AssociatedControlID="ProductIDDynamicControl" OnLoad="LoadLabelForDynamicControl"></asp:Label>

I recommend a consistent naming convention to make remembering control ids. Once you have replaced one template such as the EditItemTemplate you can copy it to the InsertItemTemplate and the ItemTemplate, but be sure to change the Mode property to Insert and ReadOnly respectively.

Since we will have to get the metadata for each column on the FromView we will create a method that does this for us. Below is that method.

/// <summary>
/// Assuming you have a Label and DynamicControl (with the AssoicatedControlID on the label control set to the DynamicControl)
/// on your FormView it will set the text of the label control to the DisplayName which is from the MetaColumn for the column
/// used by the DynamicControl.
/// </summary>
/// <param name="labelControl">The label control that will have its text changed</param>
private void SetLabelBasedOnMetaData(Label labelControl)
{

    string dynamicControlID = labelControl.AssociatedControlID;
    if (string.IsNullOrEmpty(dynamicControlID)) throw new Exception("The AssociatedControlID must be set for Label control with ID: " + labelControl.ID);

    var container = labelControl.NamingContainer;
    var userControl = container.FindControl(dynamicControlID) as DynamicControl;
    if (userControl == null) throw new Exception("Could not find dynamic control with id: " + dynamicControlID);

labelControl.Text = userControl.Column.DisplayName;
}

This will be called from the OnLoad event for each label.

protected void LoadLabelForDynamicControl(object sender, EventArgs e)
{
Label labelControl = sender as Label;
SetLabelBasedOnMetaData(labelControl);
}

If you run this now you will see that there is no real difference from the user’s perspective. However, the labels are being pulled from the MetaData (the Model). Now we just need to specify the label we want to display. This is the same label that is used in generated error messages.

To specify the labels, open the AWDomainService.metadata.cs and find the property you want to set the label for and add the following line.

[Display(Name="Product ID")]
public int ProductID { get; set; }

Do this for each property. Re-run and this time you will see that the labels are the values you set in the Display attribute.

It is a little more work, but in the end your labels will always be in sync from screen to screen and your error messages will reflect the name as well. When you need to make a change, you just do it once in your model. It is also the same information that your scaffolded screens will use as well.

All source for this project can be downloaded here.

Create CRUD UI using ASP.NET 4.0, FormView, Dynamic Data, DomainDataSource, Entity Framework, WCF Domain Service, LINQ, and custom validation – Part I

2011-04-28T17:46:00.001-07:00

This is one entry in a series of blog entries:

The title is a mouthful and thus the topic can be kind of overwhelming. With ASP.NET 4 there are so many different technology frameworks that you need to be familiar with. The problem that I have found is that most examples only use a one or two of these frameworks. In my world, I want to use them all and the examples are never as simple as the examples. In this blog entry, I will give a read world example of how to combine all these technologies. I find myself having to figure out how to do this over and over again. Hopefully, this will be of help to others.

This blog entry is NOT about how to create a Dynamic Data project in VS2010 and create an admin UI or something like that. This is about creating a page from scratch that uses this technology. To better understand the different scenarios that Dynamic Data can be used in and steps on how to extend it, see here.

Technology Overview

I am writing in the context of ASP.NET 4.0 and VS2010 (Visual Studio 2010). These technologies are relatively new in some cases and the api’s have changed since betas. I am only showing stuff that works in VS2010 (the final released version).

ASP.NET 4.0 – This is the version of ASP.NET that I am targeting.
FormView – This is the control that we will be using as a basis for the CRUD UI (User Interface).
Dynamic Data – This is the technology that allows you to use DynamicControl on your FormView instead of the standard ASP.NET Forms controls. BTW, you don’t have to have anything installed (not even the Dynamic Data files and templates). You get basic implementation without them, but with them you can customize and get a more full-featured set of tools. See here for a GREAT video on using Dynamic Data in “old” applications that don’t have all these cool technologies. For the most up to date resources, click here.
Entity Framework 4.0 – The standard with VS2010 for database to object mapping. Click here if you want to see how to use Dynamic Data with an ObjectDataSource and GridView.
WCF Domain Service – This is your middle tier where you would put your business logic, complex validation, etc. It provides a consist way to access your perform CRUD operations on your data. Abstraction layer before the Entity Framework.
LINQ – We will use LINQ to Entities in the WCF Domain Service to query database through the Entity Framework.
DomainDataSource – This is a control that works much like the ObjectDataSource or LinqDataSource or EntityDataSource except that it can connect to your WCF Domain Service from ASP.NET. Please note that there is also one for Silverlight and it has the same name so when you are Googling be sure to check what the context is. This is a good doc on using this control.

Getting Started

You can select most any type of web application project in VS2010 to get started. My instructions will be for the Dynamic Data project so that I don’t have to explain how to manually move over the Dynamic Data files. If you have an existing project and need to copy over the Dynamic Data files, click here for detailed instructions. Please note, I have copied some of these steps (and written others) from instructions I have read on MSDN, but have combined them together in one continuous instruction set. For example, most of the instruction are from: here and here.

Open Visual Studio 2010 and create a new project of type ASP.NET Dynamic Data Domain Service Web Application. I’m calling my project MyDynamicDataExample.
Optional - Create two Directories: Models and Services.
Optional - Download and install the AdventureWorksLT database if you don’t already have it. The particular database is not really that important to understand the concepts. Notice I am using the Lite version since it is a bit simpler to follow, etc.
Add a New Item… and select ADO.NET Entity Data Model. I called MyModel.edmx. We will generate from Database. Select the database you want to work with (add a new connection if it is not there). On the Choose Your Database Objects screen, select Product and ProductCategory. Keep everything else default.
You must build your project so that the next step will work.
Add (to Services folder) a New Item… and select Domain Service Class. I called mine AWDomainService.cs. Select the options as shown below:
Add (to Models folder) a New Item… and select Web Form using Master Page. Call it DDTest.aspx. Select the Site.master on the next screen.
From the General or Data group of the Toolbox, add a DomainDataSource control to the page. If you have never used the DomainDataSource before you will need to add it to your toolbox. To add it to your toolbox. Click the Tools menu, and then click Choose Toolbox Items. In the Choose Toolbox Items dialog box, click the .NET Framework Components tab, select the DomainDataSource check box, and then click OK.
Add a DomainDataSource to the page. Configure the datasource to point to GetProducts() method on the service you created. Enable Inserts, Updates and Deletes.
Add a QueryExtender to the page. Set the TargetControlID to the id of the DomainDataSource (mine is called DomainDataSource1).
Add a FormView (though a GridView or DetailsView could also be used). Check the Enable Dynamic Data Support checkbox. Please note it will not stay checked when you come back to it later (in some cases). Set the Data Source to the DomainDataSource you added earlier.
Add a DynamicDataManager control to the page. Chose Register Controls… and add the FormView as shown below.

This registers the data-bound control and enables dynamic behavior for it.
Open the code-behind. Add a Page_Init method and add the following line:

protected void Page_Init(object sender, EventArgs e)
{
FormView1.EnableDynamicData(typeof(MyDynamicDataExample.Models.Product));
}
Go to the .aspx page again and go to the Source view. Find the QueryExtender you added earlier. Add the following line to it:

<asp:QueryExtender ID="QueryExtender1" runat="server" TargetControlID="DomainDataSource1">
<asp:DynamicRouteExpression ColumnName="ProductID" />
</asp:QueryExtender>

This will allow us to pass the ID that we want to edit via the url as a query string.
Now we need to do some clean up. In each of the templates in the FormView remove the Text and Dynamic controls for: ProductCategoryReference, EntityState, and EntityKey. Also, remove the rowguid from the insert template.
Let’s test to see if this works now. You will see that you get the YSOD (Yellow Screen Of Death) when you click the Edit button and then the Update button. You will get an EntityOperationException and the last method that was called was HandleValidationErrors. This means that something in the model failed.
Create event handlers on the DomainDataSource for the Updated and Inserted events. They should look like this when you are done:

protected void DomainDataSource1_Inserted(object sender, Microsoft.Web.UI.WebControls.DomainDataSourceStatusEventArgs e)
{
    if (e.ChangeSetEntry.HasError)
    {
        foreach (var error in e.ChangeSetEntry.ValidationErrors)
        {
            AddValidationSummaryItem(error.Message);
        }
    }
}

protected void DomainDataSource1_Updated(object sender, Microsoft.Web.UI.WebControls.DomainDataSourceStatusEventArgs e)
{
    if (e.ChangeSetEntry.HasError)
    {
        foreach (var error in e.ChangeSetEntry.ValidationErrors)
        {
            AddValidationSummaryItem(error.Message);
        }
    }
}
Next add this supporting method.

public void AddValidationSummaryItem(string errorMessage)
{
    var validator = new CustomValidator();
    validator.IsValid = false;
    validator.ErrorMessage = errorMessage;
    this.Validators.Add(validator);
}
Now put a breakpoint on the Updated event handler and debug your application. Do the same test again, you’ll see there is some error about the field: ThumbnailPhoto. Note that the error does not display on the page, but it doesn’t go to the YSOD either. Like any other validation exception that is NOT column specific, we need to use the ValidationSummary control to view this error.
Drag a ValidationSummary control to your page. Re-run and you’ll see the exception. This does give little bit of database schema information. This may be considered by some to be bad. I’ll leave that up to you. If you don’t do this then you won’t get validation exceptions that are thrown from your domain service which is how you will implement your business logic. My experience shows that nothing too serious comes out of here once you have everything wired up correctly. If you don’t like that you’ll have to come up with another solution.
Let’s quickly fix this by removing the ThumbNailPhoto field from each of the FormView templates. Re-run, and this time you won’t get any errors. We can do this because it is an optional field.
Let’s add some validation to see how to do that. Go to the AWDomainService.cs and find the UpdateProduct method. Modify it so that it looks like this:

public void UpdateProduct(Product currentProduct)
{
    this.ObjectContext.Products.AttachAsModified(currentProduct, this.ChangeSet.GetOriginal(currentProduct));
    if (currentProduct.ListPrice < 2000)
        throw new ValidationException("List Price is too low.");
}
Re-run, change the List Price to less than 2000 and update and you’ll get the message shown in the ValidationSummary control.
You can also use ValidationAttributes such as Required, Range, RegularExpression, etc in the Model to declaratively validate the data. To do this open your AWDomainServices.metadata.cs. Find the StandardCost property and modify it so that it looks like this:

[Range(0, 200, ErrorMessage="{0} must be between 0 and 200.")]
public decimal StandardCost { get; set; }

Re-run and and change the standard cost to something greater than 200 if it isn’t already and Update. You’ll see it says StandardCost must be between 0 and 200. Notice it filled in the name of the field for us. It is the actual property name though.
To display a user-friendly version of the property name you can add Display information as shown below.

[Display(Name="Standard Cost")]
[Range(0, 200, ErrorMessage="{0} must be between 0 and 200.")]
public decimal StandardCost { get; set; }

Re-run and you’ll see the same message but with the friendly name of the property.
To Edit a particular record such as the Product with ID = 800, just go to the same url, but with DDTest.aspx?ProductID=800.
If you try to run your application without specifying the specific test page you will get an error like this “There are no accessible tables. Make sure that at least one data model is registered in Global.asax and scaffolding is enabled or implement custom pages.”

To fix that you just need to go to your Global.asax.cs and uncomment the line that start with DefaultModel.RegisterContext. Change it so that it looks like this:

DefaultModel.RegisterContext(new DomainModelProvider(typeof(MyDynamicDataExample.Services.AWDomainService)), new ContextConfiguration() { ScaffoldAllTables = true});

When you do this you’ll have some CRUD pages for each of the tables you included. This is separate from what we are doing here which is a custom page, but let’s fix the errors anyway. Another option would be to not have this default page and leave that line above commented. That way you won’t have all these admin page open to the world. Yes you can secure them, but they are not by default.
Back on track, when you access the Default.aspx page you will get an error like this: “The method 'Skip' is only supported for sorted input in LINQ to Entities. The method 'OrderBy' must be called before the method 'Skip'.”

To fix this just go to the AWDomainService.cs and add a sort method to the default query methods which are GetProducts and GetProductCategories. Just make the methods look like these:

public IQueryable<ProductCategory> GetProductCategories()
{
return this.ObjectContext.ProductCategories.OrderBy(o => o.Name);
}

public IQueryable<Product> GetProducts()
{
return this.ObjectContext.Products.OrderBy(o => o.Name);
}
Re-run. This time go to the Product link. The same validation we added before is also here. This is because the validation is written at the Model, not the UI. This is AWESOME in my opinion. I love it. This is why I love Dynamic Data.

I hope this has been beneficial to all.

The entire source for this example (minus the AdventureWorksLT database) can be downloaded from here.

AzGroups 8th Annual Scott Guthrie Event

2011-04-26T16:05:00.001-07:00

Things to check out:

NUGET

(pronounced NEWGET) – it is built into VS2010 (Visual Studio 2010) and makes finding, downloading, and installing libraries (open source included) into your project. It even does some configuration in the config files, etc. Pretty awesome. You can get it here.

elmah

It is an error logging facility that is completely pluggable that can added to a running ASP.NET web application without re-compilation or deployment. When an exception occurs it logs it, but allows you decide where to log it to such as a database, RSS, email, etc. It also records A LOT of VERY useful information for determining what happened. See here for more details.

IIS Express

Can be used side-by-side with the built in web server (Cassini) in VS2010. It launches fast just like the web server built into VS2010, but doesn’t require you setup virtual directories, etc like you do with IIS (full version). You now get best of both worlds. It now let’s you develop using the IIS 7.x feature-set. It is now as easy to use as the web server built into VS2010. It doesn’t require admin account. You don’t have to change any code or the way you work to take advantage of it. See Scott’s blog for more details.

Web Matrix

You should still continue to use Visual Studio for professional work, but Web Matrix project are great for hobby, family sites, etc. It has been totally reworked to be a task based tool. It makes publishing very easy. If the site gets more complex the project can be opened up in Visual Studio.

SQL Server Compact Edition 4

It is great for ASP.NET development. It is light weight and free. It is an embedded database (no SQL Server required) so it is in the binaries (bin directory) that you can xcopy to your server. You can use Standard SQL Server syntax so anything that supports the ADONET provider model (such as LINQ and EF) will work. You are also licensed to redistribute it. All this means you can now deploy this in a medium trust ASP.NET shared web hosting such as DiscountASP, GoDaddy, etc. This means you don’t have to pay for the SQL database. It integrates into VS2010 (coming soon). It is different from previous version because it is safe to use with multi-threaded environments (such as ASP.NET). and no longer blocked from being used with ASP.NET. The catch is that it is for light-usage production (4GB or less for datafile) usage scenarios and it does NOT support stored procedures. For high-volume usage usage, you’ll probably want to use SQL Server Express (which is still free), SQL Server, or SQL Azure. Also, since stored procs are not supported the ASP.NET Membership is not supported (though they will be shipping new providers so that they do). The only change to your code when you migrate is your configuration string. See Scott’s blog for more details.

Portable Library Project

It is a special kind of project in VS2010 that only allows code that will work on particular targets. In particular, the targets are Silverlight and Silverlight for Window Phone 7. Intellisense only lets you see Intellisense on methods, properties, classes, namespaces, etc that are compatible with both. The most I could find on it was here.

Silverlight 5

Great stuff coming with Silverlight 5. Better DataBinding support for MVVM. Improved Printing and Text. Finally there will be a 64-Bit version of Silverlight. 3D Modeling that use Hardware acceleration. 3D and 2D can be used together. Breakpoints on XAML Bindings.

MVC 3

I am really impressed with MVC3. I am very tempted to try a project with MVC3. It takes advantage of HMTL 5 including Data validation, round corners, etc.

MVC Basics

The basic idea is that a url is mapped to a controller and method on that controller. That method makes calls to the model where most of the business logic resides, and then typically returns a view which is responsible for rendering the response (page). It maps url to action methods on controller class. In general the best practice with MVC is to have skinny controllers and fat models. Default method is called Index() and is called when no action is specified. Can pass entities as parameters to methods; just need to map them. Default parameters can be used as well. (i.e. int num = 5). ASP.NET MVC forces separation, while ASP.NET Forms does not.

Routing

When determining which rule matches it starts with the one that was added first. It then proceeds down the list until one matches. Routing is used to map urls to controllers and actions, but it is also used to generate urls in the application.

Glimpse

It is a an open source tool that allows you to diagnose what is happening on the server, but from the client. It is very useful when trying to figure out what happened once your request made it to the server. For example, what route was executed. It is basically Fiddler for the server.

For more information see here.

Razor

It is a View Engine for MVC. For more info see ScottGu’s blog. Razor files are .cshtml. Tags start with @ and it automatically figures out where code ends and content begins. Content and code can be intermingled. A layout page is basically like a master page in ASP.NET Forms. You can use traditional parameters or Named parameters. Use Custom helpers for custom rendering; refactoring in a way.

MVCScaffolding package

It is a code generator for MVC; a smart one at that. Highly recommended so you don’t have to write so much code to do CRUD UIs. Use NuGet Package Manager to get and install it.

Entity Framework 4.1

Great code first support. You can use POCO objects, make changes and the database just updates. It is so cool. This behavior is optional, but very nice. You can setup database initialization code to populate some basic data when the database is dropped.

Modernizr

A great tool that allows you to take advantage of HTML 5 when it is available and downgrade when it is not. An example of this is round corners. Very nice tool that can be used in most any ASP.NET project. http://www.modernizr.com/

Windows Phone 7

Later this year there will be a new release called Mango. It is a very exciting release as it will bring the Windows Phone 7 much closer to the competition as far as its offering. To get started with Windows Phone 7 development go here. Everything that is developed for Windows 7 Phone falls into two categories: Silverlight or XNA. XNA is for games (also used for XBOX), and Silverlight is for everything else. You can import some XNA libraries (such as audio) into Silverlight. Tombstoning is basically storing state to appear like the application never quit when in fact it did. It basically works by providing a state bag that most anything can be written/read to when different events such as (navigate to and from are called). It works much like Viewstate does in ASP.NET except it is not automatic. So more specifically it works like adding and retrieving stuff to/from viewstate in ASP.NET. The cost is $99 per year to put your application (or as many as you want except not more than 15 free ones) in the Marketplace. You can register to developer unlock your Windows Phone 7. Visual Studio 2010 comes with an emulator, but the one that will be in the mango release is sooo much better because it simulates things like sensors. The talk was given by Jeff Wilcox.

Tips on Windows Phone 7 development

Change splashscreen (an image) to all black if you app launches immediately. That way it is perceived as faster and cleaner. If your app takes a few seconds to load then put in a custom splashscreen (change the image) to give a more professional look. Add scrollviewer or list so that scrolling will always be available if something doesn’t fit on the screen. Use the metro look and feel styles, don’t hard code styles. If things don’t align as expected, then use 12px margins. Promote your app by creating a site for it. Prompt a user for review of your app after you think they have used it for a while. Statistically most apps are only used once and never used again. Use Watson to collect crash information, bugs, etc. Use Google Analytics to track page navigation within your app. Keep your main dll as small as possible since the entire thing is checked before loading into memory. Delay load pages / dlls on an as needed basis to help accomplish this. This also helps with startup times. Use a tombstoning helper. Use caching whenever possible.

Windows Azure – by Scott Guthrie and Mark Russinovich

Windows Azure is a great technology that needs some work with regards to deployment speed, and in general ease of use, but it is extremely well architected for scaling your application in a very elastic way. It allows you to worry about your app and not the infrastructure it is running it. It is Microsofts offering of cloud computing. The idea is that if you need 100 servers for an event you have them. Then after the event you don’t need them. With traditional models you would have to have servers ready to go at all times and pay for that much power. Since Microsoft has a giant server farm called Windows Azure and a middle tier between you and the server OS you only pay for what you use for the length of time you use it. Everything is continually monitored for 99.? uptime.

Mark went into all the details or the architecture. He convinced me it is very complex and robust, but not so fun to listen to a discussion about it. way too much details for my interest level. However, here as some of the things I found interesting. SQL is actually much slower than the way Windows Azure does it. They actually have everything as read only. Once it is read the objects are de-serialized and used. If they need to update, they are written back to the cloud as serialized objects. This takes only a trillionth of a second instead thousandths of a second that SQL takes. This allows them to scale fast. Their belief is that in the next 10 years or so this will scenario will be common more so than the relational database. Currently options in Azure are SQL, Blobs, and Queries. I believe SQL Azure is part of Windows Azure.

Some Barriers / solutions to get over before cloud computing will be accepted are: Trust of proprietary data being outsourced; loss of control; confidence (ISO and other certifications will help); Private clouds may be an alternative; license to run in own data center may help also.

Zune software is not installed. Install the latest version of Zune software

2011-04-23T14:59:00.001-07:00

If you are using Visual Studio and opened an existing or created a new Windows Phone 7 project you may get the following build error.

Zune software is not installed. Install the latest version of Zune software

The reason it is complaining about the Zune software is that it is assuming that you want to deploy to a Windows Phone 7 DEVICE and NOT to the Windows Phone 7 EMULATOR. IF you are deploying to a REAL Windows Phone 7 DEVICE then you should install the Zune software. More likely, you want to test your project in the Windows Phone 7 EMULATOR that works with Visual Studio 2010. To target the Emulator, you just need to the target in your Drop Down List from Windows Phone 7 Device to Windows Phone 7 Emulator. After making the change, try debugging again and it should work much better.

300 blog entries now

2011-04-19T15:01:00.001-07:00

I can’t believe I have written 300 blog entries over the years. WOW! This is SO COOL! Given the large number of people that visit my blog I am so thrilled to be able to give back to the community.

How to change the display format of a field using MetaData

2011-04-19T14:49:00.001-07:00

Let’s assume you have a Domain Service class that you are access from your ASP.NET Dynamic Data application (should work for Silverlight client as well (I think)) and that you have a table called Person. The Person table has a field / property / column that is called BirthDate. By default it is displayed as a date AND time in the GridView and the Edit and Detail Forms. The way to change the display format is in the MetaData for the Person table. In particular the MetaData for the property BirthDate. The basic MetaData class is generated for you when you create the Domain Service class.

The question is what Attribute do we use to communicate what we want. The answer is: DataFormatAttribute. Use it like you would a string format in most other places in the .NET framework. In the example below only the date (not the time) will always be shown.

[DisplayFormat(DataFormatString = "{0:d}", ApplyFormatInEditMode=true)]
public DateTime PerformedOnDateTimeUtc { get; set; }

If we remove or set the ApplyFormatInEditMode parameter to false (as shown below) then insert and edit will still show the time, but the gridview will still only show the date (no time).

[DisplayFormat(DataFormatString = "{0:d}", ApplyFormatInEditMode=false)]
public DateTime PerformedOnDateTimeUtc { get; set; }

You can use custom date formats, use on different datatypes, etc.

How to determine if your Windows 7 Firewall is blocking your connection

2011-04-12T15:22:00.001-07:00

If you are like me, you cannot turn off your Windows 7 Firewall for even a minute on your work computer. If you are lucky enough to have admin rights on your computer or you can add a rule to your firewall then you are in luck. It just so happens that you can add a rule that allows most anything to open a connection from your laptop to just about anywhere.

To add a rule, go to the Start Menu | Administrative Tools | Windows Firewall with Advanced Security. Right-click the Inbound Rules on the left navigation area and then select New Rule…

Next choose Custom rule. Click the next button.

Leave all programs setting. Click the next button.

Leave Protocol type to Any. Click the next button.

Leave IP addresses as Any. Click the next button.

Make sure Allow the Connection radio button is check. Click the next button.

Choose all three (Domain, Private, Public). Click the next button.

Give the rule something descriptive.

You have now opened up your computer for potential attack (at least from inbound). I highly recommend you not keep this rule enabled.

How to consume a SharePoint Web Service with WCF Client

2011-04-11T15:59:00.001-07:00

SharePoint Web Services are great because they can be called from any computer that can connect to SharePoint, not just the server that is running SharePoint. This is nice for installations of SharePoint like MOSS 2007 or 2010, but it is critical for SharePoint Online (BPOS) or SharePoint 360 where you will NOT have access to the server that SharePoint runs on. The problem is they are not nearly as easy to work with as just using the SharePoint object model, and they don’t expose everything through the web services. This is a real bummer! It is still a very useful alternative for situations where you cannot use the SharePoint Object Model.

There are a few tricks for working with SharePoint Web Services. There are lots of ways you can access the web services. I prefer to use WCF to access the services so that I get cool syntax from XElement.

Here is a list of web services that are available. Let’s use the Webs web service. Below are the step by step (close anyway) to using the web service in Visual Studio 2010.

Create a console (for simplicity) or any other project type in Visual Studio 2010. NOTE: This can be on your laptop that is NOT running SharePoint.
Go to the docs for the Webs Web Service. Here you will see that the url is: http://<Site>/_vti_bin/Webs.asmx you need to replace <Site> with your hostname or dns name, etc.
Add a Service Reference to the above url (after you replaced <Site> with your site info). I am naming my MyWebs, but you can call it whatever you like, just make the appropriate changes to the code I show later.
If you require all users to be authenticated (don’t accept anonymous users) then you will need to change the WCF bindings that were automatically created in your app.config such that it will pass security information to the web service. To do this, just search for the url of the web service. That will take you to a XML tag called endpoint. In that same XML tag you will see an attribute called WebsSoap. In this same app.config do a search for WebsSoap. This will take you to a binding tag. In that binding tag you will see a security tag. That security tag should have a mode=”None” attribute. Change mode=”None” to mode=”TransportCredentialOnly”. Then just below that you will see a transport tag, change the clientCredentialType=”None” to clientCredentialType=”Ntlm” and change the proxyCredentialType=”None” to proxyCredentialType=”Ntlm”.
All SharePoint services are work on Site Collections or Sites (webs) so really the docs should say http://<Site>/<subsite>/_vti_bin/Webs.asmx because if you are dealing with a subsite you need to use that url. You don’t need to add a service reference for each site, you can change it in the code. Be sure to change the endpoint address line in the example below.
Since we are using WCF, we get XElement which gives us LINQ syntax and objects from XML. How cool is that! When determining what is available as attributes it is easiest to just look in the debugger (use the visualizer).
Now that we the results as objects we can do whatever we want with them.

NOTE: You can also do this with the old Web Service client, but you don’t have XElement, LINQ support, but changing the URL is as easy as just changing the url and the Credentials property is all you need to change the credentials. You also don’t really have to worry about the WCF app.config stuff, but in the end I still like WCF.

private static void GetWebs()
{
    using (var ws = new MyWebs.WebsSoapClient())
    {
        // pass the proper credentials. Comment/Uncomment the proper lines depending on your situation
        ws.ClientCredentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials; // use current security context
        //ws.ClientCredentials.Windows.ClientCredential = new NetworkCredential("username", "password", "domain"); // use another Active Directory account
        //ws.ClientCredentials.Windows.ClientCredential = new NetworkCredential("username", "password"); // use account that is used for BPOS

        // change url to the site we want to work with
        ws.Endpoint.Address = new System.ServiceModel.EndpointAddress("http://myhost/sites/SomeSiteHere/_vti_bin/Webs.asmx");

        // get all the webs at and below the specified site
        var results = ws.GetAllSubWebCollection();

        // use XElement and LINQ to get our results and create objects from the XML.
        var test = from r in results.Elements()
                    orderby r.Attribute("Url").Value
                    select new { Title = r.Attribute("Title").Value, Url = r.Attribute("Url").Value };

        // the results are just objects now, so do whatever you want with them.
        foreach (var item in test)
        {
            //Console.WriteLine(item.Url + " | " + item.Title);
            Console.WriteLine(item.Url);
        }

    }
}

Migrating MOSS to BPOS –Part II

2011-04-08T17:20:00.001-07:00

This is a great video on how you can pull data from SharePoint using LINQ to SharePoint. I think for an Alpha it is quire stable from what I can tell. To better understand LINQ to SharePoint, the video below goes into how to use Web Services, the SharePoint Object Model, writing your own CAML by hand, how to write you own LINQ syntax and how LINQ works. Very good course on LINQ itself, lamdba expressions, etc.

Highlights:

CAML Builder Tool if you want to use Object Model and not hand write the CAML code
Everything that can be done in the UI of SharePoint can be done using the Object Model (Sharepoint.dll)
LINQ to SharePoint
Intro into Extension Methods and Lamdba Expressions (shorter syntax for specifying delegate methods).
How to write your own LINQ language such as LINQ to SharePoint which is really just mapping LINQ to CAML
Can use LINQ to SharePoint in a SharePoint web part which uses the Object Model, but can ALSO do the same code but have it use the web services model by sending all the CAML to SharePoint at once and getting back result (using LINQ to SharePoint).
Linq to SharePoint is not created by Microsoft, but is available on codeplex.
Once LINQ to SharePoint is installed, there is a new type (template) that you can select in Visual Studio called LINQ to SharePoint File.
LINQ to SharePoint is nice syntax and easy to work with and it is also very fast like CAML.

After watching this, I am excited to use it. I installed LINQ to SharePoint and I like it a lot. It is very nice. Be sure to use the newest example, don’t go to the samples page until you understand that SharePointDataSource is now a SharePointDataContext and the code is a little bit different. I did find out that it ONLY works on lists. It does NOT work on Shared Document Libraries, but it does work on Events and Tasks. Basically, I believe it works on anything that shows up under the List of a site. Also, from what I can tell, it only works on a particular web. This can be good and bad.

In the end, this will NOT give me a list of users or anything like that because it is not a list. It also doesn’t appear to support any kind of updating of data. I think I am back to the SharePoint web services for updating or getting users or document libs, etc. I think LINQ to SharePoint is a great tool for querying list data in SharePoint.

Migrating MOSS to BPOS (Microsoft SharePoint Online) –Part I

2011-04-08T17:17:00.001-07:00

MOSS 2007 is also known as Microsoft Office SharePoint Server, and BPOS is also known as Business Productivity Online Suite, but more specifically Microsoft SharePoint Online since BPOS is actually more than just SharePoint. Every since the beginning Microsoft has never really known how to market / name SharePoint, and they still don’t IMHO, but that isn’t the point of this blog.

When you start Googling around you will most likely come across Metalogix tools. The two most notable are BPOS Commander and SharePoint Migration Manager. I have tried to get pricing or a demo on both of these products and the company has been 100% non-responsive other than an automated email that says an account rep will contact me shortly. It has been a while now, and no response, so I tried again. Still no response. I expect the product is priced like Oracle… “how much money do you have to spend with us” is the answer you will get when you read between the lines. With that said, the tooling looks WONDERFUL and should make the task much simpler than many other ways.

The big issues to consider are

Mapping Users from current domain to BPOS users.
Cleaning up users that are no longer with company
Keeping Permissions
Domain Groups and SharePoint groups
User account activation since they are not active initially
Hard coded Urls in things such as Wikis, Announcements, Tasks, HTML Content Areas, etc.
Changing Site Hierarchy
Identifying Owners of sites… Abandoned sites, SPS 2003 migrated sites so no Owner groups in some cases, etc
Determining what sites are still used
Converting custom Site Definitions that are basically the same as original Site Definitions to a standard Site Definition that BPOS will accept.

Given that I have not heard back from Metalogix and I don’t even know if we will have a budget for such a tool, I decided to start looking elsewhere. This brought me to the SharePoint Web Services 3.0 SDK which is a Windows Help file. It is much easier to use than the online version in my opinion. Though if you want just the docs for each of the web services, it is pretty good. Though, the downloaded versions has code samples for each web service, I find that VERY useful to get started. It does a good job of getting you started. There are two section of interest. Programming Tasks –> Programming Web Services for Windows SharePoint Services and Reference –> Web Services.

Two other good places to start are below. They do a good job of explaining what is available and how to start coding:

I wrote up an Example of how to use the SharePoint Web Services here.

In the past I used the SharePoint Object Model and .NET to develop migration scripts. I also did some direct database updates. The problem with these is that they require console (Server) access and I won’t have that with BPOS. So, the only option if I want to use the SP Object Model is to do the change before hand, then export the data to BPOS and hope for the best.

Since I won’t have console access, I think it makes sense to look at the web services route. The best part about this is that I can do the development (including debugging) on my laptop instead of the SharePoint server itself. The big downside is that the web services seem to be much more complicated to use. Everything seems to be XML based and a special schema just for querying. I would much prefer working with strongly typed objects and being able to use Intellisense.

One helpful thing is that InfoPath can be used. It appears dealing with the namespaces can be a bit tricky, but can be done and hopefully abstracted away. Here and here are posts on how to use XPath and SharePoint Web Services.

In the quest for being able to use strongly-typed objected I thought maybe someone created a LINQ to SharePoint provider. That would be great! As it turns out, someone did. It can be seen here. The problem is that it is ALPHA and appears to have died quite a while ago. The good news is that SharePoint 2010 has been released and it appears that they have included the LINQ to SharePoint Provider. Click here to see it. It looks similar, but I’m not sure if it is the same as the ALPHA one or not.

If you are not working with lists specifically then SharePoint Web Services are a good option.

Can’t access SQL Server Cluster or non-standard port or named instance after upgrading to Windows 7

2011-04-07T11:15:00.001-07:00

After I migrated to Windows 7 we were not able to connect to the SQL Cluster using MS SQL Management Studio, Linqpad,Visual Studio, my apps, odbc, etc. The reason is that Windows firewall blocks non-default ports for SQL Server.

Determine if the Windows Firewall is the cause

The first thing you probably want to do is confirm that it is actually a Firewall issue. The easiest way (if you are lucky) is to just turn off the Windows Firewall. If you are not lucky enough to be able to turn off your firewall due to corporate policies then there is still hope. To help troubleshoot this, I created a rule in the Firewall that opened up all ports for all programs. Click here to see the details of how to do this.

Once you have determined that the firewall is the issue, you may want to open the port instead of opening it for individual programs or open all ports between your computer and the server you are trying to access.

Option 1: Open Firewall for server

This seems to be the easiest by far since you only have to do it once unless you change server ip addresses or ports.

To add a rule, go to the Start Menu | Administrative Tools | Windows Firewall with Advanced Security. Right-click the Inbound Rules on the left navigation area and then select New Rule…

Next choose Custom rule. Click the next button.

Leave all programs setting. Click the next button.

Leave Protocol type to Any. Click the next button.

Leave local IP addresses as Any. Add ALL IP addresses that you server has to the list of remote IP Addresses. I was surprised that I needed all the ip addresses for the server (use ipconfig from the command line if you don’t know them) and not just one, so to be safe add them all. Click the next button.

Make sure Allow the Connection radio button is check. Click the next button.

Choose only Domain (you may need to add private if you run into issues with vpn, etc). Click the next button.

Give the rule something descriptive.

NOTE: A slightly more secure version of this would be to specify TCP as the Protocol instead of Any. Copy the rule and edit it such that you change TCP to UDP. Then you’ll have one rule for TCP and one for UDP. This is the method I personally chose.

NOTE: You could do the same, but do it for you subnet, but that is a bit riskier from a security standpoint, though not bad on a corporate network. Just be certain to select domain (no public or private).

Option 2: Open Firewall for each program

Next choose your program such as MS SQL Management Studio, Linq pad, Visual Studio, odbc, etc. Be sure to click the Network location types… button and select Home Network if you connect from home (and have selected that connection to be a home connection). FYI, to create an odbc connection using a non-standard port you will need to add the C:\Windows\System32\odbcad32.exe program.

Side note on MS Access: If you are trying to have your MS Access application access a linked server that uses ODBC to connect to the SQL Server you will need to create a new ODBC Data Source, but be sure NOT to make it a System DSN. You want to select User DSN otherwise MS Access will not be able to access it. Though, I imagine if you ran MS Access as administrator you could make it work that way with a System DSN.

I still had trouble when running in Visual Studio 2010 when debugging (worked fine if just ran without debugging). I never did figure out how to get past this. If anyone knows, please let me know.

Option 3: Hardcode server ip address

Workaround, if you can’t change your Firewall, you need admin rights on your machine I think. If you can’t do that, then you can always access the server using the ip address and port number separated by a comma. I don’t know why it works, but it does (at least on my machine).

Legal like warning: I am by no means a security or firewall guru. Please don’t take these as security recommendations. I simply figured out how to solve the problem while taking the security issues into consideration the best way I know how (I’m a programmer not a security expert). So, please don’t make these changes unless you understand the risks as you are messing with firewall rules that are meant to protect your computer from attack. You assume all responsibility for your changes.

Tips on How to install SQL Server 2008 R2

2011-04-07T10:16:00.001-07:00

First this is NOT a step by step installation. I assume you can find the setup.exe and run it. I do address some of the less obvious issues that you may encounter.

BEFORE you run setup.exe: If you have Visual Studio 2008 installed be sure that you also have Service Pack 1 installed for VS2008 (Visual Studio 2008). Otherwise, you will have to stop the SQL Server 2008 R2 installation, go install SP1 for VS2008, then run the SQL Server 2008 R2 installer again.

Once you launch the setup.exe you will see the SQL Server Installation Center. Click the Installation navigation link on the left, and then select New installation or add features to an existing installation.

After a number of straight-forward screens you’ll come to this screen. This is the screen were you determine what you want to install. Basically the first 5 checkboxes (the ones under Instance Features) are for the server and the rest are for the most part for client, though they are “shared” so some of them are for server I assume also.

Check the items you want. I suggest the following.

NOTE: In the installation it will create a Integration Services (service). I guess it is needed even though I am not hosting it.

Visual Studio 2010 won’t debug under Windows 7

2011-04-06T15:40:00.001-07:00

I found out that Visual Studio 2010 won’t let the debugger started until I run Visual Studio 2010 with Run as Administrator. I think I figured out why. Here is an account of what I was seeing and how I fixed it.

I could open my C# ASP.NET web application just fine using Run (Control-F5). Then I tried to do it with Debug (F5) and I got the following message:

Unable to launch the ASP.NET Development Server.Uncaught exception thrown by method called through Reflection.

I then Googled that error message. I found this link. I go to the section 2.4.11.2 (shown below).

2.4.11.2 Debugger start fails if the IntelliTrace recordings directory is Read-Only

By default, IntelliTrace files are located in the "%ProgramData%\Microsoft Visual Studio\10.0\TraceDebugging\" directory. If this directory is made read-only, or the directory is changed to a read-only location, an uncaught exception gets raised to the user. "Error while trying to run project: Uncaught exception thrown by method called through Reflection."

To resolve this issue:

Option 1: Modify the permissions of the directory to be writable.

Option 2: Change the directory where IntelliTrace recordings are stored.

○ In Visual Studio, open the Options dialog box.

○ Expand the IntelliTrace item in the left pane of the dialog box.

○ Click the Advanced item under IntelliTrace.

○ Change the IntelliTrace recordings directory to one that is not read-only.

○ Click OK.

I tried both option 1 and Option 2. The problem is that when I tried Option 1 and went to the directory suggested, it was not there. When I tried Option 2 I got the following message where I was expecting to see some kind of useful UI:

An error occurred loading this property page.

Then I got the idea, what if Visual Studio 2010 needs to be run as administrator even though I am an admin under. I am knew to Windows 7, but I am quickly learning that most techie tasks require the programs to be Run As Administrator; Visual Studio is no exception to this. It seems that I am not the only one to come to this conclusion. Here Microsoft suggests changing Visual Studio 2010 to run as Administrator and I have shown instructions below:

If you are running Windows Vista, you must start Visual Studio by going to the Start menu and right clicking Visual Studio 2010 and selecting Run as Administrator. To always launch Visual Studio 2010 as an administrator you can create a short cut, right click the short cut, select properties, select the Compatibility tab, and check the Run this program as an administrator checkbox. When you start Visual Studio 2010 with this shortcut, it will always run as administrator.

The good news is that after to run VS2010 (Visual Studio 2010) as Administrator the proper directory structure will be created and the if you happen to run VS2010 without running it as Administrator you can now run as Debug (F5) and you can also go to the Options | IntelliTrace | Advanced and see a useful UI with no errors.

I think to keep things simple I will run VS2010 as administrator at all times.

Export to Excel using Dynamic Data

2011-03-25T16:30:00.001-07:00

If you are using Dynamic Data ASP.NET web application you are likely using the Entity Framework and a Domain Service. With these key technologies it is easy to add an “Export to Excel” link to all your List pages in your project. The best part is that once you add this, you don’t have to write it again for each table. This is a write once use for everything solution.

To implement the writing of data to Excel I don’t want to use anything that requires Microsoft Excel to be installed (except by the people that are downloading the file). I chose a free solution from CarlosAg.net called Excel Xml Writer. Click here to go to the product page and download it. It is implemented using C# and is a managed solution (no COM, interops, etc). The file that is generated is a xml version of the Excel file format so it is easy to debug and tweak if needed. Best of all the installation is simple: just add a reference to the single .dll. There should be no other dependencies.

I wanted to use the same column names that are in List page that I am exporting and I didn’t want to have to hard code them. I wanted them to be read from the MyDomainService.metadata.cs. If you use the DisplayAttribute to specify user friendly names, my code uses them instead of the column names. Also, not every property in the table are shown. It depends on what the MetaTable.GetScaffoldColumns() returns which is what the DefaultAutoFieldGenerator() for the List.aspx.cs page uses.

To encapsulate this logic, I created a class called PopulateExcelWorksheetForDynamicData.cs. To download the source for the file click the filename. I’m not going to go through all the code step by step. However, the basic idea is that the Populate() method is called. You pass it an IQueryable object and the MetaTable information. Based on that the MetaTAble.GetScaffoldColumns() is called to determine what columns should be shown. For any foreign key columns I resolve to the related (called the parent table) and display the appropriate column based on the DisplayColumnAttribute or default DisplayColumn if one is not specified via the attribute.

The datatypes that are specified in the MetaTable are converted to Excel types. Special formatting such as DateTimes require a special format in order for Excel to know how to show them as dates. I make some assumptions about the format the user will want to see. This is hardcoded, but could be extended or changed.

You can tweak the formatting of the file as well. There are two formats I use. See: SetCannedStyle1(), SetDefaultStyle(). I haven’t used SetCannedStyle1() in a while, but I think it should still work.

That is the guts of the logic. Now it is just a matter of creating a button or a link on the List.aspx.cs page and executing code similar to the following.

protected void btnExportToExcel_Click(object sender, EventArgs e)
       {
           IQueryable queryResults = null;
           using (var service = new DistiPromoDomainService())
           {
               // execute the query that was used to populate the GridView.
               var selectMethodInfo = service.GetType().GetMethod(GridDataSource.QueryName);
               queryResults = selectMethodInfo.Invoke(service, null) as IQueryable;

// add in any filters that are applied via the QueryExtender as well
var queryExtenderExpressions = queryExtender.Expressions;

                foreach (DataSourceExpression expr in queryExtenderExpressions)
                {
                    queryResults = expr.GetQueryable(queryResults);
                }

               var workbook = new Workbook();
               var sheet = workbook.Worksheets.Add("Sheet 1");
               var excel = new PopulateExcelWorksheetForDynamicData(sheet);
               excel.Populate(queryResults, table);
               PopulateExcelWorksheetForDynamicData.SetDefaultStyle(workbook);
               // don't use spaces as this will cause issues. You can also try .xls, but you will get an corruption warning in IE.
               string filename = Title.Replace(" ", "-") + "-Export.xml";
               PopulateExcelWorksheetForDynamicData.SendToBrowserForDownload(workbook, filename, Context.Response);
           }
       }
You may want to tweak the filename. That should do it.

Automatic Auditing of Changes using Domain Service and Entity Framework

2011-03-03T14:00:00.001-07:00

A special thanks to Fredrik Normen for providing the basis for my code. His great article is here. For a better understanding of the basic code, please read Fredrik’s implementation first. He does a good job of walking you through his code.

With that said I have changed most of his code and added logging to a database table using another Entity Framework (EF) model. You can download my updates to his code under the Source Code section.

Please note that only changes made through the Domain Service will be logged. If you don’t use a Domain Service to modify all your data that you want logged this solution is probably not for you. However, if you use a Domain Service and Entity Framework this is a pretty robust solution for adding auditing to your ASP.NET web application in minutes. I am using this with my Dynamic Data web application and it works great!

The code works because the Domain Service has a concept of a ChangeSet. When you save (update or insert or delete) a ChangeSet is created that basically shows what the original entities look like and what the current entities look like. This is all standard Domain Service stuff. Nothing I did. However, what I do is look at the original and current values and create EntityChangeInfo objects and PropertyChangeInfo objects. These then get written to the database where there is a record in the database for each field. This is very verbose for Inserts and for Deletes, but just write for Update. If you don’t need insert and delete auditing, you can easily comment them out.

To implement my solution, here are the basic steps (from my memory) to add auditing to you application.

Download and use the AuditLog-Create.sql to create an AuditLog table in a database.
Create an Auditing directory in your Services directory (if you have one).
Download and copy the AuditLogger.cs, ChangeSetHelper.cs, EntityChangeInfo.cs, PropertyChangeInfo.cs to the Auditing directory you just created.
In your Models directory (if you have one) create a new ADO.NET Entity Framework Data Model that points to the database where you created the AuditLog table. Call the model AuditModel. The only table you want to add to it is the AuditLog table. You don’t need a Domain Services or anything else for this. We will reference it directly.
Download and copy the snippet from the DomainService-PersistChangeSetSnippet.cs and paste it in your domain service file that you already should have in your project. This is the file for the domain service that you want to audit. If you don’t want INSERT or DELETE auditing you can comment out the appropriate line in this snippet.

That is it. You should now be able to run your application, make some changes and see the changes show up in the AuditLog table.

You can then execute a query something like this to see the history of a particular record in a table.

select * from auditlog where EntityName = 'Activity' and EntityPKValues = '46' and operation = 'Insert'

select * from auditlog where EntityName = 'Activity' and EntityPKValues = '46' and operation = 'Update'

select * from auditlog where EntityName = 'Activity' and EntityPKValues = '46' and operation = 'Delete'

My Comments

You may notice that I log my times in UTC and that the date/time is the same for a given update, insert, or delete. In other words, when an update is written to the database, multiple records are created (one for each column that changes), but the CreatedDateTimeUTC field is stamped with a ONE datetime.

When values are inserted, the OriginalValue column will ALWAYS blanks. When a record is deleted, the CurrentValue column will ALWAYS be blank.

If you have a properties that you have added to an entity they will have an initial value and the computed value in some cases. If this is the case for you, you may want to hard code these property names the ChagneSetHelper.GetPropertiesChangeInfo method as properties to not log. Just like I have done for the EntityState property. Up to you.

Source Code

PropertyChangeInfo.cs

DomainService-PersistChangeSetSnippet.cs

The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'

2011-03-01T14:57:00.001-07:00

You will get this error when you try to use the msdb.dbo.sp_send_dbmail stored proc and the user that you are using does not have permissions to execute the stored proc.

The user must be a member of the DatabaseMailUserRole in the msdb database. To give the user permission to do so, execute the following:

EXEC msdb.dbo.sp_addrolemember 'DatabaseMailUserRole', 'username here'

NOTE: If the user has not be added to the msdb database, then you will get an error like the following:

Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75

User or role 'username here' does not exist in this database.

To fix this, you can execute the following

use msdb

go

CREATE USER [username here] FOR LOGIN [username here]

You can also use MS SQL Management Studio to add the user to msdb and to the DatabaseMailUserRole in one step if you like the UI. :)

The resource cannot be found error when using Dynamic Data.

2011-02-25T12:26:00.001-07:00

Have you seen the error page shown below when you are developing a ASP.NET Dynamic Data application?

Server Error in '/' Application.

The resource cannot be found.

Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable. Please review the following URL and make sure that it is spelled correctly.
Requested URL: /DynamicData/PageTemplates/Edit.aspx

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1

I thought my application was broken. In the end, it had nothing to do with my application. It has everything to do with how a ASP.NET Dynamic Data Domain Service Web Application is setup by default when press F5. By default the project is setup to open the Current Page as the start action. I noticed that if I have the Edit.aspx page selected in my Solution Explorer and then hit F5 I get the error above. Notice the url in your browser, it is something like this:

http://localhost:1440/DynamicData/PageTemplates/Edit.aspx

What I wanted and expected to happen was open the default page when I hit F5. The error is 100% correct. That page is not directly available since I am using Dynamic Data.

If I select my project instead and hit F5 I get the default page as expected and desired.

So, really all this is just because I didn’t realize what the default behavior was. The remedy is very simple. Right click on Default.aspx in the Solution Explorer and select Set as Start Page. This will change the Start Action to use a Specific Page (the Default.aspx in this case) instead of the Current Page.

If you are not sure what your project is set to you can right-click on your project in Solution Explorer and go to the Web tab. Here you will see Start Action. To have F5 always start the Default.aspx page make sure Default.aspx is in the Specific Page textbox.

Here is what it should look like:

Setting default values in Entity Framework when using Silverlight

2011-02-09T15:35:00.001-07:00

I found myself amazed at how difficult it is to set default values for a property in the Entity Framework (even version 4) when used with Silverlight. Ideally I could use the DefaultValue attribute in the metadata class for my model. The problem is that Silverlight doesn’t use it. It seems to work ok in my tests that don’t use Silverlight if I remember correctly. But that only partially helps. I explored putting the value in the constructor for the entity that has the property I want to set the default value on, but that fires every time the object is created. This means that it was created even when displaying the results of a query, which is not the event I was hoping for. I want the default value to be used when a new entity is created, but just the object instantiated.

Then I ran across this post. It talks about creating a partial class on the client-side (Silverlight) and using the partial method called OnCreated(). The forum seems to say that you can define the OnCreated() partial method in the partial entity class that is on the server-side, but that method is not defined on the server-side that I can tell. So, the partial keyword doesn’t work in that context. The server-side object has a .shared.cs instead of just .cs extension so the file will be copied to the client-side. The problem is that the code has to work on the client and the server side. As noted before, the OnCreated method only exists on the client-side.

The solution is actually quite simple. I just had to explicitly define a partial class on the client-side even though I already have one on the server-side and is shared. This allows me to leverage the OnCreated partial method on the client that will fire when the entity is created, not just instantiated, and still keep my shared partial class that is on the server and client side.

I haven’t tried it, but by doing like Kyle recommends I should be able to use the DefaultValue attribute in the metadata and have it carry over to the client-side by calling the utility method that reads the DefaultValue attribute from the model when the OnCreated method is called.

So, here is an example, of what I am trying to explain.

In this example (to keep with the example on the forum) my entity is called Entity1

In my Silverlight project, I define a file called Entity1.cs. It would contain:

public partial class Entity1
    {
        partial void OnCreated()
        {
            DefaultValueUtility.InitializeDefaultValues(this);
        }
    }

NOTE: I could also just set the property directly instead of pulling the data from the model.

I can, but don’t have to have a file called Entity1.shared.cs on my server-side that will can contain whatever I want it to. It will be copied to the client-side as well. Then it will be merged with the Entity1.cs and the generated Entity1 file from the model itself on the client side because they are all partial classes.

Here is the code for the DefaultValueUtility call above. You will want to put this code somewhere in your Silverlight project.

using System;
using System.ComponentModel;
using System.Globalization;
using System.Linq;
using System.Reflection;

namespace DefaultSetter
{
    public static class DefaultValueUtility
    {
        public static void InitializeDefaultValues(object entity)
        {
            PropertyInfo[] properties = entity.GetType().GetProperties();
            foreach (PropertyInfo property in properties)
            {
                MethodInfo propertySetter = property.GetSetMethod();
                if (propertySetter != null)
                {
                    DefaultValueAttribute defaultValueAttribute = (DefaultValueAttribute)
                        property.GetCustomAttributes(typeof(DefaultValueAttribute), true).FirstOrDefault();

                    if (defaultValueAttribute != null)
                    {
                        object defaultValue = 
                            Convert.ChangeType(defaultValueAttribute.Value, property.PropertyType, CultureInfo.InvariantCulture);

                        propertySetter.Invoke(entity, new object[] { defaultValue });
                    }
                }
            }
        }
    }
}

Hints on how to start writing an Internet Explorer Add-on

2011-02-03T11:19:00.001-07:00

Most of the info here are for how to write a Browser Helper Object or (BHO). A BHO is a faceless piece of code that is executes when iexplore.exe (Internet Explorer) and explore.exe (Windows Explorer) launch. Yes, believe it or not by default when you add to one, you are adding to the other. I’ll tell you how to avoid that later. You can also write toolbars, etc, but I don’t really cover that here other than some of the links at the bottom of the page do.

A BHO is the simplest plug-in you can write for IE because there is no UI, it is just sitting there allowing you to hook into IE’s events. The two most common are fired when a page has completed its loading (OnDocumentComplete), and also when navigating (OnBeforeNavigate2) like when a user clicks the link on a page. You may also want to look at NavigateError which is called when there is an error during navigation.

There is also a RegisterBHO method that gets called when you register your BHO which is just a .dll. It is called RegisterBHO, and has a similar method called UnregisterBHO that gets called when you unregister your BHO. When I say register, I am talking about the days of COM. So, you will need to use regasm to register and unregister your BHO. regasm is included with all (I think) the Visual Studio versions. However, you will need to go to the start menu and then go to the Visual Studio 2XXX item and choose something called something like: Visual Studio Tools then Visual Studio Command Prompt (2010). This will bring regasm into the execution path. The two commands you will need are:

regasm /codebase "BHOHelloWorld.dll"

regasm /unregister /codebase "BHOHelloWorld.dll"

You would use these (or similar) to install and uninstall your BHO.

There is some code you will want to put in your RegisterBHO so that IE will see it and load it on startup. Basically, your code will need to get the GUID that you assigned to your BHO class using the Guid attribute, and then add a new SubKey to

HKLM\Software\\Microsoft\Windows\CurrentVersion\Explorer\Browser Helper Objects

If you don’t want the BHO to be used by Windows Explorer and thus only by Internet Explorer, you need to Add a NoExplorer Key at the following path where <GUID> where <GUID> is the GUID for you BHO and also the SubKey your code should have added to the above path in the registry.

HKLM\Software\\Microsoft\Windows\CurrentVersion\Explorer\Browser Helper Objects\<GUID>\NoExplorer

Set that value to the number 1. If you don’t do this before you register your BHO, you will need to re-register it so that the code will be executed when it registers or just add it manually. Regardless, you may find that after a reboot (or sooner) that Windows Explorer has loaded your BHO and won’t let you build (if you registered the .dll in your bin directory that you are building to). In that case, un-register the .dll, and logout and log back into Windows. Now you should be able to build again. You will always need to have IE closed when you are building also.

I would recommend going to here and downloading this project and opening it. Before you run it, change the RegisterBHO to what I have below.

[ComRegisterFunction]
        public static void RegisterBHO(Type type)
        {
            // if the path doesn't exist then create it
            RegistryKey registryKey = Registry.LocalMachine.OpenSubKey(BHOKEYNAME, true);

            if (registryKey == null)
                registryKey = Registry.LocalMachine.CreateSubKey(BHOKEYNAME);

            // if the Key for this BHO doesn't exist then create it
            // NOTE: The guid comes from the Guid attribute for this class (not the one in the AssemblyInfo.cs)
            string guid = type.GUID.ToString("B");
            RegistryKey ourKey = registryKey.OpenSubKey(guid);

            if (ourKey == null)
                ourKey = registryKey.CreateSubKey(guid);

            // We only use this BHO in iexplore.exe, not explore.exe, so tell it not to load in explore.exe with the special flag (the name should not be changed)
            ourKey.SetValue("NoExplorer", 1);

            // clean up
            registryKey.Close();
            ourKey.Close();
        }

This will prevent Windows Explorer from loading it (assuming that is what you want as well). Register the .dll using regasm.exe and then open IE. There you have it.

Remember, you can also use Visual Studios debugging facilities. You just have to set your breakpoints, build, open IE, attach to the process (VS | Debug menu | Attach to Process…| look for iexplore.exe in list and click the Attach button. Now navigate or refresh in IE. The debugger should hit your breakpoint if you put it in the BeforeNavigate2 or DocumentComplete event.

If you want to find more info on this stuff I suggest searching for DWebBrowserEvents2 or BeforeNavigate2 or DocumentComplete or better yet start by checking out the links below.

Useful Links

How to attach to Browser Helper Object (BHO) with C# in two minutes
This is where I started from since it is in C#, converts to VS 2010 with no errors, and is easy to follow. I built my first BHO based on this.

Creating Custom Explorer Bars, Tool Bands, and Desk Bands
Explains the different type of add-ons

Developing IE Add-ons
Has links to lots of useful articles, sites, etc. Links to IE Protected Mode considerations. Links to how to write Browser Helper Objects (BHO).

Building Browser Helper Objects with Visual Studio 2005
Good article that seems to apply to VS 2010 as well. Good intro.

15 Seconds: Build a Managed BHO and Plug into the Browser
Good tutorial on Browser Helper Objects (BHO).

Solution for Can’t disable/enable add-on in Internet Explorer

2011-02-01T15:07:00.001-07:00

If you can’t enable or disable any add-ons in Internet Explorer then it is likely that there is a group policy that is preventing you from doing it. Like most group policies they can be temporarily overwritten by modifying the registry key. You can copy and paste the text in blue into a text file and give it the .reg exension. Now when you open it, it will modify the registry for you. Below the setting is set to 0 which means that you CAN enable or disable add-ons in Internet Explorer. Set it to 1 to prevent yourself from doing it. NOTE: HKLM is for all users, however if you go to the same path, but for HKCU the change will only affect you unless there is a value in HKLM which will then override the user value (I think).

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Internet Explorer\Restrictions]
"NoExtensionManagement"=dword:00000000

Behavior Driven Development

2011-01-27T11:33:00.001-07:00

No this isn’t a new program from HR, it is a theoretically nice way to ensure that your requirements, documentation, test cases, and code are all in sync. The difficulty with anything like this is that you have to actually use it.

Below is a link to a 30 minute video on how this can be done using a free tool that integrates with Visual Studio and NUnit.

http://specflow.org/specflow/screencast.aspx

Check it out. Remember, you can give it a try on a new module or feature for an existing application or even apply it an existing project just to test it and also document it.

Why is my MS SQL Database so slow now?

2011-01-25T12:54:00.001-07:00

If your database worked great when you developed and is now slow, here are some things to consider. If you didn’t create indexes initially, you data may have grown since you started, and need some indexes. If your data changes a lot, the database can become fragmented. Fragmentation can come at the disk level and can be remedied by something like Windows disk defragger tool. Fragmentation can also come at the database level which could mean even certain tables or partitions are fragmented.

Checking for Index Fragmentation

Use DBCC SHOWCONTIG to determine how fragmented your tables are. You can use different parameters, if you just execute with no parameters it will be run for all tables in the database. I recommend using the following parameters so that it can be shown in table for all tables in the database.

DBCC SHOWCONTIG WITH TABLERESULTS;

NOTE: You will see an column called IndexName, but know that this is just the name of the primary key index, not all the indexes on the table.

In particular, the first thing to look at is the Local Scan Fragmentation. This is the percent that is fragmented. For a very healthy table 0% is what you want. Secondly, look at the Scan Density line. For a really healthy table you want 100% which is the ratio or Best Count to Actual Count.

You should eventually move to sys.dm_db_index_physical_stats instead since DBCC SHOWCONTIG will be removed after SQL Server 2008. I don’t know how to get the Scan Density though. So, I show both here. However, some one at Microsoft posted that you don’t need the Scan Density and should instead use the contiguous extent allocation which is provided by sys.dm_db_index_physical_stats, but I don’t know which column they are referring to. Does anyone else know? I suspect it has something to do with fragment size.

Evaluating Index Fragments

A fragment is made up of physically consecutive leaf pages in the same file for an allocation unit. An index has at least one fragment. The maximum fragments an index can have are equal to the number of pages in the leaf level of the index. Larger fragments mean that less disk I/O is required to read the same number of pages. Therefore, the larger the avg_fragment_size_in_pages value, the better the range scan performance. The avg_fragment_size_in_pages and avg_fragmentation_in_percent values are inversely proportional to each other. Therefore, rebuilding or reorganizing an index should reduce the amount of fragmentation and increase the fragment size. For more info click here.

If you want to go off of just the average logical fragmentation percent and you have SQL 2005 or greater you can use the following statement which is very fast if you just want to see the fragmentation of the index on the table MyTable in this example

SELECT a.index_id, name, avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'MyTable'),

NULL, NULL, NULL) AS a

JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

Alternatively, if you want to see it for all the tables, you can do this one.

SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), null,

NULL, NULL, NULL) AS a

JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

Possible Solutions

If you have index fragmentation you have three options noted in docs on DBCC SHOWCONTIG

Drop and re-create a clustered index.

Re-creating a clustered index reorganizes the data, and causes full data pages. The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX. The drawbacks of this method are that the index is offline during the drop or re-create cycle, and that the operation is atomic. If the index creation is interrupted, the index is not re-created.

Example:

CREATE CLUSTERED INDEX IX_WorkOrder_ID ON WorkOrder(ID)

    WITH (FILLFACTOR = 80,

        PAD_INDEX = ON,

        DROP_EXISTING = ON);

or

DROP INDEX followed by CREATE INDEX

Reogranize / Defrag

Reorder the leaf-level pages of the index in a logical order.

Use ALTER INDEX…REORGANIZE to reorder the leaf-level pages of the index in a logical order. Because this operation is an online operation, the index is available when the statement is running. The operation is also interruptible without loss of completed work. The drawback of this method is that the method does not do as good a job of reorganizing the data as a clustered index drop or re-create operation.

Note: You can also use DBCC INDEXDEFRAG since it is old SQL Server 2000 equivalent.

Example: ALTER INDEX PK_Employee_ID ON Employee REORGANIZE;

Rebuild the index.

Use ALTER INDEX with REBUILD to rebuild the index. For more information, see ALTER INDEX (Transact-SQL).

Note: You can also use DBREINDEX since it is the old SQL Server 2000 equivalent.

Example: ALTER INDEX PK_Employee_ID ON.Employee REBUILD;

Choosing the right solution

The rule of thumb that Microsoft recommends is the following:

% Logical Fragmentation	Corrective Statement
< 5%	No action should be taken
> 5% and <= 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*

* Use the ONLINE = ON to keep the index usable and data available to users. ONLINE = OFF can also be used if availability is not a concern

Automation

Example E in the docs has a great script for defragging any index that has more than a specified threshold of fragmentation.

The easy way is to Rebuild indexes in the Maintenance Plan for the database. Though this requires the indexes be taken offline, so if you run it weekly and load data daily, it may not be often enough. Then you may want to use the script below.

This script actually follows the recommendations above, except puts changes the 5% boundary to 10%. The problem with the script is that it does NOT support anything except the default schema, uses 10% instead 5%, and includes indexes that don’t have names such as when there is no primary key on the table. If you want to use the same script I use that I have modified to support these issues, click the link below.

FireFox supports Integrated Windows Authentication

2011-01-21T09:11:00.001-07:00

Most people (including me until recently) don’t know that FireFox support Integrated Windows Authentication (NTLM). The behavior that most people are accustomed to is when you go to a web site on a corporate intranet that often requires Windows Authentication using Firefox you get a prompt for your username and password and for Internet Explorer you are logged in automatically. So, the assumption that many people (me included) make is that Firefox doesn’t support Integrated Windows Authentication. Fortunately, this is a wrong assumption.

Internet explorer determines what sites are ok to use Integrated Windows Authentication on by looking to see if the site is in the Intranet Zone which can be done by looking the url, etc. Well Firefox doesn’t use that criteria. Firefox instead uses a white list or in others you have to explicitly tell it which sites to trust and that will then use Integrated Windows Authentication. Since the list is empty by default every site that has Integrated Windows Authentication enabled still gets a prompt. The simple solution is to add the sites that are on your intranet that use Integrated Windows Authentication to this list.

The Easy Way

The good news is that someone created a nice and easy to use Add-on for Firefox. So you can open up Firefox and go to Tools menu | Add-ons | Get Add-ons tab and type in the search box: NTLMAuth. You will likely get one result and it is for an add-on called NTLMAuth For Firefox. Click the Add to Firfox… button, install, and restart Firefox.

Open up Firefox again and go to Tools menu | NTLM-Enabled Sites

You will get a screen that looks like this:

All you have to do is add the sites you want to have Integrated Windows Authentication enabled for. No more prompt will be shown for these sites. Please note that you do NOT want to use http:// or anything after the domain name except a colon then port number if it is not port 80.

For example, you could put www.apple.com or myapp.mycompany.com:1234.

For Geeks

The truth be told you don’t need the add-on at all. You can do this with just Firefox itself. The add-on just saves you from changing some configuration items in Firefox.

If you want to do it yourself, here is what you need to do.

Open FireFox
Type about:config in the address bar. (Tip: Don’t let Google try to search on this item if that feature is enabled by hitting escape after you type it). Hit enter of course to go to it.
If you are using FireFox 3.x or later you will be warned. Agree if you want to continue. If you have already told it to not bother you in the past you won’t get this prompt.
You can look through the list or simply type network.automatic in the Filter at the top of the the screen. Look for a line that is called network.automatic-ntlm-auth.trusted-uris. Double-click that like to bring up the tiny little editor. It’ll look like this.
You can now type in your sites separated by commas. It worked for me without spaces, but I read that you can use spaces also.

TIP: I recommend just typing them all into notepad or some text editor, then copy and paste the big line into this little textfield.
Click OK and that is it.

Figuring out what your proxy is

2011-01-20T11:09:00.001-07:00

If you work in corporate America chances are you are going through a proxy when you are surfing the web. Sometimes the proxy server is specified, sometimes you have a url that then determines what proxy to go to. If the server is directly specified as shown in the Proxy Server address below (the lower yellow area), then it is easy to tell. What is there is your proxy server.

On the other hand, if you have a .pac file or out configuration script that is being used then it is hard to tell from here because the rules are in the .pac file. This would be specified in the first yellow area shown below. You can use FireFox or sometimes Internet Explorer to save the .pac if you go to it in the browser. It can be saved and opened in any text editor. You can’t change it, but you can at least see what it is doing. But the question I had was how do I confirm what proxy server the .pac file has directed me to?

No matter what these settings are you can tell for sure what you proxy server you are actually connecting to by using the command line. First make sure you have Internet Explorer open and on the page in question. Open a command prompt (Start menu | Run… | cmd.exe) and then type the following:

netstat –b –n –p tcp

Now look for iexplore.exe. You will see something like

Notice I have highlighted the proxy server and port in yellow.

If you want to know what that is from a dns name perspective, you can use nslookup to look it up.

In this example, you would type:

nslookup 134.27.74.36

This will return some name of the server if you are lucky.

How to pass a list of ids to a stored procedure and iterate over them using a cursor

2011-01-18T16:25:00.001-07:00

As you probably know there is no array type in T-SQL. Let’s say the end user selects some records and you want to pass the ids of each of these records to a stored procedure. You can just use Dynamic SQL to handle this, but what if you actually want to use a cursor to iterate over each one and do something for each item. There are many reasons such as calling a stored proc for each item or maybe sending mail, etc. In general I don’t like cursors because they are slow, but I think there are cases. I had one of those cases, and the number of records I needed to loop through was small so I have no problem doing so.

The idea is to just pass the list of ids as comma separated values as a string as a parameter to a stored procedure. Be careful how this list is generated. You never want to leave this data unchecked for SQL Injection attacks. In particular, I recommend building an array of integers (not strings) in .NET and then converting the array to a comma separated list of ids. I got the idea from here. I also has a wealth of other techniques for simulating arrays in T-SQL.

The problem you will soon run into is that you can’t use standard Cursor syntax when you use Dynamic SQL. The code below shows you how you can do so using a Cursor Variable. You can also do it using a Global Cursor. If you want to go the Global Cursor route, check out the code here.

The code below basically is called something like this:

IterateThroughItems ‘1234,456,789’

This would select three records with ids 1234, 456, 789 and then print out the ID. Instead of printing out the ID, you could print the FName, LName, sendmail, update other tables, whatever you like.

-- WARNING: The end user should NEVER be able to enter or submit any information that is then passed into
-- the @IDs parameter. If they do, SQL-injection is available for them to hijack the database.
-- Only pass data that has been type-checked, etc. For example, I recommend using an array or list of 
-- integers in .NET and then using string.join() to populate the value of @IDs.
create proc IterateThroughItems @IDs as varchar(2000)
as

Declare @SelectStmt as nvarchar(3000)
Declare @Sql as nvarchar(3200)

Declare @ID as int
Declare @FName as nvarchar(50)
Declare @LName as nvarchar(100)

-- this is a cursor variable and is needed so we can use dynamic-sql which we need to deal with the list of ids
Declare @PersonCursor CURSOR

Set @SelectStmt = 'select ID, FName, LName from Person where ID in (' + @IDs + ') order by ID'

Set @Sql = 'Set @PersonCursor = CURSOR FAST_FORWARD FOR ' + @SelectStmt + '; OPEN @PersonCursor'

exec sp_executesql @Sql, N'@PersonCursor CURSOR OUTPUT', @PersonCursor OUTPUT

FETCH NEXT FROM @PersonCursor into @ID, @FName, @LName

WHILE @@FETCH_STATUS = 0
BEGIN
    print 'Processing: ' + Cast(@ID as varchar(20))
    
    FETCH NEXT FROM @PersonCursor into @ID, @FName, @LName
END

CLOSE @PersonCursor
DEALLOCATE @PersonCursor

Using LINQ in a foreach loop to build a query

2011-01-18T11:54:00.001-07:00

I love using LINQ because it makes building up queries so easy. The exception to this is when using a foreach with a union. I suspect it is just the foreach that is causing this behavior, but for sure the behavior is consistent when using both.

Let’s pretend that you have a list of people that you let the user select one or more people from. You want to pull back the person records of just the records they selected. Maybe you want to do a database round-trip to get the latest data before an edit or may you just want to filter the list in memory. In either case, this post addresses both of them since LINQ is used in both cases. The only difference may be is that instead of a List<People> you may be using an IQueryable<Person> if you are using the database. The tricky step is identical. I know because I was originally seeing the issue on the database example I had. I wrote this using just standard LINQ (to Objects) and in memory objects to make the running of this easy for you.

With that said, I have created a Person class below that is meant to simulate the Person table in the database. I then populate it with three records (Person objects). I then specify that I want just the people with the ids 1 or 2. I then run the query, dump the results. This shows the issue. We only get one record back. Then I run the next query and dump the results. This time the results are correct and give us two records as expected.

What is the difference in implementation you ask? There is very little different. In both cases there is a foreach loop and use the union method to build up a query. Union is similar to using an OR in the where clause of a SQL statement, but often the performance is much better. The downside is that much of the query is duplicated and makes maintenance a pain. No so with LINQ. It is very easy to do so here.

The ONLY difference between what works and what doesn’t work is that I declare a LOCAL variable INSIDE the foreach loop. I then use the LOCAL variable as a parameter in the LINQ expression. I have to assume this causes it to get the value from the variable before the LINQ expression is evaluated and thus it has the value we want. Otherwise, I assume it just looks at the current (last value) of the variable declared in the foreach statement itself at the end after the foreach loop has finished executing. I don’t know exactly how it works, but that is my assumption based on what I see from the behavior of the two methods. I don’t know if I would call this a bug or not, but it is not intuitive to me and makes for it to be very easy to write buggy code if not properly tested.

public class Person
{
    public string Name {get; set;}
    public int ID {get; set;}
}

List<Person> people = new List<Person>();

void Main()
{
    // simulate a database and a table called people that has 3 records
    people.Add(new Person{ Name="Brent", ID=1});
    people.Add(new Person{ Name="Lance", ID=2});
    people.Add(new Person{ Name="Kim", ID=3});
    
    // this could ids of the records the user selected
    List<int> idsToFind = new List<int>();
    idsToFind.Add(1);
    idsToFind.Add(2);
    
    // execute the query
    var allPeople = GetPeopleByIDsBroken(idsToFind);
    
    // output the results
    allPeople.Dump();
    
    // The results will be ONE record and that is Lance
    // This might be surprising because we passed in the ids for Lance and Brent.
    
    // execute the query
    var allPeople2 = GetPeopleByIDsWorks(idsToFind);
    
    // output the results
    allPeople2.Dump();
    
    // The results will be TWO record and that is Brent and Lance
    // This is the expected result and what we wanted.
}




IEnumerable<Person> GetPeopleByIDsBroken(List<int> ids)
{
    IEnumerable<Person> builtUpQuery = null;
    
    foreach (int id in ids)
    {
         // first time through only.
        if (builtUpQuery == null)
        {    
            builtUpQuery = people.Where(p => p.ID == id);
        }
            // all subsequent times
        else
        {
            builtUpQuery = builtUpQuery.Union(people.Where(p => p.ID == id));
        }
    }
    
    return builtUpQuery;
}

IEnumerable<Person> GetPeopleByIDsWorks(List<int> ids)
{
    IEnumerable<Person> builtUpQuery = null;
    
    foreach (int id in ids)
    {
        // We have to get a local copy of the id. 
        // Othewise, the value is not taken from the id in the foreach loop 
        // until the entire expression is evaluated
        int localCopyOfID = id;
        
         // first time through only.
        if (builtUpQuery == null)
        {    
            builtUpQuery = people.Where(p => p.ID == localCopyOfID);
        }
            // all subsequent times
        else
        {
            builtUpQuery = builtUpQuery.Union(people.Where(p => p.ID == localCopyOfID));
        }
    }
    
    return builtUpQuery;
}

BrentBot is born today

2011-01-16T20:33:00.009-07:00

Here is my robot - BrentBot

I am very excited to have created a robot from scratch. It took a lot of research to figure out how I wanted to build the robot. In the end, I followed an article in Servo magazine that walked me through it. Like most things in life, it didn't go quite as I planned. I didn't have all the parts that the article required, so a lot of them I found at Ace Hardware and later found out that Fry's Electronics had cheaper screws when I bot some other stuff there. If you have a Fry's electronics where you live, you can start there. Some Radio Shacks are supposed to have some of this stuff, but most requires it be ordered online and shipped to your home or Radio Shack for pickup. There are lots of Robot companies online. I personally like RobotShop since it has such a wide variety of things and they at least have a branch in the US even though they are a Canadian company.

The cost was about $100, but I didn't end up using everything, so like the article said, it is probably pretty close to $85. If you want professional results or just want to save a ton of time fabricating the deck, connecting wheels, making servo mounts, etc I highly suggest buying them. The cost was actually only around $15, but I wanted to see what I could come up with.

I made my deck out of nylon cutting board. It is very strong and durable and doesn't split at all (from what I can tell). The downside is that it is not as easy to work with as I had hoped. For instance, I used my Jigsaw/Scrollsaw, etc to cut it and found that even on the lowest speed it just melted the nylon and made a mess and was hard to cut. By the end, I figured out that if I just tap the trigger and then push the saw through until the blade stopped this was slow enough and kept everything cool enough that it was fairly accurrate to cut with and was managable. If I had to do it again, I probably would not use this material again, but it was free since it was an old cutting board. I did sand it down to make it smooth since there were so many knife marks on it.

The cutting board was a bit thicker than the original design called for and the wheels that I chose were smaller that suggested I ended up changing the original design from a two decker to a one deck robot for now. This actually worked to my favor. Now, I was able to mount the servos and the batteries on the underside of the deck and still had all the room on the top side of the deck. So really I have the same amount of deck space, but with half the height and much less weight. I like my design actually.

I chose the Arduino Uno as the brain for controlling everything. Mostly because it is open source and open hardware and it is WAY cheaper than the Parallax Stamp Kits. Personally, I don't like VB or anything that looks like VB and that is what the Stamp uses. Unless you want to pay even more the Javaline I think it is called. That is just way too expensive for me just to not write in VB like language when I have a much cheaper (and significantly more power) processor (Arduno Uno). My goal was to build a robot under $100 and I succeeded. Granted it doesn't do much because it doesn't have any sensors. I'll add those later at additional cost obviously, but they will come in time as they are needed.

As word about Parallax and the BeoBot. If you don't want to work really hard and do this all from scratch and you want support and supported enhancements, I think the BeoBot would be great. I also think the Lego Mindstorm is a bit more pricey, but you can do so much in a short amount of time with it. If I didn't have such a strong desire to build this robot from scratch just to say that I did it all, you may want to seriously consider one of those other options. The lego solution is great because there are parts readily available and you can create so many different kinds of robots in little time.

Don't underestimate the time it takes to fabricate everything. Designing a robot from scatch takes a lot of time. You have to think about how wires will be run, line up holes on parts, match nuts and bolts, solder, and in general just spend time though trial and error. Don't forget the error part is great for learning, but takes time.

I soldered the battery connectors and barrel connector for powering the Arduino. This was time consuming. I did remember how to solder thankfully. I also figured out to de-solder because I had the polarity of the barrel connector backwards. I don't know how I did that. I did figure out that the barrel connector needs the positive on the inside part and negative on the outside part. Even with that knowledge, I got confused. Oops. Oh well, in the end it gave me a chance to add more length to the plug since the first one was a bit short. Good luck de-soldering the wires from the barrel connector if you wrap them around the posts before you solder them. They will never come off without a LOT of work, though I did finally get the wires off and new ones put on.

When cutting the servo brackets they need to fit snuggly around the servo. This is because the mounting brackets on the servos are right there close to the sides of the servos. If you leave too much space around the servo the screws that go in the mounting brackets won't have anything to screw into. This kind of defeats the purpose of the servo brackets.

I did and you probably will also need to have two sets of power supplies. One for the Arduino and one for the servos. The reason is that the Arduino can only handle so many things plugged into it. This is especially true when it comes to the Servoes. Besides, it is good design to have separate drive system power. That way there isn't noise (electrical noise) that can make your Arduino and you cicuits act funny. Don't worry, it is actually very easy. All you do is connect the grounds together and they work great.

Once BrentBot started to work, I quickly found out I will need at least one swithc for the power. I don't have one right now and everytime I want to test anything, I have to disconnect the main barrel connector, and plug in the usb cable. Not to mention, I usually have to disconnect the batteries for the drive system. While not a pain, these plugs will eventually wear out and they are not really cheap to replace. A switch is a much better solution and allows for faster test cycles.

Servos are great for a simple light weight robot that is under say 3 pounds. Over that, you'll have to get the large servos or go to a motor and gearbox. In either case, always get wheels that mount directly on the servo's shaft. If you don't, you will be spending lots of time trying to figure out how to mount a wheel onto it like I did. In the end, I think it is quite straight.

When you are writing your code to control the servos there is a library to do so. This doesn't mean you have nothing to do. You still have to write your basic functions like forward, reverse, turn left, turn right. If you don't attach and the detach for each of these functions your servos may jitter when they are idle. To prevent the jitters all just make sure you detach after each function call. This has the added benefit of saving your battery life also.

The power for the Arduino is a single 9V battery. The drive system is powered by 4 rechargeable AA batteries. I highly recommend rechareable batteries. Otherwise, you will be spending a lot on batteries. They don't really last long.

By all means, unless you have servos already, be sure to by the continuous rotation versions. They are the same price and will save you from trying to figure out how to take one apart and modifying it. If you need to modify it, there are article on the internet that tell you how to do it. I didn't want to bother with it. It seems silly these days when continuous ones are readily available at the same cost.

I found that having old computer plugs, wires, etc came in handy. If you have the long header pins they fit the computer plugs perfectly because a computer is a circuit after all. I also found the wire useful for the power lines since they are pretty thin. Yes, I know you can buy wire, but it usually isn't two wires side by side which creates less mess, and in my case I had them already so they were free to me. If you don't have them, consider an old computer from a garage sale that would have stuff you can use. Goodwill is another option that can be fairly inexpensive. On the other hand, unless you find just a screaming deal, you are probably better off just buying wire and taping them together, or using zip-ties.

It is important that you get your polarities correct. This is particular important with your servo. if you get this wrong, you can permanently damage your servo. I think the Arduino can cope even if the polarity is reversed, but I don't really recommend it. The easy way to do this is with a battery tester or better yet a multi-meter. To see which wire is the positive one, you touch the wire to the red lead of the meter, and the other wire to the black lead of the meter. If you get a positive voltage (or if the battery tester says good) then the wire on the red lead is your positive wire. If you get a negative voltage or no ready on the battery tester then the wire on the red lead is negative. In general it is a good idea to start at a larger range on your multi-meter. Says something like 20, but starting at 2 should be good also since we are talking about small voltages here and it is know to be say 9Volts for a 9Volt battery.

I hope this is useful to someone that is starting with robotics.

Getting Current User information in Silverlight 4

2010-11-11T10:35:00.001-07:00

Let’s assume for a Silverlight application. You may be wondering where to find out who the current user is. In ASP.NET there is a User property on the Page object (also other places such as the HttpContext.Current), but where is that in Silverlight 4 (not in early versions that I am aware of). The answer is the WebContext.

I think the WebContext was introduced in Silverlight 4.You The WebContext is created in the constructor of the App class that is in the App.xaml.cs file. If it isn’t you may be using a different type of Silverlight 4 project than the Silverlight Business Application or Silverlight Navigation Application. If you app doesn’t have this, I am assuming you can add it. Just create on of these projects and copy what is done there.

Among other things, the WebContext has a User property. This gives you what acts like what you may expect if you are accustomed to ASP.NET. For example, it has a Roles property to see what roles the user in. It has a IsUserInRole() method to see if a user is in a particular role. It has a Name property that gives you the username.

The WebContext is accessible from code by using WebContext and is also added to the Application Resources in the Application_Startup method which makes it accessible via XAML. To show the current username using XAML you would do something like the following:

Using XAML is really great because as Current.User changes from null to some value when the user logs in or Windows Authentication logs them in automatically, the TextBlock will be notified and display the change.

. The same is NOT true if you use code that you may write in ASP.NET to set the value. For example:

Assume you XAML is now defined like this:

You might be tempted to write the following line of code

CurrentUser.Text = WebContext.Current.User.Name;

The problem is that WebContext.Current.User may be null initially AND the biggest problem is that you won’t be able to pick up the Name since it may NOT be set yet. Really what we want is to using Bindings which notify the other side of the binding when the value changes. This means that initially we would get no username, but as soon as it is loaded we would be notified that there is a new value and we can show it.

The code is a little more complex than above, but not really too bad. Basically, we are trying to do what we did in XAML in the first example, but do it in code. Here is all you have to do:

CurrentUser.SetBinding(TextBlock.TextProperty, WebContext.Current.CreateOneWayBinding("User.Name"));

Now we the CurrentUser textblock will always display the current value of WebContext.Current.User.Name even as it changes.

Getting the Action or DynamicDataRoute from a DynamicData page

2010-11-03T11:05:00.001-07:00

If you are working in a Dynamic Data web application sometimes you may need to know what the Action (List, Details, Edit, Insert) is. The Action is just a string and is set in the Global.asax.cs in the RegisterRoutes() method that is called on application startup. Actually, if you look at each route that is added, the object being added is a DynamicDataRoute object. This is the object we eventually want to access since it has our Action property.

When you are on a PageTemplate, a CustomPage, EntityTemplate, or a UserControl that you use in one of the previous items you have access to a class called DynamicDataRouteHandler. It has a static method called GetRequestContext() which has a RouteData property. This gives us the RouteData object which has a Route property which is of type BaseRoute. Remember, we need to get an instance of the DynamicDataRoute. As it turns out DynamicDataRoute is a subclass of Route which is a subclass of BaseRoute. So, all we have to do is cast the BaseRoute object we now have to a DynamicDataRoute and we now have access to the Action property.

I guess that is a lot of explaining for a few lines of code :) Here is the code.

RouteData route = DynamicDataRouteHandler.GetRequestContext(Context).RouteData;
DynamicDataRoute ddRouteData = route.Route as DynamicDataRoute;
string action = ddRouteData.Action;

Adding an item to the ValidationSummary programmatically

2010-11-02T13:43:00.001-07:00

In ASP.NET, the ValidationSummary can be used to display errors to the end user. They can specific to a field or just be entity specific, etc. In general, field specific validation shows up next to a control that it is validating (assuming you put the validators next to it). But what about validation that happens in a Domain Service Class or your Custom BLL for example? These exceptions will by default be caught by the application and show as a nasty error to the user, or go to the error page. This is hardly the desired behavior for a validation error.

First I like to change the default behavior of bubbling up to the application to be caught to being handled at the button or page level. To do this I put a try-catch in my button action or other applicable event that you can tap into. In the catch, it would be ideal to add a custom error message to the ValidationSummary. How do we do that though?

Thankfully, it is quite easy to add an item to the ValidationSummary. The key is that the Page has a Validators property that all validators are automatically added to when you put them on your .aspx page. The problem is that we don’t have a CustomValidator.

Thus we need to create a CustomValidator, but what a pain really since we only want to use it when we actually have an exception in our BLL. My solution is to create method to encapsulate the logic to create a new CustomValidator and add it to the Page’s Validators collection. So that it can easily be accessed on any page, I have implemented it as an Extension to the Page class. Below is the code to do so.

namespace MyExtensions
{
    public static class PageExtensions
    {
        public static void AddValidationSummaryItem(this Page page, string errorMessage)
        {
            var validator = new CustomValidator();
            validator.IsValid = false;
            validator.ErrorMessage = errorMessage;
            page.Validators.Add(validator);
        }
    }
}

To use this method just put the using MyExtensions; statement at the top of your code-behind of the page that you want to use it on. Then you can do the following:

protected void btnSubmit_Click(object sender, EventArgs e)
{
    try
    {
        // do some stuff like call my BLL that may throw an exception
    }
    catch (Exception ex)
    {
        if (ex.Message == "Some key string I want to handle")
        {
            Page.AddValidationSummaryItem("Password must be at least 6 characters in length.");
        }
        else
        {
            throw ex;
        }
    }
}

WARNING: Be careful with what you display to the end user. You should never catch an exception and display the Message directly to the user. It could have information that a hacker can use to compromise your application.

UPDATE 5/5/2011:

You may notice that there are times the above method does not work as expected. In particular you may notice that the error message does not show in the ValidationSummary control as you expected even though the Page.IsValid is false (as expected). The problem is likely that you have specified the ValidationGroup on the ValidationSummary control. This is a common issue when using validators and ValiationSummary controls. They must have the same value for ValidationGroup in order to tie them together. When using DynamicData templates the ValidationGroup is NOT set so the above method works great. However, in other situations such as a simple form or maybe the FormView the ValidationGroup property may be specified depending on how your code was generated or if you changed the defaults.

To help with that issue, I have created another version (overloaded method) of this method that takes one addition parameter called validationGroup.

public static void AddValidationSummaryItem(this Page page, string errorMessage, string validationGroup)
{
    var validator = new CustomValidator();
    validator.IsValid = false;
    validator.ErrorMessage = errorMessage;
    validator.ValidationGroup = validationGroup;
    page.Validators.Add(validator);
}

WARNING: If you call Page.Validate() or Page.Validate(“your validation group here”) after the above call, the validator will be reset to valid since we hardcoded the IsValid value and the default is valid.

Returning the row index using LINQ

2010-10-19T13:48:00.001-07:00

Imagine you have a contest and each entry has a some points that they were awarded. To show an ordered list of them is easy enough. Something like the following will return the names and the related points.

Entries
.Select(entry => new {
entry.Name,
entry.TotalPoints,
}
)
.OrderBy (o => o.TotalPoints)

Now what if you wanted to show the rank (1st, 2nd, 3rd, etc) for each entry. So basically now we have three columns in the results: Rank, Name, TotalPoints. How would we do this?

The answer is hidden in the LINQ to Objects specially overloaded Select method that is available only when you use the Lambda syntax where if you specify a second parameter it will put the row index in it. Here is that same query, but with the Rank column added.

Entries.ToList()
.Select((entry, index) => new {
entry.Name,
entry.TotalPoints,
Rank = index + 1
}
)
.OrderBy (o => o.TotalPoints)

Notice how we used the ToList() to convert the query to a List first. This is critical as you will get a runtime error otherwise.

FYI, the error is: Unsupported overload used for query operator 'Select'.

Also note, the name index is not important. It is the order in the Select method that is important. The index is zero based, and I wanted to show 1 based Rankings so I added one to the index to get the Rank.

Using GroupBy with LINQ

2010-10-19T11:01:00.001-07:00

I love LINQ, but sometimes the syntax can be a little strange. In particular, I find the group by syntax to be a little weird, but not too bad once I broke it down.

For this example, let’s assume we have a person table. The person table has a column called Score and has some other fields that don’t really matter for this example. The related table is a Gender table. It has one field of importance and that is the Name field. The two rows in this table are Male and Female. There is a foreign key in the Person table that points to the Gender table. Think of it this way. The person table is the main table, and there could be a drop down list to select the gender for the person.

With that in mind, we want to know what the total score for Males and Females. We want to do this using a group by. The results will be two columns: Gender and Score.

Here is an example of the output that we desire.

Gender	Score
Female	2013
Male	1923

Here is the lamdba based LINQ query we would need to do this.

   People
   .GroupBy (e => new {Gender = e.Gender.Name} )
   .Select (byGenderGroup =>
         new
         {
            Gender = byGenderGroup.Key.Gender,
            Score = byGenderGroup.Sum (t => t.Score)
         }
   );

If we take this line by line we will see that this really isn’t so different from SQL that would be generated. Here is the SQL

SELECT SUM([t0].[Score]) AS [Score], [t1].[Name] AS [Gender]
FROM [Person] AS [t0]
INNER JOIN [Gender] AS [t1] ON [t1].[ID] = [t0].[GenderID]
GROUP BY [t1].[Name]

Let’s go line by line of the LINQ code.

People is the main table we are working with

GroupBy creates an anonymous type (that way we can easily add additional columns to group by). In this case we group by the Gender.Name just like the last line of the SQL statement.

The Select lines create another anonymous type so that we can select just the columns we want to return. Notice that byGenderGroup doesn’t have a property called Gender. Since byGenderGroup doesn’t represent a person record and actually represents the grouped results, we can access any of the columns that we have grouped by in the above GroupBy line. In this case, Key collection only gives us one property, and that is Gender. The byGenderGroup does have many other methods that are available though. One example is the sum method. In general byGenderGroup has all the aggregate functions you would have in SQL.

FYI, you can also do this without using lamdba expressions, though I personally don’t like the syntax and find it confusing.

var results = from p in People group p by new {Gender = p.Gender.Name} into byGenderGroup
select new {byGenderGroup.Key.Gender, Score = byGenderGroup.Sum (t => t.Score)};

The choice is yours.

SharePoint users can no longer upload files larger than 25 MB after moving from Windows Server 2003 to 2008

2010-10-18T19:00:00.001-07:00

I can’t take credit for this one, but it is worth a post.

Problem:

After moving SharePoint from Windows Server 2003 to Server 2008, users can no longer upload files larger than 25 MB.

Explanation:

This is by design. SharePoint, by default, restricts file uploads greater than 50 MB. But IIS 7, by default, restricts file uploads greater than 25 MB. When this threshold is reached the user gets a 404.13 error. Of course since all users have "friendly" errors turned on, it is hard to tell why the file didn't upload. One user even got a Google "Oops" page.

Solution:

Edit the web.config file for each virtual directory and force the threshold to be a little bigger than SharePoint's threshold. If the threshold is hit, you want the SharePoint error to appear and not IIS's error.

        1: Replace:

       2: <system.net>

       3: <defaultProxy>

       4: <proxy autoDetect="true" />

       5: </defaultProxy>

       6: </system.net>

       7: </configuration>

       8:  

       9: With:

      10: <system.net>

      11: <defaultProxy>

      12: <proxy autoDetect="true" />

      13: </defaultProxy>

      14: </system.net>

      15: <system.webServer>

      16: <security>

      17: <requestFiltering>

      18: <requestLimits maxAllowedContentLength="52428800"/>

      19: </requestFiltering>

      20: </security>

      21: </system.webServer>

      22: </configuration>

The change is instant. No closing of the browser or restarting of anything is needed.

KB Resource:

http://support.microsoft.com/kb/944981

Working with Dynamic Data Controls: Finding, Set Value, Get Value

2010-10-18T16:54:00.001-07:00

Once you start working with ASP.NET, FormView, and the DynamicData controls you will soon learn that it isn’t as convenient to get and set values as you might like. You may also want to find controls not by their name, but by the column / property that they are bound to in the ADO.NET Entity Model.

I have put together some extensions to the Control class that make doing all these things much easier. All you have to do is create a class in your project called ControlExtensions. Copy and paste the code below into it. Change the namespace as needed. Then on the page you want to use it, just include the namespace. Then when you look at the methods on any control you will see the methods below. I have tested the code below with a FormView and also with DynamicControls on a User Control that is then on a FormView. In both cases, the code works well.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.DynamicData;
using System.ComponentModel;

namespace DistiPromo.Helpers
{
public static class ControlExtensions
{

        /// <summary>
        /// This will find the Dynamic Data control for the specified column (name from Entity model)
        /// </summary>
        /// <param name="control">the control (such as FormView or UserControl) that directly contains the control you are looking for</param>
        /// <param name="columnName">the name of the column (from the Entity Model) that you are looking for</param>
        /// <returns>The DynamicData Control for the column that you are looking for</returns>
        public static Control FindFieldTemplate(this Control control, string columnName)
        {
            // code copied from internal method: System.Web.DynamicData.DynamicControl.GetControlIDFromColumnName
            // Since it is internal, it could change, but I needed to get to it
            return control.FindControl("__" + columnName);
        }

        public static FieldTemplateUserControl FindFieldTemplateUserControl(this Control control, string columnName)
        {
            var userControl = ((FieldTemplateUserControl)(control.FindFieldTemplate(columnName)));
            if (userControl == null) throw new Exception("Could not find FieldTemplate in Control for " + columnName);
            return userControl;
        }

        public static Control FindControlForColumn(this Control control1, string columnName)
        {
            var control = FindFieldTemplateUserControl(control1, columnName).DataControl;
            if (control == null) throw new Exception("Could not find control in Control for " + columnName);
            return control;
        }

        public static T FindControlForColumn<T>(this Control control, string columnName) where T : Control
        {
            return FindControlForColumn(control, columnName) as T;
        }

        public static object GetValueForColumn(this Control control, string columnName)
        {
            var actualControl = FindFieldTemplateUserControl(control, columnName).DataControl;
            return GetValue(control, actualControl);
        }

        public static T GetValue<T>(this Control control, string columnName)
        {
            object rawVal = GetValueForColumn(control, columnName);
            //return (T)Convert.ChangeType(rawVal, typeof(T));

            // this is a little better because it handles nullable types also
            return (T)TypeDescriptor.GetConverter(typeof(T)).ConvertFrom(rawVal);
        }

        public static T GetValue<T>(this Control control1, Control control)
        {
            object rawVal = GetValue(control1, control);
            //return (T)Convert.ChangeType(rawVal, typeof(T));

            // this is a little better because it handles nullable types also
            return (T)TypeDescriptor.GetConverter(typeof(T)).ConvertFrom(rawVal);
        }

        public static object GetValue(this Control control, Control actualControl)
        {
            if (actualControl is ITextControl)
            {
                return (actualControl as ITextControl).Text;
            }
            else if (actualControl is ICheckBoxControl)
            {
                return (actualControl as ICheckBoxControl).Checked;
            }
            else if (actualControl is ListControl)
            {
                return (actualControl as ListControl).SelectedValue;
            }
            else if (actualControl is HiddenField)
            {
                return (actualControl as HiddenField).Value;
            }
            else throw new Exception("Could not get value of unknown control type: " + actualControl.GetType().ToString());

}

        public static void SetValue(this Control control, string columnName, object value)
        {
            Control actualControl = FindControlForColumn(control, columnName);
            SetValue(control, actualControl, value);
        }

public static void SetValue(this Control control, Control actualControl, object value)
{

            if (actualControl is ITextControl)
            {
                (actualControl as ITextControl).Text = Convert.ToString(value);
            }
            else if (actualControl is ICheckBoxControl)
            {
                (actualControl as ICheckBoxControl).Checked = Convert.ToBoolean(value);
            }
            else if (actualControl is ListControl)
            {
                (actualControl as ListControl).SelectedValue = Convert.ToString(value);
            }
            else if (actualControl is HiddenField)
            {
                (actualControl as HiddenField).Value = Convert.ToString(value);
            }
            else throw new Exception("Could not set value of unknown control type: " + actualControl.GetType().ToString());
        }
    }
}

Just geeks

SQL Server Resources

Check if application is running via command line (in Windows)

Zabbix Server Tips

Topics

Zabbix Server is down

Checking Log Files

What ports is zabbix listening on

Check built in items

Checking how many instances of an application are running

Documentation of all the built in items

Adding Time Zone Support to Dynamic Data

Enhancing DateTime.ascx

Enhancing DateTime_Edit.ascx

Doing the actual Timezone Conversion

Converting CLng in MS Access SQL to T-SQL (SQL Server)

Get First or Second Word using T-SQL

Get First Word

Get Second Word

Usage

Extending the Code

Note on Performance

Efficiently Counting Spaces using T-SQL

Exactly 1 Space

Exactly 2 Space

Less than or equal to 3 Space

Open 2 Excel Files in separate windows

.XLSX

.XLS

PNG Compression Tools

A look at the topic of Artificial Intelligence (AI)

General AI

Areas of interest for me:

Data Mining

Text Mining

Reading a MS Word 2007 Document in .docx format using C#

Convert a batch of .doc files to .docx using C# and Word 2007

Email Aliases in Gmail

Free Options for reading and writing Excel Files

Using Excel PIA Articles:

OLE DB Articles

Other Useful Links

Configuring Subversion for MS Office files

Goal 1: Make subversion not automatically merge MS Office files

Goal 2: Lock the file so others cannot make changes and commit them.

Goal 3: Automatically communicate to other users that the file is locked.

Unlocking

Additional Resources

Example Configuration File

Change Default Paste to Unformatted Text in MS Word 2007

How to add another user’s inbox to Outlook

Screen-scraping / Automation Tools

Using command line to control Windows Scheduled Tasks

Monitoring

Basic Info on local machine

Basic Info for remote machine

Verbose Info

Verbose Info with CSV output to screen

Verbose Info with CSV output to file

Controlling

Disable a Scheduled Task

Enable a Scheduled Task

Other Commands

Getting Started with Microsoft Charting

Easily Disable Row Selection for a Silverlight DataGrid

Creating your own MessageBox in Silverlight

Download Source Code for Control

Usage

MessageBox

MessageBox2

Reliable Sending of Email for.Net/C# using SQL Server

Show JavaScript alert() or execute other code from Code-Behind

Get usage for all columns in a SQL Server database

Deploying RIA Services seems a bit too complicated

Renaming a Visual Studio Project

Using Batch File or Command Line to recursively find all files that have a filename that contains a particular string

Easy Way to remove Visual Studio 2010 project from Visual SourceSafe

Compiler error with VS2010 SP1 and SL 4 with WCF RIA Services and Entity Framework 4

error CS0102: The type 'MyApp.Model.MyClassHere' already contains a definition for 'MyPropertyHere'

How to Download File from inside an UpdatePanel in ASP.NET