Lieven Cardoen

In the last two days I refactored a lot of Stored Procedures. Today I ran SubSonic and tried the application and I got this error that drove me crazy until 5 minutes ago:

[code]
Could not locate entry in sysdatabases for database 'sp'. No entry found with that name. Make sure that the name is entered correctly. : at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

at SubSonic.SqlDataProvider.GetDataSet[T](QueryCommand qry)

at SubSonic.DataService.GetDataSet[T](QueryCommand cmd)

at SubSonic.StoredProcedure.GetDataSet[T]()

at SubSonic.StoredProcedure.GetDataSet()

at Edu3.Business.MetaDataLogic.LoadAllLibraries() in C:\Users\LIC.TELEVIC\_PROJECTS\_TELEVIC\Edumatic3\edumatic-3-framework-server-.NET\Edumatic3.Business\MetaDataLogic.cs:line 68

at Edu3.Service.MetaDataService.LoadAllLibraries() in C:\Users\LIC.TELEVIC\_PROJECTS\_TELEVIC\Edumatic3\edumatic-3-framework-server-.NET\Edumatic3.Services\MetaDataService.cs:line 34
[/code]

Well, I refactored the sp’s and I replaced _ in names by . (that’s a point). Apparently the points were the whole problem. A SP named [dbo].[sp.Edu3.CopyBlock] doesn’t work, but [dbo].[sp_Edu3_CopyBlock] does…

Would like to know the reason for this…

Lieven Cardoen aka Johlero

This only works with SubSonic 2.1!

1
2
3
4
5
6

var query = new Select().Top("1").From(TreeHtmlExports.Schema)
			.Where(TreeHtmlExports.Columns.TreeId).IsEqualTo(treeId)
			.OrderDesc(TreeHtmlExports.Columns.DateCreated);
 
Dal.TreeHtmlExportsCollection treeHtmlExportsCollection = 
	query.ExecuteAsCollection<TreeHtmlExportsCollection>();

Ciao! Lieven Cardoen aka Johlero

Last week I had to transfer data from an old database to a refactored one (linq to sql). To do this I also needed to read in more than thousand Xml-files (linq to xml) to map certain Id’s from the old database to Id’s of the new refactored database.

I thought this to be a good opportunity to look at Linq and use it in practise. It was fun, it was really fun and mostly quick, very quick. Linq is excellent for little projects or console applications like mine here. If it’s usefull in big projects I wouldn’t know. There’s also the Entity Framework I should have a look at to answer this question (discussions on this you’ll find plenty if you google for a split second…).

Anyway, I easily created a datacontext for the old database and another for the refactored database. I did have some problems with identical tables in both databases, so I had to change some names. But everyting went very smooth. The queries are extremely easy to create, updating and creating records is a piece of cake and accessing a table is as easy as dragging the table from the server explorer in the dbml file.

I had one book next to me which helped me a lot:

Linq in Action

Besides the I also used this link a lot.

One thing I did search a while for is the transact SQL coalescing equivalent. In C# the equivalent is ??.

Have a look at this link to find out more.

Ciao, Lieven Cardoen aka Johlero

I’ve seen that working with stored procedures in MSSQL2005 sometimes raises a lot of questions in me in how to maintain all those stored procedures. I was wondering if there was a way of checking Data Object Dependencies, such as stored procedures that depend on a table.

I’ve gotten some good answers on StackOverflow.

Apparently there is a database engine stored procedure and two dynamic management functions you can use to do this like:

