Entity-Generation Tools - SQL Server 2008

O/RM is based on entities. An entity is a class that has its properties mapped to table columns. Usually, databases have a lot of tables and a lot of columns. An O/RM framework such as LINQ to SQL provides tools to generate entities automatically, so developers don’t need to create them manually.

The LINQ to SQL framework provides two tools for generating entities: SQLMetal and the LINQ to SQL Classes Designer in Visual Studio 2008. These tools generate the same entities with the same code. The big difference is that Visual Studio’s LINQ to SQL Classes Designer allows you to choose which table to transform into an entity; the SQLMetal tool does not allow you to choose. SQLMetal generates all the entities analyzing all the tables contained in the database. Also, SQLMetal can produce .mdf, .sdf, or .dbml files. The LINQ to SQL Classes Designer produces only .dbml files. A .dbml file contains XML tags representing the database’s structure.

Using SQLMetal

SQLMetal is a command-line program that generates entities from a provided database. The executable resides in the C:Program FilesMicrosoft SDKsWindowsv6.0Abin folder, but you can easily use it from the Visual Studio 2008 command prompt, accessed from the Visual Studio Tools menu. The following is the command to use SQLMetal:

SQLMetal [options] [input file]

For [options], you can use the options listed in Table 18-1. The [input file] optional option is a SQL Server Express filename (.mdf), a SQL Server CE filename (.sdf), or a DBML filename (.dbml). You can provide the database filename and obtain the entities mapped on the database’s structure. More interesting is the DBML file option. This file is easy to read and modify, so you can change its content and obtain a part of the whole database’s structure (just as with Visual Studio’s LINQ to SQL Designer) or easily re-create entities after the database has changed its structure. For example, if the database administrator (DBA) changes a table name or adds a new column to a table, you can modify the DBML file manually and execute the SQLMetal tool to re-create the entities.

SQLMetal Generation Tool Options

SQLMetal Generation Tool Options

Following is a practical SQLMetal tool usage example:

SQLMetal /server:.SQL2K8 /database:AdventureWorks /pluralize /code: AdventureWorks.cs

In this example, the tool connects to an instance of a SQL Server 2008 database called SQL2K8. Then it examines the structure of the AdventureWorks database and generates the entities. They will be written in the AdventureWorks.cs file with C# language code.

Here’s another example:

SQLMetal /conn:"server=.sql2k8; database=AdventureWorks;Integrated Security=
SSPI" /dbml:AdventureWorks.dbml

In this case, SQLMetal uses a connection string to connect to the database. Then it analyzes the AdventureWorks database and generates a DBML file.

Finally, here’s an example that specifies options to generate code that manages stored procedures, functions, and views other than entities:

SQLMetal /server:.SQL2K8 /database:AdventureWorks /user:sa /password:admin /code:AdventureWorks.cs /sprocs /views /functions

Using the Visual Studio LINQ to SQL Classes Designer

The LINQ to SQL Classes Designer, included in Visual Studio 2008, is a visual designer that allows you to drag and drop a table from Server Explorer onto the designer. In this way, you can generate entity code automatically. The designer generates DBML files.

The designer is able to recognize when tables have relations. Figure shows the designer with a couple of tables from the AdventureWorks database. The designer has recognized one relationship, indicated by the arrow, and created the necessary classes.

Visual Studio LINQ to SQL Classes Designer with some AdventureWorks tables

Visual Studio LINQ to SQL Classes Designer with some AdventureWorks tables

To add a LINQ to SQL Classes file to your project, select Project ➤New Item to open the Add New Item dialog box. In this dialog box, select the LINQ to SQL Classes template, as shown in Figure.

Adding a new LINQ to SQL Classes file

Adding a new LINQ to SQL Classes file

Now that you’re familiar with the tools, it’s time to analyze the code that they generate.

Analyzing the Generated Code

After you’ve used either SQLMetal or LINQ to SQL Classes Designer to create entities, you can use the classes to manage the database. But first you should understand what has been generated by the tool that you’re using.

If you have used SQLMetal with the /code option, just open the file with your preferred text editor. To read the code generated by the Visual Studio LINQ to SQL Classes Designer, you need to expand the DBML file node in Solution Explorer, as shown in Figure, and take a look at the source code within the designer.cs file.

The code generated by the LINQ to SQL Classes Designer is contained in the designer.cs file.

The code generated by the LINQ to SQL Classes Designer is contained in the designer.cs file.

In our example, the first important instruction in the code is the AdventureWorksDataContext class definition. This class derives from the DataContext class.

[System.Data.Linq.Mapping.DatabaseAttribute(Name="AdventureWorks")] public partial class AdventureWorksDataContext : System.Data.Linq.DataContext

The DataContext class represents the database. The LINQ to SQL framework uses it to connect to the database and to manage it (as detailed in the “The DataContext Class” section later in this chapter). The DatabaseAttribute attribute specifies the database’s name. Specifying the database’s name is useful in two cases: the LINQ to SQL framework uses the name when you ask to create a database with the CreateDatabase method of the DataContext class, and the framework uses the name when you connect to the database without specifying the database in the connection string.

Next in the generated code, you’ll see that the LINQ to SQL framework classes have been added to the System.Data.Linq namespace:

public System.Data.Linq.Table<Customer> Customers
{
get
{
return this.GetTable<Customer>();
}
}
public System.Data.Linq.Table<CustomerAddress> CustomerAddresses
{
get
{
return this.GetTable<CustomerAddress>();
}
}

This snippet of code defines a property for each entity within the class (in this case, two properties). The GetTable method provided by the DataContext class returns an instance of the entity. So, it’s time to look at how an entity is defined in the class:

[Table(Name="Sales.Customer")]
public partial class Customer : INotifyPropertyChanging, INotifyPropertyChanged

Every entity in the class has a Table attribute with an optional Name property used to indicate the name of the table as specified in the database. You can omit Name when the partial class’s name is equal to the table’s name.

The LINQ to SQL framework has a monitoring feature that allows it to catch changes to your entities after having filled them with data retrieved from the database. This feature is provided with the help of the entity class and its implementation of both the INotifyPropertyChanging and INotifyPropertyChanged interfaces. The methods defined by these interfaces enable the framework to inform you that a property is both going to change and has changed, respectively.

Next in the generated code, you can find the declaration of a private field for each column defined in the table. The Customer entity code has these declarations:

private int _CustomerID;
private System.Nullable<int> _TerritoryID;
private string _AccountNumber;
private char _CustomerType;
private System.Guid _rowguid;
private System.DateTime _ModifiedDate;
private EntitySet<CustomerAddress> _CustomerAddresses;
And the CustomerAddress entity code has these declarations:
private int _CustomerID;
private int _AddressID;
private int _AddressTypeID;
private System.Guid _rowguid;
private System.DateTime _ModifiedDate;
private EntityRef<Customer> _Customer;

Every field has the related column’s datatype, and a nullable datatype is used when the column defined in the database can contain a null value. Much more interesting are the EntitySet and EntityRef classes. They are provided by the LINQ to SQL framework to manage relationships between entities and data consistency. The EntitySet class contains items from the many part of the one-tomany relationship type between the entities. In this case, a customer can have more than one address specified in the CustomerAddress table, so an EntitySet<CustomerAddress> field is defined to store related records. The EntityRef class represents the one part of the one-to-many relationship type between the entities. So, if you examine the code of the CustomerAddresses entity, you will see the EntityRef<Customer> field. This is correct, because a customer address belongs to only one customer.

Continuing with the generated code, you’ll next see this:

[Column(Storage="_CustomerID", AutoSync=AutoSync.OnInsert,
DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true,
IsDbGenerated=true)]
public int CustomerID
{
get
{
return this._CustomerID;
}
set
{
if ((this._CustomerID != value))
{
this.OnCustomerIDChanging(value);
this.SendPropertyChanging();
this._CustomerID = value;
this.SendPropertyChanged("CustomerID");
this.OnCustomerIDChanged();
}
}
}

Every private field has a public property that allows you to set and retrieve its value. The propertyis decorated with attributes that allow the LINQ to SQL framework to manage the field. Just as with theDatabaseAttribute attribute, these values are useful to create the database with the CreateDatabasemethod and to manage the entity. Table shows the attributes to decorate a field. The interesting part of the preceding code snippet resides in the set accessor. The SendPropertyChanging and SendPropertyChanged methods are the implementations of the INotifyPropertyChanging and INotifyPropertyChanged interfaces, respectively. They inform the LINQ to SQL framework that the property is changing by calling the PropertyChanging method. They also inform the framework that the property has changed by calling the PropertyChanged method. Moreover, the OnCustomerIDChanging and OnCustomerIDChanged methods are partial methods. Partial methods are a new C# 3.0 feature that allows you to define partial classes in which to insert implementations of those methods. If you do not eventually provide implementations for partial methods, the C# compiler will exclude them from the compilation. The PropertyChanging and PropertyChanged methods use the n[PropertyName]Changingand On[PropertyName]Changed format. That format is created for each property in the entity class.

Column Attribute Properties

Column Attribute Properties
The last snippet of generated code that deserves an explanation is where the entity manages the EntitySet and EntityRef fields. Here’s that bit of code from our example:

[Association(Name="Customer_CustomerAddress", Storage="_CustomerAddresses",
OtherKey="CustomerID")]
public EntitySet<CustomerAddress> CustomerAddresses
{
get
{
return this._CustomerAddresses;
}
set
{
this._CustomerAddresses.Assign(value);
}
}

Every property participating in the relationship is decorated with the Association attribute. This attribute owns properties useful to the LINQ to SQL framework to understand the name and the type of the relationship (of course, they are useful to create the relashionship in the database with the CreateDatabase method, too). Table lists the Association attribute properties that you can use to specify the relationship between two entities.

Association Attribute Properties

Association Attribute Properties
A one-to-many relationship has been defined between the Customer and CustomerAddress entities. So two Association attributes are specified: one in the Customer entity and one in the CustomerAddress entity. The preceding code is stored in the Customer entity that has the collection of addresses related to a customer. So, the Customer entity represents the one side of the relationship where you need to indicate which property represents the foreign key in the other entity. You indicate a foreign key by setting the OtherKey property with the name of the child entity’s property. In the set accessor, the CustomerAddress object is added to the collection of the Customer’s addresses by the Assign method. A way to relate a Customer object with a CustomerAddress object is using the CustomerAdresses property, as shown in the following code snippet:

CustomerAddress ca = new CustomerAddress();
Customer c = new Customer();
c.CustomerAddresses.Add(ca);

When the Add method is called, a call to the set accessor within the Customer entity is automatically done, and the Assign method is called.

In the following code snippet, you can see the code within the CustomerAddress entity, the one representing the many side of the relationship:

[Association(Name="Customer_CustomerAddress", Storage="_Customer",
ThisKey="CustomerID", IsForeignKey=true)]
public Customer Customer
{
get
{
return this._Customer.Entity;
}
set
{
Customer previousValue = this._Customer.Entity;
if (((previousValue != value)
|| (this._Customer.HasLoadedOrAssignedValue == false)))
{
this.SendPropertyChanging();
if ((previousValue != null))
{
this._Customer.Entity = null;
previousValue.CustomerAddresses.Remove(this);
}
this._Customer.Entity = value;
if ((value != null))
{
value.CustomerAddresses.Add(this);
this._CustomerID = value.CustomerID;
}
else
{
this._CustomerID = default(int);
}
this.SendPropertyChanged("Customer");
}
}
}

The second Association attribute is defined in the CustomerAddress entity. Here, the Customer property uses the IsForeignKey property of the Association attribute set to true to indicate that the Customer property is the foreign key. Finally, the ThisKey property indicates that the CustomerID is the name of the foreign key.

In the preceding snippet, the set accessor contains very important code because it manages the consistency of the entity object’s data. The Assign method is one way to relate a Customer object to a CustomerAddress object. But using the CustomerAddresses property is not the only way to relate those objects. You can start creating or retrieving with a query to a CustomerAddress object and then use its Customer property to relate them. Using this approach, you call the code within the set accessor of the Customer property. The following code snippet demonstrates this concept:

AdventureWorksDataContext db = new AdventureWorksDataContext();
CustomerAddress ca = (from addr in db.CustomerAddresses
select addr).First();
Customer c = new Customer();
ca.Customer = c;

The set accessor of the Customer property is responsible for managing the data and preventing it from becoming inconsistent. From the preceding code snippet, you can see that a CustomerAddress object is retrieved by a query. This object already contains a relationship with a Customer record, but in the code, you are going to change it because you are assigning the new Customer object to the CustomerAddress object with its Customer property. The set accessor must avoid data inconsistency, so it needs to break the relationship with the old Customer and then create the new one.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics