As you may have heard, SQL Server 2008 now comes with two additional data types for spatial data, "Geometry" and "Geography". Thus, it is now possible to query spatial data using standard SQL syntax. You can find out more about the spatial capabilities of SQL Server 2008 @ http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx
Many people already have spatial data stored in proprietary file formats, but would like to use the power of a full-fledged database to query that data. So the question becomes, "how to we load such data into a database?"
There are two .NET Mapping Frameworks that can do this for you, and yes, there are tools that can do it for you as well, such as Morten Nielsen's shape2sql tool. You can download Morten's shape2sql application free of charge from his blog @ http://www.sharpgis.net/page/Shape2SQL.aspx
However, I prefer to use the open-source Mapping Frameworks for the simple reason that I can see the code, and alter it in case something does not work as it should.
The two .NET Mapping Frameworks I am familiar with are
SharpMap is a veteran compared to the new Cumberland framework, though I believe these frameworks complement each other. Both have data adapters that can work with spatial data in SQL Server 2008, ESRI Shapefiles, etc. In addition, Cumberland can generate Virtual Earth or Google Maps raster tiles or even KML data for Google Earth.
If you try Cumberland, please be aware that some applications may fail with a "proj.dll not found" error message. This may occur if you are running Windows Vista. This occurs because the proj.dll is actually an unmanaged DLL. It is responsible for setting projection information and you can find out more about the Proj.4 Cartographic Projection Library @ http://trac.osgeo.org/proj. It turns out that this DLL actually has a dependency on another DLL that is supposed to ship with Windows, but newer operating systems do not have it (such as Windows Vista). This special DLL is named msvcr71.dll. I recommend you download it from http://www.dll-files.com/dllindex/dll-files.shtml?msvcr71
After you download it, just make sure both proj.dll and msvcr71.dll are in the same folder, and everything should work just fine. If you are curious how I got it to work, I highly recommend Dependency Walker which is a free tool that allows you to examine any Windows library/executable and see the dependencies it has on other libraries (whether the dependencies are on system libraries or not). Dependency Walker can be downloaded for free from http://www.dependencywalker.com
Have Fun!