CoNatural Components v1.5 Released to CodePlex

Roger Torres - March 5th, 2010

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:

  1. 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.)
    2. 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>
  2. Open Visual Studio 2008 and create a new C# console application. Name it “AdventureWorks2008Console”.
  3. 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).

    4. Start Table Import Wizard

  4. Point the wizard to your AdventureWorks database in step 1 of 5.
  5. Hit the Refresh button and select all the tables in step 2 of 5.

    6. Step 2

  6. 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.

    7. Step 3

  7. 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).

    8. Step 4

  8. 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.
  9. 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:

    10. Project

  10. 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.
    11. 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.

    s6

    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!

« New CoNatural Components 1.4 Released to CodePlex
CoNatural Components v1.6 »

Leave a comment