Archive

Posts Tagged ‘SQL Server 2008’

Mapping .NET CLR Datatypes with SQL Server 2008 Parameters

October 12, 2009 Leave a comment

The following table maps .NET CLR datatypes with SQL Server parameters.  The full Microsoft article is here.

SQL Server data type CLR data type (SQL Server) CLR data type (.NET Framework)
bigint SqlInt64 Int64, Nullable<Int64>
binary SqlBytes, SqlBinary Byte[]
bit SqlBoolean Boolean, Nullable<Boolean>
char None None
cursor None None
date SqlDateTime DateTime, Nullable<DateTime>
datetime SqlDateTime DateTime, Nullable<DateTime>
datetime2 SqlDateTime DateTime, Nullable<DateTime>
DATETIMEOFFSET None DateTimeOffset, Nullable<DateTimeOffset>
decimal SqlDecimal Decimal, Nullable<Decimal>
float SqlDouble Double, Nullable<Double>
geography SqlGeographySqlGeography is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack. None
geometry SqlGeometrySqlGeometry is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack. None
hierarchyid SqlHierarchyIdSqlHierarchyId is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack. None
image None None
int SqlInt32 Int32, Nullable<Int32>
money SqlMoney Decimal, Nullable<Decimal>
nchar SqlChars, SqlString String, Char[]
ntext None None
numeric SqlDecimal Decimal, Nullable<Decimal>
nvarchar SqlChars, SqlStringSQLChars is a better match for data transfer and access, and SQLString is a better match for performing String operations. String, Char[]
nvarchar(1), nchar(1) SqlChars, SqlString Char, String, Char[], Nullable<char>
real SqlSingle Single, Nullable<Single>
rowversion None Byte[]
smallint SqlInt16 Int16, Nullable<Int16>
smallmoney SqlMoney Decimal, Nullable<Decimal>
sql_variant None Object
table None None
text None None
time TimeSpan TimeSpan, Nullable<TimeSpan>
timestamp None None
tinyint SqlByte Byte, Nullable<Byte>
uniqueidentifier SqlGuid Guid, Nullable<Guid>
User-defined type(UDT) None The same class that is bound to the user-defined type in the same assembly or a dependent assembly.
varbinary SqlBytes, SqlBinary Byte[]
varbinary(1), binary(1) SqlBytes, SqlBinary byte, Byte[], Nullable<byte>
varchar None None
xml SqlXml None

Hope this helps.

A Practical Architecture for WCF – Part 1

August 20, 2009 Leave a comment

This is the first in a series of articles designed to provide an example of a practical architecture for WCF in its support of a services oriented architecture.

We are going to look at how you could take an existing web site and wrap its functionality in services to be consumed by either a web site or a windows forms application.  We will show you how to configure your network and servers to offer these services across domains and machine boundaries.  We’ll also take a look at how to structure data contracts to provide robust objects and lists of objects across the wire with exception reporting.  Finally we’ll look at how to configure support for distributed transactions and some suggestions for structuring projects to make testing and deployment easier.

There are a few references that I recommend using as you are learning WCF and they are posted here on the Computer Mutt.  Those resources will address a wider range of WCF concepts much more in-depth than I will here as this article is focused on providing an approach to a particular problem, and not structured to review all options that are available.  Michelle Leroux’s introduction in the chapter on working with WCF in Visual Studio 2008 which covers SOA and WCF is total gold, and every time I re-read it I get something out of it.

Ok, let’s take the first step look at what we have and what we want to wind up with.  We are starting out with a current web site that site is a very simple ASP.NET 3.5 public web application in production that provides a list of cheese brands and cheese types that are made without animal rennet.   It has discreet layers consisting of a data layer, business layer and presentation layer in the form of web pages.  The data layer talks to Microsoft SQL Server 2008 and runs on IIS7 on Windows Server 2008.  Everything is 64 bit and everything is on a single box.  Basically it is simple running website and thus a good candidate for a first step.

Here is a network diagram showing the current architecture of the website we are going to transform:

Current Architecture

The diagram below is what the architecture will look like after we transform it.  The differences are that major components are separated by machine boundaries.  The database has its own box, services has its own box, and the web site has its own box.  On the services box we also have the original business and data layers along with IIS.  

Once we transform our original website and began offering services, we are simply serving content to presentation layers both inside and outside of your network allowing you to use either browsers or Windows Forms applications.  In the case of a browser, the user points to a URL to get content, in your Windows Forms application, you can configure an endpoint to point to, either way, desktop configuration and support can be much simpler as a result.

Configuring the environment for services behind the firewall is simpler than offering them over the network, and the scope of this series is for services on the inside of the firewall.  In a subsequent article, I’ll walk you through the steps necessary to offer secure services over the internet.

NewArchitecture

We are going to use Visual Studio 2008 with .NET 3.5 along with WCF Services Templates to code services.  Development is on a Vista machine.  An aside on Vista first.  I’ve heard how Vista sucked and to avoid it, however when I switched to developing and supporting ASP.NET apps on Windows Server 2008 & IIS7, I found that using Vista simplified development. 

First, Vista uses IIS7 (which is the version on Server 2008) and it understands the web.config files generated by Visual Studio 2008.  I can also configure those web.config files once in one place in the file, unlike developing on XP where you have to configure the same thing in several places in the file.  Yes, UAC blows, but you can turn it off and I have had no problems using it.  I’m not advocating that you switch from XP if you want to develop services to run on Windows Server 2008, I’m just passing along that I found with Vista I created a less error prone development environment.

One last word on environment.  While you will be able to do everything in this series of articles on one machine, it will work out better for you in the long run if you have an Active Directory environment with at least three dedicated boxes; Web, Services, Database.  If you have limited equipment, consider creating seperate environments using Hyper-V.  Go to my recent post for help on creating and cloning Hyper-V environments. 

I really want to emphasize that it is in your interest to invest the sweat in creating seperate environments to run the examples in this series.  The reason is that a lot happens by default when you run everything on a single box and you’ll never realize it until you begin to move things around, and by then you’ll have to spend an undetermined amount of time to backtrack to uncover your mistakes.  Also, nobody is really going to be running this stuff on a single machine in a production environment anyway, so you might as well get used to it right off of the bat.  If you understand what the network requires to support WCF you’ll be more valuable to your company and customers.  

Before finishing this introduction, some thoughts on the Web Client Software Factory (WCSF).  This tool will allow you to code WCF services using a visual designer in Visual Studio.   One of its strengths is that it forces a particular format on project directory structure which can be advantagous since everyone on the team will be forced to structure their work the same way.   I began working in WCF using this tool, and I have to say it was a very confusing time for me as took a lot of effort to understand where the tool left off and WCF began; it was unnecessairly complex.

Furtheremore, once you start a project in the tool and need to make changes, you must go back and make them through the designer.  There are some other very severe side effects to.  One is that the tool will create directory and filenames that will break Visual Studio and this goes double if you are using Team Foundation Server.  Having outlined some of my concerns, you have to that creating such a tool is a huge accomplishment, and although this tool is just in its beginning stages, over time I am certian refinements will be introduced smoothing out some of its early difficulties.

In my mind it is best to start out simply and do everything manually so you get a clear understanding of what you need to do to support WCF services.

Next up – A Practical Architecture for WCF – Part 2 which will demonstrate the creation of services.

%d bloggers like this: