constant vs readonly in c#

constant vs readonly in c#

There is just a minute difference between the constant and the readonly, both of them looks the same but small difference is there in assigning the value to both of them. below URL shows a neat differences between the two

http://dotnetbites.com/c-constant-vs-readonly

Get filename from an URL using Sql Server Function

I have written an User defined function, this may help you in getting the file name from an URL you pass as input.

[code]
CREATE FUNCTION dbo.GetFileNameFromURL
(
@Url VARCHAR(2000)
)
/* -- Comments --
Created By: Alwyn Duraisingh.M
Created On: 26th September 2010
Purpose: To get the filename from an URL
*/
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @FileName VARCHAR(255)
DECLARE @TempUrl VARCHAR(2000)
SET @TempUrl = REPLACE(@Url, '\', '/')
SET @TempUrl = REVERSE(@TempUrl)
SELECT @FileName = REVERSE(SUBSTRING(@TempUrl,1, CHARINDEX('/', @TempUrl) - 1))
RETURN @FileName
END
[/code]

you can call this function like;

SELECT dbo.GetFileNameFromURL('http://Folders/Adsingh/MyTextFile.txt')

Result:

MyTextFile.txt

Sql Server date Format 1st January 2010

There are several dateformat available in Sql Server, but some usable formats
are not available.

This function is designed to get the Date like 1st January 2010

Input Parameters
@GivenDate DATETIME

For this you can pass the value you get from the system function GETDATE().

CREATE FUNCTION dbo.DateFormat_8th_february_2010
(
@GivenDate DATETIME
)RETURNS VARCHAR(50)
/*
Created by Alwyn Duraisingh.M on 9th July 2010
Purpose: To get the dateformat like 9 th July 2010
*/
AS
BEGIN
DECLARE @ConvertedDate VARCHAR(50)
SELECT @ConvertedDate = CASE LEFT(Dates, 2)
WHEN '11' THEN REPLACE(Dates, '11 st', '11 th')
WHEN '12' THEN REPLACE(Dates, '12 nd', '12 th')
WHEN '13' THEN REPLACE(Dates, '13 rd', '13 th')
ELSE Dates END
FROM (
SELECT CONVERT(VARCHAR(2), DAY(@GivenDate)) + ' ' +
CASE RIGHT(CONVERT(VARCHAR(2), DAY(@GivenDate)),1)
WHEN '1' THEN 'st'
WHEN '2' THEN 'nd'
WHEN '3' THEN 'rd'
ELSE 'th' END + ' ' + DATENAME(MM, @GivenDate) + ' ' + CONVERT(VARCHAR(4), YEAR(@GivenDate)) AS Dates
) a
RETURN @ConvertedDate
END

You can execute the function by
SELECT dbo.DateFormat_8th_february_2010(GETDATE())
Result:
9 th July 2010
The result is because i executed the query on 9th July 2010it will vary
according to the current date.

Arithmetic overflow error converting expression to data type datetime

This error occurs because of the Dateformat mismatch (Culture Problem)

Date : 26/02/2010

This date is in the format DMY(Day-Month-Year), But US servers will support
only the format of YMD (Year-Month-Day). So by this time you will get the
afore- mentioned error.

Let me explain with a SAMPLE;

DECLARE @Date NVARCHAR(50)SET @Date = '26-02-2010 00:00:000'SELECT CONVERT(DATETIME, @Date)

If you execute the above 3 Sql Statements, you will get an error like

"Arithmetic overflow error converting expression to data type datetime"

suppose, if you store the @Date in a VARCHAR Datatype;

DECLARE @Date VARCHAR(50)SET @Date = '26-02-2010 00:00:000'SELECT CONVERT(DATETIME, @Date)

you will get an error like this

"The conversion of a char data type resulted in an out-of-range datetime value."

SOLUTION:
---------

To overcome these type of errors, use SET DATEFORMAT;

SET DATEFORMAT DMYDECLARE @Date NVARCHAR(50)SET @Date = '26-02-2010 00:00:000'SELECT CONVERT(DATETIME, @Date)

Now the error will be resolved, instead you would get the desired result as
output :-)

Sql Server Limitations

The following table specifies the maximum sizes and numbers of various objects defined in SQL Server databases or referenced in Transact-SQL statements.

Sql Server Objects	Limits(32-Bit)	Limits (64-Bit)
Bytes per foreign key	900	900
Bytes per primary key	900	900
Bytes per foreign key	900	900
Bytes per primary key	900	900
Bytes per row	8060	8060
Bytes per varchar(max), varbinary(max), xml, text, or image column	2^31-1	2^31-1
Characters per ntext or nvarchar(max) column	2^30-1	2^30-1
Clustered indexes per table	1	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes	Limited only by number of bytes
Columns per index key	16	16
Columns per Foreign key	16	16
Columns per Primary key	16	16
Columns per SELECT statement	4,096	4,096
Columns per INSERT statement	4,096	4,096
Database size	524,272 terabytes	524,272 terabytes
Databases per instance of SQL Server	32,767	32,767
Files per database	32,767	32,767
Foreign key table references per table	253	253
Locks per connection	Maximum locks per server	Maximum locks per server
Nested stored procedure levels	32	32
Nested subqueries	32	32
Nested trigger levels	32	32
Parameters per stored procedure	2,100	2,100
Rows per table	Limited by available storage	Limited by available storage
User connections	32,767	32,767
XML indexes	249	249

Yield Keyword in C#

Some cool and hidden facts of C# is the yield keyword. Among us many of them does not know it's feature.
The yield keyword is used in an iterator block to provide a value to the enumerator object
or to signal the end of the iteration. When used the expression is evaluated and returned
as a value to the enumerator object. Note the expression has to be implicitebly convertible
to yield type of the iterator.

Here is an example

public static IEnumerable GetIntCollectionFromString(string InputString)
{
string[] StringArray = InputString.Split(' ');
int intVal;
foreach (string token in StringArray)
{
if (int.TryParse(token, out intVal))
{
yield return intVal;
}
else
{
yield break;
}
}
}
Here since we are using the yield keyword the function will return the collection of intVal
instead of the value of intVal. The statement will only return when the iteration of the loop is complete.


There are some limitation with the yield keywords.

1.Unsafe blocks are not allowed
2.Parameters to the method, operator, or accessor cannot be ref or out.

Find the LENGTH of a string without using LEN Function in Sql Server

This user-defined function will give you the length of the string.

CREATE FUNCTION dbo.GetLength(@InputString VARCHAR(8000))
RETURNS INT
AS
/* -- Comments --
Created By: Alwyn Duraisingh.M
Created on: April 26 2010 10:10 PM
Purpose: To find the length of the string without using LEN() function
*/
BEGIN
DECLARE @Count INT, @Index INT, @Flag INT
SELECT @InputString = REPLACE(@InputString, ' ','*'),
@Count = 0, @Index = 1, @Flag = 1
--Looping through each and every character and count one by one
WHILE ( @Flag = 1 )
BEGIN
IF ((SELECT SUBSTRING(@InputString,@Index,1)) <> '')
BEGIN
SET @Count = @Count + 1
END
ELSE
BEGIN
SET @Flag = 0
END
SET @Index = @Index + 1
END
RETURN @Count
END



You can call the function like

SELECT dbo.GetLength('God is only one') AS LENGTH

RESULT:
*******
LENGTH
*******
15