Click here to Skip to main content
Click here to Skip to main content

Cool Database Tool

By , 17 Jun 2009
Rate this:
Please Sign up or sign in to vote.

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-thumb7.png

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-thumb8.png

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());

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-thumb9.png

Here they are

image-thumb10.png

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-thumb11.png

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

image-thumb12.png

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-thumb13.png

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

image-thumb14.png

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]

And here is a screen shot after the Synchronize .

image-thumb15.png

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Sacha Barber
Software Developer (Senior)
United Kingdom United Kingdom
I currently hold the following qualifications (amongst others, I also studied Music Technology and Electronics, for my sins)
 
- MSc (Passed with distinctions), in Information Technology for E-Commerce
- BSc Hons (1st class) in Computer Science & Artificial Intelligence
 
Both of these at Sussex University UK.
 
Award(s)

I am lucky enough to have won a few awards for Zany Crazy code articles over the years

  • Microsoft C# MVP 2014
  • Codeproject MVP 2014
  • Microsoft C# MVP 2013
  • Codeproject MVP 2013
  • Microsoft C# MVP 2012
  • Codeproject MVP 2012
  • Microsoft C# MVP 2011
  • Codeproject MVP 2011
  • Microsoft C# MVP 2010
  • Codeproject MVP 2010
  • Microsoft C# MVP 2009
  • Codeproject MVP 2009
  • Microsoft C# MVP 2008
  • Codeproject MVP 2008
  • And numerous codeproject awards which you can see over at my blog

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140421.2 | Last Updated 17 Jun 2009
Article Copyright 2009 by Sacha Barber
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid