I have a project that requires me to send contracts that are stored in SQL Server to customers and I started poking around for the best way to do it.  First, I found a quick code snippet that sends an email using sp_send_dbmail:

 EXEC msdb.dbo.sp_send_dbmail
@recipients = 'lsmithmier@productiveedge.com',
@body = 'Just testing the mail',
@subject = 'Sending Mail using Database Mail' ;

 

When I first ran it on my database server (SQL Server 2008 R2) I got the following error:

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component
'Database Mail XPs' because this component is turned off as part of the
security configuration for this server. A system administrator can enable the
use of 'Database Mail XPs' by using sp_configure. For more information about
enabling 'Database Mail XPs', see "Surface Area Configuration"
in SQL Server Books Online.

So I cranked up Microsoft SQL Server Management Studio and set email up:

1.  Open up the Management section and right click on the Database Mail item:

2.  Choose Configure Database Mail

3.  Click Next

4.  Click Next

5.  Click Yes

6.  Set the Profile name and choose Add an SMTP account:

7.  Enter your settings:

8.  Set the profile as both Public and Default:

9.  Click Next

10. Click Finish

11. Click Close

and it is all setup!  Really easy compared to the old way of setting email up with SQL Server.  Then I re-ran the query and got the following response:

Mail queued.

 

But then what?  I ran a quick query and found that the email wasn’t sent correctly:

 use [msdb]
SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO 

so I looked back over my settings and updated the port number to hit the outgoing relay and everything started working!

DatabaseMail · SQL Server

I have been working with Telerik controls for a little over two months (the ASP.NET AJAX suite) and have been continuously impressed with the quality and power they provide.  I am working on a project that requires the upload of several different file types which need to be displayed in a grid once uploaded.  The cleanest solution I could think of was to create a User Control to handle the uploads and embed it within the grid through the EditFormSettings FormTemplate.  So first, I created a simple upload User Control as follows:

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="UploadControl.ascx.cs"
    Inherits="WebApplication1.UserControls.UploadControl" %>
    
    
    
    
    
    Submit

with the code behind set up to store the uploaded files in the database using LINQ:

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            LinerModelDataContext dataContext = new LinerModelDataContext();
            foreach (UploadedFile file in RadUpload1.UploadedFiles)
            {
                byte[] bytes = new byte[file.ContentLength];
                file.InputStream.Read(bytes, 0, file.ContentLength);
                Attachment attachment = new Attachment();
                attachment.FileName = file.GetName();
                attachment.BinaryAttachment = bytes;
                attachment.CustomerID = this.CustomerID;
                dataContext.Attachments.InsertOnSubmit(attachment);
            }
            dataContext.SubmitChanges();
        }

I then just added the EditFormSettings section to RadGrid1 and I was able to upload files:

But my grid wasn’t refreshing and I wasn’t getting any notification that the upload was complete. So, to provide the user experience I wanted, I exposed an event to the grid from the upload control by modifying the code behind of the UserControl. I added a delegate, and exposed the event as follows:

    public delegate void UploadHandler();

    public partial class UploadControl : System.Web.UI.UserControl
    {
...
        public event UploadHandler Upload;

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            LinerModelDataContext dataContext = new LinerModelDataContext();
            foreach (UploadedFile file in RadUpload1.UploadedFiles)
            {
                byte[] bytes = new byte[file.ContentLength];
                file.InputStream.Read(bytes, 0, file.ContentLength);
                Attachment attachment = new Attachment();
                attachment.FileName = file.GetName();
                attachment.Attachment1 = bytes;
                attachment.CustomerID = this.CustomerID;
                dataContext.Attachments.InsertOnSubmit(attachment);
            }
            dataContext.SubmitChanges(ConflictMode.ContinueOnConflict);
            if (Upload != null)
            {
                Upload.Invoke();
            }
        }

and changing the FormTemplate to be:

(please note that the syntax highlighter I am using mangled the code by changing OnUpload to be onupload in the above source) and adding the handler to the main page:

        protected void UploadControl1_Upload()
        {
            RadGrid1.MasterTableView.IsItemInserted = false;
            RadGrid1.Rebind();
        }

Event · Example · Telerik · UserControl

I love working with legacy code, really I do.  I find the effort of trying to put myself mindset of the people who originally coded the applications enjoyable.  It is somewhat satisfying to be able to get enough of a feel for the code to get a feel for what they were attempting to do as well as what got done.  In most cases, it is quite easy to see which libraries and sections were implemented first, and which were either rushed to completion or tacked on as an afterthought.  It often makes me spend the extra 15 minutes to fully implement something rather than simply patching over a flaw and planning on implementing the ‘real code’ at some mythic future date.  Coming in cold to a project, doing a search for TODO is a good start, but I have also found a lot of cases where that isn’t good enough; so, I have begun setting Visual Studio (VS) to break on any thrown exception.  If you aren’t familiar with how to change the way VS handles exceptions in debug mode, here is where you can make the changes:

Doing this helps me track down gems like this (from actual code, I made minor changes to keep from exposing client specific information):

            //TEMPORARY - make sure the ID contains at least one alpha
            try
            {
                int i = int.Parse(newID);
                newID = newID.Remove(4, 1).Insert(2, "X");
            }
            catch { }

 

To be fair to the coder, TryParse has only been in the framework since 2.0, and this code could well have have begun life as a 1.1 application, but the TEMPORARY in the comment was obviously overly optimistic.  My solution was to replace the above with:

            int i;
            //make sure the ID contains at least one alpha
            if (int.TryParse(newID, out i))
            {
                newID = newID.Remove(4, 1).Insert(2, "X");
            }

Exceptions · Legacy · Visual Studio

I am often tasked with making modifications to existing UIs, and tend to try and limit the impact I have by pushing a lot of the formatting and BL into the database.  One piece of code that I regularly have to look up is the code to convert a result set into a comma delimited list.  Here is the SQL 2005/2008 code that I just created for use in a client environment:

CREATE FUNCTION [dbo].[fn_LinkedString]
(
	-- Add the parameters for the function here
	@ID varchar(10)
)
RETURNS nvarchar(1000)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result nvarchar(1000)

	-- Add the T-SQL statements to compute the return value here
	SELECT @Result= x.List
	FROM
		(
			SELECT
				L1.ID,
				List = SUBSTRING((SELECT (', '+ LinkedID)
										FROM Linked L2
										WHERE
											L1.ID = L2.ID
										ORDER BY
											ID,
											LinkedID
										FOR XML PATH('')
										), 3, 1000) FROM Linked L1
			GROUP BY ID
		) x
	WHERE
		x.ID = @ID
	-- Return the result of the function
	RETURN @Result

END

I wrapped the code in a scalar valued function to make it easier to use and to keep the code clean when used.

SQL Server · TSQL

I occasionally need to get some representative values from a table for testing and thought my solution might be something worth sharing.  I tend to think in nested selects and go back and clean up code into joins where appropriate.  Below you will see that I am using a group by and a min function to identify single instances of different types, and then pulling the required data in an outer select:

SELECT
	UserName
FROM
	Users
WHERE
	UserID in
	(
	SELECT
		MIN(UserID)
	FROM
		USERS
	WHERE
		Active = 1
	GROUP BY
		TypeCode
	)

Example · SQL Server · TSQL