1022 builds later, today, we (Red Gate) released SQL Data Generator which is available to download as a 14 day trail. SQL Data Generator intelligently creates meaningful test data by automatically taking the table and column names in to account, along with field length and any existing constraints. We feel it's a great tool and is really useful during test and development, it's just a great tool to have in order to quickly fill your database. From a DBA's point of view, the tool is great for performance and scalability when huge amounts (millions of rows) is required to test against.  To prove this, see below for how I used the application to generate data for the Northwind database.

We are really looking forward to hearing what you think in order to make the best possible product.  If you have any comments or questions, please let us know via the forums.

On a side note, we have created a CodePlex site to host all any generators created by the community.  We already have a few online for you to download and use so I strongly recommend you take a look.  My favourite generator is the C# Code Generator created by Lionel, allowing you to write and compile C# code directly within the UI to use as the column generator.

Generating data for Northwind

Northwind is one of the sample Microsoft databases originally shipped with SQL Server 2000 and is used in a very large number of articles, as such it seemed logical to use it for this post. Below demonstrates how you can very quickly generate data for the Northwind database.

After loading the application, you will be presented with a Project Configuration dialog.  This allows you to specify which server and database you want to produce data for.  At this point you can configure project options, such as pre\post scripts to execute and if triggers should be fired when generating data.  I'm just going to connect to my local server and the Northwind database.

At this point, the application will load the schema and automatically attempt to match our built-in generators to columns within your table. The screenshot below has three main areas, the list on the left specifies which tables will be populated. The top middle panel provides all of the generation settings for the table or column (based on what is selected).  In this example, I have the Categories table selected and I wish to generate 1,000 rows.  In the lower middle panel, you have a preview table of how the data might look.  The CategoryName column has been populated with some possible categories, while Description has some latin text with the Picture column being populated with a valid image. This is straight out of the box, I haven't done anything yet apart from connect to the database.

Selecting a column in the preview table displays the column generation settings.  Each generator has its own set of properties allowing you to adapt the data to your own requirements, you can also select different generators by using the dropdown at the top. I'm happy with the pre-defined data so I will leave it as it is.

Clicking the Generate Data... button on the toolbar will display the Data Population Summary, this just simply says what is going to be done against the database. In this case, DELETE FROM and TRUNCATE TABLE commands will be executed with 1000 rows being inserted for each table. 

Clicking Generate Data will populate the table. After which a report will be displayed detailing what happened and if any errors occurred. The data looks to have been inserted successfully, It's amazing how quickly data can actually be generated using this application. 

That's it - very direct and simple. We can select data from the table using SQL Management Studio.  This data is all the default settings, I haven't modified anything.

If your happy with the data produced then you can save generation settings as a project file to use later, or you can tweak the settings to tailor the data generated for your own database.

However, the application has really loads of cool features which I haven't even touched on here, hopefully I will be able to cover them in later posts and articles.  Until then, why don't you download the free 14 day trail from the website and see what features you really like. The team and I would love to hear your feedback!

More information at http://www.red-gate.com/products/SQL_Data_Generator/index.htm

Product forums at http://www.red-gate.com/sqldatagenerator/forum/v1

CodePlex website at http://www.codeplex.com/SDGGenerators

Labels: Red Gate, SQL Server

If you ever looked at the dynamic management views or one of the system catalogs in SQL Server a column called Object ID will be associated, from the looks of it this number doesn't actually relate to anything - but there is actually a lot more to it than that. Object_ID is a unique id number for an object within the database, this is used internally by SQL Server.  It should be noted, not all objects have an object_id.  DDL triggers for example do not as they are not schema-scoped.

If we run a query against sys.columns, lots of information will be returned about all the columns in the database.

SELECT * FROM sys.columns

However, the result set will look something like this:

object_id | name | column_id ....
4    rowsetid    1
4    rowsetcolid    2
...
151671588    MiddleName    4

While the column name is in readable text, the object_id isn't actually anything useful (to a human).  In fact, this is the ID of the table which the column belongs to. In order to get the name of the object from the object_id there is a function called Object_Name(). This will take the object_id and return a readable name. If we re-run the query but including the object_name

SELECT OBJECT_NAME(object_id) as TableName, * FROM sys.columns

Then the table name is returned in a helpful manner.

TableName | object_id | name | column_id ....
sysrowsetcolumns    4    rowsetid    1
sysrowsetcolumns    4    rowsetcolid    2
vEmployee    151671588    MiddleName    4

We could also use the Object_ID() function to obtain the object_id for an object in database.  For example, the query below asks for the object_id for the Customers table in Northwind.  This returns 21575115 

