Embedding IronRuby and the DLR into Red Gate’s SQL Data Generator

Over the past few months there has been a lot of discussions online about how embedding the DLR (Dynamic Language Runtime) into your application opens up some amazing use cases. For example, Phil Haack recently wrote about how ASP.net MVC views can be stored in the database thanks for the DLR.  Resolver Systems have been doing with with great success.

Tonight, my latest article for Simple Talk was published. The article explains how I went about creating a new generator for SQL Data Generator which allows the user to define the data to be generated based on IronRuby code.  I think this is an amazing use case, both for SQL Data Generator and the DLRIronRuby. By taking advantage of the DLR, we have opened up some extremely useful use-cases within the application. To find out more take a look at the article on Simple Talk.

An example of the generator in action is shown in the screenshot.

IronRuby_SDG 

The article includes the source code and binaries for the SQL Data Generator example generator which you can start using within your project.

NOTE: This Plug-in generator is provided ‘as-is’ and is completely unsupported.

SQL Doc 2.0 – Customizing the Word cover page

In my previous posts I discussed how you could create printable documentation from SQL Doc. In this post, I want to discuss how you can customize the front cover of the documentation. By default, the output of the Word cover will look like this.

image

Within the SQL Doc installation directory (C:Program FilesRed GateSQL Doc 2), there is a style directory.  This style directory contains different artefacts used when generating the documentation, within the Word folder there is a file called DefaultCoverPage.doc. This is just a standard Word document, with placeholder fields used to insert the appropriate information for the document during generation. You can now customize the cover as you see fit such as including your own information, logo etc. For example, below I’ve moved the Red Gate logo, added an additional title, changed the size and colour of the title while moving the created date time to the footer of the page.

image

The next time a Word document is generated on that machine the new cover page will be used.

image

A ‘word’ of warning for users on Vista, you will need to run Word as Administrator, or save the customized cover page to a different path and copy the file back as Program Files is a restricted location which requires elevated permissions to write too.

I would also recommend taking a copy of the original cover in case you want to revert back. If you do accidentally change the cover page without taking a backup, delete the file DefaultCoverPage.doc and repair the installation via the control panel. This will restore the application to the original install files.

image

Technorati Tags: ,

SQL Doc 2.0 – Output as PDF or XPS

In my previous post I explained how you can use SQL Doc 2.0 to document ASPNetDB and produce a Microsoft Word document. One huge advantage of having it in Microsoft Word is that it can be exported to a number of different formats very easily – for example PDF or XPS.

To do this from Word 2007, the first step is to download the Word 2007 Save as PDF XPS addin from here – http://www.microsoft.com/downloads/details.aspx?FamilyID=4d951911-3e7e-4ae6-b059-a2e79ed87041&displaylang=en.  After installing this you will have a new option within your Save As menu.

image

After producing your database documentation as a Document, simple select this option to save the document.  It will ask you where you wish to save the document and the format it should be saved as. You can select between PDF, or Microsoft’s XPS Document – both work exactly perfectly, it’s just personal choice.

image

Once you have selected your location, the document will open. This is how the PDF document looks, which you can download from here

image

This is how the XPS document looks within IE8, which you can download here.

image

The document looks exactly the same as the original word output, however it can now be shared in different formats.

However, if you don’t have Office 2007, all is not lost. There is a free download available called CutePDF Writer where you can print any document or file as a PDF – very useful to have installed.

image

By selecting this as your printer, it will create you a PDF document which you can then use as you wish. For those of you with .Net 3.0 framework installed, there is an XPS Document Writer installed, which is very similar to CutePDF.  This will create you XPS documents in exactly the same way.

image

A 14 day free trail of SQL Doc can be downloaded from http://www.red-gate.com/Products/SQL_Doc

Technorati Tags: , , ,

Red Gate SQL Doc 2.0 – Producing printable database documentation

image

Last week I successfully released my first project! The project was a major release of SQL Doc from Red Gate which allows you to easily document your database schema. While maybe not as popular as SQL Compare, it solves a problem many people experience very effectively.

With SQL Doc 2.0, we had very clear aims and goals for what we wanted to achieve. We used a variety of different sources, such as support calls and forum posts, to identity the core features people where asking for. This provided us with three main features:

  • Printable documentation
  • Improve the description editor
  • Be able to use the links within the preview panel

With 2.0, we implemented the above features along with a series of small, yet important, changes to improve the overall experience for the user.

Documenting ASPNetDB

With ASP.net, there is a database called ASPnetDB, by default this is where various settings and data is stored for the built-in ASP.net providers, such as Membership and Personalization. While most of the time you don’t need to interact with the database directly, the times you do are extremely difficult due to the lack of documentation and the fact that SQL Server Management Studio isn’t designed for gaining a high level picture of the database schema. This is one example of where SQL Doc can help.

Upon connecting to your server, you can select the database(s) you wish to document. Here I’ve just selected aspnetdb.

image

Once you have selected all your databases, click Generate Documentation… SQL Doc can output the documentation to three different formats, CHM, HTML and now Microsoft Word (.doc). Depending on your use-case depends on the type of documentation you might require – we feel SQL Doc provides you with excellent support, however if you feel we are missing something let us know.

image

To understand ASPNetDb, I want to print off the database schema as I always find reading this easier in paper format than online. As such, I select Document to be my output. After a few moments, Word will open and I will have my documentation produced.

image

We can now navigate and read the document using word, or print it off. The documentation itself contains all the same information it would have within CHM or HTML, it’s just now in a printable format.

image

You can download this sample document here.

We also include the functionality to filter down the different objects, for example I might only want to include tables and stored procedures within the documentation. Using the treeview down the size, we can unselect the objects and categories we do not wish to be included.

By default, we include the SQL creation script for each database object, while this is very interesting certain users requested the ability to exclude this – which we have done. Within the Edit Project dialog there is a check box to include the SQL creation script, untick this and it will no longer be included.

image

The documentation now only includes the relevant sections while shorter due to the lack of SQL Scripts.  Feel free to take a look here.

You can download a 14 day free trail from our website:

http://www.red-gate.com/products/sql_doc

Technorati Tags: , ,

Red Gate and I @ PDC 2008

PDC is fast approaching with some amazing technology due to be announced. You can’t help but get excited by Windows 7, VS10 and who knows what else? Don’t forget technologies such as the Pfx, IronRuby and Pex who will all be talking about their future plans.  PDC is partly the reason why I haven’t been blogging as much, but trust me – there is content lined up!

But PDC is not just about the sessions!! Don’t forget about the partner expo where I’ll be working on the Red Gate stand!  If you haven’t had chance to try our tools before, we have some great demos lined up for you so you can see for yourself how more productive you can be with our tools!

If you are already a user of our tools, why not stop by anyway for a chat and let us know what you think about our products!

If you want to know more about the rest of the team who will be at PDC, read the post on the red@work blog.

The Red Gate PDC team will be at booth numbers 410 and 412! Why not stop by for a chat and a quick demo!

Finally, keep an eye on the blog, I’m planning to write a few posts live from the stand.

Technorati Tags: , , ,

Very exciting news! Red Gate’s .Net Reflector

ReflectorRed Gate announced today that, under a new agreement, it will be responsible for the future development of .NET Reflector, the popular tool authored by Lutz Roeder.

Reflector is one of the must-have tools if you are a .Net developer or tester, allowing you to really understand the software and APIs in a very unique fashion. Personally, I always have a copy of Reflector open on my desktop and without it .Net development would be very different!

If you are interested knowing more about this agreement, I would recommend you read the interview between James Moore and Lutz Roeder on Simple Talk. The most important part – “Red Gate will continue to offer the tool for free to the community.”

If you haven’t tried Reflector yet, I really recommend you download it. It could change the way you develop .Net applications – http://reflector.red-gate.com/

Technorati Tags: ,

How To: Script a SQL Database Schema and Data to a single file

At the moment I’m working on a web application, using ASP.net MVC connected to SQL Server 2005. As part of this work, I wanted to script out my current database schema into a flat file. I had created my SQL Database using SQL Management Studio on my laptop, but I wanted to reliability deploy this onto a machine in the office. 

