Improve your unit testing with:
Subscribe Now
Tuesday, November 13, 2007
Sunday, November 11, 2007
Futzing with FUTS - Part I
Unit testing is a well accepted practice in the software development community. There are many tools and articles devoted it. Google 'unit testing' if you have any doubts.
What about those of us working in the SAS realm? Given that SAS is basically a data-oriented scripting language, is it feasible to even think of unit test SAS code? I would say, "of course it is, it's code afterall!" If there is code, we can test it. There's CUnit for heaven's sake! I've even had the pleasure of using it (and then opted to roll my own C unit tests). :)
A typical SAS program moves data around or analyzes it and consists of data steps (cursor style data manipulation) and/or procedures ("procs"). Also thrown in are miscellaneous statements to make it all work how it should (e.g., libname statements). Here is SAS program that creates a text file with 100 random numbers between 1 and 10.
data _NULL_;
file "c:\my_folder\random.txt";
do i = 1 to 100;
r = 1 + int(10*ranuni(-1));
put r;
end;
run;
Naturally, SAS is capable of WAY more powerful things, but we must start simple.
What if I want to test my random number generator to make sure that it always and only generates numbers between 1 and 10? How would I do such a thing in SAS? Let's change gears for a minute and consider what we would do in C#. In C# we would have solution containing three projects: a class library called RandomNumberGenerator, a console application called RandomClient, and a class library called TestHarness.
RandomNumberGenerator
using System;
namespace UtilityLib
{
public class RandomNumberGenerator
{
private static Random generator = new Random();
public static int Ranuni()
{
return generator.Next(1, 11);
}
}
}
RandomClient
using System;
using System.IO;
using UtilityLib;
namespace RandomClient
{
class Program
{
static void Main(string[] args)
{
using (StreamWriter writer = new StreamWriter(@"c:\my_folder\random.txt"))
{
for(int i=0; i<100; ++i)
writer.WriteLine(RandomNumberGenerator.Ranuni());
writer.Flush();
writer.Close();
}
}
}
}
TestHarness
using System;
using UtilityLib;
using NUnit.Framework;
namespace TestHarness
{
[TestFixture]
public class RandomNumberGeneratorTestSuite
{
[Test]
public void Ranuni_TestBounds()
{
for (int i = 0; i < 100; ++i)
{
int r = RandomNumberGenerator.Ranuni();
Assert.IsTrue(r >= 1 && r <= 10);
}
}
[Test]
public void Ranuni_TestFullRange()
{
int[] counts = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
for (int i = 0; i < 100; ++i)
counts[RandomNumberGenerator.Ranuni() - 1] += 1;
for (int i = 0; i < 10; ++i)
Assert.IsTrue(counts[i] >= 1);
}
}
}
Now let's get back to SAS. What can we similarly do to test random number generation in SAS? The trick to unit testing in SAS is to place the code you want to test (your black box, if you will), into a macro. So the random number generation code becomes a SAS macro like this.
RandomNumberGenerator
%macro RandomNumberGenerator;
1 + int(10*ranuni(-1))
%mend;
Now my client code looks like this. It just calls the RandomNumberGenerator macro 100 times to create the output file.
RandomClient
data _NULL_;
file "c:\my_folder\random.txt";
do i = 1 to 100;
r = %RandomNumberGenerator;
put r;
end;
run;
Now what about a test harness for RandomNumberGenerator? It is finally time for FUTS (Framework for Unit Testing SAS® programs) to make its appearance. FUTS, a free product from Thotwave, is a wonderful set of easy to use assert SAS macros that test for various conditions - similar to the set of NUnit asserts. Unlike NUnit, FUTS doesn't have a slick GUI, and instead FUTS throws errors into the SAS log when an assert fails and writes nothing to the log in case of success. To run your tests, run the test harness code and check the SAS log for errors.
To test the RandomNumberGenerator macro, I first create a temporary dataset called test1 that contains 100 random numbers. To do a lower and upper bounds check (i.e., all random numbers are between 1 and 10), I select the max(r) and min(r) into macro variables and use the FUTS macro %assert_sym_compare to test (a) minr is greater than or equal to (GE) 1 and (b) maxr is less than or equal to (LE) 10. This is equivalent to Assert.IsTrue(r >= 1 && r <= 10); in the C# test Ranuni_TestBounds() above. The second test, making sure that each number from 1 to 10 is generated at least once, is accomplished by first performing a proc freq (count how many times each value appears), then getting the count for each number (1, 2, ..., 10) into a macro variable and testing, using %assert_sym_compare again, that each count is GE 1.
TestHarness
data test1;
do i = 1 to 100;
r = %RandomNumberGenerator;
output;
end; drop i;
run;
proc sql noprint;
select min(r), max(r) into :minr, :maxr from test1;
quit;
%assert_sym_compare(&minr, 1, type=COMPARISON, operator=GE);
%assert_sym_compare(&maxr, 10, type=COMPARISON, operator=LE);
proc freq data=test1;
tables r / noprint out=freqout;
run;
proc sql noprint;
select count into :count1 from freqout where r=1;
select count into :count2 from freqout where r=2;
select count into :count3 from freqout where r=3;
select count into :count4 from freqout where r=4;
select count into :count5 from freqout where r=5;
select count into :count6 from freqout where r=6;
select count into :count7 from freqout where r=7;
select count into :count8 from freqout where r=8;
select count into :count9 from freqout where r=9;
select count into :count10 from freqout where r=10;
quit;
%assert_sym_compare(&count1, 1, type=COMPARISON, operator=GE);
%assert_sym_compare(&count2, 1, type=COMPARISON, operator=GE);
%assert_sym_compare(&count3, 1, type=COMPARISON, operator=GE);
%assert_sym_compare(&count4, 1, type=COMPARISON, operator=GE);
%assert_sym_compare(&count5, 1, type=COMPARISON, operator=GE);
%assert_sym_compare(&count6, 1, type=COMPARISON, operator=GE);
%assert_sym_compare(&count7, 1, type=COMPARISON, operator=GE);
%assert_sym_compare(&count8, 1, type=COMPARISON, operator=GE);
%assert_sym_compare(&count9, 1, type=COMPARISON, operator=GE);
%assert_sym_compare(&count10, 1, type=COMPARISON, operator=GE);
This last bit of code is repetitive and would ideally be "macro-ized".
Friday, November 9, 2007
Fun with XMethods.com
It's fun to occasionally take a stroll through xmethods.com see what kinds of web services folks are creating.
Here are some of the ones I found interesting in my look today.
Convert Text to Braille
Here is a neat and easy to use service for converting text to Braille. The BrailleText conversion method takes two parameters, the text you want to convert (string) and the font size for the output (float). It returns a byte array that is easily converted into a JPG and displayed on a WinForm.
private void button1_Click(object sender, EventArgs e)
{
net.webservicex.www.Braille svc = new TextToBraille1.net.webservicex.www.Braille();
byte[] response = svc.BrailleText(this.textBox1.Text, 20);
using (FileStream fs = new FileStream("braille.jpg", FileMode.Create, FileAccess.Write))
{
foreach(byte b in response)
{
fs.WriteByte(b);
}
}
pictureBox1.Load("braille.jpg");
}
How to convert the image to actual Braille that the blind can read...hmmm...looks like there are many possibilities there.
Dates of U.S. Holidays
This web service offers methods for discovering the dates of U.S. holidays for the year of your choice. For example, the GetThanksgivingDay method requires that you pass it a year value (integer) and it returns a DateTime value. I wired this service up to a simple ASP.NET page containing a calendar control and show this year's and next year's holidays, disabling selection of those dates as well.
Imports System.Collections.Generic
Public Class Holiday
Public Sub New(ByVal d As DateTime, ByVal nm As String)
Me.Date = d
Me.Name = nm
End Sub
Public [Date] As DateTime
Public Name As String
End Class
Partial Class _Default
Inherits System.Web.UI.Page
Private holidays As New List(Of Holiday)
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim svc As New com.holidaywebservice.www.USHolidayDates()
For yr As Integer = DateTime.Now.Year To DateTime.Now.Year + 1
holidays.Add(New Holiday(svc.GetChristmasDay(yr), "Christmas"))
holidays.Add(New Holiday(svc.GetIndependenceDay(yr), "Independence Day"))
holidays.Add(New Holiday(svc.GetLaborDay(yr), "Labor Day"))
holidays.Add(New Holiday(svc.GetMartinLutherKingDay(yr), "MLK Day"))
holidays.Add(New Holiday(svc.GetMemorialDay(yr), "Memorial Day"))
holidays.Add(New Holiday(svc.GetNewYear(yr), "New Year's Day"))
holidays.Add(New Holiday(svc.GetPresidentsDay(yr), "President's Day"))
holidays.Add(New Holiday(svc.GetThanksgivingDay(yr), "Thanksgiving"))
Next
End Sub
Protected Sub Calendar1_DayRender(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DayRenderEventArgs) Handles Calendar1.DayRender
For Each h As Holiday In holidays
If e.Day.Date.Year = h.Date.Year AndAlso e.Day.Date.DayOfYear = h.Date.DayOfYear Then
e.Cell.Text = h.Name
e.Cell.Enabled = False
End If
Next
End Sub
End Class
While the intent of this web service is quite nice, sadly it provides incorrect results as you can see in the image above (Christmas on the 24th? New Year's Day on Dec 31st?). This just illustrates that there are dangers in using black box web services that live "out there in the wild."
Daily Dilbert Cartoon
And last, but not least, this delightful web service serves up the URL of the
Dilbert Cartoon of the day (or the bytes of the JPG). I used the GetDailyDilbertImagePath method (no arguments) to return the URL (string) of the cartoon JPG location and plug that into a picturebox on a WinForm. Very easy.
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim svc As New com.esynaps.www.DailyDilbert()
Dim url As String = svc.DailyDilbertImagePath()
Me.PictureBox1.Load(url)
Me.Text = url
End Sub
End Class
Thursday, November 8, 2007
Unit Testing an Email Method
I recently ran into the problem of needing to unit test a web method in an app that sends email. Searching the web, I finally found this which helped me develop my test. Many thanks to Simon Soanes for his post. Here’s the C# 2.0 test harness code.
using System;
using NUnit.Framework;
using MyApp.WebLayer;
using System.Runtime.InteropServices;
using Outlook = Microsoft.Office.Interop.Outlook; //Refs Microsoft Outlook 11.0 Object Library
namespace TestHarness
{
[TestFixture]
public class EmailServiceTestSuite
{
[Test]
public void SendRegularText()
{
EmailWebService.EmailService svc = new EmailWebService.EmailService();
DateTime now = DateTime.Now;
svc.SendRegularText("me@myorg.org", "SendRegularText Test Email - " + now, "");
System.Threading.Thread.Sleep(10000); //Wait 10 seconds for email to arrive...sometimes not enough
Outlook.ApplicationClass outlookApp = null;
Outlook.NameSpace outlookNS = null;
bool emailArrived = false;
try
{
outlookApp = new Outlook.ApplicationClass();
outlookNS = outlookApp.GetNamespace("MAPI");
outlookNS.Session.Logon("outlook", "", false, true);
Outlook.MAPIFolder inbox = outlookNS.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox);
foreach (Outlook.MailItem email in inbox.Items)
{
if (email.Subject == "SendRegularText Test Email - " + now)
{
emailArrived = true;
email.Delete();
}
}
}
finally
{
if (outlookNS != null)
outlookNS.Logoff();
}
Assert.IsTrue(emailArrived, "Email failed to arrive");
}
}
}
For testing purposes, I just send a test email to myself with a guaranteed unique subject line, wait for 10 seconds which is usually enough time for the email to show up in my Outlook Inbox, then assert that the email is present. The SendRegularText web method accepts three parameters: email recipient, subject line, and body text (left blank in the test).
Wednesday, November 7, 2007
VB.NET / C# Language Conversion Links
I find it's really helpful to have at least a basic understanding of VB.NET and C#.
C#
using System;
public class MyClass
{
static void Main()
{
Console.WriteLine("Hi, from C#!");
}
}
VB.NET
Imports System
Public Class MyClass
Shared Sub Main()
Console.WriteLine("Howdy, from VB.NET!")
End Sub
End Class
Here are links to some miscellaneous online resources.
Tuesday, November 6, 2007
Test Driving TallPDF.NET 3.0
I am a big fan of PDF documents. The only thing about them that is a drag is creating them. I usually create them from a source document of some kind using CutePDF Writer. As the website says, "FREE for personal and commercial use! No watermarks! No Popup Web Ads!" What could be better!? It installs as a printer driver, so you can create a PDF out of just about any kind of file.
For example, I can take this carefully drawn self-portrait in mspaint.exe and make a PDF using CutePDF Writer in no time flat.
Hit the File | Print... menu...
Select CutePDF Writer, press Print, et voila! It's a PDF...
What could be better than that? Well, I'll tell you...programmatically creating PDFs!
This is where TallPDF.NET 3.0 comes in.
After downloading this .NET component (I really just need the DLL, although the documentation that comes with it is nice, too), I just reference it in any old .NET application. I will use a C# console application.
Here's all I need in the way of code and references.
I actually don't need System.Data or System.Xml for this example, but System.Drawing, System.Web, and of course TallComponents.PDF.Layout are all required. As you can see from the using statements, TallPDF is organized into multiple namespaces. Here I only reference the three required by this example. The object model is pretty self-explanatory: we have a document object, with one or more section objects, each containing paragraph objects which may be text or shapes or images. At the end of the code, we're just pumping PDF bits through a file stream using the Document.Write method.
The PDF produced looks like this.
Here's a slightly more complex example.
using System;
using System.Drawing;
using System.IO;
using TallComponents.PDF.Layout;
using F = TallComponents.PDF.Layout.Fonts;
using TallComponents.PDF.Layout.Paragraphs;
using TallComponents.PDF.Layout.Navigation;
using TallComponents.PDF.Layout.Shapes;
using B = TallComponents.PDF.Layout.Brushes;
using P = TallComponents.PDF.Layout.Pens;
using FLD = TallComponents.PDF.Layout.Shapes.Fields;
namespace TestTallPdf2
{
class Program
{
static void Main(string[] args)
{
string lorem1 = "Lorem ipsum dolor sit amet, consectetuer adipiscing elit...";
string lorem2 = "Cras suscipit. Aliquam hendrerit. Vivamus aliquam. Vestibulum...";
Document doc = new Document();
Section sec = new Section();
doc.Sections.Add(sec);
Header hdr = new Header();
TextParagraph phdr = new TextParagraph();
hdr.Paragraphs.Add(phdr);
phdr.Fragments.Add(new Fragment("My Lorem Ipsum Document", F.Font.HelveticaBoldOblique, 12));
hdr.TopMargin = new Unit(0.5, UnitType.Inch);
sec.Header = hdr;
Heading hd1 = new Heading(0);
hd1.SpacingBefore = 10;
hd1.SpacingAfter = 5;
hd1.Fragments.Add(new Fragment("Introduction", F.Font.Helvetica, 11));
sec.Paragraphs.Add(hd1);
TextParagraph psec = new TextParagraph();
psec.Fragments.Add(new Fragment(lorem1, F.Font.TimesRoman, 10));
psec.SpacingAfter = 10;
psec.LineSpacing = 3;
sec.Paragraphs.Add(psec);
Heading hd2 = new Heading(0);
hd2.SpacingBefore = 10;
hd2.SpacingAfter = 5;
hd2.Fragments.Add(new Fragment("About the Author", F.Font.Helvetica, 11));
sec.Paragraphs.Add(hd2);
TextParagraph psec2 = new TextParagraph();
psec2.Justified = true;
psec2.Fragments.Add(new Fragment(lorem2, F.Font.TimesRoman, 10));
psec2.LineSpacing = 3;
sec.Paragraphs.Add(psec2);
Heading hd3 = new Heading(0);
hd3.SpacingBefore = 10;
hd3.SpacingAfter = 5;
hd3.Fragments.Add(new Fragment("1/4 of a Pie", F.Font.Helvetica, 11));
sec.Paragraphs.Add(hd3);
Drawing drawing = new Drawing(60, 60);
PieShape pie = new PieShape();
pie.Start = 0;
pie.Sweep = 90;
pie.Pen = new P.Pen(System.Drawing.Color.Red, 2);
pie.Brush = new B.SolidBrush(System.Drawing.Color.Blue);
drawing.Shapes.Add(pie);
sec.Paragraphs.Add(drawing);
Heading hd4 = new Heading(0);
hd4.SpacingBefore = 10;
hd4.SpacingAfter = 5;
hd4.Fragments.Add(new Fragment("Lorem Ipsum Bracelet", F.Font.Helvetica, 11));
sec.Paragraphs.Add(hd4);
Drawing drawing2 = new Drawing(180, 180);
ImageShape img = new ImageShape(@"C:\my_folder\bracelet.jpg");
drawing2.Shapes.Add(img);
sec.Paragraphs.Add(drawing2);
Heading hd5 = new Heading(0);
hd5.SpacingBefore = 10;
hd5.SpacingAfter = 5;
hd5.Fragments.Add(new Fragment("Enter your name", F.Font.Helvetica, 11));
sec.Paragraphs.Add(hd5);
Drawing drawing3 = new Drawing(200, 30);
FLD.TextFieldShape field = new FLD.TextFieldShape(200, 30);
field.FullName = "txtName";
drawing3.Shapes.Add(field);
sec.Paragraphs.Add(drawing3);
ViewerPreferences vp = new ViewerPreferences();
vp.ZoomFactor = 0.75; //75%
doc.ViewerPreferences = vp;
using (FileStream fs = new FileStream(@"C:\my_folder\lorem.pdf",
FileMode.Create, FileAccess.Write))
{
doc.Write(fs);
}
}
}
}
This produces a PDF looking like this.
Pretty nice, eh!
It works in ASP.NET, too.
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using TallComponents.PDF.Layout;
using TallComponents.PDF.Layout.Paragraphs;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Document doc = new Document();
Section sec = new Section();
doc.Sections.Add(sec);
TextParagraph par = new TextParagraph();
par.Fragments.Add(new Fragment("Hello from ASP.NET!"));
sec.Paragraphs.Add(par);
doc.Write(Response);
}
}
Saturday, November 3, 2007
SubSonic for Database Versioning
If you're a regular SubSonic user, then you are probably well aware of its database versioning feature. Even if you're into other CRUD glue like data adapters, LINQ, or NHibernate, I think you will find this feature
a reason to check out SubSonic. This is not about source control (there are plenty of other tools for that). This is about taking a snapshot of your database objects (including the data in the tables) at a point in time and being able to recreate an exact copy of that snapshot.
With SubSonic installed, open Visual Studio and go into the Tools | External Tools... menu and configure a new tool as shown. In this case, I'm calling the new tool SubSonic DB Versioner.
Click ok, then add an app.config/web.config to your project. I'll explain this using the app.config of a Winforms app.
To set up SubSonic for database versioning, here is the minimum you will need to specify in the config file. This is boilerplate code you can easily reuse by just changing the provider/connection string.
I recommend creating a new folder in your project called something like DatabaseSnapshots. You are now ready to make a database snapshot using the versioning tool. All you have to do is go back into the Tools menu and select SubSonic DB Versioner. A window will pop up asking you if you want to accept the default command line arguments (because we checked Prompt for arguments). For the purposes of this demonstration, we need to change the Arguments from "version /out App_Code\DB" to "version /out DatabaseSnapshots". Then let 'er rip, keeping an eye on the Output window to see the progress messages.
When it finishes, you will end up with two new .sql scripts in your DatabaseSnapshots folder (e.g., SqlDataProvider_Schema_2007_10_30.sql and SqlDataProvider_Data_2007_10_30.sql). The first script, the one with Schema in the name, contains code to recreate all tables, views, stored procedures, etc.
Here's just a tiny portion of the schema script.
/****** Object: Table [dbo].[CustomerDemographics] Script Date: 10/30/2007 13:52:13 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CustomerDemographics]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[CustomerDemographics](
[CustomerTypeID] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CustomerDesc] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY NONCLUSTERED
(
[CustomerTypeID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
/****** Object: Table [dbo].[Region] Script Date: 10/30/2007 13:52:14 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Region]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Region](
[RegionID] [int] NOT NULL,
[RegionDescription] [nchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Region] PRIMARY KEY NONCLUSTERED
(
[RegionID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
/****** Object: Table [dbo].[Employees] Script Date: 10/30/2007 13:52:14 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Employees]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Title] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TitleOfCourtesy] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BirthDate] [datetime] NULL,
[HireDate] [datetime] NULL,
[Address] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Region] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostalCode] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Country] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HomePhone] [nvarchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Extension] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Photo] [image] NULL,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReportsTo] [int] NULL,
[PhotoPath] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
That's pretty neat, but the second script is the coolest. It contains code to populate the database tables with your data. Here's a snippet of the data script.
ALTER TABLE [Region] NOCHECK CONSTRAINT ALL
GO
PRINT 'Begin inserting data in Region'
INSERT INTO [Region] ([RegionID], [RegionDescription])
VALUES(1, 'Eastern ')
INSERT INTO [Region] ([RegionID], [RegionDescription])
VALUES(2, 'Western ')
INSERT INTO [Region] ([RegionID], [RegionDescription])
VALUES(3, 'Northern ')
INSERT INTO [Region] ([RegionID], [RegionDescription])
VALUES(4, 'Southern ')
ALTER TABLE [Region] CHECK CONSTRAINT ALL
GO
ALTER TABLE [Shippers] NOCHECK CONSTRAINT ALL
GO
SET IDENTITY_INSERT [Shippers] ON
PRINT 'Begin inserting data in Shippers'
INSERT INTO [Shippers] ([ShipperID], [CompanyName], [Phone])
VALUES(1, 'Speedy Express', '(503) 555-9831')
INSERT INTO [Shippers] ([ShipperID], [CompanyName], [Phone])
VALUES(2, 'United Package', '(503) 555-3199')
INSERT INTO [Shippers] ([ShipperID], [CompanyName], [Phone])
VALUES(3, 'Federal Shipping', '(503) 555-9931')
SET IDENTITY_INSERT [Shippers] OFF
ALTER TABLE [Shippers] CHECK CONSTRAINT ALL
GO
ALTER TABLE [Suppliers] NOCHECK CONSTRAINT ALL
GO
SET IDENTITY_INSERT [Suppliers] ON
PRINT 'Begin inserting data in Suppliers'
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(1, 'Exotic Liquids', 'Charlotte Cooper', 'Purchasing Manager', '49 Gilbert St.', 'London', NULL, 'EC1 4SD', 'UK', '(171) 555-2222', NULL, NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(2, 'New Orleans Cajun Delights', 'Shelley Burke', 'Order Administrator', 'P.O. Box 78934', 'New Orleans', 'LA', '70117', 'USA', '(100) 555-4822', NULL, '#CAJUN.HTM#')
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(3, 'Grandma Kelly''s Homestead', 'Regina Murphy', 'Sales Representative', '707 Oxford Rd.', 'Ann Arbor', 'MI', '48104', 'USA', '(313) 555-5735', '(313) 555-3349', NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(4, 'Tokyo Traders', 'Yoshi Nagase', 'Marketing Manager', '9-8 Sekimai Musashino-shi', 'Tokyo', NULL, '100', 'Japan', '(03) 3555-5011', NULL, NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(5, 'Cooperativa de Quesos ''Las Cabras''', 'Antonio del Valle Saavedra', 'Export Administrator', 'Calle del Rosal 4', 'Oviedo', 'Asturias', '33007', 'Spain', '(98) 598 76 54', NULL, NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(6, 'Mayumi''s', 'Mayumi Ohno', 'Marketing Representative', '92 Setsuko Chuo-ku', 'Osaka', NULL, '545', 'Japan', '(06) 431-7877', NULL, 'Mayumi''s (on the World Wide Web)#http://www.microsoft.com/accessdev/sampleapps/mayumi.htm#')
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(7, 'Pavlova, Ltd.', 'Ian Devling', 'Marketing Manager', '74 Rose St. Moonie Ponds', 'Melbourne', 'Victoria', '3058', 'Australia', '(03) 444-2343', '(03) 444-6588', NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(8, 'Specialty Biscuits, Ltd.', 'Peter Wilson', 'Sales Representative', '29 King''s Way', 'Manchester', NULL, 'M14 GSD', 'UK', '(161) 555-4448', NULL, NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(9, 'PB Knäckebröd AB', 'Lars Peterson', 'Sales Agent', 'Kaloadagatan 13', 'Göteborg', NULL, 'S-345 67', 'Sweden', '031-987 65 43', '031-987 65 91', NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(10, 'Refrescos Americanas LTDA', 'Carlos Diaz', 'Marketing Manager', 'Av. das Americanas 12.890', 'Sao Paulo', NULL, '5442', 'Brazil', '(11) 555 4640', NULL, NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(11, 'Heli Süßwaren GmbH & Co. KG', 'Petra Winkler', 'Sales Manager', 'Tiergartenstraße 5', 'Berlin', NULL, '10785', 'Germany', '(010) 9984510', NULL, NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(12, 'Plutzer Lebensmittelgroßmärkte AG', 'Martin Bein', 'International Marketing Mgr.', 'Bogenallee 51', 'Frankfurt', NULL, '60439', 'Germany', '(069) 992755', NULL, 'Plutzer (on the World Wide Web)#http://www.microsoft.com/accessdev/sampleapps/plutzer.htm#')
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(13, 'Nord-Ost-Fisch Handelsgesellschaft mbH', 'Sven Petersen', 'Coordinator Foreign Markets', 'Frahmredder 112a', 'Cuxhaven', NULL, '27478', 'Germany', '(04721) 8713', '(04721) 8714', NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(14, 'Formaggi Fortini s.r.l.', 'Elio Rossi', 'Sales Representative', 'Viale Dante, 75', 'Ravenna', NULL, '48100', 'Italy', '(0544) 60323', '(0544) 60603', '#FORMAGGI.HTM#')
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(15, 'Norske Meierier', 'Beate Vileid', 'Marketing Manager', 'Hatlevegen 5', 'Sandvika', NULL, '1320', 'Norway', '(0)2-953010', NULL, NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(16, 'Bigfoot Breweries', 'Cheryl Saylor', 'Regional Account Rep.', '3400 - 8th Avenue Suite 210', 'Bend', 'OR', '97101', 'USA', '(503) 555-9931', NULL, NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(17, 'Svensk Sjöföda AB', 'Michael Björn', 'Sales Representative', 'Brovallavägen 231', 'Stockholm', NULL, 'S-123 45', 'Sweden', '08-123 45 67', NULL, NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(18, 'Aux joyeux ecclésiastiques', 'Guylène Nodier', 'Sales Manager', '203, Rue des Francs-Bourgeois', 'Paris', NULL, '75004', 'France', '(1) 03.83.00.68', '(1) 03.83.00.62', NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(19, 'New England Seafood Cannery', 'Robb Merchant', 'Wholesale Account Agent', 'Order Processing Dept. 2100 Paul Revere Blvd.', 'Boston', 'MA', '02134', 'USA', '(617) 555-3267', '(617) 555-3389', NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(20, 'Leka Trading', 'Chandra Leka', 'Owner', '471 Serangoon Loop, Suite #402', 'Singapore', NULL, '0512', 'Singapore', '555-8787', NULL, NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(21, 'Lyngbysild', 'Niels Petersen', 'Sales Manager', 'Lyngbysild Fiskebakken 10', 'Lyngby', NULL, '2800', 'Denmark', '43844108', '43844115', NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(22, 'Zaanse Snoepfabriek', 'Dirk Luchte', 'Accounting Manager', 'Verkoop Rijnweg 22', 'Zaandam', NULL, '9999 ZZ', 'Netherlands', '(12345) 1212', '(12345) 1210', NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(23, 'Karkki Oy', 'Anne Heikkonen', 'Product Manager', 'Valtakatu 12', 'Lappeenranta', NULL, '53120', 'Finland', '(953) 10956', NULL, NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(24, 'G''day, Mate', 'Wendy Mackenzie', 'Sales Representative', '170 Prince Edward Parade Hunter''s Hill', 'Sydney', 'NSW', '2042', 'Australia', '(02) 555-5914', '(02) 555-4873', 'G''day Mate (on the World Wide Web)#http://www.microsoft.com/accessdev/sampleapps/gdaymate.htm#')
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(25, 'Ma Maison', 'Jean-Guy Lauzon', 'Marketing Manager', '2960 Rue St. Laurent', 'Montréal', 'Québec', 'H1J 1C3', 'Canada', '(514) 555-9022', NULL, NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(26, 'Pasta Buttini s.r.l.', 'Giovanni Giudici', 'Order Administrator', 'Via dei Gelsomini, 153', 'Salerno', NULL, '84100', 'Italy', '(089) 6547665', '(089) 6547667', NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(27, 'Escargots Nouveaux', 'Marie Delamare', 'Sales Manager', '22, rue H. Voiron', 'Montceau', NULL, '71300', 'France', '85.57.00.07', NULL, NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(28, 'Gai pâturage', 'Eliane Noz', 'Sales Representative', 'Bat. B 3, rue des Alpes', 'Annecy', NULL, '74000', 'France', '38.76.98.06', '38.76.98.58', NULL)
INSERT INTO [Suppliers] ([SupplierID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [HomePage])
VALUES(29, 'Forêts d''érables', 'Chantal Goulet', 'Accounting Manager', '148 rue Chasseur', 'Ste-Hyacinthe', 'Québec', 'J2S 7S8', 'Canada', '(514) 555-2955', '(514) 555-2921', NULL)
SET IDENTITY_INSERT [Suppliers] OFF
ALTER TABLE [Suppliers] CHECK CONSTRAINT ALL
GO
I was so excited the first time I saw this, I nearly choked on my Diet Coke.
There's a nice introduction to SubSonic here. The latest news (here and here) is that Rob Conery (a.k.a. Mr. SubSonic) has joined Microsoft and MS will be paying him to continue developing SS. Rock on.
Subscribe to:
Posts (Atom)