This project is read-only.

 

LINQ To SQL model is easily synchronized with your live database

About Linq To Sql
Nothing is ideal
Using SQL Schema Sync API to transfer modifications from LINQ To SQL Classes to database structure
Features demonstration
Changes in LINQ To SQL Classes schema and automatic transfer of these changes to the live database
Adding elements that are not provided by the model to the database
As a conclusion

Attachment: LinqToSqlAndRestyleDemo. (Sample solution created to demonstrate automatic synchronization of LINQ To SQL model with your live database).

About Linq To Sql

LINQ To SQL is one of the most promising technologies that appeared lately in .NET programming. Since it became available developers using .Net have no need to mix code with SQL commands within the application to access database objects. Now developers can access data in databases using programming language they get used to (C#, VB.NET). Moreover, using LINQ To SQL allows developer to uniformly manage all iterative data sources: databases, XML, various collections, etc. In addition you get code verification during compilation, full integration in Visual Studio which provides many advantages: IntelliSense, database model designer, auto generation of code by the model and so on.

LINQ To SQL is a technology worth paying attention to when you develop applications that actively uses external data.

Nothing is ideal

And still in spite of all advantages of using LINQ To SQL, its usage in real large projects is complicated by some problems. Using LINQ To SQL in the project will make you use such Visual Studio component as LINQ To SQL Classes. The component is complicated to use as it should be kept in sync with the structure of real database. Here problems start:

     1. If you make modifications to LINQ To SQL Classes, these modifications can be transferred to real database only by its complete re-creation (it’s good that the availability of database creation by LINQ To SQL Classed is stipulated and implemented). Such approach is very often unaccepted, since database can be filled with real or test data.

     2. If you make modifications to real database structure, then it is possible to get LINQ To SQL Classes again by complete re-creation of LINQ To SQL Classes: you need to delete the old schema and create a new one. This causes much inconvenience as it results in complete code re-generation and change of schema elements’ position.

The variant of synchronizing LINQ To SQL Classes and database structure in manual mode is not considered at all due to huge work content and possibility of errors. Nevertheless, it is very important to keep LINQ To SQL Classes in sync with database structure, as LINQ To SQL Classes and database structure are often modified during software design.

Using SQL Schema Sync API to transfer modifications from LINQ To SQL Classes to database structure

In order to solve the first problem mentioned in the previous chapter, Perpetuum Software LLC developed the PerpetuumSoft.DataModel.LinqToSql library. This library allows the transfer of changes from LINQ To SQL to database structure without database re-creation. This library is based on another more functional SQL Schema Sync API designed to synchronize two databases.

Using SQL Schema Sync API will help you execute database synchronization by LINQ To SQL Classes with just a few additional lines of code.

Features demonstration

Let’s create WindowsFromApplication and add LINQ To SQL Classes type component there (name it AdvWorks). In order not to create LINQ To SQL Classes schema manually, use the ability to create it by the ready database. Create new connection to the AdventureWork MS SQL Server database (Server Explorer window in Visual Studio) and drag-and-drop necessary tables from database to the schema. Select the following tables: Employee, EmployeeAddress and Address:

 

LinqToSQLClasses

In order to demonstrate how SQL Schema Sync API works, let’s place the following component onto the main application form:

targetServerTextBox – to enter server name;

targetDbTextBox – to enter database name;

logTextBox – to display text of SQL commands;

showScriptsButton – on button press, the LogTextBox will display SQL commands required to synchronize server database with LINQ To SQL Classes;

synchronizeButton – on button press, synchronization of server database with LINQ To SQL Classes will be executed; and logTextBox wil display SQL commands required to synchronize server database with LINQ To SQL Classes.

To implement this functionality, write the following code in the main form of your application:

using System;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.Data.Linq.Mapping;

using PerpetuumSoft.DataModel;

using PerpetuumSoft.DataModel.MsSql;

using PerpetuumSoft.DataModel.MsSql.Synchronizers;

using PerpetuumSoft.DataModel.LinqToSql;

 

namespace LinqToSqlAndRestyleDemo

{

  public partial class MainForm : Form

  {

    private StringBuilder log = new StringBuilder();

 

    public MainForm()

    {

      InitializeComponent();

    }

 

    protected override void OnLoad(EventArgs e)

    {

      base.OnLoad(e);

      databaseSync.ScriptExecuting +=

        new EventHandler<ScriptExecuteEventArgs>(databaseSync_ScriptExecuting);

      showScriptsButton.Click += new EventHandler(showScriptsButton_Click);

      synchronizeButton.Click += new EventHandler(synchronizeButton_Click);

    }

 

    void databaseSync_ScriptExecuting(object sender,

      PerpetuumSoft.DataModel.ScriptExecuteEventArgs e)

    {

      log.AppendLine(e.Text);

      log.AppendLine();

    }

 

    private string GetConnectionString(string serverName, string databaseName)

    {

      SqlConnectionStringBuilder sourceConnStr = new SqlConnectionStringBuilder(

        @"Integrated Security=True;Connect Timeout=30;User Instance=False;");

      sourceConnStr.DataSource = serverName;

      sourceConnStr.InitialCatalog = databaseName;

      return sourceConnStr.ConnectionString;

    }

 

    private void showScriptsButton_Click(object sender, EventArgs e)

    {

      string targetConnectionString =

        GetConnectionString(targetServerTextBox.Text, targetDbTextBox.Text);

      logTextBox.Clear();

      try

      {

        log = new StringBuilder();

        MetaModel model =

          new AttributeMappingSource().GetModel(typeof(AdvWorksDataContext));

        LinqDatabaseBuilder builder = new LinqDatabaseBuilder();

        Database database = builder.CreateDatabase(model);

 

        DatabaseSynchronizer dbSync = databaseSync.Compare(

          database, databaseSync.ReverseDatabase(targetConnectionString));

        foreach (Script script in dbSync.Scripts)

        {

          log.AppendLine(script.GetText());

          log.AppendLine();

        }

        log.AppendLine("-- End of script");

        logTextBox.Text = log.ToString();

      }

      catch (Exception ex)

      {

        logTextBox.Text = ex.Message;

      }

    }

 

    private void synchronizeButton_Click(object sender, EventArgs e)

    {

      log = new StringBuilder();

      string targetConnectionString =

        GetConnectionString(targetServerTextBox.Text, targetDbTextBox.Text);

      try

      {

        try

        {

          MetaModel model =

            new AttributeMappingSource().GetModel(typeof(AdvWorksDataContext));

          Database database = new LinqDatabaseBuilder().CreateDatabase(model);

          databaseSync.UpdateDatabase(database, targetConnectionString);

          log.AppendLine("-- End of script");

          logTextBox.Text = log.ToString();

        }

        catch (Exception ex)

        {

          logTextBox.Text = log.ToString();

          logTextBox.Text += ex.Message;

        }

      }

      catch (Exception ex)

      {

        logTextBox.Text = log.ToString();

        logTextBox.Text += ex.Message;

      }

    }

  }

}

 

Execute synchronization of the shown model with live server database. Let’s say that database is just created and that’s why all SQL commands generated by SQL Schema Sync API will aimed at the creation of three tables and relations between them. This script was generated by SQL Schema Sync API to create database:

CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo]

 

