| Blog Stats |
-
Blogs -
32
-
Posts -
83
-
Articles -
5
-
Comments -
34
-
Trackbacks -
18
|
| Bloggers (posts, last update) |
-
Brian Orrell
(
30,
10/26/2007 2:00 AM)
-
James Davis
(
15,
4/17/2007 2:14 PM)
-
Jeff Townes
(
9,
7/11/2008 6:05 PM)
-
Justin Finch
(
7,
6/6/2007 11:13 AM)
-
Nathan Zumwalt
(
6,
9/7/2005 8:03 AM)
-
Scott Booth
(
5,
1/15/2007 5:44 PM)
-
Aaron Boswell
(
4,
3/4/2005 2:32 PM)
-
Michael Steinberg
(
3,
10/27/2006 6:25 PM)
-
Kim Nguyen
(
2,
4/5/2007 9:48 AM)
-
Tom Grounds
(
1,
9/26/2006 12:15 PM)
-
Lisa Breytspraak
(
1,
3/8/2005 5:44 PM)
|
|
Count us in! We just registered a team to participate in the www.WeAreMicrosoft.com charity challenge weekend on January 18-20. It's a great opportunity to serve charities in our community using skills we know well.
We hope you'll join, too! 
I have been working recently with SQL Server 2005 Reporting Services to display dollar sums and volume sums, and I’m happy to report [I couldn’t resist] that I’ve found a neat trick for formatting non-currency negative numbers.
Our client requested that we apply the following formatting rules to report figures:
· Use parenthesis instead of a minus sign to display negative values
· Display no decimals and format numbers with a comma when they exceed 999
· Display the currency sign for financial figures
These are seemingly simple requirements to accomplish with standard .NET format strings. First, setting the Format property of the SSRS textbox (or other control) to C0 [c-zero] will format numbers as currency with no decimal places. This format will also automatically add parenthesis around negative numbers:
o $999 or $1,000 for positive dollar figures
o ($900) or ($1,000) for negative dollar figures
Unfortunately, none of the easy-bake format strings for numeric values appeared to automatically display parenthesis for negative numbers. I tried N0 [n-zero], G0 [g-zero], D0 [d-zero] and various other combinations. The answer was to use a custom format string instead.
In order to format numeric, non-currency values to use parenthesis when displaying negative numbers, set the Format property of the SSRS textbox to “#,0;(#,0)” (and omit the quotation marks). Problem solved!
Finally, if you’re looking to add a bit extra, format negative numbers to display in red font instead of black by adding this expression to the Color property of the textbox:
=IIf([Expression to Evaluate] >= 0, "Black", "Red")
Or, in my case, the specific formula was:
=IIf(SUM(Fields!AmountBilled.Value) >= 0, "Black", "Red") Happy Reporting!
For those who came to our talk on Microsoft's Language Integrated Query, here are links to the code and deck.
LINQ PowerPoint Deck
Code and SQL
I’ve finally learned my lesson about setting default values for C# Enumerates – always set them, because bad stuff happens when you don’t.
Our team built a web application that communicated with a business logic tier using WCF services. The design allowed us to utilize the same business and data retrieval logic across multiple client interfaces, returning Data Contracts to each interface, instead of scattering our logic enterprise-wide. Everything was working well… until we moved the application to Production.
After moving to Production, we began receiving an ugly, non-descript error message in our user interface that “an existing connection was forcibly closed by the remote host,” and we chased it for hours before we found the problem. First, we verified that we could communicate (authenticate and exchange information) with our WCF tier. Success. Next, we verified that our data schemas were the same in DEV and PROD. They were. Then, we checked for any problems with our user interface. None found. Finally, in a desperation move, I changed our database connection string to point to the development database server… and suddenly the application worked without errors… but re-pointing the connection string to production data caused the error to resurface. I was puzzled.
Finally, our first clue came to us when we checked the XML log file created by WCF. First, it logged a “Failed to send response message over HTTP” error, which was followed by an error message giving us the real error:
<Message>There was an error while trying to serialize parameter http://tempuri.org/:[WcfOperationContractName]. The InnerException message was 'Enum value '0' is invalid for type '[Client].[Application].Entity.[EnumerateType]' and cannot be serialized. Ensure that the necessary enum values are present and are marked with EnumMemberAttribute attribute if the type has DataContractAttribute attribute.'. Please see InnerException for more details.Message>
It turns out that the stored procedure feeding data to this service returned some nullable columns of data, including the enumerate value, and this column happened to be NULL in Production but not in Development. So, when WCF attempted to serialize the Data Contract and deliver it to the interface, it failed. Unfortunately, the user interface did not receive a friendly message, but at least the information was in the WCF server logs.
In order to prevent this problem, I made two code modifications. First, I included an “Unknown” Enum Member in my enumerate with a numeric value of 0:
[ DataContract]
public enum SampleEnumerateType
{
[ EnumMember] Unknown = 0,
[ EnumMember] High = 1,
[ EnumMember] Medium = 2,
[ EnumMember] Low = 3
}
Next, I added a default value to the property where this enumerate was used:
private SampleEnumerateType _status = SampleEnumerateType.Unknown;
[ DataMember]
public SampleEnumerateType Status
{
get { return _status; }
set { _status = value; }
}
And with that, the problem vanished. Our user interface correctly displayed its data – including a row with a status value of “Unknown” – and the application was ready for primetime.
I just read on Chris Koenig's blog that the next BarCampDallas event is scheduled for Saturday, September 29: https://barcamp.pbwiki.com/BarCampDallas-4?doneSave=1. There was a good buzz around the last event, and I'm looking forward to experiencing this one myself. See you there!
Houston Techfest was held on Saturday, August 25 at the University of Houston, and it was a great success for HDNUG and the event organizers. Brad Abrams flew in from Seattle for the keynote, and he and I had a great conversation late-morning about the Cider tools for creating XAML-based applications in Visual Studio 2005 and the future in VS 2008.
Speaking of VS 2008, I saw two new features which look very promising and immediately valuable to our clients. First, there’s a new type of database project – not to be confused with the db projects released with .NET 1.0 – that look like a great way to finally manage and deploy database schema changes as part of an automated build process. The demo only touched on them briefly, but I’m going to look around on Scott Guthrie’s blog to see if I can learn more.
Second, it looks like Microsoft heard me groan when I learned that there’s not a built-in web interface to invoke WCF services. With ASMX, I could launch a service, type in any required parameters (assuming they were not complex types), and then click the Invoke button. With WCF, this was not possible, so basic testing became a slight bit harder because now I had to setup a unit test project and adjust my configurations appropriately so that I could establish a connection for testing. Visual Studio 2008 (and the corresponding release of the .NET 3.5 framework) have tools that will address this need. Specifically, there’s a service host executable that will enable you to quickly host a WCF service without setting up lots of configurations, and there’s a corresponding service client executable that will then allow you invoke the hosted WCF services. The interface feels almost exactly like the ASMX interface for invoking services, and the results display in a very readable format so that I don’t have to comb through XML. While this isn’t an enterprise hosting solution, it will certainly make setting up a Hello World services example much quicker.
Finally, I gave a presentation on Building Content Managed Websites Using ASP.NET 2.0 (the DotNetNuke counterpart). The presentation covered master pages, navigation, security, and web parts, and the demo compared these technologies to the features available with the DNN portal. Click this link to download my presentation and source code: http://media.parivedasolutions.com/Public/HoustonTechFest/.
Thanks again to the event organizers. Great job!
I was trying to use FxCop 3.5 to make up for some of the missing features in the current version of Team System Code Analysis. Specifically, the spell checker.
I ran FxCop on my (fairly) clean development machine running Vista, Office 2007 and Visual Studio 2005 Team Suite SP1.
Everything ran, but it found no spelling errors even when I purposely put them in to confirm that spelling was being checked.
The solution? Install Office 2003 Proofing Tools - available on MSDN Subscriber Downloads. Evidently the API changed significantly between 2003 and 2007. The next version of FxCop will ship with its own non-Office-dependent spell checker and this fix won't be required.
If you have been working with WPF and have had issues with your ComboBox or any list control binding then you can stop pulling your hair out. The problem occurs when you try to bind the SelectedValue property of the control to a Nullable Int (int?). I am currently working with Microsoft to resolve the root problem, but here is a work around for now:
1. Create an IValueConveter:
public class NullIntToNegOneConverter : IValueConverter
{
public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
{
if (value == null)
{
return -1;
}
else
{
return value;
}
}
public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
{
int i;
if (int.TryParse(value.ToString(), out i))
{
if (i == -1)
{
return null;
}
else
{
return value;
}
}
else
{
return value;
}
}
}
2. Add a -1 value to the list that your control is bound to:
Person nullPerson = new Person();
nullPerson.Id = -1;
nullPerson.Name = "None";
people.Insert(0, nullPerson);
3. Bind to the Selected Value using the Converter:
<ComboBox ItemsSource="{Binding Source={x:Static Application.Current},Path=People}"
SelectedValuePath="Id" DisplayMemberPath="Name" IsSynchronizedWithCurrentItem="False"
Margin="3,3,3,3" HorizontalAlignment="Right" FontSize="18"
SelectedValue="{Binding Path=PersonId, UpdateSourceTrigger=Explicit, Converter={StaticResource NullIntToNegOneConverter}}"/>
You should be all set. If your id is null then you get the -1 value that you inserted in the list because the convert converts the null value to -1. However, when you go to save it to the database, the convert also converts the -1 back to a null value, so you won’t have any -1 values in your database.
I ran across a weird error when I was trying to open an existing SSIS package in VS2005:
Error 1 Error loading 'Package.dtsx' : Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED))
To fix the problem do the following:
1. Open the command prompt
2. Navigate to C:\Program Files\Microsoft SQL Server\90\DTS\Binn
3. Run C:\Program Files\Microsoft SQL Server\90\DTS\Binn>regsvr32 dts.dll
That should fix the problem.
Say you have a page like this:
< asp:TextBox ID="NameTextBox" runat="Server" /><asp:Button ID="SubmitButton" Text="Submit" runat="server" OnClick="SubmitButton_Click" />
<asp:Label ID="MessageLabel" runat="server" />
With the following code behind.
using System;
namespace AspUnitTest
{
public partial class _Default : System.Web.UI.Page
{
protected void SubmitButton_Click(object sender, EventArgs e)
{
MessageLabel.Text = string.Format("Hello, {0}", NameTextBox.Text);
}
}
}
Create a unit test with the following code to test it.
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Microsoft.VisualStudio.TestTools.UnitTesting.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System;
namespace AspUnitTest.Test
{
///
/// Summary description for DefaultAspxTest
///
[TestClass]
public class DefaultAspxTest
{
private TestContext testContextInstance;
public TestContext TestContext
{
get { return testContextInstance; }
set { testContextInstance = value; }
}
[TestMethod()]
[HostType("ASP.NET")]
[UrlToTest("http://localhost:50551/default.aspx")]
[AspNetDevelopmentServerHost("C:\\Users\\brian.orrell.PARIVEDA\\Documents\\Source\\Samples\\AspUnitTest\\AspUnitTest")]
public void TestNameEntry()
{
Page page = testContextInstance.RequestedPage; //1
TextBox NameTextBox = (TextBox)page.FindControl("NameTextBox");
Assert.IsNotNull(NameTextBox);
Button SubmitButton = (Button)page.FindControl("SubmitButton"); //2
Assert.IsNotNull(SubmitButton);
Label MessageLabel = (Label)page.FindControl("MessageLabel");
Assert.IsNotNull(MessageLabel);
PrivateObject po = new PrivateObject(page); //3
string name = "Brian Orrell";
NameTextBox.Text = name;
po.Invoke("SubmitButton_Click", SubmitButton, EventArgs.Empty);
Assert.AreEqual(string.Format("Hello, {0}", name), MessageLabel.Text);
}
}
}
Pretty easy.
At Pariveda, we typically use a standard Config project in Visual Studio to keep track of all our configuration files and accessor classes. We then write MSBuild scripts to deploy them to the appropriate web, service, winform, wpf, wcf directories as appropriate. This provides the ability to maintain a single set of config files even if they are being deployed to multiple application source environments.
This is especially appropriate with testing frameworks such as nUnit and Team Test. In order to test effectively, many files need to be copied to the running test directory as well as the main application directory. Instead of having to write duplicate msbuild tasks for copying to these directories, it would be nice if we could iterate through a list of directories that the files needed to be copied to.
You see this question being asked all the time: How do I recursively copy a series of files to another folder? That question has been answered multiple times on multiple sites. But what about needing to copy that same series of files to multiple different deployment folders? The scenario is like the one I described above or if you were wanting to automate deployment of files to a web farm. The answer is using the ItemGroup appropriately. If you can create an ItemGroup that contains the list of deployment directories that need to receive the files, you can write the script once that will iterate over this list of deployment directories and copy them to all of them.
MSBuild is certainly not as intuitive to me as NAnt. NAnt seems to be more understandable proceduraly (foreach tasks, etc) while MSBuild is more geared towards XSL type transformations. Once I got the hang of how to use ItemGroups, however, I can see how powerful MSBuild as a scripting language can be.
<Project DefaultTargets="CopyConfig" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<!-- Define the list of directories to be copied to -->
<PropertyGroup>
<ConfigFolder>c:\msbuild\config</ConfigFolder>
</PropertyGroup>
<!-- Setup the directories to deploy to -->
<ItemGroup>
<ConfigDeploy Include="Service">
<Path>c:\msbuild\Service</Path>
</ConfigDeploy>
<ConfigDeploy Include="Test">
<Path>c:\msbuild\Test</Path>
</ConfigDeploy>
</ItemGroup>
<!-- Now the task that copies the config files-->
<Target Name="CopyConfig" Condition="'@(ConfigDeploy)'!=''">
<!-- Delete any pre-existing deployment folders (Optional but good for testing) -->
<RemoveDir Directories="%(ConfigDeploy.Path)" />
<!-- Get all Files within the config directory and create multiple sets of the files
with metadata that describes where the files are to be deployed to -->
<CreateItem Include="$(ConfigFolder)\**\*.*"
AdditionalMetadata="ToDir=%(ConfigDeploy.Path)">
<Output TaskParameter="Include" ItemName="ConfigFilesToDeploy" />
</CreateItem>
<!-- Now copy all of the files to the appropriate folders -->
<Copy SourceFiles="@(ConfigFilesToDeploy)"
DestinationFolder="%(ToDir)\%(RecursiveDir)" />
</Target>
</Project>
I am currently working with a team to build a large, distributed point-of-sale application for a national flight services company using WPF and WCF. We’ve been making great progress on the software dev side, but lately we’ve encountered a few hardware troubles and tricks that I thought I should pass along…
Background
Our first hardware challenge was supporting MagTek USB credit card swipe blocks in a terminal services environment. We were (and are) going to support terminal services operations as an alternative to local application installation, but the swipe block wasn’t cooperating.
Swipes emulate keyboard input, so in theory the swipes should look identical regardless of whether the software is local or on a terminal server. But, as we like to say,
object reality != theory.InUse(environment);
Issue
We swiped the same credit card on a local machine and terminal server, but the results were different.
Local Machine Test
This was the correct swipe, and it was what all of the swipes should look like. Incidentally, I’ve altered the swipes AND used a test-only credit card, so no devious purchases for you, folks.
%B4716150001669044^BAKER/GREGORY ^0108XXXX000000767000000?;471615000166XXXX=...
Terminal Server Test
The terminal server was not just wrong – it was also inconsistent. Two swipes resulted in two different character strings.
Swipe 1
5b$7161500016690446bakEr/grEgory 60109XXXX000000767000000/;471615000166XXXX=...
Swipe 2
%b47161500016690446baker/gregORy 60109XXXX000000767000000/;471615000166XXXX=...
It’s easy to see the differences between these swipes and the first (local machine) swipe. Most notably, Swipe #1 has the wrong character at the beginning of the string. Then, you’ll notice a missing ^ character and some inconsistencies in the name capitalization of the cardholder name on both swipes.
Resolution
The resolution is fairly simply yet obscure.
When you connect to the terminal server (or remote desktop session), click Options, navigate to the Local Resources tab, and set the “Apply Windows key combinations” setting to “On the local computer” instead of the default “In full screen mode only”. Thanks to Nathan Caneday for finding this one!
Final Terminal Server Test
After toggling this setting, here was the resultant swipe, a perfect match…
%B4716150001669044^BAKER/GREGORY ^0109XXXX000000767000000?;471615000166XXXX=...
Needle in the haystack.
Dallas Code Camp was held on Saturday, April 21 at Microsoft's offices in Irving, and the event was a great success. The event had strong attendance, and the presenters were educational and entertaining... or, as my wife reminds me... as entertaining as a group of developers can be.
Brian Orrell presented on SharePoint Mash-ups, and I gave a presentation on Building Content Managed Websites Using ASP.NET 2.0 (the DotNetNuke counterpart). The presentation covered master pages, navigation, security, and web parts, and the demo compared these technologies to the features available with the DNN portal. Click these links to download my presentation and source code.
Kudos to the Dallas Code Camp team for organizing a successful event. I look forward to next year!
Nigel Rivett wrote an awesome blog on partitioned tables in SQL Server 2005 that provides a lot of useful information and applicable examples. If you are thinking of partitioning your tables, start here.
Information Covered
- Creating a Partitioned Table
- Adding and Removing Partitions
- Switching a Populated Partition
- Identities in a Partitioned Table
- Clustered Indexes
- Unique Indexes
- Non-Unique Indexes
- Computed Partitioned Functions
- Partitioning on Multiple Columns
- Sliding Date Range Partitions
The Panel - Code and Examples
Code examples and applications for WPF and Windows Live applications. The Panel is the place to collect showcases of new user experiences and provide insights on how they were implemented. WPF applications and Windows Live gadgets are featured.
Better late than never. Here are the links to our AJAX Presentation we presented at the January Dev Cares event.
Session 1
Session 2
When I tried to push some schema changes to a database under replication the other day, I came upon the angry red error message:
"Cannot add columns to table 'XXXX' because it is being published for merge replication."
Luckily, in SQL Server 2000, there is a very simple solution to this issue.
1. Open up Enterprise Manager > [Your SQL Server 2000 DB Server] > Replication > Publications > [Your Publication]
2. Right click on [Your Publication] and select "Properties". This should pop up a new dialog.
3. Select the "Filter Columns" tab and you will see a listing of the tables in this publication.
4. Select the table you want to add a column to and select the button "Add Column to Table". This will pop up a new dialog.
5. Enter a column name in the "Column Name" textbox and a column definition in the "Column definition" textbox. For example, if you want to add column TestColumn of type varchar(50):
Column Name: "TestColumn"
Column Definition: "varchar(50) NULL"
You must allow nulls in this column or have a default value.
6. Hit OK and you are done! You will immediately see the new column in your Publisher database, and depending on your replication schema, will see the new column in subscriber databases soon after. In my case, we had a merge replication schema set up and the new column showed up in the subscriber database in less than a minute.
You may also remove columns from tables in a similar manner. When on the "Filter Columns" tab, you can select a column in a table, and the button "Drop Selected Column" will become enabled. You will see a confirmation message when you select this button.
In my research for an answer I also learned that you can run the stored procedures "sp_repladdcolumn" and "sp_repldropcolumn" on the publisher database to get the same results.
Also, if you are working in SQL Server 2005 replication, you can go here to read about schema changes: http://msdn2.microsoft.com/en-us/library/ms151870.aspx
Hope this helps you out if you are looking at schema changes with replication in the future!
BEGIN TRANSACTION TransactionName
BEGIN TRY
PRINT 'Do work here'
END TRY
BEGIN CATCH
PRINT 'ERROR: ' + CAST(ERROR_MESSAGE() AS VARCHAR(1000))
PRINT 'Aborting and Rolling Back'
ROLLBACK TRANSACTION TransactionName
END CATCH;
-- <- Use this section for unit testing -> --
PRINT 'Aborting and Rolling Back'
ROLLBACK TRANSACTION TransactionName
-- <- Use this section to commit your changes -> --
--PRINT 'Commiting Transaction'
--COMMIT TRANSACTION TransactionName
I have recently been working on a WPF touch screen application that doesn’t lend it self well to normal scroll bars. Luckly this is no problem with WPF because you have the ability to easily modify how a control is rendered. Below is a rough template that puts a button above and below scrollable content and when clicked they provide the same functionality as the default scroll bar.
<ControlTemplate TargetType="{x:Type ScrollViewer}"
x:Key="ButtonOnlyScrollViewer">
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="*"/>
<RowDefinition Height="Auto"/>
Grid.RowDefinitions>
<Grid.ColumnDefinitions>
<ColumnDefinition />
<ColumnDefinition />
Grid.ColumnDefinitions>
<RepeatButton Grid.Row="0" Grid.Column="0" HorizontalAlignment="Stretch"
Content="ScrollUp"Command="ScrollBar.LineUpCommand"/>
<ScrollContentPresenter Grid.Row="1" Content="{TemplateBinding Content}"
Grid.Column="0" ScrollViewer.VerticalScrollBarVisibility ="Auto"
Height="Auto" Margin="{TemplateBinding Margin}"/>
<RepeatButton Grid.Row="2" Grid.Column="0" Height="20"
Content="ScrollDown"
Command="ScrollBar.LineDownCommand"/>
Grid>
ControlTemplate>
If you've ever had to strong name sign the MS Enterprise Library, you'll find that it is a rather difficult task.
Luckily, this link below is a very detailed and complete explanation of how you do it. I followed these instructions exactly and was able to successfully strongly name the entire library.
http://www.mvwood.com/blogs/blog/archive/2006/06/22/Adventures_in_Strong_Names_with_Enterprise_Library.aspx
The code for the server-side ASP.NET AJAX 1.0 implementation was released this morning. You can download it here. It is being released under the Microsoft Reference License (Ms-RL). Included with the source code are debugger symbols for the shipped binary, which will allow you to step from your own code into the ASP. NET AJAX library while debugging, with line number and symbol data preserved. Note that the setup installs the source code locally on your machine within the "\Program Files\Microsoft ASP.NET\ASP.NET 2.0 AJAX Extensions\v1.0.61025\Source" directory.
Check out ScottGu's Blog for more info.
With all the new development tools coming out it is difficult to figure out which version works with which version. Some are in Beta, some in CTP and others fully released. If you are looking to start developing in WPF and WCF here are the 3 things you need:
1. .NET 3.0 Framework - Version 3.0
http://www.microsoft.com/downloads/details.aspx?FamilyId=10CC340B-F857-4A14-83F5-25634C3BF043&displaylang=en
2. Windows SDK - Version 1.0
http://www.microsoft.com/downloads/details.aspx?FamilyId=C2B1E300-F358-4523-B479-F53D234CDCCF&displaylang=en
3. VS 2005 Extensions - November 2006 CTP
http://www.microsoft.com/downloads/details.aspx?FamilyId=F54F5537-CC86-4BF5-AE44-F5A1E805680D&displaylang=en
Assuming that you have Visual Studio 2005 installed, download and install them in the above order and you should be ready to go.
Great Speech!
The Art of Innovation Video
The Art of Innovation Visually Summarized (1024 x 558 - 348k) (4075 x 2217 - 7.4mb)
p.s.: minus the bashing of consultants
The Visual Literacy Organization put out this absolutely stunning and amazing periodic table of visualization methods. This is an amazing resource for any person trying to visualize almost anything and lets face it we are all visual learners to some degree. Just hover over one of the blocks and you should get a pop-up that gives you an example of the visualization. I don't say this much, but this is incredibly useful. WOW!
If you ever have a situation in which you need to convert a DateTime data type into hex, here's an easy way to do so:
If you do this conversion often, use constant:
const long ticks1970 = 621355968000000000; // .NET ticks for 1970
This constant was generated with:
DateTime dt70 = new DateTime( 1970, 1, 1, 0, 0, 0, 0 );
long ticks1970 = dt70.Ticks;
get current time:
int gmt = (int) ((DateTime.UtcNow.Ticks - ticks1970 ) / 10000000L);
String hexDate = gmt.ToString(“X2“);
or you can convert back:
int gmt = 0x3e482b89; // sample GMT time in seconds since 1970
DateTime yourDateTime = new DateTime( ticks1970 + gmt * 10000000L );
Erika has compiled an awesome list of resources that everyone should have in their back pocket. Check it out!
2007 Microsoft Office System - Top Ten List of Resources for Developers and Architects
A NULL value in a database record can create some interesting problems for a variety of database operations, but people get especially confused about how to best deal with them for joins. Let's say you have these records in your SalesPerson table:
LastName FirstName Phone
Humphrey John 214.555.1111
Steinberg Michael NULL
Boswell Aaron 214.555.2222
Orrell Brian 214.733.3333
And you have an older version of the table with these records:
LastName FirstName Phone
Humphrey John 972.555.4444
Steinberg Michael NULL
Boswell Aaron 214.555.2222
If you wanted to know which records from the old version were exact matches in the new version, you could join them and see what matches. Since you don't have a unique primary key and because you want to return records that are complete matches, you have to use all fields in the join, which would look like this:
SELECT
SalesPerson.LastName
, SalesPerson.FirstName
, SalesPerson.PhoneNumber
FROM
SalesPerson
JOIN
SalesPerson_OLD
ON SalesPerson.LastName = SalesPerson_OLD.LastName
AND SalesPerson.FirstName = SalesPerson_OLD.FirstName
AND SalesPerson.PhoneNumber = SalesPerson_OLD.PhoneNumber
Only one record is returned from this query: Aaron Boswell is a complete match in both tables. John Humphrey was left out because his phone number changed and Brian Orrell only existed in the new version of the table. But why didn’t the record for Michael Steinberg result in a match?
Of course, the reason is the NULL value for the phone number. The important thing to keep in mind about a NULL value is that it does not just mean “empty” or “nothing.” It can also mean “unknown.” Since 2 unknowns can not be said to be equal, it follows that NULL <> NULL.
So, what’s the right solution? One option is to use ISNULL to convert all NULL values to something that can be compared:
SELECT
SalesPerson.LastName
, SalesPerson.FirstName
, SalesPerson.PhoneNumber
FROM
SalesPerson
JOIN
SalesPerson_OLD
ON ISNULL(SalesPerson.LastName, '') = ISNULL(SalesPerson_OLD.LastName, '')
AND ISNULL(SalesPerson.FirstName, '') = ISNULL(SalesPerson_OLD.FirstName, '')
AND ISNULL(SalesPerson.PhoneNumber, '') = ISNULL(SalesPerson_OLD.PhoneNumber, '')
This was the solution I initially came up with in a real world problem, until Samir pointed me in the right direction. The query gets the results we are expecting (both Aaron and Michael are matches), but it has a couple of major drawbacks:
1. By using a function to convert the data, we are preventing the use of any indexes on these columns for the join.
2. We would implicitly be making a business decision to treat NULLs as Empty Strings. This could lead to false positives if one of the fields was a real empty string and the other started off as NULL. False positives become even more problematic when dealing with numeric and other data types where you would have to treat NULLs as zeroes or some other arbitrary number.
The better option is to check for NULL values in the relevant columns without converting the values. To do that, the query would look like this:
SELECT
SalesPerson.LastName
, SalesPerson.FirstName
, SalesPerson.PhoneNumber
|