USE Northwind
GO
SELECT OBJECT_ID('Customers')

Of course, flipping this around will return us Customers again.

There is also a similar function when you are given a schema_id. Schema_Name() works in the same fashion but returns the schema name based on the Schema_Id.

SELECT OBJECT_NAME(object_id), SCHEMA_NAME(schema_id), * FROM sys.tables

Hopefully this will clear up what Object_ID is. I know next time I query one of SQL Server tables I will be able to return at least some more meaningful information.

Labels: SQL Server

In my previous post, I discussed Visual Studio Integration, IronPython and how difficult it was to get started. However, I came across an application called IronPython Studio (Colin also pointed it out on the comments) which uses the Visual Studio 2008 Shell to provide a IronPython IDE. You can download the application from http://www.codeplex.com/IronPythonStudio.

On my first attempt to install the application, I was greeted with this error message:

---------------------------
Microsoft Visual Studio Shell Isolated
---------------------------
Cannot find one or more components. Please reinstall the application.
---------------------------
OK  
---------------------------

Turns out you need to have Microsoft Visual Studio 2008 Shell (isolated mode) Redistributable Package (http://www.microsoft.com/downloads/details.aspx?FamilyId=ACA38719-F449-4937-9BAC-45A9F8A73822&displaylang=en) installed which the installer doesn't check for, this is a 384mb download.

Installing this, I found that it doesn't actually install it - if I had actually read it correctly I would have read that it was just extracting.  I then had to go into the directory where I had extracted it to (C:\VS 2008 Shell Redist\Isolated Mode) and run the real installer (vs_shell_isolated.enu.exe).  I then uninstalled and reinstalled IP Studio without any problems.

Upon launching Iron Python Studio, I was greeted with a very familiar feel to Visual Studio. Going File > New > Project gave me the same options as was available in the VS 2008 SDK integration.

After creating a new Windows Application, it looks and feels the same as VS so you won't have any trouble navigating around.  There is some intellisense (it didn't like System.Windows...) and the syntax highlighting seems to work.

You can build and debug just as you would for Visual Studio 2008 and C#.  It is a good application to have around, however for now I think I will stick with ipy and notepad2. The fact you need the VS2008 Shell installed at 385mb is another fact which turns me against this.  It's a shame they didn't just release it as a VS Package which could plug straight into VS2008.

Labels: DLR, IronPython

It has been a while since the first EAP build of ReSharper 4 was released.  There was some discuss on the forums that build 755 was good so I thought I would look at they have been doing.  This isn't the latest, but just potentially stable release.

The first thing to notice is that they have a new installer.  This installer covers both VS 2005 and VS 2008 integration, previously they had two separate installers. There are two options, Install and Advanced.  Install just uses the default configuration, where as Advanced you can configure different options.  By default, it will integrate into both 2005 and 2008.

Screen once it is installing:  Bug - Says ReSharper 3.0 Setup.

Finished.

Everything looked to be working just fine. 

The first thing I noticed is that they have removed the little window in the corner which tells you what is going on. This information is now in Visual Studio's status bar, this cause less distraction.

The first item I heard to work correctly was Linq.  A quick query provides that ReSharper doesn't provide errors about the Linq syntax like in previous versions. The intellisense window also highlights the keywords such as Select and OrderBy.

One problem I found with this is that if you remove unused references, it will remove System.Linq but generally it is looking good for Linq.

Sadly, it still doesn't support Partial Methods

JetBrains have also introduced a Errors in Solution window.  This is great, apart from if you have auto-generated code. While you can configure it to folder a file\folder, it would be much better if this supported wildcard expressions - then I could say ignore *.dbml and *.designer.* etc.

It looks like 4.0 is moving along and they are working hard to solve problems. Build 755 is looking good.

Labels: ReSharper

While reading my SQL Server 2005 MCTS book, I came across sp_helpdb.  This is a database engine stored procedure which can provide you with information about the databases in your system. You can execute the stored procedure within management studio using:

sp_helpdb

This will return the following results set.

As you can see, it has given information about each database - including db_size of the database.  The status containing more information about the database, the status for AdventureWorks is:

Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Latin1_General_CS_AS, SQLSortOrder=0, IsAnsiNullsEnabled, IsAnsiPaddingEnabled, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsNullConcat, IsQuotedIdentifiersEnabled

We cam also execute it for just a single database.

sp_helpdb @dbname = 'AdventureWorks'

This will return the same information as above for the table, but we also get information about the actual filegroups for the database, for AdventureWorks we just have the primary filegroup for the data and the transaction log.  This can also be obtained by executing sp_helpfile.

