Format date from MM/DD to MM/DD/YYYY

CREATE FUNCTION [dbo].[FORMATEDATE](


@INPUT VARCHAR(100),

@FORMAT VARCHAR(100)

)

RETURNS VARCHAR(100)

AS

BEGIN

DECLARE @DAY VARCHAR(100),@MONTH VARCHAR(100),@YEAR VARCHAR(100)



IF @INPUT IS NOT NULL

BEGIN

IF(UPPER(@FORMAT) = 'MM/DD')

BEGIN

SET @MONTH = SUBSTRING(@INPUT,0,CHARINDEX('/',@INPUT))

SET @DAY = SUBSTRING(@INPUT,CHARINDEX('/',@INPUT)+1,LEN(@INPUT))



IF(@MONTH > MONTH(GETDATE())) SELECT @YEAR = YEAR(GETDATE())

ELSE IF(@MONTH = MONTH(GETDATE()) AND @DAY >= DAY(GETDATE())) SELECT @YEAR = YEAR(GETDATE())

ELSE SELECT @YEAR = YEAR(GETDATE()) + 1



IF(@MONTH = 2 AND @DAY > 28)

BEGIN

IF (SELECT DBO.[ufn_IsLeapYear](@YEAR)) <> 1

BEGIN

SET @YEAR = @YEAR + 4-@YEAR%4

END

END

SET @INPUT = @MONTH + '/' + @DAY + '/' + @YEAR

END

END

RETURN @INPUT



END

GO

dump transaction in sqlserver 2008

-- =============================================
-- Author: Asif Ghanchi
-- Create date: 11/24/2010
-- Description: Dump transaction for Sql Server 2008
-- =============================================
CREATE PROCEDURE dump_transaction
@database_name varchar(100)
AS
BEGIN

declare @name varchar(100)

select @name = name from sys.database_files where type = 1

declare @query nvarchar(max)

SET @query = 'USE '+ @database_name +';

ALTER DATABASE '+ @database_name +'
SET RECOVERY SIMPLE;

DBCC SHRINKFILE ('''+ @name +''');

ALTER DATABASE '+ @database_name +'
SET RECOVERY FULL;'

exec sp_executesql @query

END

EXCLUDE COLUMN IN SQL SERVER

In one of my project i want a select query on table which have bunch of columns and want to exclude a column "Reason" from them.

Query

DECLARE @tableName SYSNAME,


@excludeColumnName SYSNAME,

@whereClause VARCHAR(MAX)



SET @tableName = 'EXPCONTACT'

SET @excludeColumnName = 'REASON'



DECLARE @query NVARCHAR(MAX)

DECLARE @select VARCHAR(MAX)



select @select = COALESCE(@select + ',','') + COLUMN_NAME

from INFORMATION_SCHEMA.COLUMNS

where TABLE_NAME = @tableName and COLUMN_NAME <> @excludeColumnName

order by ORDINAL_POSITION



set @query = 'SELECT ' + @select + char(13) +

'FROM ' + UPPER(@tableName) +

case when @whereClause is not null then char(13) + 'where ' + @whereClause else '' end



print @query

exec sp_executesql @query

PROPERSTATEMENT function in sqlserver to proper statement like excel

I find in one of my project to use some function like proper in excel which i already find from google. But i am having bunch of record to proper even want to sepearate some words in the sentence.

e.g.
query : select properstatement('asifghanchi','asif')
output : asif ghanchi

Description : asif is word to seperate and apply proper to whole statement.

Code:

USE [DATABASE]
GO

/****** Object: UserDefinedFunction [dbo].[PROPERSTATEMENT] Script Date: 08/04/2010 11:05:22 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROPERSTATEMENT]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[PROPERSTATEMENT]
GO

USE [DATABASE]
GO

/****** Object: UserDefinedFunction [dbo].[PROPERSTATEMENT] Script Date: 08/04/2010 11:05:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[PROPERSTATEMENT]
(
@input varchar(8000),
@properinput varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
IF (LEN(@properinput) = 0 OR LEN(@input) = 0)
RETURN DBO.PROPERCASE(@input)
ELSE IF (CHARINDEX(',',@properinput) = 0 AND CHARINDEX(@properinput,@input) = 0)
RETURN DBO.PROPERCASE(@input)
ELSE IF (CHARINDEX(',',@properinput) = 0 AND CHARINDEX(@properinput,@input) = 1)
RETURN DBO.PROPERCASE(@properinput) + ' ' + DBO.PROPERCASE(REPLACE(@input,@properinput,''))
ELSE IF (CHARINDEX(',',@properinput) = 0 AND CHARINDEX(@properinput,@input) > 1)
RETURN DBO.PROPERCASE(SUBSTRING(@input,1,CHARINDEX(@properinput,@input)-1)) + ' ' + DBO.PROPERCASE(@properinput) + ' ' + DBO.PROPERCASE(SUBSTRING(@input,CHARINDEX(@properinput,@input)+LEN(@properinput),LEN(@input)))
ELSE IF CHARINDEX(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1),@input) = 0
RETURN [dbo].[PROPERSTATEMENT](@input,SUBSTRING(@properinput,CHARINDEX(',',@properinput)+1,LEN(@properinput)))
ELSE IF CHARINDEX(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1),@input) = 1
RETURN DBO.PROPERCASE(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1)) + ' ' + [dbo].[PROPERSTATEMENT](REPLACE(@input,SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1),''),SUBSTRING(@properinput,CHARINDEX(',',@properinput)+1,LEN(@properinput)))
ELSE IF CHARINDEX(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1),@input) > 1
RETURN [dbo].[PROPERSTATEMENT](SUBSTRING(@input,1,CHARINDEX(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1),@input) - 1) ,SUBSTRING(@properinput,CHARINDEX(',',@properinput)+1,LEN(@properinput))) + ' ' + DBO.PROPERCASE(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1)) + ' ' + [dbo].[PROPERSTATEMENT](SUBSTRING(@input,CHARINDEX(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1),@input) + LEN(SUBSTRING(@properinput,1,CHARINDEX(',',@properinput)-1)),LEN(@input)),SUBSTRING(@properinput,CHARINDEX(',',@properinput)+1,LEN(@properinput)))
ELSE
RETURN @input
RETURN @input
END

GO


Used function in properstatement : proper

Code:

USE [database]
GO

/****** Object: UserDefinedFunction [dbo].[PROPERCASE] Script Date: 08/03/2010 18:06:25 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROPERCASE]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[PROPERCASE]
GO

USE [database]
GO

/****** Object: UserDefinedFunction [dbo].[PROPERCASE] Script Date: 08/03/2010 18:06:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[PROPERCASE]
(
--The string to be converted to proper case
@input varchar(8000)
)
--This function returns the proper case string of varchar type
RETURNS varchar(8000)
AS
BEGIN
IF @input IS NULL
BEGIN
--Just return NULL if input string is NULL
RETURN NULL
END

--Character variable declarations
DECLARE @output varchar(8000)
--Integer variable declarations
DECLARE @ctr int, @len int, @found_at int
--Constant declarations
DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int

--Variable/Constant initializations
SET @ctr = 1
SET @len = LEN(@input)
SET @output = ''
SET @LOWER_CASE_a = 97
SET @LOWER_CASE_z = 122
SET @Delimiter = ' ,-'
SET @UPPER_CASE_A = 65
SET @UPPER_CASE_Z = 90

WHILE @ctr <= @len BEGIN --This loop will take care of reccuring white spaces WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
SET @ctr = @ctr + 1
END

IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
BEGIN
--Converting the first character to upper case
SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
END
ELSE
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
END

SET @ctr = @ctr + 1

WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
BEGIN
IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
BEGIN
SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
END
ELSE
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
END
SET @ctr = @ctr + 1
END

END
RETURN @output
END

GO

Get the value of static variable using reflection

I come to this code when i want to create new company for my application when new company is created i want to create whole folder structure again for uploading their documents or images with their company folder name.

Code

public static void CreateCorporateStructure(String _foldername)
    {
        try
        {
            Type typeOfclsConstant = typeof(clsConstant);
            FieldInfo[] objFieldInfo = typeOfclsConstant.GetFields();
            foreach (FieldInfo fi in objFieldInfo.Where(fi => fi.Name.Contains("_POSTFIX")))
            {
                Directory.CreateDirectory(HttpContext.Current.Server.MapPath("../" + clsConstant.COMPANY_PREFIXURL) + _foldername + Convert.ToString(fi.GetValue(null)).Substring(0, Convert.ToString(fi.GetValue(null)).Length - 1));
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Explaination

• foreach (FieldInfo fi in objFieldInfo.Where(fi => fi.Name.Contains("_POSTFIX"))) is used to fetch number of fields from static class where variable name is ending with "_POSTFIX".
• fi.GetValue(null)) which give the static value for field or variable.

Give some comment for above code if it is useful. If you find any suggestion than please reply.

Microsoft Ajax Editor Control With ToolsFile

I come to ajxt toolkit editor control and find that there's no option or property like radeditor where we can define the path of ToolsFile to give dynamic look to toolbars.

I try to implement a extender control for Editor which having the property ToolsFile

Example:

Extender Control
------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AjaxControlToolkit.HTMLEditor;
using System.ComponentModel;
using System.Web.UI;
using System.Xml.Linq;
using System.Web;
using System.Reflection;
using AjaxControlToolkit.HTMLEditor.ToolbarButton;
using System.Collections.ObjectModel;

namespace SazzControls
{
    public class editor : Editor
    {
        ///

        /// Created by Asif Ghanchi Dated On June 21 2010
        ///

        #region " Local Variable "
        #endregion
        #region " Properties"

        [Description("Relative path of Tools File"), Category("Appearance"), Bindable(BindableSupport.Yes),
        UrlProperty("*.xml")]
        public string ToolsFile
        {
            get
            {
                return (Convert.ToString(ViewState["ToolsFile"]) == null) ? string.Empty : Convert.ToString(ViewState["ToolsFile"]);
            }
            set
            {
                ViewState["ToolsFile"] = value;
            }
        }

        #endregion
        #region " Constructor "
        #endregion
        #region " Control Methods "

        protected override void FillTopToolbar()
        {
            if (ToolsFile != string.Empty)
            {
                XDocument xd = XDocument.Load(HttpContext.Current.Server.MapPath(ToolsFile));
                Assembly asm = Assembly.GetAssembly(typeof(AjaxControlToolkit.HTMLEditor.ToolbarButton.CommonButton));

                foreach (XElement tool in xd.Descendants("tool"))
                {
                    Type t = asm.GetTypes().FirstOrDefault(cb => cb.Namespace == "AjaxControlToolkit.HTMLEditor.ToolbarButton" && cb.Name == tool.FirstAttribute.Value);
                    if (t != null)
                    {
                        CommonButton _tool = (CommonButton)Activator.CreateInstance(t);
                        TopToolbar.Buttons.Add(_tool);
                        foreach (XElement option in tool.Descendants("option"))
                        {
                            Collection opts = null;
                            AjaxControlToolkit.HTMLEditor.ToolbarButton.SelectOption opt;
                            if (tool.FirstAttribute.Value == "FontName") opts = ((FontName)_tool).Options;
                            else if (tool.FirstAttribute.Value == "FontSize") opts = ((FontSize)_tool).Options;
                            opt = new AjaxControlToolkit.HTMLEditor.ToolbarButton.SelectOption();
                            opt.Value = option.Attribute("value").Value;
                            opt.Text = option.Attribute("text").Value;
                            opts.Add(opt);
                        }
                    }
                }
            }
        }

        #endregion
        #region " Methods "
        #endregion
        #region " Enum "
        #endregion
    }
}
---------------------------
XML File

download
--------------------------------

Please give comments for above if you having any query or suggestion regarding this.