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
- Tdo home situated: http://tdo.sourceforge.net (documentation and quickstart).
- Tdo sourgeforge situated: http://sourceforge.net/projects/tdo (download, news, forum, mailing list).