CREATE TABLE [HumanResources].[Employee]([EmployeeID] INT NOT NULL IDENTITY(1,1),[NationalIDNumber] NVARCHAR(15) NOT NULL ,[ContactID] INT NOT NULL ,[LoginID] NVARCHAR(256) NOT NULL ,[ManagerID] INT NULL ,[Title] NVARCHAR(50) NOT NULL ,[BirthDate] DATETIME NOT NULL ,[MaritalStatus] NCHAR(1) NOT NULL ,[Gender] NCHAR(1) NOT NULL ,[HireDate] DATETIME NOT NULL ,[SalariedFlag] BIT NOT NULL ,[VacationHours] SMALLINT NOT NULL ,[SickLeaveHours] SMALLINT NOT NULL ,[CurrentFlag] BIT NOT NULL ,[rowguid] UNIQUEIDENTIFIER NOT NULL ,[ModifiedDate] DATETIME NOT NULL )

 

ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [PK_Employee] PRIMARY KEY NONCLUSTERED ([EmployeeID] ASC)  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)

 

CREATE TABLE [HumanResources].[EmployeeAddress]([EmployeeID] INT NOT NULL ,[AddressID] INT NOT NULL ,[rowguid] UNIQUEIDENTIFIER NOT NULL ,[ModifiedDate] DATETIME NOT NULL )

 

ALTER TABLE [HumanResources].[EmployeeAddress] ADD CONSTRAINT [PK_EmployeeAddress] PRIMARY KEY NONCLUSTERED ([EmployeeID] ASC,[AddressID] ASC)  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)

 

CREATE SCHEMA [Person] AUTHORIZATION [dbo]

 

CREATE TABLE [Person].[Address]([AddressID] INT NOT NULL IDENTITY(1,1),[AddressLine1] NVARCHAR(60) NOT NULL ,[AddressLine2] NVARCHAR(60) NULL ,[City] NVARCHAR(30) NOT NULL ,[StateProvinceID] INT NOT NULL ,[PostalCode] NVARCHAR(15) NOT NULL ,[rowguid] UNIQUEIDENTIFIER NOT NULL ,[ModifiedDate] DATETIME NOT NULL )

 

ALTER TABLE [Person].[Address] ADD CONSTRAINT [PK_Address] PRIMARY KEY NONCLUSTERED ([AddressID] ASC)  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)

 

ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [Employee_Employee] FOREIGN KEY (ManagerID) REFERENCES [HumanResources].[Employee] (EmployeeID)  ON UPDATE NO ACTION ON DELETE NO ACTION

 

ALTER TABLE [HumanResources].[EmployeeAddress] WITH CHECK ADD CONSTRAINT [Employee_EmployeeAddress] FOREIGN KEY (EmployeeID) REFERENCES [HumanResources].[Employee] (EmployeeID)  ON UPDATE NO ACTION ON DELETE NO ACTION

 

ALTER TABLE [HumanResources].[EmployeeAddress] WITH CHECK ADD CONSTRAINT [Address_EmployeeAddress] FOREIGN KEY (AddressID) REFERENCES [Person].[Address] (AddressID)  ON UPDATE NO ACTION ON DELETE NO ACTION

 

All that was done by this moment could be done without SQL Schema Sync API. You just could fire the CreateDatabase() method of the AdvWorksDataContext type object. Further, we will consider the situation for which the PerpetuumSoft.DataModel.LinqToSql library was designed, as offered functionality provides new features that were not provided by the developers of .Net Framework.

Changes in LINQ To SQL Classes schema and automatic transfer of these changes to the live database.

Very often during software design, developers have to make modifications to database structures used by the application. This happens for several reasons: requirement for final product are changed, details that were not considered at design-time appeared, etc.

Let’s make some changes to LONQ To SQL Classes schema:

1. Change type of the [HumanResources].[Employee].[MaritalStatus] field to bool (Bit NOT NULL – data type on server); this field will store True value id the employee is married;

2. Add new [HumanResources].[Employee].[IsPlaceOfBirth] field informing that the specified address is employees place of birth. Define type of a new field as bool (Bit NOT NULL – data type on server).

ALTER TABLE [HumanResources].[EmployeeAddress] DROP CONSTRAINT [Employee_EmployeeAddress]

 

ALTER TABLE [HumanResources].[EmployeeAddress] DROP CONSTRAINT [Address_EmployeeAddress]

 

ALTER TABLE [HumanResources].[Employee] DROP CONSTRAINT [Employee_Employee]

 

ALTER TABLE [HumanResources].[EmployeeAddress] DROP CONSTRAINT [PK_EmployeeAddress]

 

ALTER TABLE [HumanResources].[Employee] DROP CONSTRAINT [PK_Employee]

 

ALTER TABLE [HumanResources].[Employee] ADD TEMP_COLUMN BIT NOT NULL  CONSTRAINT TEMP_CONSTRAINT DEFAULT 0 WITH VALUES