1. sp_depends: Displays information about database object dependencies, such as the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure. References to objects outside the current database are not reported. –> will be removed in future versions of mssqlserver
2. sys.dm_sql_referencing_entities: Returns one row for each entity in the current database that references another user-defined entity by name. A dependency between two entities is created when one entity, called the referenced entity, appears by name in a persisted SQL expression of another entity, called the referencing entity. For example, if a user-defined type (UDT) is specified as the referenced entity, this function returns each user-defined entity that reference that type by name in its definition. The function does not return entities in other databases that may reference the specified entity. This function must be executed in the context of the master database to return a server-level DDL trigger as a referencing entity.
3. sys.dm_sql_referenced_entities: Returns one row for each user-defined entity referenced by name in the definition of the specified referencing entity. A dependency between two entities is created when one user-defined entity, called the referenced entity, appears by name in a persisted SQL expression of another user-defined entity, called the referencing entity. For example, if a stored procedure is the specified referencing entity, this function returns all user-defined entities that are referenced in the stored procedure such as tables, views, user-defined types (UDTs), or other stored procedures.

Also very usefull is SysComments :

SELECT DISTINCT Object_Name(ID)
FROM SysComments
WHERE text LIKE '%Table%'
AND text LIKE '%Column%'

Apparently this SysComments table is a sql server 200 system table included for backward compatibility. Instead you should be using sys.sql_modules, which is a view object.

Writing tests however seems to me the most reliable way of ensuring that nothing was broken when changing an object in the database.

Lieven Cardoen aka Johlero

Go to Edit Project and in the Options tab check the Collations Checkbox under Ignore.

That’s it!

Otherwise SQL Compare will rebuild all tables that have different Collation… Takes longer!

The Behavioral Problem

There are the structural aspects (how tables relate to objects) but there’s also a behavioral problem which is harder to solve. How will you get the objects to load and save themselves in the database? If you load a lot of objects into memory, you will have to make sure that the database you are working with stays consistent and synchronized with your memory model. This is not an easy job because for instance sometimes you need to create a row first before you can modify another (because you need the id of the created row). Concurrency is also a big issue here!

The Unit of Work pattern can solve both of these problems. It keeps track of all objects read from the database together with all objects that are modified. It also knows how to make updates to database. The programmer then tells the Unit of Work to commit. It is the controller of the database mapping.

An Identity Map is needed to keep a record of every row you read. If you read some data, you can check this Identity Map to know if you don’t allready have it.

When using a Domain Model, usually you will load linked objects together. Without Lazy Load however, this would mean that enormous object graphs would be loaded out of the database. Lazy Load relies on having a placeholder for a reference to an object. If you try to follow the placeholder, the real object will get pulled from the database. A good example here is when in Flex we have to load big trees. If you have a tree with 100.000 folders, loading them all together would take a while. If we load a folder, we give back its children but not the children of the children. So a depth 1 is returned.

In a previous project created with Flex/Fluorine Gateway/.NET C#/SQL2005 I just save the whole model. Having to program a Unit of Work keeping track of changes would have taken me too long. I realize that if the project would be used by thousand concurrent users, changes would have to be made. For now, it isn’t a problem, but I can imagine that working out a Unit of Work would not be an easy job. In the current project a similar problem arises when editing exams. An exam consists of some components that contain items (exercises). A Unit of Work would have to keep track of deleting items, moving items, changing items, changing components, … Now we just save the whole Exam object (this means mapping it to DTO’s in Flex, sending it to the service layer, converting it to Table Data Gateway, deleting the Exam and saving the Table Data Gateway). A fast solution, but it actually takes 30 seconds to do it… No need to say we’ll have to change this in the future.

A big part in the role of the data source layer is talking to a database, most of the times a relational database. I have done some reading about how to implement this data source layer because it can become pretty messy in big projects. It’s also hard to refactor once a choice of pattern is made so it’s important to make the right choice from the beginning. For my current project I’ll look into some patterns, ideas, new technologies and write down a resume here.

Architectural patterns

They define how the domain logic talks to the database. In my experience there should be data access layer (.NET, Java, …) developers and database developers. Most of the times a programmer also does the database side as a result of which they don’t become database experts. When you have data access layer developers and database developers, you will want to seperate SQL from the code, so that a database developer can have a look at the SQL. In this way, a Database administrator can understand how best to tune it and how to arrange indexes.

