Complete Programming Stuff

Monday, January 28, 2013

Search a text from all Stored Procedures

SELECT obj.Name SPName, sc.TEXT SPText

FROM sys.syscomments sc

INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID

WHERE sc.TEXT LIKE '%' + 'YouTextHere' + '%'

AND TYPE = 'P'

SQL UDF to get No. of Days in a Month

CREATE FUNCTION [dbo].[fn_GetNumDaysInMonth] ( @myDateTime DATETIME )

RETURNS INT

BEGIN

DECLARE @rtDate INT

SET @rtDate = CASE WHEN MONTH(@myDateTime)

IN (1, 3, 5, 7, 8, 10, 12) THEN 31

WHEN MONTH(@myDateTime) IN (4, 6, 9, 11) THEN 30

ELSE CASE WHEN (YEAR(@myDateTime) % 4 = 0

AND

YEAR(@myDateTime) % 100 != 0)

(YEAR(@myDateTime) % 400 = 0)

THEN 29

ELSE 28 END

END

RETURN @rtDate

END

SQL Query to display all User Defined Functions

SELECT name AS function_name

,SCHEMA_NAME(schema_id) AS schema_name

,type_desc

FROM sys.objects

WHERE type_desc LIKE '%FUNCTION%'

SQL UDF to get Currency in Comma seperated

CREATE FUNCTION [dbo].[fn_CurrencyInComma]

(

@Value DECIMAL(18, 2)

)

RETURNS VARCHAR(50)

BEGIN

DECLARE @s VARCHAR(50),

@ptr SMALLINT

--FOR INDIA SET 2, AND FOR UK SET 3

DECLARE @IndiaOrUK SMALLINT = 2

SELECT @s = CAST(@Value AS VARCHAR(50)),

@ptr = DATALENGTH(@s) - 5

WHILE @ptr >= 2

SELECT @s = STUFF(@s, @ptr, 0, ','),

@ptr = @ptr - @IndiaOrUK

RETURN @s

END

Saturday, December 1, 2012

Working with xp_cmdshell

xp_cmdshell executes Operating system command through SQL.
Syntax:
EXEC xp_cmdshell {'command_string'} [, no_output]

command_string:
type here operating system commands.

no_output:
if no_output is specified, it will not return values. By default it returns 0(Success) or 1(Failure).

How to enable xp_cmdshell:

EXEC sp_configure 'show advanced options',1

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

Example:

EXEC xp_cmdshell 'ver'

EXEC xp_cmdshell 'ipconfig'

EXEC xp_cmdshell 'vol'

EXEC xp_cmdshell 'dir'

Now lets play more with it.

--create a textfile and write some content into it

EXEC xp_cmdshell 'echo hello, this is a text msg >h:\test.txt'

--now read it

EXEC xp_cmdshell 'type h:\test.txt'

--make a newdirectory

EXEC xp_cmdshell 'md h:\to'

--copy this textfile 'test.txt' to another location

EXEC xp_cmdshell 'copy h:\test.txt h:\to\copied.txt'

--now see newlycreated directory and check presense of your copied file

EXEC xp_cmdshell 'dir h:\to'

--now read againfrom copied location for verification

EXEC xp_cmdshell 'type h:\to\copied.txt'

--rename this file

EXEC xp_cmdshell 'ren h:\to\copied.txt renamed.txt'

--delete this file

EXEC xp_cmdshell 'del h:\to\renamed.txt'

--and removedirectory too

EXEC xp_cmdshell 'rd h:\to\'

Saturday, November 24, 2012

How to Show HTML content or Code Behind Code or SQL Code in Web Page with all Formatting like Font, Font Size, Color etc.

Normally if you paste any content in .NET TextBox control then it will be saved as plain text. To solve this problem you must have to paste data in your HTML control and save it. Although this editor captures all font information but it does not capture color information of .NET code or any code. Now to solve this problem you must have to paste it in MS WORD and then copy and paste it again to your HTML editor control.

Here Describes the process of Showing HTML content or Code Behind Code or SQL Code in Web Page with all Formatting like Font, Font Size, Color etc.

Step 1:
Copy your code to Microsoft Word.

Step 2:
Now Just again copy the content of your word using ctrl+A, ctrl+C

Step 3:

Open Any HTML Editor like Ajax HTML Editor or FCK Editor and Paste the code in it and save this control data it your database.

Step 4:
Now fetch this data from database and show it in your webpage in DIV control. Use innerHTML to show in your Page.
div1.InnerHtml = ds.Tables[0].Rows[0][0].ToString();

Output:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

</div>

</form>

</body>

</html>

Saturday, July 28, 2012

Validation Expression for Regular Expression

Date (mm/dd/yyyy):

"^(((0?[1-9]|1[012])/(0?[1-9]|1\d|2[0-8])|(0?[13456789]|1[012])/(29|30)|(0?[13578]|1[02])/31)/(19|[2-9]\d)\d{2}|0?2/29/((19|[2-9]\d)(0[48]|[2468][048]|[13579][26])|(([2468][048]|[3579][26])00)))$"

Date (dd/mm/yyyy):

"^(((0[1-9]|[12]\d|3[01])\/(0[13578]|1[02])\/((19|[2-9]\d)\d{2}))|((0[1-9]|[12]\d|30)\/(0[13456789]|1[012])\/((19|[2-9]\d)\d{2}))|((0[1-9]|1\d|2[0-8])\/02\/((19|[2-9]\d)\d{2}))|(29\/02\/((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))$"

Pincode:

"\d{6,6}"

Mobile Number:

^((\+){0,1}91(\s){0,1}(\-){0,1}(\s){0,1}){0,1}9[0-9](\s){0,1}(\-){0,1}(\s){0,1}[1-9]{1}[0-9]{7}$

Email:
\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*