tag:blogger.com,1999:blog-16434763208671178912024-03-14T14:01:34.520+05:30Dot Net Free Code SnippetsMicrosoft Technologies, ASP.NET, C#, VB.NET, SQL Server, JavaScript, XML, XSLT, Import/Export Excel and csv files, Payment Gateway Integration, Ajax.Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.comBlogger79125tag:blogger.com,1999:blog-1643476320867117891.post-86310877106538231512017-10-26T11:18:00.000+05:302017-10-26T14:38:42.677+05:30SQL Script to Generate Insert Query Script from Given Table Name and Condition<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
This post will help developer create insert script from the table with some condition or in other term to create insert script from given select statement.<br />
This will be helpful when we need to move some limited data from one environment to other.<br />
<br />
<br /></div>
<pre><blockquote>
DECLARE
@Table_name varchar(240),
@Condition Varchar(MAX)
SET @Table_name='NotificationBase'
SET @Condition = ' NotificationID IN (''11DB04B4-3F10-422F-B6C5-89914E7FD293'', ''4EB8D85E-6817-4220-93DF-54B84B3CFE62'')'
DECLARE @COLUMNS table (Row_number SmallINT , Column_Name varchar(Max) )
DECLARE
@Schema_name varchar(30),
@Conditions as varchar(MAX),
@Total_Rows as SmallINT,
@Counter as SmallINT,
@ComaCol as varchar(max),
@Query AS VARCHAR(MAX),
@InsertQuery VARCHAR(MAX),
@FinalQuery VARCHAR(MAX)
SET @Schema_name = 'dbo'
SET @ComaCol=''
Set @Counter=1
set @Conditions=''
SET @Query =''
SET @InsertQuery = ''
SET @FinalQuery = ''
INSERT INTO @COLUMNS
SELECT Row_number()Over (Order by column_id ) [Count] , Name
FROM SYS.COLUMNS WHERE Object_Name(Object_ID) = @Table_name AND system_type_id <> 189
AND is_identity = 0 AND is_computed = 0
SELECT @Total_Rows= Count(1) FROM @COLUMNS
Set @Table_name= '['+@Table_name+']'
Set @Schema_name='['+@Schema_name+']'
While (@Counter <= @Total_Rows )
BEGIN
SELECT @ComaCol= @ComaCol + '['+Column_Name+'], '
FROM @COLUMNS
WHERE [Row_number] = @Counter
SELECT @Query = @Query + ' + Case When [' + Column_Name + '] is null then ''NULL'' Else '''''''' +
Replace( Convert(varchar(Max),[' + Column_Name + '] ) ,'''''''','''' ) +'''''''' end+' + ''','''
FROM @COLUMNS
WHERE [Row_number]=@Counter
SET @Counter=@Counter+1
End
SET @Query = Right(@Query,LEN(@Query)-2)
SET @Query= LEFT(@Query,LEN(@Query)-4)
SET @ComaCol= substring (@ComaCol,0, len(@ComaCol) )
SET @InsertQuery= '''INSERT INTO ' + @Schema_name + '.' + @Table_name + '(' + @ComaCol + ')' + ' Values( ' + '''' + '+' + @Query + '+' + ''')'''
SET @FinalQuery= 'SELECT ' + @InsertQuery +'FROM ' + @Schema_name + '.' + @Table_name + ' With(NOLOCK) ' + ' WHERE ' + @Condition
PRINT(@FinalQuery)
EXECUTE(@FinalQuery) <br />
<table here="" name=""></table>
</blockquote>
</pre>
</div>
Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-65321248403202400372014-12-09T00:16:00.001+05:302014-12-09T00:20:32.778+05:30Delete Duplicate Data from SQL Table Using Common Table Expression (CTE)<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<blockquote>
DECLARE @DuplicateRcordTable TABLE (Col1 varchar(10), Col2 varchar(10))
INSERT INTO @DuplicateRcordTable
SELECT 'A', 'B'<br />
UNION ALL SELECT 'A', 'B' --duplicate<br />
UNION ALL SELECT 'C', 'C' --duplicate<br />
UNION ALL SELECT 'C', 'C'<br />
UNION ALL SELECT 'C', 'C' --duplicate<br />
UNION ALL SELECT 'D', 'E'<br />
UNION ALL SELECT 'E', 'F'<br />
<br />
SELECT * FROM @DuplicateRcordTable;<br />
<br />
WITH CTES(COl1,Col2, DuplicateCount)<br />
AS<br />
(<br />
SELECT COl1,Col2,
ROW_NUMBER()<br />
OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount<br />
FROM @DuplicateRcordTable<br />
)<br />
DELETE
FROM CTES
WHERE DuplicateCount > 1<br />
<br />
SELECT * FROM @DuplicateRcordTable</blockquote>
</div>
</div>
Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-28590519675101677932014-07-16T01:24:00.000+05:302014-07-16T01:24:02.232+05:30Remove Duplicate Records from SQL table<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
This Code will help to remove duplicate records when there is not key field in the table.</div>
<blockquote>
/* Create Table Statement */<br />
CREATE TABLE #Employee<br />
(<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>Name varchar(55) NULL,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>Salary decimal(10, 2) NULL,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>Designation varchar(20) NULL<br />
)<br />
<br />
/* Insert Data into the Table*/<br />
INSERT INTO #Employee VALUES('Lakhan Pal Garg', 10000, 'AST')<br />
INSERT INTO #Employee VALUES('Lakhan Pal Garg', 10000, 'AST')<br />
INSERT INTO #Employee VALUES('Amit Tyagi', 10000, 'ITA')<br />
INSERT INTO #Employee VALUES('Amit Tyagi', 10000, 'ITA')<br />
INSERT INTO #Employee VALUES('Lakhan Pal Garg', 12000, 'AST')<br />
INSERT INTO #Employee VALUES('Amit Tyagi', 13000, 'ITA')<br />
INSERT INTO #Employee VALUES('Sumit Sharma', 15000, 'AST')<br />
INSERT INTO #Employee VALUES('Sumit Sharma', 15500, 'AST')<br />
<br />
/* Mark Duplicate Record */<br />
WITH TempEmp (Name,duplicateRecCount)<br />
AS<br />
(<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary, Designation ORDER BY Name)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>AS duplicateRecCount<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>FROM #Employee<br />
)<br />
<br />
/* Now Delete Duplicate Records */<br />
DELETE FROM TempEmp<br />
WHERE duplicateRecCount > 1<br />
<br />
/* See updated table data */<br />
Select * from #Employee </blockquote>
</div>
Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-58010205518427659732012-02-07T22:17:00.002+05:302012-02-08T15:18:11.491+05:30Create Generic Template to Create Store Procedure<div dir="ltr" style="text-align: left;" trbidi="on">This store procedure will be used to create a generic SP for insert, update, delete, select, select all statement.<br />
<br />
This will create a store procedure script for the given table and SPType (Insert, Update, Select, SelectAll, Delete).<br />
<br />
<b>Copy the following Store Procedure:</b><br />
<blockquote>/*<br />
Created By: Lakhan Pal<br />
Created On: 07/02/2012<br />
Purpose: To Create Store Procedure Template for Given Table for <br />
Insert, Update, Select, SelectAll, And Delete Script<br />
<br />
User Just need to Pass Following Arguments<br />
(1) @TableName VARCHAR(100)<br />
(2) @AuthorName VARCHAR(100)<br />
(3) @SPType VARCHAR(15) (Insert, Update, Select, SelectAll, Delete)<br />
(4) @Comment VARCHAR(500) [Optional]<br />
<br />
Execution Statement:<br />
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Insert'<br />
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Update'<br />
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Select'<br />
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'SelectAll'<br />
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Delete'<br />
*/<br />
CREATE PROCEDURE uspCreateGenericTemplateForSP<br />
(<br />
@TableName VARCHAR(100),<br />
@AuthorName VARCHAR(100),<br />
@SPType VARCHAR(15),<br />
@Comment VARCHAR(500)=NULL<br />
)<br />
AS<br />
BEGIN<br />
DECLARE @MaxID INT, @ColList VARCHAR(4000), @paramList VARCHAR(4000)<br />
DECLARE @IsIdentityExist Int<br />
DECLARE @ScriptTable Table(ID INT IDENTITY(1,1),Script VARCHAR(5000), RecordType Char(1))<br />
<br />
SET @IsIdentityExist =0 <br />
SET @ColList =''<br />
SET @paramList =''<br />
<br />
IF (@Comment IS NULL)<br />
SET @Comment =' To Create script for ' + @SPType <br />
+ ' Record into Table ' + @TableName<br />
<br />
/* To Check the Identity Column */<br />
SELECT @IsIdentityExist =COUNT(Name) FROM sys.columns <br />
WHERE object_id=object_id(@TableName) AND is_identity =1<br />
<br />
/* Comment Section - Start Here */ <br />
INSERT INTO @ScriptTable(Script) VALUES('/*')<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES('Created By: ' + @AuthorName)<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES('Created On: ' + CONVERT(VARCHAR(10), GETDATE(), 103))<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES('Purpose: ' + @Comment)<br />
INSERT INTO @ScriptTable(Script) VALUES('*/')<br />
/* Comment Section - End Here */ <br />
<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES('CREATE PROCEDURE usp' +@SPType + '_'+ @TableName )<br />
INSERT INTO @ScriptTable(Script) VALUES('(')<br />
<br />
/* If Identity Column Exists */<br />
IF (@IsIdentityExist>0)<br />
BEGIN<br />
INSERT INTO @ScriptTable(Script, RecordType)<br />
(<br />
SELECT ' @' + C.Name + ' ' + T.Name + ',' , 'P'<br />
FROM Sys.COLUMNS C INNER JOIN SYS.TYPES T <br />
ON C.System_Type_ID= T.System_Type_ID <br />
WHERE Object_ID = OBJECT_ID(@TableName) AND C.is_identity =1)<br />
END<br />
<br />
IF(@SPType ='Insert' OR @SPType ='Update')<br />
BEGIN<br />
/* Parameter Section - Start Here */ <br />
INSERT INTO @ScriptTable(Script, RecordType)<br />
(<br />
SELECT ' @' + C.Name + ' ' + T.Name + ',' , 'P'<br />
FROM Sys.COLUMNS C INNER JOIN SYS.TYPES T <br />
ON C.System_Type_ID= T.System_Type_ID <br />
WHERE Object_ID = OBJECT_ID(@TableName) AND C.is_identity =0 AND <br />
T.System_Type_ID NOT IN (173, 175, 239, 231,165, 167,106, 108 )<br />
UNION<br />
SELECT ' @' + C.Name + ' ' + T.Name + '(' + <br />
CONVERT(VARCHAR(10),C.MAX_Length) <br />
+ ', '+ CONVERT(VARCHAR(10),C.Precision) + ')' + ',', 'P'<br />
FROM Sys.COLUMNS C INNER JOIN SYS.TYPES T <br />
ON C.System_Type_ID= T.System_Type_ID <br />
WHERE Object_ID = OBJECT_ID(@TableName) AND C.is_identity =0<br />
AND T.System_Type_ID IN (106, 108)<br />
UNION<br />
SELECT ' @' + C.Name + ' ' + T.Name + '(' + <br />
CONVERT(VARCHAR(10),C.MAX_Length) + ')' + ',' , 'P'<br />
FROM Sys.COLUMNS C INNER JOIN SYS.TYPES T <br />
ON C.System_Type_ID= T.System_Type_ID <br />
WHERE Object_ID = OBJECT_ID(@TableName) AND C.is_identity =0 <br />
AND T.System_Type_ID IN (173, 175, 239, 231,165, 167 )<br />
)<br />
/* Parameter Section - End Here */ <br />
END<br />
<br />
SELECT @MaxID = MAX(ID) FROM @ScriptTable WHERE RecordType='P'<br />
UPDATE @ScriptTable SET Script = <br />
SUBSTRING(Script,0,Len(Script)) WHERE ID=@MaxID<br />
<br />
INSERT INTO @ScriptTable(Script) VALUES(')')<br />
INSERT INTO @ScriptTable(Script) VALUES('AS')<br />
INSERT INTO @ScriptTable(Script) VALUES(' BEGIN')<br />
<br />
/* Query Section - Start Here */<br />
IF(@SPType = 'Insert')<br />
BEGIN<br />
SELECT @ColList = @ColList + name +',', <br />
@paramList = @paramList + '@' + name +',' <br />
FROM sys.columns <br />
WHERE object_id=object_id(@TableName) AND is_identity =0<br />
<br />
SET @ColList = SUBSTRING(@ColList,0,Len(@ColList))<br />
SET @paramList = SUBSTRING(@paramList,0,Len(@paramList))<br />
<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES(' INSERT INTO ' + @TableName ) <br />
INSERT INTO @ScriptTable(Script) <br />
VALUES(' (' + @ColList + ')') <br />
INSERT INTO @ScriptTable(Script) <br />
VALUES(' VALUES (' + @paramList + ')')<br />
END<br />
ELSE IF (@SPType ='Update')<br />
BEGIN<br />
SELECT @ColList = @ColList + name +' = ' + '@' + name + ',' <br />
FROM sys.columns <br />
WHERE object_id=object_id(@TableName) AND is_identity =0<br />
<br />
SELECT @paramList = @paramList + name +' = ' + '@' + name + ',' <br />
FROM sys.columns <br />
WHERE object_id=object_id(@TableName) AND is_identity =1<br />
<br />
SET @ColList = SUBSTRING(@ColList,0,Len(@ColList))<br />
IF (@IsIdentityExist>0)<br />
SET @paramList = 'WHERE ' + SUBSTRING(@paramList,0,Len(@paramList))<br />
ELSE<br />
SET @paramList =''<br />
<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES(' UPDATE ' + @TableName)<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES(' SET ' + @ColList)<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES(' ' + @paramList)<br />
END<br />
ELSE IF(@SPType= 'Select')<br />
BEGIN<br />
SELECT @ColList = @ColList + name +',' <br />
FROM sys.columns <br />
WHERE object_id=object_id(@TableName)AND is_identity =0<br />
<br />
SELECT @paramList = @paramList + name +' = ' + '@' + name + ',' <br />
FROM sys.columns <br />
WHERE object_id=object_id(@TableName) AND is_identity =1<br />
<br />
SET @ColList = SUBSTRING(@ColList,0,Len(@ColList))<br />
IF (@IsIdentityExist>0)<br />
SET @paramList = 'WHERE ' + SUBSTRING(@paramList,0,Len(@paramList))<br />
ELSE<br />
SET @paramList =''<br />
<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES(' SELECT ' + @ColList)<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES(' FROM ' + @TableName)<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES(' ' + @paramList)<br />
END<br />
ELSE IF(@SPType= 'SelectAll')<br />
BEGIN<br />
SELECT @ColList = @ColList + name +',' <br />
FROM sys.columns <br />
WHERE object_id=object_id(@TableName)<br />
<br />
SET @ColList = SUBSTRING(@ColList,0,Len(@ColList))<br />
<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES(' SELECT ' + @ColList)<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES(' FROM ' + @TableName)<br />
END<br />
ELSE IF(@SPType ='Delete')<br />
BEGIN<br />
SELECT @paramList = @paramList + name +' = ' + '@' + name + ',' <br />
FROM sys.columns <br />
WHERE object_id=object_id(@TableName) AND is_identity =1<br />
<br />
IF (@IsIdentityExist>0)<br />
SET @paramList = 'WHERE ' + SUBSTRING(@paramList,0,Len(@paramList))<br />
ELSE<br />
SET @paramList =''<br />
<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES(' DELETE FROM ' + @TableName)<br />
INSERT INTO @ScriptTable(Script) <br />
VALUES(' ' + @paramList)<br />
END<br />
/* Query Section - End Here */ <br />
<br />
INSERT INTO @ScriptTable(Script) VALUES(' END')<br />
<br />
SELECT Script FROM @ScriptTable<br />
END</blockquote><br />
<br />
</div>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-1253916014018977472012-02-07T19:02:00.001+05:302012-02-07T19:04:10.876+05:30Create Auto Implemented Public Properties To Be Used in C# Code From SQL Server<div dir="ltr" style="text-align: left;" trbidi="on"><div dir="ltr" style="text-align: left;" trbidi="on">This script will help you to create Auto Implemented public properties.<br />
For this you just need to pass the table name and the Script will create properties for each column available in that Table.<br />
<br />
<b>Copy the following code to generate the properties:</b></div><blockquote>DECLARE @TableName VARCHAR(100)<br />
SET @TableName = 'tblUsers'<br />
--String<br />
SELECT 'public string ' + name + ' { get; set; }' <br />
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName)<br />
AND System_Type_ID IN (35, 36, 98, 99,165,167,173,175,231,239,241,231,189)<br />
UNION<br />
-- Int64 <br />
SELECT 'public Int64 ' + name + ' { get; set; }' <br />
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName) <br />
AND System_Type_ID IN (127)<br />
UNION<br />
-- Int32 <br />
SELECT 'public Int32 ' + name + ' { get; set; }' <br />
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName) <br />
AND System_Type_ID IN (56) <br />
UNION<br />
-- Int16 <br />
SELECT 'public Int16 ' + name + ' { get; set; }' <br />
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName) <br />
AND System_Type_ID IN (48, 52) <br />
UNION<br />
--Decimal <br />
SELECT 'public Decimal ' + name + ' { get; set; }' <br />
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName) <br />
AND System_Type_ID IN (108, 62, 106, 60,122) <br />
UNION<br />
--Boolean <br />
SELECT 'public Boolean ' + name + ' { get; set; }' <br />
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName) <br />
AND System_Type_ID IN (104)<br />
UNION<br />
-- DateTime <br />
SELECT 'public DateTime ' + name + ' { get; set; }' <br />
FROM Sys.COLUMNS WHERE Object_ID = OBJECT_ID(@TableName) <br />
AND System_Type_ID IN (58,61)</blockquote></div>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-8038448111374878862011-09-08T10:56:00.001+05:302011-09-08T11:00:09.778+05:30Replace A String From The Data In The Database<div dir="ltr" style="text-align: left;" trbidi="on"><p>Following SQL script will help to replace a particular string from all user defined tables in the database.<br />
</p><br />
<blockquote>DECLARE @TEMP TABLE(tabname VARCHAR(100)) <br />
DECLARE @COUNT INT, @tabname VARCHAR(100)<br />
DECLARE @ColTemp TABLE(colName VARCHAR(100))<br />
DECLARE @ColCount INT, @ColName VARCHAR(100)<br />
DECLARE @SQLQuery VARCHAR(1000), @ReplaceChar VARCHAR(10), @ReplaceTo VARCHAR(10)<br />
<br />
SET @ReplaceChar='-'<br />
SET @ReplaceTo = '?'<br />
<br />
INSERT INTO @TEMP (tabname)<br />
(SELECT [name] FROm SYS.TABLES WHERE name='tblFormula')<br />
<br />
SELECT @COUNT=Count(*) FROM @TEMP<br />
<br />
WHILE @COUNT>0<br />
BEGIN<br />
SELECT TOP 1 @tabname=tabname FROM @TEMP<br />
<br />
INSERT INTO @ColTemp (colName)<br />
(SELECT Top 1 [name] FROm SYS.COLUMNS WHERE [Object_id]=Object_ID(@tabname))<br />
<br />
SELECT @ColCount=Count(*) FROM @ColTemp<br />
WHILE @COLCount>0<br />
BEGIN<br />
SELECT TOP 1 @ColName=ColName FROM @ColTemp<br />
<br />
SET @SQLQuery = 'UPDATE ' + @tabname + ' SET ' + @ColName + '= REPLACE(' + @ColName + ', ''' + @ReplaceChar + ''','''+ @ReplaceTo + ''')'<br />
<br />
PRINT @SQLQuery<br />
<br />
DELETE FROM @ColTemp WHERE ColName=@ColName<br />
SELECT @ColCount=Count(*) FROM @ColTemp<br />
END<br />
<br />
DELETE FROM @TEMP WHERE tabname=@tabname<br />
SELECT @COUNT=Count(*) FROM @TEMP<br />
END<br />
</blockquote></div>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-66850251241232883752011-07-19T15:39:00.000+05:302011-07-19T15:39:27.106+05:30Get The First And Last Working Day Of Each Month For the Given YearThis store procedure will help to get the first and last working day of each month in a given year.<br />
<blockquote>DECLARE @Year INT<br />
SET @Year=2011<br />
<br />
DECLARE @FirstDate DateTime,@LastDate DATETIME, @DayofWeek INT<br />
DECLARE @FirstWorkingDay DATETIME, @LastWorkingDay DATETIME<br />
<br />
SET @FirstDate = '01-01-'+CONVERT(VARCHAR(4),@Year)<br />
SET DATEFIRST 1<br />
<br />
WHILE (MONTH(@FirstDate) <=12 AND YEAR(@FirstDate)=@Year)<br />
BEGIN<br />
SET @DayofWeek = DATEPART(weekday,@FirstDate)<br />
--First working day of the month<br />
IF(@DayofWeek = 7)<br />
SET @FirstWorkingDay= @FirstDate+1<br />
ELSE IF (@DayofWeek = 6)<br />
SET @FirstWorkingDay = @FirstDate+2<br />
ELSE <br />
SET @FirstWorkingDay = @FirstDate<br />
<br />
--Get Last working day of the month<br />
SET @LastDate=DATEADD(mm,1,@FirstDate)-1<br />
SET @DayofWeek = DATEPART(weekday,@LastDate)<br />
IF(@DayofWeek = 7)<br />
SET @LastWorkingDay = @LastDate-2<br />
ELSE IF (@DayofWeek = 6)<br />
SET @LastWorkingDay = @LastDate-1<br />
ELSE <br />
SET @LastWorkingDay = @LastDate<br />
<br />
SELECT @FirstWorkingDay AS FirstDay, @LastWorkingDay AS 'Last Day'<br />
SET @FirstDate=DATEADD(mm,1,@FirstDate)<br />
END</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com1tag:blogger.com,1999:blog-1643476320867117891.post-66842844539182298232010-07-30T12:28:00.000+05:302010-07-30T12:28:30.317+05:30AutoComplete functionality Using Ajax Control ToolkitThis code snippet will help user to implement autocomplete functionality and also help to customize the autocomplete functioanlity. Like pass optional parameters while getting suggestions and disable tab button and execute some event on selection.<br />
<br />
<b>Sample.aspx:</b><br />
<blockquote><script type="text/javascript"><br />
var KeyID;<br />
<br />
function LoadAutoComplete(source, eventArgs) {<br />
SetContextKeyValue();<br />
}<br />
function DoPostBack(source, eventArgs) {<br />
KeyCheck(source);<br />
if (KeyID != 9)<br />
__doPostBack('lnkSubmit', ''); //Replace this with your script.<br />
else<br />
document.getElementById('<%=txtLastName.ClientID %>').value = source._currentPrefix<br />
}<br />
function KeyCheck(e) {<br />
<br />
KeyID = (window.event) ? event.keyCode : e.keyCode;<br />
}<br />
function SetContextKeyValue() {<br />
var firstname = document.getElementById('<%=txtFirstName.ClientID %>').value<br />
var location = document.getElementById('<%=drLocation.ClientID %>').value<br />
if (location == "0")<br />
location = ""<br />
IncludeWOPhone="1"<br />
varFilters = firstname + "~" + <br />
$find('acLastName').set_contextKey(varFilters);<br />
}<br />
</script><br />
<br />
<asp:TextBox ID="txtLastName" TabIndex="1" runat="server" MaxLength="50"></asp:TextBox><br />
<ajaxToolkit:AutoCompleteExtender runat="server" ID="acLastName" TargetControlID="txtLastName" ContextKey="testValue" UseContextKey="true" ServicePath="../AutoComplete.asmx" ServiceMethod="GetLastName" MinimumPrefixLength="3" OnClientItemSelected="DoPostBack" CompletionInterval="50" EnableCaching="false" CompletionSetCount="10" CompletionListCssClass="ac_completionListElement" OnClientPopulating="LoadAutoComplete" CompletionListItemCssClass="ac_listItem" CompletionListHighlightedItemCssClass="ac_highlightedListItem"> </ajaxToolkit:AutoCompleteExtender><br />
</blockquote><p>In the above AutocompleteExtender control I have used properties ContextKey & UseContextKey. The purpose of these properties is to pass optional parameters to filter the records that we are going to populate in auto complete list.<br />
If we want to pass optional parameter then we need to set UseContextKey=”True”. By default value of UseContextKey is False. And in ContextKay property we can pass the filter values.<br />
We can also set these values dynamically using OnClientPopulating event of AutocompleteExtender.<br />
In this event I am calling a JavaScript method “LoadAutoComplete” and within this method I am calling “SetContextKeyValue” to set the ContextKey value at run time.<br />
I have also added one more customization in this code is disable item selection on click of Tab button click and execute some code on selection (Click of enter button or mouse click). I have used OnClientItemSelected event of AutocompleteExtender. And call a method to check if the key pressed is Tab key then do nothing otherwise redirect to next form using __doPostBack.<br />
</p><b>AutoComplete.asmx.vb</b><br />
<blockquote>Imports System.Web<br />
Imports System.Web.Services<br />
Imports System.Web.Services.Protocols<br />
Imports System.ComponentModel<br />
<System.Web.Script.Services.ScriptService()> _<br />
<WebService(Namespace:="http://tempuri.org/")> _<br />
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _<br />
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _<br />
<br />
Public Class AutoComplete Inherits System.Web.Services.WebService<br />
<br />
<WebMethod()> _<br />
Public Function GetLastName(ByVal prefixText As String, ByVal count As Integer, ByVal contextKey As String) As String()<br />
Dim _nameList As New Search<br />
// Call your Own Method Here To get the Result in String Array<br />
Return GetNameList(prefixText, count, contextKey)<br />
<br />
End Function<br />
End Class<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com1tag:blogger.com,1999:blog-1643476320867117891.post-23882077045716834912010-07-30T11:55:00.000+05:302010-07-30T11:55:06.137+05:30That assembly does not allow partially trusted callersSolution for error:<br />
Execption Details: System.Security.SecurityException: That assembly does not allow partially trusted callers.<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiE3U42zW1bcRoUeVL9gkMe5FNKFej4AFwPzQU153jlx795Gd8q8p8ItN5rmL0lZtYZM7YI1aRYs08qHkoxsMxcUY3q9C1Vo9bep64ypMhxXtahGM1Asv3Bc3hpTl_-o6qWSAg_TLKrO-rT/s1600/err1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" bx="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiE3U42zW1bcRoUeVL9gkMe5FNKFej4AFwPzQU153jlx795Gd8q8p8ItN5rmL0lZtYZM7YI1aRYs08qHkoxsMxcUY3q9C1Vo9bep64ypMhxXtahGM1Asv3Bc3hpTl_-o6qWSAg_TLKrO-rT/s320/err1.jpg" /></a></div>Check if the trust level is set to Medium then either remove this tag from web.config file or change it to Full<br />
as:<br />
<blockquote><trust level="Full" /></blockquote><div align="left" class="separator" style="clear: both;"><br />
</div>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com1tag:blogger.com,1999:blog-1643476320867117891.post-60921524641350052692009-12-02T11:40:00.000+05:302009-12-02T11:40:22.147+05:30Get Feed Subscriber Count For Given Feed Burner URLWhen we send a request on the following url https://feedburner.google.com/api/awareness/1.0/GetFeedData?uri= then the XML data will be returned as a XML.<br />
<br />
This is sample XML retured from feed burner:<br />
<blockquote style="color: #38761d;"><statuses type="array"><br />
<status><br />
<created_at>Tue Oct 27 16:44:10 +0000 2009</created_at><br />
<id>5204855334</id><br />
<text><br />
http://bit.ly/oUB92 Reading: Create Script File For Each Store Procedure And Save into Seperate SQL File For Each Store Procedure<br />
</text><br />
<source>web</source><br />
<truncated>false</truncated><br />
<in_reply_to_status_id/><br />
<in_reply_to_user_id/><br />
<favorited>false</favorited><br />
<in_reply_to_screen_name/><br />
<user><br />
<id>28538625</id><br />
<name>Lakhan Pal Garg</name><br />
<screen_name>lakhangarg</screen_name><br />
<location/><br />
<description/><br />
<profile_image_url><br />
http://a1.twimg.com/profile_images/319559736/PhotoFunia-19dab_normal.jpg<br />
</profile_image_url><br />
<url>http://lakhangarg.blogspot.com/</url><br />
<protected>false</protected><br />
<followers_count>16</followers_count><br />
<profile_background_color>9ae4e8</profile_background_color><br />
<profile_text_color>000000</profile_text_color><br />
<profile_link_color>0000ff</profile_link_color><br />
<profile_sidebar_fill_color>e0ff92</profile_sidebar_fill_color><br />
<profile_sidebar_border_color>87bc44</profile_sidebar_border_color><br />
<friends_count>15</friends_count><br />
<created_at>Fri Apr 03 10:39:09 +0000 2009</created_at><br />
<favourites_count>0</favourites_count><br />
<utc_offset>-36000</utc_offset><br />
<time_zone>Hawaii</time_zone><br />
<profile_background_image_url><br />
http://s.twimg.com/a/1258070043/images/themes/theme1/bg.png<br />
</profile_background_image_url><br />
<profile_background_tile>false</profile_background_tile><br />
<statuses_count>12</statuses_count><br />
<notifications/><br />
<geo_enabled>false</geo_enabled><br />
<verified>false</verified><br />
<following/><br />
</user><br />
<geo/><br />
</status><br />
</statuses><br />
</blockquote><b>Sample code to get the follower count from the XML data is:</b><br />
<blockquote style="color: #38761d;">private static string GetTwitterFollowers(string TwitterURL)<br />
{<br />
try<br />
{<br />
if (TwitterURL != "")<br />
{<br />
TwitterURL = TwitterURL.Substring(TwitterURL.LastIndexOf('/') + 1);<br />
Uri uri = new Uri("http://twitter.com/statuses/user_timeline/" + TwitterURL + ".xml?count=1");<br />
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(uri);<br />
req.UserAgent = "Get Content";<br />
WebResponse resp = req.GetResponse();<br />
Stream stream = resp.GetResponseStream();<br />
StreamReader sr = new StreamReader(stream);<br />
string s = sr.ReadToEnd();<br />
System.Xml.XmlDocument xDoc = new System.Xml.XmlDocument();<br />
xDoc.LoadXml(s);<br />
string FollowerCount = xDoc.GetElementsByTagName("followers_count").Item(0).InnerText;<br />
if (FollowerCount == "")<br />
FollowerCount = "0";<br />
return FollowerCount;<br />
}<br />
else<br />
return "0";<br />
}<br />
catch<br />
{<br />
return "0";<br />
}<br />
}<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-85742967706277447032009-12-01T15:37:00.000+05:302009-12-01T15:37:32.699+05:30Code To Find Number of Sunday for the given MonthSuppose we know the month and year and we want to calculate the number of sunday in that month.<br />
<br />
Here is the code:<br />
<blockquote>private string GetNumberOfSundays(Int32 Month, Int32 Year)<br />
{<br />
DateTime StartDate = Convert.ToDateTime(Month.ToString() + "/01/" + Year.ToString());<br />
Int32 iDays = DateTime.DaysInMonth(Year,Month);<br />
DateTime EndDate = StartDate.AddDays(iDays - 1);<br />
Int32 Count = 0;<br />
Int32 SundayCount = 0;<br />
while (StartDate.DayOfWeek != DayOfWeek.Sunday)<br />
{<br />
StartDate = StartDate.AddDays(1);<br />
}<br />
SundayCount = 1;<br />
StartDate = StartDate.AddDays(7);<br />
while (StartDate <= EndDate)
{
SundayCount += 1;
StartDate = StartDate.AddDays(7);
}
return SundayCount.ToString();
}
</blockquote><br />
<br />
Pass the Month and Year as Integer value and the above method will return the number of sunday in that month as a string value.Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-39618208826456960282009-11-26T17:12:00.001+05:302009-11-26T17:19:53.863+05:30Get IP Address of Client and Server Sytem<b>Code To Get the IP Address of Host:</b><br />
<blockquote>private void GetHostaddress()<br />
{<br />
string strHostName = Dns.GetHostName();<br />
IPAddress[] ips;<br />
ips = Dns.GetHostAddresses(strHostName);<br />
foreach (IPAddress ip in ips)<br />
{<br />
Response.Write(ip);<br />
}<br />
}<br />
</blockquote><br />
<b>Code To Get the IP Address of Client's Machine:</b><br />
<blockquote>Request.UserHostAddress.ToString()</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-5998528261382007542009-11-22T01:33:00.002+05:302009-11-22T01:33:28.441+05:30Query to Get the dependencies for given Table NameFollowing Query is used to get the dependencies for the given Table.<br />
Suppose we need to check the dependencies for the table tbl_Customer<br />
<br />
Sample Query:<br />
<blockquote>DECLARE @Objectname varchar(100)<br />
SET @Objectname='tbl_Customer'<br />
<br />
SELECT O1.name,O2.name FROM sys.sql_dependencies D INNER JOIN Sys.Objects O1 ON O1.object_id=D.object_id<br />
INNER JOIN Sys.Objects O2 ON O2.object_id=D.referenced_major_id<br />
WHERE O1.name=@Objectname OR O2.name=@Objectname<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-21574288371643442042009-11-22T01:32:00.005+05:302009-11-22T01:32:55.786+05:30Query to get the name of the identity column for a given tableQuery to get the name of the identity column for a given table.<br />
To Get the Identity column name for Tbl_Customer we need to execute the following query.<br />
<blockquote>SELECT D.name AS ColumnName, O.name AS TableName<br />
FROM sys.identity_columns D INNER JOIN Sys.Objects O ON O.object_id=D.object_id<br />
Where O.name='Tbl_Customer<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-13417883393762346792009-11-22T01:32:00.002+05:302009-11-22T01:32:25.944+05:30Query to Get the Forgien Key Name Their Primary Table and Referrence TableFollowing Query will give you the List of Forgien Key in a Database with Their Primary and Referrence Tables Name.<br />
<br />
Sample Query:<br />
<blockquote>SELECT F.name AS ForgienKey,O2.name AS ParentTable,O3.name AS RefTable FROM sys.foreign_keys F<br />
INNER JOIN Sys.Objects O2 ON O2.object_id=F.parent_object_id<br />
INNER JOIN Sys.Objects O3 ON O3.object_id=F.referenced_object_id<br />
</blockquote>If you want to get forgien key for a particular table then you can use like this:<br />
<blockquote>SELECT F.name AS ForgienKey,O2.name AS ParentTable,O3.name AS RefTable FROM sys.foreign_keys F<br />
INNER JOIN Sys.Objects O2 ON O2.object_id=F.parent_object_id<br />
INNER JOIN Sys.Objects O3 ON O3.object_id=F.referenced_object_id<br />
Where O3.name='Tbl_User'<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-10319738522315067332009-11-22T01:31:00.005+05:302009-11-22T01:31:57.228+05:30SQL Statement to Get the Definition of the Store ProceduresSQL Statement to Get the Definition of the System Store Procedures.<br />
<blockquote>SELECT definition FROM sys.system_sql_modules<br />
</blockquote>SQL Statement to Get the Definition of the User defined Store Procedures.<br />
<blockquote>SELECT definition FROM sys.sql_modules<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-29227168981108494652009-11-22T01:31:00.002+05:302009-11-22T01:31:15.853+05:30Query in SQL Server to get the Parameter list of given Store ProcedureWrite a Query in SQL Server to get the Parameter list of given Store Procedure.<br />
<br />
Suppose we want to get the name of the parameter for the Store Procedure<br />
Course_Insert_sp then folllowing needs to be executed.<br />
<blockquote>SELECT * FROM sys.parameters D INNER JOIN Sys.Objects O ON O.object_id=D.object_id WHERE O.name='Course_Insert_sp'<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-23107592138121980212009-11-16T23:40:00.001+05:302009-11-16T23:41:10.656+05:30Code to Get Twitter Follower's CountWhen we send a request on the following url http://twitter.com/statuses/user_timeline/lakhangarg.xml?count=1 then the XML data will be returned as a XML.<br />
<br />
This is sample XML retured from twitter:<br />
<blockquote style="color: #38761d;"><statuses type="array"><br />
<status><br />
<created_at>Tue Oct 27 16:44:10 +0000 2009</created_at><br />
<id>5204855334</id><br />
<text><br />
http://bit.ly/oUB92 Reading: Create Script File For Each Store Procedure And Save into Seperate SQL File For Each Store Procedure<br />
</text><br />
<source>web</source><br />
<truncated>false</truncated><br />
<in_reply_to_status_id/><br />
<in_reply_to_user_id/><br />
<favorited>false</favorited><br />
<in_reply_to_screen_name/><br />
<user><br />
<id>28538625</id><br />
<name>Lakhan Pal Garg</name><br />
<screen_name>lakhangarg</screen_name><br />
<location/><br />
<description/><br />
<profile_image_url><br />
http://a1.twimg.com/profile_images/319559736/PhotoFunia-19dab_normal.jpg<br />
</profile_image_url><br />
<url>http://lakhangarg.blogspot.com/</url><br />
<protected>false</protected><br />
<followers_count>16</followers_count><br />
<profile_background_color>9ae4e8</profile_background_color><br />
<profile_text_color>000000</profile_text_color><br />
<profile_link_color>0000ff</profile_link_color><br />
<profile_sidebar_fill_color>e0ff92</profile_sidebar_fill_color><br />
<profile_sidebar_border_color>87bc44</profile_sidebar_border_color><br />
<friends_count>15</friends_count><br />
<created_at>Fri Apr 03 10:39:09 +0000 2009</created_at><br />
<favourites_count>0</favourites_count><br />
<utc_offset>-36000</utc_offset><br />
<time_zone>Hawaii</time_zone><br />
<profile_background_image_url><br />
http://s.twimg.com/a/1258070043/images/themes/theme1/bg.png<br />
</profile_background_image_url><br />
<profile_background_tile>false</profile_background_tile><br />
<statuses_count>12</statuses_count><br />
<notifications/><br />
<geo_enabled>false</geo_enabled><br />
<verified>false</verified><br />
<following/><br />
</user><br />
<geo/><br />
</status><br />
</statuses><br />
</blockquote><b>Sample code to get the follower count from the XML data is:</b><br />
<blockquote style="color: #38761d;">private static string GetTwitterFollowers(string TwitterURL)<br />
{<br />
try<br />
{<br />
if (TwitterURL != "")<br />
{<br />
TwitterURL = TwitterURL.Substring(TwitterURL.LastIndexOf('/') + 1);<br />
Uri uri = new Uri("http://twitter.com/statuses/user_timeline/" + TwitterURL + ".xml?count=1");<br />
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(uri);<br />
req.UserAgent = "Get Content";<br />
WebResponse resp = req.GetResponse();<br />
Stream stream = resp.GetResponseStream();<br />
StreamReader sr = new StreamReader(stream);<br />
string s = sr.ReadToEnd();<br />
System.Xml.XmlDocument xDoc = new System.Xml.XmlDocument();<br />
xDoc.LoadXml(s);<br />
string FollowerCount = xDoc.GetElementsByTagName("followers_count").Item(0).InnerText;<br />
if (FollowerCount == "")<br />
FollowerCount = "0";<br />
return FollowerCount;<br />
}<br />
else<br />
return "0";<br />
}<br />
catch<br />
{<br />
return "0";<br />
}<br />
}<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-55847324010926244732009-10-27T22:04:00.001+05:302009-10-27T22:05:37.420+05:30SQL Server Interview Questions<b>(1) How To Update Description Value for a Column in Table using SQL Command?</b><br />
We can Update Description to Column using sp_updateextendedproperty System Store Procedure.<br />
Sample Command to Update Description for Column in a Table:<br />
<blockquote style="color: #38761d;">EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName', @level2type=N'COLUMN',@level2name=N'ColumnName'<br />
</blockquote><br />
<b>(2) How To Delete Description Value for a Column in Table using SQL Command?</b><br />
We can Delete Description from Column using sp_dropextendedproperty System Store Procedure.<br />
Sample Command to Delete Description from Column in a Table:<br />
<blockquote style="color: #38761d;">EXEC sys.sp_dropextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName', @level2type=N'COLUMN',@level2name=N'ColumnName'<br />
</blockquote><br />
<b>(3) How we can add Description to the Column using Sql Command?</b><br />
We can Add Description to Column using sp_addextendedproperty System Store Procedure.<br />
Sample Command to Insert Description for Column in a Table:<br />
<blockquote style="color: #38761d;">EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName', @level2type=N'COLUMN',@level2name=N'ColumnName'<br />
</blockquote><br />
<b>(4) How To Get Description Value for all Column in Table using SQL Command?</b><br />
To Get Description of Columns we need to use system function sys.fn_listextendedproperty.<br />
Command To Get Description Data for all Columns:<br />
<blockquote style="color: #38761d;">SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA',<br />
'dbo', 'TABLE', 'YourTable Name Here', 'COLUMN', NULL)<br />
</blockquote><br />
<b>(5) How To Get Description Value for Single Column in Table using SQL Command?</b><br />
To Get Description of Single Column we need to use system function sys.fn_listextendedproperty. we need to pass the column Name is this case.<br />
Command To Get Description Data for Single Columns:<br />
<blockquote style="color: #38761d;">SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA',<br />
'dbo', 'TABLE', 'Table Name Here', 'COLUMN', 'Column Name Here')<br />
</blockquote><br />
<b>(6) How We can get the DB name using SQL Command?</b><br />
Following is the Command to get the DB name using Command analyzer<br />
<blockquote style="color: #38761d;">SELECT DB_NAME()<br />
</blockquote><br />
<b>(7) What is the use of Set NOCOUNT ON;?</b><br />
By Default When we execute any command it return us the number of record affected. if we don't want to return the number of records affected then we can use <br />
<div style="color: #38761d;">SET NOCOUNT ON;<br />
</div>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-82150216154150415942009-10-27T21:47:00.002+05:302009-10-27T21:50:20.375+05:30Store Procedure To Create Script for Create TableFollowing is the store procedure to create script for create table. this will create script for all constraint, description of columns, and index etc.<br />
<br />
<b>Store Procedure Script:</b><br />
<blockquote><div style="color: #38761d;">ALTER PROCEDURE sp_CreateTableScript <br />
</div><div style="color: #38761d;">( <br />
</div><div style="color: #38761d;">@TableName SYSNAME, <br />
</div><div style="color: #38761d;">@IncludeConstraints BIT = 1, <br />
</div><div style="color: #38761d;">@IncludeIndexes BIT = 1, <br />
</div><div style="color: #38761d;">@NewTableName SYSNAME = NULL, <br />
</div><div style="color: #38761d;">@UseSystemDataTypes BIT = 0 <br />
</div><div style="color: #38761d;">) <br />
</div><div style="color: #38761d;">AS <br />
</div><div style="color: #38761d;">BEGIN <br />
</div><div style="color: #38761d;">SET NOCOUNT ON; <br />
</div><div style="color: #38761d;">DECLARE @MainDefinition TABLE <br />
</div><div style="color: #38761d;">( <br />
</div><div style="color: #38761d;">FieldValue NVARCHAR(500) <br />
</div><div style="color: #38761d;">) <br />
</div><div style="color: #38761d;">DECLARE @DBName SYSNAME <br />
</div><div style="color: #38761d;">DECLARE @ClusteredPK BIT <br />
</div><div style="color: #38761d;">DECLARE @TableSchema NVARCHAR(255) <br />
</div><div style="color: #38761d;">SET @DBName = DB_NAME(DB_ID()) <br />
</div><div style="color: #38761d;">SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName) <br />
</div><div style="color: #38761d;">DECLARE @ShowFields TABLE <br />
</div><div style="color: #38761d;">( <br />
</div><div style="color: #38761d;">FieldID INT IDENTITY(1,1), <br />
</div><div style="color: #38761d;">DatabaseName VARCHAR(100), <br />
</div><div style="color: #38761d;">TableOwner VARCHAR(100), <br />
</div><div style="color: #38761d;">TableName VARCHAR(100), <br />
</div><div style="color: #38761d;">FieldName VARCHAR(100), <br />
</div><div style="color: #38761d;">ColumnPosition INT, <br />
</div><div style="color: #38761d;">ColumnDefaultValue VARCHAR(100), <br />
</div><div style="color: #38761d;">ColumnDefaultName VARCHAR(100), <br />
</div><div style="color: #38761d;">IsNullable BIT, <br />
</div><div style="color: #38761d;">DataType VARCHAR(100), <br />
</div><div style="color: #38761d;">MaxLength INT, <br />
</div><div style="color: #38761d;">NumericPrecision INT, <br />
</div><div style="color: #38761d;">NumericScale INT, <br />
</div><div style="color: #38761d;">DomainName VARCHAR(100), <br />
</div><div style="color: #38761d;">FieldListingName VARCHAR(110), <br />
</div><div style="color: #38761d;">FieldDefinition CHAR(1), <br />
</div><div style="color: #38761d;">IdentityColumn BIT, <br />
</div><div style="color: #38761d;">IdentitySeed INT, <br />
</div><div style="color: #38761d;">IdentityIncrement INT, <br />
</div><div style="color: #38761d;">IsCharColumn BIT <br />
</div><div style="color: #38761d;">) <br />
</div><div style="color: #38761d;">DECLARE @HoldingArea TABLE <br />
</div><div style="color: #38761d;">( <br />
</div><div style="color: #38761d;">FldID SMALLINT IDENTITY(1,1), <br />
</div><div style="color: #38761d;">Flds VARCHAR(4000), <br />
</div><div style="color: #38761d;">FldValue CHAR(1) DEFAULT(0) <br />
</div><div style="color: #38761d;">) <br />
</div><div style="color: #38761d;">DECLARE @PKObjectID TABLE <br />
</div><div style="color: #38761d;">( <br />
</div><div style="color: #38761d;">ObjectID INT <br />
</div><div style="color: #38761d;">) <br />
</div><div style="color: #38761d;">DECLARE @Uniques TABLE <br />
</div><div style="color: #38761d;">( <br />
</div><div style="color: #38761d;">ObjectID INT <br />
</div><div style="color: #38761d;">) <br />
</div><div style="color: #38761d;">DECLARE @HoldingAreaValues TABLE <br />
</div><div style="color: #38761d;">( <br />
</div><div style="color: #38761d;">FldID SMALLINT IDENTITY(1,1), <br />
</div><div style="color: #38761d;">Flds VARCHAR(4000), <br />
</div><div style="color: #38761d;">FldValue CHAR(1) DEFAULT(0) <br />
</div><div style="color: #38761d;">) <br />
</div><div style="color: #38761d;">DECLARE @Definition TABLE <br />
</div><div style="color: #38761d;">( <br />
</div><div style="color: #38761d;">DefinitionID SMALLINT IDENTITY(1,1), <br />
</div><div style="color: #38761d;">FieldValue NVARCHAR(500) <br />
</div><div style="color: #38761d;">) <br />
</div><div style="color: #38761d;">INSERT INTO @ShowFields <br />
</div><div style="color: #38761d;">( <br />
</div><div style="color: #38761d;">DatabaseName, <br />
</div><div style="color: #38761d;">TableOwner, <br />
</div><div style="color: #38761d;">TableName, <br />
</div><div style="color: #38761d;">FieldName, <br />
</div><div style="color: #38761d;">ColumnPosition, <br />
</div><div style="color: #38761d;">ColumnDefaultValue, <br />
</div><div style="color: #38761d;">ColumnDefaultName, <br />
</div><div style="color: #38761d;">IsNullable, <br />
</div><div style="color: #38761d;">DataType, <br />
</div><div style="color: #38761d;">MaxLength, <br />
</div><div style="color: #38761d;">NumericPrecision, <br />
</div><div style="color: #38761d;">NumericScale, <br />
</div><div style="color: #38761d;">DomainName, <br />
</div><div style="color: #38761d;">FieldListingName, <br />
</div><div style="color: #38761d;">FieldDefinition, <br />
</div><div style="color: #38761d;">IdentityColumn, <br />
</div><div style="color: #38761d;">IdentitySeed, <br />
</div><div style="color: #38761d;">IdentityIncrement, <br />
</div><div style="color: #38761d;">IsCharColumn <br />
</div><div style="color: #38761d;">) <br />
</div><div style="color: #38761d;">SELECT <br />
</div><div style="color: #38761d;">DB_NAME(), <br />
</div><div style="color: #38761d;">TABLE_SCHEMA, <br />
</div><div style="color: #38761d;">TABLE_NAME, <br />
</div><div style="color: #38761d;">COLUMN_NAME, <br />
</div><div style="color: #38761d;">CAST(ORDINAL_POSITION AS INT), <br />
</div><div style="color: #38761d;">COLUMN_DEFAULT, <br />
</div><div style="color: #38761d;">dobj.name AS ColumnDefaultName, <br />
</div><div style="color: #38761d;">CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END, <br />
</div><div style="color: #38761d;">DATA_TYPE, <br />
</div><div style="color: #38761d;">CAST(CHARACTER_MAXIMUM_LENGTH AS INT), <br />
</div><div style="color: #38761d;">CAST(NUMERIC_PRECISION AS INT), <br />
</div><div style="color: #38761d;">CAST(NUMERIC_SCALE AS INT), <br />
</div><div style="color: #38761d;">DOMAIN_NAME, <br />
</div><div style="color: #38761d;">COLUMN_NAME + ',','' AS FieldDefinition, <br />
</div><div style="color: #38761d;">CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn, <br />
</div><div style="color: #38761d;">CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed, <br />
</div><div style="color: #38761d;">CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement, <br />
</div><div style="color: #38761d;">CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn <br />
</div><div style="color: #38761d;">FROM <br />
</div><div style="color: #38761d;">INFORMATION_SCHEMA.COLUMNS c <br />
</div><div style="color: #38761d;">JOIN sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name <br />
</div><div style="color: #38761d;">LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name <br />
</div><div style="color: #38761d;">JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name <br />
</div><div style="color: #38761d;">LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D' <br />
</div><div style="color: #38761d;">WHERE c.TABLE_NAME = @TableName <br />
</div><div style="color: #38761d;">ORDER BY <br />
</div><div style="color: #38761d;">c.TABLE_NAME, c.ORDINAL_POSITION <br />
</div><div style="color: #38761d;">SELECT TOP 1 @TableSchema = TableOwner <br />
</div><div style="color: #38761d;">FROM @ShowFields <br />
</div><div style="color: #38761d;">INSERT INTO @HoldingArea (Flds) VALUES('(') <br />
</div><div style="color: #38761d;">INSERT INTO @Definition(FieldValue) <br />
</div><div style="color: #38761d;">VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @TableSchema + '.' + @TableName END) <br />
</div><div style="color: #38761d;">INSERT INTO @Definition(FieldValue) <br />
</div><div style="color: #38761d;">VALUES('(') <br />
</div><div style="color: #38761d;">INSERT INTO @Definition(FieldValue) <br />
</div><div style="color: #38761d;">SELECT <br />
</div><div style="color: #38761d;">CHAR(10) + FieldName + ' ' + <br />
</div><div style="color: #38761d;">CASE <br />
</div><div style="color: #38761d;">WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN <br />
</div><div style="color: #38761d;">DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END <br />
</div><div style="color: #38761d;">ELSE UPPER(DataType) + <br />
</div><div style="color: #38761d;">CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END + <br />
</div><div style="color: #38761d;">CASE WHEN IdentityColumn = 1 THEN <br />
</div><div style="color: #38761d;">' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END + <br />
</div><div style="color: #38761d;">CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END + <br />
</div><div style="color: #38761d;">CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN <br />
</div><div style="color: #38761d;">'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END <br />
</div><div style="color: #38761d;">END + <br />
</div><div style="color: #38761d;">CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END <br />
</div><div style="color: #38761d;">FROM @ShowFields <br />
</div><div style="color: #38761d;">IF @IncludeConstraints = 1 <br />
</div><div style="color: #38761d;">BEGIN <br />
</div><div style="color: #38761d;">INSERT INTO @Definition(FieldValue) <br />
</div><div style="color: #38761d;">SELECT <br />
</div><div style="color: #38761d;">',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')' <br />
</div><div style="color: #38761d;">FROM <br />
</div><div style="color: #38761d;">( <br />
</div><div style="color: #38761d;">SELECT <br />
</div><div style="color: #38761d;">ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name, <br />
</div><div style="color: #38761d;">REVERSE(SUBSTRING(REVERSE(( <br />
</div><div style="color: #38761d;">SELECT cp.name + ',' <br />
</div><div style="color: #38761d;">FROM <br />
</div><div style="color: #38761d;">sys.foreign_key_columns fkc <br />
</div><div style="color: #38761d;">JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id <br />
</div><div style="color: #38761d;">WHERE fkc.constraint_object_id = fk.object_id <br />
</div><div style="color: #38761d;">FOR XML PATH('') <br />
</div><div style="color: #38761d;">)), 2, 8000)) ParentColumns, <br />
</div><div style="color: #38761d;">REVERSE(SUBSTRING(REVERSE(( <br />
</div><div style="color: #38761d;">SELECT cr.name + ',' <br />
</div><div style="color: #38761d;">FROM <br />
</div><div style="color: #38761d;">sys.foreign_key_columns fkc <br />
</div><div style="color: #38761d;">JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id <br />
</div><div style="color: #38761d;">WHERE fkc.constraint_object_id = fk.object_id <br />
</div><div style="color: #38761d;">FOR XML PATH('') <br />
</div><div style="color: #38761d;">)), 2, 8000)) ReferencedColumns <br />
</div><div style="color: #38761d;">FROM sys.foreign_keys fk <br />
</div><div style="color: #38761d;">) a <br />
</div><div style="color: #38761d;">WHERE ParentObject = @TableName <br />
</div><div style="color: #38761d;">INSERT INTO @Definition(FieldValue) <br />
</div><div style="color: #38761d;">SELECT',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints <br />
</div><div style="color: #38761d;">WHERE OBJECT_NAME(parent_object_id) = @TableName <br />
</div><div style="color: #38761d;">INSERT INTO @PKObjectID(ObjectID) <br />
</div><div style="color: #38761d;">SELECT DISTINCT <br />
</div><div style="color: #38761d;">PKObject = cco.object_id <br />
</div><div style="color: #38761d;">FROM <br />
</div><div style="color: #38761d;">sys.key_constraints cco <br />
</div><div style="color: #38761d;">JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id <br />
</div><div style="color: #38761d;">JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id <br />
</div><div style="color: #38761d;">WHERE <br />
</div><div style="color: #38761d;">OBJECT_NAME(parent_object_id) = @TableName AND <br />
</div><div style="color: #38761d;">i.type = 1 AND <br />
</div><div style="color: #38761d;">is_primary_key = 1 <br />
</div><div style="color: #38761d;">INSERT INTO @Uniques(ObjectID) <br />
</div><div style="color: #38761d;">SELECT DISTINCT <br />
</div><div style="color: #38761d;">PKObject = cco.object_id <br />
</div><div style="color: #38761d;">FROM <br />
</div><div style="color: #38761d;">sys.key_constraints cco <br />
</div><div style="color: #38761d;">JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id <br />
</div><div style="color: #38761d;">JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id <br />
</div><div style="color: #38761d;">WHERE <br />
</div><div style="color: #38761d;">OBJECT_NAME(parent_object_id) = @TableName AND <br />
</div><div style="color: #38761d;">i.type = 2 AND <br />
</div><div style="color: #38761d;">is_primary_key = 0 AND <br />
</div><div style="color: #38761d;">is_unique_constraint = 1 <br />
</div><div style="color: #38761d;">SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END <br />
</div><div style="color: #38761d;">INSERT INTO @Definition(FieldValue) <br />
</div><div style="color: #38761d;">SELECT ',CONSTRAINT ' + name + CASE type WHEN 'PK' THEN <br />
</div><div style="color: #38761d;">' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END <br />
</div><div style="color: #38761d;">WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END + '(' + <br />
</div><div style="color: #38761d;">REVERSE(SUBSTRING(REVERSE(( <br />
</div><div style="color: #38761d;">SELECT <br />
</div><div style="color: #38761d;">c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ',' <br />
</div><div style="color: #38761d;">FROM <br />
</div><div style="color: #38761d;">sys.key_constraints ccok <br />
</div><div style="color: #38761d;">LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id <br />
</div><div style="color: #38761d;">LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id <br />
</div><div style="color: #38761d;">LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id <br />
</div><div style="color: #38761d;">WHERE <br />
</div><div style="color: #38761d;">i.object_id = ccok.parent_object_id AND <br />
</div><div style="color: #38761d;">ccok.object_id = cco.object_id <br />
</div><div style="color: #38761d;">FOR XML PATH('') <br />
</div><div style="color: #38761d;">)), 2, 8000)) + ')' <br />
</div><div style="color: #38761d;">FROM <br />
</div><div style="color: #38761d;">sys.key_constraints cco <br />
</div><div style="color: #38761d;">LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID <br />
</div><div style="color: #38761d;">LEFT JOIN @Uniques u ON cco.object_id = u.objectID <br />
</div><div style="color: #38761d;">WHERE <br />
</div><div style="color: #38761d;">OBJECT_NAME(cco.parent_object_id) = @TableName <br />
</div><div style="color: #38761d;">END <br />
</div><div style="color: #38761d;">INSERT INTO @Definition(FieldValue) <br />
</div><div style="color: #38761d;">VALUES(')') <br />
</div><div style="color: #38761d;">INSERT INTO @Definition(FieldValue) <br />
</div><div style="color: #38761d;">VALUES('GO') <br />
</div><div style="color: #38761d;">IF @IncludeIndexes = 1 <br />
</div><div style="color: #38761d;">BEGIN <br />
</div><div style="color: #38761d;">INSERT INTO @Definition(FieldValue) <br />
</div><div style="color: #38761d;">SELECT <br />
</div><div style="color: #38761d;">ISNULL(('CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' + OBJECT_NAME(object_id) + '] (' + <br />
</div><div style="color: #38761d;">REVERSE(SUBSTRING(REVERSE(( <br />
</div><div style="color: #38761d;">SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ',' <br />
</div><div style="color: #38761d;">FROM <br />
</div><div style="color: #38761d;">sys.index_columns sc <br />
</div><div style="color: #38761d;">JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id <br />
</div><div style="color: #38761d;">WHERE <br />
</div><div style="color: #38761d;">OBJECT_NAME(sc.object_id) = @TableName AND <br />
</div><div style="color: #38761d;">sc.object_id = i.object_id AND <br />
</div><div style="color: #38761d;">sc.index_id = i.index_id <br />
</div><div style="color: #38761d;">ORDER BY index_column_id ASC <br />
</div><div style="color: #38761d;">FOR XML PATH('') <br />
</div><div style="color: #38761d;">)), 2, 8000)) + ')' ),'') <br />
</div><div style="color: #38761d;">FROM sys.indexes i <br />
</div><div style="color: #38761d;">WHERE <br />
</div><div style="color: #38761d;">OBJECT_NAME(object_id) = @TableName <br />
</div><div style="color: #38761d;">AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1 <br />
</div><div style="color: #38761d;">AND is_unique_constraint = 0 <br />
</div><div style="color: #38761d;">AND is_primary_key = 0 <br />
</div><div style="color: #38761d;">END <br />
</div><div style="color: #38761d;">INSERT INTO @Definition(FieldValue) <br />
</div><div style="color: #38761d;">SELECT 'EXEC sys.sp_addextendedproperty @name=N'''+name+''', @value=N'''+CONVERT(varchar(100),value)+''' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N'''+@TableName+''', <br />
</div><div style="color: #38761d;">@level2type=N''COLUMN'',@level2name=N'''+objName+''''<br />
</div><div style="color: #38761d;">FROM ::fn_listextendedproperty(NULL, 'SCHEMA',<br />
</div><div style="color: #38761d;">'dbo', 'TABLE', @TableName, 'COLUMN', NULL) WHERE CONVERT(varchar(100),value)<>'' <br />
</div><div style="color: #38761d;"><br />
</div><div style="color: #38761d;">INSERT INTO @MainDefinition(FieldValue) <br />
</div><div style="color: #38761d;">SELECT FieldValue FROM @Definition <br />
</div><div style="color: #38761d;">ORDER BY DefinitionID ASC <br />
</div><div style="color: #38761d;">SELECT * FROM @MainDefinition <br />
</div><span style="color: #38761d;">END</span> <br />
</blockquote><br />
How to execute the above store procedure and get the create table script:<br />
<blockquote style="color: #38761d;">EXEC sp_CreateTableScript 'TableName'<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-81463965961461658652009-10-27T21:34:00.001+05:302009-10-27T21:36:24.441+05:30Create Script File For Each Store Procedure And Save into Seperate SQL File For EachWith the help of <b>sqlcmd utility</b> we can save the script of store procedure in separate file for each store procedure.<br />
<br />
Name of the file will be same as the name of the store procedure.<br />
<b>Sample Code to Create Script and Save</b>:<br />
<blockquote style="color: #38761d;">select N'sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext ' + name + N' " -h-1 -k 1 >> c:\DB\sp\' + name + N'.sql' from sys.objects where type='P'<br />
</blockquote><br />
When you will execute the above command the following output will be generated based on the number of store procedure in your database:<br />
<br />
<blockquote style="color: #38761d;">sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext User_Authenticate_sp " -h-1 -k 1 >> c:\DB\sp\User_Authenticate_sp.sql<br />
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext User_GetPassword_sp " -h-1 -k 1 >> c:\DB\sp\User_GetPassword_sp.sql<br />
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_upgraddiagrams " -h-1 -k 1 >> c:\DB\sp\sp_upgraddiagrams.sql<br />
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_helpdiagrams " -h-1 -k 1 >> c:\DB\sp\sp_helpdiagrams.sql<br />
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_helpdiagramdefinition " -h-1 -k 1 >> c:\DB\sp\sp_helpdiagramdefinition.sql<br />
</blockquote><br />
Save these output data into a batch file execute this batch file. this will save the store procedure script on <b>C:\DB\sp</b>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-12693615273440140572009-10-06T15:10:00.002+05:302009-10-06T15:18:54.555+05:30Decrypt String DataTo decrypt data we need to pass the encrypted string data.<br />
To see the code to Encrypt string data <a href="http://lakhangarg.blogspot.com/2009/10/encrypt-string-data.html">click here</a><br />
<br />
<blockquote style="color: #6aa84f;">public static string Decrypt(string cipherString)<br />
{<br />
byte[] keyArray;<br />
//get the byte code of the string<br />
byte[] toEncryptArray = Convert.FromBase64String(cipherString);<br />
string key = ")(*&";<br />
//if hashing was used get the hash code with regards to your key<br />
MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();<br />
keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));<br />
//release any resource held by the MD5CryptoServiceProvider<br />
hashmd5.Clear();<br />
<br />
TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();<br />
//set the secret key for the tripleDES algorithm<br />
tdes.Key = keyArray;<br />
//mode of operation. there are other 4 modes. We choose ECB(Electronic code Book)<br />
<br />
tdes.Mode = CipherMode.ECB;<br />
//padding mode(if any extra byte added)<br />
tdes.Padding = PaddingMode.PKCS7;<br />
<br />
ICryptoTransform cTransform = tdes.CreateDecryptor();<br />
byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);<br />
//Release resources held by TripleDes Encryptor <br />
tdes.Clear();<br />
//return the Clear decrypted TEXT<br />
return UTF8Encoding.UTF8.GetString(resultArray);<br />
} <br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-57095812024987447412009-10-06T15:01:00.005+05:302009-10-06T15:18:21.629+05:30Encrypt a string DataPass the string data to this function and this function will return the encrypted string. To see the code to decrypt string data <a href="http://lakhangarg.blogspot.com/2009/10/decrypt-string-data.html">click here</a><br />
<br />
<blockquote style="color: #6aa84f;">public static string Encrypt(string toEncrypt)<br />
{<br />
byte[] keyArray;<br />
byte[] toEncryptArray = UTF8Encoding.UTF8.GetBytes(toEncrypt);<br />
string key = ")(*&";<br />
MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();<br />
keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));<br />
//Always release the resources and flush data of the Cryptographic service provide. Best Practice<br />
hashmd5.Clear();<br />
TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();<br />
//set the secret key for the tripleDES algorithm<br />
tdes.Key = keyArray;<br />
//mode of operation. there are other 4 modes. We choose ECB(Electronic code Book)<br />
tdes.Mode = CipherMode.ECB;<br />
//padding mode(if any extra byte added)<br />
<br />
tdes.Padding = PaddingMode.PKCS7;<br />
<br />
ICryptoTransform cTransform = tdes.CreateEncryptor();<br />
//transform the specified region of bytes array to resultArray<br />
byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);<br />
//Release resources held by TripleDes Encryptor<br />
tdes.Clear();<br />
//Return the encrypted data into unreadable string format<br />
return Convert.ToBase64String(resultArray, 0, resultArray.Length);<br />
}<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-18135521845548611282009-09-29T16:00:00.006+05:302009-09-29T16:07:15.160+05:30Method to Get Time Elapsed With Refernce to Current Date TimeWith the help of following code we can get the time elapsed with refernce to current date time.<br />
<br />
Pass the Previous Activities Date Time and the following function will return time elapsed in the format:<br />
<b>{Number} hour/hours ago,{Number} Day/Days ago,{Number} week/weeks ago,{Number} month/months ago</b><br />
<blockquote><span style="color: #6aa84f;">public static string GetDaysAgo(string strCreatedDateTime)</span><br />
<span style="color: #6aa84f;">{</span><br />
<span style="color: #6aa84f;">try</span><br />
<span style="color: #6aa84f;">{</span><br />
<span style="color: #6aa84f;">DateTime CreatedDateTime = Convert.ToDateTime(strCreatedDateTime);</span><br />
<span style="color: #6aa84f;">string StrReturn = null;</span><br />
<span style="color: #6aa84f;">TimeSpan TimeDiff = DateTime.Now - CreatedDateTime;</span><br />
<span style="color: #6aa84f;">double MinDiff = Convert.ToDouble(TimeDiff.TotalMinutes.ToString());</span><br />
<span style="color: #6aa84f;">if (MinDiff < 0) MinDiff = 0;</span><br />
<span style="color: #6aa84f;">if (MinDiff < 60) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " minutes ago";</span><br />
<span style="color: #6aa84f;">else { MinDiff = MinDiff / 60; </span><br />
<span style="color: #6aa84f;">if (MinDiff < 24) if (Math.Floor(Convert.ToDecimal(MinDiff)) == 1) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " hour ago"; </span><br />
<span style="color: #6aa84f;">else StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " hours ago"; </span><br />
<span style="color: #6aa84f;">else { MinDiff = MinDiff / 24; </span><br />
<span style="color: #6aa84f;">if (MinDiff < 7) if (Math.Floor(Convert.ToDecimal(MinDiff)) == 1) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " day ago"; </span><br />
<span style="color: #6aa84f;">else StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " days ago"; </span><br />
<span style="color: #6aa84f;">else if (MinDiff < 30) { MinDiff = MinDiff / 7;</span><br />
<span style="color: #6aa84f;">if (Math.Floor(Convert.ToDecimal(MinDiff)) == 1) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " week ago";</span><br />
<span style="color: #6aa84f;">else StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " weeks ago";</span><br />
<span style="color: #6aa84f;">} else { MinDiff = MinDiff / 30; </span><br />
<span style="color: #6aa84f;">if (Math.Floor(Convert.ToDecimal(MinDiff)) == 1) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " month ago";</span><br />
<span style="color: #6aa84f;">else StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " months ago";</span><br />
<span style="color: #6aa84f;">} } } return StrReturn;</span><br />
<span style="color: #6aa84f;">} catch (Exception ex) { return "1 months ago";</span><br />
<span style="color: #6aa84f;">} }</span><br />
</blockquote>Call this function like this:<br />
<blockquote>GetDaysAgo("30/8/2009");<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1643476320867117891.post-76724393929660309122009-09-26T12:03:00.001+05:302009-09-26T12:03:32.226+05:30Store Procedure to be Used in Custom PaggingWith the help of the below set of query we can get the number of record that we want to show to user.<br />
<br />
For this we need to pass two parameters. first is the @RecordsToPick (Number of Records that you want to picjk for the page) and the second is @PageNumber (Page number for which you want to get the records from Database.)<br />
<br />
<blockquote>USE DBName<br />
GO<br />
DECLARE @RecordsToPick smallint, @PageNumber smallint<br />
SET @RecordsToPick = 10<br />
SET @PageNumber = 2<br />
<br />
DECLARE @StartRow INT<br />
DECLARE @EndRow INT<br />
SET @StartRow = ((@PageNumber-1) * @RecordsToPick)<br />
SET @EndRow = @StartRow + @RecordsToPick<br />
<br />
SELECT * FROM ( SELECT UserName,UserID,City,State,Country ROW_NUMBER()OVER(ORDER BY UserID) AS RowNumber<br />
FROM as_TblMembers) As AliasName WHERE RowNumber > @StartRow AND RowNumber <= @EndRow
GO
</blockquote><br />
<br />
In the above store procedure first we will get the @StartRow and @EndRow to get the number of first record and last record respectively.<br />
<blockquote>ROW_NUMBER()OVER(ORDER BY UserID) AS RowNumber<br />
</blockquote>ROW_NUMBER() will assign a unique number to each query order by UserID. with the help of it is easy for us to get the required result.Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0