We had a performance problem with lazy loading a tree. It went so slow that usability was down to nothing. The trees are saved as left right trees in the database (preorder tree traversal algorithm). Normally the advantage of these trees are that loading the whole tree goes very fast. However, for the lazy loading, we only need the children of the node we are loading, and this took a very long time. After searching a bit on indexes in SQL, I managed to improve the performance to a level that usability is really very good now. Lazy loading the trees is now a pleasure.

A good way to do this is to have classes based on the tables (one class for each table). These classes form a gateway to the table. Developers who specialize in the database have a clear place to go.

1. Row Data Gateway: An instance for each row that’s returned by a query.
2. Table Data Gateway: Single object for each table in the database. This provides methods to query the database that return a Record Set.

This will only work for applications where the domain model is allmost equal to the database structure, having one class for each table.

A Table Data Gateway can also be used to organise Stored Procedures. You could treat the Stored Procedures as Tables and have a Table Data Gateway to wrap the calls to the stored procedures.

Active Record is having a Row Data Gateway and then adding domain logic to the class. Again, if your Domain Model corresponds closely to the database structure, this option is usefull. However, if your Domain Model becomes more and more complicated, the Active Record approach will break down sooner or later. As you start to implement inheritance, OO patterns, stategies, … in your Domain Model, things will get out of control. Another thing is that O/R mapping tools generate classes that you will need to extend to add Domain Logic. Eventually sending these objects to Flex will get messy as you will have to exclude certain properties that do not need to be send. A solution is to isolate your Domain Model from the database by making an indirection layer responsible for the mapping between domain objects and db tables. This is called a Data Mapper and it completly isolates the two layers.

There are tools that create these patterns(O/R mapping tools). I have used MyGeneration together with Entity Spaces and SubSonic (both Table & Row Data Gateways I think). Both were very helpfull but not ideal. They are third party tools and when you change the database you need to recreate all the classes. The biggest problem is that if the database changes, compiling the project will be no problem. At runtime however problems will start and errors will follow. All the projects that I’ve done are RIA using Flex and Remote Calls to the Server. This means having DTO’s. The classes created by Entity Spaces and SubSonic are too large and complicated too send to Flex. So I had to create Mappers from Entity Spaces/SubSonic to the DTO’s, which made it complicated. I’m kind of hoping that LINQ solves this. In the current project I’m working on the complexity is even bigger. I’m using Stored Procedures to retrieve data and SubSonic to save data and sometimes retrieve data. This means I have to convert DataSets and SubSonic Objects to DTO’s before sending them to Flex. Now we are also porting the model from Flex to .NET, which means that converters also need to be able to convert datasets and subsonic objects to the Model. It gets kind of complicated and I’m searching for a good solution here.

Another thing are OO databases. I haven’t experimented with them yet, but the whole problem of mapping the database to the model would disappear with a OO database (I think). The whole mapping thing is only relevant because of the fundamental difference between objects and relations. Objects hold a reference to another object while tables hold a primary id to another record. I guess not many projects use OO databases, maybe because relational databases have been around for a long time and are proven technology.

When using SubSonic you have support for deleting a record instead of destroying it. Deleting would be having a flag Deleted or IsDeleted set to true. Destroying removes the record from the Table.

SubSonic adds logic to the classes if a Table contiains a Deleted or IsDeleted column (a non null bit column). Setting this Flag to true is very easy (SubSonic 2.1):

1

Dal.Channels.Delete(channelId);

If the table doesn’t have a Deleted or IsDeleted flag, the record will be removed.

Getting all the channels in SubSonic would normally be done like this:

1
2

ChannelsCollection channelColl =
new ChannelsCollection ().Where(Channels.Column.IsDeleted, false).Load();

With SubSonic 2.1 howver the same result is gotten using just:

1

ChannelsCollection userColl = new ChannelsCollection ().Load();

This Stored Procedure deletes the children of a node in a left/right tree (Preorder Tree Traversal). The two tables are what you need to understand the Stored Procedure. This SP is allmost identical to the sp_deletetreestructure.

