Cool Database Tool

For the last 2 months Jan/Feb 2008, I have won the C# article of the month at codeproject, and as such, I receive a lot of free software. Which I normally don’t ever look at. The reason for this is simple. I write articles to share with people, so these free bits of software really are not much use to me. As when I publish a new article the reader of the article will NOT have the same software installed as me, so will not be able to run my code in Visual Studio.

So because of this I rarely even look at the free software that comes my way.

However at work I do a lot of work with databases, it used to be all SQL Server 2005. But now its Oracle 10g. Though I still prefer SQL Server by miles.

So as I like SQL Server, I look out for cool things to help me out in my day to day work.

One of the products that was part of the codeproject prize fund for these 2 months, actually turned out to be worth looking at. As I have done loads of database stuff in the past, so have an appreciation for any tool that saves me time. I feel this is one such product.

The product is a database re-synchronization component. Notice that its not an application but a component, which means you can embed it straight into your application and use it by calling the UpdateDatabase() directly on the component.

The component is called "Database Restyle" by a company called Perpetuumsoft, and it integrates straight into a .NET project.

I think the best way to demonstrate this components, capabilities is to have a look at what it does. I am using a SQL Server 2005 installation with the standard Northwind database installed. Notice below that at the moment there are no Scalar-Valued Functions as part of the Northwind database.

image

Now jumping over to Visual Studio (I’m using VS2008), we can see that there is a actual component we can drag to a Winforms/Console/Web application.

image

Now in code behind, I can simple use the components update method, and my database will re-synchronize with any thing that has been altered on the current schema. Thats the model the application is using.

Again maybe an example is required here. I am using the example that came with the Database Restyle component installed samples.

   1:  /*****************************************************************************
   2:  
   3:      This source file is a part of Database Restyle
   4:      
   5:      Copyright (c) 2008 Perpetuum Software LLC. All rights reserved.
   6:      THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY 
   7:      OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
   8:      LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
   9:      FITNESS FOR A PARTICULAR PURPOSE.
  10:      
  11:      Copyright (c) 2008 Perpetuum Software LLC. All rights reserved.
  12:  
  13:  *****************************************************************************/
  14:  using System;
  15:  using System.Collections.Generic;
  16:  using System.ComponentModel;
  17:  using System.Data;
  18:  using System.Data.SqlClient;
  19:  using System.Data.Linq.Mapping;
  20:  using System.Drawing;
  21:  using System.Linq;
  22:  using System.Text;
  23:  using System.Windows.Forms;
  24:  using PerpetuumSoft.DataModel.MsSql.Synchronizers;
  25:  using PerpetuumSoft.DataModel.MsSql;
  26:  using PerpetuumSoft.DataModel.LinqToSql;
  27:   
  28:  namespace LinqToSql
  29:  {
  30:      public partial class MainForm : Form
  31:      {
  32:          public MainForm()
  33:          {
  34:              InitializeComponent();
  35:              dbBuilder.CreateFunctions = true;
  36:              dbBuilder.FunctionRequire += new 
  37:                  EventHandler<PerpetuumSoft.DataModel.FunctionRequareEventArgs>
  38:                  dbBuilder_FunctionRequire);
  39:          }
  40:   
  41:          private StringBuilder log;
  42:   
  43:          private LinqDatabaseBuilder dbBuilder = new LinqDatabaseBuilder();
  44:   
  45:          private void dbBuilder_FunctionRequire(object sender, 
  46:              PerpetuumSoft.DataModel.FunctionRequareEventArgs e)
  47:          {
  48:              if (e.SchemaName == "dbo" && e.FunctionName == "ProductsUnderThisUnitPrice")
  49:              {
  50:                  string text =
  51:                      @"CREATE FUNCTION [dbo].[ProductsUnderThisUnitPrice]()
  52:                      RETURNS int
  53:                      AS
  54:                      BEGIN
  55:                         DECLARE @retval int
  56:                         SELECT @retval = COUNT(*) FROM Territory
  57:                         RETURN @retval
  58:                      END;";
  59:                  ScalarFunction function = new 
  60:                      PerpetuumSoft.DataModel.MsSql.ScalarFunction(e.FunctionName, text);
  61:                  function.ReturnValueType = new DataType.Int();
  62:                  e.Function = function;
  63:              }
  64:              else
  65:              {
  66:                  throw new Exception(String.Format("Unknown function: [{0}].[{1}].", 
  67:                      e.SchemaName, e.FunctionName));
  68:              }
  69:          }
  70:   
  71:          private void exitButton_Click(object sender, EventArgs e)
  72:          {
  73:              this.Close();
  74:          }
  75:   
  76:          private void syncButton_Click(object sender, EventArgs e)
  77:          {
  78:              try
  79:              {
  80:                  log = new StringBuilder();
  81:   
  82:                  MetaModel model = new AttributeMappingSource().
  83:                      GetModel(typeof(DataClassesDataContext));
  84:                  Database sourceDB = dbBuilder.CreateDatabase(model);
  85:                  databaseSync.UpdateDatabase(sourceDB, GetConnectionString());
  86:   
  87:                  logTextBox.Text = log.ToString();
  88:              }
  89:              catch (Exception ex)
  90:              {
  91:                  logTextBox.Text = log.ToString();
  92:                  logTextBox.Text += ex.ToString();
  93:              }
  94:          }
  95:   
  96:          private string GetConnectionString()
  97:          {
  98:              SqlConnectionStringBuilder connectionString = 
  99:                  new SqlConnectionStringBuilder();
 100:              connectionString.IntegratedSecurity = true;
 101:              connectionString.InitialCatalog = databaseName.Text;
 102:              connectionString.DataSource = serverName.Text;
 103:              return connectionString.ConnectionString;
 104:          }
 105:   
 106:          private void databaseSync_ScriptExecuting(object sender, 
 107:              PerpetuumSoft.DataModel.ScriptExecuteEventArgs e)
 108:          {
 109:              log.AppendLine(e.Text);
 110:          }
 111:   
 112:          private void databaseSync_DatabaseUpdating(object sender, 
 113:              PerpetuumSoft.DataModel.DatabaseUpdatingEventArgs e)
 114:          {
 115:              log.Append("Begin synchronize: [");
 116:              log.Append(DateTime.Now.ToLongTimeString());
 117:              log.AppendLine("]");
 118:          }
 119:   
 120:          private void databaseSync_DatabaseUpdated(object sender, EventArgs e)
 121:          {
 122:              log.Append("End synchronize: [");
 123:              log.Append(DateTime.Now.ToLongTimeString());
 124:              log.Append("]");
 125:          }
 126:   
 127:          private void clearDbButton_Click(object sender, EventArgs e)
 128:          {
 129:              try
 130:              {
 131:                  log = new StringBuilder();
 132:   
 133:                  Database sourceDB = Database.CreateDatabaseWithSystemObjects();
 134:                  databaseSync.UpdateDatabase(sourceDB, GetConnectionString());
 135:   
 136:                  logTextBox.Text = log.ToString();
 137:              }
 138:              catch (Exception ex)
 139:              {
 140:                  logTextBox.Text = log.ToString();
 141:                  logTextBox.Text += ex.ToString();
 142:              }
 143:          }
 144:   
 145:          private void viewScriptsButton_Click(object sender, EventArgs e)
 146:          {
 147:              try
 148:              {
 149:                  log = new StringBuilder();
 150:   
 151:                  MetaModel model = new AttributeMappingSource().
 152:                      GetModel(typeof(DataClassesDataContext));
 153:                  Database sourceDB = dbBuilder.CreateDatabase(model);
 154:                  DatabaseSynchronizer dbSynchronizer = 
 155:                      databaseSync.Compare(sourceDB, 
 156:                      databaseSync.ReverseDatabase(GetConnectionString()));
 157:                  foreach (Script script in dbSynchronizer.Scripts)
 158:                  {
 159:                      log.AppendLine(script.GetText());
 160:                  }
 161:                  logTextBox.Text = log.ToString();
 162:              }
 163:              catch (Exception ex)
 164:              {
 165:                  logTextBox.Text = log.ToString();
 166:                  logTextBox.Text += ex.ToString();
 167:              }
 168:          }
 169:   
 170:      }
 171:  }

