Technology Consulting

Basic Security Settings for ASMX

Ted Spence — Thu, 12 Aug 2010 17:18:51 GMT

I often find that Microsoft's older SOAP service pattern is quicker to set up and provides better performance than a more complex WCF service. Here are a few basic settings you should use when publishing an ASMX service.

Have an API folder

Use a completely separate folder for all your ASMX files. Don't keep them in a directory shared with regular web pages. That way you can create more reliable monitoring, better firewall rules, or authentication.

SSL

Encrypting your web communication is totally worth it for any data of value. It's easy, it doesn't have to cost you money (you can self-generate a certificate), and it gives you a box to tick on your security checklist.

In IIS 7, click on the folder that holds your ASMX files and select "SSL Settings." Click "Open Feature", and click "Require SSL."

Hide Documentation

You should probably not expose documentation for your API if you can avoid it. Create a blank page that says "Contact your account manager for documentation" and use this walkthrough to configure it:

http://www.15seconds.com/issue/040609.htm

Temp tables vs Declare Tables

Ted Spence — Wed, 04 Aug 2010 18:01:57 GMT

SQL Server offers two different ways to create a temporary table in your stored procedure. The two options are declaring a local variable and creating a temporary table.

DECLARE @test TABLE (n int, v int, etc int)

CREATE TABLE #test (n int, v int, etc int)

When you create a local variable, you don't have to worry about deleting it when you are done. Local variables have a large number of special cases that you should know about; you can pass them as parameters and use them in user defined functions. But, they are created in memory, which doesn't always result in the performance you want.

Temporary tables are generally better supported and have fewer special cases, but if you forget to delete them they can prevent you from running the same commands twice.

Our database server has a dedicated disk for TempDB. As a result, I discovered during some testing today that creating temporary tables was an order of magnitude faster than using declared tables. I don't believe this performance issue is global, but in our case it was big enough.

Now you know!

"Workbridge Associates" Scam

Ted Spence — Tue, 20 Jul 2010 17:37:28 GMT

In my junior years, I accidentally sent my resume to a recruiting company known then as "MacArthur Associates". They turned out to be a shamelessly harassing, unprofessional and rude headhunting company who inappropriately sent my resume to a number of companies and jeopardized job interviews I had already scheduled with people in my network by claiming that hiring me would trigger their referral fee, when I had already submitted my resume separately. Their high pressure sales tactics alienated companies with whom I might have had a chance during a regular interview. Eventually, I declined to work with them and moved on with my career.

However, they subsequently renamed themselves "Atlantis Partners" and later to "Workbridge Associates". I've heard they have more names as well. Each individual group has the same churn rate - they hire people to man the phone banks to harass employers with nonstop phonecalls and resume blasts. The recruiters do not know anything about the profession and know less about each individual company they target. Instead, they get their leads through trolling Craigslist, Monster.com and other job boards and attempt to sell overpriced recruiting.

I've recently been made a target as an employer as well. When I informed them that I would not work with them and asked them politely not to call me, they instead subjected me to random phonecalls from different salespeople from different affiliates all trying to get an "in". It truly leaves a bad taste. There are so many good, honest, patient, and knowledgeable recruiters in the world, why work with someone like Workbridge?

Select biggest record using LINQ

Ted Spence — Thu, 27 May 2010 21:47:59 GMT

I just realized I'd hate to write out the code to sort through a list of strings and find the longest one. So I tried to do it in LINQ. Here's what I got:

List words = new List();
... fill in a bunch of data ...
var v = (from s in words orderby s.Length descending select s).Take(1);

The "orderby s.Length" clause sorts by string length. Descending takes longest ones first and shortest ones last. The part I didn't know was the ".Take(1)" function - that's interesting! So, basically, I get the first string returned by the select statement.

Of course, now you have to take a "var" and turn it into a string object. Here's a shortcut:

IEnumerable list = v as IEnumerable;
if (list != null) {
    foreach (string s in list) {
        return s;
    }
}

Windows 7 Problems

Ted Spence — Tue, 29 Sep 2009 16:36:15 GMT

As much as I'd welcome an upgrade to Windows, I doubt I'll be interested in what Microsoft is producing.

1) The new UI has a nifty "translucent" look to everything. However, this makes it virtually impossible to glance at text - now you have to study it carefully to make sure the background isn't distorting your words. This is part of the reason I haven't grown attached to Vista, and I'm sad to see it isn't changing much in the next release.

2) Every individual part of the default UI is now more cluttered. What used to be a simple "folder" window now has tons of options, hyperlinks, and buttons. In many cases, the icons now randomly jump in size depending on what Windows thinks you have in the folder - you can go from tiny icons to huge ones, and funny "1-5 star" ratings systems interjected if the folder contains some music or video files.

3) The control panel now attempts to hide stuff it thinks you don't want to see. This causes two problems: first, it's harder to find the correct choice to make since you have to bypass all the choices it thinks you want to see; and secondly, when you make a choice, it's hard to know if you've found the correct place to make it. For example, It takes a very long time now to find "network connections", when it used to be very simple to bring up a list of all network adapters on your computer and select the one you wanted to check.

I would be very interested in an OS that would reduce the amount of unnecessary garbage that clogs up the user interface. Ubuntu and Mac OSX both do well in this regard.

Null Pointer Exceptions when Comparing Strings

Ted Spence — Thu, 06 Aug 2009 16:32:46 GMT

In the olden days, it was possible to compare strings like so:

string s1, s2;

if (s1 = s2) then begin
    PrintLn("Strings match!");
end else begin
    PrintLn("Strings don't match.");
end;

Of course, we're no longer in the Turbo Pascal era. What happens when you compare two strings using the == symbol in .net? It wraps a call to String.Equals. However, it also returns true if both strings are null!

string s1, s2;

if (s1 == s2) {
    Console.WriteLine("S1 and S2 are equivalent according to String.Equals().  They could be null!");
} else {
    Console.WriteLine("S1 and S2 have different values, or one is null and the other is not null.");
}

Another way to run this test is to use s1.equals(s2) to see if they match - but wait! Null pointer exceptions!

string s1 = null;
string s2 = "Test";

if (s1.Equals(s2)) {
    Console.WriteLine("Poof!  Null Pointer Exception!");
}

Of course, there's a simple answer. There is one and only one correct way to compare strings. Here it is:

string.Equals(s1, s2, StringComparison.CurrentCultureIgnoreCase)

That also gets rid of the "case sensitivity" bug :)

SUM() Strings in Microsoft SQL server

Ted Spence — Fri, 24 Jul 2009 16:51:42 GMT

It's really easy to sum numeric values. You write this:

SELECT SUM(revenue) FROM orders

Every so often I wish I could write a similar query for strings. For example, wouldn't it be neat if we could get a list of employees by department like this?

SELECT TO_LIST(name), department FROM employees GROUP BY department

As it turns out, there's a way to do this, but it's a little bit tricky. You have to use an XML feature to do something it wasn't really designed to do. By converting results into XML and back, you can produce lists exactly the way you might want. Here's what the query looks like:

SELECT department
, SUBSTRING(
	(SELECT ', ' + name
	FROM employees e2
    WHERE e2.department = e1.department
    FOR XML PATH(''))
  , 2, 999) AS list_of_names
FROM employees e1
GROUP BY department

Why does this work? First, you're doing a main query that just lists all the departments. So you get a list like this:

department
-----------
Sales
Accounting
Development

Then, for each record in this list, we do a subquery listing "', ' + name" for each record. That produces these results internally:

field1
-----------
, Bob
, Chris
, Alex

The XML path with a blank parameter turns that into a gigantic text string, like so:

field1
------------------
, Bob, Chris, Alex

And of course the Substring removes the first two characters. The end result is that you get exactly the list you expect!

Fast Record Insertion into Microsoft SQL Server using C#

Ted Spence — Wed, 01 Jul 2009 18:48:12 GMT

So I typically have to insert large data sets into our database. I partner with a large number of companies that send us dozens of megabytes of data daily up to gigabytes. Most of this data also needs parsing: we need to execute logic on each record before it gets pumped into the database. This prevents us from using bulk data loaders except in the most basic cases.

I'm aware of a few different ways of loading data into a SQL database; each of which has positives and negatives.

Insert using SqlCommand and SqlParameter

This is typically the slowest approach, but it works well, it's safe from SQL injection attacks, and it can be used for any number of rows from one to one million. Here's a sample insert for 10,000 rows:

SqlConnection conn = new SqlConnection(connection_string);
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO test_table (var1, var2, var3, str1, str2) VALUES (@var1, @var2, @var3, @str1, @str2)", conn);
cmd.Parameters.Add(new SqlParameter("@var1", var1));
cmd.Parameters.Add(new SqlParameter("@var2", var2));
cmd.Parameters.Add(new SqlParameter("@var3", var3));
cmd.Parameters.Add(new SqlParameter("@str1", str1));
cmd.Parameters.Add(new SqlParameter("@str2", str2));
try {
    for (int i = 0; i < 10000; i++) {
        cmd.Parameters["@var1"].Value = var1;
        cmd.Parameters["@var2"].Value = var2;
        cmd.Parameters["@var3"].Value = var3;
        cmd.Parameters["@str1"].Value = str1;
        cmd.Parameters["@str2"].Value = str2;

        cmd.ExecuteNonQuery();
    }
} finally {
    conn.Close();
}

Insert using Stored Procedures

This is a bit faster than the basic method, since the SQL server doesn't have to recompile a command each time it gets executed. However, it's still fairly slow since each insert is a separate command.

SqlConnection conn = new SqlConnection(actual_string);
conn.Open();

// Create the command string
SqlCommand cmd = new SqlCommand("EXEC insert_test @var1, @var2, @var3, @str1, @str2", conn);

// Iterate through all of the objects
try {
    for (int i = 0; i < 10000; i++) {
        cmd.Parameters.Clear();
        cmd.Parameters.Add(new SqlParameter("@var1", var1));
        cmd.Parameters.Add(new SqlParameter("@var2", var2));
        cmd.Parameters.Add(new SqlParameter("@var3", var3));
        cmd.Parameters.Add(new SqlParameter("@str1", str1));
        cmd.Parameters.Add(new SqlParameter("@str2", str2));

        // Read in all the data
        cmd.ExecuteNonQuery();
    }
} finally {
    conn.Close();
}

Insert using Stored Procedure and Transactions

This combines multiple best practices for SQL database management. First, it uses parameters to protect against SQL injection attacks. Next, it uses stored procedures, which isolates your database from your C# code and keeps the data layer separate from the business layer. Third, it uses transactions to limit the length of time the database is hit by locking. It's also faster!

// Create the database connection
SqlConnection conn = new SqlConnection(actual_string);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();

// Create the command string
SqlCommand cmd = new SqlCommand("EXEC insert_test @var1, @var2, @var3, @str1, @str2", conn);
cmd.Transaction = trans;

// Iterate through all of the objects
try {
    for (int i = 0; i < 10000; i++) {
        cmd.Parameters.Clear();
        cmd.Parameters.Add(new SqlParameter("@var1", var1));
        cmd.Parameters.Add(new SqlParameter("@var2", var2));
        cmd.Parameters.Add(new SqlParameter("@var3", var3));
        cmd.Parameters.Add(new SqlParameter("@str1", str1));
        cmd.Parameters.Add(new SqlParameter("@str2", str2));

        // Read in all the data
        cmd.ExecuteNonQuery();

        // Periodically commit the transaction
        if (i % 1000 == 999) {
            trans.Commit();
            trans = conn.BeginTransaction();
            cmd.Transaction = trans;
        }
    }
} finally {
    if (trans != null) trans.Commit();
    conn.Close();
}

Insert using Union All

This is a really clever approach that is blindingly fast, but it requires that you manually escape your text to prevent SQL injection attacks. Here's some sample code:

SqlConnection conn = new SqlConnection(actual_string);
conn.Open();

// Build a massive insert statement
try {
    StringBuilder sql;
    for (int i = 0; i < 100; i++) {
        sql = new StringBuilder();
        sql.Append("INSERT INTO test_table (var1, var2, var3, str1, str2) SELECT \r\n");
        for (int j = 0; j < 100; j++) {
            sql.AppendFormat("{0}, {1}, {2}, '{3}', '{4}' UNION ALL SELECT \r\n",
                var1, var2, var3, str1.Replace("'", "''"), str2.Replace("'", "''")
            );
        }

        // Subtract the last "union all select"
        sql.Length -= 19;

        // Insert this batch
        SqlCommand cmd = new SqlCommand(sql.ToString(), conn);
        cmd.ExecuteNonQuery();
    }
} finally {
    conn.Close();
}

Insert using XML Parsing

This is a clever trick I've seen that seems to have roughly the same performance as a Union All, but can work with big XML documents.

List values = new List();
for (int i = 0; i < 10000; i++) {
    values.Add(String.Format("{0}{1}{2}{3}{4}", var1, var2, var3, str1, str2));
}

string xmlString = XMLUtilities.BuildSQLXmlString("Items", "Item", values.ToArray());

// Create the database connection
SqlConnection conn = new SqlConnection(actual_string);
conn.Open();
SqlCommand cmd = new SqlCommand(@"  DECLARE @XML XML
                                    SELECT @XML = @xmlString
                                    DECLARE @Mapping TABLE (
                                        var1 INT,
                                        var2 INT,
                                        var3 INT,
                                        str1 NVARCHAR(255),
                                        str2 NVARCHAR(20)
                                    ) 

                                    INSERT INTO @Mapping(var1,var2,var3,str1,str2) 
                                    SELECT ParamValues.Item.query('var1').value('.','INT')
                                           , ParamValues.Item.query('var2').value('.','INT')
                                           , ParamValues.Item.query('var3').value('.','INT')
                                           , ParamValues.Item.query('str1').value('.','NVARCHAR(255)')
                                           , ParamValues.Item.query('str2').value('.','NVARCHAR(20)')
                                      FROM @XML.nodes('/Items/Item') AS ParamValues(Item)

                                    INSERT INTO test_table(var1,var2,var3,str1,str2)
                                    SELECT m.var1,m.var2,m.var3,m.str1,m.str2
                                      FROM @Mapping m", conn);
cmd.CommandTimeout = 10000;
cmd.Parameters.Add(new SqlParameter("@xmlString", xmlString));

// Iterate through all the items we're going to create
try {
    cmd.ExecuteNonQuery();
} finally {
    conn.Close();
}

Relative Performance

Bet you're wondering how they stack up! Here are the numbers for a run of 10,000 records:

Insert with Command/Parameter - 64.48 seconds
Insert Stored Procedure - 58.40 seconds
Insert Stored Procedure using BeginTransaction - 16.03 seconds
Insert Union All using SQLParameters - 3.18 seconds
Insert Union All using escaped text - 1.42 seconds
Insert XML - Occasional Crash!

Recommendations

If you're writing general purpose code, just use the plain old stored procedure approach, and use a transaction if you do multiple inserts at a time. It's guaranteed to be reliable, it's guaranteed to be a bit faster than the typical method, and it's all around good practice.

However, if you do need absolute speed, I highly recommend the Union All approach. The best part is, if you switch to a different database, most other RDBMS programs support something similar to Union All so your code will directly convert over!

CruiseControl.NET and Visual Studio - Quick Setup

Ted Spence — Thu, 11 Jun 2009 16:55:09 GMT

Up until now, my team has been using Visual Studio for all our test and build needs. We've used Visual Studio completely - test suites, solution files, etc. Recently we decided to start using CruiseControl.NET to put ourselves on the continuous integration path. What's the best way to get started quickly on CruiseControl without changing our workflow?

On the positive side, we use Subversion for our version control, which is supported right out of the box. It's very easy for us to create a new account and flag it for use of CruiseControl.net, because we run VisualSVN Server and we have active directory authentication set up.

Then the sacrifices came into play. We decided against using NANT to build our code. We have been successfully using Visual Studio 2008 to build / edit / test, and we didn't want to have to support both the Visual Studio style Solution / Project files (*.csproj, *.sln) as well as the NANT files. So that solution didn't work. Similarly, since we're using MSTEST (Microsoft's built-in test suite stuff), NUnit doesn't do everything we want.

