TDO - Typed Data Object 2.0


Tdo - Typed Data Object is a set made up of a base classes library and a .NET 2.0 code generator for Data Layers based on Ms Sql Server 7/2000/MSDE/2005/EXPRESS databases and the .NET Framework 2.0.

What is Tdo ?

Tdo is the acronym of Typed Data Object and is made up of a NET assembly (Tdo.dll) and a source code generator (TdoCodeGenerator.exe).

The source code generator produces a collection of derived classes (VB.NET/C#.NET) that extends the base classes within the Tdo.dll assembly, all under the Object Oriented model.

Every generated class has the scope "to represent" an object of your SQL database, where object are Tables, Views, Stored Procedures, Functions. According to this model, every table or view, etc..., is considered as an object itself that shows its attributes and operations (that are properties and methods).

That means that the single cell of a table or the single parameter of a stored procedure will be seen as object itself. All these classes are enclosed in a hierarchically macro-object as a representation of the entire database - the TdoHelper class.

More ahead we'll see all the possible operations that we can execute on our database (select, insert, update, delete, stored procedure, etc...) that will be called from the present methods within the TdoHelper class objects.

Tdo Is Open Source

From this version (2.0), Tdo has become an open source project and both the source code and the setup package are downloadable from sourceforge.net at the following web address: http://tdo.sourceforge.net.

The setup package installs both the base library Tdo.dll and the source code generator TdoCodeGenerator.exe.

To be able to use it it's recommended to have already installed both MS Visual Studio .NET 2005 and Sql Server 2005 (or EXPRESS/2000/7/MSDE).

Production of the source code and the Typed DataSet  

The first operation to do is certainly to install Tdo and then generate the necessary source code (it's needed to have already installed the .NET Framework 2.0).

Once it's installed, we can launch TdoCodeGenerator - Figure 1 - and from the project menu above on the left, we can add a new Tdo Project to the default solution Tdo Solution 1 - Figure 2.

After this new project has been added, you will be asked to include all the information about the connection to the Sql database: first of all the name of the Tdo project, which language the source code will be generated in, the selection mode of the objects (all or custom selection), the directory where the code will be saved and at last if a file has to be created for every generated class or just a single file that will contain all the classes. This window message will ask you a .NET namespace where the classes will be declared and a web root namespace to use for all XML serialization operations (web service that returns Tdo objects).

Once the project is created, it's possible to select the tables, views, stored procedures and Tdo functions, for which generate a relative class wrapper, if you have

chosen the Selection only mode, otherwise everything will be automatically included in the creation. On the left bottom there is a navigation menu between several objects in a MS Outlook style (Tables, Views, Stored Procedures, Table-valued Functions, Scalar-valued Functions), in the center up you can see a list of the available objects in the database and at the bottom a list of the selected objects for the production.

It's now sufficient to save your Tdo solution and your Tdo project that you just created and then click on Generate button - Figure 3. The source code generation window will be opened and it will show the progress state of the operation - Figure 4 - and if the database is integral, that is for example views that focalize on non-existent tables, the source code will be written in the specified output folder.

In the example showed in this figure, a unique C#.NET file has been generated with all the database Northwind tables, views, stored procedures and functions. The file has been saved in the C:\ root directory and it's called TdoNorthwind.cs.

 

Moreover on the computer where we are testing (a pentium IV 3.40 GHz with 2 Gb of RAM), the process of production of source code has demanded only 0,6 sec. for a total of 420Kb generated source and 8.835 lines of code, therefore it takes a very short time (less than a second).

In the same folder you will find the XML DataSet typed schema for the entire Northwind database TdoNorthwindDataSet.xsd with tables, views,stored procedures and relations.

Tips & Tricks:

  • During the software development and mainly at the beginning, the database will be often changed and this will require a new process of Tdo code production (for example if you add a column in a table or if a new view is created). My suggestion is to add the Tdo project file (that one just saved with the .tdoprj extension) directly to the Visual Studio .NET solution so that it can be regenerated on demand from IDE environment just double clicking (associate, only the first time, the .tdoprj extension to the TdoCodeGenerator.exe executable).
  • After you've created the project in Visual Studio .NET , it's suggested to change the Output folder among the Tdo project properties so that every time the source code will be overwritten (VS.NET will notice that the file has been modified from the outside and will ask you to recharge it).

Compile the generated code 

Once the code has been generated you will create a new .NET (C# or VB) Class Library project that will be the Data Layer for your test on Northwind database and will be called TdoNorthwind.

You will add to this project the TdoNorthwind.cs source file just generated, first you need to eliminate the Class1.cs file added automatically by VS.NET, and at the end add the TdoNorthwindDataSet.xsd. file too. On this last file you will do a double click so to generate a wrapping code for the typed dataset. As we are saying before , the source code that Tdo has generated is a set of derived and typed classes that extend a set of base classes existing in the Tdo.dll assembly, therefore another thing to do is to add the reference to this assembly that you will see directly in the .NET References window. If you don't see it there, you will find it in the Tdo installation directory: C:\Program Files\Tdo - Typed Data Object.

Now you just have to compile. The TdoNorthwind.dll assembly is ready to be referenced in your Presentation Layer (an ASP.NET application or a Windows Smart Client).

Tdo Object Model

If you want to see the source code generated by Tdo you will see that the structure is very simple and smart.

A class exists for every table, view, stored procedure and function. For example, if you take in exam the Employees table, that, in the source code is associated with the EmployeesTable class.

The Figure 5 shows the classes diagram and the objects hierarchy to which the class belongs to:

 

System.Object --> Tdo.Common.TdoEntityBase --> Tdo.Common.Tables.TdoTableBase --> Tdo.Northwind.Entities.EmployeesTable, where the Tdo.Common.TdoEntityBase class implements the Tdo.Common.ITdoEntity interface and the Tdo.Common.Tables.TdoTableBase class implements the Tdo.Common.Tables.ITdoTabl interface.

Figures 6 and 7 show respectively the classes diagrams of Alphabetical List Of Products view and of Cust Order Hist stored procedure.

All these classes are re-exposed as properties of TdoHelper class ... the class that represents the entire database.

Figure 8 shows its diagram and Figure 9 shows the organization of Tdo.dll. assembly namespaces.

Now we are going to see how the TdoHelper class works in concrete.

As you can see in Figure 8, the TdoHelper class properties that expose tables have a "t" (table) for prefix. In the same way the properties that expose views, stored procedures and function , have respectively a "v", "p", "f" as prefix.

This simple technique helps you to find as quickly as possible, while writing code, the objects on which you want to operate, thanks to the VS.NET intellisense - Figures 10 and 11.

 

 

Moreover, every single class, that exposes a table (it's the same with views, stored procedures and functions), has all the fields of the same table, exposed as properties of the Tdo.Common.TdoTypes.TdoTypeBase.type. This base type has the work to formalize all the column properties (type, dimension, nullability, etc...) and obviously the field value through the Value property, that will be specified according to the real type (int, char, binary, etc...) thanks to the .NET 2.0 generics - Figures 12 and 13.

 

Tips & Tricks:

  • The generated classes are all extensible through inheritance and are all CLS-compliant, serializable and marked with the partial prefix. This allows you to "complete" the Tdo generated classes with their custom methods/properties/events in a .cs/.vb file, different from that ones generated by Tdo (if you re-generate the source code, because of db schema changes, you could lose your implementations).

The TdoHelper class.

Now, we are going to do some examples about using the TdoHelper class, generated for the Northwind database.

We take a look to the Northwind database, always present just like a tradition, in the SQL 2005 Express version too, and then we suppose to have already generated all the classes using TdoCodeGenerator.

We have already said that all the operations begin from the TdoHelper class and therefore we start writing the instance of this class.

Print 1:

C#

//Automatic connection string building

TdoHelper tdo = new TdoHelper ("(local)", "Northwind", "sqlusername", "sqlpassword");

VB.NET

' Automatic connection string building

Dim tdo As New TdoHelper("(local)", "Northwind", "sqlusername", "sqlpassword")

The only information you should supply the class constructor is the Connection String. In this case you are saying Tdo to use the default Sql Server instance on your machine and to access the northwind database through Sql-authentication. The TdoHelper class is supplied with 11 constructors that allow to specify all the possible way of connection, among them: windows-authentication, asynchronous processing, connection pooling, failover partner, etc ... .

Here, in Print 2, some other ways useful to specify the logon information:

Print 2:

C #

//default constructor - assign connection string properties

TdoHelper tdo1 = new TdoHelper ();

tdo1.ConnectionString = "data source=(local);Initial Catalog=Northwind;Integrated Security=SSPI" 

//Windows Authentication

TdoHelper tdo2 = new TdoHelper ("(local)", "Northwind");  

//Windows Authentication with asynchronous processing enabled.

TdoHelper tdoàsync = new TdoHelper ("(local)", "Northwind", true);  

//Sql Authentication

TdoHelper tdo3 = new TdoHelper ("(local)", "Northwind", "sqlusername", "sqlpassword");  

//Sql Authentication with asynchronous processing enabled.

TdoHelper tdoásync = new TdoHelper ("(local)", "Northwind", "sqlusername", "sqlpassword", true);  

//Several parameters

TdoHelper tdo4 = new TdoHelper (

"SqlServerName", //sql serveur name

"DataBaseName", //database name

"sqlusername", //sql username

"sqlpassword", //sql password

30, //connection life Time

true, //connection reset

false, //enlist

50, //max pool size

1, //min pool size

true, //pooling

true, //asynchronous processing
"PartnerSqlServerName"); //sql backup serveur name

VB.NET

' default constructor - assign connection string properties

Dim tdo1 As New TdoHelper()

tdo1.ConnectionString = "data source=(local);Iniial Catalog=Northwind;Intgrated Security=SSPI"  

' Windows Authentication

Dim tdo2 As New TdoHelper("(local)", "Northwind" 

' Windows Authentication with asynchronous processing enabled.

Dim tdo2sync As New TdoHelper("(local)", "Northwind", True 

' Sql Authentication

Dim tdo3 As New TdoHelper("(local)", "Northwind", "sqlusername", "sqlpassword" 

' Sql Authentication with asynchronous processing enabled.

Dim tdo3sync As New TdoHelper("(local)", "Northwind", "sqlusername", "sqlpassword", True 

' Several parameters

Dim tdo4 As New TdoHelper("SqlServerName", "DataBaseName", "sqlusername", "sqlpassword", _

30, True, False, 50, 1, True, True, "PartnerSqlServerName")

The TdoHelper class is supplied with a set of properties that controls its activities during run-time, for example the ability to open and close the connection automatically or to enclose all the CRUD operations automatically in a single sql transaction.

Print 3:

C #

//before Automatic connection open any operation

/ and close automatically after execution

tdo.AutomaticOpenCloseConnection = true; //default is true  

//Automatic BEGIN TRANSACTION on every CRUD operation (and SPs)

//... and commit after execution

tdo.AutomaticTransaction = false 

//Command timeout
tdo.CommandTimeOut = 60; //default is 60  

VB.NET

' before Automatic connection open any operation

'... and close automatically after execution

tdo.AutomaticOpenCloseConnection = True ' default is true  

' Automatic BEGIN TRANSACTION on every CRUD operation (and SPs)

'... and commit after execution

tdo.AutomaticTransaction = False  

' Command timeout
tdo.CommandTimeOut = 60 ' default is 60

SELECT operations

Now, we are going to see how to use Tdo with only one line of code to execute select operations.

There are different ways to execute such operations according to the result-set type we want; here we use the SelectDataTable method that returns an ADO.NET 2.0 System.Data.DataTable object ( between comment the corresponding sql-statement).

Print 4:

C #

//SELECT * FROM dbo.Employees
DataTable dtEmployees = tdo.tEmployees.SelectDataTable();
 

VB.NET

' SELECT * FROM dbo.Employees

Dim dtEmployees As DataTable = tdo.tEmployees.SelectDataTable()

As we examine the SelectDataTable method, used without input parameters, is equal to a

select * from Employees.

Obviously it's possible, in the same way, to specify which fields can be included in the result-set - Print 5 ...

Print 5:

C #

//SELECT EmployeeId, LastName, FirstName FROM dbo.Employees

DataTable dtEmployees = tdo.tEmployees.SelectDataTable

(

tdo.tEmployees.Employeeid,

tdo.tEmployees.Lastname,

tdo.tEmployees.Firstname
);  

VB.NET

' SELECT EmployeeId, LastName, FirstName FROM dbo.Employees

Dim dtEmployees As DataTable = tdo.tEmployees.SelectDataTable(_

tdo.tEmployees.Employeeid, _

tdo.tEmployees.Lastname, _
tdo.tEmployees.Firstname)  

... and eventual Where/Order By/Group by clauses ...

Print 6:

C #

//SELECT EmployeeId, LastName, FirstName

//FROM dbo.Employees

//WHERE LastName=' Davolio' AND FirstName=' Nancy'

DataTable dtEmployees = tdo.tEmployees.SelectDataTable

(

Clause Where(tdo.tEmployees.Lastname == "Davolio" & tdo.tEmployees.Firstname == "Nancy"),

tdo.tEmployees.Employeeid,

tdo.tEmployees.Lastname,

tdo.tEmployees.Firstname
);

VB.NET

' SELECT EmployeeId, LastName, FirstName

' FROM(dbo.Employees)

' WHERE LastName=' Davolio' AND FirstName=' Nancy'

Dim dtEmployees As DataTable = tdo.tEmployees.SelectDataTable(_

Clause.Where(tdo.tEmployees.Lastname = "Davolio" And tdo.tEmployees.Firstname = "Nancy"), _

tdo.tEmployees.Employeeid, _

tdo.tEmployees.Lastname, _
tdo.tEmployees.Firstname)  

... still all in one line of code. 

Tdo Sql Expression DOM  

We're seeing some more complex expressions and how it's possible to write them using the operators overloading method introduced by the Tdo Sql Expression DOM model.

(Tdo.Common.TdoSqlExpressionDOM). 

Print 7:

C #

//SELECT EmployeeId, LastName, FirstName

//FROM dbo.Employees

//WHERE EmployeeId>=5

//OR

//(TitleOfCourtesy LIKE ' Ms.' AND LastName' Davolio ')

//AND

//(REGION IS NOT NULL)

DataTable dtEmployees = tdo.tEmployees.SelectDataTable

(

Clause Where

(

tdo.tEmployees.Employeeid>=5

|

(tdo.tEmployees.Titleofcourtesy % "Ms." & tdo.tEmployees.Lastname! = "Davolio")

&

(tdo.tEmployees.Region! = DBNull Value)

),

tdo.tEmployees.Employeeid,

tdo.tEmployees.Lastname,

tdo.tEmployees.Firstname
);

VB.NET

' SELECT EmployeeId, LastName, FirstName

' FROM dbo.Employees

' WHERE EmployeeId>=5

' OR

' (TitleOfCourtesy LIKE ' Ms.' AND LastName' Davolio ')

' AND

' (REGION IS NOT NULL)

Dim dtEmployees As DataTable = tdo.tEmployees.SelectDataTable(_

Clause.Where (_

tdo.tEmployees.Employeeid > = 5 _

Or _

(tdo.tEmployees.Titleofcourtesy Mod "Ms." And tdo.tEmployees.Lastname < > "Davolio") _

And _

(tdo.tEmployees.Region < > DBNull.Value) _

), _

tdo.tEmployees.Employeeid, _

tdo.tEmployees.Lastname, _

tdo.tEmployees.Firstname)

In prints 8 and 9 we see the TdoWheresetofValues and TdoWhereRange classes used to build sql expressions based on IN and BETWEEN t-sql operators.

Print 8:

C #

//SELECT *

//FROM dbo.Employees

//WHERE Titleofcourtesy IN (' Mrs.', ' Mr. ', ' Ms.')

DataTable dtEmployees = tdo.tEmployees.SelectDataTable

(

Clause Where

(

tdo.tEmployees.Titleofcourtesy == new TdoWheresetOfValues ("Mrs.", "Mr." , "Ms." )

)
); 

VB.NET

' SELECT *

' FROM dbo.Employees

' WHERE Titleofcourtesy IN (' Mrs.', ' Mr. ', ' Ms.')

Dim dtEmployees As DataTable = tdo.tEmployees.SelectDataTable(_

Clause.Where (_

tdo.tEmployees.Titleofcourtesy = New TdoWheresetOfValues("Mrs." , "Mr." , "Ms." )))  

 

Print 9:

C#

//SELECT *

//FROM dbo.Employees

//WHERE (BirthDate BETWEEN ' 1950-01-01 ' AND ' 1960-12-31 ')

DataTable dtEmployees = tdo.tEmployees.SelectDataTable

(

Clause Where

(

tdo.tEmployees.Birthdate == new TdoWhereRange

(

new DateTime (1950.1.1), new DateTime (1960.12.31)

)

)
); 

VB.NET

' SELECT *

' FROM dbo.Employees

' WHERE (BirthDate BETWEEN ' 1950-01-01 ' AND ' 1960-12-31 ')

Dim dtEmployees As DataTable = tdo.tEmployees.SelectDataTable(_

Clause.Where (_

tdo.tEmployees.Birthdate = New TdoWhereRange (_

New DateTime(1950, 1, 1), New DateTime(1960, 12, 31))))  

In prints 10 and 11 we see an example of Order by and Group By :

Print 10:

C #

//SELECT *

//FROM dbo.Employees

//ORDER BY TitleOfCourtesy DESC, LastName, FirstName

DataTable dtEmployees = tdo.tEmployees.SelectDataTable

(

Clause OrderBy

(

tdo.tEmployees.Titleofcourtesy, OrderByOperator Desc,

tdo.tEmployees.Lastname, OrderByOperator Asc,

tdo.tEmployees.Firstname, OrderByOperator Asc

)
); 

VB.NET

' SELECT *

' FROM dbo.Employees

' ORDER BY TitleOfCourtesy DESC, LastName, FirstName

Dim dtEmployees As DataTable = tdo.tEmployees.SelectDataTable(_

Clause.OrderBy(_

tdo.tEmployees.Titleofcourtesy, OrderByOperator.Asc, _

tdo.tEmployees.Lastname, OrderByOperator.Asc, _

tdo.tEmployees.Firstname, OrderByOperator.Asc))  

Print 11:

C #

//SELECT CategoryID AS [ Category ID ], COUNT(ProductID) AS Total

//FROM Products

//GROUP BY CategoryID

DataTable dtProductsCount = tdo.tProducts.SelectDataTable

(

Clause GroupBy(tdo.tProducts.Categoryid),

tdo.tProducts.Categoryid.Alias("Category ID"),

Functions Count< TdoInt32 > (tdo.tProducts.Productid, "Total")
);
 

VB.NET

' SELECT CategoryID AS [ Category ID ], COUNT(ProductID) AS Total

' FROM Products

' GROUP BY CategoryID

Dim dtProductsCount As DataTable = tdo.tProducts.SelectDataTable _

(_

Clause.GroupBy(tdo.tProducts.Categoryid), _

tdo.tProducts.Categoryid.Alias("Category ID"), _

Functions.Count(Of TdoInt32)(tdo.tProducts.Productid, "Total") _
 

Beyond the SelectDataTable method there are other methods such as SelectDataReader, SelectScalar, SelectIdentity, SelectXmlReader that return respectively System.Data.SqlDataReader, System.Object, System.Int32, System.Xml.XmlReader, objects, all with several overloads as for the SelectDataTable method.

Print 12:

C #

//SELECT LastName, FirstName FROM Employees

//WHERE LastName = ' Davolio'

SqlDataReader dataReader = tdo.tEmployees.SelectDataReader(

Clause Where(tdo.tEmployees.Lastname == "Davolio"),

CommandBehavior SingleRow,

tdo.tEmployees.Lastname, tdo.tEmployees.Firstname); 

dataReader.Close(); //must be closed before another select operation  

//SELECT Birthdate FROM Employees

//WHERE LastName = ' Davolio'

DateTime birthDate = (DateTime)tdo.tEmployees.SelectScalar(

Clause Where(tdo.tEmployees.Lastname == "Davolio"),

tdo.tEmployees.Birthdate);  

//SELECT EmployeeId FROM Employees

//WHERE LastName = ' Davolio'

int davolioEmpId = tdo.tEmployees.SelectIdentity(

Clause Where(tdo.tEmployees.Lastname == "Davolio")).Value;  

//SELECT LastName, FirstName FROM Employees

//WHERE LastName = ' Davolio' FOR XML CAR, ELEMENTS

XmlReader xmlEmployees = tdo.tEmployees.SelectXmlReader(

Clause Where(tdo.tEmployees.Lastname == "Davolio"),

"FOR XML CAR, ELEMENTS",
tdo.tEmployees.Lastname, tdo.tEmployees.Firstname); 

VB.NET

' SELECT LastName, FirstName FROM Employees

' WHERE LastName = ' Davolio'

Dim dataReader As SqlDataReader = tdo.tEmployees.SelectDataReader(_

Clause.Where(tdo.tEmployees.Lastname = "Davolio"), _

CommandBehavior.SingleRow, _

tdo.tEmployees.Lastname, tdo.tEmployees.Firstname)
dataReader.Close() ' must be closed before another select operation  

' SELECT Birthdate FROM Employees

' WHERE LastName = ' Davolio'

Dim birthDate As DateTime = DirectCast (tdo.tEmployees.SelectScalar(_

Clause.Where(tdo.tEmployees.Lastname = "Davolio"), _

tdo.tEmployees.Birthdate), DateTime)  

' SELECT EmployeeId FROM Employees

' WHERE LastName = ' Davolio'

Dim davolioEmpId As Integer = tdo.tEmployees.SelectIdentity(_

Clause.Where(tdo.tEmployees.Lastname = "Davolio")).Value  

' SELECT LastName, FirstName FROM Employees

' WHERE LastName = ' Davolio' FOR XML CAR, ELEMENTS

Dim xmlEmployees As XmlReader = tdo.tEmployees.SelectXmlReader(_

Clause.Where(tdo.tEmployees.Lastname = "Davolio"), _

"FOR XML CAR, ELEMENTS", _

tdo.tEmployees.Lastname, tdo.tEmployees.Firstname) 

Picture 1: List of the .NET operators in overloading for T-SQL expressions in according to the Tdo Sql Expression DOM model.
 

T-sql C#.NET VB.NET
= == =
< > ! = < >
> > >
>= >= >=
< < <
<= <= <=
NOT ! NOT
AND & AND
OR | OR
LIKE % Mod
NOT LIKE - -

INSERT, UPDATE, DELETE operations and support to the Transactions

INSERT, UPDATE and DELETE operations on tables are very simple and little expensive in terms of lines of code. We take attention for the Employees table and its relative Tdo.Northwind.Entities.EmployeesTable Tdo class. This class is mapped with all the table fields such as Employeeid, Lastname, Birthdate, Photo, etc ..., re-exposed with other Tdo types and respectively as TdoInt32, TdoString, TdoDateTime, TdoBinary.

Inside every type, in addition to several informations that specify the field type (AllowDBNull, AutoIncrement, MaxLenght, etc...), we can find the Value property; for the above-mentioned types the property will be respectively of SqlInt32, SqlString, SqlDateTime, SqlBinary type that is, Tdo is using existing structure in the ADO.NET 2.0 System.Data.SqlTypes namespace to expose the values of each field.

At the end we do not forget that the Tdo.Northwind.Entities.EmployeesTable class is exposed as property of the TdoHelper class with the tEmployees name ("t" as prefix cause it's a table). Considering that we can see how to execute insert, update and delete operations on the employees table in prints 13, 14, 15 e 16. 

Print 13:

C #

//INSERT INTO Employees

//(FirstName, LastName, Birthdate)

//VALUES

//(' Andrea', ' Ferendeles', ' Jun 23 1973 12:00:00:000AM ')  

//first way... using properties

EmployeesTable emp=tdo.tEmployees; //emp is just to tEmployees reference  

emp.BeginEdit();

emp.Firstname.Value = "Andrea";

emp.Lastname.Value = "Ferendeles";

emp.Birthdate.Value = new DateTime (1973, 6, 23, 12, 00, 00);

emp.EndEdit(); //or emp.CancelEdit() to undo  

//all other fields to are NULL

int affectedRecords = emp.Insert(); //I give INSERT  

//[ SQL Profiler Trace ]: /

//exec sp_executesql N' insert into dbo.Employees (LastName, FirstName, BirthDate)

//values (@LastName, @FirstName, @BirthDate);SELECT SCOPE_IDENTITY() ',

//N' @LastName nvarchar(10), @FirstName nvarchar(6), @BirthDate datetime ',

//@LastName = N' Ferendeles ', @FirstName = N' Andrea ',

//@BirthDate = ' Jun 23 1973 12:00:00:000AM'  
int insertedEmpId = emp.Employeeid.Value.Value; //SCOPE_IDENTITY() result

VB.NET

' INSERT INTO Employees

' (FirstName, LastName, Birthdate)

' VALUES

' (' Andrea', ' Ferendeles', ' Jun 23 1973 12:00:00:000AM ')  

' first way... using properties

Dim emp As EmployeesTable = tdo.tEmployees ' emp is just to tEmployees reference  

emp.BeginEdit()

emp.Firstname.Value = "Andrea"

emp.Lastname.Value = "Ferendeles"

emp.Birthdate.Value = New DateTime(1973, 6, 23, 12, 0, 0)

emp.EndEdit() ' or emp.CancelEdit() to undo  

' all other fields to are NULL

Dim affectedRecords As Integer = emp.Insert() ' I give INSERT  

' [ SQL Profiler Trace ]: '

' exec sp_executesql N' insert into dbo.Employees (LastName, FirstName, BirthDate)

' values (@LastName, @FirstName, @BirthDate)SELECT SCOPE_IDENTITY() ',

' N' @LastName nvarchar(10), @FirstName nvarchar(6), @BirthDate datetime ',

' @LastName = N' Ferendeles ', @FirstName = N' Andrea ',

' @BirthDate = ' Jun 23 1973 12:00:00:000AM'  

Dim insertedEmpId As Integer = emp.Employeeid.Value.Value ' SCOPE_IDENTITY() result 

Print 14:

C #

//INSERT INTO Employees

//(FirstName, LastName, Birthdate)

//VALUES

//(' Andrea', ' Ferendeles', ' Jun 23 1973 12:00:00:000AM ')  

//second way... using Insert method with all parameters

SqlString ns = SqlString Null; //ns is just to SqlString.Null reference

int affectedRecords = tdo.tEmployees.Insert (

"Ferendeles", "Andrea", ns, ns, new DateTime (1973, 6, 23, 12, 00, 00),

SqlDateTime Null, ns, ns, ns, ns, ns, ns, ns, SqlBinary Null, ns, SqlInt32 Null, ns

); //all in one  

//[ SQL Profiler Trace ]:

/

//exec sp_executesql N' insert into dbo.Employees

//(LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City,

//Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath)

//values

//(@LastName, @FirstName, @Title, @TitleOfCourtesy, @BirthDate, @HireDate, @Address, @City,

//@Region, @PostalCode, @Country, @HomePhone, @Extension, @Photo, @Notes, @ReportsTo,

//@PhotoPath);SELECT SCOPE_IDENTITY() ',

//N' @LastName nvarchar(10), @FirstName nvarchar(6), @Title nvarchar(4000),

//@TitleOfCourtesy nvarchar(4000), @BirthDate datetime, @HireDate datetime,

//@Address nvarchar(4000), @City nvarchar(4000), @Region nvarchar(4000),

//@PostalCode nvarchar(4000), @Country nvarchar(4000), @HomePhone nvarchar(4000),

//@Extension nvarchar(4000), @Photo varbinary(8000), @Notes nvarchar(4000),

//@ReportsTo int, @PhotoPath nvarchar(4000) ',

//@LastName = N' Ferendeles ', @FirstName = N' Andrea ', @Title = NULL,

//@TitleOfCourtesy = NULL, @BirthDate = ' Jun 23 1973 12:00:00:000PM ',

//@HIREDATE = NULL, @ADDRESS = NULL, @CITY = NULL, @REGION = NULL, @POSTALCODE = NULL,

//@COUNTRY = NULL, @HOMEPHONE = NULL, @EXTENSION = NULL, @PHOTO = NULL, @NOTES = NULL,

//@REPORTSTO = NULL, @PHOTOPATH = NULL 
int insertedEmpId = tdo.tEmployees.Employeeid.Value.Value; //SCOPE_IDENTITY() result  

VB.NET

' INSERT INTO Employees

' (FirstName, LastName, Birthdate)

' VALUES

' (' Andrea', ' Ferendeles', ' Jun 23 1973 12:00:00:000AM ')  

' second way... using Insert method with all parameters

Dim ns As SqlString = SqlString.Null ' ns is just to SqlString.Null reference

Dim affectedRecords As Integer = tdo.tEmployees.Insert _

(_

"Ferendeles", "Andrea", ns, ns, New DateTime(1973, 6, 23, 12, 0, 0), _

SqlDateTime.Null, ns, ns, ns, ns, ns, ns, ns, SqlBinary.Null, ns, SqlInt32.Null, ns _

) ' all in one  

' [ SQL Profiler Trace ]:

'

' exec sp_executesql N' insert into dbo.Employees

' (LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City,

' Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath)

' values

' (@LastName, @FirstName, @Title, @TitleOfCourtesy, @BirthDate, @HireDate, @Address, @City,

' @Region, @PostalCode, @Country, @HomePhone, @Extension, @Photo, @Notes, @ReportsTo,

' @PhotoPath)SELECT SCOPE_IDENTITY() ',

' N' @LastName nvarchar(10), @FirstName nvarchar(6), @Title nvarchar(4000),

' @TitleOfCourtesy nvarchar(4000), @BirthDate datetime, @HireDate datetime,

' @Address nvarchar(4000), @City nvarchar(4000), @Region nvarchar(4000),

' @PostalCode nvarchar(4000), @Country nvarchar(4000), @HomePhone nvarchar(4000),

' @Extension nvarchar(4000), @Photo varbinary(8000), @Notes nvarchar(4000),

' @ReportsTo int, @PhotoPath nvarchar(4000) ',

' @LastName = N' Ferendeles ', @FirstName = N' Andrea ', @Title = NULL,

' @TitleOfCourtesy = NULL, @BirthDate = ' Jun 23 1973 12:00:00:000PM ',

' @HIREDATE = NULL, @ADDRESS = NULL, @CITY = NULL, @REGION = NULL, @POSTALCODE = NULL,

' @COUNTRY = NULL, @HOMEPHONE = NULL, @EXTENSION = NULL, @PHOTO = NULL, @NOTES = NULL,

' @REPORTSTO = NULL, @PHOTOPATH = NULL  

Dim insertedEmpId As Integer = tdo.tEmployees.Employeeid.Value.Value ' SCOPE_IDENTITY() result 

Print 15 shows an example of Tdo transaction. All the enclosed objects in the TdoHelper class, in fact share the same db connection and participate automatically to the same transaction (if demanded). To execute a set of commands during transaction (it's possible to specify the isolation level) it's sufficient to enclose all the Tdo operations between two calls to TdoHelper.BeginTransaction and TdoHelper.CommitTransaction (or TdoHelper.RollBackTransaction) methods.

Print 15:

C #

//SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

//BEGIN TRANSACTION

//UPDATE Employees

//SET Lastname = ' Ferendeles mod ', Firstname = ' Andrea mod'

//WHERE Lastname = ' Ferendeles'

//COMMIT TRANSACTION

tdo.OpenConnection();

tdo.BeginTransaction(IsolationLevel ReadUncommitted);

tdo.tEmployees.Lastname.Value = "Ferendeles mod";

tdo.tEmployees.Firstname.Value = "Andrea mod";

int affectedRecords =

tdo.tEmployees.Update(Clause Where(tdo.tEmployees.Lastname == "Ferendeles"));

tdo.CommitTransaction();

tdo.CloseConnection();  

//[ SQL Profiler Trace ]:

/

//exec sp_executesql N' update dbo.Employees

//SET LastName=@LastName, FirstName=@FirstName

//WHERE (Employees.LastName = @_p14705386011) ',

//N' @_p14705386011 nvarchar(10), @LastName nvarchar(14), @FirstName nvarchar(10) ',

//@_p14705386011 = N' Ferendeles ',
//@LastName = N' Ferendeles mod ', @FirstName = N' Andrea mod'  

VB.NET

' SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

' BEGIN TRANSACTION

' UPDATE Employees

' SET Lastname = ' Ferendeles mod ', Firstname = ' Andrea mod'

' WHERE Lastname = ' Ferendeles'

' COMMIT TRANSACTION

tdo.OpenConnection()

tdo.BeginTransaction(IsolationLevel.ReadUncommitted)

tdo.tEmployees.Lastname.Value = "Ferendeles mod"

tdo.tEmployees.Firstname.Value = "Andrea mod"

Dim affectedRecords As Integer = _

tdo.tEmployees.Update(Clause.Where(tdo.tEmployees.Lastname = "Ferendeles"))

tdo.CommitTransaction()

tdo.CloseConnection()

 

' [ SQL Profiler Trace ]:

'

' exec sp_executesql N' update dbo.Employees

' SET LastName=@LastName, FirstName=@FirstName

' WHERE (Employees.LastName = @_p14705386011) ',

' N' @_p14705386011 nvarchar(10), @LastName nvarchar(14), @FirstName nvarchar(10) ',

' @_p14705386011 = N' Ferendeles ',

' @LastName = N' Ferendeles mod ', @FirstName = N' Andrea mod'

 

Print 16:

C #

//DELETE FROM Employees

//WHERE Lastname LIKE ' %Ferendeles% '

int affectedRecords =

tdo.tEmployees.Delete(Clause Where(tdo.tEmployees.Lastname % "%Ferendeles%"));  

//[ SQL Profiler Trace ]:

/

//exec sp_executesql N' delete from dbo.Employees

//WHERE (Employees.LastName LIKE @_p631522191) ', N' @_p631522191 nvarchar(12) ',
//@_p631522191 = N' %Ferendeles% '  

VB.NET

' DELETE FROM Employees

' WHERE Lastname LIKE ' %Ferendeles% '

Dim affectedRecords As Integer = _

tdo.tEmployees.Delete(Clause.Where(tdo.tEmployees.Lastname Mod "%Ferendeles%"))

 

' [ SQL Profiler Trace ]:

'

' exec sp_executesql N' delete from dbo.Employees

' WHERE (Employees.LastName LIKE @_p631522191) ', N' @_p631522191 nvarchar(12) ',

' @_p631522191 = N' %Ferendeles% ' 

Asynchronous Commands

ADO.NET 2.0 has been equipped for executing commands in asynchronous way, especially taking advantage of the BeginXXX/EndXXX methods of the SqlCommand object. At the same way Tdo supplies all the operations with equivalent BeginXXX/EndXXX couple of methods. In Print 17 we see an example of INSERT operation in asynchronous way.

Print 17:

C #

public void example_017()

{

tdo = new TdoHelper ("(local)", "Northwind", true); //asynchronousProcessing = true

tdo.OpenConnection();

object myobjectstate = null; //loads custom object

tdo.tEmployees.Firstname.Value = "async name";

tdo.tEmployees.Lastname.Value = "async lastname";

IAsyncResult result=tdo.tEmployees.BeginInsert(new AsyncCallback (this InsertDoneCallBack), myobjectstate);

//or...

//tdo.tEmployees.BeginInsert(); //or one-way async execution

//or...

//IAsyncResult result = tdo.tEmployees.BeginInsert(); //or

//I give something else...

//and then...

//tdo.tEmployees.EndInsert(result); //... wait for completition

tdo.CloseConnection();

}

 

public void InsertDoneCallBack(IAsyncResult result)

{

if (result.IsCompleted)

{

object myobjectstate = result.AsyncState;

//I give something

}
} 

VB.NET

Public Sub example_017()
tdo = New TdoHelper("(local)", "Northwind", True) ' asynchronousProcessing = true

tdo.OpenConnection()

Dim myobjectstate As Object = Nothing ' loads custom object

tdo.tEmployees.Firstname.Value = "async name"

tdo.tEmployees.Lastname.Value = "async lastname"

Dim result As IAsyncResult = tdo.tEmployees.BeginInsert(New AsyncCallback(AddressOf MyClass InsertDoneCallBack), myobjectstate)

' or...

' tdo.tEmployees.BeginInsert() ' or one-way async execution

' or...

' Dim result As IAsyncResult = tdo.tEmployees.BeginInsert() ' or

' I give something else...

' and then...

' tdo.tEmployees.EndInsert(result) '... wait for completition

tdo.CloseConnection()

End Sub

 

Public Sub InsertDoneCallBack(ByVal result As IAsyncResult)

If result.IsCompleted Then

Dim myobjectstate As Object = result.AsyncState

' I give something

End If

End Sub 

Stored Procedure and User-Function

Similar to CRUD operations, Stored Procedures are represented by Tdo through derived classes of the Tdo.Common.Programmability.TdoStoredProcedureBase base class and exposed as properties of the TdoHelper class.

We take into consideration the CustOrderHist stored procedure, defined with an input parameter @CustomerID of nchar(5) type and none output parameter.

In Print 18 the stored procedure is invoked passing the CustomerId parameter to the FillDataTable method in one line of code.

Print 18:

C #

//CREATED CustOrderHist PROCEDURES @CustomerID nchar(5)

//AS

//SELECT ProductName, Total=SUM(Quantity)

//FROM Products P, [ Order Details ] OR, Orders Or, Customers C

//WHERE C.CustomerID = @CustomerID

//AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

//GROUP BY ProductName 
DataTable dtCustomerHistory = this tdo.pCustorderhist.FillDataTable("ALFKI");

VB.NET

' CREATED CustOrderHist PROCEDURES @CustomerID nchar(5)

' AS

' SELECT ProductName, Total=SUM(Quantity)

' FROM Products P, [ Order Details ] OR, Orders Or, Customers C

' WHERE C.CustomerID = @CustomerID

' AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

' GROUP BY ProductName  

Dim dtCustomerHistory As DataTable = MyClass tdo.pCustorderhist.FillDataTable("ALFKI")

Print 19 shows the same call, this time passing single values of each parameter and then returning the eventual values of output parameters.

Print 19:

C #

this tdo.pCustorderhist.pCustomerid = "ALFKI";

SqlParameterCollection outputParameters;

SqlDataReader custReader = this tdo.pCustorderhist.ExecuteReader(CommandBehavior SingleResult, out outputParameters);

//I give something with custReader  

custReader.Close(); //close before retrieve output parameters

this tdo.pCustorderhist.AssignOutputParameterValues(outputParameters);
//object somevalue = this.tdo.pCustorderhist.pSomeOutputParameter.Value;

VB.NET

MyClass tdo.pCustorderhist.pCustomerid = "ALFKI"

Dim outputParameters As SqlParameterCollection = Nothing

Dim custReader As SqlDataReader = MyClass tdo.pCustorderhist.ExecuteReader(CommandBehavior.SingleResult, outputParameters)

' I give something with custReader  

custReader.Close() ' close before retrieve output parameters

MyClass tdo.pCustorderhist.AssignOutputParameterValues(outputParameters)

' object somevalue = this.tdo.pCustorderhist.pSomeOutputParameter.Value 

Print 20 shows the asynchronous call to the same stored procedure and an example of how to use the TdoHelper class in a using construct (TdoHelper is System.IDisposable). 

Unfortunately in Northwind database there isn't a user-function, and therefore I suggest you to create your own user-function, then generate the source code with TdoCodeGenerator and at last perform the tests.

Print 20:

C #

using (TdoHelper tdo = new TdoHelper ("(local)", "Northwind", true))

{

tdo.pCustorderhist.AssignParameterValues("ALFKI"); //assign all in one row

SqlParameterCollection outputParameters;

IAsyncResult asyncResult = tdo.pCustorderhist.BeginExecuteReader(

null, null, out outputParameters);

//I give something else

SqlDataReader custReader = tdo.pCustorderhist.EndExecuteReader(asyncResult);

custReader.Read();

//I give something with results

custReader.Close(); //close reader before retrieve output parameters

tdo.pCustorderhist.AssignOutputParameterValues(outputParameters);

int retValue = tdo.pCustorderhist.pReturnValue.Value;
} 

VB.NET

Using tdo As New TdoHelper("(local)", "Northwind", True)

tdo.pCustorderhist.AssignParameterValues("ALFKI") ' assign all in one row

Dim outputParameters As SqlParameterCollection = Nothing

Dim asyncResult As IAsyncResult = tdo.pCustorderhist.BeginExecuteReader(_

Nothing, Nothing, outputParameters)

' I give something else

Dim custReader As SqlDataReader = tdo.pCustorderhist.EndExecuteReader(asyncResult)

custReader.Read()

' I give something with results

custReader.Close() ' close reader before retrieve output parameters tdo.pCustorderhist.AssignOutputParameterValues(outputParameters)

Dim retValue As Integer = tdo.pCustorderhist.pReturnValue.Value

End Using 

Tdo and Typed DataSet

In addition to generate all the source code needed to work with database, TdoCodeGenerator produces an ADO.NET 2.0 typed DataSet (System.Data.DataSet - Figure 14), that is for, an example, data-bindings, offline modality or xml web service operations. Before using the DataSet it's necessary to do a double click (or right click - run custom tool) on the .xsd file from the inside of Visual Studio .NET. Such operation allows IDE to generate correctly the code-behind (typed classes) from the XSD DataSet schema. From this moment the DataSet will be visible as a component for the consumer applications and dragging on the designer.

Obviously Tdo supplies all the necessary support to work with typed DataSets providing methods for loading and manipulating to a single DataRow level.

 

In Print 21 there is an example of how to load the typed DataSet NorthwindDataSet.DataTable Orders and in Print 22 we see how it's possible to update a row already modified in the dataset with Tdo; at the end in Print 23 how to insert into the dataset a row already inserted by Tdo in the database. 

Print 21:

C #

//Created Typed DataSet instance or drag & drop on your component designer

NorthwindDataSet nwDs = new NorthwindDataSet ();  

//Fill with tdo methods

nwDs.Orders.Merge(tdo.tOrders.SelectDataTable());

nwDs.__Order_Details_.Merge(tdo.tOrderDetails.SelectDataTable()); 

//Use
this dataGridView1.DataSource = nwDs.Orders;  

VB.NET

' Created Typed DataSet instance or drag & drop on your component designer

Dim nwDs As New NorthwindDataSet()  

' Fill with tdo methods

nwDs.Orders.Merge(tdo.tOrders.SelectDataTable())

nwDs.Order_Details.Merge(tdo.tOrderDetails.SelectDataTable())  

' Use

MyClass DataGridView1.DataSource = nwDs.Orders

 

Print 22:

C #

//Popolate Typed DataSet table rows

this northwindDataSet1.Orders.Merge(this tdo.tOrders.SelectDataTable(Clause Where(tdo.tOrders.Customerid == "ALFKI")));  

//Fill tOrders properties with typed row values.

this tdo.tOrders.LoadFromDataRow(this northwindDataSet1.Orders[0 ]);  

//update with Tdo

this tdo.tOrders.BeginEdit();

this tdo.tOrders.Shippeddate.Value = DateTime Now;

this tdo.tOrders.EndEdit();
this tdo.tOrders.Update(Clause Where(tdo.tOrders.Orderid == tdo.tOrders.Orderid.Value.Value));  

VB.NET

' Popolate Typed DataSet table rows

MyClass NorthwindDataSet1.Orders.Merge(MyClass tdo.tOrders.SelectDataTable(Clause.Where(tdo.tOrders.Customerid = "ALFKI")))  

'Fill tOrders properties with typed row values.

MyClass tdo.tOrders.LoadFromDataRow(MyClass NorthwindDataSet1.Orders(0))  

' update with Tdo

MyClass tdo.tOrders.BeginEdit()

MyClass tdo.tOrders.Shippeddate.Value = DateTime.Now

MyClass tdo.tOrders.EndEdit()

MyClass tdo.tOrders.Update(Clause.Where(tdo.tOrders.Orderid = tdo.tOrders.Orderid.Value.Value))

 

Print 23:

C #

//Retrieve EmpId for Nancy Davolio

tdo.tEmployees.SelectFirstRow(

Clause Where(

tdo.tEmployees.Lastname == "Davolio" & tdo.tEmployees.Firstname == "Nancy"));

//Retrieve Shipper Id for Federal Shipping

tdo.tShippers.SelectFirstRow(Clause Where(tdo.tShippers.Companyname == "Federal Shipping"));

//Insert new Order with Tdo

tdo.tOrders.BeginEdit();

tdo.tOrders.Customerid.Value = "ALFKI";

tdo.tOrders.Employeeid.Value = tdo.tEmployees.Employeeid.Value;

tdo.tOrders.Orderdate.Value = DateTime Now;

tdo.tOrders.Shipvia.Value = tdo.tShippers.Shipperid.Value;

tdo.tOrders.EndEdit();

tdo.tOrders.Insert();  

//Created to new Typed DataRow

DataRow drNewOrder = tdo.tOrders.FillDataRow(this northwindDataSet1.Orders);  

//Add drNewOrder to Orders DataTable

this northwindDataSet1.EnforceConstraints = false;
this northwindDataSet1.Orders.Rows.Add(drNewOrder);  

VB.NET

' Retrieve EmpId for Nancy Davolio

tdo.tEmployees.SelectFirstRow(_

Clause.Where(_

tdo.tEmployees.Lastname = "Davolio" And tdo.tEmployees.Firstname = "Nancy"))

' Retrieve Shipper Id for Federal Shipping

tdo.tShippers.SelectFirstRow(Clause.Where(tdo.tShippers.Companyname = "Federal Shipping"))  

' Insert New Order with Tdo

tdo.tOrders.BeginEdit()

tdo.tOrders.Customerid.Value = "ALFKI"

tdo.tOrders.Employeeid.Value = tdo.tEmployees.Employeeid.Value

tdo.tOrders.Orderdate.Value = DateTime.Now

tdo.tOrders.Shipvia.Value = tdo.tShippers.Shipperid.Value

tdo.tOrders.EndEdit()

tdo.tOrders.Insert()  

' Created to New Typed DataRow

Dim drNewOrder As DataRow = tdo.tOrders.FillDataRow(MyClass NorthwindDataSet1.Orders)  

' Add drNewOrder to Orders DataTable

MyClass NorthwindDataSet1.EnforceConstraints = False

MyClass NorthwindDataSet1.Orders.Rows.Add(drNewOrder)

TdoHelper ... Select Join, SqlDependecy, SqlNotificationRequest, Properties & Events

The TdoHelper class is provided by a set of methods for generic operations or that ones not specific for a single database object ( a single table/view/SP/function).

We take into consideration an INNER JOIN operation between two tables: the Orders and the Order Details tables.

According to Object Oriented logic this operation is at database level and therefore exposed through a TdoHelper class method.

The Tdo idea is to push both developer and DBA to operate with Views and Stored procedures for performance and maintenance reasons (Tdo says: "create objects in the database and then regenerate the code on the fly"); said that , in TdoHelper class we find all the support for Join operations on-the-fly because it's not ever possible to operate on the db.

In Print 24 there is an example of INNER JOIN with the TdoHelper.SelectJoin method between the two tables we talked about before. 

Print 24:

C #

DataTable dtOrder_OrderDetails = tdo.SelectJoin(

new JoinHelper (Join InnerJoin, tdo.tOrders.Orderid, JoinOperator Equal, tdo.tOrderDetails.Orderid),

Clause Where(tdo.tOrders.Customerid == "ALFKI"),

tdo.tOrders.Orderid, tdo.tOrders.Customerid, tdo.tOrders.Orderdate,

tdo.tOrderDetails.Productid, tdo.tOrderDetails.Quantity, tdo.tOrderDetails.Unitprice);  

//[ SQL Profiler Trace ]:

/

//exec sp_executesql N' select Orders.OrderID as [ OrderID ], Orders.CustomerID as [ CustomerID ],

//Orders.OrderDate as [ OrderDate ], [Order Details].ProductID as [ ProductID ],

//[ Order Details].Quantity as [ Quantity ], [Order Details].UnitPrice as [ UnitPrice ]

//from Orders INNER JOIN dbo.[Order Details ] ON Orders.OrderID = [ Order Details].OrderID
//WHERE (Orders.CustomerID = @_p10513825641) ', N' @_p10513825641 nvarchar(5) ', @_p10513825641 = N' ALFKI'

VB.NET

Dim dtOrder_OrderDetails As DataTable = tdo.SelectJoin(_

New JoinHelper(Join.InnerJoin, tdo.tOrders.Orderid, JoinOperator.Equal, tdo.tOrderDetails.Orderid), _

Clause.Where(tdo.tOrders.Customerid = "ALFKI"), _

tdo.tOrders.Orderid, tdo.tOrders.Customerid, tdo.tOrders.Orderdate, _

tdo.tOrderDetails.Productid, tdo.tOrderDetails.Quantity, tdo.tOrderDetails.Unitprice)  

' [ SQL Profiler Trace ]:

'

' exec sp_executesql N' select Orders.OrderID as [ OrderID ], Orders.CustomerID as [ CustomerID ],

' Orders.OrderDate as [ OrderDate ], [Order Details].ProductID as [ ProductID ],

' [ Order Details].Quantity as [ Quantity ], [Order Details].UnitPrice as [ UnitPrice ]

' from Orders INNER JOIN dbo.[Order Details ] ON Orders.OrderID = [ Order Details].OrderID

' WHERE (Orders.CustomerID = @_p10513825641) ', N' @_p10513825641 nvarchar(5) ', @_p10513825641 = N' ALFKI' 

The Tdo.Common.TdoSqlExpressionDOM.JoinInfo class is provided by 5 constructors in order to admit JOIN operations involving not more than 6 tables/views.

Other TdoHelper class interesting methods are:

  • CreateCommand in order to create quickly SqlCommand objects , with the possibility to pass a type SqlNotificationRequest parameter
    (rif.:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/querynotification.asp).
    Print 25 shows an example of how using the CreateCommand method with the System.Data.Sql.SqlDependency object for SQL Server 2005.
  • CreateDataAdapter in order to create quickly SqlDataAdapter objects.
  • ExecuteNonQuery, ExecuteReader, ExecuteScalar, ExecuteXmlReader.
  • Fill, FillSchema.  

Print 25:

C #

public void example_025()

{

this tdo = new TdoHelper (@".\sql2005", "Northwind");

SqlDependency Start(tdo.Connection.ConnectionString);

SqlCommand empCmd = tdo.CreateCommand(

String Format("select * from {0}.{1}", tdo.tEmployees.SchemaName, tdo.tEmployees.EntityName),

CommandType Text);

SqlDependency dep = new SqlDependency (empCmd);

dep.OnChange + = new OnChangeEventHandler (dep_OnChange);

tdo.OpenConnection();

SqlDataReader dr = empCmd.ExecuteReader();

//I give something with dr

dr.Close();

}

 

public void dep_OnChange(object sender, SqlNotificationEventArgs and)

{

System.Diagnostics. Debug WriteLine(

String Format(@"Source: {0}\r\nInfo: {1}, Type: {2} ", e.Source, e.Info, e.Type));
} 

VB.NET

Dim WithEvents dep As SqlDependency

 

Public Sub example_025()

MyClass tdo = New TdoHelper("\SQL2005", "Northwind")

SqlDependency.Start(tdo.Connection.ConnectionString)

Dim empCmd As SqlCommand = tdo.CreateCommand(_

String Format("select * from {0}.{1}", tdo.tEmployees.SchemaNam and, tdo.tEmployees.EntityName), CommandType.Text)

dep = New SqlDependency(empCmd)

tdo.OpenConnection()

Dim dr As SqlDataReader = empCmd.ExecuteReader()

' I give something with dr

dr.Close()

End Sub

 

Private Sub dep_OnChange(ByVal sender As Object, ByVal and As System.Data.SqlClient.SqlNotificationEventArgs) Handles dep.OnChange  

System.Diagnostics.Debug.WriteLine( String Format("Source: {0}, Info: {1}\r\n, Type: {2} ", e.Source, e.Info, e.Type)) 
End Sub 

Here some other interesting property in order "to navigate" in objects collection of the database during run-time:

  • TdoTables
  • TdoViews
  • TdoStoredProcedures
  • TdoTableFunctions  

In Print 26 there is an example that uses the TdoHelper.TdoTables property to obtain during run-time all the names of the columns of all the db tables. 

Print 26:

C #

foreach (Tdo.Common.Entities.Tables. ITdoTable table in tdo.TdoTables)

{

System.Diagnostics. Debug WriteLine(String Format("Table: {0} ", table.EntityName));

foreach (Tdo.Common.TdoTypes. ITdoColumn column in table.TdoColumns)

{

System.Diagnostics. Debug WriteLine(String Format("\tColumn: {0} ", column.ColumnName));

}

}

//Output Window sample:

/

//Table: [ Order Details ]

//Column: OrderID

//Column: ProductID

//Column: UnitPrice

//Column: Quantity

//Column: Discount

//Table: Categories

//Column: CategoryID

//Column: CategoryName

//Column: Description

//Column: Picture
//...  

VB.NET

For Each table As Tdo.Common.Entities.Tables.ITdoTable In tdo.TdoTables

System.Diagnostics.Debug.WriteLine(String Format("Table: {0} ", table.EntityName))

For Each column As Tdo.Common.TdoTypes.ITdoColumn In table.TdoColumns

System.Diagnostics.Debug.WriteLine(String Format("Column: {0} ", column.ColumnName))

Next

Next

' Output Window sample:

'

' Table: [ Order Details ]

' Column: OrderID

' Column: ProductID

' Column: UnitPrice

' Column: Quantity

' Column: Discount

' Table: Categories

' Column: CategoryID

' Column: CategoryName

' Column: Description

' Column: Picture
'... 

We take a look to the events - Table 1 - that Tdo supports both in TdoHelper class and specific db-objects classes; in Print 27 we see an example of how to use such events to create a custom logging component for all the operations executed by Tdo:

Table 1:

Tdo Class Handler Event
TdoHelperBase StateChangeEventHandler ConnectionStateChange
EventHandler Disposed
FillErrorEventHandler FillError
SqlInfoMessageEventHandler InfoMessage
SqlRowUpdatedEventHandler RowUpdated
SqlRowUpdatingEventHandler RowUpdating
StatementCompletedEventHandler StatementCompleted
TdoEntityBase FillErrorEventHandler FillError
SqlRowUpdatedEventHandler RowUpdated
SqlRowUpdatingEventHandler RowUpdating
StatementCompletedEventHandler StatementCompleted
TdoStoredProcedureBase StatementCompletedEventHandler StatementCompleted

Print 27:

C #

public void example_027()

{

tdo.StatementCompleted + = new StatementCompletedEventHandler (tdo_StatementCompleted);

DataTable dtEmployees = tdo.tEmployees.SelectDataTable();

//tdo_StatementCompleted raised

}

 

public void tdo_StatementCompleted(object sender, StatementCompletedEventArgs and)

{

SqlCommand cmd = (SqlCommand)sender;

int rc = e.RecordCount;

//Write Log information to somewhere

System.Diagnostics. Debug WriteLine(String Format("Text: {0} - Type: {1} - RecordCount: {2} ", cmd.CommandText, cmd.CommandType, rc));
} 

VB.NET

Public Sub example_028()

Dim dtEmployees As DataTable = tdo.tEmployees.SelectDataTable()

' tdo_StatementCompleted raised

End Sub

 

Private Sub tdo_StatementCompleted(ByVal sender As Object, ByVal and As System.Data.StatementCompletedEventArgs) Handles tdo.StatementCompleted

Dim cmd As SqlCommand = DirectCast (sender, SqlCommand)

Dim rc As Integer = e.RecordCount

' Write Log information to somewhere

System.Diagnostics.Debug.WriteLine(String Format("Text: {0} - Type: {1} - RecordCount: {2} ", cmd.CommandText, cmd.CommandType, rc))

End Sub 

Conclusions  

Tdo offers many other characteristics that I didn't say here, here a short list of other important Tdo features:  

  • Tdo classes and the generate source code by TdoCodeGenerator are serializable both in bynary and XML format;
  • all classes are CLS-Compliant (in some cases the typed DataSet is exceptional);
  • everything is typed - if the db has changed , for example the name or type of a table field is changed, the new source code generated by Tdo warns about eventual differences during compile-time;
  • all T-SQL statements that Tdo generates at run-time are parametrics (reuse of Sql execution plan, no sql-injection, etc...);
  • a single language (C# or VB.NET) to write T-SQL statements and therefore where clauses, for example written in .NET (Tdo Sql Expression DOM);
  • Xml documentation: all the source code generated by Tdo is already documented through .NET Xml Documentation and the code is organized in #region, so you have source code written in a well-formed.
  • performance: Tdo is 45% faster than operations executed with the SqlDataAdapter object and still 7% faster than non parametric statements executed with SqlCommand object of ADO.NET 2.0;
  • can be used for data-bindings operations; 
  • interoperable with COM/COM+;
  • in Tdo.Common.Programmability namespace we find Functions class that re-exposes the most of Sql Server functions (Count, Max, etc...); such functions can be used for Tdo Sql Expression DOM expressions:
  • in Tdo.Common namespace there is a Utility class useful , for example, to write on hard disk an entire Tdo object in Xml format and eventually read the content in a second time (some like DataSet.WriteXml and DataSet.ReadXml).  

We are at the end of this article and we make a decisive question : "Why to use Tdo rather than ADONET 2,0 ?"  

In view that this question is absolutely personal , we can find some strong points in Tdo rather than the ADO.NET 2.0 model.

First of all, Tdo is not going to replace ADO.NET, but it uses some features of it , taking advantage of all the MS Sql Server characteristics that in Ado.NET are not implemented (at least for our specific database) because of "generality" of the code.

The most important and significant feature of Tdo is the typization of the classes (Typed data object) regarding "our" database and not a generic DB as for ADO.NET, during planning time it has to make (all is "object").

In my opinion I have always had "nightmares" to cable the name of a table or the name of a field directly into the source code or making continuously casting operations from object type to the data type that I was using and then I have seen many developers go mad with configuration database(inside the name of the tables and fields), xml file too long or developers enrolled to fun-club such like "everything with Stored Procedure" J masking themselves behind the false cause of the performances.

Just on this matter someone of you could object saying... "but there are the typed DataSet "; but they work well only for disconnected operations. In ADO.NET 2,0 there's been an attempt from Microsoft to compensate this lack (with the TableAdapters) but the gap is still there. The TableAdapters, that are the equivalent SqlDataAdapter one for every table, and the Insert/Update/Delete and Select commands which the typed dataset generates, are still too much generic and less adaptable to the different types of query that are needed at run-time. As an example the Insert method forces to pass ALL the values of the fields; and if we want to insert only some fields and the others Null ?! Or if we want to execute a select with the where clause? We would be forced, once again, to write the where clause directly in the query text.

The other great point is that Tdo "is more pushed" for connected operations, delegating to ADO.NET the disconnected ones. Here I do not know if someone of you use DataSet and DataAdapter for upgrade operations in ASP.NET applications; personally I avoid it, not for a performance issues but because we tend to use it in the old way ADO vb6 style: the user modifies a record in the form ... and we go to upgrade the database with DataSet and DataAdapter. This is called paranoia!!! Different matter in case the application is planned to work in disconnected way... but it's singular to see it one.

An other good point for Tdo is the possibility to directly write T-sql expressions in C # or VB.NET, therefore a single language and above all control on types during compilation.

Last point but not the little important is that Tdo is open source: both the library base sources (Tdo.dll) and the generator of code one (TdoCodeGenerator) are available for download on tdo.sourceforge.net and this means customization of the code and greater control.

Anyone of you is interested to participate to the continuous development of Tdo or simply to ask some more clarifications can take in contact with me.

References

  1. Tdo home situated: http://tdo.sourceforge.net (documentation and quickstart).
  2. Tdo sourgeforge situated: http://sourceforge.net/projects/tdo (download, news, forum, mailing list). 


Similar Articles