This can also be obtained by using the Report button on the Summary tab for a database within SQL Management Studio.  The report we are interested in is the Disk Usage report.

This will display the information in a nice table view, however takes a lot longer to execute and process than the raw queries.

The above report also gives information about Disk space used by tables (along with a few other items).  This can be obtained by executing sp_spaceused which will return the information on a database as a whole. If you are interested in a particular table, then you can set it as a parameter.

EXEC sp_spaceused @objname = 'HumanResources.Employee'.

Labels: SQL Server

For the past week, I have been thinking about aims and target technology which I really want to learn more about, or dig even deeper into in some cases. One of those technologies is the DLR and the related dynamic languages. This post will just cover the very basics of getting started with one of the DLR languages - IronPython.  IronPython is about bring the Python language onto the DLR for use with the .Net framework.

1) Download

The first step to getting started is to actual download IronPython.  The project is hosted on CodePlex with all of the source code available, at the moment there are two main releases for IronPython, the first is 1.1.1 which is the latest 1x version and is built on top of the CLI.  The second is the latest 2.0 Beta 1 and is built on top of the DLR and is targeting Python v2.5

Download links:

1.1.1 - http://www.codeplex.com/IronPython/Release/ProjectReleases.aspx?ReleaseId=5141

2.0 Beta 1 - http://www.codeplex.com/IronPython/Release/ProjectReleases.aspx?ReleaseId=10266

For this series of posts, I will be focusing on 2.0 Beta 1.

2) Command Line

After you have downloaded the binaries as a zip file, extract it into its own directory.  You now have IronPython installed and setup - easy.  Within your extracted folder, you have everything you need to start using IronPython.

The main application is ipy.exe, this is the interactive command line for IronPython.  This allows you to execute IronPython code, as Python is a dynamic language there is no need to build the code as it can be executed on the fly.  This is great for experimenting with the language and trying out samples.

The other exe is ipyw.exe.  This is the same as ipy.exe, however runs windowless which allows you to execute a IronPython script without having to display the console.  For example, if we save the following code in a file called Hello.py in the same directory.

import clr
clr.AddReference("System.Windows.Forms")
import System.Windows.Forms as WinForms
WinForms.MessageBox.Show("Hello", "Hello World")

>ipyw.exe Hello.py

The only thing displayed to the end user is the message box.

Other important files are the assembles, IronPython.dll, IronPython.Modules.dll and Microsoft.Scripting.dll.  From what I can tell, Microsoft.Scripting.dll is the core DLR code and it will be referred by the languages (IronRuby etc) and also hosters (Silverlight). IronPython.dll is the main assembly containing the language with Modules containing additional functionality and classes.  In Python, modules are useful utilities and 'classes' - for example PythonRegex.

3) Visual Studio Integration

At the moment, Visual Studio integration can be done via the SDK (http://www.microsoft.com/downloads/details.aspx?familyid=30402623-93ca-479a-867c-04dc45164f5b&displaylang=en), it looks like this is targeting IronPython 1.1.1. After installing the SDK, the IronPython integration is just a series of samples. After building the solution (IronPython.sln), you will have multiple assemblies but nothing actually which can be executed.  Well, in order to use these assemblies, you need to load them using the 'Experimental Hive', I always think this sounds like something from Resident Evil but this in fact is Visual Studio with a separate registry so you can destroy it without taking down your machine instance. The easiest way to do this is press F5, yet, while using the integration, I found it throw far too many exceptions and just be really difficult to use. Using Ctrl-F5 (Run without debugging).  This stopped Visual Studio kicking in saying something has gone wrong on every mouse click.

What the integration gives you is a Visual Studio project types for WinForms, ASP.net, Console Applications, WPF and Class library.  It also provides colorisation and intellisense (especial for the modem day developer) and a tool window which is similar to ipy.

I really hope this is involved in the near future, I've heard that the IronRuby team are working on integration so I guess its only time before this is officially released.

4) Executing stand-alone applications

When it comes to actually using IronPython as the basis for your own application you have a couple of routes and your requirements.  IronPython currently supports Silverlight and ASP.net (see point 5).  When it comes to WinForms\standalone applications I can find three solutions which might work.  One is executing ipyw as shown in point 2.  Another solution is to host IronPython in a C# application, which I will discuss in a later post. The final solution is to use the Visual Studio Integration (point 3),  this includes the Visual Studio template which will compile the code into a single exe as static assemblies.

