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:
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!
|