The ADO .NET Entity Framework

During the development cycle of a database application, the developer may face issues having to do with the fact that the logical (relational) and related infrastructure may not match the application’s needs. This happens as relational schemas are often normalized in such a way, for being able to be shared across multiple applications.

Still, this does not allow the relational schemas to be somehow “aware” of the business concept of the database application. As a result, developers often find themselves in the position of trying to remember whether a database table is called Employee or Employees, how the employees of the various departments are separated within the same table and so on. This requires going over the database’s schema diagram repeatedly which is a task consuming valuable time.

The ADO .NET Entity Framework provides a solution to this problem by allowing the creation of conceptual models mapped to the corresponding relational models. In order to understand the concept of the ADO .NET Entity Framework, first you have to understand what the Entity Data Model is. This is actually the core concept of the Entity Framework.

The Entity Data Model (EDM) is a conceptual model of problem space. It introduces an object model which is independent of any storage considerations. Within this model the Entity is introduced. An entity is an instance of an Entity Type (i.e. an SQL Server Database Table). Entities can contain relational database types (e.g. int, varchar) as well as multi-property types (e.g. address). Entities are grouped in Entity-Sets. EDM also introduces Relationships. Relationships associate entities and are grouped in Relationship-Sets.

The ADO .NET Entity Framework technology features an architecture where there are the ADO .NET 2.0 Data Providers which provide access to the various DBMSs and on top of these there is the EntityClient Data Provider which is very similar to an ordinary .NET provider. This provider's "database" is the Entity Data Model. This allows Object Services which is on top of the abovementioned components, to support various levels of access and services to the conceptual schema like: Change Tracking, Object Identity, Overriding INSERT/UPDATE/DELETE with stored procedures, Column-level concurrency for updates, System.Transactions or explicit transactions. Object Services also support LINQ to Entities which is a very powerful way for querying the conceptual model.

A simple procedure of using the Entity Framework is the following:

1. Connect to a DBMS via an ADO .NET Provider.
2. Create the conceptual model (EDM) by mapping database entities to conceptual entities.

Then, you can access/query the conceptual model either by using Entity SQL (a special version of SQL language containing certain extensions), or by using Object Services, or by using LINQ to Entities. The latter is a very powerful way of accessing the conceptual model because it provides all the benefits that LINQ offers like: compile-time syntax checking, intellisense and much more!

Now, if the relational model (database) and/or the mappings change, this will not be a problem for the developer as he/she will still be able to query the same conceptual entities without requiring to change anything in the code!

Please keep in mind that for being able to use the ADO .NET Entity Framework you will need the Microsoft .NET Framework 3.5 and Visual Studio 2008.

SQL Server 2008 provides significant support and many features for use with the ADO .NET Entity Framework. More information can be found on this link.

There is also excellent online documentation regarding the ADO .NET Entity Framework and LINQ to Entities. More information is available on this MSDN Library link.


Read more on this article...

uCertify PrepKit (070-443) Review

I was recently asked to review one of the products of uCertify company. uCertify provides IT certification preparation PrepKits for a large number of vendors. To this end, I was given a copy of PrepKit 070-443 which is for the corresponding Microsoft Exam 70-443: PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005 and reviewed it.

I found the PrepKit very useful, with comprehensive content and various ways of study/practice, which help the candidate to easily learn the technology featured in the target exam. Here follows my detailed review categorized in sections:

1. License Key Installation
The license key installation was very easy. The entire procedure was simple and yet very efficient.

2. Graphical User Interface Experience
The Graphical User Interface of the application is very user-friendly. It is easy for the user to navigate through the various features of the application without requiring the application’s help feature. The file menu is also efficient. Though the user-friendliness of the application’s main screen allows the user to directly use the various features by just clicking on the icons.

3. Updates
Updating the PrepEngine is very easy as a relevant hyperlink is displayed in the PrepKit which allows the user to click on it and automatically check for updates.

4. Tips
The tips being displayed on the main screen of the PrepKit are very good as they give important advices especially for beginners not having much experience with professional exams.

5. Main Features

5.1 Features with main title “Enhance your understanding”
The PrepEngine provides many features that facilitate the candidates to perform a comprehensive preparation for the target exam.

The “Exam Objectives” are very important as they summarize the knowledge that must be gained by the candidates for the specific exam. An excellent feature is the “Objectives with Notes” which provides the main concepts for each section of the exam in the form of comprehensive notes.

The “Interactive Quiz” is also very good as it provides the candidates with a direct way of validating their knowledge.

Also the “Flash cards”, “Study notes”, “Articles”, “Study tips” and “How tos” features provide comprehensive information related to the exam and allow the candidates to gain additional, in-depth knowledge on the exam’s subject.

All these features provide many alternative ways to the candidates for testing their knowledge and getting a deeper understanding of the various concepts with respect to the target exam.

5.2 Features with main title “Practice Test”
I believe that the organization of the Practice tests is very good. First, by providing a diagnostic test, the PrepKit allows the candidates to get a first idea regarding their level of knowledge on the subject.

Then, after the candidates complete their study, they can use the three pre-defined practice tests and eventually, take the Final practice test.

I also found the adaptive test very interesting because by using the technique of dynamically adjusting the level of difficulty, it allows the candidates to be presented with questions based on their level of knowledge.

The custom test is also important as it allows the candidates to select questions from specific chapters/exam sessions.

Last but not least, the fixed time test is very good for practicing the timing because, as always, during an exam timing plays a significant role towards success.

