Tuesday, June 28, 2011

Reliable Sending of Email for.Net/C# using SQL Server

If you want more reliable sending of emails from your .Net code you may think it is too much work to implement logging, retrying if smtp server is unavailable, control returned to user while email is sending, etc. The truth is that if you try to do it all via ASP.NET this can involve a lot of work. Thankfully, there is an easy way to meet all these requirements and not have to do much work at all.

As it turns out SQL Server has a robust facility for sending emails called Database Mail. It meets all the above requirements and these requirements are configurable. I have posted many entries on Database Mail such as resending emails, reviewing logs, etc. Just search on my blog for them. Much of the configuration comes from the Profile and Account settings you configure when you setup this info for your app.

Below is a C# method that you can add to your project. Calling this will allow you to send email reliably from your app and not have to worry about the delivery of it. it assumes that you have a class called Config with two static methods YourConnectionString and AllowEmailsToBeSent. You can hard code those obviously. The two methods just pull the values from the Web.config, but it could be anywhere in theory. The first is just your database connection string and the second controls whether the contents of the email will be written to a hard coded path on your harddrive for testing or it will be connect to SQL Server and actually send the email. This is useful for development / testing when you don’t want to disturb real users.

/// <summary>
/// Calls SQL Server Send Mail procedure, or logs to log file
/// </summary>
/// <param name="fromEmailAddress"></param>
/// <param name="toEmailAddresses">semi-colon separated list of recipients</param>
/// <param name="subject"></param>
/// <param name="body"></param>
public void SendEmail(string fromEmailAddress, string toEmailAddresses, string subject, string body)
{
    // this should never happen, but just so I will know about it
    if (string.IsNullOrEmpty(toEmailAddresses))
    {
        toEmailAddresses = "test@yourdomain.com";
    }
    // this should never happen, but just so I will know about it
    if (string.IsNullOrEmpty(fromEmailAddress))
    {
        fromEmailAddress = "test@yourdomain.com";
    }


    if (Config.AllowEmailsToBeSent)
    {
        // send email here
        using (SqlConnection conn = new SqlConnection(Config.YourConnectionString))
        {
            conn.Open();

            // use sql server database mail instead of .net mail
            // so that we get retry, logging, automatic batching, etc for free. Better reliability and recover.
            //-- for all parameters:
http://msdn2.microsoft.com/en-us/library/ms190307.aspx   
            SqlCommand cmd = new SqlCommand("msdb.dbo.sp_send_dbmail", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@profile_name", SqlDbType.VarChar).Value = "Your Profile name here";
            cmd.Parameters.Add("@recipients", SqlDbType.VarChar).Value = toEmailAddresses;
            cmd.Parameters.Add("@subject", SqlDbType.VarChar).Value = subject;
            cmd.Parameters.Add("@body", SqlDbType.VarChar).Value = body;

            cmd.ExecuteNonQuery();
        }

    }
    else
    {

        StreamWriter writer = new StreamWriter(@"c:\Email.log", true);
        using (writer)
        {
            writer.WriteLine("\r\n\r\nDate/Time Stamp: " + DateTime.Now.ToLocalTime());
            writer.WriteLine("From: " + fromEmailAddress);
            writer.WriteLine("To: " + toEmailAddresses);
            writer.WriteLine("Subject: " + subject);
            writer.WriteLine("Body: " + body);
        }
    }
}

Monday, June 27, 2011

Show JavaScript alert() or execute other code from Code-Behind

I find that sometimes I need to show the user a JavaScript alert for something I don’t want them to be able to miss and I only want to show it based on some server-side (Code-Behind) code. This will work for most any chunk of JavaScript code as well.

Below is the code that you can put in your Page_Load()

if (!Page.ClientScript.IsClientScriptBlockRegistered("MyMadeUpNameHere"))
{
    string jscript = "alert('test here');";
    Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "MyMadeUpNameHere", jscript, true);
}

Notice that MyMadeUpNameHere is some unique string that allows you to not have multiple copies of your code on the page. This ensures that you will only have it execute once. If you don’t want that functionality then don’t do the check.

In this example, the user will see the ‘test here’ in a JavaScript alert (Message Box).

Note, that the RegisterClientScriptBlock puts the JavaScript immediately below the opening form tag. If your code references any form elements or any other elements on the page this is most likely going to be too early since the page has not finished loading. This makes this method good for simple things like I did here that doesn’t really care about the page, or you can use it to register JavaScript functions that is executed later in the page or in an event handler.

If you want your code to execute LATER then I recommend using the RegisterStartupScript and IsStartUpScriptRegistered methods instead. This code executes at the end of the page after all the items on the page have finished loading. Here is the same code as above, but with the StartUp versions.

if (!Page.ClientScript.IsStartupScriptRegistered("MyMadeUpNameHere"))
{
    string jscript = "alert('test here');";
    Page.ClientScript.RegisterStartupScript(this.GetType(), "MyMadeUpNameHere", jscript, true);
}

Like I said before, if you had more complex logic you could put it earlier in the page and just execute the function using the RegisterStartupScript. You can also put your complex logic in functions in JScript include and use the RegisterClientScriptInclude. See here for more details on that.

I don’t know why I can never remember which methods to use for showing a JavaScript alert or executing other JavaScript code when the page loads, so I am writing in down for me and everyone else to have.

Tuesday, June 21, 2011

Get usage for all columns in a SQL Server database

First if you just want to know how one column is used you want to use the UI or check out my other entry. If however you want to scan your entire database and determine the dependencies (usage) of each and every column in the database then stay here. The code found in either of these places will only look at the database references (stored procedures, functions, views, triggers). This means if you are using something like SSIS, LINQ, Entity Framework, embedded SQL in your code, etc you will need to check these area on your own.

With that said, I really just continued the thought from my other entry (same as the one I noted above). The first step is to convert the stored procedure used there to one that just dumps the data to a table for later querying. I call this table ColumnUsage. I also add a TagName to the table so that you can easily identify your results among other results in that table. That way you can compare results over time, or just support multiple users. The table will be created the first time the stored procedure is executed.

Here is the new stored proc

/****** Object:  StoredProcedure [dbo].[UTL_07_WriteColumnUsageToColumnUsageTable]    Script Date: 06/21/2011 15:25:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[UTL_07_WriteColumnUsageToColumnUsageTable]
    @vcTableName varchar(100),
    @vcColumnName varchar(100),
    @tagName varchar(100)
AS
/************************************************************************************************
DESCRIPTION:    writes all stored procedures, views, triggers
        and user-defined functions that reference the
        table/column passed into this proc into the ColumnUsage table.
   
PARAMETERS:
        @vcTableName - table containing searched column
        @vcColumnName    - column being searched for
        @tagName - a name you make up to so you can later query for your results

        This procedure must be installed in the database where it will
        be run due to it's use of database system tables.

USAGE:   
  
  UTL_07_WriteColumnUsageToColumnUsageTable 'schema.tablename', 'columnname', 'tagName'
   
AUTHOR:    Karen Gayda

DATE: 07/19/2007

MODIFICATION HISTORY:
WHO        DATE        DESCRIPTION
---        ----------    -------------------------------------------
Brent Vermilion    06.21.2011    Recreated such that writes to a table instead of output
*************************************************************************************************/
SET NOCOUNT ON

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'ColumnUsage'))
BEGIN
    CREATE TABLE dbo.ColumnUsage
        (
        ID int NOT NULL IDENTITY (1, 1),
        TableName varchar(500) NOT NULL,
        ColumnName varchar(500) NOT NULL,
        UsedByType varchar(50) NOT NULL,
        UsedByName varchar(2000) NOT NULL,
        TagName varchar(100) NOT NULL
        )  ON [PRIMARY]

    ALTER TABLE dbo.ColumnUsage ADD CONSTRAINT
        PK_ColumnUsage PRIMARY KEY CLUSTERED
        (
        ID
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

END

insert into dbo.ColumnUsage(TableName, ColumnName, UsedByType, TagName, UsedByName)
SELECT DISTINCT @vcTableName, @vcColumnName, 'Stored Procedure', @tagName, SUBSTRING(o.NAME,1,60) AS [Procedure Name]
        FROM sysobjects o
        INNER JOIN syscomments c
            ON o.ID = c.ID
        WHERE     o.XTYPE = 'P'
            AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
    ORDER BY  [Procedure Name]


insert into dbo.ColumnUsage(TableName, ColumnName, UsedByType, TagName, UsedByName)
SELECT DISTINCT @vcTableName, @vcColumnName, 'View', @tagName, SUBSTRING(o.NAME,1,60) AS [View Name]
        FROM sysobjects o
        INNER JOIN syscomments c
            ON o.ID = c.ID
        WHERE     o.XTYPE = 'V'
            AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%'             
    ORDER BY  [View Name]


insert into dbo.ColumnUsage(TableName, ColumnName, TagName, UsedByName, UsedByType)
SELECT DISTINCT @vcTableName, @vcColumnName, @tagName, SUBSTRING(o.NAME,1,60) AS [Function Name],
        CASE WHEN o.XTYPE = 'FN' THEN 'Scalar Function'
            WHEN o.XTYPE = 'IF' THEN 'Inline Function'
            WHEN o.XTYPE = 'TF' THEN 'Table Function'
            ELSE '? Function'
        END
        as [Function Type]
        FROM sysobjects o
        INNER JOIN syscomments c
            ON o.ID = c.ID
        WHERE     o.XTYPE IN ('FN','IF','TF')
            AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
    ORDER BY  [Function Name]


insert into dbo.ColumnUsage(TableName, ColumnName, UsedByType, TagName, UsedByName)
SELECT DISTINCT @vcTableName, @vcColumnName, 'Trigger', @tagName, SUBSTRING(o.NAME,1,60) AS [Trigger Name]
        FROM sysobjects o
        INNER JOIN syscomments c
            ON o.ID = c.ID
        WHERE     o.XTYPE = 'TR'
            AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%'     
    ORDER BY  [Trigger Name]

Now that we have that we can write another stored proc that will loop over every column in our database and call the above stored procedure for each column. Please note, depending on how complex your schema, the number of stored procedures, number of columns, etc this query could take quite a long time. Here is the stored proc to scan your database.


/****** Object:  StoredProcedure [dbo].[UTL_08_WriteAllColumnUsageToColumnUsageTable]    Script Date: 06/21/2011 15:25:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[UTL_08_WriteAllColumnUsageToColumnUsageTable]
    @TagName varchar(100)
AS
/************************************************************************************************
DESCRIPTION:    writes all stored procedures, views, triggers
        and user-defined functions that reference the
        table/column passed into this proc into the ColumnUsage table.
   
PARAMETERS:
        @TagName - a name you make up to so you can later query for your results

        This procedure must be installed in the database where it will
        be run due to it's use of database system tables.

USAGE:   
  
  UTL_08_WriteAllColumnUsageToColumnUsageTable 'tagName'
   
AUTHOR:    Brent Vermilion

DATE: 06/21/2011
*************************************************************************************************/

Declare @TableName as nvarchar(512)
Declare @ColumnName as nvarchar(300)

Declare ColumnCursor CURSOR FAST_FORWARD FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS order by TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME

OPEN ColumnCursor
FETCH NEXT FROM ColumnCursor
into @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- do row specific stuff here
    exec UTL_07_WriteColumnUsageToColumnUsageTable @TableName, @ColumnName, @TagName

    FETCH NEXT FROM ColumnCursor
    into @TableName, @ColumnName
END

CLOSE ColumnCursor
DEALLOCATE ColumnCursor

After executing the either of the stored procedure on your database you can answer some very useful questions.

  1. What columns are not being used?
  2. select distinct allcols.TABLE_SCHEMA + '.' + allcols.TABLE_NAME, COLUMN_NAME, UsedByName from
    INFORMATION_SCHEMA.COLUMNS allcols
    left outer join ColumnUsage usage
    on (allcols.TABLE_SCHEMA + '.' + allcols.TABLE_NAME = usage.TableName and allcols.COLUMN_NAME = usage.ColumnName)
    where UsedByName is null

  3. What is using a particular column?
    select * from ColumnUsage where TableName = 'MyTableHere' and ColumnName = 'MyColumnHere'

Friday, June 17, 2011

Deploying RIA Services seems a bit too complicated

I have spent many hours troubleshooting deployments my Silverlight application (server side) just to figure out that I have the wrong version of a .dll. It seems that the easiest way to guarantee that you have the correct assemblies is to open your web project and select all the References and change the Copy Local property to True. I didn’t notice more than a second or two delay in build time and then when I deploy I know that I will get all the assemblies I need.

Generally, you don’t need to worry about the references changing except when you get a service pack, a new version, upgrade, etc so if you like you can keep the Copy Local set to False most of the time. Then when your references change be sure to change the Copy Local back to True.

I’m so lost as to why this is so difficult.

If anyone has a better way, please let me know. What price can you put on sanity? Smile

Wednesday, June 15, 2011

Renaming a Visual Studio Project

I had a project I wanted to rename from ProjectA to ProjectB. I wasn’t quite sure how to do this. I mean, I wanted the namespaces to change, the default namespaced (used for new files), project file names, filenames, etc. In my case, I had multiple projects in my solution. I wasn’t sure how this would work.

In the end, I used a search and replace tool to change every reference in every fiel from ProjectA to ProjectB. Then I renamed all the files. To find the files that need to be renamed, you might want to check here. Then I opened the solution up in Visual Studio 2010 (VS2010) and it just worked! So cool! Thanks to most files being text or XML this worked seamlessly.

Using Batch File or Command Line to recursively find all files that have a filename that contains a particular string

It sounds like a tall order at first, but then it also sounds like something that should be built into Windows. In particular, I am using Windows 7, but I assume it works on XP as well.

The solution is actually quite simple and is indeed built into Windows 7.

dir /s /b *my string*

This will give you something similar to:

c:\temp\This is a test of my string.txt

c:\mydocs\My string could be found here.txt

As shown above it will search the current directory and all directories below the current one for files that contain ‘my string’. You can also specify something like this to specify the directory you want to start searching in regardless of your current directory.

dir /s /b “c:\mydocs\*my string*”

You can use all the standard wild card syntax such as the following to search only .html files.

dir /s /b “c:\mydocs\*my string*.html”

In case you are wondering the /s is for recursive and the /b is to return just the path\filename (no file info such as date, size, etc).

Easy Way to remove Visual Studio 2010 project from Visual SourceSafe

Visual SourceSafe creates .scc files when you add a Visual Studio 2010 (VS2010) project to Visual SourceSafe. The easiest way I know to remove a project from Visual SourceSafe version control (while still leaving the actual files in SourceSafe) is to do the following. You might want to do this if you want to make a copy of your project to use as a starting point for your new project or maybe want to share the project with someone else or maybe you are changing version control systems. Regardless, below are the steps to do this.

  1. Make a copy of your project using copy and paste in Windows Explorer.
  2. Recursively Search the project directory for all files that end in .scc. You can do this using the Search in Windows 7 or Search Companion (I think it was called in XP).
  3. Delete the results found.
  4. Open solution in VS2010. It will complain and eventually ask if you want to permanently remove the bindings. Do so for each project in your solution. You can go to the File menu | Source Control | Change Source Control… If there are any projects that are still bound, you can unbind them.

That’s it. Relatively easy actually.

Compiler error with VS2010 SP1 and SL 4 with WCF RIA Services and Entity Framework 4

Did you have a VS2010 project (before installing VS2010 SP1 or RIA Service Pack 1) that used Silverlight 4 and WCF RIA Services and Entity Framework 4 (EF4) and now that VS2010 Service Pack 1 is installed (or maybe some other framework) your Silverlight Project no longer compiles. Chances are if you are using RIA Services you may have created files that you want to share (called Shared Code)on the Server and the SL Client. This is done in RIA by using the .shared.cs file extension on a filename.

After I installed either VS2010 SP1 or RIA Services SP1 and then I tried to compile my project and got a compiler error similar to:

error CS0102: The type 'MyApp.Model.MyClassHere' already contains a definition for 'MyPropertyHere'

I learned that this may be a RIA Services SP1 regression bug from this forum. The beta had issues also.

It appears that this MAY be fixed now, but I’m not sure since I don’t have an issue exactly like what they had. I only have the issue if I have to shared files and one the classes (in one of the files) has a property or method that has a return type that is the other class type. If I get rid of that dependency and just return the object type instead then it compiles fine. While returning the type object, I have to cast every time I use it and that is silly and kind of a pain / ugly.

I hope that helps someone. If anyone else has this issue I’d love to know about it.

Friday, June 3, 2011

How to Download File from inside an UpdatePanel in ASP.NET

PartialPageRendering when used with the UpdatePanel is a great thing in general. One problem comes when you want to mess with the Response and call things like End which is required when you return want to return a file instead of the page you were on. An example of this is a GridView with a Download button below it. If you are not using the UpdatePanel then there is no problem and clicking the Download button will all you to download the file as expected. There are lots of posts on the web on how to do this so I won’t go into the code. However, it involves calling things like Response.Clear(), Response.ContentType, Response.AddHeader(), Response.End().
Now let’s assume you put the GridView in an UpdatePanel. All those Response calls noted above are things that the PartialPageRendering and UpdatePanel don’t know what to do with because they were expected something like the page that made the request. So we need to work around the issue. I found some solutions on the internet, but didn’t really find them very easy or straight forward. So, I came up with my own.

Solution 1

One solution is to put the Download button outside the UpdatePanel. This tends to work best, but is not always possible.

Solution 2

If you can’t do solution 1, then consider faking it. What I mean is move the Download button which in my case is implemented as a LinkButton outside the UpdatePanel and hide it using css. Then add a Hyperlink inside the UpdatePanel. Now, on your page load you can set the hyperlink to have the same postback url as the LinkButton that is outside the UpdatePanel. That way when you click the hyperlink inside the UpdatePanel you are effectively doing the same thing as if you clicked the hidden LinkButton that is outside the UpdatePanel. Fairly simple.
Here is the snippets of code to help you see what I am talking about.
Here is the HyperLink that is inside the UpdatePanel and is also the button that you will be clicking in the Browser.
<asp:HyperLink ID="btnExportToExcelClicker" runat="server" Text="Export all rows to Excel" />

Here is the LinkButton that is invisible to the user and is outside the UpdatePanel
<asp:LinkButton ID="btnExportToExcel" runat="server" onclick="btnExportToExcel_Click" Text="Export all rows to Excel" style="display:none;" />

Here is the code-behind. In my code btnExportToExcelClicker is the button that is in the UpdatePanel and btnExportToExcel is the button that is outside the UpdatePanel. You will actually be clicking btnExportToExcelClicker because btnExportToExcel is hidden.
protected void Page_Load(object sender, EventArgs e)
{
    btnExportToExcelClicker.NavigateUrl = Page.ClientScript.GetPostBackClientHyperlink(btnExportToExcel, null);
}

This technique uses GetPostBackClientHperlink and can be expanded for other purposes and other controls. In some cases you may need to consider using GetPostBackEventReferences, though I haven’t tried it.

UPDATED: November 5, 2013

Solution 3

This is my new favorite solution due to its simplicity. This involves creating a new page in your application. Call it ReportDownload.aspx (or whatever you desire). It is critical that it not have an update panel. Best choice is usually to not even use a master page for this. The reason is that we are not going to use anything on the ReportDownload.aspx file. We will however change the ReportDownload.aspx.cs file. On the page load just put your download streaming code that would be something similar to the following:

var response = Page.Response;
response.Clear();
response.AddHeader("content-disposition", string.Format("attachement; filename=\"{0}\"", "Report.pdf"));
response.ContentType = "application/octet-stream";
response.WriteFile(@"c:\temp\DB.pdf");
response.End();

You just need to put this in the Load or PreRender events. To test this, just go to the new page you created in your favorite browser. You should see the file you specified in the code above. Now that you know that going to the url works, it is just a matter of using that url anywhere on a page (including in an UpdatePanel). I used it in LinkButton or HyperLink. You can pass a code (i.e. a record id, etc) in the url to generate the url. The nice thing is you can also incorporate security on the ReportDownload.aspx as well.