deletetreestructurechildren :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

/******
Object:  StoredProcedure [dbo].[sp_DeleteTreeStructureChildren]
Script Date: 08/17/2008 19:12:16
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DeleteTreeStructureChildren]
(
@TreeStructureId INT -- Id TreeStructure which children should be deleted
)
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
    DECLARE @TranName NVARCHAR(MAX)
    SELECT @TranName = 'DeleteTreeStructureChildren';
    BEGIN TRANSACTION @TranName
 
    BEGIN TRY
 
        DECLARE @RightValue INT
        DECLARE @LeftValue INT
        DECLARE @Width INT
        DECLARE @TreeId INT
 
        SELECT	@RightValue = (RightValue - 1),
        		@LeftValue = (LeftValue + 1),
        		@Width = (RightValue - LeftValue) - 1,
        		@TreeId = TreeId
        FROM	TreeStructures
        WHERE	(TreeStructureId = @TreeStructureId)
 
        DELETE
        FROM	TreeStructures
        WHERE	(LeftValue BETWEEN @LeftValue AND @RightValue)
        AND 	TreeId = @TreeId
 
        UPDATE	TreeStructures
        SET 	RightValue = RightValue - @Width
        WHERE	RightValue > @RightValue
        AND 	TreeId = @TreeId
 
        UPDATE	TreeStructures
        SET 	LeftValue = LeftValue - @Width
        WHERE	LeftValue > @RightValue
        AND 	TreeId = @TreeId
 
    COMMIT TRANSACTION @TranName
 
    END TRY
 
    BEGIN CATCH
 
    /*
    If an error occured we Roll Back the transaction
    and raise an error that VS can catch
    */
 
    ROLLBACK TRANSACTION @TranName
    RAISERROR 50001 'Error in sp_DeleteTreeStructureChildren.
    Rollback has been executed.'
 
    /**********************************************************/
 
    END CATCH
END

This Stored Procedure deletes a node in a left/right tree (Preorder Tree Traversal) and all its children. The two tables are what you need to understand the Stored Procedure.

sp_deletetreestructure :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

/****** 
	Object:  StoredProcedure [dbo].[sp_DeleteTreeStructure]    
	Script Date: 08/17/2008 19:12:16 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DeleteTreeStructure]
(
	@TreeStructureId INT -- Id TreeStructure to be deleted
)
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
	DECLARE @TranName NVARCHAR(MAX)
	SELECT @TranName = 'DeleteTreeStructure';
	BEGIN TRANSACTION @TranName
 
	BEGIN TRY
 
		DECLARE @RightValue INT
		DECLARE @LeftValue INT
		DECLARE @Width INT
		DECLARE @TreeId INT
 
 
 
		SELECT	@RightValue = RightValue, 
				@LeftValue = LeftValue, 
				@Width = (RightValue - LeftValue) + 1,
				@TreeId = TreeId
		FROM	TreeStructures
		WHERE	(TreeStructureId = @TreeStructureId)
 
		DELETE
		FROM	TreeStructures
		WHERE	(LeftValue BETWEEN @LeftValue AND @RightValue) 
				AND TreeId = @TreeId
 
 
		UPDATE	TreeStructures 
		SET 	RightValue = RightValue - @Width 
		WHERE	RightValue > @RightValue 
				AND TreeId = @TreeId
 
		UPDATE	TreeStructures 
		SET 	LeftValue = LeftValue - @Width 
		WHERE	LeftValue > @RightValue 
				AND TreeId = @TreeId
 
		COMMIT TRANSACTION @TranName
 
	END TRY
 
	BEGIN CATCH
 
		/* 
			If an error occured we Roll Back the transaction	
			and raise an error that VS can catch 
         */
 
		ROLLBACK TRANSACTION @TranName
		RAISERROR 50001 'Error in sp_DeleteTreeStructure. 
						Rollback has been executed.'
 
		/**********************************************************/	
 
	END CATCH
END