VB.NET Extending the MailMessage to have the ability to save the mail message

Problem:

The System.Net.Mail.MailMessage is badly missing the option of saving the Message as a file. Though there is a workaournd by using the SpecifiedPickupDirectory option of the SmtpClient to write the mail message ot the specific folder. This option however doesn't have flexibility in naming the files. In a multi-threaded parallel execution environment, it is difficult to identify and rename the specific files post writing to disk. There ought to be a better solution to handle this problem.

Solution:

You can extend the MailMessage class and add a Save method yourself. The Send method of the SmtpClient creates and uses MailWriter object to write out the mail message and it uses fileMailWriter when the SpecificPickupDirectory is specified as the DeliveryMethod. Create your own MailWriter object using FileStream and invoke the internal Send method, passing your MailWriter object. Check out this Code Project Article for more details. The code samples given there are for the C# language. Just in case you need, given below the same code for the Extension in VB.NET.

---

Imports System
Imports System.Net.Mail
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.IO
Imports System.Reflection
Imports System.Runtime.CompilerServices

Module MailMessageExtension
    <Extension()>
    Public Sub Save(ByVal msg As MailMessage, FileName As String)

        Dim asm As Assembly = New SmtpClient().GetType().Assembly
        Dim _mailWriterType As Type = asm.GetType("System.Net.Mail.MailWriter")


        Using _fileStream As FileStream = New FileStream(FileName, FileMode.Create)


            ' Get reflection info for MailWriter contructor
            Dim _mailWriterContructor As ConstructorInfo = _mailWriterType.GetConstructor(BindingFlags.Instance Or BindingFlags.NonPublic, Nothing, New Type() {GetType(Stream)}, Nothing)

            ' Construct MailWriter object with our FileStream
            Dim _mailWriter As Object = _mailWriterContructor.Invoke(New Object() {_fileStream})

            ' Get reflection info for Send() method on MailMessage
            Dim _sendMethod As MethodInfo = New MailMessage().GetType().GetMethod("Send", BindingFlags.Instance Or BindingFlags.NonPublic)


            ' Call method passing in MailWriter

            _sendMethod.Invoke(msg, BindingFlags.Instance Or BindingFlags.NonPublic, Nothing, New Object() {_mailWriter, True, True}, Nothing)

            ' Finally get reflection info for Close() method on our MailWriter
            Dim _closeMethod As MethodInfo = _mailWriter.GetType().GetMethod("Close", BindingFlags.Instance Or BindingFlags.NonPublic)

            ' Call close method
            _closeMethod.Invoke(_mailWriter, BindingFlags.Instance Or BindingFlags.NonPublic, Nothing, New Object() {}, Nothing)
        End Using

    End Sub
End Module

---

You may observe that you need to create extensions as a module.

Hypothetical Indexes in SQL Server

Problem:


While attempting to rebuild all indexes on a table, I am getting the following error:

Cannot rebuild hypothetical index '_dta_index_table_name_10_992214785__K9' online.

What is this hypothetical index and I cannot see any such indexes in the object explorer against the specific table. How can I get over this error.


By: Anonymous


Solution:


You or someone who works on the database should have used Database Tuning Advisor (DTA) to analyze the performance aspect of the tables and indexes. DTA creates hypothetical Indexes when it checks queries & tables for columns to measure the performance gain that such indexes may offer and thus come up with a recommendation. Usually when the DTA completes its analysis, it deletes all the hypothetical indexes if it cleanly finishes but in some cases, these indexes may be left over.


For a query or a stored procedure execution, after an initial recompile is triggered, the optimizer uses some of the information from these hypothetical indexes, which is out of date, and hence incorrectly determines that a recompile is needed again. During the ensuing recompiles, the information from the hypothetical indexes is never refreshed, and so the optimizer remains in a recompile loop. Hence, dropping hypothetical indexes is a better choice.


Clustered hypothetical indexes can be dropped with DROP INDEX statement. Nonclustered hypothetical indexes can be dropped with DROP STATISTICS statement.

You may find out all hypothetical indexes using this query:


WITH [hypo] AS (

SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Table] , QUOTENAME([i].[name]) AS [Index_or_Statistics], 1 AS [Type]
FROM sys.[indexes] AS [i]
JOIN sys.[objects] AS [o] ON i.[object_id] = o.[object_id]
WHERE 1=1
AND INDEXPROPERTY(i.[object_id], i.[name], 'IsHypothetical') = 1
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1

UNION ALL

SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(o.[object_id])) AS [Table], QUOTENAME([s].[name]) AS [Index_or_Statistics], 2 AS [Type]
FROM sys.[stats] AS [s]
JOIN sys.[objects] AS [o] ON [o].[object_id] = [s].[object_id]
WHERE [s].[user_created] = 0
AND [o].[name] LIKE '[_]dta[_]%'
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1
)

SELECT [hypo].[Table],[hypo].[Index_or_Statistics], 

CASE [hypo].[Type]
WHEN 1 THEN 'DROP INDEX ' + [hypo].[Index_or_Statistics] + ' ON ' + [hypo].[Table] + ';'
WHEN 2 THEN 'DROP STATISTICS ' + [hypo].[Table] + '.' + [hypo].[Index_or_Statistics] + ';'
END AS [Drop Stmt]
FROM [hypo]

Hope this helps you.