Ole Automation Procedures - SQL Server special settings

Special settings that you need to apply in some cases like sending out SMSes through SQL Server ...

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

Generic lists comparer - gridview sorting

Following is a good-code-snippet to sort the generic lists in C#.

Another snippet presented right below it can be used to sort the GridView in ASP .net.

-------------------------------------------------------------------------------------

public class GenericComparer<T> : IComparer<T>

{

    private SortDirection sortDirection;  

    public SortDirection SortDirection

    {

        get { return this.sortDirection; }

        set { this.sortDirection = value; } 

    }

    private string sortExpression; 

    public GenericComparer(string sortExpression, SortDirection sortDirection)

    {

        this.sortExpression = sortExpression;

        this.sortDirection = sortDirection; 

    }

    public int Compare(T x, T y)

    {

        PropertyInfo propertyInfo = typeof(T).GetProperty(sortExpression);

        IComparable obj1 = (IComparable)propertyInfo.GetValue(x, null);

        IComparable obj2 = (IComparable)propertyInfo.GetValue(y, null);

        if (SortDirection == SortDirection.Ascending)

        {

            return obj1.CompareTo(obj2);

        }

        else return obj2.CompareTo(obj1); 

     }

}

--------------------------------------------------

Capture the "sorting" event of the ASP .NET gridview and put the following code in the event handler. You need to have the above GenericComparer class in order to make this work. Enjoi!

        private SortDirection GridViewSortDirection

        {

            get

            {

                if (ViewState["sortDirection"] == null)

                    ViewState["sortDirection"] = SortDirection.Ascending;

                return (SortDirection)ViewState["sortDirection"];

            }

            set { ViewState["sortDirection"] = value; }

        }

        protected void gvEvents_Sorting(object sender, GridViewSortEventArgs e)

        {

            if (GridViewSortDirection == SortDirection.Ascending)

            {

                GridViewSortDirection = SortDirection.Descending;

            }

            else

            {

                GridViewSortDirection = SortDirection.Ascending;

            }

            events.Sort(new GenericComparer<EWent.BusinessEntities.Event>(e.SortExpression, GridViewSortDirection));

            PopuplateEventsGridview();

        }

--------------------

Looping through temporary table and CASE statements

Below is a nice stored procedure written to demonstrate looping through a temporary table and using the CASE statement.

Hope this helps.

Ishan

 <pre style="font-family:arial;font-size:12px;border:1px dashed #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;;background-image:URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhg8j6CvyCjQOs5FbK6wq5m1sqYLT9_LTBPyeVOoHj6TzkggyesWA5YsBav67dqWRLGY24VJqSiSFmkt99TTg79vfwDwWdmSywnWEW4stFYhHpIhcdQJnxdNjvnIIlSTMFfkV9OIj3I-s_e/s320/codebg.gif);padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;"> -- Insert statements for procedure here   

CREATE TABLE #temp_tblMessage

(

nId INT IDENTITY(1,1),

aEventId INT,

nGlobalCaseId INT,

mMsgContent NTEXT,

mMsgHTMLContent NTEXT,

dCDServerTime DATETIME,

nType INT,

nMediaType INT,

nStatus INT,

)

INSERT #temp_tblMessage

SELECT aEventID,

nGlobalCaseID,

mMsgContent,

mMsgHTMLContent,

dCDServerTime,

nType,

nMediaType,

nStatus

FROM dbo.tblMessage

WHERE nGlobalCaseId = @nInteractionId AND (nType = 32 OR nType = 64 or nType = 96)

DECLARE @tFullMessage NVARCHAR(MAX)

DECLARE @tHTMLMessageContent NVARCHAR(MAX)

DECLARE @tPlainMessageContent NVARCHAR(MAX)

DECLARE @tMessage NVARCHAR(MAX)

DECLARE @tMessagePrefix NVARCHAR(MAX)

DECLARE @dMessageDatetime DATETIME

DECLARE @nType INT

DECLARE @nCount INT

DECLARE @nIndex INT

SET @tFullMessage = ''

SET @nIndex = 0

SELECT @nCount = Count(nId) From #temp_tblMessage

WHILE (@nCount > @nIndex)

BEGIN

SET @nIndex = @nIndex + 1

SELECT @dMessageDatetime = dCDServerTime,

@tPlainMessageContent=mMsgContent,

@tHTMLMessageContent = mMsgHTMLContent,

@tMessagePrefix =

CASE nType

WHEN 32 THEN 'Message sent by you at : '

WHEN 64 THEN 'Message from System at : '

WHEN 96 THEN 'Message from System at : '

END

FROM #temp_tblMessage

WHERE nId = @nIndex

SET @tMessagePrefix = @tMessagePrefix + CAST(@dMessageDatetime AS NVARCHAR(255)) + CHAR(13)

SET @tMessage = ISNULL(NULLIF(@tPlainMessageContent, ''), @tHTMLMessageContent)

SET @tMessage = @tMessagePrefix + @tMessage

SET @tFullMessage = @tFullMessage + @tMessage + CHAR(13)

END

SET @tMessageContent = @tFullMessage

</code></pre>

Ever got stuck due to SQL Server processes ?

Ever got stuck due to SQL Server processes ?

Here is an easy way of finding the culprit and resolving :)

 select * from master..sysprocesses where blocked <> 0  

dbcc inputbuffer(<processid>)

kill <processid>

Code formatter for the bloggers

The below blogspot helps you format the C#, VB code to a good-readable format for the blogger :)

http://codeformatter.blogspot.com/2009/06/about-code-formatter.html

ASP .NET Membership settings - that worked :)

Many a time, there are situations where we have to bang our head for getting asp .net membership login controls to work!
Happens,, never mind :)
Below is the snip in the web config, which worked for me and I'm sure it will work for you all too... Mainly that happens when we miss out attributes like "applicationName" etc.

Hope this helps.

 <membership defaultProvider="CustomAspNetSqlMembershipProvider">  
                <providers>  
     <clear/>  
                     <add name="CustomAspNetSqlMembershipProvider"   
        type="System.Web.Security.SqlMembershipProvider"   
        connectionStringName="eWentDatabase"   
        applicationName="/"   
        minRequiredPasswordLength="6"  
        requiresUniqueEmail="true"   
        requiresQuestionAndAnswer="true"   
        passwordFormat="Hashed" />  
                </providers>  
           </membership>  
   <roleManager enabled="true" defaultProvider="CustomRoleProvider">  
    <providers>  
     <add name="CustomRoleProvider"   
        type="System.Web.Security.SqlRoleProvider"   
        applicationName="/"   
        connectionStringName="eWentDatabase"   
        />  
    </providers>  
   </roleManager>  

Generic method to populate dropdown list in asp .net

An attempt of making the dropdown list population easier for everyday projects :)

 ///  
 /// Populates the drop down list box with given list type.  
 ///  
 /// The dropdown list to be populated.  
 /// The data source.  
 /// The datatext field for the dropdown list.  
 /// The datavalue field for the dropdown list.  
 /// The selected value, if any.  
 /// Whether to add an empty value (e.g.--Select--).  
 public static void PopulateDropDownList(DropDownList dropDownList, IList dataSource, string dataText, string dataValue, string selectedValue, bool addEmptyValue)  
 {  
 if (dropDownList != null && dataSource != null)  
 {  
 if (dropDownList.Items != null)  
 {  
 dropDownList.Items.Clear();  
 }  
 dropDownList.DataSource = dataSource;  
 dropDownList.DataTextField = dataText;  
 dropDownList.DataValueField = dataValue;  
 dropDownList.DataBind();  
 if (addEmptyValue)  
 {  
 dropDownList.Items.Insert(0, new ListItem("--Select--", "-1"));  
 }  
 if (!string.IsNullOrEmpty(selectedValue))  
 {  
 dropDownList.SelectedValue = selectedValue;  
 }  
 }  
 }  

Formatting SQL Server code

Need to format some lengthy difficult to read sql server code ? Here are a couple of online instant code-formatter :)

http://www.dpriver.com/pp/sqlformat.htm

http://www.sqlinform.com/free_online_sw.html