Category: Pro Tips

Pro Tip : SQL Tips

Calling a udf from SQL Query Analyser
SELECT dbo.udf_calculate_working_days(’01/01/2000′, ’01/01/2001′)

Calling a Table valued function from SQL Query Analyser
SELECT * from dbo.udf_calculate_working_days(’01/01/2000′, ’01/01/2001′)

Get the number of days between 2 dates
SELECT DATEDIFF(dd, ’01/01/2009′ , ’01/01/2010′) as ‘total_days’

SQL for when checking against todays date
SELECT DATEADD(dd, DATEDIFF(dd,0,@order_date), 0



Pro Tip: Being more productive with ReSharper Live Templates

Whilst watching a @tekpub video about Roy Osherove’s TDD Masterclass I saw a tip he gave where he showed how to create a ReSharper Live Template to save time and be more productive, although this was related to TDD you can create your own user template for anything you like, this tip is for creating NUnit test methods but the main thing is the idea, here is what he showed:-

1 – Install ReSharper.
2 – Then from within Visual Studio, select ReSharper–> Live Templates.
3 – Check the box next to User Templates and then, click the Add icon or select New Template.
4 – Give the Template a name and then enter content as the below screenshot:-

Test Template

Once this is saved if you back to your class and type test and then hit the tab key you’ll see the empty [Test] method generated and it asks you to supply the name.

Try this out and you will soon see the potential time savers you can come up with.



using the exchange web service API from c#

This past week I have been looking at the exchange web service API and how we can inspect Emails within Exchange. If you need to read emails from an inbox then you can very easily and quickly by using the exchange web service API which you can download from here.

I was looking for a way to check a folder for emails and then look at the xml file attachments and then do some work on the contents of the attached files – once done with the contents then mark the email as read and then move the email to an archived folder.

In the following example lets assume we will have emails which will be directed into a folder we specify using a rule, the name of the folder will be stored in the web.config file so we can make this configurable at any point, for arguments sake lets call this folder ExchangeAPIDropFolder.

The following code demonstrates how to go about this:-
[sourcecode language=”csharp”]

private static void CheckEmailFolderForContents()
{
string exchangeUsername = ConfigurationManager.AppSettings["ExchangeUsername"];
string exchangePassword = ConfigurationManager.AppSettings["ExchangePassword"];
string exchangeAutodiscoverUrl = ConfigurationManager.AppSettings["ExchangeAutodiscoverUrl"];
string exchangeAPIDropFolderFolderName = ConfigurationManager.AppSettings["ExchangeAPIDropFolderFolderName"];
string exchangeAPIDropFolderArchivedFolderName = ConfigurationManager.AppSettings["ExchangeAPIDropFolderArchivedFolderName"];

ExchangeService ews = new ExchangeService(ExchangeVersion.Exchange2010_SP1)
{
Credentials = new WebCredentials(exchangeUsername, exchangePassword)
};

ews.AutodiscoverUrl(exchangeAutodiscoverUrl);

FindFoldersResults folderSearchResults = ews.FindFolders(WellKnownFolderName.Inbox, new FolderView(int.MaxValue));

Folder exchangeExchangeAPIArchivedFolder = folderSearchResults.Folders.ToList().Find(
f => f.DisplayName.Equals(exchangeAPIDropFolderFolderName, StringComparison.CurrentCultureIgnoreCase));

//Set the number of items we can deal with at anyone time.
ItemView itemView = new ItemView(int.MaxValue);

foreach (Folder folder in folderSearchResults.Folders)
{
if (folder.DisplayName.Equals(exchangeAPIDropFolderFolderName, StringComparison.OrdinalIgnoreCase))
{
Folder boundFolder = Folder.Bind(ews, folder.Id);

SearchFilter unreadSearchFilter =
new SearchFilter.SearchFilterCollection(
LogicalOperator.And, new SearchFilter. IsEqualTo(
EmailMessageSchema.IsRead, false));

//Find the unread messages in the email folder.
FindItemsResults<Item> unreadMessages = boundFolder.FindItems(unreadSearchFilter, itemView);

foreach (EmailMessage message in unreadMessages)
{
message.Load();

foreach (Attachment attachment in message.Attachments)
{
if (attachment is FileAttachment)
{
FileAttachment fileAttachment = attachment as FileAttachment;
fileAttachment.Load();

MemoryStream ms = new MemoryStream(fileAttachment.Content);
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(ms);

//TODO – Process File Contents
}
}

//Mark the message as read and then move it to the Archived Folder
message.IsRead = true;
message.Update(ConflictResolutionMode.AlwaysOverwrite);
message.Move(exchangeExchangeAPIArchivedFolder.Id);
}
}
}
}
[/sourcecode]

Thats it – enjoy and feel free to add comments or ask me about this code.



How to Source Control your SQL Database

In this blog post I am going to show you how to add a SQL Server database to Subversion using Redgate SQL Source Control, the tool claims you can add your database to a source control provider such as Subversion in about 5 minutes – lets see whats involved.

I have Subversion running on my own windows 7 pc at home, I use it for testing out tools and integration with CI and so on, in future blog posts I will cover more on Continuous Integration.

I’m going to start by adding an existing database into Subversion using the SQL Source Control tool with screenshots so you can follow. I currently have Subversion installed and running on c:\svn\trunk\

Below is a screenshot of my local SQL Server

screenshot1

screenshot1

SportsStore is a local database used within one of the MVC books I have, lets add this database to Subversion using the tool now.

Create a New Folder which needs to be empty within your local repository folder, in my case my subversion folder is c:\svn\trunk\ – this is where your database scripts and data will be where your scripts in source control will be locally.

screenshot3

screenshot3

Highlight the database on the left and then select the blue text on the right which says “Link database to source control…”

The following window will pop-up, here enter the url to your repository, since mine is local I just need to add c:\svn\trunk\SportStoreDB (this is the folder your db scripts will go into), click go.

screenshot2

screenshot2

You should see the following :-

screenshot4

screenshot4

At this point the tool has added all the scripts including the data for all your tables to Subversion – now we just need to get this from Subversion into our local folder – to do this right-click on the folder and choose SVN Checkout and then click ok as below

screenshot5

screenshot5

This now leaves us with the entire SportStore database having been scripted and placed within your subversion repository and you now have a local copy – perfect and easily accomplished.

Below are the screenshots of the output from the tool within your Subversion folder.

screenshot6

And a view of the stored procedures folder with created scripts for you.

screenshot7

For more on this great tool check out Redgate SQL Source Control.



Find Out Your Project Build Times in Visual Studio

Recently I was looking to try and work out why a project I was working with was taking a very long time to build – I was pleased to find that Visual Studio can out put individual project build times for a solution, this allowed me to see which projects within the solution were taking longer that I would have anticipated.

To View the build time of projects within a solution go to Tools, Options and look for Projects and Solutions, then look for Build And Run (or similar depending on the version of Visual Studio) and then change the drop down for MSBuild project output – change the loggig from Normal to Diagnostic.

Diagnostics Option

Now as the solution is building go to the View mwnu and select OutPut (to view the ouput window) and you can see a whole raft of build details including build times for each project.

Build Times

Hopes that useful to someone.



Quick SQL Server Tip – Creating your Insert Stored Procedure

When I start to create the insert stored procedure for an existing database table I used to always start by copying an existing stored procedure (for the template only purpose) and then empty the contents of the stored procedure, then I would right-click on the table and then select, script table as ‘Insert’ from the menu options as shown below:-

Insert into a table

This template always kind of annoyed me as I felt it never laid it out very well, only last week did someone point out that the best thing to do is to actually just type inside your stored procedure:-

INSERT INTO and then press Enter

This will generate the insert and also tell you the column types and sizes for variables such as varchar(s) as shown below, including putting in default dates.

Insert syntax

That’s it for now.



Quick Tip: Tracing SQL from your application

Tip: When your debugging some code and you want to trace the SQL that’s hitting your SQL Server Database then this is how I would normally cut through all the traffic hitting the SQL database your using.

Tip:- Add an application_name part to your connection string in the app.config or web.config, here is an example.

Before:-
add name=”TestConn” connectionString=”data source=ServerName;Integrated Security=SSPI;initial Catalog=TESTDB;providerName=”System.Data.SqlClient”

After:-
add name=”TestConn” connectionString=”data source=ServerName;Integrated Security=SSPI;initial Catalog=TESTDB;application name=;” providerName=TESTAPP_GREG”System.Data.SqlClient”

Couple of things to notice in the lines above – the second connection string I added an application name, but I also added my name so that when I run the application I can use the application name setting with my name so I only trace my code when I hit the database and no other code being ran will be shown within my trace window.

To do this I usually fire up SQL Server Management studio and then from within their fire up SQL Server Profiler and do the following (make sure to click Show All Columns):-

SQL Profiler

Then add the name of the application name into the box as below:-

SQL Profiler

Click ok and the only trace information recorded now will be the SQL that you are actually hitting the database with and no one else – enjoy.