Roger Torres - March 26th, 2010
Comments
You can find the latest CoNatural Components (v1.6) Here.
This version is mainly an upgrade to the Visual Studio AddIn, incorporating many new features like asynchronous wizards and support for T4 templates.
If you are planning to use T4 to generate your CRUD commands and/or Model types from SQL tables, I’m providing basic templates, but you can modify them to fit your requirements. For example, if you use a datetime field named “ModifiedDate” in your tables to record the last time a record was modified, you can use the following T4 template:
Update.tt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
| <#@ template language="C#v3.5" HostSpecific="True" #>
<#
CrudHost host = (CrudHost)Host;
#>
//-------------------------------------------------------
// CoNatural.Data Visual Studio AddIn T4 Template Engine
//-- <#= host.Name #>.cs
//--
//-- Created by <#= Environment.UserName #>
//-- Created on <#= DateTime.Now #>
//-------------------------------------------------------
using System;
using System.Data;
using CoNatural.Data;
namespace <#= host.Namespace #> {
public class <#= host.Name #> : ICommand {
public <#= host.Name #>() {}
public string GetScript() {
return
@"UPDATE
[<#= host.Schema #>].[<#= host.Table #>]
SET
<#
int a = 0;
for(int i = 0; i < host.Columns.Length; i++) {
if (host.InPrimaryKey[i]) continue;
if (host.Columns[i] == "ModifiedDate") {
#>
<#= a++ > 0 ? ", " : " " #>[<#= host.Columns[i] #>] = getdate()
<#
}
else {
#>
<#= a++ > 0 ? ", " : " " #>[<#= host.Columns[i] #>] = @<#= host.Properties[i] #>
<#
}
}
#>
WHERE
<#
int b = 0;
for(int i = 0; i < host.Columns.Length; i++) {
if (host.InPrimaryKey[i] || host.Columns[i] == "ModifiedDate") {
#>
<#= b++ > 0 ? "AND " : " " #>[<#= host.Columns[i] #>] = @<#= host.Properties[i] #>
<#
}
}
#>
IF @@ROWCOUNT = 0
RAISERROR(N'Update failed. Check if original record was modified by another user.', 16, 1)
";
}
<#
for(int i = 0; i < host.Columns.Length; i++) {
#>
<#= host.PropertyAttributes[i] #>public <#= host.PropertyTypes[i] #> <#= host.Properties[i] #> { get; set; }
<#
}
#>
}
} |
The SQL statement will raise an error if the ModifiedDate field doesn’t match, which means someone else changed the record. This is a very basic example, but you get the idea right?
All the T4 templates must be located under the {Project Root}/T4 folder as shown below:
Project with T4 templates
Just make sure you remove the “Custom Tool” option when adding .tt template files to your project.
The AddIn provides a custom text templating host to process CoNatural templates. The templates are identified by name (Insert.tt, Select.tt, SelectAll.tt, Update.tt, Delete.tt, and Model.tt).
If you are planning to modify a template, you must cast the Host property to the type CrudHost (this is our custom host).
This type provides many properties you can use to build your logic:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
| using System;
using System.Data;
using System.Collections.Generic;
using Microsoft.SqlServer.Management.Smo;
using CoNatural.Data.VisualStudio.AddIn.Helpers;
namespace CoNatural.Data.VisualStudio.AddIn.Tools {
[Serializable]
public class CrudHost : TextTemplatingEngineHostBase {
public string Schema { get; private set; }
public string Table { get; private set; }
public int IdentityIndex { get; private set; }
public string[] Columns { get; private set; }
public string[] ColumnTypes { get; private set; }
public bool[] InPrimaryKey { get; private set; }
public string[] Properties { get; private set; }
public string[] PropertyTypes { get; private set; }
public string[] PropertyAttributes { get; private set; }
internal CrudHost(Table table, string _namespace, string name, CrudOptionsEnum commandType) {
Namespace = _namespace;
Name = name;
Schema = table.Schema;
Table = table.Name;
IdentityIndex = -1;
int colCount = table.Columns.Count;
Columns = new string[colCount];
ColumnTypes = new string[colCount];
InPrimaryKey = new bool[colCount];
Properties = new string[colCount];
PropertyTypes = new string[colCount];
PropertyAttributes = new string[colCount];
for (int i = 0; i < colCount; i++) {
Column col = table.Columns[i];
if(col.Identity)
IdentityIndex = i;
Columns[i] = col.Name;
ColumnTypes[i] = col.DataType.Name;
InPrimaryKey[i] = col.InPrimaryKey;
ParameterDirection direction = ParameterDirection.Input;
switch (commandType) {
case CrudOptionsEnum.Insert:
direction = col.Identity ? ParameterDirection.Output : ParameterDirection.Input;
break;
case CrudOptionsEnum.Select:
direction = col.InPrimaryKey ? ParameterDirection.InputOutput : ParameterDirection.Output;
break;
case CrudOptionsEnum.SelectAll:
direction = ParameterDirection.Output;
break;
case CrudOptionsEnum.Update:
direction = ParameterDirection.Input;
break;
case CrudOptionsEnum.Delete:
direction = ParameterDirection.Input;
break;
}
var ph = PropertyHelper.CreatePropertyHelper(table.Parent, col.Name, col.DataType, col.Nullable, direction);
Properties[i] = ph.Name;
PropertyTypes[i] = ph.TypeName;
PropertyAttributes[i] = ph.Attribute;
}
}
}
} |
Just a couple of final notes:
1) T4 templates are optional. If you remove some .tt files from the T4 folder (or remove the folder entirely), the AddIn will use the internal CRUD generator (released in the previous version) to generate CRUD commands/model with no .tt files.
2) The AddIn CommandBar buttons are now showing at the end of the Popup Menus (in case you missed them 
)
Enjoy.
Roger Torres - March 5th, 2010
Comments
I just released version 1.5 to CodePlex. You can find the latest binaries, source code, and release notes there.
This version supports new data types, contains new optimized type materializer/mapper classes, and improved visual studio add-in.
Let’s say you need to start a new data centric application from scratch. For simplicity, we will create a new C# Console application and connect to AdventureWorks2008 (the one that’s compatible with Sql Server 2008, including the new data types.)
Here are the steps you need to follow:
- Make sure you have the CoNatural.Data.VisualStudio.AddIn.AddIn file under your {User Profile}\Documents\Visual Studio 2008\AddIns folder. This file is a pointer to the add-in library, here is what I have in my development system (note that yours must point to the location where you saved the binaries.)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| <?xml version="1.0" encoding="UTF-16" standalone="no"?>
<Extensibility xmlns="http://schemas.microsoft.com/AutomationExtensibility">
<HostApplication>
<Name>Microsoft Visual Studio</Name>
<Version>9.0</Version>
</HostApplication>
<Addin>
<FriendlyName>CoNatural Data VisualStudio AddIn</FriendlyName>
<Description>Manage CoNatural data commands in Visual Studio 2008.</Description>
<Assembly>C:\Users\Roger\Documents\Visual Studio 2008\Projects\conatural\CoNatural Components\bin\CoNatural.Data.VisualStudio.AddIn.dll</Assembly>
<FullClassName>CoNatural.Data.VisualStudio.AddIn.Connect</FullClassName>
<LoadBehavior>1</LoadBehavior>
<CommandPreload>1</CommandPreload>
<CommandLineSafe>0</CommandLineSafe>
</Addin>
</Extensibility> |
- Open Visual Studio 2008 and create a new C# console application. Name it “AdventureWorks2008Console”.
- Create a new folder named “Commands”, and start the table import wizard (in this case we will import only “SelectAll” commands, but you can play with other options if you like).
- Point the wizard to your AdventureWorks database in step 1 of 5.
- Hit the Refresh button and select all the tables in step 2 of 5.
- In step 3 of 5, make sure “Include Schema” is checked, and “Generate .sql file” is unchecked. This is to follow the default naming conventions when generating the commands, and include the sql scripts inside the .cs class files.
- In step 4 of 5, make sure you are generating only “SelectAll” commands and the Model classes (under the /Model folder that will be created automatically).
- Complete the wizard and go to project/References to add references to CoNatural.Data (binaries folder) and Microsoft.SqlServer.Types (this should be in the gac if you have installed the sql server 2008 feature pack - with support to the new sql data types). If you don’t find the second assembly in your system, the project won’t compile, but you can always find the files with the missing references and delete them from the project.
- There is one final step. In the model you might find a couple of classes that won’t compile because a property and the type share the same name. You can rename the classes to anything you like. Here is a view of the project so far:
- Make sure the project compiles now. Then copy the following code to Program.cs. Note that you must change the connection string to fit your environment.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
| using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoNatural.Data;
using CoNatural.Data.SqlClient;
namespace AdventureWorks2008Console {
class Program {
static void Main(string[] args) {
try {
IConnection c1 = new SqlClientConnection(@"Data Source=PHENOM-II\PHENOM_II;Initial Catalog=AdventureWorks2008;Integrated Security=True");
RunTest("Reading with default connection", c1);
IConnection c2 = new ConnectionBase(@"Data Source=PHENOM-II\PHENOM_II;Initial Catalog=AdventureWorks2008;Integrated Security=True",
new DataCommandFactoryBase(new SqlClientDbProvider()),
new OptimizedTypeMaterializerFactoryBase(),
new OptimizedTypeMapperFactoryBase());
RunTest("Reading with optimized connection", c2);
}
catch (Exception ex) {
Console.WriteLine(ex.Message);
}
Console.WriteLine("DONE");
Console.ReadLine();
}
static void RunTest(string testName, IConnection c) {
DateTime startTime = DateTime.Now;
int iterations = 2;
while (iterations-- > 0) {
ReadAll<Commands.Sales.SalesOrderDetailSelectAll, Model.Sales.SalesOrderDetail>(c);
ReadAll<Commands.Production.TransactionHistorySelectAll, Model.Production.TransactionHistory>(c);
ReadAll<Commands.Production.TransactionHistoryArchiveSelectAll, Model.Production.TransactionHistoryArchive>(c);
ReadAll<Commands.Production.WorkOrderSelectAll, Model.Production.WorkOrder>(c);
ReadAll<Commands.Production.WorkOrderRoutingSelectAll, Model.Production.WorkOrderRouting>(c);
ReadAll<Commands.Sales.SalesOrderHeaderSelectAll, Model.Sales.SalesOrderHeader>(c);
ReadAll<Commands.Sales.SalesOrderHeaderSalesReasonSelectAll, Model.Sales.SalesOrderHeaderSalesReason>(c);
ReadAll<Commands.Person.BusinessEntitySelectAll, Model.Person.BusinessEntity>(c);
ReadAll<Commands.Person.PersonSelectAll, Model.Person.Person>(c);
ReadAll<Commands.Purchasing.PurchaseOrderDetailSelectAll, Model.Purchasing.PurchaseOrderDetail>(c);
}
Console.WriteLine("Test [{0}] completed in {1} seconds.", testName, DateTime.Now.Subtract(startTime).TotalSeconds);
}
static void ReadAll<TCommand, TModel>(IConnection connection) where TCommand : ICommand {
ICommand command = (ICommand)Activator.CreateInstance<TCommand>();
int rows = 0;
connection.ExecuteReader<TModel>(command).All(d => {
rows++;
return true;
});
Console.WriteLine("ReadAll returned {0} rows of {1}.", rows, typeof(TModel).Name);
}
}
} |
Here we selected 10 of the largest tables and decided to execute the “SelectAll” commands twice, using the standard type materializer and the new optimized type materializer. Here is the resulting console.
And that’s all. You have created a DAL to read from AdventureWorks to your own PONO model, it’s up to you now to play with this and find a more useful application.
I hope you enjoyed it!
