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

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

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

-- 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) 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

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

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

1. I highly recommend you back up your registry before making any changes.
2. Open regedit.exe
3. Navigate to HKEY_CLASSES_ROOT/Excel.Sheet.12/shell/Open/command
4. Open (Double-click) the key called (Default)
5. 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”
6. Click the OK button
7. 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)
8. Your screen should now look something like the following (I appended bv to the command key, but you should probably use something different)
   
   
   
9. 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)
10. Your screen should now look something like the following:
    
    
    
11. 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

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)

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#

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

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

1. 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)
2. Open MS Word in the background (You can see winword.exe in your Processes under Task Manager).
3. Loop through each file found
4. Open the current file
5. Tell MS Word 2007 to save the file as .docx
6. Close the File
7. 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

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

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 OpenXmlWriter	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	?	Yes	Yes	No	Yes	Yes
ExcelMapper for tabular data	No	ACE & JET	?	Yes	Yes	Yes	Yes	Yes
CarlosAg Excel Xml Writer Library – uses old xml spreadsheet format	No	No	No	Yes	No	Yes	No	No (only .xml)
GemBox (for small files it is free)	No	No?	?	Yes	Yes	Yes	Yes	Yes
Koogra	No	No	?	Yes?	Yes	No?	Yes?	Yes
MyXLS	No	Yes	?	Yes	Yes	Yes	Yes	No
ExtremeML	No	No	No	Yes	Yes	Yes	No?	Yes
ClosedXML	No	No	No	Yes	Yes	Yes	No	Yes
SpreadSheetLight	No	No	Yes	Yes	Yes	Yes	No	Yes
EPPlus	No	No	?	Yes	Yes	Yes	No	Yes

My thoughts (Feb 2015)

If you are doing anything server side such as ASP.NET, I highly suggest staying away from anything that uses the MS Excel Interop (PIA). It is nice it supports .xls format though.

If you need to read data VERY fast OleDB will be WAY faster than any api.

If you need to write many MB of data I suggest you use a product that uses the OpenXmlWriter.

I have used and like ClosedXML alot. It is reasonably fast and easy to use.

I have read about SpreadSheetLight and it looks very easy to program because it is designed to work like you are using Excel. It appears that it uses the OpenXmlWriter which is as fast as you can get. It appears to be very efficient at writing large amounts of data, but less efficient for editing existing sheets. It has excellent documentation. I think this would be my choice if I were to start a new project again and didn't know the OpenXml API.

EPPlus also look good and has like 4 times the downloads of ClosedXML now. It does do Data Validations and pivot tables which is nice. The documentation seems lacking. If you want to be close to OpenXml and be able to interact with it, you may like this product.

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 for very large amounts of data 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

Other Useful Links

FileHelpers – import/export data from fixed length or delimited records using strongly type values
Web Spreadsheet
OpenXMLDeveloper.org

Configuring Subversion for MS Office files

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

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

 

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:

1. Open Outlook 2007 if you have not already
2. Go to the Tools menu and choose Account Settings…
3. Assuming you are now on the E-mail tab, click your default account.
4. Click the Change…
5. Click More Settings…
6. Click the Advanced tab
7. Click the Add… button
8. Type in the name of the user who’s mailbox or calendar, etc that you want to access
9. Click OK and OK until you are back on the screen titled Change E-mail Account.
10. Wait until the Next > button becomes active and then click it.
11. Click the Finish button.
12. 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:

1. Open Outlook 2007 if you have not already.
2. Go to the File menu and choose Open and then Other User’s Folder…
3. Type the name of the person in the Name… textbox.
4. 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

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

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

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

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

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

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

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.