How To Determine The Generated SQL Queries In Entity Framework 4

With the help of Entity Framework, you may never write a single SQL Query code in your programming projects. Some of the benefits of Entity framework include:

1. Visual Studio intellisense
2. Compile checking
3. Faster development
4. Auto generated domain objects
5. Lazy loading, etc.

However, sometimes you need to know how Entity Framework translates your LINQ Query into a SQL Query. You can't just trust the framework to correctly formulate the Query. If you are concerned about scalability and performance, you need to check and analyze the underlying query and if possible modify or refine it to increase querying speed and performance.

The easiest way to view the generated SQL is to use a profiler tool that's included in database management tools (for instance, SQL Server Manangement Studio). Using this tool, you can monitor all SQL statements executed against the database. But using this tool requires that you have access to the database. Fortunately, with Entity Framework 4, you can use ObjectSet class's ToTraceString method. 

Here's the example code on how to do it in C# and VB.NET: 

C#:

var result = ctx.Orders.Where(o => o.Date.Year == DateTime.Now.Year);
var SQL = (result as ObjectQuery).ToTraceString();

VB:

Dim result = ctx.Orders.Where(Function(o) o.Date.Year = DateTime.Now.Year)
Dim SQL = TryCast(result, ObjectQuery).ToTraceString() 

Sending Automated SQL Server Notification Emails

One of our clients asked today to be automatically notified through email if a failure in the regularly scheduled jobs in SQL Server occurs. This seems like a pizza cake (yummy!) considering that sending email is common and I'm sure I can find lots of information on the internet on how to achieve this in SQL Server.

The task was simple but unfortunately it took me the whole day to configure the Server. Information was available on the internet and I seem to be following the instructions correctly to the letter. But somehow, the feeling of success and being able to shout Yes! did not occur immediately and the simple task became a nightmare of trial and errors which left me wondering whether working in the office instead of working at home is better.

 
Now to the main course, in order for SQL Server to be able to send emails, the Database Mail must be enabled and configured. The Database Mail is available only for versions higher than the SQL Server Express Editions. First, you need to open SQL Server Management Studio and connect to the SQL Server Database. Once connected, go to the Object Explorer window and under the Management folder, right click Database Mail and select "Configure Database Mail".

When the initial screen shows, click Next.

In the "Select Configuation Task" Window, select "Setup Database Mail" option.

In the "New Profile" window, specify the Profile Name and Description in the designated text boxes and then click "Add".

In the "Add Account To Profile", select an account to add to the Profile you've created earlier or you can click "New Account" to create a new account.

When you create a new account, the "New Database Mail Account" window opens. specify the account name and description, then specify the the Outgoing Mail Server (SMTP) configuration and its authentication. Here, you need to be able to know the SMTP settings that your company uses. Then click OK. You will be taken back to the "New Profile" window earlier but with a new SMTP account specified. Click next.

In the "Manage Profile Security" window, click the Option to make your profile public and available. Click Next.

In the "Configure System Parameters" window, you can leave the default settings and click Next.

Then click Finish.

The Database Mail is now configured. You can check if you've configured it correctly by right-clicking on the Database Mail in Object Explorer and select "Send Test Email". Provide the email of the recipient and click Send. If the email is received, then pat yourself in the back because you're already halfway passed the work to be made.

We need to be able to use Notification Email in SQL Agent. To do that, you need to specify the "Operator" or the list of emails that can be the recipient of the email Notifications.
In the Object Explorer again, under the SQL Server Agent node, right click the folder Operators and select "New Operator".

In the "New Operator" window, specify the name of the Operator. Put the email recipients separated by semicolon in E-mail name text box. Click OK.

Then you need to enable the mail profile in SQL Server Agent properties. After enabling, restart the SQL Server Agent.

And finally, you can use the settings you've configured above in your SQL Server Jobs so that it can send email automatically if a job fails to execute. To do that, in the Jobs properties, under Notifications Tab,check email option and specify the operator you want to receive the email and select the event that can trigger the sending of the notification email.

With the above information in hand, I'm sure configuring another email notifications in SQL Server will be a breeze!

Hope this helps,
Noli

Convert Any Web Page To A PDF Document

There are times when we need a web page to be downloaded and viewed as a PDF document. Web pages display differently based on several factors such as the type and version of browser and size of the computer's screen. To be able to share the web page with other users and ensure that it displays correctly and uniformly regardless of these factors,  you can opt to view it as a PDF document.

