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 it's 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 I 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 it's 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 the 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.
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.
Now in code behind, I can simply use the components update method, and my database will re-synchronize with anything that has been altered on the current schema. That's 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:
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.
Here they are:
But in the code that is 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.
Well that’s all well and good but did it actually create this in the underlying database?
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.
And going back to SQL, we can see these tables are no longer within the Northwind database.
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.
Cool, huh?
Perpetuumsoft also claims 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 the Perpetuumsoft web site, if you want to download it and play with it.
