Introduction
The SqlMetal tool is useful to generate C# or VB.NET objects mapped on SQL Server database. It provides classes with members and properties mapped on table and view columns. It reflects the relationships between tables. It provides also a class derived from base class DataContext which maps, on request, functions and stored procedures, making difference between table functions and scalar functions and between stored procedures which returns a rowset and those who perform only operations such as insert, update and delete. Another feature is the creation of the XML map used to store separately database objects definitions.
The application DLinqEntityGenerator generates objects in a similar way as SqlMetal tool does, but it is built as an add-in on VS.NET and have more options like generating output files for each database object and creating a C# or VB.NET project attached to the current solution. It is not a tool to replace SqlMetal, but a wizard-style option to generate DLinq classes.
The idea started from the previous article DACBuilder – Data Access objects generation tool based on XML and XSL templates transformation, which have used XSL transformations to generate simple objects mapped on different data sources. It is much easier to use XML and XSL transformations, because in case of modifications and later features you only have to modify the template without changing the code and recompiling applications. Another reason is that you can customize the templates, using XSL parameters (using xsl:param nodes).
Metadata
Database objects metadata definitions are provided using INFORMATION_SCHEMA views and FOR XML feature available on SQL Server. The objects called are:
EXCLUDE_DB_OBJECTS – a table containing the database entities which are not subject to map; vwGetTables – a view for tables; vwGetViews – a view for views; vwGetProcedures – a view for procedures; vwGetFunctions – a view for functions; GetTableColumnsXML – a stored procedure for metadata information about the columns in a table (either base table or view); GetRoutineParametersXML – a stored procedure for metadata information about the parameters of a routine (stored procedure or function); GetFunctionColumnsXML – a stored procedure for metadata information about the columns returned by a function; fnGetExtendedProperty – a function for extended properties of a specified column in a table; fnCreateFriendlyName – a function for creating a friendly name from a string (useful to provide valid class names and identifiers);
The vwGetTables and vwGetViews use INFORMATION_SCHEMA.TABLES view to get tables or views with a specific filter for TABLE_TYPE column and excluding corresponding objects in EXCLUDE_DB_OBJECTS table.
The vwGetProcedures and vwGetFunctions views use INFORMATION_SCHEMA.ROUTINES to get stored procedures or functions with a specific filter for ROUTINE_TYPE column and excluding corresponding objects in EXCLUDE_DB_OBJECTS table.
The GetTableColumnsXML stored procedure uses FOR XML EXPLICIT clause to store hierarchically information about a specified table, its columns and its relationships with other tables. The logic of the FOR XML EXPLICIT clause in the stored procedure could look complicated, but it is the standard technique to create nested hierarchies. The structure is similar to the GetTableColumnsXML stored procedure used by DACBuilder application, but a little more elaborated, to include more information about the referential constraints. The nesting is ensured by using TAG and PARENT aliases and by the ordering applied over the attributes which have IDREF and HIDE directives.
The GetRoutineParametersXML stored procedure reads information about a specified stored procedure or function from the INFORMATION_SCHEMA.PARAMETERS view.
The GetFunctionColumnsXML stored procedure reads information about a specified function using INFORMATION_SCHEMA.ROUTINE_COLUMNS view.
The wizard form (frmWizard) and DLinqEntitiesEventArgs class
The form used to collect information about the database objects and the entities which will map them is called frmWizard. Being a little lazy, I have used a little trick to simulate the wizard. I have added a TabControl object at design-time to create all needed controls on corresponding steps. The TabControl control has the Visible property set to false. Each step has its own TabPage object, which has a Panel control containing the corresponding TexBox, Label, PictureBox, Combobox, Button controls. When the user performs next or back action, the corresponding Panel object is dettached from its TabPage parent and added to the form.
When the user click Next or Back buttons, the NextAction or BackAction methods of the form are called. Validation clauses are provided, so the user can not advance to the next step of the wizard if the current step settings are not valid. However, the user can go back to change the settings of the previous step.
The first step is the connection information. The server name or IP address, user name and password and a database name must be provided in order to pass to the next step. If the server name, user name and password are correct, when you "drop down" the databases combo box, it will be filled with all the databases existing on the specified server.
ServerConnection cnn = new ServerConnection(txtServer.Text, txtUser.Text,
txtPassword.Text);
cnn.Connect();
Server srvr = new Server(cnn);
DatabaseCollection oColl = srvr.Databases;
The ServerConnection class is part of Microsoft.SqlServer.Management.Common namespace, while Server and DatabaseCollection classes pertain to Microsoft.SqlServer.Management.Smo namespace.
The second step is the database objects selection step. These objects are stored in a TreeView control on four separate branches: Tables, Views, Procedures, and Functions. Each node is an object instance of a class derived from the TreeNode base class, because it was necessary to store supplementary information (object type, name, friendly name and, if it is a function, the returned type) about the database object. This information is part of a structure called DBObject:
public struct DBObject
{
public DBObjectTypes ObjectType;
public string ObjectName;
public string ObjectFriendlyName;
public string RoutineDataType;
}
You can not go to the next step if at least one database object is not checked.
The third step consists in supplementary options, like the project name, the folder you want to store the resulting files, the generated code language, the XML map file and mapping option on generated classes. By default, the application creates the project in the selected language, but this could be disabled. In the case of a project creation, the selected folder must be empty. The project will be added to the current solution. If no solution is available, a new one will be created. Optionally, you can generate all the classes in a single file.
The summary after the third step recaps the information collected offering a short description for every setting.
The frmWizard form has a public event WizardFinished following the definition of the delegate:
public delegate void DLinqEntitiesEventHandler(object sender,
DLinqEntitiesEventArgs e);
...
public event DLinqEntitiesEventHandler WizardFinished;
When the wizard finishes its job, all the collected information is stored in an object of DLinqEntitiesEventArgs type. The event can consume this object created in OnWizardFinished method:
private void OnWizardFinished()
{
ArrayList dbObjects = new ArrayList();
foreach(TreeNode root in tvDBObjects.Nodes)
{
foreach(DBObjectTreeNode tn in root.Nodes)
{
if(tn.Checked)
{
dbObjects.Add(tn.DBObj);
}
}
}
string dbName = cboDatabases.SelectedIndex >= 0 ?
DBHelper.CreateFriendlyName(
cboDatabases.SelectedItem.ToString()) : "";
LanguageTypes lt = rbCSharp.Checked ? LanguageTypes.CSharp :
LanguageTypes.VisualBasic;
string projectName = chkDontCreateProject.Checked ? null :
DBHelper.CreateFriendlyName(txtProjectName.Text);
string path = txtFolder.Text;
DLinqEntitiesEventArgs e = new DLinqEntitiesEventArgs(cnnString, dbName,
dbObjects, chkUseOneOnlyFile.Checked, lt,
path, projectName, txtXMLFile.Text,
chkUseMapping.Enabled &&
chkUseMapping.Checked);
if(WizardFinished != null)
{
WizardFinished(this, e);
}
}
The DLinqEntitiesEventArgs class has the following properties:
ConnectionString – built using the connection information in the first step; DBName – selected database name; DBObjects – an ArrayList containing only the selected database objects; UseOneOnlyFile – boolean property specifying if the generated classes will be outputted in only one file or for each object a file will be generated; LanguageType – LanguageTypes enum property (two values, CSharp and VisualBasic), specifying generated code language; Path – the full directory name where the output will be saved; ProjectName – the name of the project; CreateProject – boolean property, returns true if the ProjectName is not null and not empty, otherwise false; ProjectExtension – depending on the FileExtension – depending on the GenerateXML – boolean property, returns true if the XML map file name is not null and not empty, otherwise false; XmlMapFileName – the full name of the XML map file; UseMapping – boolean property, specifies whether to generate classes using mapping source or not (classes which don't use mapping include database object metadata in their definitions, as custom attributes).
The DBHelper class
The DBHelper class provides methods and properties for:
- connecting to a SQL Server:
OpenConnection, CloseConnection, IsConnectionOpen; - calling metadata views and stored procedures:
GetTables, GetViews, GetProcedures, GetFunctions, GetTableColumns, GetRoutineParameters, GetFunctionColumns, GetProcedureType; - transforming XML documents or readers using XSL stylesheets (either as a file on the disk, either as a XML document in memory):
Transform (with three overloading definitions); - other utilities:
SetXslDocumentParameters (allow customizing a XSL document, using XSL parameters), CreateFriendlyName (static method useful to generate a valid class name or identifiers).
All methods except Transform and GetProcedureType don't pose problems in understanding. Most of them are simple wrappers for the views and procedures used to gather metadata information.
The Transform(XmlReader xmlRdr, XmlDocument xslTemplateDoc) method uses the new XslCompiledTransform class for .NET Framework 2.0:
public string Transform(XmlReader xmlRdr, XmlDocument xslTemplateDoc)
{
string strXML = string.Empty;
try
{
XmlNamespaceManager nsmgr
= new XmlNamespaceManager(xslTemplateDoc.NameTable);
nsmgr.AddNamespace("xsl", "http://www.w3.org/1999/XSL/Transform");
XmlUrlResolver resolver = new XmlUrlResolver();
XslCompiledTransform trans = new XslCompiledTransform();
trans.Load(xslTemplateDoc, null, resolver);
TextWriter writer = new StringWriter();
XmlTextWriter xmlWriter = new XmlTextWriter(writer);
xmlWriter.Formatting = Formatting.Indented;
trans.Transform(xmlRdr, xmlWriter);
strXML = writer.ToString();
}
catch(Exception ex)
{
strXML = "<errors>";
while(ex != null)
{
strXML += "<error>";
strXML += ex.Message;
strXML += "</error>";
ex = ex.InnerException;
}
strXML += "</errors>";
}
return strXML;
}
The GetProcedureType(string procedureName, XmlDocument parametersDoc, string xslExecuteProcTemplatePath, out XmlDocument rowsetMetaDataDoc, out string strErrors) method is useful to make difference between stored procedures which returns rowsets and those who returns a value as a result of an operation of insert, update, delete, etc. Because I haven't found a way provided by SQL Server to retrieve rowset metadata for a stored procedure, I used ExecuteReader method of a SqlCommand object with a parameter set on CommandBehavior.SchemaOnly. A problem was the SQL string to execute, because I had to give default values for the parameters. This can be an issue, because a value of NULL given to a parameter can change the behavior and the return type of a stored procedure. Also, when I have generated DLinq entities using the SqlMetal tool, I have seen that some schema errors occurred for procedures which were OK from T-SQL perspective. I would be grateful if someone has helpful information about how to retrieve rowset information and want to share it.
First, the GetProcedureType method generates a valid SQL statement for EXECUTE procedures giving default values for the parameters (the parametersDoc parameter is a XML document created using GetRoutineParametersXML stored procedure): -1 for numeric types, 0 for bit, NULL for all other types. The result of the ExecuteReader method is a SqlDataReader object. I assumed that if the FieldCount property of this object is greater than 0, the procedure returns a rowset, else it returns a value or nothing:
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
if(rdr.FieldCount > 0)
{
...
return ProcedureTypes.Rowset;
}
else
{
...
return ProcedureTypes.ReturnValue;
}
If the procedure returns a rowset, metadata information is found in the schema table obtained using GetSchemaTable() method of the SqlDataReader object. Navigating through the columns collection of the GetFunctionsColumnsXML is created.
If some SQL errors occur, the return type is a UnableToHandleSQL and if other unknown errors occur, the return type is UnableToHandleOther, as it is defined in ProcedureTypes enum:
public enum ProcedureTypes
{
UnableToHandleSQL = 0
, UnableToHandleOther = 1
, Rowset = 2
, ReturnValue = 3
}
Add-In Connect class
The Connect class is the main class defined to attach the add-in instance to VS.NET. The method which helps us to execute our actions is called Exec and has the following definition:
public void Exec(string commandName, vsCommandExecOption executeOption,
ref object varIn, ref object varOut, ref bool handled)
This method instantiates an object of frmWizard type and attach a handler for the WizardFinished event:
frmWizard frm = new frmWizard();
frm.WizardFinished += new DLinqEntitiesEventHandler(frm_WizardFinished);
DialogResult dr = frm.ShowDialog();
The handler frm_WizardFinished generates the DLinq classes using the settings collected by the wizard and stored in DLinqEntitiesEventArgs e parameter. It navigates through the DBObjects collection and depending on the ObjectType property, it performs some action. To access VS.NET objects, the Add-In application uses _applicationObject object of type DTE2.
private DTE2 _applicationObject;
If the project creation is selected, it uses specific methods to create the project in the selected language and to add references to System.Data.DLinq and System.Query assemblies. To reflect current status of the code generation, it uses the VS.NET status bar for text and progress of the operations:
if(e.CreateProject)
{
prjPath = e.Path;
prjTemplatePath = sln.GetProjectTemplate("ClassLibrary.zip",
e.LanguageType.ToString());
sln.AddFromTemplate(prjTemplatePath, prjPath, e.ProjectName, false);
prj = sln.Projects.Item(sln.Projects.Count);
ProjectItem item = sln.FindProjectItem(prjPath + "\\Class1." +
e.FileExtension);
item.Remove();
itemTemplatePath = sln.GetProjectItemTemplate("Class.zip",
e.LanguageType.ToString());
VSProject thisPrj = (VSProject)prj.Object;
thisPrj.References.Add("System.Data.DLinq");
thisPrj.References.Add("System.Query");
_applicationObject.StatusBar.Progress(true, "Creating project " +
e.ProjectName + "." + e.ProjectExtension + "...",
2, 100);
}
For each database object, depending on the settings, the application get the structure of the database object, performs the code generation, if it must be saved in its file, it is added to a StreamWriter object, and, if the project creation is selected, the item will be added to the previously created project. For example, for tables and views, the code is:
xmlDoc = dbH.GetTableColumns(dbObj.ObjectName);
if(e.UseOneOnlyFile)
{
sCode += dbH.Transform(xmlDoc, xslDoc);
}
else
{
sCode = dbH.Transform(xmlDoc, xslDoc);
sw = new StreamWriter(e.Path + @"\" + dbObj.ObjectFriendlyName + "." +
e.FileExtension);
sw.Write(sCode);
sw.Close();
if(e.CreateProject)
{
prj = sln.Projects.Item(sln.Projects.Count);
prj.ProjectItems.AddFromFile(e.Path + @"\" + dbObj.ObjectFriendlyName
+ "." + e.FileExtension);
}
}
After all selected database objects are transformed, the application generates the main class derived from DataContext, containing the definitions for tables (including the views), stored procedures and functions.
If the user asks the XML map creation, a XML file with database objects definitions is created at specified location. Also, if the user asks that the generated classes to use mapping, the custom attributes containing the database objects definitions will not be added to the classes.
The XSL stylesheets and DLinq classes
The table and views classes, as TABLE functions and rowset stored procedures classes, are mapped using table_CSharp.xsl and table_VisualBasic.xsl stylesheets. They contain specific language syntax and templates for generating class definition, constructors, private members and public properties mapped on the columns, private members and public properties for relationships (EntityRef and EntitySet objects), and events reflecting the change in the columns values.
The main class derived from DataContext is yielded using data_context_CSharp.xsl and data_context_VisualBasic.xsl. It is the class mapped on the selected database, having constructors and public properties mapped on Table objects, StoredProcedureResult for rowset stored procedures, on int data type for stored procedures which return values, on a return type for scalar functions, and on IQueryable objects for TABLE functions.
All the stylesheets include the stylesheet called type.xsl, used to map SQL types to C# or VB.NET primitive types and to include default values for these languages.
The execute_procedure.xsl stylesheet is used to generate a valid T-SQL statement for execute stored procedures with default values for parameters, useful to distinguish between rowset stored procedures and stored procedures which return values.
The map.xsl is used to obtain the XML map file which contain the database objects metadata definitions.
The output is very similar to the one created by the SqlMetal tool, except EntityRef and EntitySet properties. The classes defintions are like this:
[Table(Name="Customers")]
public partial class Customer
{
private int xCustomerID;
[Column(Storage="xCustomerID", DBType="int NOT NULL IDENTITY",
Id=true, AutoGen=true)]
public CustomerID
{
get
{
return xCustomerID;
}
set
{
if(this.xCustomerID != value)
{
this.OnPropertyChanging("CustomerID");
this.xCustomerID = value;
this.OnPropertyChanged("CusotmerID");
}
}
}
}
The EntitySet class is used in the parent class to have the "set" of the children entities.
[Table(Name="Customers")]
public class Customer
{
[Column(Id=true)]
public string CustomerID;
...
private EntitySet<Order> _Orders;
[Association(Storage="_Orders", OtherKey="CustomerID")]
public EntitySet<Order> Orders
{
get { return this._Orders; }
set { this._Orders.Assign(value); }
}
}
The child entity must have a reference to the parent, using the EntityRef class.
[Table(Name="Orders")]
public class Order
{
[Column]
public string CustomerID;
private EntityRef<Customer> _Customer;
[Association(Storage="_Customer", ThisKey="CustomerID")]
public Customer Customer
{
get { return this._Customer.Entity; }
set { this._Customer.Entity = value; }
}
}
The class derived from DataContext (main database class) looks like this:
public partial class Northwind : DataContext
{
public Table<Customer> Customers;
public Table<Order> Orders;
public Northwind(string connection): base(connection) {}
[StoredProcedure(Name="CustOrderHist")]
public StoredProcedureResult<CustOrderHistResult>
CustOrderHist([Parameter(Name="CustomerID")] string customerID)
{
return this.ExecuteStoredProcedure<CustOrderHistResult>
(((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID);
}
[StoredProcedure(Name="InsertRegion")]
public int InsertRegion([Parameter(Name="RegionID", DBType="int")]
System.Nullable<int /> RegionID,
[Parameter(Name="RegionDescription",
DBType="nchar(50)")] string RegionDescription)
{
StoredProcedureResult result =
this.ExecuteStoredProcedure(((MethodInfo)
(MethodInfo.GetCurrentMethod())), RegionID,
RegionDescription);
return result.ReturnValue.Value;
}
[Function(Name="[dbo].[ProductsCostingMoreThan]")]
public IQueryable<ProductsCostingMoreThanResult>
ProductsCostingMoreThan(System.Nullable<decimal> cost)
{
MethodCallExpression mc = Expression.Call(
((MethodInfo)(MethodInfo.GetCurrentMethod())),
Expression.Constant(this),
new Expression[] {
Expression.Constant(cost, typeof(System.Nullable<decimal>))
}
);
return this.CreateQuery<ProductsCostingMoreThanResult>(mc);
}
[Function(Name="[dbo].[fnGetCustomers]")]
public IQueryable<FnGetCustomersRowset> FnGetCustomers()
{
MethodCallExpression mc = Expression.Call(((MethodInfo)
(MethodInfo.GetCurrentMethod())),
Expression.Constant(this), null);
return this.CreateQuery<FnGetCustomersRowsetRowset>(mc);
}
}
A simple usage of the generated classes, as the LINQ documentation shows:
NorthwindDataContext db
= new NorthwindDataContext("c:\\northwind\\northwnd.mdf");
var q =
from c in db.Customers
where c.City == "London"
select c;
foreach (var cust in q)
Console.WriteLine("id = {0}, City = {1}",cust.CustomerID, cust.City);
Using the application
First of all, you must run the scripts for tables, views, functions and stored procedures used to select metadata information. The database you want to be mapped over the DLinq entities must have all these objects created (available in the scripts folder).
To make DLinqEntityGenerator add-in application available to VS.NET, the assembly and the .AddIn file must be copied in one of the directories specified in "Add-in File Paths" option (Tools - > Options -> Add-In/Macros Security). Also, the "Allow Add-in components to load" option must be checked. The Templates folder containing the XSL stylesheets used to generate classes must be copied in the selected directory. If you encounter difficulties running the DLinqEntityGenerator Add-in, copy in the same directory the additional assemblies provided with DlinqEntityGenerator.dll file.
After the files(s) copy process, when you start VS.NET, in the Tools menu you will find at the beginning a new command called "DLinqEntityGenerator". The DLinqEntityGenerator application should be available now as a new tool in VS.NET environment.