If you don't have the software to convert a web page to pdf, you can use the tool I've provided here using a little bit of JavaScript and a service from PDFmyURL.com. Just input the url of the web page that you want to download as pdf and click "Generate PDF" button. Make sure though that the url that you input are not "log-in" protected. 








PDF, or otherwise known as  Portable Document Format, is the de facto standard for printable documents on the web. Most books now are published as a PDF. Ebooks, as they're called, has a few if not many advantages over its HardCover counterpart. To name a few, an Ebook can be searched automatically, you can bring a 5000-page ebook in your pocket, and it can't worn out over time.

A PDF document can be viewed in your PC or Mac using Ebook readers software such as Acrobat Reader, Foxit Reader, Google Chrome,etc. But when you want to read your ebook like a traditional book,  a must have is either Amazon's Kindle or Apple's IPad. Both are great for reading pdf documents.

Convert SQL to LINQ

LINQ (Language Integrated Query) is Microsoft's technology that provides .NET languages the capability to query data of all types. These types include in-memory arrays and collections, databases, XML documents, and more.

LINQ is such a vast topic that you will be better off learning comprehensively by buying a book on this superb technology. I'd highly recommend Steve Eichert's and Fabrice Marguerie's "LINQ in Action" book available in Amazon.




In this post, I'll provide examples for converting SQL queries into LINQ (in VB.NET and C#).
These samples are taken from VB Team's blog: http://blogs.msdn.com/b/vbteam/archive/tags/converting+sql+to+linq/default.aspx. Since all their samples are in VB.NET, I'll include C# for completeness.

SELECT * Query

SQL

SELECT *
FROM CustomerTable

LINQ(VB.NET)

From Contact In CustomerTable

LINQ(C#)

from Contact in CustomerTable
select Contact

SELECT Query

SQL

SELECT Name CustomerName, CustomerID ID
FROM Customers

LINQ(VB.NET)

From cust In Customers _
Select CustomerName = cust.Name, ID = cust.CustomerID _
Order By ID

LINQ(C#)

from cust in Customers
select new(){CustomerName=cust.Name, ID=cust.CustomerID }

SELECT... WHERE Query
SQL

SELECT * FROM CustomerTable
WHERE State = “WA”

LINQ(VB.NET)

From Contact In CustomerTable _
Where Contact.State = “WA”

LINQ(C#)

from Contact in CustomerTable
where Contact.State=="WA"
select Contact

SELECT DISTINCT Query
SQL

SELECT DISTINCT Name, Address
FROM CustomerTable

LINQ(VB.NET)

From Contact In CustomerTable _
Select Contact.Name, Contact.Address _
Distinct

LINQ(C#)

(from Contact in CustomerTable
select new(){Contact.Name,Contact.Address}).Distinct()

AND Operator Query
SQL

SELECT * FROM CustomerTable
WHERE City = “Seattle” AND Zip = “98122”

LINQ(VB.NET)

From Contact In CustomerTable _
Where Contact.City = “Seattle” And Contact.Zip = “98122”

LINQ(C#)

from Contact in CustomerTable
where Contact.City=="Seattle" && Contact.Zip=="98122"
select Contact

BETWEEN Operator Query
SQL

SELECT * FROM OrderTable
WHERE OrderDate BETWEEN ‘Sept-22-2007’ AND ‘Sept-29-2007’

LINQ(VB.NET)

From Shipment In OrderTable _
Where (Shipment.OrderDate > #9/22/2007#) _
    And (Shipment.OrderDate < #9/29/2007#)

LINQ(C#)

from Shipment in OrderTable
where Shipment.OrderDate > new Date(2007,9,22) && 
Shipment.OrderDate <  new Date(2007,9,29)
select Shipment

Order By Query
SQL

SELECT * FROM CustomerTable
ORDER BY Phone

LINQ(VB.NET)

From Contact In CustomerTable _
Order By Contact.Phone

LINQ(C#)

from Contact in CustomerTable
orderby Contact.Phone
select Contact

Order By ASC/DESC Query
SQL

SELECT * FROM CustomerTable
ORDER BY Phone ASC, Name DESC

LINQ(VB.NET)

From Contact In CustomerTable _
Order By Contact.Phone Ascending, Contact.Name Descending

LINQ(C#)

from Contact in CustomerTable
orderby Contact.Phone ascending, Contact.Name descending
select Contact

I'll update this blog later and add more samples...