The most important lines above are these

   1:  MetaModel model = new AttributeMappingSource().
   2:     GetModel(typeof(DataClassesDataContext));
   3:  Database sourceDB = dbBuilder.CreateDatabase(model);
   4:  databaseSync.UpdateDatabase(sourceDB, GetConnectionString());

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

This example is using a LINQ to SQL file as the database schema that is the one that forms the applications model at runtime. So this one will be the source of the comparison against the underlying database when a resynch is performed. It can be seen that this model (LINQ to SQL) has 3 tables in existence. These tables already exist in the underlying Northwind database.

image

Here they are

image

But the in the code that associated with the form, that I showed earlier, there is a new Function created entitled "ProductsUnderThisUnitPrice" which doesn’t exist in the underlying Northwind database.

So running the application shows us this new Function being created by the Database Restyle component.

image

Well that’s all well and good but did it actually create this in the underlying database?

image

The answer is yes.

So using the demo code, I decided to do something radical and clear the database, and then do a re-synchronization. And that worked as well. You can see below that there is a bunch of SQL generated to DROP tables/constraints etc etc

image

And going back to SQL we can see these tables are no longer within the Northwind database.

image

I then hit the Synchronize button, went back to SQL Server, and bingo all was back again. Here is what the component produces by way of script to re-create the schema again.

 

   1:  Begin synchronize: [08:49:24]
   2:   
   3:  CREATE TABLE [dbo].[Categories]([CategoryID] INT NOT NULL IDENTITY(1,1),
   4:  [CategoryName] NVARCHAR(15) NOT NULL ,[Description] NTEXT NULL ,[Picture] IMAGE NULL )
   5:   
   6:  ALTER TABLE [dbo].[Categories] ADD CONSTRAINT [PK_Categories] PRIMARY KEY 
   7:  NONCLUSTERED ([CategoryID] ASC)  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,
   8:  STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)
   9:   
  10:  CREATE TABLE [dbo].[Products]([ProductID] INT NOT NULL IDENTITY(1,1),
  11:  [ProductName] NVARCHAR(40) NOT NULL ,[SupplierID] INT NULL ,[CategoryID] INT NULL ,
  12:  [QuantityPerUnit] NVARCHAR(20) NULL ,[UnitPrice] MONEY NULL ,[UnitsInStock] SMALLINT NULL ,
  13:  [UnitsOnOrder] SMALLINT NULL ,[ReorderLevel] SMALLINT NULL ,[Discontinued] BIT NOT NULL )
  14:   
  15:  ALTER TABLE [dbo].[Products] ADD CONSTRAINT [PK_Products] 
  16:  PRIMARY KEY NONCLUSTERED ([ProductID] ASC)  
  17:  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,
  18:  ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)
  19:   
  20:  CREATE TABLE [dbo].[Suppliers]([SupplierID] INT NOT NULL IDENTITY(1,1),
  21:  [CompanyName] NVARCHAR(40) NOT NULL ,[ContactName] NVARCHAR(30) NULL ,
  22:  [ContactTitle] NVARCHAR(30) NULL ,[Address] NVARCHAR(60) NULL ,[City] NVARCHAR(15) NULL ,
  23:  [Region] NVARCHAR(15) NULL ,[PostalCode] NVARCHAR(10) NULL ,[Country] NVARCHAR(15) NULL ,
  24:  [Phone] NVARCHAR(24) NULL ,[Fax] NVARCHAR(24) NULL ,[HomePage] NTEXT NULL )
  25:   
  26:  ALTER TABLE [dbo].[Suppliers] ADD CONSTRAINT [PK_Suppliers] 
  27:  PRIMARY KEY NONCLUSTERED ([SupplierID] ASC)  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,
  28:  STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)
  29:   
  30:  ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [Category_Product] 
  31:  FOREIGN KEY (CategoryID) REFERENCES [dbo].[Categories] (CategoryID)  
  32:  ON UPDATE NO ACTION ON DELETE NO ACTION
  33:   
  34:  ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [Supplier_Product] 
  35:  FOREIGN KEY (SupplierID) REFERENCES [dbo].[Suppliers] (SupplierID)  
  36:  ON UPDATE NO ACTION ON DELETE NO ACTION
  37:   
  38:  CREATE FUNCTION [dbo].[ProductsUnderThisUnitPrice]()
  39:                      RETURNS int
  40:                      AS
  41:                      BEGIN
  42:                         DECLARE @retval int
  43:                         SELECT @retval = COUNT(*) FROM Territory
  44:                         RETURN @retval
  45:                      END;
  46:  End synchronize: [08:49:24]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

And here is a screen shot after the Synchronize .

image

Cool huh.

Perpetuumsoft also claim to support other schemas apart from using LINQ to SQL.

All in all a very nice easy to use product I think. This is one that I think is actually fairly useful (that’s why I bothered to blog about it), and I shall be pushing to use whenever I get back to working with SQL Server.

 

Here is Perpetuumsoft web site, if you want to download it and play for yourself http://www.perpetuumsoft.com

Advertisements

8 thoughts on “Cool Database Tool

  1. Uwe says:

    So this is like RedGate’s “SQL Compare”, just as a component to integrate into my own applications?!?

  2. sacha says:

    Yeah SQL Compare is another great tool. Its been a while since I looked at that, but did that have a component that you could use in your own apps?

    I didnt think so. The idea behind this one is that it is a component, so yes you can use it in your own apps.

    It looks pretty cool actually.

  3. Brendan says:

    Uwe, yes this is like Red-gate’s tool.

    Sacha, Red-gate have had a synchronization library since at least 2005 as far as I recall. It wasn’t a component as such that you could drop onto a form, but they did provide a full set of API’s, you just had to add the references to the CLR assemblies. It is basically a combination of their 3 products, SQL Compare, SQL Data Compare and SQL Packager. It was sold under the name SQL Toolkit if memory serves me.

    I haven’t looked at it since 2005 so maybe they have components to manage it now.

    However getting free s/w is always nice 🙂

  4. sacha says:

    Oh Ok Brendan..Sounds like you know more than me, memory banks are tired.

    Thanks for the extra info here

  5. roalyroe says:

    Nice find sacha! per usual!

    I keep my eye on you and your blog roll mate…
    You’re one to watch these days!!! Very informative fellow..!


    William.

  6. roalyroi says:

    BTW, a very nice make-over of your site; it’s clean and cut mate!

  7. sacha says:

    Thanks very much

  8. sacha says:

    Ah the site is just wordpress with some skin I liked of the net. I like it though.

    Its one of the better ones out there

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: