Stored Procedure using the most CPU

The following query gives you a high-level view of which currently cached batches or procedures are using the most CPU. The query aggregates the CPU consumed by all statements with the same plan__handle (meaning that they are part of the same batch or procedure). If a given plan_handle has more than one statement, you may have to drill in further to find the specific query that is the largest contributor to the overall CPU usage.
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

Creating Antibot Image With c#

Antibots images are needed for avoiding Trojans to make fake registration with out human interaction.In the method given below generate random strings and generate the bitmap of that.This method Should be added as web handler(ashx) in the asp.net project.


public void ProcessRequest(HttpContext context)
{
if(context.Session["antibotimage"] == null)
{
context.Session["antibotimage"] = generateRandomString(4).ToUpper();
}

GenerateImage(context.Session["antibotimage"].ToString(), 100, 20, "Arial").Save(context.Response.OutputStream, ImageFormat.Jpeg);
}


private Bitmap GenerateImage(string text, int width, int height, string fontFamily)
{
Random random = new Random();

// Create a new 32-bit bitmap image.
Bitmap bitmap = new Bitmap(width, height, PixelFormat.Format32bppArgb);

// Create a graphics object for drawing.
Graphics g = Graphics.FromImage(bitmap);
g.SmoothingMode = SmoothingMode.AntiAlias;
Rectangle rect = new Rectangle(0, 0, width, height);

// Fill in the background.
HatchBrush hatchBrush = new HatchBrush(HatchStyle.Wave, Color.LightGray, Color.White);
g.FillRectangle(hatchBrush, rect);

// Set up the text font.
SizeF size;
float fontSize = rect.Height + 1;
Font font;
StringFormat format = new StringFormat();
format.Alignment = StringAlignment.Center;
format.LineAlignment = StringAlignment.Center;

// Adjust the font size until the text fits within the image.
do
{
fontSize--;
font = new Font(fontFamily, fontSize, FontStyle.Bold);
size = g.MeasureString(text, font, new SizeF(width, height), format);
} while (size.Width > rect.Width);

// Create a path using the text and warp it randomly.
GraphicsPath path = new GraphicsPath();
path.AddString(text, font.FontFamily, (int)font.Style, font.Size, rect, format);
float v = 4F;
PointF[] points =
{
new PointF(random.Next(rect.Width) / v, random.Next(rect.Height) / v),
new PointF(rect.Width - random.Next(rect.Width) / v, random.Next(rect.Height) / v),
new PointF(random.Next(rect.Width) / v, rect.Height - random.Next(rect.Height) / v),
new PointF(rect.Width - random.Next(rect.Width) / v, rect.Height - random.Next(rect.Height) / v)
};
Matrix matrix = new Matrix();
matrix.Translate(0F, 0F);
path.Warp(points, rect, matrix, WarpMode.Perspective, 0F);

// Draw the text.
hatchBrush = new HatchBrush(HatchStyle.DashedUpwardDiagonal, Color.DarkGray, Color.Black);
g.FillPath(hatchBrush, path);

// Add some random noise.
int m = Math.Max(rect.Width, rect.Height);
for (int i = 0; i < (int)(rect.Width * rect.Height / 30F); i++)
{
int x = random.Next(rect.Width);
int y = random.Next(rect.Height);
int w = random.Next(m / 50);
int h = random.Next(m / 50);
g.FillEllipse(hatchBrush, x, y, w, h);
}

// Clean up.
font.Dispose();
hatchBrush.Dispose();
g.Dispose();

return bitmap;
}


private string generateRandomString(int size)
{
StringBuilder builder = new StringBuilder();
Random random = new Random();
char ch;
for (int i = 0; i < size; i++)
{
ch = Convert.ToChar(Convert.ToInt32(Math.Floor(26 * random.NextDouble() + 65)));
builder.Append(ch);
}
return builder.ToString();
}

Programmed by
Renju.R,
Software Engineer,
ISOFT, Chennai,India

Parsing/Looping through Treeview using Interface

private static void parseNode(TreeNode tn)
{

IEnumerator ie = tn.ChildNodes.GetEnumerator();

string parentnode = "";

parentnode = tn.Text;

while (ie.MoveNext())
{
TreeNode ctn = (TreeNode) ie.Current;

if (ctn.ChildNodes.Count == 0)
{
sr.Write(ctn.Text);
}
else
{
sr.Write("<" + ctn.Text + ">");
}
if (ctn.ChildNodes.Count > 0)
{
parseNode(ctn);
}
}

sr.Write("");
sr.WriteLine("");

}

Saving the treeview to XML

public static void exportToXml(TreeView tv, string filename)
{
sr = new StreamWriter(filename, false, System.Text.Encoding.UTF8);
sr.WriteLine("");
if (tv.Nodes.Count > 0)
{
IEnumerator ie = tv.Nodes.GetEnumerator();
ie.Reset();
if (ie.MoveNext())
{
TreeNode tn = (TreeNode)ie.Current;
sr.WriteLine("<" + tn.Text + ">");
parseNode(tn);
}
}

sr.Close();
}

SCOPE_IDENTITY

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

SCOPE_IDENTITY()

Returns numeric value.eg:- RETURN SCOPE_IDENTITY()

Parsing Emails using Regular Expressions

Using Regular Expressions

Using regular expression we can simply parse valid emails from the given contents.this is mainly used to extract actual email addressess from the to,from,cc and bcc fields.

Implementation(c#)

First import using System.Text.RegularExpressions;

the code is as below

private string ParseEmails(string text)
{
const string emailPattern = @"\w+@\w+\.\w+((\.\w+)*)?";

MatchCollection emails = Regex.Matches(text, emailPattern, RegexOptions.IgnoreCase);
StringBuilder emailString = new StringBuilder();
foreach (Match email in emails)
{
if (emailString.Length ==0)
{
emailString.Append(email.Value);
}
else
{
emailString.Append("," + email.Value);
}
}

return emailString.ToString();
}

Word Break for Gridview/DataGrid

Sometime we are facing problems with gridview in inserting word breaks to format the grid.ie if we are inserting a coloumn that is having more length so the grid layout is changed.

"wbr" Tag is used for that.That should be placed in angle bracket("<>").I'm representing it as wbr in the code given below as it is an html tag.

public static string WordBreak(String inString, int length)
    {
        StringBuilder stb = new StringBuilder();
        String ret = "";
        int pos;
        if (length == 0)
            length = 10;
        while ((true))
        {
            if (inString.Length <= length)
            {
                ret += inString;
                break;
            }
            pos = inString.IndexOf(" ");
            if (pos != -1)
            {
                if (pos > length - 1)
                {
                    pos = length;
                    ret += inString.Substring(0, pos + 1) + "wbr";
                }
                else
                {
                    ret += inString.Substring(0, pos + 1);
                }
                inString = inString.Substring(pos + 1);
            }
            else
            {
                ret += inString.Substring(0, length) + "wbr";
                inString = inString.Substring(length);
            }
        }
        if (ret.Length > 50)
        {
            string temp = ret.Substring(0, 50);
            stb.Append(temp);
            stb.Append("....");
            ret = stb.ToString();
        }
        return ret;
    }

Regards
www.renjucool.com

Don’t run production ASP.NET Applications with debug=”true” enabled

One of the things you want to avoid when deploying an ASP.NET application into production is to accidentally (or deliberately) leave the switch on within the application’s web.config file.
Doing so causes a number of non-optimal things to happen including:

1) The compilation of ASP.NET pages takes longer (since some batch optimizations are disabled)

2) Code can execute slower (since some additional debug paths are enabled)

3) Much more memory is used within the application at runtime

4) Scripts and images downloaded from the WebResources.axd handler are not cached

This last point is particularly important, since it means that all client-javascript libraries and static images that are deployed via WebResources.axd will be continually downloaded by clients on each page view request and not cached locally within the browser. This can slow down the user experience quite a bit for things like Atlas, controls like TreeView/Menu/Validators, and any other third-party control or custom code that deploys client resources. Note that the reason why these resources are not cached when debug is set to true is so that developers don’t have to continually flush their browser cache and restart it every-time they make a change to a resource handler (our assumption is that when you have debug=true set you are in active development on your site).

When is set, the WebResource.axd handler will automatically set a long cache policy on resources retrieved via it – so that the resource is only downloaded once to the client and cached there forever (it will also be cached on any intermediate proxy servers). If you have Atlas installed for your application, it will also automatically compress the content from the WebResources.axd handler for you when is set – reducing the size of any client-script javascript library or static resource for you (and not requiring you to write any custom code or configure anything within IIS to get it).

Model View Controller Pattern

Luckily, the ASP.NET team has been listening to developers like me and has started development of a new Web application framework that sits side-by-side with the Web Forms you know and love but has a distinctly different set of design goals:

* Embrace HTTP and HTML—don't hide it.
* Testability is built-in from the ground up.
* Extensible at almost every point.
* Total control over your output.

This new framework is based around the Model View Controller (MVC) pattern, thus the name, ASP.NET MVC. The MVC pattern was originally invented back in the '70s as part of Smalltalk. As I'll show in this article, it actually fits into the nature of the Web quite well. MVC divides your UI into three distinct objects: the controller, which receives and handles input; the model, which contains your domain logic; and the view, which generates your output. In the context of the Web, the input is an HTTP request, and the request flow looks like Figure
above

This is actually quite different from the process in Web Forms. In the Web Forms model, the input goes into the page (the View), and the view is responsible for both handling the input and generating the output. When it comes to MVC, on the other hand, the responsibilities are separated.

Building Web Apps without Web Forms

Click Here

Insert Values into an Identity Column

Identity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted.

use tempdb
GO
IF OBJECT_ID('IdentityTable') IS NOT NULL
DROP TABLE IdentityTable
GO
CREATE TABLE IdentityTable (
TheIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
TheValue NVARCHAR(20) NOT NULL )
GO

Simply trying to INSERT a value into the identity column generates an error:

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (1, 'First Row')
GO

Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'IdentityTable' when IDENTITY_INSERT is set to OFF.

The trick is to enable IDENTITY_INSERT for the table. That looks like this:

SET IDENTITY_INSERT IdentityTable ON

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (3, 'First Row')

SET IDENTITY_INSERT IdentityTable OFF

Here are some key points about IDENTITY_INSERT

* It can only be enabled on one table at a time. If you try to enable it on a second table while it is still enabled on a first table SQL Server will generate an error.
* When it is enabled on a table you must specify a value for the identity column.
* The user issuing the statement must own the object, be a system administrator (sysadmin role), be the database owner (dbo) or be a member of the db_ddladmin role in order to run the command.

If you insert a value greater than the current identity seed SQL Server uses the value to reset the identity seed. For example:

SET IDENTITY_INSERT IdentityTable ON

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (10, 'Row Ten')

SET IDENTITY_INSERT IdentityTable OFF

INSERT IdentityTable(TheValue)
VALUES ('Should be 11')

SELECT * FROM IdentityTable
GO

(1 row(s) affected)

(1 row(s) affected)
TheIdentity TheValue
----------- --------------------
10 Row Ten
11 Should be 11

(2 row(s) affected)

Row Locking

BEGIN TRAN

SELECT *
FROM authors
WITH (HOLDLOCK, ROWLOCK)
WHERE au_id = '274-80-9391'

/* Do all your stuff here while the record is locked */

COMMIT TRAN

The HOLDLOCK hint will instruct SQL Server to hold the lock until you commit the transaction. The ROWLOCK hint will lock only this record and not issue a page or table lock.

The lock will also be released if you close your connection or it times out. I'd be VERY careful doing this since it will stop any SELECT statements that hit this row dead in their tracks. SQL Server has numerous locking hints that you can use. You can see them in Books Online when you search on either HOLDLOCK or ROWLOCK.

Converting Time Units With Math

* To convert seconds to hours, simply divide by 3600 (since each hour has 60 seconds * 60 minutes). The remainder is the remaining seconds.
* To convert seconds to minutes, simply divide by 60. The remainder is the remaining seconds.

Nothing too shocking there, right? So, let's do some math. If we have a TotalSeconds, we can get:

* Hours = (TotalSeconds / 3600)
* Remaining Minutes = (TotalSeconds % 3600) / 60
* Remaining Seconds = (TotalSeconds % 60)

(The % is the modulo operator in T-SQL, which returns the remainder when dividing two integers.)

Thus, we can write our SQL like this to return 3 integer columns (Hours, Minutes, Seconds) for each event:

select
EventID,
TotalSeconds / 3600 as Hours,
(TotalSeconds % 3600) / 60 as Minutes,
TotalSeconds % 60 as Seconds
from
(
select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events
) x


Results

EventID Hours Minutes Seconds
----------- ----------- ----------- -----------
1 6 11 22
2 7 42 29
3 0 21 6
4 3 51 21
5 1 15 53
6 5 24 0
7 16 50 2


Calculating Duration Totals

select
sum(TotalSeconds / 3600) as Hours,
sum((TotalSeconds % 3600) / 60) as Minutes,
sum(TotalSeconds % 60) as Seconds
from
(
select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events
) x

Hours Minutes Seconds
----------- ----------- -----------
38 214 133


select
sum(TotalSeconds) / 86400 as Days,
(sum(TotalSeconds) % 86400) / 3600 as Hours,
(sum(TotalSeconds) % 3600) / 60 as Minutes,
sum(TotalSeconds) % 60 as Seconds
from
(
select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events
) x

Days Hours Minutes Seconds
----------- ----------- ----------- -----------
1 17 36 13

Ajax Auto complete Extender webservice

Sample Code in the Webservice

public String[] FillList(string prefixText, int count)
{
SqlCommand cmd = new SqlCommand("select distinct BugTitle from bugmaster where bugtitle like '" + prefixText.Replace("'", "''") + "%'", new clsGroConnection().getOpenCon());
SqlDataAdapter da = new SqlDataAdapter(cmd);
int nn_itmCount;
DataTable dt=new DataTable("renju");
da.Fill(dt);
ArrayList arr = new ArrayList();
for (nn_itmCount = 0; nn_itmCount < ((dt.Rows.Count >= 100) ? 10 : dt.Rows.Count); nn_itmCount++)
{
arr.Add(dt.Rows[nn_itmCount]["BugTitle"]);
}
return (String[])arr.ToArray(typeof(string));
}

STUFF vs REPLACE

(see PIVOT Table Post for its usage)

STUFF - Deletes a specified length of characters and inserts another set of characters at a specified starting point.
SELECT STUFF('abcdef', 2, 3, 'ijklmn')
GO
Here is the result set:
---------
aijklmnef
REPLACE - Replaces all occurrences of the second given string expression in the first string expression with a third expression.
SELECT REPLACE('abcdefghicde','cde','xxx')
GO
Here is the result set:
------------
abxxxfghixxx

Delete the rows which are duplicate (don’t delete both duplicate records)

SET ROWCOUNT 1
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
WHILE @@rowcount > 0
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
SET ROWCOUNT 0

Table's row count-you can use alternative way instead of SELECT COUNT(*)

Because SELECT COUNT(*) statement make a full table scan to return the total table's row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*):


SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2

So, you can improve the speed of such queries in several times.

Bubble Sort Algorithm in C#

static int[] bubbleSort(int[] numbers, int array_size)
{
int i, j, temp;

for (i = (array_size - 1); i >= 0; i--)
{
for (j = 1; j <= i; j++)
{
if (numbers[j - 1] > numbers[j])
{
temp = numbers[j - 1];
numbers[j - 1] = numbers[j];
numbers[j] = temp;
}
}
}
return numbers;
}

Returning multiple values from a Function

public static int add(int a, int b, ref int c)
{
c = a + 2;
return a + b;
}

We can use out and ref,can be called as shown below

int e = add(2, 3, ref d);

How to format a Date or DateTime in SQL Server

Question: How do you create a DateTime in a specific format in SQL Server?

Answer: You don't. You can't. The only way to translate a DateTime into a specific format is to convert it to a VARCHAR or other "string" data type. This means that it is no longer a DateTime. It is a VARCHAR.

This might not be the answer you are looking for, but please ... don't stop reading!

. . .

Why do people have trouble with the concept of raw data versus the presentation of that data? Am I missing something? If you use CONVERT() in an attempt format your DateTime data, surely people must understand that CONVERT() also converts that data from a DateTime to another data type? It's right there in the expression:

SELECT CONVERT(varchar(10), someDate)

That clearly is CONVERTing someDate to a varchar(10), isn't it? What gets returned when you convert something to a VARCHAR, a date or a string? The answer is a string of meaningless characters that no longer have any value as an actual date.

"You clearly are a hack, Jeff," you tell me, "since it is very easy for a SQL-Master such as myself to format dates without converting them to strings. For example, check this out this sweet 'mm/dd/yyyy' format:

select right('0' + rtrim(month(@d)),2) + '/' + right('0' + rtrim(day(@d)),2) + '/' + rtrim(year(@d)).

I can write stuff like this all week! It's easy. You've got much to learn!"

Well, I have bad news for you. That still is implicitly converting everything to a string -- the rtrim() function is handling that part. This is even worse than doing it explicitly with a CONVERT() function, and the end result is not easy to read or work with or write, it is not efficient, and it is still not returning a DateTime value.

. . .

Always remember: If a value is not a DateTime datatype, it is not a date. No matter what it looks like, or how neatly formatted you made that string, or how careful you were to use an ISO compliant format, it is not a Date. Period.

It is crucial to understand this, and to thus to understand the implications of trying to "format" data at the database layer. It cannot be done! All you can do is convert things to generic "string" datatypes. That's it.

. . .

No matter what they may look like, strings don't sort like dates. They don't compare like dates. You can't get the month from a string consistently, or calculate the amount of minutes between two strings, or add x days to a string. You can't ensure that different databases or stored procedures or functions or applications will always interpret your chosen date formatted string the same. Client applications -- who should be doing the formatting -- cannot apply date formatting to a string, they need an actual date stored in the correct data type. Thus, they would need to convert this string back to a DateTime type and only then can they format it for display purposes or use standard date calculations on the value. Does it really make sense to start with a date value, convert it to a string in SQL, and then have your client convert it back to a date value?

Simply return raw data from your database using the proper data types, and then simply use the tools designed to handle raw data in the correct types at your clients to format and present that data.

* In crystal reports or other reporting tools, you can just drop your nice, clean, raw unformatted datetime value on your report, right-click it, and easily format it any way you want. You can use regional settings, specify mm/dd/yyyy format strings, and all kinds of options. It's simple and easy, but you must return datetime values back from SQL, not VARCHARS!
* In Excel, again, you can simply right-click and choose any format you want, or create your own.
* In .NET applications, you can usually format dates in data bound controls using the GUI interface, and you can also format things using the ToString() method of a true datetime value and specify all kinds of simple yet flexible formatting strings.
* In ASP.NET web pages, just about all data bound web controls let you specify a FormatString property for your bound columns, giving you clear, simple control over exactly how your dates look.
* In VB and VBA, there is a Format() function that again works with named formats or custom format strings.
* In MS Access, the report and form designer lets you format any text box containing a datetime value any way you want, again with simple format names or format strings, and you have all of the VBA functions available to format dates in your code. You can even specify the specific date format for columns in a query in the query designer -- but, again, you must be working with data in the correct datetime data type.

Isn't it much easier to simply right-click on something and then enter a simple "mmm dd, yyyy" format string instead of building and parsing this manually using CONVERT and SUBSTRING parsing in T-SQL? Isn't it more flexible to do all formatting at your presentation layer so that you can just return data from your database and not worry about how it looks? Then 5 different clients can query the same stored procedure and each output those dates any way they want -- without changing any database code.

Sql Server TimeSpan

With those functions in place, we can add two more that will give us further flexibility when working with dates and times: The concept of a "TimeSpan", very similar to what is available in the .NET framework.

create function TimeSpan(@Days int, @Hours int, @Minutes int, @Seconds int)
-- returns a datetime the specified # of days/hours/minutes/seconds from the "base" date of 1/1/1900 (a "TimeSpan")
returns datetime
as
begin
return dbo.Time(@Hours,@Minutes,@Seconds) + @Days
end

create function TimeSpanUnits(@Unit char(1), @TimeSpan datetime)
-- returns the # of units specified in the TimeSpan.
-- The Unit parameter can be: "d" = days, "h" = hours, "m" = minutes, "s" = seconds
returns int
as
begin
return case @Unit
when 'd' then datediff(day, 0, @TimeSpan)
when 'h' then datediff(hour, 0, @TimeSpan)
when 'm' then datediff(minute, 0, @TimeSpan)
when 's' then datediff(second, 0, @TimeSpan)
else Null end
end

Here, a TimeSpan is just a datetime offset from the "base" date of 1/1/1900. Creating one is the same as creating a Time using the Time() function, but we have added a parameter for Days to give more flexibility.

The TimeSpanUnits() function works similar to standard T-SQL DatePart() function, but it returns the total # of units in the given time span. So, if you create a time span of 1 day and 2 hours, then TimeSpanUnits("d") will return 1 and TimeSpanUnits("h") will return 26. Negative values can be returned as well. You also may wish to implement the TimeSpanUnits() function as multiple functions, one per unit (e.g., TimeSpanHours(), TimeSpanDays(), etc) depending on your preference.

Of course, a simple way to create a TimeSpan is to simply subtract two standard T-SQL DateTimes. Also please note that we can add and subtract Dates, Times, and TimeSpans all together using standard + and - operators and everything will work as expected. We can also add integers to our Dates and Times which will add entire days to the values.

Here's a TimeSpan usage example:

declare @Deadline datetime -- remember, we still use standard datetimes for everything, include TimeSpans
set @Deadline = dbo.TimeSpan(2,0,0,0) -- the deadline is two days

declare @CreateDate datetime
declare @ResponseDate datetime

set @CreateDate = dbo.DateTime(2006,1,3,8,30,0) -- Jan 3, 2006, 8:30 AM
set @ResponseDate = getdate() -- today

-- See if the response date is past the deadline:
select case when @ResponseDate > @CreateDate + @Deadline then 'overdue.' else 'on time.' end as Result

-- Find out how many total hours it took to respond:
declare @TimeToRepond datetime
set @TimeToRespond = @ResponseDate - @CreateDate

select dbo.TimeSpanUnits('h', @TimeToRespond) as ResponseTotalHours

-- Return the response time as # of days, # of hours, # of minutes:
select dbo.TimeSpanUnits('d',@TimeToRespond) as Days, DatePart(hour, @TimeToRespond) as Hours, DatePart(minute, @TimeToRespond) as Minutes

-- Return two days and two hours from now:
select getdate() + dbo.TimeSpan(2,2,0,0)

SQL Server Date, Time and DateTime Functions

create function DateOnly(@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
begin
return dateadd(dd,0, datediff(dd,0,@DateTime))
end
go

create function Date(@Year int, @Month int, @Day int)
-- returns a datetime value for the specified year, month and day
-- Thank you to Michael Valentine Jones for this formula (see comments).
returns datetime
as
begin
return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
end
go

create function Time(@Hour int, @Minute int, @Second int)
-- Returns a datetime value for the specified time at the "base" date (1/1/1900)
-- Many thanks to MVJ for providing this formula (see comments).
returns datetime
as
begin
return dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)
end
go

create function TimeOnly(@DateTime DateTime)
-- returns only the time portion of a DateTime, at the "base" date (1/1/1900)
returns datetime
as
begin
return @DateTime - dbo.DateOnly(@DateTime)
end
go

create function DateTime(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
-- returns a dateTime value for the date and time specified.
returns datetime
as
begin
return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute,@Second)
end
go

Remember that you must prefix UDFs with the owner (usually dbo) when calling them.

Usage Examples:

* where TransactionDate >= dbo.Date(2005,1,2) -- no formatting or implicit string conversions needed for date literals

* select dbo.Date(year(getdate()), 1,1) -- returns the first day of the year for the current year.

* select dbo.DateOnly(getdate()) -- returns only the date portion of the current day.

* if dbo.TimeOnly(SomeDate) = dbo.Time(5,30,0) -- check to see if the time for a given date is at 5:30 AM

* select dbo.Date(year(getdate()), month(getdate()),1) -- returns the first day of the current month.

* select dbo.Date(year(getdate()), month(getdate())+1,0) -- returns the last day of the current month.

* where SomeDate >= dbo.DateOnly(getdate()) and SomeDate < dbo.DateOnly(getDate())+1 -- a simple way to get all transactions that occurred on the current date

* select dbo.DateOnly(getdate()) + 1 + dbo.Time(14,30,0) -- returns tomorrow at 2:30 PM.

Joins,Groupby,Datetime Stored Procedures(REFERENCE)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Renju.R
-- Create date: August 1,2008
-- Description:
-- =============================================
ALTER procedure [dbo].[GetEmpPerformance]
@fromDate datetime,
@toDate datetime
--@projectId int,
--@moduleId int
as
begin
select ws.currentStatus,am.activityName,
w.workID,w.work,w.startDate,w.finishDate,w.formId,w.activityId,
ts.activityId,sum(ts.hoursInActivity),sum(ts.minInActivity),
(select UserName from dbo.UserRegMaster where UserID=w.toUser) as ToUser,
--dbo.GetWorkHours(@fromDate, @toDate) TotalWorkingHours,
sum(DATEDIFF(hour,w.startDate,w.finishDate)) as WorkHours,
sum(DATEDIFF(minute,w.startDate,w.finishDate) - (DATEDIFF(hour,w.startDate,w.finishDate) * 60)) as WorkMinutes
from work w
left outer join dbo.workStatus ws on ws.workID=w.workID
and statusDate=(select max(statusDate) from workStatus where workId=w.workID)
left outer join dbo.TimeSheet ts
on ts.activityId=w.activityId and
ts.workID=w.workID
left outer join dbo.ActivityMaster am on ts.activityId=am.activityId and ts.workID=w.workID
where --projID=@projectId and modID=@projectId and
startDate between
convert(datetime,@fromDate,101) and convert(datetime,@toDate,101) group by
ws.currentStatus,am.activityName,
w.workID,w.work,w.startDate,w.finishDate,w.formId,w.activityId,
ts.activityId,ToUser
end

Looping and inserting values to Datatable

public DataTable ModifyData(DataSet dsMod)
{

int rCount = 0;
string temp2 = "";
DataTable dtNew = new DataTable();
DataTable dt = dsMod.Tables[0];
DataRow dr;
for (int k = 0; k <= dt.Columns.Count - 1; k++)
{
string cname = dt.Columns[k].ColumnName;
dtNew.Columns.Add(cname);
}

foreach (DataRow dataRow in dt.Rows)
{
int cnt = 0;
dr = dtNew.NewRow();
dtNew.Rows.Add(dr);
foreach (DataColumn dataColumn in dt.Columns)
{
string t1 = dataRow[dataColumn].ToString();
if (Convert.ToString(dataColumn) != "Name")
{
if (t1 == "")
{
temp2 = "";
}
else
{
temp2 = clsCommon.MinutesToHours(Convert.ToInt32(t1));
}
}
else
{
temp2 = t1;
}
dtNew.Rows[rCount][cnt] = temp2.ToString();
cnt++;

}
rCount++;
}
return dtNew;
}

Exporting DataTable to Excel