Shame on us for using proprietary vendor code :) Anyways, turns out there is a good solution. Here's how it works.

1) Set up a desktop machine OR a Virtual PC image / VMWare image. We went with the Virtual PC solution since I could just run it on my local desktop. Microsoft publishes some stock Virtual PC Images of Windows XP that you can use.

2) Install the following programs - Subversion client, Visual Studio 2008, and CruiseControl.NET.

3) Create a new Subversion account on your repository named "CruiseControl", and grant it read-only access.

4) Edit your CCNet.config file (c:\program files\cruisecontrol.net\server\ccnet.config) to set up the project, the test solution, and launch CruiseControl as a service. We chose to send out build emails since we're still adopting the service and we need to get people used to it; eventually I'm sure folks will use the tray application.

5) You should probably copy up the files from your CruiseControl folder to your intranet server in a location where everyone can browse it. That helps you see statistics and build results.

Here's my sample CCNet.config file:


    
    
        your intranet web server URL here
        a temporary directory here
        a different temporary directory here
        10

        
            
        

        
            c:\program files\subversion\bin\svn.exe
            your subversion repository location here
            Where the code will go on your build machine
            cruisecontrol
            your password here
            true
            true
            false
        

        
            
                c:\windows\microsoft.net\framework\v3.5\msbuild.exe
                The application directory here
                The name of the solution file
                build
                300
            
            
                cmd.exe
                /c del /s/q c:\mstest-results.trx
                This clears out the data from the previous MSTEST.EXE run.
            
            
                C:\program files\microsoft visual studio 9.0\common7\ide\mstest.exe
                /testcontainer:name of your test DLL file here /resultsfile:c:\mstest-results.trx
                Run unit tests for your application and save the results to c:\mstest-results.trx
            
        

        
            
                
                    c:\mstest-results.trx
                
            
            
            
            
                               mailhost="smtp server goes here" 
                   mailport="25" 
                   mailhostUsername="cruisecontrol" 
                   mailhostPassword="password goes here" 
                   includeDetails="true">
                
                    
                
                
                    
                
                
                    Failed
                    Fixed

Scheduling Tasks on Windows

Ted Spence — Tue, 21 Apr 2009 17:15:13 GMT

In the old days I used to use the "AT" command to schedule simple recurring tasks for Windows. The "AT" command was simple enough but had some limitations, and for whatever reason it never seemed reliable, although maybe that was because I never treated it seriously.

As it turns out, Microsoft has replaced that with a new command called "SCHTASKS". Try this:

Start | Run
CMD.EXE
schtasks /query

You should see a message like this:

INFO: There are no scheduled tasks present in the system.

This just tells you that you do not have any scheduled tasks. Apparently, though, schtasks lets you set them up via the command line! Very useful. Of course, the alternative is to go Start | Control Panel | Scheduled Tasks and use the GUI; however I find many situations where I like to have the ability to execute changes automatically using the command line.

For more instructions on SCHTASKS, type "SCHTASKS /?" and you'll get a help page. The instructions seem good enough, so enjoy!

EDIT: Also note that when you're using SCHTASKS, quotes are escaped using the backslash. Here's an example of an escaped nested command:

schtasks /create /ru Administrator /rp 33d@r1294 /SC DAILY /TN "Download Scripts" /TR "C:\GamePulse\TaskRun.exe \"cmd.exe\" \"/c c:\gamepulse\running\download-scripts.cmd\" log %CUSTID% \"Download Scripts\""