ALTER TABLE [HumanResources].[Employee] DROP CONSTRAINT TEMP_CONSTRAINT

UPDATE [HumanResources].[Employee] set TEMP_COLUMN = ([MaritalStatus]) WHERE MaritalStatus IS NOT NULL

ALTER TABLE [HumanResources].[Employee] DROP COLUMN [MaritalStatus]

Execute sp_rename 'HumanResources.Employee.TEMP_COLUMN','MaritalStatus','COLUMN'

 

ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [PK_Employee] PRIMARY KEY NONCLUSTERED ([EmployeeID] ASC)  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)

 

ALTER TABLE [HumanResources].[EmployeeAddress] ADD IsPlaceOfBirth BIT NOT NULL  CONSTRAINT TEMP_CONSTRAINT DEFAULT 0 WITH VALUES

ALTER TABLE [HumanResources].[EmployeeAddress] DROP CONSTRAINT TEMP_CONSTRAINT

 

ALTER TABLE [HumanResources].[EmployeeAddress] ADD CONSTRAINT [PK_EmployeeAddress] PRIMARY KEY NONCLUSTERED ([EmployeeID] ASC,[AddressID] ASC)  WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)

 

ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [Employee_Employee] FOREIGN KEY (ManagerID) REFERENCES [HumanResources].[Employee] (EmployeeID)  ON UPDATE NO ACTION ON DELETE NO ACTION

 

ALTER TABLE [HumanResources].[EmployeeAddress] WITH CHECK ADD CONSTRAINT [Employee_EmployeeAddress] FOREIGN KEY (EmployeeID) REFERENCES [HumanResources].[Employee] (EmployeeID)  ON UPDATE NO ACTION ON DELETE NO ACTION

 

ALTER TABLE [HumanResources].[EmployeeAddress] WITH CHECK ADD CONSTRAINT [Address_EmployeeAddress] FOREIGN KEY (AddressID) REFERENCES [Person].[Address] (AddressID)  ON UPDATE NO ACTION ON DELETE NO ACTION 

 

Adding elements that are not provided by the model to the database

LINQ To SQL Classes support only the following object types: tables, stored procedures, primary keys, external keys. But what to do if your database has to contain other important object types? For example, it would be useful to add indexes, constraints, DLL triggers, etc.

SQL Schema Sync API provides such ability. In this part of the article we will consider mechanism of adding indexes as a sample to create a system that allows automatic addition of objects of necessary types to the database structure.

The LinqDatabaseBuilder object returns Database type object that is further used during synchronization. This object represents object model of database structure and makes it possible to add, delete, and modify objects in this structure. After the Database object is got from the LinqDatabaseBuilder, it would be easy to add there necessary objects by yourself (indexes, constraints, etc.). But this approach is fraught with errors – changes in database model don’t synchronize with addition of these objects. This article will show how to automate this process by using attributes, partial classes and reflection mechanism.

So, let’s create the IndexAttribute attribute to mark classes corresponding to DataContext tables. To demonstrate the approach, it is enough to specify name of the created index and list of columns the index is created for in this attribute (though it is possible to create many other settings for the attribute). The following code is got for the IndexAttribute class:

using System;

using System.Collections.Generic;

 

namespace LinqToSqlAndRestyleDemo

{

  [AttributeUsage(AttributeTargets.Class, AllowMultiple = true)]

  public class IndexAttribute : Attribute

  {

    public IndexAttribute(string name, params string[] columnNames)

    {

      this.name = name;

      foreach (string col in columnNames)

      {

        this.columnNames.Add(col);

      }

    }

 

    #region Properties

   

    private string name = string.Empty;

 

    public string Name

    {

      get

      {

        return name;

      }

      set

      {

        name = value;

      }

    }

 

    private List<string> columnNames = new List<string>();

 

    public List<string> ColumnNames

    {

      get

      {

        return columnNames;

      }

    }

 

    #endregion

  }

}

 

As you see from code, classes can be marked with this attribute, and each class can be marked for several times. It is possible to create partial classes for classes corresponding to tables. For example, create partial class for the Employee class and mark it with our new attribute.