At present, not sure which is the 'best\recommend' solution, had a quick chat with Michael Foord (who mentioned Static Assemblies) and it appears as if compiling your code into an assembly instead of loose py files is possible in 1.0 and 1.1 but not currently available in 2.0.

5) Silverlight and ASP.net

There has been a lot of talk about Silverlight and the DLR recently so I won't go into details in this post.  It appears as if you just have your codebehind file as .py and Silverlight will do the rest for you (more or less).  Silverlight Quickstarts outlines how to do this - http://www.silverlight.net/Quickstarts/ProgramDlr.aspx

An area which hasn't got as much attention is ASP.net futures (http://www.microsoft.com/downloads/details.aspx?FamilyId=A5189BCB-EF81-4C12-9733-E294D13A58E6&displaylang=en).  First, you need to create a new website (File > New > Website), you can then select IronPython from the language dropdown. You can then use IronPython as the code-behind language for your ASP.net web application.  While I didn't have an in depth look, I tried to add a Click event to a button, but found I had to do everything manually - but it works.

6) Books and Resources

Michael Foord's book IronPython in Action can be purchased as a early access ebook - http://www.manning.com/foord

Michael Foord's Blog - http://www.voidspace.org.uk/python/weblog/index.shtml

Codeplex site - IronPython - Home and lots more resources listed here http://www.codeplex.com/IronPython/Wiki/View.aspx?title=News%20Feeds&referringTitle=Home

If you know any other really good resources for IronPython and the DLR, then please leave them in the comments.

I hope you have found this post useful to get you up and running using IronPython.

Labels: DLR, IronPython

For DDDIreland, all of the speakers where asked to do a teaser about their session. This is the session I will be running at DeveloperDayScotland and NxtGenUG Coventry.

The teaser is just a short 4 min demo of writing your first test and what else to expect from my session.

Session abstract:

"Starting to unit test your first project is difficult, where to start? What to test? How do you even get started? In this session, Ben starts from scratch and implements an ASP.net 2.0 application using test driven development techniques. The application will have to deal with real world situations such as databases, web services and even some users! Ben will demonstrate how to design the application for testability and how unit testing and mock frameworks can make your life easier and your tests less fragile. At the end, will the tests go green?"

If you have any questions about the session, then please contact me.

View it online - SPEAKING @ DDDIreland.com - BEN HALL - May 3rd 2008 Galway

Download from http://blog.benhall.me.uk/downloads/Screencasts/DDDIreland/DDDIrelandTeaser.wmv

As a side note, the registration for DDDIreland is now open! You can register here

Labels: DDD, DDDIreland, DDS

  Just to let everyone know, I will be speaking at DDD Scotland (May 10th). I will be doing my Red, Green, Refactor session!

Session Abstract:

Red, Green, Refactor!
Speaker: Ben Hall
Starting to unit test your first project is difficult, where to start? What to test? How do you even get started? In this session, Ben starts from scratch and implements an ASP.net 2.0 application using test driven development techniques. The application will have to deal with real world situations such as databases, web services and even some users! Ben will demonstrate how to design the application for testability and how unit testing and mock frameworks can make your life easier and your tests less fragile. At the end, will the tests go green?

For the agenda visit http://developerdayscotland.com/main/Agenda/tabid/68/Default.aspx

You can register at the registration page

I look forward to seeing everyone there.

Labels: DDD, DDS

An article I have wrote about Extending NUnit has just been published on the Simple Talk website. In the article I discuss how to extend the framework to support your own custom attributes, the article includes sample code about how I built attributes such as SqlServerDataSource and ExecuteSql for use when writing your test code.

You can read the article here : Testing Times Ahead- Extending NUnit

-------------------------------------------------

Simple-Talk is an online technical journal and community hub for working SQL Server and .NET developers and administrators, as well as enthusiasts and those taking their first steps with these Microsoft technologies. Simple-Talk is a growing site, currently with 150,000 subscribers, who each receive a twice-monthly newsletter providing highlights from the Simple-Talk website along with updates and announcements concerning Red Gate tools

Labels: NUnit, Red Gate, TDD, Testing

I've been wanting to write about this for a while however haven't really had the chance.  While prepping for a NxtGenUG session I came across the TestSuite attribute.  By using this, you can build up your tests to execute dynamically and provide the parameters when the tests are loaded into the runner and framework.  This allows for some great flexibility and extendibility, especially if you don't know all of the possible test values at design-time and want values to be easily added (adding a row to the database, or dropping another file into a directory).

To take advantage of this, you need to use the TestSuiteFixtureAttribute at the top of your class.

[TestSuiteFixture]
public class TestSuiteTests

You can then create the test methods you want to be executed in order to test your system.  Two important points, they must take a parameter of type Object and return an Object - the rest is up to you. In the first test, I just write out the ToString value to the console,  in the second I check to see if the value is between 0 and 5.

public Object MethodUnderTest(object testData)
{
    Console.WriteLine("MethodUnderTest Executed");
    StringAssert.Contains(testData.ToString(), "Parameter");
    return null;
}

public Object AnotherMethodUnderTest(object testData)
{
    Console.WriteLine("AnotherTest");
    string testDataString = testData.ToString();

    int testExecution = Convert.ToInt32(testDataString[testDataString.Length -1].ToString());
    Assert.Between(testExecution, 0, 5);

    return null;
}

The final stage is to create the method which will programmatically create all of your tests and test parameters.

The method below is marked with the TestSuiteAttribute so the framework knows this can create tests.  Inside the method, we create a new TestSuite object with a name to identify it, we can then add new items into the collection which are then returned to the framework and executed.  The parameters to add are:

1. Name of test
2. Method to call (the test itself) as a TestDelegate object
3. Any parameters for the test

[TestSuite]
public ITestSuite Suite()
{
    TestSuite suite = new TestSuite("NewSimpleSuite");

    for (int i = 0; i < 10; i++)
    {
        suite.Add("MethodUnderTest" + i, new TestDelegate(this.MethodUnderTest), "Parameter" + i);
        suite.Add("AnotherMethodUnderTest" + i, new TestDelegate(this.AnotherMethodUnderTest), "Parameter" + i);
    }

    return suite;
}

The above code creates 20 tests, 10 for each test method. In the UI, the tests are all loaded correctly, as you can see each one is named correctly based on our code.

Very powerful attribute! But use the power wisely...

Download code sample here - http://blog.benhall.me.uk/Code/MbUnit/TestSuiteTests.cs.txt

Labels: MbUnit, TDD, Testing

Within SQL Server, there is an application called Bulk Copy Program (BCP) which is generally used for importing data very quickly into SQL Server.  However, the application also has the functionality to export data from a table or query into an external file.

Below is the command to export all of the data from Northwind.dbo.Customers into a NorthwindCustomers.csv file.  I then use the -t, to say the column separator should be a comma.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe" Northwind.dbo.Customers out "C:\NorthwindCustomers.csv" -c -T -t,

If we wanted some more control, we could write a SQL query and use the queryout option to export the results returned.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe" "SELECT * FROM Northwind.dbo.Customers" queryout "C:\NorthwindCustomers.csv" -c -T -t,

Combine this with xp_cmdshell, and all of it can be done from a stored procedure. Great way for getting data very quickly out of a table and into a flat file.

Labels: SQL Server

Having lots of fun with SQL Express tonight, attempting to load another sample I hit another error:

---------------------------
Microsoft Visual Studio
---------------------------
Failed to generate a user instance of SQL Server due to a failure in copying database files. The connection will be closed.
---------------------------
OK  
---------------------------

In the connection string, I changed it from creating a user instance each time to using a single instance and it appeared to solve my problem.

This is done by modifying the connection string to be "User Instance=False;" instead of "User Instance=True;"

Labels: SQL Server

While I am a fan of Vista and UAC, I have to admit there are times when other applications really cause it to be annoying, tonight it was SQL Express causing me pain.  I wanted to try one of Microsoft sample's, so I downloaded the zip, extracted it, loaded Visual Studio 2008 and hit F5.  At this point, the debugger stepped in and told me there was an error opening the database connection. 

"CREATE DATABASE permission denied in database 'master'.\r\nAn attempt to attach an auto-named database for file <<Path>>\App_Data\\MyDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

To create a database on SQL you need local admin permissions, as the application was automatically attaching the database I had to run Visual Studio as Administrator before I could execute it.  Just find it a bit annoying that you need access to Master to attach a SQL Express database as the whole point is for it to be zero friction.

Labels: SQL Server, Vista

Yesterday I attended the SQLBits conference.  This is a community conference based around SQL Server with SQL developers and DBA's attending the conference to discuss anything SQL Server, unlike other conferences, this was based at Aston University in Birmingham.

It was a great day, met with some really interesting people and attended some great sessions. It has really drawn my attention to certain areas which I need to look into.

However, it could have went horrible wrong. A lot of the sessions where based on SQL Server 2008, however with CTP6 there is a bug which causes it to stop working on February 29th (Friday). Everything is fine every other day, just didn't like leap years.  Conference could have been a whole different story if it was the day before. More here.

Labels: SQLBits