Dot Net Free Code Snippets

Replace A String From The Data In The Database

2011-09-08T10:56:00.001+05:30

Following SQL script will help to replace a particular string from all user defined tables in the database.

DECLARE @TEMP TABLE(tabname VARCHAR(100))
DECLARE @COUNT INT, @tabname VARCHAR(100)
DECLARE @ColTemp TABLE(colName VARCHAR(100))
DECLARE @ColCount INT, @ColName VARCHAR(100)
DECLARE @SQLQuery VARCHAR(1000), @ReplaceChar VARCHAR(10), @ReplaceTo VARCHAR(10)

SET @ReplaceChar='-'
SET @ReplaceTo = '?'

INSERT INTO @TEMP (tabname)
(SELECT [name] FROm SYS.TABLES WHERE name='tblFormula')

SELECT @COUNT=Count(*) FROM @TEMP

WHILE @COUNT>0
BEGIN
SELECT TOP 1 @tabname=tabname FROM @TEMP

INSERT INTO @ColTemp (colName)
(SELECT Top 1 [name] FROm SYS.COLUMNS WHERE [Object_id]=Object_ID(@tabname))

SELECT @ColCount=Count(*) FROM @ColTemp
WHILE @COLCount>0
BEGIN
SELECT TOP 1 @ColName=ColName FROM @ColTemp

SET @SQLQuery = 'UPDATE ' + @tabname + ' SET ' + @ColName + '= REPLACE(' + @ColName + ', ''' + @ReplaceChar + ''','''+ @ReplaceTo + ''')'

PRINT @SQLQuery

DELETE FROM @ColTemp WHERE ColName=@ColName
SELECT @ColCount=Count(*) FROM @ColTemp
END

DELETE FROM @TEMP WHERE tabname=@tabname
SELECT @COUNT=Count(*) FROM @TEMP
END

Get The First And Last Working Day Of Each Month For the Given Year

2011-07-19T15:39:00.000+05:30

This store procedure will help to get the first and last working day of each month in a given year.

DECLARE @Year INT
SET @Year=2011

DECLARE @FirstDate DateTime,@LastDate DATETIME, @DayofWeek INT
DECLARE @FirstWorkingDay DATETIME, @LastWorkingDay DATETIME

SET @FirstDate = '01-01-'+CONVERT(VARCHAR(4),@Year)
SET DATEFIRST 1

WHILE (MONTH(@FirstDate) <=12 AND YEAR(@FirstDate)=@Year)
BEGIN
SET @DayofWeek = DATEPART(weekday,@FirstDate)
--First working day of the month
IF(@DayofWeek = 7)
SET @FirstWorkingDay= @FirstDate+1
ELSE IF (@DayofWeek = 6)
SET @FirstWorkingDay = @FirstDate+2
ELSE
SET @FirstWorkingDay = @FirstDate

--Get Last working day of the month
SET @LastDate=DATEADD(mm,1,@FirstDate)-1
SET @DayofWeek = DATEPART(weekday,@LastDate)
IF(@DayofWeek = 7)
SET @LastWorkingDay = @LastDate-2
ELSE IF (@DayofWeek = 6)
SET @LastWorkingDay = @LastDate-1
ELSE
SET @LastWorkingDay = @LastDate

SELECT @FirstWorkingDay AS FirstDay, @LastWorkingDay AS 'Last Day'
SET @FirstDate=DATEADD(mm,1,@FirstDate)
END

AutoComplete functionality Using Ajax Control Toolkit

2010-07-30T12:28:00.000+05:30

This 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.

Sample.aspx:

<script type="text/javascript">
var KeyID;

function LoadAutoComplete(source, eventArgs) {
SetContextKeyValue();
}
function DoPostBack(source, eventArgs) {
KeyCheck(source);
if (KeyID != 9)
__doPostBack('lnkSubmit', ''); //Replace this with your script.
else
document.getElementById('<%=txtLastName.ClientID %>').value = source._currentPrefix
}
function KeyCheck(e) {

KeyID = (window.event) ? event.keyCode : e.keyCode;
}
function SetContextKeyValue() {
var firstname = document.getElementById('<%=txtFirstName.ClientID %>').value
var location = document.getElementById('<%=drLocation.ClientID %>').value
if (location == "0")
location = ""
IncludeWOPhone="1"
varFilters = firstname + "~" +
$find('acLastName').set_contextKey(varFilters);
}
</script>

<asp:TextBox ID="txtLastName" TabIndex="1" runat="server" MaxLength="50"></asp:TextBox>
<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>

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.
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.
We can also set these values dynamically using OnClientPopulating event of AutocompleteExtender.
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.
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.

AutoComplete.asmx.vb

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _

Public Class AutoComplete Inherits System.Web.Services.WebService

<WebMethod()> _
Public Function GetLastName(ByVal prefixText As String, ByVal count As Integer, ByVal contextKey As String) As String()
Dim _nameList As New Search
// Call your Own Method Here To get the Result in String Array
Return GetNameList(prefixText, count, contextKey)

End Function
End Class

That assembly does not allow partially trusted callers

2010-07-30T11:55:00.000+05:30

Solution for error:
Execption Details: System.Security.SecurityException: That assembly does not allow partially trusted callers.

Check if the trust level is set to Medium then either remove this tag from web.config file or change it to Full
as:

<trust level="Full" />

Get Feed Subscriber Count For Given Feed Burner URL

2009-12-02T11:40:00.000+05:30

When 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.

This is sample XML retured from feed burner:

<statuses type="array">
<status>
<created_at>Tue Oct 27 16:44:10 +0000 2009</created_at>
<id>5204855334</id>
<text>
http://bit.ly/oUB92 Reading: Create Script File For Each Store Procedure And Save into Seperate SQL File For Each Store Procedure
</text>
<source>web</source>
<truncated>false</truncated>
<in_reply_to_status_id/>
<in_reply_to_user_id/>
<favorited>false</favorited>
<in_reply_to_screen_name/>
<user>
<id>28538625</id>
<name>Lakhan Pal Garg</name>
<screen_name>lakhangarg</screen_name>
<location/>
<description/>
<profile_image_url>
http://a1.twimg.com/profile_images/319559736/PhotoFunia-19dab_normal.jpg
</profile_image_url>
<url>http://lakhangarg.blogspot.com/</url>
<protected>false</protected>
<followers_count>16</followers_count>
<profile_background_color>9ae4e8</profile_background_color>
<profile_text_color>000000</profile_text_color>
<profile_link_color>0000ff</profile_link_color>
<profile_sidebar_fill_color>e0ff92</profile_sidebar_fill_color>
<profile_sidebar_border_color>87bc44</profile_sidebar_border_color>
<friends_count>15</friends_count>
<created_at>Fri Apr 03 10:39:09 +0000 2009</created_at>
<favourites_count>0</favourites_count>
<utc_offset>-36000</utc_offset>
<time_zone>Hawaii</time_zone>
<profile_background_image_url>
http://s.twimg.com/a/1258070043/images/themes/theme1/bg.png
</profile_background_image_url>
<profile_background_tile>false</profile_background_tile>
<statuses_count>12</statuses_count>
<notifications/>
<geo_enabled>false</geo_enabled>
<verified>false</verified>
<following/>
</user>
<geo/>
</status>
</statuses>

Sample code to get the follower count from the XML data is:

private static string GetTwitterFollowers(string TwitterURL)
{
try
{
if (TwitterURL != "")
{
TwitterURL = TwitterURL.Substring(TwitterURL.LastIndexOf('/') + 1);
Uri uri = new Uri("http://twitter.com/statuses/user_timeline/" + TwitterURL + ".xml?count=1");
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(uri);
req.UserAgent = "Get Content";
WebResponse resp = req.GetResponse();
Stream stream = resp.GetResponseStream();
StreamReader sr = new StreamReader(stream);
string s = sr.ReadToEnd();
System.Xml.XmlDocument xDoc = new System.Xml.XmlDocument();
xDoc.LoadXml(s);
string FollowerCount = xDoc.GetElementsByTagName("followers_count").Item(0).InnerText;
if (FollowerCount == "")
FollowerCount = "0";
return FollowerCount;
}
else
return "0";
}
catch
{
return "0";
}
}

Code To Find Number of Sunday for the given Month

2009-12-01T15:37:00.000+05:30

Suppose we know the month and year and we want to calculate the number of sunday in that month.

Here is the code:

private string GetNumberOfSundays(Int32 Month, Int32 Year)
{
DateTime StartDate = Convert.ToDateTime(Month.ToString() + "/01/" + Year.ToString());
Int32 iDays = DateTime.DaysInMonth(Year,Month);
DateTime EndDate = StartDate.AddDays(iDays - 1);
Int32 Count = 0;
Int32 SundayCount = 0;
while (StartDate.DayOfWeek != DayOfWeek.Sunday)
{
StartDate = StartDate.AddDays(1);
}
SundayCount = 1;
StartDate = StartDate.AddDays(7);
while (StartDate <= EndDate) { SundayCount += 1; StartDate = StartDate.AddDays(7); } return SundayCount.ToString(); }

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.

Get IP Address of Client and Server Sytem

2009-11-26T17:12:00.001+05:30

Code To Get the IP Address of Host:

private void GetHostaddress()
{
string strHostName = Dns.GetHostName();
IPAddress[] ips;
ips = Dns.GetHostAddresses(strHostName);
foreach (IPAddress ip in ips)
{
Response.Write(ip);
}
}

Code To Get the IP Address of Client's Machine:

Request.UserHostAddress.ToString()

Query to Get the dependencies for given Table Name

2009-11-22T01:33:00.002+05:30

Following Query is used to get the dependencies for the given Table.
Suppose we need to check the dependencies for the table tbl_Customer

Sample Query:

DECLARE @Objectname varchar(100)
SET @Objectname='tbl_Customer'

SELECT O1.name,O2.name FROM sys.sql_dependencies D INNER JOIN Sys.Objects O1 ON O1.object_id=D.object_id
INNER JOIN Sys.Objects O2 ON O2.object_id=D.referenced_major_id
WHERE O1.name=@Objectname OR O2.name=@Objectname

Query to get the name of the identity column for a given table

2009-11-22T01:32:00.005+05:30

Query to get the name of the identity column for a given table.
To Get the Identity column name for Tbl_Customer we need to execute the following query.

SELECT D.name AS ColumnName, O.name AS TableName
FROM sys.identity_columns D INNER JOIN Sys.Objects O ON O.object_id=D.object_id
Where O.name='Tbl_Customer

Query to Get the Forgien Key Name Their Primary Table and Referrence Table

2009-11-22T01:32:00.002+05:30

Following Query will give you the List of Forgien Key in a Database with Their Primary and Referrence Tables Name.

Sample Query:

SELECT F.name AS ForgienKey,O2.name AS ParentTable,O3.name AS RefTable FROM sys.foreign_keys F
INNER JOIN Sys.Objects O2 ON O2.object_id=F.parent_object_id
INNER JOIN Sys.Objects O3 ON O3.object_id=F.referenced_object_id

If you want to get forgien key for a particular table then you can use like this:

SELECT F.name AS ForgienKey,O2.name AS ParentTable,O3.name AS RefTable FROM sys.foreign_keys F
INNER JOIN Sys.Objects O2 ON O2.object_id=F.parent_object_id
INNER JOIN Sys.Objects O3 ON O3.object_id=F.referenced_object_id
Where O3.name='Tbl_User'

SQL Statement to Get the Definition of the Store Procedures

2009-11-22T01:31:00.005+05:30

SQL Statement to Get the Definition of the System Store Procedures.

SELECT definition FROM sys.system_sql_modules

SQL Statement to Get the Definition of the User defined Store Procedures.

SELECT definition FROM sys.sql_modules

Query in SQL Server to get the Parameter list of given Store Procedure

2009-11-22T01:31:00.002+05:30

Write a Query in SQL Server to get the Parameter list of given Store Procedure.

Suppose we want to get the name of the parameter for the Store Procedure
Course_Insert_sp then folllowing needs to be executed.

SELECT * FROM sys.parameters D INNER JOIN Sys.Objects O ON O.object_id=D.object_id WHERE O.name='Course_Insert_sp'

Code to Get Twitter Follower's Count

2009-11-16T23:40:00.001+05:30

When 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.

This is sample XML retured from twitter:

<statuses type="array">
<status>
<created_at>Tue Oct 27 16:44:10 +0000 2009</created_at>
<id>5204855334</id>
<text>
http://bit.ly/oUB92 Reading: Create Script File For Each Store Procedure And Save into Seperate SQL File For Each Store Procedure
</text>
<source>web</source>
<truncated>false</truncated>
<in_reply_to_status_id/>
<in_reply_to_user_id/>
<favorited>false</favorited>
<in_reply_to_screen_name/>
<user>
<id>28538625</id>
<name>Lakhan Pal Garg</name>
<screen_name>lakhangarg</screen_name>
<location/>
<description/>
<profile_image_url>
http://a1.twimg.com/profile_images/319559736/PhotoFunia-19dab_normal.jpg
</profile_image_url>
<url>http://lakhangarg.blogspot.com/</url>
<protected>false</protected>
<followers_count>16</followers_count>
<profile_background_color>9ae4e8</profile_background_color>
<profile_text_color>000000</profile_text_color>
<profile_link_color>0000ff</profile_link_color>
<profile_sidebar_fill_color>e0ff92</profile_sidebar_fill_color>
<profile_sidebar_border_color>87bc44</profile_sidebar_border_color>
<friends_count>15</friends_count>
<created_at>Fri Apr 03 10:39:09 +0000 2009</created_at>
<favourites_count>0</favourites_count>
<utc_offset>-36000</utc_offset>
<time_zone>Hawaii</time_zone>
<profile_background_image_url>
http://s.twimg.com/a/1258070043/images/themes/theme1/bg.png
</profile_background_image_url>
<profile_background_tile>false</profile_background_tile>
<statuses_count>12</statuses_count>
<notifications/>
<geo_enabled>false</geo_enabled>
<verified>false</verified>
<following/>
</user>
<geo/>
</status>
</statuses>

Sample code to get the follower count from the XML data is:

private static string GetTwitterFollowers(string TwitterURL)
{
try
{
if (TwitterURL != "")
{
TwitterURL = TwitterURL.Substring(TwitterURL.LastIndexOf('/') + 1);
Uri uri = new Uri("http://twitter.com/statuses/user_timeline/" + TwitterURL + ".xml?count=1");
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(uri);
req.UserAgent = "Get Content";
WebResponse resp = req.GetResponse();
Stream stream = resp.GetResponseStream();
StreamReader sr = new StreamReader(stream);
string s = sr.ReadToEnd();
System.Xml.XmlDocument xDoc = new System.Xml.XmlDocument();
xDoc.LoadXml(s);
string FollowerCount = xDoc.GetElementsByTagName("followers_count").Item(0).InnerText;
if (FollowerCount == "")
FollowerCount = "0";
return FollowerCount;
}
else
return "0";
}
catch
{
return "0";
}
}

SQL Server Interview Questions

2009-10-27T22:04:00.001+05:30

(1) How To Update Description Value for a Column in Table using SQL Command?
We can Update Description to Column using sp_updateextendedproperty System Store Procedure.
Sample Command to Update Description for Column in a Table:

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'

(2) How To Delete Description Value for a Column in Table using SQL Command?
We can Delete Description from Column using sp_dropextendedproperty System Store Procedure.
Sample Command to Delete Description from Column in a Table:

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'

(3) How we can add Description to the Column using Sql Command?
We can Add Description to Column using sp_addextendedproperty System Store Procedure.
Sample Command to Insert Description for Column in a Table:

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'

(4) How To Get Description Value for all Column in Table using SQL Command?
To Get Description of Columns we need to use system function sys.fn_listextendedproperty.
Command To Get Description Data for all Columns:

SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA',
'dbo', 'TABLE', 'YourTable Name Here', 'COLUMN', NULL)

(5) How To Get Description Value for Single Column in Table using SQL Command?
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.
Command To Get Description Data for Single Columns:

SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA',
'dbo', 'TABLE', 'Table Name Here', 'COLUMN', 'Column Name Here')

(6) How We can get the DB name using SQL Command?
Following is the Command to get the DB name using Command analyzer

SELECT DB_NAME()

(7) What is the use of Set NOCOUNT ON;?
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

SET NOCOUNT ON;

Store Procedure To Create Script for Create Table

2009-10-27T21:47:00.002+05:30

Following is the store procedure to create script for create table. this will create script for all constraint, description of columns, and index etc.

Store Procedure Script:

ALTER PROCEDURE sp_CreateTableScript
(
@TableName SYSNAME,
@IncludeConstraints BIT = 1,
@IncludeIndexes BIT = 1,
@NewTableName SYSNAME = NULL,
@UseSystemDataTypes BIT = 0
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MainDefinition TABLE
(
FieldValue NVARCHAR(500)
)
DECLARE @DBName SYSNAME
DECLARE @ClusteredPK BIT
DECLARE @TableSchema NVARCHAR(255)
SET @DBName = DB_NAME(DB_ID())
SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)
DECLARE @ShowFields TABLE
(
FieldID INT IDENTITY(1,1),
DatabaseName VARCHAR(100),
TableOwner VARCHAR(100),
TableName VARCHAR(100),
FieldName VARCHAR(100),
ColumnPosition INT,
ColumnDefaultValue VARCHAR(100),
ColumnDefaultName VARCHAR(100),
IsNullable BIT,
DataType VARCHAR(100),
MaxLength INT,
NumericPrecision INT,
NumericScale INT,
DomainName VARCHAR(100),
FieldListingName VARCHAR(110),
FieldDefinition CHAR(1),
IdentityColumn BIT,
IdentitySeed INT,
IdentityIncrement INT,
IsCharColumn BIT
)
DECLARE @HoldingArea TABLE
(
FldID SMALLINT IDENTITY(1,1),
Flds VARCHAR(4000),
FldValue CHAR(1) DEFAULT(0)
)
DECLARE @PKObjectID TABLE
(
ObjectID INT
)
DECLARE @Uniques TABLE
(
ObjectID INT
)
DECLARE @HoldingAreaValues TABLE
(
FldID SMALLINT IDENTITY(1,1),
Flds VARCHAR(4000),
FldValue CHAR(1) DEFAULT(0)
)
DECLARE @Definition TABLE
(
DefinitionID SMALLINT IDENTITY(1,1),
FieldValue NVARCHAR(500)
)
INSERT INTO @ShowFields
(
DatabaseName,
TableOwner,
TableName,
FieldName,
ColumnPosition,
ColumnDefaultValue,
ColumnDefaultName,
IsNullable,
DataType,
MaxLength,
NumericPrecision,
NumericScale,
DomainName,
FieldListingName,
FieldDefinition,
IdentityColumn,
IdentitySeed,
IdentityIncrement,
IsCharColumn
)
SELECT
DB_NAME(),
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
CAST(ORDINAL_POSITION AS INT),
COLUMN_DEFAULT,
dobj.name AS ColumnDefaultName,
CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
DATA_TYPE,
CAST(CHARACTER_MAXIMUM_LENGTH AS INT),
CAST(NUMERIC_PRECISION AS INT),
CAST(NUMERIC_SCALE AS INT),
DOMAIN_NAME,
COLUMN_NAME + ',','' AS FieldDefinition,
CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn,
CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed,
CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement,
CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn
FROM
INFORMATION_SCHEMA.COLUMNS c
JOIN sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'
WHERE c.TABLE_NAME = @TableName
ORDER BY
c.TABLE_NAME, c.ORDINAL_POSITION
SELECT TOP 1 @TableSchema = TableOwner
FROM @ShowFields
INSERT INTO @HoldingArea (Flds) VALUES('(')
INSERT INTO @Definition(FieldValue)
VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @TableSchema + '.' + @TableName END)
INSERT INTO @Definition(FieldValue)
VALUES('(')
INSERT INTO @Definition(FieldValue)
SELECT
CHAR(10) + FieldName + ' ' +
CASE
WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN
DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
ELSE UPPER(DataType) +
CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +
CASE WHEN IdentityColumn = 1 THEN
' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +
CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +
CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN
'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END
END +
CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END
FROM @ShowFields
IF @IncludeConstraints = 1
BEGIN
INSERT INTO @Definition(FieldValue)
SELECT
',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'
FROM
(
SELECT
ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name,
REVERSE(SUBSTRING(REVERSE((
SELECT cp.name + ','
FROM
sys.foreign_key_columns fkc
JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
)), 2, 8000)) ParentColumns,
REVERSE(SUBSTRING(REVERSE((
SELECT cr.name + ','
FROM
sys.foreign_key_columns fkc
JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
)), 2, 8000)) ReferencedColumns
FROM sys.foreign_keys fk
) a
WHERE ParentObject = @TableName
INSERT INTO @Definition(FieldValue)
SELECT',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints
WHERE OBJECT_NAME(parent_object_id) = @TableName
INSERT INTO @PKObjectID(ObjectID)
SELECT DISTINCT
PKObject = cco.object_id
FROM
sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
OBJECT_NAME(parent_object_id) = @TableName AND
i.type = 1 AND
is_primary_key = 1
INSERT INTO @Uniques(ObjectID)
SELECT DISTINCT
PKObject = cco.object_id
FROM
sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
OBJECT_NAME(parent_object_id) = @TableName AND
i.type = 2 AND
is_primary_key = 0 AND
is_unique_constraint = 1
SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
INSERT INTO @Definition(FieldValue)
SELECT ',CONSTRAINT ' + name + CASE type WHEN 'PK' THEN
' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END
WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END + '(' +
REVERSE(SUBSTRING(REVERSE((
SELECT
c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM
sys.key_constraints ccok
LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id
LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
i.object_id = ccok.parent_object_id AND
ccok.object_id = cco.object_id
FOR XML PATH('')
)), 2, 8000)) + ')'
FROM
sys.key_constraints cco
LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID
LEFT JOIN @Uniques u ON cco.object_id = u.objectID
WHERE
OBJECT_NAME(cco.parent_object_id) = @TableName
END
INSERT INTO @Definition(FieldValue)
VALUES(')')
INSERT INTO @Definition(FieldValue)
VALUES('GO')
IF @IncludeIndexes = 1
BEGIN
INSERT INTO @Definition(FieldValue)
SELECT
ISNULL(('CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' + OBJECT_NAME(object_id) + '] (' +
REVERSE(SUBSTRING(REVERSE((
SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM
sys.index_columns sc
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE
OBJECT_NAME(sc.object_id) = @TableName AND
sc.object_id = i.object_id AND
sc.index_id = i.index_id
ORDER BY index_column_id ASC
FOR XML PATH('')
)), 2, 8000)) + ')' ),'')
FROM sys.indexes i
WHERE
OBJECT_NAME(object_id) = @TableName
AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1
AND is_unique_constraint = 0
AND is_primary_key = 0
END
INSERT INTO @Definition(FieldValue)
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+''',
@level2type=N''COLUMN'',@level2name=N'''+objName+''''
FROM ::fn_listextendedproperty(NULL, 'SCHEMA',
'dbo', 'TABLE', @TableName, 'COLUMN', NULL) WHERE CONVERT(varchar(100),value)<>''

INSERT INTO @MainDefinition(FieldValue)
SELECT FieldValue FROM @Definition
ORDER BY DefinitionID ASC
SELECT * FROM @MainDefinition
END

How to execute the above store procedure and get the create table script:

EXEC sp_CreateTableScript 'TableName'

Create Script File For Each Store Procedure And Save into Seperate SQL File For Each

2009-10-27T21:34:00.001+05:30

With the help of sqlcmd utility we can save the script of store procedure in separate file for each store procedure.

Name of the file will be same as the name of the store procedure.
Sample Code to Create Script and Save:

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'

When you will execute the above command the following output will be generated based on the number of store procedure in your database:

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
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
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
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
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

Save these output data into a batch file execute this batch file. this will save the store procedure script on C:\DB\sp

Decrypt String Data

2009-10-06T15:10:00.002+05:30

To decrypt data we need to pass the encrypted string data.
To see the code to Encrypt string data click here

public static string Decrypt(string cipherString)
{
byte[] keyArray;
//get the byte code of the string
byte[] toEncryptArray = Convert.FromBase64String(cipherString);
string key = ")(*&";
//if hashing was used get the hash code with regards to your key
MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
//release any resource held by the MD5CryptoServiceProvider
hashmd5.Clear();

TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
//set the secret key for the tripleDES algorithm
tdes.Key = keyArray;
//mode of operation. there are other 4 modes. We choose ECB(Electronic code Book)

tdes.Mode = CipherMode.ECB;
//padding mode(if any extra byte added)
tdes.Padding = PaddingMode.PKCS7;

ICryptoTransform cTransform = tdes.CreateDecryptor();
byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);
//Release resources held by TripleDes Encryptor
tdes.Clear();
//return the Clear decrypted TEXT
return UTF8Encoding.UTF8.GetString(resultArray);
}

Encrypt a string Data

2009-10-06T15:01:00.005+05:30

Pass the string data to this function and this function will return the encrypted string. To see the code to decrypt string data click here

public static string Encrypt(string toEncrypt)
{
byte[] keyArray;
byte[] toEncryptArray = UTF8Encoding.UTF8.GetBytes(toEncrypt);
string key = ")(*&";
MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
//Always release the resources and flush data of the Cryptographic service provide. Best Practice
hashmd5.Clear();
TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
//set the secret key for the tripleDES algorithm
tdes.Key = keyArray;
//mode of operation. there are other 4 modes. We choose ECB(Electronic code Book)
tdes.Mode = CipherMode.ECB;
//padding mode(if any extra byte added)

tdes.Padding = PaddingMode.PKCS7;

ICryptoTransform cTransform = tdes.CreateEncryptor();
//transform the specified region of bytes array to resultArray
byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);
//Release resources held by TripleDes Encryptor
tdes.Clear();
//Return the encrypted data into unreadable string format
return Convert.ToBase64String(resultArray, 0, resultArray.Length);
}

Method to Get Time Elapsed With Refernce to Current Date Time

2009-09-29T16:00:00.006+05:30

With the help of following code we can get the time elapsed with refernce to current date time.

Pass the Previous Activities Date Time and the following function will return time elapsed in the format:
{Number} hour/hours ago,{Number} Day/Days ago,{Number} week/weeks ago,{Number} month/months ago

public static string GetDaysAgo(string strCreatedDateTime)
{
try
{
DateTime CreatedDateTime = Convert.ToDateTime(strCreatedDateTime);
string StrReturn = null;
TimeSpan TimeDiff = DateTime.Now - CreatedDateTime;
double MinDiff = Convert.ToDouble(TimeDiff.TotalMinutes.ToString());
if (MinDiff < 0) MinDiff = 0;
if (MinDiff < 60) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " minutes ago";
else { MinDiff = MinDiff / 60;
if (MinDiff < 24) if (Math.Floor(Convert.ToDecimal(MinDiff)) == 1) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " hour ago";
else StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " hours ago";
else { MinDiff = MinDiff / 24;
if (MinDiff < 7) if (Math.Floor(Convert.ToDecimal(MinDiff)) == 1) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " day ago";
else StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " days ago";
else if (MinDiff < 30) { MinDiff = MinDiff / 7;
if (Math.Floor(Convert.ToDecimal(MinDiff)) == 1) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " week ago";
else StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " weeks ago";
} else { MinDiff = MinDiff / 30;
if (Math.Floor(Convert.ToDecimal(MinDiff)) == 1) StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " month ago";
else StrReturn = Math.Floor(Convert.ToDecimal(MinDiff)).ToString() + " months ago";
} } } return StrReturn;
} catch (Exception ex) { return "1 months ago";
} }

Call this function like this:

GetDaysAgo("30/8/2009");

Store Procedure to be Used in Custom Pagging

2009-09-26T12:03:00.001+05:30

With the help of the below set of query we can get the number of record that we want to show to user.

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.)

USE DBName
GO
DECLARE @RecordsToPick smallint, @PageNumber smallint
SET @RecordsToPick = 10
SET @PageNumber = 2

DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = ((@PageNumber-1) * @RecordsToPick)
SET @EndRow = @StartRow + @RecordsToPick

SELECT * FROM ( SELECT UserName,UserID,City,State,Country ROW_NUMBER()OVER(ORDER BY UserID) AS RowNumber
FROM as_TblMembers) As AliasName WHERE RowNumber > @StartRow AND RowNumber <= @EndRowGO

In the above store procedure first we will get the @StartRow and @EndRow to get the number of first record and last record respectively.

ROW_NUMBER()OVER(ORDER BY UserID) AS RowNumber

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.

Post Your Queries

2009-09-22T14:59:00.003+05:30

Hello Friends,

You can post your queries here in this section as a comment. I will try my best to answer those queries or you can also post answer to the queries posted by other people. In this way you can help other and get help from others.

Thanks & Regards
Lakhan Pal Garg

Validate Textbox Value for Special Character Paste Data

2009-09-18T10:35:00.004+05:30

JavaScript Method to Validate '<' and '>' Character on paste data in text box or in Text Area.
Read the Text from clipboard and validate using ValidateText Function.

function OnTextPaste()
{
var data="";
data=window.clipboardData.getData("Text");
if(data)
{
var ret=ValidateText(data);
if(!ret)
alert('Sorry, you can not paste text having <, > in it.');
return ret;
}
else
return false;
}

function ValidateText(str)
{
var ret;
if(str.indexOf('<') != -1)
ret=false;
else if(str.indexOf('>') != -1)
ret=false;
else
ret=true;
return ret;
}

Create XML Data From SQL Server Using "For XML Explicit"

2009-09-17T11:56:00.005+05:30

There are different ways to get data in XML format from SQL Server like:

For XML Auto
For XML RAW
For XML Explicit

But in the first and second method we can't customize the format of XML. if we need customize XML then we need to use "For XML Explicit Method" With the Help of "For XML Explicit" we can create XML in the required format.

'Tag' and 'Parent' Column are used to determine the hierarchy of xml.
Now this code will generate a parent node name root and Session as Child of Root.

[Session!2!ID] this will create an attribute of Session Node Named ID.
and [Session!2!Notes!element] this will create a Child element of Session Named Notes.

Sample Code:
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [Session!2!ID],
NULL AS [Session!2!Notes!element],
0 AS [root!1!Customer!hide]
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
Session.SessionID AS [Session!2!ID],
Session.Notes AS [Session!2!Notes!element],
1 AS [root!1!Customer!hide]
FROM tbl_Session AS Session WHERE Session.SessionID<20
FOR XML EXPLICIT

Hide JavaScript Error

2009-09-16T18:06:00.004+05:30

This Method is used to Handle JavaScript Error that we don't want to show in case of unavoidable conditions.

Code:
function handleError()
{
return true;
}
window.onerror = handleError;

Parse XML Data in SQL Server 2005 without using OPENXML Method

2009-09-15T14:04:00.004+05:30

In SQL Server 2005 we can parse the XML Data without using OPENXML Methods that was used in SQL Server 2000.

There are few methods defined in SQL Server 2005 for XML DataType like:

nodes
query
value

Sample Code:

DECLARE @XmlData xml
SET @XmlData='<Library>
<Subject name="ASP.NET">
<Book ID="1">
<Author>Lakhan Pal Garg</Author>
<Title>ASP.NET Tips</Title>
<Price>$100</Price>
</Book>
<Book ID="2">
<Author>Lakhan Pal Garg</Author>
<Title>SQL Server Tips</Title>
<Price>$90</Price>
</Book>
</Subject>
<Subject name="XML">
<Book ID="3">
<Author>Peter</Author>
<Title>XSLT Tutorial</Title>
<Price>$140</Price>
</Book>
<Book ID="4">
<Author>Rihana</Author>
<Title>XML Parsing in SQL Server</Title>
<Price>$120</Price>
</Book>
</Subject>
</Library>'

select R.i.value('@ID', 'varchar(30)') [BookID],
R.i.query('Author').value('.', 'varchar(30)') [Author],
R.i.query('Title').value('.', 'varchar(30)') [Title],
R.i.query('Price').value('.', 'varchar(30)') [Price]

from @XmlData.nodes('/Library/Subject/Book') R(i)

In the above Select Statement we have used @XmlData.nodes and this will return a node list we used the Alias for this "R" and i is the index of the node. now to read the value of a attribute we can use R.i.value('@ID','INT') [BookID] here BookID is Alias name for column. and to read the value of an element that is child of Book we need to write like this R.i.query('Author').value('.','varchar(30)') [AuthorName] Author is the name of Child element of Book.

Payment Gateway Integration - Plimus

2009-09-14T13:16:00.005+05:30

To integrate Plimus first we need to create an account. using our account we can create products and contract in the products. we can define number of contract in a single product.

You can define products and contract either of recurring type or of non-recurring type too.

Here is a Sample code to integrate plimus as payment gateway either for one time payment or for Recurring payment.

<form id="form1" action="https://sandbox.plimus.com/jsp/buynow.jsp" method="post">
<div>
<input type="hidden" name="contractId" value="2114108" />
<input type="hidden" name="quantity" value="1" />
<input type="hidden" name="overridePrice" value="20" />
<input type="hidden" name="custom1" value="1001" />
<input type="hidden" name="enableCustom" value="Y" />
<input type="hidden" name="firstName" value="Lakhan" />
<input type="hidden" name="lastName" value="Pal" />
<input type="hidden" name="email" value="lakhangarg@gmail.com" />
<input type="hidden" name="address1" value="6252 NW 65 Terrace" />
<input type="hidden" name="address2" value="Test Course" />
<input type="hidden" name="city" value="Parkland" />
<input type="hidden" name="zipCode" value="33067" />
<input type="hidden" name="country" value="us" />
<input type="hidden" name="state" value="FL" />
<input type="hidden" name="workPhone" value="1-858-350-7473" />
<input type="hidden" name="cardNumber" value="4111111111111111" />
<input type="hidden" name="verifyCardCode" value="411" />
<input type="hidden" name="bCur" value="USD" />
<input type="hidden" name="currency" value="USD" />
<input type="submit" id="btnSubmit" name="btnSubmit" value="Plimus - Pay Now"/>
</div>
</form>

in the above sample custom1 defined as the custom variable we can define maximum five custom variable from the plimus site for each contract. on the contract detail page there is a link custom field to define custom variables.

bCur is used for base currency and currency is used to convert base currency into required type.

C# Connectivity with MYSQL using ODBC

2009-09-11T16:51:00.003+05:30

First of all Downlaod MyODBC-3.51.06.exe and install.

Now add the following lines of code to connect it to the DataBase.

string sConString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost;DATABASE=Lakhan; UID=root; PASSWORD=; OPTION=3";
OdbcConnection oConnection = new OdbcConnection(sConString);
string sSQL = "SELECT * FROM tdTest";
OdbcDataAdapter oDataAdapter = new OdbcDataAdapter(sSQL, oConnection);
DataSet oDataSet = new DataSet();
oDataAdapter.Fill(oDataSet);

This piece of code will return a DataSet that contains data from tdTest Table.

Regular Expression for Website URL

2009-09-08T18:59:00.003+05:30

(http|ftp|https):\/\/[\w\-_]+(\.[\w\-_]+)+([\w\-\.,@?^=%&:/~\+#]*[\w\-\@?^=%&/~\+#])?

^(((ht|f)tp(s?))\://)?(www.|[a-zA-Z].)[a-zA-Z0-9\-\.]+\.(com|edu|gov|mil|net|org|biz|info|name|museum|us|ca|uk)(\:[0-9]+)*(/($|[a-zA-Z0-9\.\,\;\?\'\\\+&%\$#\=~_\-]+))*$

/^((http(s?)|ftp))\:\/\/)?
(www.|[a-zA-Z].)[a-zA-Z0-9\-\.]+\.(com|edu|gov|mil|net|org|biz|info|name|museum|us|ca|uk)(\:[0-9]+)*(\/($|[a-zA-Z0-9\.\,\;\?\'\\\+&%\$#\=~_\-]+))*$/

Regular Expression for Password

2009-09-08T18:36:00.001+05:30

^.*(?=.{10,})(?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[@#$%^&+=]).*$

The regular expression enforces the following rules:
* Must be at least 10 characters
* Must contain at least one one lower case letter, one upper case letter, one digit and one special character
* Valid special characters (which are configurable) are - @#$%^&+=

^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{4,8}$

Password matching expression. Password must be at least 4 characters, no more than 8 characters, and must include at least one upper case letter, one lower case letter, and one numeric digit.

Regular Expression for Email

2009-09-08T18:27:00.003+05:30

/^([a-zA-Z0-9])+([\.a-zA-Z0-9_-])*@([a-zA-Z0-9])+(\.[a-zA-Z0-9_-]+)+$/

^(([a-zA-Z0-9])+([\.a-zA-Z0-9_-])*@([a-zA-Z0-9])+(\.[a-zA-Z0-9_-]+)?(\.[a-zA-Z0-9_-]+))$

\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*

JavaScript Object Notation (JSON)

2009-09-01T17:07:00.003+05:30

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. JSON is a language independent text format which is fast and easy to understand. JavaScript Object Notation (JSON) is a text format for the serialization of structured data. File extension for json file is *.json

The application/json is media type for JavaScript Object Notation (JSON). JSON can represent four primitive types:
• Strings (double-quoted Unicode with backslash escaping)
• Numbers (integer, real, or floating point)
• Booleans (true and false)
• Null
And two structured types:
• Objects (collection of key:value pairs, comma-separated and enclosed in curly braces)
• Arrays (an ordered sequence of values, comma-separated and enclosed in square brackets)

A JSON text is a serialized object or array.

JSON-text = object / array

These are the six structural characters:

begin-array [ left square bracket

begin-object { left curly bracket

end-array ] right square bracket

end-object } right curly bracket

name-separator : colon

value-separator , comma

The following example shows the JSON representation of an object that describes a person. The object has string fields for first name and last name, contains an object representing the person's address, and contains a list of phone numbers (an array)

.{
"firstName": "John",
"lastName": "Smith",
"address": {
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021"
},
"phoneNumbers": [
{ "type": "home", "number": "212 555-1234" },
{ "type": "fax", "number": "646 555-4567" }
]
}

The equivalent for the above in XML:

<Person firstName="John" lastName="Smith">
<Address>
<streetAddress>21 2nd Street</streetAddress>
<city>New York</city>
<state>NY</state>
<postalCode>10021</postalCode>
</Address>
<phoneNumber type="home">
212 555-1234
</phoneNumber>
<phoneNumber type="fax">
646 555-4567
</phoneNumber>
</Person>

JSON is like XML because:
1. They are both 'self-describing' meaning that values are named, and thus 'human readable'
2. Both are hierarchical. (i.e. You can have values within values.)
3. Both can be parsed and used by lots of programming languages
4. Both can be passed around using AJAX (i.e. httpWebRequest)

JSON is Unlike XML because:
1. XML uses angle brackets, with a tag name at the start and end of an element: JSON uses squiggly brackets with the name only at the beginning of the element.
2. JSON is less verbose so it's definitely quicker for humans to write, and probably quicker for us to read.
3. JSON can be parsed trivially using the eval() procedure in JavaScript
4. JSON includes arrays {where each element doesn't have a name of its own}
5. In XML you can use any name you want for an element, in JSON you can't use reserved words from JavaScript

What is good about JSON?
When you're writing Ajax stuff, if you use JSON, then you avoid hand-writing xml. This is quicker.
Again, when you're writing Ajax stuff, which looks easier? The XML approach or the JSON approach:
The XML approach:
1. Bring back an XML document
2. Loop through it, extracting values from it
3. Do something with those values, etc,
The JSON approach:
1. Bring back a JSON string.
2. 'eval' the JSON

Get the Number of Days in a Month for Given Month and Year

2009-08-31T17:54:00.004+05:30

With this sample code user can get the number of days in the Given Month and Year.

User's Input Data: '02-2009' or '02/2009'

There are two method to do this:

First Method:

DECLARE @Date varchar(20),@FirstDay varchar(2), @Seperator char(1)
SET @Date='02-2009'
SET @FirstDay='01'
SET @Seperator=SUBSTRING(@Date,3,1)
SET @Date=SUBSTRING(@Date,1,3) + @FirstDay + @Seperator + SUBSTRING(@Date,4,7)
SELECT DATEDIFF(DAY,Convert(Datetime,@Date), DATEADD(MONTH,1,Convert(Datetime,@Date))) 'Days'

Second Method:

DECLARE @Date varchar(20),@Month int, @NextMonth varchar(20), @Year varchar(5),@Seperator char(1)
DECLARE @FirstDay varchar(2)
SET @Date='02-2009'
SET @FirstDay='01'
SET @Year=SUBSTRING(@Date,4,7)
SET @Seperator=SUBSTRING(@Date,3,1)
SET @Month =Convert(int,(SUBSTRING(@Date,1,2)))+1
SET @Date=SUBSTRING(@Date,1,3)+@FirstDay+@Seperator+@Year
IF(@Month>12)
BEGIN
SET @Month=1
SET @Year=CONVERT(varchar(5), (CONVERT(int, @Year)+1))
END

SET @NextMonth=convert(varchar(2), @Month) + @Seperator + @FirstDay+@Seperator+@Year
SELECT Convert(int,((Convert(Datetime,@NextMonth)) - (Convert(Datetime,@Date))))

Output:
28 Days

Create XML Doc in C#

2009-08-28T15:33:00.004+05:30

This Sample code will show you how to create a XML document using C#.

Suppose we have Data like:

PublisherName: Wrox

Book Subject: ASP.NET

Book Title:

(1) Beginning ASP.NET MVC 1.0

(2) Silverlight 3 Programmer's Reference

(3) Professional Refactoring in C# & ASP.NET

Book Subject: SQL Server

Book Title:

(1) Beginning Microsoft SQL Server 2008 Administration

(2) Professional LINQ

And we want to show this data in XML format:

Sample Code to show above Data into XMl Format:

XmlDocument xDoc = new XmlDocument();

XmlNode ndRootNode = xDoc.CreateElement("BookData");

xDoc.AppendChild(ndRootNode);

XmlNode ndPublisher = xDoc.CreateElement("Publisher");

XmlAttribute atName = xDoc.CreateAttribute("Name");

atName.Value = "Wrox";

ndPublisher.Attributes.Append(atName);

ndRootNode.AppendChild(ndPublisher);

XmlNode ndBookSubject1 = xDoc.CreateElement("Subject");

XmlAttribute atSubject1 = xDoc.CreateAttribute("Name");

atSubject1.Value = "ASP.NET";

ndBookSubject1.Attributes.Append(atSubject1) ;

ndPublisher.AppendChild(ndBookSubject1);

XmlNode ndBookTitle1 = xDoc.CreateElement("Title");

ndBookTitle1.AppendChild(xDoc.CreateCDataSection("Beginning ASP.NET MVC 1.0"));

ndBookSubject1.AppendChild(ndBookTitle1);

XmlNode ndBookTitle2 = xDoc.CreateElement("Title");

ndBookTitle2.AppendChild(xDoc.CreateCDataSection("Silverlight 3 Programmer's Reference"));

ndBookSubject1.AppendChild(ndBookTitle2);

XmlNode ndBookTitle3 = xDoc.CreateElement("Title");

ndBookTitle3.AppendChild(xDoc.CreateCDataSection("Professional Refactoring in C# & ASP.NET"));

ndBookSubject1.AppendChild(ndBookTitle3);

XmlNode ndBookSubject2 = xDoc.CreateElement("Subject");

XmlAttribute atSubject2 = xDoc.CreateAttribute("Name");

atSubject2.Value = "SQL Server";

ndBookSubject1.Attributes.Append(atSubject2);

ndPublisher.AppendChild(ndBookSubject2);

XmlNode ndBookTitle4 = xDoc.CreateElement("Title");

ndBookTitle4.AppendChild(xDoc.CreateCDataSection("Beginning Microsoft SQL Server 2008 Administration"));

ndBookSubject2.AppendChild(ndBookTitle4);

XmlNode ndBookTitle5 = xDoc.CreateElement("Title");

ndBookTitle5.AppendChild(xDoc.CreateCDataSection("Professional LINQ"));

ndBookSubject2.AppendChild(ndBookTitle5);

Output:

<BookData>
<Publisher Name="Wrox">
<Subject Name="SQL Server">
<Title>Beginning ASP.NET MVC 1.0</Title>
<Title>Silverlight 3 Programmer's Reference</Title>
<Title>Professional Refactoring in C# & ASP.NET</Title>
</Subject>
<Subject>
<Title>Beginning Microsoft SQL Server 2008 Administration</Title>
<Title>Professional LINQ</Title>
</Subject>
</Publisher>
</BookData>

The CreateCDataSection

xDoc.CreateCDataSection("Beginning Microsoft SQL Server 2008 Administration")

is used to write the data in CData Section

JavaScript Interview Questions

2009-08-21T15:16:00.002+05:30

(1) Default setting for the expires attribute of the document.cookie property?
Ans: The duration of the browser session.

(2) Which JavaScript feature uses JAR files?
Ans: Object signing

(3) If onKeyDown event returns false, the key-press event is cancelled.

(4) File is a Server-side JavaScript Object.

(5) FileUpload is a Client-side JavaScript Object.

TextBox MaxLength Validation and Display Remaining Characters

2009-08-21T15:01:00.002+05:30

Text Box OR Text area max length validation and also display the words remaining.
Arguments:
(1) TextBox or Text Area ID
(2) Label ID where we want to show count of remaining character.
(3) Number of Maximum character that are allowed.

Sample Code:
function CountCharactersGeneral(sourceTextBox, displayControl, maxLength)
{
if(sourceTextBox != null && displayControl != null)
{
sourceTextBox = document.getElementById(sourceTextBox);
displayControl = document.getElementById(displayControl);
if(sourceTextBox != null)
{
var len = sourceTextBox.value.length
if (len<=maxLength)
{
displayControl.innerHTML = maxLength -len +" Character(s) remaining.";
}
else
{
sourceTextBox.value = sourceTextBox.value.substring(0, maxLength);
return false;
}
}
}
}

Show Current - Date,Month,Year in JavaScript

2009-08-21T14:53:00.002+05:30

This Code will return the Result in numeric form as :
for date 21/8/2009:

objDate.getDate(); will return 21.
objDate.getMonth() + 1 will return 8
objDate.getFullYear() will return 2009

var objDate=new Date();
var today=objDate.getDate();
var month=objDate.getMonth() + 1;
var year=objDate.getFullYear();

Sample Code To Show Today's Detail:
var months = new Array(12);
months[0]="January";
months[1]="February";
months[2]="March";
months[3]="April";
months[4]="May";
months[5]="June";
months[6]="July";
months[7]="August";
months[8]="September";
months[9]="October";
months[10]="November";
months[11]="December";

var days = new Array(7);
days[0]="Sunday";
days[1]="Monday";
days[2]="Tuesday";
days[3]="Wednesday";
days[4]="Thursday";
days[5]="Friday";
days[6]="Saturday";
var objDate=new Date();
var today=objDate.getDay();
var date=objDate.getDate();
var month=objDate.getMonth();
var year=objDate.getFullYear();
document.write("Today is : " + days[today] + ", " + months[month] + " " + date+ ", " + year);
document.write("Current date is : " + today + " / " + month + " / " + year);

Display Random Message on Page Refresh

2009-08-21T14:50:00.000+05:30

Show Rendom Message On Page refresh.
Place all the Messages in an Array
Math.floor(Math.random() * 8) function will pick the random record from array.

Sample Code:

<script language="JavaScript" type="text/javascript">
var arMessage = new Array(
'She is a complete waste of space',
'I did not recognize you with your clothes on ',
'If I never see you again, it will be too soon ',
'He is so full of shit, even his eyes are brown',
'You are so far up our managers ass, I can almost see your legs',
'She is as welcome as a turd in a swimmingpool',
'My boss is an accident waiting for a place to happen',
'I would not piss on you, even when you were on fire');
var ind = Math.floor(Math.random() * 8);
document.write('<b class="r">'+arMessage[ind]+'</b>');
</script>

Code to Create Own Currency Convertor

2009-08-21T14:46:00.003+05:30

XML Based Currency Converter

Sample Code:

using System.Xml;
private void ReadXMLData(string SourceCountry, string DestCountry)
{
double Amount = 10;
string SourceCode = "";
double SourceCountryValue=0;
double DestCountryValue=0;
string DestCode = "";
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(Server.MapPath("Convertor.xml"));
XmlNodeList xmlList= xmlDoc.GetElementsByTagName("Country");
for (Int16 iCount = 0; iCount <>
{
if (xmlList.Item(iCount).Attributes.GetNamedItem("name").InnerText == SourceCountry)
{
SourceCountryValue = Convert.ToDouble(xmlList.Item(iCount).Attributes.GetNamedItem("value").InnerText);
SourceCode = xmlList.Item(iCount).Attributes.GetNamedItem("CurrencyCode").InnerText;
}
else if (xmlList.Item(iCount).Attributes.GetNamedItem("name").InnerText == DestCountry)
{
DestCountryValue = Convert.ToDouble(xmlList.Item(iCount).Attributes.GetNamedItem("value").InnerText);
DestCode = xmlList.Item(iCount).Attributes.GetNamedItem("CurrencyCode").InnerText;
}
}
Response.Write(Amount.ToString()+ " "+SourceCode +"=" +Convert.ToString(Amount * (DestCountryValue / SourceCountryValue)));
}
Call the above Method Like This: ReadXMLData("UK", "India");
Click Here to get the Sample Data.

Clear value of controls on a page

2009-08-21T14:41:00.003+05:30

This Code is used to clear the value of TextBox, DropDownList, CheckBox, CheckBoxList controls available in any container control.

in this Sample Code we need to pass the instance of the form (Page obj) and the Container Control's ID.

Sample Code:
public static void ClearControls( Page obj, string strContentHolder)
{
foreach (Control c in obj.Master.FindControl (strContentHolder).Controls)
{

if (c.GetType().ToString().Equals("System.Web.UI.WebControls.TextBox"))
{

TextBox t = (TextBox)c;
t.Text ="";
}
if (c.GetType().ToString().Equals("System.Web.UI.WebControls.DropDownList"))
{
DropDownList ddl = (DropDownList)c;
ddl.SelectedIndex = 0;
}
if (c.GetType().ToString().Equals("System.Web.UI.WebControls.CheckBox"))
{
CheckBox chk = (CheckBox)c;
chk.Checked =false;
}

if (c.GetType().ToString().Equals("System.Web.UI.WebControls.CheckBoxList"))
{
CheckBoxList chkList = (CheckBoxList)c;
for (int i = 0; i <>
{
chkList.Items[i].Selected =false;
}
}
}
}

Implement Multilingual in ASP.NET Web Site using globalization.

2009-08-17T17:03:00.007+05:30

Before going to implement we need to know the answer of few question:

What is globalization?
Globalization is the process of making an application that supports multiple cultures without mixing up the business logic and the culture related information of that application.

What is localization?
In Localization you customize the application for new locales. This consists of translating resources that you identified during the globalization phase.

What is Local Resource?
File which stores resources with local scope into App_LocalResource directory. We can not access one pages local resource from another page.
They can be generated by ASP.Net automatically. For it go to design view of aspx page and then Tools > Generate

What is Global Resource?
File used to store resources globally, means we can access same resource from different pages. It store in App_GlobalResource folder. we can't generate global resource automatically.

Difference between Local Resource and Global Resource:

Performance: Global Resources are faster than Local Resource- because Global Resources are Strongly Typed.
Consistency. Explanation: In your application you have Label Text as Category and ten pages. Now you want to change it to CATEGORY then if you using Local Resource concept then you have to change 10 resource files, where as in Global Resource you need to change only one.
Difference as per Project Type:
- Local Resource is treated as content based resource, so we can change it and it affects to website.
- Global Resource is treated as embedded resource, So they are compiled into specific language dlls into bin folder.

Code to implement multilingual using global resource:

Add This in global.asax file:

private void Application_BeginRequest(Object source, EventArgs e)
{
string[] languages = HttpContext.Current.Request.UserLanguages;
if (languages[0].ToLower() != null && languages[0].ToLower()!="")
{
System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(languages[0].ToLower());
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture(languages[0].ToLower());

}
}

now we can use resource file on page like this:
suppose we have three resource file in app_globalresource folder:
TestResource.resx (Default)
TestResource.hi.resx (Hindi)
TestResource.zn-ch.resx (Chinese)
<:Label ID="lblText" runat="server" Text="<%$ Resources:TestResource, LabelText %&>">

now as we set the language from browser, the text for label will be read from respected files.

Get Median value from SQL Server Table

2009-08-05T14:42:00.002+05:30

Sample code to get the median of table data in SQL Server. Declare two variables first to get the number of rows and second to store median value.
DECLARE @Count int,@Median Float

Query to get the number of rows:
SELECT @Count=COUNT(*) FROM (
SELECT ID,COUNT(ID) AS IDCount FROM MyTable GROUP BY ID)AB

if the number of rows are even then query to get the median value is:
SELECT @Median=(SUM(Convert(float,IDCount))/2) FROM(SELECT ID,COUNT(ID)AS IDCount,ROW_NUMBER() OVER(ORDER BY COUNT(ID))AS ROW
FROM MyTable GROUP BY ID)AB
WHERE AB.Row IN ((@Count/2),(@Count/2)+1)

Query to get the median in case of odd rows:
SELECT @Median=IDCount FROM(SELECT ID,COUNT(ID)AS IDCount,ROW_NUMBER() OVER(ORDER BY COUNT(ID))AS ROW
FROM MyTable GROUP BY ID)AB
WHERE AB.Row =((@Count/2)+1)

Sample Query:
DECLARE @Count int,@Median Float
SELECT @Count=COUNT(*) FROM (
SELECT ID,COUNT(ID) AS IDCount FROM MyTable GROUP BY ID)AB

DECLARE @SQLQuery varchar(500)
IF(@Count%2=0)
BEGIN
SELECT @Median=(SUM(Convert(float,IDCount))/2) FROM(SELECT ID,COUNT(ID)AS IDCount,ROW_NUMBER() OVER(ORDER BY COUNT(ID))AS ROW
FROM MyTable GROUP BY ID)AB
WHERE AB.Row IN ((@Count/2),(@Count/2)+1)
END
ELSE
SELECT @Median=IDCount FROM(SELECT ID,COUNT(ID)AS IDCount,ROW_NUMBER() OVER(ORDER BY COUNT(ID))AS ROW
FROM MyTable GROUP BY ID)AB
WHERE AB.Row =((@Count/2)+1)
SELECT @Median

HTTP Request Message Format

2009-07-29T18:55:00.008+05:30

Key Concept: HTTP requests are the means by which HTTP clients ask servers to take a particular type of action, such as sending a file or processing user input. Each request message begins with a request line, which contains three critical pieces of information: the method (type of action) the client is requesting; the URI of the resource upon which the client wishes the action to be performed, and the version of HTTP that the client is using. After the request line come a set of message headers related to the request, followed by a blank line and then optionally, the message body of the request.

The client initiates an HTTP session by opening a TCP connection to the HTTP server with which it wishes to communicate. It then sends request messages to the server, each of which specifies a particular type of action that the user of the HTTP client would like the server to take. Requests can be generated either by specific user action (such as clicking a hyperlink in a Web browser) or indirectly as a result of a prior action (such as a reference to an inline image in an HTML document leading to a request for that image.)

HTTP requests use a message format that is based on the generic message format described in the preceding topic, but specific to the needs of requests. The structure of this format is as follows:

This diagram shows the structural elements of an HTTP request and an example of the sorts of headers a request might contain. Like most HTTP requests, this one carries no entity, so there are no entity headers and the message body is empty.

Request Line

The generic start line that begins all HTTP messages is called a request line in request messages. It has a three-fold purpose: to indicate the command or action that the client wants performed; to specify a resource upon which the action should be taken; and to indicate to the server what version of HTTP the client is using. The formal syntax for the request line is:

Method

The method is simply the type of action that the client wants the server to take; it is always specified in upper case letters. There are eight standard methods defined in HTTP/1.1, of which three are widely used: GET, HEAD and POST. They are called “methods” rather than “commands” because the HTTP standard uses terminology from object-oriented programming. I explain this and also describe the methods themselves in the topic describing HTTP methods.

Request URI

The request URI is the uniform resource identifier of the resource to which the request applies. While URIs can theoretically refer to either uniform resource locators (URLs) or uniform resource names (URNs), at the present time a URI is almost always an HTTP URL that follows the standard syntax rules of Web URLs.

Interestingly, the exact form of the URL used in the HTTP request line usually differs from that used in HTML documents or entered by users. This is because some of the information in a full URL is used to control HTTP itself. It is needed as part of the communication between the user and the HTTP client, but not in the request from the client to the server. The standard method of specifying a resource in a request is to include the path and file name in the request line (as well as any optional query information) while specifying the host in the special Host header that must be used in HTTP/1.1 requests.

For example, suppose the user enters a URL such as this:

http://www.myfavoritewebsite.com:8080/chatware/chatroom.php

We obviously don't need to send the “http:” to the server. The client would take the remaining information and split it so the URI was specified as “/chatware/chatroom.php” and the Host line would contain “www.myfavoritewebsite.com:8080”. Thus, the start of the request would look like this:

GET /chatware/chatroom.php HTTP/1.1
Host: www.myfavoritewebsite.com:8080

The exception to this rule is when a request is being made to a proxy server. In that case, the request is made using the full URL in its original form, so that it can be processed by the proxy just as the original client did. The request would be:

GET http://www.myfavoritewebsite.com:8080/chatware/chatroom.php HTTP/1.1

Finally, there is one special case where a single asterisk can be used instead of a real URL. This is for the OPTIONS method, which does not require the specification of a resource. (Nominally, the asterisk means the method refers to the server itself.)

HTTP Version

The HTTP-VERSION element tells the server what version the client is using so the server knows how to interpret the request, and what to send and not to send the client in its response. For example, a server receiving a request from a client using versions 0.9 or 1.0 will assume that a transitory connection is being used rather than a persistent one, and will avoid using version 1.1 headers in its reply. The version token is sent in upper case as “HTTP/0.9”, “HTTP/1.0” or “HTTP/1.1”—just the way I've been doing throughout my discussion of the protocol.

Headers

After the request line come any of the headers that the client wants to include in the message; it is in these headers that details are provided to the server about the request. The headers all use the same structure, but are organized into categories based on the functions they serve, and whether they are specific to one kind of message or not

General Headers: General headers refer mainly to the message itself, as opposed to its contents, and are used to control its processing or provide the recipient with extra information. They are not particular to either request or response messages, so they can appear in either. They are likewise not specifically relevant to any entity the message may be carrying.

Request Headers: These headers convey to the server more details about the nature of the client's request, and give the client more control over how the request is handled. For example, special request headers can be used by the client to specify a conditional request—one that is only filled if certain criteria are met. Others can tell the server which formats or encodings the client is able to process in a response message.
Entity Headers: These are headers that describe the entity contained in the body of the request, if any.

Request headers are obviously used only in request messages, but both general headers and entity headers can appear in either a request or a response message. Since there are so many headers and most are not particular to one message type, they are described in detail in their own complete section.

Info Collected from : http://www.tcpipguide.com/free/t_HTTPRequestMessageFormat.htm

Dot Net Interview Tips

2009-07-28T16:47:00.004+05:30

Data that can be stored in a cookie? 4 KB/2096 Bytes
Most Browsers provides a limit of storing cookies is 20. If new cookie came, it will discard the old one but some of browser support up to 300 cookies.
Persistent Cookies can be called as permanent cookie. Which is stored in client hard drive until it expires. While non persistent cookies can be called as temporary cookies, if there is no expire time defined then the cookie is stored in the browser’s memory.
Difference in persistence and non persistence cookies is persistence cookies should have an expiration time defined within it.
Where does cookies are stored in local hard drive? C:\Documents and Settings\{Current Login User}\Cookies
To expire the persistent cookies before its expiration time the expiration time as the Now.DateTime.AddDays(-1);
‘Cookie Munging’ is a concept of ASP.Net which lets users to manage session variables if their browsers do not support cookies. By default, ASP.Net uses cookies to store session ID’s. But, as we all know that some browsers do not support cookies. To overcome from this ASP.Net uses ‘Cookie Munging’ to manage session variables without cookies.
How Cookie Munging works?
When user requests page from server ASP.Net adds encoded session ID to every href in page. When user clicks a link ASP.Net decodes that encoded session ID and passes it back to the page the user is requesting. Now, the requesting page can use that ID to set or retrieve any session variables. This all happens automatically, if asp.net detects that the users browser do not support cookies.

Read, Insert and Update Data into Excel Sheet

2009-07-18T12:51:00.008+05:30

This application will tell you about the operation on excel sheet (like Insert, Update and Read) with the help of simple queries.
i am using OLEDB provider for these operations.
Connection String for OLEDB is:

string file = Server.MapPath("UserData.xls"); string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";
Extended Properties=Excel 8.0;";

Read data and display the result in repeater control:
First we'll read the data from the Excel sheet and display the result into a repeater control.
first query to fetch the records:

string query = "Select UserID,UserName,Country,State,City from [Sheet1$]";

[Sheet1$] name of the sheet in the Excel workbook.And UserID,UserName,Country,State,City are the name of the column in this sheet.
this query will return all the records from Excel Sheet [Sheet1$].
Code to execute the above query is:

DataSet dsUserData = new DataSet();
using (OleDbConnection Connection = new OleDbConnection(constr))
{
using (OleDbDataAdapter DataAdapter = new OleDbDataAdapter(query, Connection))
{
DataAdapter.Fill(dsUserData, "UserData");
DataAdapter.AcceptChangesDuringFill = false;
DataAdapter.Dispose();
Connection.Close();
}
}

Update Data:
we can click on the Edit Data link for the corresponding row to edit the record.
on click of Edit Data button the corresponding records data will be shown in textboxes. user can change the text and click on the "Update Excel Data" to reflect changes is Excel Sheet.
Code to Update Record:

string file = Server.MapPath("UserData.xls");
string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";
using (OleDbConnection Connection = new OleDbConnection(constr))
{
Connection.Open();
string query = "UPDATE [Sheet1$] SET UserName=\"" + txtUserName.Text.Trim() + "\",Country=\"" + txtCountry.Text.Trim() + "\",State=\"" + txtState.Text.Trim() + "\",City=\"" + txtCity.Text.Trim() + "\" WHERE UserID="+ btnUpdate.CommandArgument.ToString();
using (OleDbCommand objCmd = new OleDbCommand(query, Connection))
{
objCmd.ExecuteNonQuery();
objCmd.Dispose();
Connection.Close();
}
}

Similar way we can add the new record into the Excel Sheet. first we will get the UserId of the Last record and add one to that record to get the next UserID as:

Int32 LastUserID = Convert.ToInt32(((Label)rptUserData.Items[rptUserData.Items.Count - 1].FindControl("lblID")).Text);
LastUserID += 1;
Click Here To Download Source Code

Note: Please contact me in case you have any error in download the code: lakhangarg@gmail.com

Note: User the Following Connection String for xlsx file:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties=Excel 12.0;"

Sorting in Repeater Control

2009-07-18T12:30:00.007+05:30

To implement sorting in repeater control first add the column name as link button:

<asp:LinkButton ID="lnkUserName" runat="server" CommandName="UserName">User Name</asp:LinkButton>

Please Note : Command Name Should be Same as the name of the column in your Datatable.

In this app we are using ViewState to Store the Column name and the Sort Order.
ViewState["Column"] & ViewState["Order"]

The Purpose of using the CommandName same as of Column name to avoid more hardcoding and to make application simple.

we can simply get the column name from e.CommanName.

Sample Code:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//Set View State Value for Initial Sort Order
ViewState["Column"] = "UserName";
ViewState["Order"] = "ASC";

//Call To Method to Bind Repeater
BindRepeater();
}
}

/// <summary>
/// Bind Repeater with the Data
/// </summary>
private void BindRepeater()
{
DataTable dtData = GetData();
DataView dvData = new DataView(dtData);

//Sorting Filter
dvData.Sort = ViewState["Column"].ToString() + " " + ViewState["Order"].ToString();

rptUserData.DataSource = dvData;
rptUserData.DataBind();
dtData.Dispose();
dvData.Dispose();
}

/// <summary>
/// Method To Get Data in the form of DataTable
/// </summary>
/// <returns>DataTable</returns>
private DataTable GetData()
{
DataTable dtNew = new DataTable();
DataColumn dcUserName = new DataColumn("UserName");
dtNew.Columns.Add(dcUserName);
DataColumn dcAge = new DataColumn("Age");
dtNew.Columns.Add(dcAge);
DataColumn dcCountry = new DataColumn("Country");
dtNew.Columns.Add(dcCountry);
DataColumn dcState = new DataColumn("State");
dtNew.Columns.Add(dcState);
DataColumn dcCity = new DataColumn("CityName");
dtNew.Columns.Add(dcCity);
DataRow dr;

dr = dtNew.NewRow();
dr["UserName"] = "Lakhan Pal Garg";
dr["Age"] = "26";
dr["Country"] = "India";
dr["State"] = "Punjab";
dr["CityName"] = "Chandigarh";
dtNew.Rows.Add(dr);

dr = dtNew.NewRow();
dr["UserName"] = "Naveen Garg";
dr["Age"] = "24";
dr["Country"] = "Canada";
dr["State"] = "Ontario";
dr["CityName"] = "Toronto";
dtNew.Rows.Add(dr);

dr = dtNew.NewRow();
dr["UserName"] = "Amrik Singh";
dr["Age"] = "28";
dr["Country"] = "India";
dr["State"] = "Haryana";
dr["CityName"] = "Pehowa";
dtNew.Rows.Add(dr);

dr = dtNew.NewRow();
dr["UserName"] = "Puneet Sharma";
dr["Age"] = "25";
dr["Country"] = "England";
dr["State"] = "Rutland";
dr["CityName"] = "Oakham";
dtNew.Rows.Add(dr);

dr = dtNew.NewRow();
dr["UserName"] = "Avinash";
dr["Age"] = "40";
dr["Country"] = "USA";
dr["State"] = "New York";
dr["CityName"] = "Albany";
dtNew.Rows.Add(dr);

return dtNew;
}

/// <summary>
/// Repeater's Item Command Event
/// In this we will set the Column name and sorting Order for the selected column.
/// </summary>
protected void rptUserData_ItemCommand(object source, RepeaterCommandEventArgs e)
{
if (e.CommandName == ViewState["Column"].ToString())
{
if (ViewState["Order"].ToString() == "ASC")
ViewState["Order"] = "DESC";
else
ViewState["Order"] = "ASC";
}
else
{
ViewState["Column"] = e.CommandName;
ViewState["Order"] = "ASC";
}

//Call To Method to Bind Repeater
BindRepeater();
}
}

Click Here To Download Source Code

Note: Please contact me in case you have any error in download the code: lakhangarg@gmail.com

SCOPE_IDENTITY() v/s @@IDENTITY

2009-07-16T14:31:00.002+05:30

SCOPE_IDENTITY() v/s @@IDENTITY:
Both SCOPE_IDENTITY() and @@IDENTITY will return the last identity value generated in the table. but there is some difference between the two:

SCOPE_IDENTITY() will return the Identity value generated in a table that is currently in scope.

@@IDENTITY will return the Identity value generated in a table irrespective of the scope.

For Example:
Let us suppose we have two tables named table1 & table2... and we have one trigger defined on table1 that is insert a record in table2 when new record will be inserted into table1.

in this case the Output of Both SCOPE_IDENTITY() and @@IDENTITY will be different.
SCOPE_IDENTITY() will return the identity value of table1 that is in current scope.
while @@IDENTITY will return the identity value of table2.

Create Thumbnail

2009-07-15T14:53:00.003+05:30

This Code will help the users to create thumbnail of an image. for this user need to pass three parameters as:
(1) Image To Scale
(2) Desired height of the output image
(3) Desired width of output image

First we will get the image in a Bitmap Object and then we will scale the image.

Here is the Code to create thumbnail:

/// Scales an image as per the ratio of dimensions of the image to hight and width specified in the
/// parameters

public static System.Drawing.Image ScaleByPercent(string strImage, double dblImgHt, double dblImgWd)
{
Bitmap imgRetPhoto = null;
double dblWdRatio, dblHtRatio;

try
{
imgRetPhoto = new Bitmap(strImage);
if (imgRetPhoto.Height > Convert.ToInt32(dblImgHt) || imgRetPhoto.Width > Convert.ToInt32(dblImgWd))
{
if (imgRetPhoto.Height > dblImgHt)
{
dblHtRatio = dblImgHt / Convert.ToDouble(imgRetPhoto.Height);
dblWdRatio = Convert.ToDouble(imgRetPhoto.Width) * dblHtRatio;
imgRetPhoto = new Bitmap(imgRetPhoto, Convert.ToInt32(dblWdRatio), Convert.ToInt32(dblImgHt));
imgRetPhoto.SetResolution(imgRetPhoto.HorizontalResolution, imgRetPhoto.VerticalResolution);
}

if (imgRetPhoto.Width > dblImgWd)
{
dblWdRatio = dblImgWd / Convert.ToDouble(imgRetPhoto.Width);
dblHtRatio = Convert.ToDouble(imgRetPhoto.Height) * dblWdRatio;
imgRetPhoto = new Bitmap(imgRetPhoto, Convert.ToInt32(dblImgWd), Convert.ToInt32(dblHtRatio));
imgRetPhoto.SetResolution(imgRetPhoto.HorizontalResolution, imgRetPhoto.VerticalResolution);
}
return imgRetPhoto;
}
else
return imgRetPhoto;
}
catch (Exception ex)
{
throw ex;
}
}

Function To Get Tabular Data From a Delimilted String Variable

2009-07-15T11:16:00.007+05:30

In this there are two input variables. first is delimited string variable and the second is delimited character.

Sample Code:

CREATE FUNCTION [dbo].[function_string_to_table]
(
@string VARCHAR(7999),
@delimiter CHAR(1)
)
RETURNS @output TABLE(
data VARCHAR(256)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

WHILE @start <>
IF @end = 0
SET @end = LEN(@string) + 1

INSERT INTO @output (data)
VALUES(LTRIM(RTRIM(SUBSTRING(@string, @start, @end - @start))))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END

Suppose we have string variable like:'1,2,3,4,5'
and in this the delimiter character is comma ','
if we call this as:
SELECT [dbo].[function_string_to_table]('1,2,3,4,5',',')

Output of this will be :
1
2
3
4
5

Get Values of a Column in a Variable

2009-07-15T11:03:00.003+05:30

Get all the values of a single column in a variable separated by comma.
DECLARE a Variable and set the Size of the variable according to your requirement. here in this code i have mentioned MAX.

Now Using :
SELECT @TempString = @TempString + CityName +',' FROM Tbl_City

Statement we can append all the values in @TempString separated by comma.

@TempString contains a extra comma at the end. so we need to remove that with the help of :
SUBSTRING(@TempString,0,LEN(@TempString))

Sample Code:

DECLARE @TempString VARCHAR(MAX)
SET @TempString = ''
SELECT @TempString = @TempString + CityName +',' FROM Tbl_City
SELECT SUBSTRING(@TempString,0,LEN(@TempString))

Directly Create SQL Update Statements of Data in a Table

2009-07-14T18:22:00.003+05:30

These Set of statements are used to directly create SQL update statements of data for given table.
we just need to pass the name of the Table. Here in this just replace 'MyTestTable' with your own table name.

if there is any identity Column in your table then that will be used for the where condition otherwise first numeric column will be used for where condition.

DECLARE @TempString VARCHAR(7999),@Tablename varchar(200)
SET @Tablename='MyTestTable'
SET @TempString = ''

SELECT @TempString = @TempString + SYS.Columns.Name+'=''''''+ISNULL('+SYS.Columns.Name+','''')+'''''','
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id IN (239,175,167,231,99,35,34)AND SYS.Columns.IS_Identity=0

SELECT @TempString = @TempString + SYS.Columns.Name+'=''''''+CONVERT(varchar(30),ISNULL('+SYS.Columns.Name+',''1/1/1900''))+'''''','
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id IN (61)AND SYS.Columns.IS_Identity=0

SELECT @TempString = @TempString + SYS.Columns.Name+'=''+CONVERT(varchar(10),ISNULL('+SYS.Columns.Name+',0))+'','
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id NOT IN (239,175,167,231,99,35,34,61)AND SYS.Columns.IS_Identity=0

SET @TempString= SUBSTRING(@TempString,0,LEN(@TempString)-2)

DECLARE @ConditionColumn varchar(200)
SELECT @ConditionColumn=SYS.Columns.name FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and IS_Identity=1

IF (@ConditionColumn=NULL)
SELECT TOP 1 @ConditionColumn=SYS.Columns.name FROM SYS.Columns Inner JOIN SYS.Objects
ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename AND
SYS.Columns.System_type_id NOT IN (239,175,167,231,99,35,34,61)

SET @ConditionColumn=' +'' WHERE '+@ConditionColumn+'=''+CONVERT(varchar(10),ISNULL('+@ConditionColumn+',0))'

DECLARE @SQLQuery varchar(Max),@SubQuery varchar(Max)
SET @SubQuery='''UPDATE '+@Tablename+' SET '+@TempString+ ISNULL(@ConditionColumn,'')

SET @SQLQuery= 'SELECT ' +@SubQuery+' FROM '+@Tablename
EXEC (@SQLQuery)

Directly Create SQL Insert Statements of Data in a Table

2009-07-14T18:14:00.005+05:30

This Statements are Used to directly create SQL insert statements of data in a table.
we just need to pass the name of the SQL Table as i passed
Replace 'MyTestTable' with your table name and execute these statement.

Query for SQL Server 2005 and 2008:

DECLARE @TempString VARCHAR(7999),@Tablename varchar(200),@ColumnName varchar(500)
SET @Tablename='Room_Product'
SET @TempString = ''
SET @ColumnName=''

SELECT @TempString = @TempString + '+'''''',''''''+ISNULL('+SYS.Columns.Name+','''')'
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id IN (239,175,167,231,99,35,34)

IF(@TempString <>'')
SET @TempString='''''''' + @TempString

SELECT @TempString = @TempString + '+'''''',''''''+CONVERT(varchar(30),ISNULL('+SYS.Columns.Name+',0))'
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id IN (61)

SET @TempString=@TempString+'+'''''''''

SELECT @ColumnName = @ColumnName + ','+SYS.Columns.Name
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id IN (239,175,167,231,99,35,34)

SELECT @ColumnName = @ColumnName + ','+SYS.Columns.Name
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id IN (61)

SELECT @TempString = @TempString + '+'',''+CONVERT(varchar(10),ISNULL('+SYS.Columns.Name+',0))'
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id NOT IN (239,175,167,231,99,35,34,61)

SELECT @ColumnName = @ColumnName + ','+SYS.Columns.Name
FROM SYS.Columns Inner JOIN SYS.Objects ON SYS.Objects.Object_Id=SYS.Columns.Object_Id
WHERE SYS.Objects.type='u' and SYS.Objects.name=@Tablename and
SYS.Columns.System_type_id NOT IN (239,175,167,231,99,35,34,61)

SET @TempString= SUBSTRING(@TempString,9,LEN(@TempString)-8)
SET @ColumnName= SUBSTRING(@ColumnName,2,LEN(@ColumnName)-1)
--SELECT @TempString,@ColumnName

DECLARE @SubQuery varchar(5000),@SQLQuery varchar(max)
SET @SubQuery='INSERT INTO '+@Tablename+'('+@ColumnName+') VALUES ('+@TempString+'+'''+')'
SET @SQLQuery='SELECT '''+@SubQuery+''' FROM '+@Tablename

EXEC (@SQLQuery)

Query for SQL Server 2000:

DECLARE @TempString VARCHAR(7999),@Tablename varchar(200),@ColumnName varchar(500)
SET @Tablename='tblProdline'
SET @TempString = ''
SET @ColumnName=''

SELECT @TempString = @TempString + '+'''''',''''''+ISNULL('+SYSColumns.Name+','''')'
FROM SYSColumns Inner JOIN SYSObjects ON SYSObjects.ID=SYSColumns.ID
WHERE SYSObjects.type='u' and SYSObjects.name=@Tablename and
SYSColumns.xtype IN (239,175,167,231,99,35,34)

IF(@TempString <>'')
SET @TempString='''''''' + @TempString

SELECT @TempString = @TempString + '+'''''',''''''+CONVERT(varchar(30),ISNULL('+SYSColumns.Name+',0))'
FROM SYSColumns Inner JOIN SYSObjects ON SYSObjects.ID=SYSColumns.ID
WHERE SYSObjects.type='u' and SYSObjects.name=@Tablename and
SYSColumns.xtype IN (61)

SET @TempString=@TempString+'+'''''''''

SELECT @ColumnName = @ColumnName + ','+SYSColumns.Name
FROM SYSColumns Inner JOIN SYSObjects ON SYSObjects.ID=SYSColumns.ID
WHERE SYSObjects.type='u' and SYSObjects.name=@Tablename and
SYSColumns.xtype IN (239,175,167,231,99,35,34)

SELECT @ColumnName = @ColumnName + ','+SYSColumns.Name
FROM SYSColumns Inner JOIN SYSObjects ON SYSObjects.ID=SYSColumns.ID
WHERE SYSObjects.type='u' and SYSObjects.name=@Tablename and
SYSColumns.xtype IN (61)

SELECT @TempString = @TempString + '+'',''+CONVERT(varchar(10),ISNULL('+SYSColumns.Name+',0))'
FROM SYSColumns Inner JOIN SYSObjects ON SYSObjects.ID=SYSColumns.ID
WHERE SYSObjects.type='u' and SYSObjects.name=@Tablename and
SYSColumns.xtype NOT IN (239,175,167,231,99,35,34,61)

SELECT @ColumnName = @ColumnName + ','+SYSColumns.Name
FROM SYSColumns Inner JOIN SYSObjects ON SYSObjects.ID=SYSColumns.ID
WHERE SYSObjects.type='u' and SYSObjects.name=@Tablename and
SYSColumns.xtype NOT IN (239,175,167,231,99,35,34,61)

SET @TempString= SUBSTRING(@TempString,9,LEN(@TempString)-8)
SET @ColumnName= SUBSTRING(@ColumnName,2,LEN(@ColumnName)-1)
--SELECT @TempString,@ColumnName

DECLARE @SubQuery varchar(5000),@SQLQuery varchar(8000)
SET @SubQuery='INSERT INTO '+@Tablename+'('+@ColumnName+') VALUES ('+@TempString+'+'''+')'
SET @SQLQuery='SELECT '''+@SubQuery+''' FROM '+@Tablename

EXEC (@SQLQuery)

Update Table Query to update data from another table directly

2009-07-04T14:04:00.006+05:30

Suppose we have two tables names:
Tbl_Topics & Tbl_TeacherTopics having columns
MyTopic, TopicId, TopicName
TeacherTopicID, TopicId, TopicName
And we want to update Tbl_Topics Table from Tbl_TeacherTopics for given TeacherTopicID

Query:

UPDATE Tbl_Topics SET TopicID=TT.TopicID,TopicName=TT.TopicName FROM
(SELECT TopicID,TopicName FROM Tbl_TeacherTopics WHERE TeacherTopicID=1062)AS TT
WHERE Tbl_Topics.MyTopic='Lakhan Pal garg'

Save Image For given URL

2009-06-22T17:39:00.003+05:30

Set Url of the Image you want to save on your system.
DestinationPath is the path where you want to save the image.
in this case it is Products folder on the root of the application folder.
strImage = url.Substring(url.LastIndexOf('/') + 1);
above line of code will give us the name of the image.
GetBytesFromUrl(url); method will return the Byte array of the image
for the corrsponding url of the image.
Once we got the Byte image data in the form of Byte array then
we can save the image using WriteBytesToFile method.

string url = "http://3.bp.blogspot.com/_Kp--fzJWFmc/
SNH1SzkEZoI/AAAAAAAAADU/W0bjZKqem6s/S220/lakhan.jpg";
strImage = url.Substring(url.LastIndexOf('/') + 1);
string DestinationPath = Server.MapPath("~/Products");
byte[] bytes = GetBytesFromUrl(url);
WriteBytesToFile(DestinationPath + "/" + strImage, bytes);

First we make the request for the given image url and server
will send us a response for that request in the form of stream of data.

With the help of BinaryReader system will read the content of image
and add that into byte array.

static public byte[] GetBytesFromUrl(string url)
{
byte[] b;
HttpWebRequest myReq =
(HttpWebRequest)WebRequest.Create(url);
WebResponse myResp = myReq.GetResponse();

Stream stream = myResp.GetResponseStream();
//int i;
using (BinaryReader br = new BinaryReader(stream))
{
//i = (int)(stream.Length);
b = br.ReadBytes(500000);
br.Close();
}
myResp.Close();
return b;
}

Here we'll write the Byte array data with the help of
BinaryWriter on the given destinaion path.

static public void WriteBytesToFile(string fileName, byte[] content)
{
FileStream fs = new FileStream(fileName, FileMode.Create);
BinaryWriter w = new BinaryWriter(fs);
try
{
w.Write(content);
}
finally
{
fs.Close();
w.Close();
}
}

Import Data to CSV File FROM DataTable

2009-06-22T17:33:00.002+05:30

In th First step we will Clear the Response Object and then we'll attach a csv file with the Response Object. that will be popup once we'll write the Data into it and End the Response Object.

After Attaching the file we'll write the column Name into the CSV File. and then iterate for all the Records to write them into the CSV file.

"" is used to protect the Data if the data contain ',' (Comma) .

Sample Code:

DataTable dtProducts=GetProductsFromDB();
string attachment = "attachment; filename=products.csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "application/octet-stream";

//Write Column Names
string str = "ProductNo,Product,SKU,ProductType,Price";
HttpContext.Current.Response.Write(str);
HttpContext.Current.Response.Write(Environment.NewLine);
for(int i =0; i
{
string strRowData="";
for(int jColumns=0; jColumns
{
if(strRowData=="")
strRowData='"'+dtProducts.Rows[i][jColumns].ToString()+'"';
else
{
strRowData=","+'"'+dtProducts.Rows[i][jColumns].ToString()+'"';
}
}
HttpContext.Current.Response.Write(strRowData);
HttpContext.Current.Response.Write(Environment.NewLine);
strRowData="";
}
HttpContext.Current.Response.End();

Scaling height width of the object.

2009-05-23T14:41:00.005+05:30

In This Sample you need maximum width of the object 1024 and maximum height 750; You need to pass the width and height of object (dblWidth AND dblHeight) and you will get the scaled height and width of the object.

Sample Code:
Double dblWRatio =0

//Actual Width of the Object
Double dblWidth =1280;
//Actual Height of the Object
Double dblHeight =960

//Maximum width of the Object
Double PalyerWidth =1024;
//Maximum height of the Object
Double PlayerHeight =750;

dblWRatio = ((PalyerWidth - dblWidth) * 100) / dblWidth;
dblWidth += dblWidth * (dblWRatio / 100);
dblHeight += dblHeight * (dblWRatio / 100);

if (dblHeight > PlayerHeight)
{
dblHRatio = ((PlayerHeight - dblHeight) * 100) / dblHeight;
dblWidth += dblWidth * (dblHRatio / 100);
dblHeight += dblHeight * (dblHRatio / 100);
}

string v/s StringBuilder

2009-05-23T14:31:00.006+05:30

System.Text.StringBuilder strTest=new System.Text.StringBuilder();
Strings are immutable. immutable means every time we alter the string a new object is created. hence lower the performance. while stringBuilder are mutable. so it increase the performance where we need to perform altered, insert and remove operations. But it is not recommended to use StringBuilder always. for small string where you need to perform less operation then use string
and in case of large string and more operation use StringBuilder.

Read the CSV Data and Save the Data in DataSet.

2009-05-23T14:22:00.007+05:30

In this first we will read the data from csv file using the select query and load the data into a dataset.

Query To read data from CSV file: 
SELECT * FROM [test.csv]

OLEDB Connectionstring:
 @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Lakhan\Projects\Testweb\Test\;Extended
Properties=""text;HDR=Yes;FMT=Delimited"""

if you want to consider first row as column then mention HDR=Yes otherwise no.
using System.Data.OleDb;
for(Int32 i=0; i
   {
       Response.Write(dataSetFromCSV.Tables[0].Columns[i].
       ColumnName + "");
   }
the above code is used to print all the column names

Sample Code:
using System.Data.OleDb;
private void ReadCSVFile()
{
 string cnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
 Source=E:\Lakhan\Projects\Testweb\Test\;Extended
 Properties=""text;HDR=Yes;FMT=Delimited""";
 OleDbConnection ExcelConnection =
 new OleDbConnection(cnStr);
 OleDbCommand ExcelCommand = new OleDbCommand
 (@"SELECT * FROM [test.csv]",
 ExcelConnection);
 OleDbDataAdapter ExcelAdapter =
 new OleDbDataAdapter(ExcelCommand);
 ExcelConnection.Open();
      
 DataSet dataSetFromCSV = new DataSet();
 ExcelAdapter.Fill(dataSetFromCSV);
 ExcelConnection.Close();
 for(Int32 i=0; i
 {
     Response.Write(dataSetFromCSV.Tables[0].
     Columns[i].ColumnName + "");
 }
}

CSV File's Content:
Customer Number,Last Name,First Name,Address,City,Province,
Postal Code,Balance10001,Smith,Dave,123 Parkside Ave.,London,
ON,N6J 4G6,125.3510002,Pearson,Anne,44 Northside Road,Toronto,
ON,N0M 5L8,38.1210003,Carson,Ronald,12 Talbot Road,London,
ON,N6U 3G8,1024.5610006,Davis,Albert,19 Southam Road,Ajax,
ON,N7J 5H7,-8.5510007,Anderson,Theresa,118 Sarnia Road,
London,ON,N6G 5C6,1181.1210009,Jones,Jason,1008,
Carver Place,Ottawa,ON,N8K 8H4,0.00

XSLT (Extensible StyleSheet Language Transformations)

2009-05-15T17:48:00.008+05:30

XSL stands for Extensible StyleSheet Language, and is a style sheet language for XML documents. XSLT stands for XSL Transformations. XSL = Style Sheets for XML.XSL consists of three parts:

XSLT - a language for transforming XML documents
XPath - a language for navigating in XML documents
XSL-FO - a language for formatting XML documents

XSLT is a language for transforming XML documents into XHTML documents or to other XML documents. XPath is a language for navigating in XML documents.

Example:
Implement Style on xml data:
*********************** Sample: Sample1.xml ************************
<?xml version="1.0" encoding="ISO-8859-1" ?>
<?xml-stylesheet type="text/xsl" href="Sample1.xslt"?>
<SqlData>
<Table>
<UserName>lakhangarg@gmail.com</UserName>
<FirstName>Lakhan Pal Garg</FirstName>
<Blog>http://www.lakhangarg.blogspot.com</Blog>
</Table>
</SqlData>

********************* XSLT Sample: Sample1.xslt ************************
<?xml version="1.0" encoding="ISO-8859-1" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<body>
<b>Author: </b><xsl:value-of select="SqlData/Table/FirstName" /><br/>
<b>Email ID:</b><xsl:value-of select="SqlData/Table/UserName" /><br/>
<b>Blog:</b><xsl:value-of select="SqlData/Table/Blog" />
</body>
</html>
</xsl:template>
</xsl:stylesheet>
******************************************************************************

Code to implement stylesheet on xml document:
XmlDocument docXML = new XmlDocument();
XslTransform docXSL = new XslTransform();
docXML.Load(Server.MapPath("sample1.xml"));
docXSL.Load(Server.MapPath("sample1.xslt"));
Xml1.Document = docXML;
Xml1.Transform = docXSL;
******************************************************************************

Output:

Author: Lakhan Pal Garg
Email ID:lakhangarg@gmail.com
Blog:http://www.lakhangarg.blogspot.com

Language Integrated Query (LINQ)

2009-05-15T17:36:00.007+05:30

LINQ Stands for Language-Integrated Query. LINQ has a great power of querying on any source of data, data source could be the collections of objects, database or XML files. We can easily retrieve data from any object that implements the IEnumerable interface. Microsoft basically divides LINQ into three areas and that are give below:

LINQ to Object {Queries performed against the in-memory data}
LINQ to ADO.Net
- LINQ to SQL (formerly DLinq) {Queries performed against the relation database only Microsoft SQL Server Supported}
- LINQ to DataSet {Supports queries by using ADO.NET data sets and data tables}
- LINQ to Entities {Microsoft ORM solution}
LINQ to XML (formerly XLinq) { Queries performed against the XML source}

Example:
int[] nums={0,1,2,3,4,5};
var res = from a in nums
where a < 3
orderby a
select a;
foreach(int i in res)
Console.WriteLine(i);
Output:
0
1
2

ref v/s out Parametes in C#

2009-05-15T17:26:00.003+05:30

out and ref looks quite similar in nature.Both parameters are used to return back some value to the caller of the function. But still there is a important difference between them. these two types are used for specific purpose.
When we use the out parameter, The program calling the function need not assign a value to the out parameter before making the call to the function. The value of the out parameter has to be set by the function before returning the value.
For a ref type parameter, the value to the parameter has to be assigned before calling the function. If we do not assign the value before calling the function we will get a compiler error.
Another important thing to note here is that in case of ref parameter, the value passed by the caller function can be very well used by the called function. The called function does not have the compulsion to assign the value to a ref type parameter. But in case of the out parameter, the called function has to assign a value.

Cross Tab Query-SQL Server

2009-05-13T16:27:00.008+05:30

Query is used to convert column into rows using the cross tab query.

DECLARE @select varchar(8000),@sumfunc varchar(100),@pivot varchar(100)
DECLARE @table varchar(100)
SET @select='SELECT CustID,SalesID FROM testTable GROUP BY CustID,SalesID'
SET @sumfunc='SUM(adsID)'
SET @pivot='adsID'
SET @table='testTable'

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot1 INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
print @sql
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot1'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot1) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot1) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON

Upload file on other system in the network

2009-05-01T15:35:00.006+05:30

This Code snippet will help us to upload file on any system other than application server.

Suppose we have separate file and application server then this code will help us to upload image on file server from application server.

Following Namespaces needs to add to upload file:
using System.Security.Principal;
using System.Runtime.InteropServices;

Summary description for FileHandler
public class FileHandler
{
public const int LOGON32_LOGON_INTERACTIVE = 2;
public const int LOGON32_PROVIDER_DEFAULT = 0;

WindowsImpersonationContext impersonationContext;

//Programatically Add Service to Get Token
[DllImport("advapi32.dll")]
public static extern int LogonUserA(String lpszUserName,
String lpszDomain,
String lpszPassword,
int dwLogonType,
int dwLogonProvider,
ref IntPtr phToken);
[DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]

//Programatically Add Service to Check for Duplicate Token
public static extern int DuplicateToken(IntPtr hToken,
int impersonationLevel,
ref IntPtr hNewToken);

//RevertToSelf terminates the impersonation of a client application.
[DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
public static extern bool RevertToSelf();

//Programatically Add Service to Terminate Process
[DllImport("kernel32.dll", CharSet = CharSet.Auto)]
public static extern bool CloseHandle(IntPtr handle);

This method is used to get the token privilege for a valid user
public bool impersonateValidUser()
{
WindowsIdentity tempWindowsIdentity;
IntPtr token = IntPtr.Zero;
IntPtr tokenDuplicate = IntPtr.Zero;

if (RevertToSelf())
{

if (LogonUserA(ConfigurationManager.AppSettings["DomainUser"].ToString(), ConfigurationManager.AppSettings["Domain"].ToString(), ConfigurationManager.AppSettings["DomainPassword"].ToString(), LOGON32_LOGON_INTERACTIVE,
LOGON32_PROVIDER_DEFAULT, ref token) != 0)
{
if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)
{
tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);
impersonationContext = tempWindowsIdentity.Impersonate();
if (impersonationContext != null)
{
CloseHandle(token);
CloseHandle(tokenDuplicate);
return true;
}
}
}
}
if (token != IntPtr.Zero)
CloseHandle(token);
if (tokenDuplicate != IntPtr.Zero)
CloseHandle(tokenDuplicate);
return false;
}

Method to Upload file on network path. parameter of this functions are:
fileuploadcontrol as reference type, Destination Path as string.
impersonateValidUser() method is used to get the token for a valid user.
once we got the token we can save image on other system.

public Boolean UploadFile(ref FileUpload flUpload,string NetworkPath)
{
if (impersonateValidUser())
{
flUpload.SaveAs(NetworkPath + flUpload.FileName);
return true;
}
else
{
return false;
}
}
}

Javascript- Pop Up window Using the window.open method

2009-04-30T21:59:00.005+05:30

The syntax of the window.open method is given below:

window.open (URL, windowName, Features)

URL:
The URL of the page to open in the new window. This argument could be blank.

Window Name:
A name to be given to the new window. The name can be used to
refer this window again.

Features:
A string that determines the various window features to be included
in the pop up window (like status bar, address bar etc)

Code to opens a new browser window with standard features.
window.open ("http://www.google.com","mywindow");

Following are the features of window.open method:
status: status bar at the bottom of the window.(e.g: status=1 or 0)
toolbar: The standard browser toolbar, with buttons such as
Back and Forward. (e.g: toolbar=1 or 0)
location: The Location entry field where you enter the URL.(e.g: location=1 or 0)
menubar: The menu bar of the window (e.g: menubar=1 or 0)
resizable: Allow/Disallow the user to resize the window.(e.g: resizable=1 or 0)
scrollbars: Enable the scrollbars if the document is bigger than the
window(e.g: scrollbars=1 or 0)
height: Specifies the height of the window in pixels. (e.g: height='200')
width: Specifies the width of the window in pixels. (e.g: width='200')

Example:
The following code opens a window with menu bar and toolbar.
The window is resizable and is having 350 pixels width and 250 pixels height.
window.open ("http://www.google.com ",
"mywindow","menubar=1,resizable=1,toolbar=1,
width=350,height=250");

SQL Server - Reading XML Data

2009-04-30T15:11:00.011+05:30

DECLARE @XmlHandle int
DECLARE @XMLDATA nTEXT

EXEC sp_xml_preparedocument @XmlHandle output,@XMLDATA

SELECT * FROM OPENXML (@XmlHandle, '{XPath}',{flag})
WITH ([col1] int '@xmlAttribute',[col2] VARCHAR(100) '.XMlLElement')

EXEC sp_xml_removedocument @XmlHandle

sp_xml_preparedocument - Reads the XML text provided as input (@XMLDATA) ,
the xml text and returns a handle that can be used to access the newly created internal representation of the XML document. The output of this procedure is XML handle that is
used for OPENXML command.

sp_xml_removedocument - A parsed document is stored in the internal cache of
SQL Server, so sp_xml_removedocument is used to remove the xml document.

OPENXML- OPENXML provides a rowset view over an XML document. Because
OPENXML is a rowset provider, OPENXML can be used in Transact-SQL
statements in which rowset providers such as a table, view, or the
OPENROWSET function can appear.

The last Attribute is for flag means weather it is attribute centric, element
centric etc. for more details about this
Visit: http://msdn.microsoft.com/en-us/library/ms186918.aspx
***********************************************************************
DECLARE @xmlDoc VARCHAR(8000)
DECLARE @xmlHandle INT
BEGIN
SET @xmlDoc ='{Products}
{Product ID="1"}
{Name}Sugar{/Name}
{Price}30 rs/Kg{/Price}
{/Product}
{Product ID="2"}
{Name}Milk{/Name}
{Price}22 rs/Kg{/Price}
{/Product}
{/Products}'

EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xmlDoc
SELECT * FROM OPENXML (@xmlHandle, '//Product', 2) WITH
(
ID INT '@ID',
[Name] VARCHAR(20) 'Name',
Price VARCHAR(10) 'Price'
)
EXEC sp_xml_removedocument @xmlHandle
END

Output:
ID Name Price
1 Sugar 30 rs/Kg
2 Milk 22 rs/

Save Data into Sql Server from Excel sheet

2009-04-29T15:12:00.002+05:30

Data into SQL Server from Excel sheet (C#). First we will get the data from excel sheet using the select query and OLEDB as provider.
Connection string for OLEDB is:
string file = "D:\\Amt.xls";
string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
file + ";Extended Properties=Excel 8.0;";

Query to fetch data from a excel sheet:
string query = "Select Name,Salary from [Sheet1$]";

This Code will help to fetch the data from excel sheet and save the same into dataset.
DataSet dataSet = new DataSet();
using (OleDbConnection Connection = new OleDbConnection(constr))
{
using (OleDbDataAdapter DataAdapter =
new OleDbDataAdapter(query, Connection))
{
DataAdapter.Fill(dataSet, "Amt1");
DataAdapter.AcceptChangesDuringFill = false;
}
}

Following lines of code will help us to save the data into SQL server DB that we get from excel sheet.
constr = "server=B0001;database=master;uid=sa;pwd=sa";
query = "Select Name,Salary from Amt";
using (SqlConnection Connection = new SqlConnection(constr))
{
using (SqlCommand Command = new SqlCommand(query, Connection))
{
using (SqlDataAdapter DataAdapter = new SqlDataAdapter(Command))
{
using (SqlCommandBuilder CommandBuilder =
new SqlCommandBuilder(DataAdapter))
{
Connection.Open();
SqlTransaction Transaction = Connection.BeginTransaction();
Command.Transaction = Transaction;
DataAdapter.InsertCommand = CommandBuilder.GetInsertCommand();
dataSet.AcceptChanges();
int check = DataAdapter.Update(dataSet, "Amt1");
Transaction.Commit();
}
}

Export Data from Dataset or Datatable to Excel File

2009-04-29T12:56:00.002+05:30

Save Data from DataSet or DataTable to Excel File (VB.NET)

Namespace that needs to import for excel:
Imports Microsoft.Office.Interop.Excel

Add instance of excel application
Excel = New Excel.Application
Excel.SheetsInNewWorkbook=3

Add Workbook to the Worksheet (We can Add More Workbooks Too in Similar Way)
Excel.Workbooks.Add()

Create new worksheet
createWorksheet("Workbook Name")

Show Dialog Box
Dim dlgsave As New SaveFileDialog
dlgsave.Filter = "Excel Spreadsheets (*.xls)|*.xls"
If dlgsave.ShowDialog() = DialogResult.OK Then

Insert Column Name into the excel sheet
For iDColumn = 0 To dtShareholders.Columns.Count - 2
Excel.Cells(iRRow, iRColumn).Value = dtShareholders.&_
Columns(iDColumn + 1).ColumnName.ToString
Excel.Cells(iRRow, iRColumn).Borders.Weight = 2
Excel.Cells(iRRow, iRColumn).Interior.ColorIndex = 49
Excel.Cells(iRRow, iRColumn).Font.ColorIndex = 2
Excel.Cells(iRRow, iRColumn).Font.Bold = True
Excel.Cells(iRRow, iRColumn).FormulaHidden = True
Excel.Cells(iRRow, iRColumn).RowHeight = 25
Excel.Cells(iRRow, iRColumn).VerticalAlignment = xlCenter
iRColumn = iRColumn + 1
Next

This loop will print all rows one by one into the excel sheet
iRRow = iRRow + 1
For iDRow = 0 To dtShareholders.Rows.Count - 2
iRColumn = 1
Excel.Cells(iRRow, iRColumn).Value = dtShareholders.&_
Rows(iDRow).ItemArray(0).ToString
Excel.Cells(iRRow, iRColumn).EntireColumn.AutoFit()
Excel.Cells(iRRow, iRColumn).Borders.Weight = 2
Excel.Cells(iRRow, iRColumn).Font.Bold = True
Excel.Cells(iRRow, iRColumn).RowHeight = 15
Excel.Cells(iRRow, iRColumn).HorizontalAlignment = xlLeft
iRRow = iRRow + 1
Next

Set the Selected Sheet in the Workbook
Excel.Worksheets(1).select()
Dim strFileName As String
strFileName = IO.Path.GetDirectoryName&_
(System.Reflection.Assembly.GetExecutingAssembly&_
.Location) & "\SheetName"
If IO.File.Exists(strFileName & ".xls") Then
IO.File.Delete(strFileName & ".xls")
End If

Save the active workbook into the given location and delete if there is any file with the same name on the same location already exist.
Excel.ActiveWorkbook().SaveAs(strFileName & ".xls")
Dim destination As String = dlgsave.FileName
''Check if the file Saved or not
If destination <> "" Then
If IO.File.Exists(destination) Then
IO.File.Delete(destination)
End If

System.IO.File.Copy(strFileName & ".xls", destination
MessageBox.Show("Excel Sheet saved successfully",&_
mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Report Is Not Saved, Operation Canceled By User",&_
mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
dlgsave.Dispose()

Javascript - Event Handling

2009-04-29T12:25:00.002+05:30

This code will help us to write any code before and after any event. BeforeAjaxRequest method will be executed before any event and pageLoadedHandler method will be executed at the end of any event.

Sample Syntax:
var pageMgr = Sys.WebForms.PageRequestManager.getInstance();
pageMgr.add_beginRequest(BeforeAjaxRequest);
pageMgr.add_pageLoaded(pageLoadedHandler);
var postbackElement;

function BeforeAjaxRequest(sender, args)
{
postbackElement=args.get_postBackElement();
if (postbackElement.id.indexOf("{ID of the Control}") != "-1")
{/* Write your Code Here */}
}

function pageLoadedHandler(sender, args)
{
if (typeof(postbackElement) == "undefined") { return;}
else if (postbackElement.id.indexOf("{ID of the Control}") != "-1")
{/* Write your Code Here */}
}

For Example: You are having a asp link button with id lnkTest now according to this your above code should be:
function BeforeAjaxRequest(sender, args)
{
postbackElement=args.get_postBackElement();
if (postbackElement.id.indexOf("lnkTest") != "-1")
{alert("Before Ajax Request");}
}

function pageLoadedHandler(sender, args)
{
if (typeof(postbackElement) == "undefined") { return;}
else if (postbackElement.id.indexOf("lnkTest") != "-1")
{alert("After Ajax the Request");}
}

Clock in Javascript

2009-04-25T14:29:00.003+05:30

To Show Clock in Javascript (Time Format: SAT 4-25-2009 2:43:22 PM)

Function to create a array of given length
function MakeArray(size)
{
this.length = size;
for(var i = 1; i <= size; i++)
{
this[i] = "";
}
return this;
}

Function to display time. in this at the end we write setTimeout("showtime()",1000).
this code will call the showtime function after every second.
function showtime ()
{
var now = new Date();
var year = now.getYear();
var month = now.getMonth() + 1;
var date = now.getDate();
var hours = now.getHours();
var minutes = now.getMinutes();
var seconds = now.getSeconds();
var day = now.getDay();
Day = new MakeArray(7);
Day[0]="SUN";
Day[1]="MON";
Day[2]="TUE";
Day[3]="WED";
Day[4]="THU";
Day[5]="FRI";
Day[6]="SAT";
var timeValue = "";
timeValue += (Day[day]) + " ";
timeValue += ((month > 10) ? " 0" : " ") + month + "-";
timeValue += date + "-" + year + " ";
timeValue += ((hours <= 12) ? hours : hours - 12);
timeValue += ((minutes <>
timeValue += ((seconds <>
timeValue += (hours <>
document.getElementById('lblClock').innerHTML = timeValue;
setTimeout("showtime()",1000);
}

Paste the above code in the script tag in Head section and add the following line after the closing of HTML tag:
showtime();

Use a span to show the clock - id of span used in this code is lblClock

Export Data From Excel Sheet into Dataset- C#

2009-04-25T12:29:00.002+05:30

Procedure To Export Excel Sheet Data into Dataset With the help of OLEDB
Connection String For Excel & Query to Fetch The Records:

string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";

Select Column1,Column2,Column3,Column4 from [Sheet1$]
Sheet1 is the name of the sheet in the Excel file from where you want to get the records.

Complete Code to Get the Excel Sheet Data
string file = "Physical path of the File"; (Like: D:\\Amt.xls)
string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";

string query = "Select Column1,Column2,Column3,Column4 from [Sheet1$]";
DataSet dataSet = new DataSet();
using (OleDbConnection Connection = new OleDbConnection(constr))
{
using (OleDbDataAdapter DataAdapter = new OleDbDataAdapter(query, Connection))
{
DataAdapter.Fill(dataSet, "DataSetName");
DataAdapter.AcceptChangesDuringFill = false;

Function To Send Email - ASP.NET (C#)

2009-04-25T12:00:00.003+05:30

User needs to pass few parameter to this method to send the mail.
body - Content of email that you want to send.
toadd - Address of the user to whom you want to send the Email.
ccAdd - Address of the user to whom you want to send Email in CC.
bccadd -Address of the user to whom you want to send Email in BCC.
fromaddm - Address of the user that want to send the Email.
subject - Subject of the Email.
attachment - Attachment any if you want to send to user otherwise set the value as blank if you don't want to send any email.

Address of SMTP Server - Replace this text with the address of your SMTP Server.
Code To Send Email:
public static bool SendMail(string body, string toadd, string fromadd, string subject, string attachment, string ccAdd, string bccadd)
{
string mailServerName = "Address of SMTP Server ";
try
{
//MailMessage represents the e-mail being sent
using (MailMessage message = new MailMessage(fromadd, toadd, subject, body))
{
if (attachment != "")
{
message.Attachments.Add(new Attachment(attachment));
}
if (bccadd != "")
{
message.Bcc.Add(bccadd);
}
message.IsBodyHtml = true;
message.Priority = MailPriority.Normal;
SmtpClient mailClient = new SmtpClient();
mailClient.Host = mailServerName;
mailClient.UseDefaultCredentials = true;
mailClient.Send(message);

}
return true;
}
catch (SmtpException ex)
{

return false;
}
catch (Exception ex)
{

return false;
}
}

ASP.NET - Form Authentication Tutorial

2009-04-24T14:08:00.005+05:30

aspx.cs File's Content:
On Button Click for Login
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "server={ServerName};uid={UserID};pwd={Password};database={DatabaseName}";
con.Open();
SqlCommand cm = new SqlCommand("select roles from users where username = '" + TextBox1.Text + "' and password = '" + TextBox2.Text + "'", con);
SqlDataReader dr = cm.ExecuteReader();
if (dr.Read())
{
FormsAuthenticationTicket tk = new FormsAuthenticationTicket(1, TextBox1.Text, DateTime.Now, DateTime.Now.AddMinutes (1),true, dr.GetString(0),FormsAuthentication.FormsCookiePath);
string hash = FormsAuthentication.Encrypt(tk);
HttpCookie Logincookie = new HttpCookie(FormsAuthentication.FormsCookieName,hash);
Response.Cookies.Add(Logincookie);
if (tk.IsPersistent)
{
HttpCookie ck = new HttpCookie( FormsAuthentication.FormsCookieName, hash);
ck.Expires = tk.Expiration;
Response.Cookies.Add(ck);
}
string returnUrl = Request.QueryString["ReturnUrl"];
if (returnUrl == null) returnUrl = "Main.aspx";
Response.Redirect(returnUrl);
}
else
{
Response.Write("The Username / Password not valid !");
}
con.Close();
dr.Close();
cm.Dispose();
}

Add These lines of code in Global.asax:
protected void Application_AuthenticateRequest(Object sender, EventArgs e)
{
if (HttpContext.Current.User != null)
{
if (HttpContext.Current.User.Identity.IsAuthenticated )
{
if (HttpContext.Current.User.Identity is FormsIdentity)
{
FormsIdentity id = (FormsIdentity)HttpContext.Current.User.Identity;
FormsAuthenticationTicket ticket = id.Ticket;
HttpCookie ck = new HttpCookie("ticket");
string userData = ticket.UserData;
string[] roles = userData.Split(',');
HttpContext.Current.User = new GenericPrincipal(id, roles);
}
}
}
}

Database:
CREATE TABLE users
(
username nvarchar(64) CONSTRAINT users_PK PRIMARY KEY,
password nvarchar(12,
roles nvarchar(64)
)
CREATE INDEX credentials ON users
(
username,
password
)
insert into users values('client','client','c')
insert into users values('admin','admin','a')
select * from users

Add These Lines in Web Config
Before </system.web>
<authentication mode="Forms">
<forms name="cookie" loginUrl="Default.aspx"> </forms>
</authentication>
<authorization>
<allow users="*">
</allow>

After </system.web> & before </configuration>
<location path="Admin"<>
<system.web>
<authorization>

<allow roles="a">
<deny users="*">
</deny>
</allow>
</authorization>
<location path="Client">
<system.web>
<authorization>

<allow roles="c,a">
<deny users="*">
</deny>
</allow>
</authorization>

Microsoft SQL Server - Create Web Service

2009-04-24T14:05:00.003+05:30

first line is to Create End Point for web service.
State can be of three types: STARTED Means Listening and Responding, DISABLED Means Neither Listening nor Responding And STOPPED Means Listening But Not Responding

specify the Transport Protocol to used
{HTTP | TCP}

Authentication mode is of three types INTEGRATED Is More Secure, While Basic is Least Secure and Digest is not Secure Authentication type

CLEAR (HTTP - port 80 by default) SSL (HTTPS - port 443 by default)
PORTS= {CLEAR | SSL}

BATCHES= {ENABLED | DISABLED}
if ENABLED, multiple SOAP request/response message pairs can be identified as part of a single SOAP session.

CREATE ENDPOINT XMLHTTPEndPoint
STATE= {STARTED | STOPPED | DISABLED}
AS
{HTTP | TCP}
(
PATH = (e.g.: '/Webservices',)
AUTHENTICATION= (BASIC | DIGEST | INTEGRATED),
PORTS= {CLEAR | SSL},
SITE=
)
FOR {SOAP | TSQL}
(
WEBMETHOD
(NAME=),
BATCHES= {ENABLED | DISABLED},
WSDL= {NONE | DEFAULT | 'sp_name'},DATABASE = <Database Name>>
NAMESPACE= http://<Server Name>/( Name where you want to create Web service)
)

WSDL Specify how the Document Generation Will Occur. If none no Response is generated or returned for query.

Microsoft SQL Server - Important Queries

2009-04-24T12:21:00.004+05:30

Query to Select n Rendom Records From a Table in the Database:
SELECT TOP 3 * FROM Tbl_WZQ_REGISTRATION ORDER By NEWID()

Null Parameter check in the query- we can add this check in the where condition
(@VariableData is NULL OR [ColumnName] = @VariableData

Get File Extension from given File Name
DECLARE @FileName VARCHAR(200)
SET @FileName='MyFile.ppt'
SELECT SUBSTRING(@FileName,(LEN(@FileName) - CHARINDEX('.', REVERSE(@FileName), 1 + 1)+2) ,LEN(@FileName)) AS Ext

OUTPUT: ppt

To Get The Column Name,DataType, And
Length of columns in a Table
select column_name, data_type, character_maximum_length from information_schema.columns
where table_name ={Table Name}

Query to Get List of Views
select * from information_schema.views

Query to Get List of Tables
select * from information_schema.Tables

Query to Get List of System Tables
select * from Sys.Objects where Type='s'

Query to Get List of User Tables
select * from Sys.Objects where Type='u'

Query to Get List of Store Procedures
select * from Sys.Objects where Type='p'

Query to Get List of Scalar Functions
select * from Sys.Objects where Type='fn'

Query to Get List of Table Valued Functions
select * from Sys.Objects where Type='tf'

Query to Get List of Primary Keys
select * from Sys.Objects where Type='PK'

Query to Get List of Unique Keys
select * from Sys.Objects where Type='uq'

Query to Get List of Forgien Keys
select * from Sys.Objects where Type='f'

Query to Get List of views
select * from Sys.Objects where Type='v'

Query to Get List of Triggers
select * from Sys.Objects where Type='tr'

Query to Get List of Internal Tables
select * from Sys.Objects where Type='it'

About Me

2008-09-18T11:27:00.004+05:30

About Our Guru ji:

Baba Gendhi Ram ji’s Dera is situated at a distance of 3 km from Dharamkot on Dharamkot- Jalandhar Road. Dera is running by a trust under the guidance of President Pandit Joginder Daas ji.

“Guru ka Langar” is arranged by Trust with the help of the people of the Dharamkot in “Sawan” (For One Month) and near “Holi” (for 10 or 12 days).

Baba ji’s Barsi is celebrated on 11th of may. people from different parts of the punjab and other states come on this occusion to get the blessing of baba ji.

About Me:

My Self Lakhan Pal Garg. I am a B.Tech (I.T ) graduate. i have completed my graduation in July 2005.

Profession : Software Developer (Mainly Working on Website Development in Microsoft Technologies like ASP.NET, C#, VB.NET, MS-SOL Server)

Somthing About My Carrier:

I had started my professional carrier with Hanu Software Solutions, Gurgaon in June 2006, after that i joined Safaltek Software Pvt. Ltd, Chandigarh in Nov 2006. i worked with Safaltek for near about 9 Months and worked on various application (both web and desktop application). i also worked on Microsoft Share Point Portal server in Safaltek, Then i moved to Sebiz Infotech, Mohali in Aug 2007 and worked there till Aug 2008. Currently i am working with Authorgen Pvt. Ltd., Chandigarh as Senior Software Engineer. i am working on the one of their product named Wiziq.com (a online virtual class based educational web site).

Links of some sites on which i have worked:

http://www.wiziq.com/
http://www.amuktrade.com/
http://www.turningpages.com/
http://www.kaatskillmtnclub.com/

About My Family:

Father Name: Sh. Varinder Kumar Garg

Mother Name: Smt. Kamal Rani Garg

Brother: Naveen Kumar Garg

Wife: Aastha Garg