private void ExportToExcel(DataTable dtIn)
{

DataTable dt = dtIn;
if (dt != null)
{
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment;filename=FileName.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
string tab = "";
foreach (DataColumn dc in dt.Columns)
{
Response.Write(tab + dc.ColumnName);
tab = "\t";
}
Response.Write("\n");
int i;
foreach (DataRow dr in dt.Rows)
{
tab = "";
for (i = 0; i < dt.Columns.Count; i++)
{
Response.Write(tab + dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.End();
}

}

Dynamic DataGrid Templates

public void DynamicGrid()
{
DataGrid dg = new DataGrid();
DataSet ds = objws.getempinactivity(1, 8, DateTime.Now.AddDays(-50), DateTime.Now);
DataTable dt = ds.Tables[0];

for (int i = 0; i <= dt.Columns.Count - 1; i++)
{
TemplateColumn templateColumn = new TemplateColumn();
string columnName = dt.Columns[i].ColumnName;
templateColumn.HeaderTemplate = new DataGridTemplate(ListItemType.Header, columnName);
for (int j = 0; j <= dt.Rows.Count - 1; j++)
{
string value = dt.Rows[j][i].ToString();
templateColumn.ItemTemplate = new DataGridTemplate(ListItemType.Item, value);

}
DataGrid1.Columns.Add(templateColumn);
}
DataGrid1.DataSource = ds;
DataGrid1.DataBind();
}



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;

///
/// RENJU.R
/// Summary description for DataGridTemplate
///
public class DataGridTemplate : ITemplate
{
ListItemType templateType;
string columnName;
public DataGridTemplate(ListItemType type, string colname)
{
//
// TODO: Add constructor logic here
//
templateType = type;
columnName = colname;

}
public void InstantiateIn(System.Web.UI.Control container)
{
Literal lc = new Literal();
switch (templateType)
{
case ListItemType.Header:
lc.Text = "" + columnName + "";
container.Controls.Add(lc);
break;
case ListItemType.Item:
lc.Text = columnName;
container.Controls.Add(lc);
break;
case ListItemType.EditItem:
TextBox tb = new TextBox();
tb.Text = "";
container.Controls.Add(tb);
break;
case ListItemType.Footer:
lc.Text = "" + columnName + "";
container.Controls.Add(lc);
break;
}
}
//public void InstantiateIn(Control container)
//{
// LiteralControl l = new LiteralControl();
// l.DataBinding += new EventHandler(this.OnDataBinding);
// container.Controls.Add(l);
//}

//public void OnDataBinding(object sender, EventArgs e)
//{
// LiteralControl l = (LiteralControl)sender;
// DataGridItem container = (DataGridItem)l.NamingContainer;
// l.Text = ((DataRowView)container.DataItem)[colname].ToString();
//}
}
Poste

Sql Server Pivot Table

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Renju.R
-- Create date: August 5,2008
-- Description: Cross Tabbing Tables
-- =============================================
ALTER procedure [dbo].[crosstab]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
as
begin
declare @sql varchar(8000), @delim varchar(1)
set nocount on
set ansi_warnings off
exec ('select ' + @pivot + ' as pivot 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)' )
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='pivot'
select @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' case ' + @pivot + ' when '
+ @delim + convert(varchar(100), pivot) + @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)
end
set ansi_warnings on

For Executing the procedure some Examples are as below


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[EvaluationFormWise]
as
begin
execute crosstab
'select pm.ProjectName,mm.ModuleName,fm.formName,fm.formType,
sm.SeverityName from bugmaster bm
inner join formmaster fm on fm.formId=bm.formid
inner join dbo.SeverityMaster sm on sm.SeverityID=bm.SeverityID
inner join dbo.ProjectMaster pm on pm.projectId=bm.projectId
inner join dbo.ModuleMaster mm on mm.moduleId=bm.moduleid
left outer join UserRegMaster um on um.userid=bm.programmerId
left outer join UserRegMaster urm on urm.userid=bm.submitterid
group by pm.ProjectName,mm.ModuleName,fm.formName,fm.formType,sm.SeverityName',
'count(bm.SeverityID)',
'SeverityName',
'SeverityMaster'
end

Asp.Net Message Box

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;
using System.Collections;
using System.Text;

///
/// Summary description for MessageBox
///
/// Done by RENJU.R
/// You are licenced to distribute the code
/// http://www.renjucool.co.nr

public class MessageBox
{

private static Hashtable m_executingPages = new Hashtable();
private MessageBox() { }
public static void Show(string sMessage)
{
if (!m_executingPages.Contains(HttpContext.Current.Handler))
{
Page executingPage = HttpContext.Current.Handler as Page;
if (executingPage != null)
{
Queue messageQueue = new Queue();
messageQueue.Enqueue(sMessage);
m_executingPages.Add(HttpContext.Current.Handler, messageQueue);
executingPage.Unload += new EventHandler(ExecutingPage_Unload);
}
}
else
{
Queue queue = (Queue)m_executingPages[HttpContext.Current.Handler];
queue.Enqueue(sMessage);
}
}

private static void ExecutingPage_Unload(object sender, EventArgs e)
{
Queue queue = (Queue)m_executingPages[HttpContext.Current.Handler];
if (queue != null)
{
StringBuilder sb = new StringBuilder();
int iMsgCount = queue.Count;
sb.Append("");
m_executingPages.Remove(HttpContext.Current.Handler);
HttpContext.Current.Response.Write(sb.ToString());

}
}

}

Sql Server getting date difference in hours,min,sec

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Renju.R
-- Create date: July 31
-- Description: getting date difference in hours,min,sec
-- =============================================
ALTER FUNCTION [dbo].[GetDateDiff]
(
@startDate datetime,
@finishDate datetime
)
RETURNS TABLE
AS
RETURN
(
SELECT DATEDIFF(hour,@startDate,@finishDate) as TotalHours,
DATEDIFF(minute,@startDate,@finishDate) - (DATEDIFF(hour,@startDate,@finishDate) * 60) as TotalMinutes,
DATEDIFF(second,@startDate,@finishDate) - (DATEDIFF(minute,@startDate,@finishDate) * 60) as TotalSeconds
)

ASP.NET Site Search

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;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Text;
using System.IO;
using System.Text.RegularExpressions;
//Programmed by Renju.R
//Refer Microsoft Indexing Service
public partial class _Default : System.Web.UI.Page
{
public static DataView tempds = new DataView();
public static int resultCount = 0;
public DataRow drIndexed;
public DataTable dtPub = new DataTable();
public DataSet dsPub = new DataSet();
public DataRow dr2;
public DataTable dt2 = new DataTable();
public DataSet ds2 = new DataSet();
public DataSet ds = new DataSet();
public DataRow dr;
public DataTable dt = new DataTable();
public static StringBuilder stb = new StringBuilder();
// public static OleDbConnection oldbCon = new OleDbConnection("Provider=MSIDXS.1;Integrated Security .='';Data Source=Reader");
public static OleDbConnection oldbCon = new OleDbConnection("Provider=MSIDXS.1;");
public int count;
protected void Page_Load(object sender, EventArgs e)
{
}
public void FillResult()
{
//ds.Merge(dtTemp, false, MissingSchemaAction.Add);
//ds.Merge(ds2.Tables[0]);
//ds.Tables[0].Merge(ds2.Tables[0]);
//tempds = ds.Tables[0].Copy().DefaultView;
MergeSearch();
Searchfill();
}
protected void Button1_Click(object sender, EventArgs e)
{
string searchText = TextBox1.Text.ToString();
//OleDbCommand cmd = new OleDbCommand("select doctitle, filename, Path ,Attrib, rank, characterization from scope() where FREETEXT('" + searchText + "') and filename <> 'Default.aspx' order by rank desc", oldbCon);
OleDbCommand cmd = new OleDbCommand("select doctitle, filename, Path ,Attrib, rank, characterization from saravanan.Reader..scope() where FREETEXT('" + searchText + "') and filename <> 'Default.aspx' order by rank desc", oldbCon);
cmd.CommandType = CommandType.Text;
if (oldbCon.State == ConnectionState.Closed)
{
oldbCon.Open();
}
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
resultCount = ds.Tables[0].Rows.Count;
ds2.Tables.Add(dt2);
dt2.Columns.Add("new");
/////
//ds.Tables.Add(dt);
//dt.Columns.Add("search");
////
dsPub.Tables.Add(dtPub);
dtPub.Columns.Add("text");
foreach (DataRow drTemp in ds.Tables[0].Rows)
{
ReadDocuments((string)drTemp["path"]);
}
FillResult();
}
void Searchfill()
{
DataList1.DataSource = ds.Tables[0].DefaultView;
DataList1.DataBind();
}
public DataSet MergeSearch()
{
ds.Tables[0].Columns.Add("search");
int cnt = 0;
// ds.AcceptChanges();
//int mCount = ds2.Tables[0].Rows.Count;
foreach (DataRow drLoop in ds2.Tables[0].Rows)
{
DataRow drTest= ds.Tables[0].Rows[cnt];
drTest["search"] = drLoop["new"];
//dt.Rows.Add(drTest);
// ds.Tables[0].Rows.Add(drTest);
cnt++;
}
return ds;
}
public DataSet MergedValues(string str)
{
DataRow dr5 = ds2.Tables[0].NewRow();
dr5["new"] = (string)str;
dt2.Rows.Add(dr5);
return ds2;
}
public DataSet SearchResult(string text)
{
DataRow drResult = dsPub.Tables[0].NewRow();
drResult["text"] = (string)text;
CreateExcerpt((string)text, TextBox1.Text.ToString(),ds);
dtPub.Rows.Add(drResult);
return dsPub;
}
private string parseHtml(string html)
{
string temp = Regex.Replace(html, "<[^>]*>", "");
return temp.Replace(" ", " ");
}
public string CreateExcerpt(string source, string keyword,DataSet ds)
{
count = 0;
string excerpt = string.Empty;
int charsBeforeAndAfter = 100;
int index = source.IndexOf(keyword, StringComparison.CurrentCultureIgnoreCase);
if (index >= 0)
{
int excerptStartIndex = 0;
int excerptEndIndex = source.Length - 1;
if (index > (charsBeforeAndAfter - 1))
excerptStartIndex = index - charsBeforeAndAfter;
if ((index + keyword.Length + charsBeforeAndAfter) < (source.Length - 1))
excerptEndIndex = index + keyword.Length + charsBeforeAndAfter;
excerpt = source.Substring(excerptStartIndex, excerptEndIndex - excerptStartIndex + 1);
index = excerpt.IndexOf(keyword, StringComparison.CurrentCultureIgnoreCase);
excerpt = excerpt.Insert(index + keyword.Length, "");
excerpt = excerpt.Insert(index, "");
excerpt = string.Format("...{0}...", excerpt);
foreach (DataRow dr in ds.Tables[0].Rows)
{
if(count ==0)
MergedValues(excerpt);
count++;
}
}

//stb.Append(excerpt.ToString());
//stb.Append("
");
//stb.Append("
");
return excerpt;
}
public void ReadDocuments(string docPath)
{
FileStream fs = File.OpenRead(docPath);
string dumptext;
using(StreamReader sr = new StreamReader(docPath, System.Text.Encoding.Default))
{
dumptext = parseHtml(sr.ReadToEnd());
}
SearchResult((string)dumptext);


}
}