Luckily, I had Red Gate SQL Compare to hand (Surprising as I work for Red Gate :))! This allows for a number of different approaches, such as connecting to the office via VPN and syncing my two databases or syncing to a scripts folder, but I found SQL Compare could create a single SQL file containing my database schema just using the command line tool.

The command below will sync my local Northwind database schema into a file called Schema.sql. If the file already exists, I override it.

SQLCompare.exe /database1:Northwind /scriptfile:”C:NorthwindSchema.sql” /force

Perfect!! I can now use SQL Cmd or SSMS to execute the file to produce my schema.

The same command works for SQL Data Compare, allowing me to script all the data in the database to a single sql file.

SQLDataCompare.exe /database1:Northwind /scriptfile:”C:NorthwindData.sql” /force

NOTE: These scripts do not include the USE statement, as such they will run under the database your connected on. You just need to be aware of what database your connected to or which database you use in your connection string

Using SQL Data Generator with your Unit Tests

Last month we released SQL Data Generator which is a great tool for generating your devtest data. One of the decisions we made for 1.0 was not to include an API. I wanted to be able to incorporate the data generation into my automated tests, so I have created an extension to SQL Data Generator as a side project which allows you to incorporate the generation process into your unit (well Integration) tests. You can now generate data as part of your unit tests.

I have wrote an article on my approach and how to use the framework over at Simple Talk – http://www.simple-talk.com/sql/sql-tools/using-sql-data-generator-with-your-unit-tests/

For the extension, I took two approaches. One approach was just a POCO (Plain Old CLR Object) which would execute the command line application for a given project file. This could be placed anywhere in your code, ideally I was thinking in the TestFixtureSetup.

[TestFixtureSetUp]
public void TestFixtureSetup()
{
    SDGConsoleWrapper sdgConsoleWrapper = new SDGConsoleWrapper(@”DatabaseNorthwindEmployees_Orders.sqlgen”);
    sdgConsoleWrapper.Execute();
}

As a parameter, you pass in the filename for the project file created via the UI.

The second approach is using custom attributes. By adding an attribute ‘SDG’ to the TestFixture and inheriting from ‘SDGContext’, we hooked into the calls for each method.  At the top of your test method, you can add the ‘SDGExecute’ with the project file name, this will then be executed before that test started.

[TestFixture]
[SDG]
public class DataAccessTests : SDGContext
{
   [Test]
   [SDGExecute(“DatabaseNorthwindCustomers_Orders.sqlgen”)]
   public void GetCustomerByID_SDGProject_1000Rows()
   {
      DataTable dt = DataAccess.GetCustomerByID(“00040”);

      Assert.AreEqual(“00040”, dt.Rows[0][“CustomerID”]);
      Assert.AreEqual(“Suppebentor Holdings “, dt.Rows[0][“CompanyName”]);
      Assert.AreEqual(“Bobbi Yates”, dt.Rows[0][“ContactName”]);
      Assert.AreEqual(“Web”, dt.Rows[0][“ContactTitle”]);
      Assert.AreEqual(“564 White Old Freeway”, dt.Rows[0][“Address”]);
   }
}

Best of all – this works with all of the unit testing frameworks! NUnit, MbUnit, XUnit, MSTest! They all can take advantage.  Feel free to download (from our CodePlex project) and start using it, I suggest you read the article for more of an in-depth overview of what is going on and the possible ways you can take advantage of it.

Finally, we would love to hear your feedback on this. I created this in my free time and is not a supported solution. However, do you think the application should have a fully supported API? Are you happy with the approach I have taken or do you think there are better ways of doing this?  Maybe your comments will help shape SQL Data Generator 2.0.

Article Link: http://www.simple-talk.com/sql/sql-tools/using-sql-data-generator-with-your-unit-tests/

Application Download Link: Red Gate SQL Data Generator

Download and Source: http://www.codeplex.com/SDGGenerators

Red Gate SQL Data Generator 1.0 – Generating data for Northwind

Red Gate SQL Data Generator1022 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 prepost 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.

image

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.

 image

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.

image

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. 

image

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. 

image

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.

image

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