namespace LinqToSqlAndRestyleDemo

{

  [Index("BirthDateIndex", "BirthDate")]

  [Index("HoursIndex", "VacationHours", "SickLeaveHours")]

  partial class Employee

  {

  }

}

It is obvious that in order to create BirthDateIndex and HoursIndex for the Employees table, it is not enough just to specify them. Let’s create a set of methods that will be responsible for adding objects that are not provided by the Database model.

private void AddCustomObjects(Type dataContextType, Database database)

{

  foreach (PropertyInfo property in dataContextType.GetProperties())

  {

    // check property type

    if (property.PropertyType.IsGenericType)

    {

      Type propertyTypeGeneric = property.PropertyType.GetGenericTypeDefinition();

      if (propertyTypeGeneric.FullName == typeof(System.Data.Linq.Table<>).FullName)

      {

        // get type of argument in generic

        Type[] types = property.PropertyType.GetGenericArguments();

        if (types.Length == 1) // must be only one parameter in generic

        {

          AddCustomObjectsForTable(database, types[0]);

        }

      }

    }

  }

}

 

private void AddCustomObjectsForTable(Database database, Type tableType)

{

  object[] tableAttributes = tableType.GetCustomAttributes(typeof(TableAttribute), false);

  if (tableAttributes.Length == 1)

  {

    foreach (Attribute indexAttribute in tableType.GetCustomAttributes(typeof(IndexAttribute), false))

    {

      FullName fullName = new FullName(((TableAttribute)tableAttributes[0]).Name);

      Table table = null;

      if (string.IsNullOrEmpty(fullName.SchemaName))

        table = database.Objects.GetElement<Table>(fullName.ObjectName);

      else

        table = database.Objects.GetObject<Table>(fullName.SchemaName, fullName.ObjectName);

      AddIndex(database, table, (IndexAttribute)indexAttribute);

    }

  }

}

 

private void AddIndex(Database database, Table indexTable, IndexAttribute indexAttribute)

{

  //create index

  Index index = new Index(indexAttribute.Name, indexTable);

  // fill index columns

  foreach (string columnName in indexAttribute.ColumnNames)

  {

    IndexColumn indexColumn = new IndexColumn(indexTable.Columns[columnName]);

    index.Columns.Add(indexColumn);

  }

  // add index to database objects

  database.Objects.Add(index);

}

 

The AddCustomObject method accepts descendant type from the DataContext and Database type object as source parameters. The dataContextType object is used to define types corresponding to tables. The AddCustomObjectsForTable method which adds objects related to the corresponding table in the database (in this case only indexes by the AddIndex method) is called for all such types. Thus, all tables included in the DataContext are defined in the code above. Then index is created for each table. Using this approach will allow you to create constraints, as it is universal.

Add the AddCustomObject to the showScriptsButton_Click и synchronizeButton_Click methods:

 

Database database = new LinqDatabaseBuilder().CreateDatabase(model);

AddCustomObjects(typeof(AdvWorksDataContext), database);

databaseSync.UpdateDatabase(database, targetConnectionString);

 

Run the application and you will see the following scripts generated to add two indexes:

 

CREATE NONCLUSTERED INDEX [BirthDateIndex] ON [HumanResources].[Employee] ([BirthDate] ASC) WITH(PAD_INDEX = OFF,SORT_IN_TEMPDB = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ONLINE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON,MAXDOP = 0)

 

CREATE NONCLUSTERED INDEX [HoursIndex] ON [HumanResources].[Employee] ([VacationHours] ASC,[SickLeaveHours] ASC) WITH(PAD_INDEX = OFF,SORT_IN_TEMPDB = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ONLINE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON,MAXDOP = 0)

 

That is the very result we needed.

As a conclusion

As you can see from all written above, LINQ To SQL is a promising technology with a wide range of features. However, people always want something better and that is why Perpetuum Software LLC offers you quite functional tool extending standard abilities of LINQ To SQL! Have a nice coding!

Last edited Nov 9, 2010 at 5:01 AM by PerpetuumSoft, version 12

Comments

No comments yet.