A remark having to do with all the practice tests is that the explanations of the answers to the questions are very comprehensive as they explain in detail why an answer is correct or wrong.

5.3 Features with main title “Track your progress”
The “Test History” feature is very useful as it shows the performance for all the practice tests the candidates have taken. This also allows the candidates to see their progress from the earliest to the most recent practice test.

Additionally, the available actions are excellent as they allow the candidates to directly perform various tasks on each practice test. Overall, I believe that the “Test History” feature is really significant as it summarizes all the practice/activity of the candidate.

Similarly, the “Readiness Report” feature provides additional statistics regarding the performance of the candidate with respect to the practice tests results. The “Readiness Report - Objectives” screen allows the candidates to see overall statistics regarding their performance for the various sections of the exam. This allows the candidates to focus on sections on which they might need further practice/study.

6. PrepKit Content
I believe that the PrepKit has a lot of content; something which enables the candidates to practice their knowledge efficiently. With comprehensive answers to all the questions, the PrepKit can help the candidates to better acquire the necessary knowledge based on the exam’s objectives. With various levels of difficulty it allows the candidates to have a proper preparation for the target exam.

In addition to the practice tests and the questions/answers sections, the rest of the features under the title “Enhance your Understanding” provide significant, additional content that undoubtedly allows the candidates to learn additional concepts and thus get an even more in-depth understanding for the technology featured in the target exam. This is very important as many times during an exam, whenever the candidates possess this in-depth understanding of the target technology, it is easier for them to properly manage their time and thus not to run out of time during the exam.

7. Overall Comments
My overall comments after this review, during which I reviewed the uCertify PrepKit M70-443, is that the specific PrepKit is excellent; it provides the candidate with a lot of content relevant to the target exam, and also provides many alternative ways for acquiring the necessary knowledge related to the technology featured by the exam. The Graphical User Interface, the navigation and the rest of the features make it really easy for the candidate to use the product and get a proper preparation for the target exam.

Read more on this article...

Exclusive access could not be obtained because the database is in use

Well, after a few days of actually doing nothing but sleeping and resting because of the flu, here I am back to blogging! :)

A very neat feature of SQL Server is the way it lets the DBA to control connections. To this end, you can explicitly control connections with specific commands and methods. Even though this is not usually needed, there are some cases where you might need to make use of this feature. One such a case is when you are presented with the error:

"Exclusive access could not be obtained because the database is in use"

In SQL Server you might be presented with the above error message in the case you are trying to perform an operation on a database that requires exclusive access. One such operation is the RESTORE.

Exclusive access is when an operation needs to run on a database (or another SQL Server object) without any other operations/sessions accessing the same database/object at the same time.

So, what can you do when you get this error message? You still need to perform the RESTORE right? :)

Here's a common way of doing that:

1. Set the database in SINGLE_USER mode and forcibly terminate other connections:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

The above command will set the database in SINGLE_USER MODE and additionally the ROLLBABK IMMEDIATE termination option will roll back all the incomplete transactions. It will also disconnect any other connections to the database.

*Note: After issuing the above command, you will only be able to use a single connection to the database. So, if you are already using a query window connected to the target database, either restore the database by using the RESTORE T-SQL command or close the query window and restore the target database by using the Restore Database Wizard from SQL Server Management Studio.

2. Restore the database.

3. Set the database back to MULTI_USER mode:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET MULTI_USER;

That's it! You have successfully restored the database!

And here's an alternative way (a little bit more complex):

1. If there are any SQL Server logins granted any access on the target database then disable them:

alter login [LOGIN_NAME] disable

*Note: Even though now you have disabled the login(s) there still might exist active connections to the database.

2. To find and terminate existing connections on the target database perform the following:

-- Query that returns all the SPIDs (process IDs)/sessions established to a given database:
SELECT spid,loginame,login_time,program_name
FROM [master]..sysprocesses
WHERE DBID=DB_ID('DATABASE_NAME')

By collecting the above SPIDs you get the sessions that must be forcibly terminated (be careful not to forcibly terminate your own session! :) By including the columns loginame, login_time and program_name in the above query, you are able to identify and exclude the spid that belongs to your session (that is, the DBA session).

You can terminate a session by executing the T-SQL Statement:

KILL @SPID

*Note 1: Replace @SPID with the appropriate SPID number.
*Note 2: You can even create a user-defined function undertaking this task.

3. Set the database in SINGLE_USER mode:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER;

*Note: After issuing the above command, you will only be able to use a single connection to the database. So, if you are already using a query window connected to the target database, either
restore the database by using the RESTORE T-SQL command or close the query window and restore the target database by using the Restore Database Wizard from SQL Server Management Studio.

4. Restore the database.

5. Set the database back to MULTI_USER mode:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET MULTI_USER;


Remarks and Considerations
------------------------------
In the case of a RESTORE operation I would personally prefer the first method as it is simpler. With a single T-SQL Statement you set the database in SINGLE_USER MODE and also terminate all the active connections immediately and roll back all the incomplete transactions.

The second method is more preferable in cases where more "elegant" session control is required. This method allows the DBA to terminate sessions one-by-one explicitly instead of the first method which massively terminates all connections.

Choosing one of the two ways I guess that relies to the urgency of the RESTORE operation, the judgment of the DBA and also any Business Policies that might stand for such cases.

Because both the abovementioned methodologies have to do with forcibly terminating connections to a database in SQL Server, you must be extremely careful when using them as you might accidentally cause severe data loss when applying them inappropriately and without the necessary authorization from the Management.

Read more on this article...