Calculate Age from Date Of Birth using SQL Server

set your birthday and Run this Query and you get your age..in years,months,days,hours, and minutes.....


declare @birth_day datetime
set @birth_day ='19860804 12:34:22'
select
years,
months,
case
when day(@birth_day)>day(getdate())
then day(getdate())+
datediff(day,@birth_day,dateadd(month,datediff(month,0,@birth_day)+1,0))-1
else day(getdate())-day(@birth_day)
end as days,
datepart(hour,convert(varchar(10),dateadd(minute,minutes,0),108)) as hours,
datepart(minute,convert(varchar(10),dateadd(minute,minutes,0),108)) as minutes
from
(
select
years,
datediff(month,dateadd(year,years,@birth_day),getdate())+
case
when day(getdate())>=day(@birth_day)
then 0
else -1
end as months,
datediff(minute,convert(varchar(8),@birth_day,108),
convert(varchar(8),getdate(),108)) as minutes
from
(
select
datediff(year,@birth_day,getdate()) +
case
when month(getdate())>=month(@birth_day)
then 0
else -1
end as years
) as t
) as t

XML File Creation using ASP.NET & C#

//XML File Creation
XmlDocument doc = new XmlDocument();

// XML declaration
XmlNode declaration = doc.CreateNode(XmlNodeType.XmlDeclaration, null, null);
doc.AppendChild(declaration);

// Root element: article
XmlElement root = doc.CreateElement("article");
doc.AppendChild(root);

// Sub-element: author
XmlElement author = doc.CreateElement("author");
author.InnerText = "Faisal Khan";
root.AppendChild(author);

// Attribute: isadmin
XmlAttribute isadmin = doc.CreateAttribute("isadmin");
isadmin.Value = "true";
author.Attributes.Append(isadmin);

// Sub-element: title
XmlElement title = doc.CreateElement("title");
title.InnerText = "Sample XML Document";
root.AppendChild(title);

// Sub-element: body (CDATA)
XmlElement body = doc.CreateElement("body");
XmlNode cdata = doc.CreateCDataSection("This is the body of the article.");
body.AppendChild(cdata);
root.AppendChild(body);


doc.Save(Server.MapPath("../text.xml"));//it makes xml file in root folder


//End of XML Creation

Sending email through a Google Apps account With ASP.NET

//Include Namespace

using System.Net.Mail;
using System.Net;
using System.IO;




//Code for sending mail

string mailFrom = "email id which appears in receiver's inbox";
string mailToInquiry = "receiver's email id";
string mailServer = "smtp.gmail.com";
string mailUsername = "email id which you want to use for sending email";
string mailPassword = "password of above email id";
int mailPort = 587;


string msgBody = "";
if (File.Exists(MapPath("emailInquiry.htm")))
{
String fileName = Server.MapPath("emailInquiry.htm"); ;
StreamReader strmR = File.OpenText(fileName);
msgBody = strmR.ReadToEnd();
strmR.Close();
}

msgBody = msgBody.Replace("{VAR_Name}", txtName.Text);
msgBody = msgBody.Replace("{VAR_Address}", txtAddress.Text);
msgBody = msgBody.Replace("{VAR_ContactNo}", txtContactNo.Text);
msgBody = msgBody.Replace("{VAR_Email}", txtEmail.Text);
msgBody = msgBody.Replace("{VAR_Comments}", txtComments.Text);

// Specify the file to be attached and sent.
// This example assumes that a file named Data.xls exists in the
// current working directory.

// Create a message and set up the recipients.
MailMessage message = new MailMessage(
mailFrom,
mailToInquiry,
txtName.Text + " is wants to contact you.",
msgBody);

message.IsBodyHtml = true;

//Send the message.
SmtpClient client = new SmtpClient(mailServer);
// Add credentials if the SMTP server requires them.

client.Port = mailPort;
client.EnableSsl = true;
client.Timeout = 60000;
client.Credentials = new NetworkCredential(mailUsername, mailPassword);
// smtp.Send(mail);


try
{
client.Send(message);
ClearData();

LblMsg.Text = "Thank you for contacting us we will get back to you as soon as possible..!!";
}
catch (Exception ex)
{
Console.WriteLine("Exception caught in CreateMessageWithAttachment(): {0}",
ex.ToString());
}

Insert Comma seperated values into table

Code Starts here---->>>>>

CREATE PROCEDURE [dbo].[YourProcedure](@string varchar(50))
AS
DECLARE @pos integer = 1,
@previous_pos integer = 0,
@value varchar(50)
BEGIN
WHILE @pos > 0
BEGIN
SET @pos = CHARINDEX(',',@string,@previous_pos+1)
IF @pos > 0
BEGIN
SET @value = SUBSTRING(@string,@previous_pos+1,@pos-@previous_pos-1)
INSERT INTO YourTable VALUES (@value)
SET @previous_pos = @pos
END
END
IF @previous_pos < LEN(@string)
BEGIN
SET @value = SUBSTRING(@string,@previous_pos+1,LEN(@string))
INSERT INTO YourTable VALUES (@value)
END
END

ASP.Net C# Convert DataSet to ArrayList

In ASP.Net, to convert DataSet to ArrayList using C# code requires some knowledge about DataSet: in-memory data collection. You must know that ASP.Net DataSet can hold the schema, DataTable as well as data retrieved from the SQL database table. DataSet represents the in-memory structure of table data retrieved from the database. DataSet as the name suggests consists of DataTable collection that can store the table structure i.e. column names, table name. It stores each DataTable in the collection at zero- based index that can be retrieved by passing the index of table whose data you want to manipulate, display and bind to any data presentation control of ASP.Net. For converting the ASP.Net DataSet to ArrayList you can select the DataTable based on its index in the DataTable Collection and insert its each row into the ArrayList as an object.

C# code for ASP.Net Convert DataSet to ArrayList

DataSet myDataSet = new DataSet();


// create an instance for ArrayList
ArrayList myArrayList = new ArrayList();

// foreach loop to read each DataRow of DataTable stored inside the DataSet
foreach (DataRow dRow in myDataSet.Tables[0].Rows)
{
// add DataRow object to ArrayList
myArrayList.Add(dRow);
}


Above C# code shows how to convert DataSet to ArrayList. Add function of ArrayList accepts the parameter as object type. DataRow object has been passed in the above example to the Add function of ArrayList to store it in the collection.

Now the next step is also necessary to retrieve the values of columns stored in each row added to the ArrayList collection. Following C# code shows how to retrieve the ArrayList item as object, then convert it to DataRow and read the column value to display it on the ASP.Net web page:

// foreach loop to get each item of ArrayList
foreach (Object objRow in myArrayList)
{
Response.Write(((DataRow)objRow)[ "categoryId" ].ToString() + " " + ((DataRow)objRow)[ "categoryName" ].ToString() + "
");
}

Delete All Tables from Database

Declare @sql Varchar(500)
Declare @get_table varchar(100)
DECLARE s_gettable CURSOR FOR SELECT name FROM sys.objects where type_desc ='USER_TABLE'
OPEN s_gettable
FETCH NEXT FROM s_gettable INTO @get_table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
Set @sql='drop table dbo.'+@get_table
exec(@sql)
END
FETCH NEXT FROM s_gettable INTO @get_table
END
CLOSE s_gettable
DEALLOCATE s_gettable

Download File in ASP.NET

protected void Button1_Click(object sender, EventArgs e)

{

// Get the physical Path of the file(test.doc)
string filepath = Server.MapPath("images/5.jpg");
// Create New instance of FileInfo class to get the properties of the file being downloaded
FileInfo file = new FileInfo(filepath);
// Checking if file exists
if (file.Exists)

{

// Clear the content of the response

Response.ClearContent();

Response.Clear();

// LINE1: Add the file name and attachment, which will force the open/cance/save dialog to show, to the header

Response.AddHeader(

"Content-Disposition", "attachment; filename=" + file.Name); // Add the file size into the response header

Response.AddHeader(

"Content-Length", file.Length.ToString()); // Set the ContentType

Response.ContentType = ReturnExtension(file.Extension.ToLower());

// Write the file into the response (TransmitFile is for ASP.NET 2.0. In ASP.NET 1.1 you have to use WriteFile instead)

Response.TransmitFile(file.FullName);

Response.Flush();

// End the response

Response.End();

}

}

private string ReturnExtension(string fileExtension)

{

switch (fileExtension)

{

case ".htm":

case ".html":

case ".log":

return "text/HTML";

case ".txt":

return "text/plain";

case ".doc":

return "application/ms-word";

case ".tiff":

case ".tif":

return "image/tiff";

case ".asf":

return "video/x-ms-asf";

case ".avi":

return "video/avi";

case ".zip":

return "application/zip";

case ".xls":

case ".csv":

return "application/vnd.ms-excel";

case ".gif":

return "image/gif";

case ".jpg":

case "jpeg":

return "image/jpeg";

case ".bmp":

return "image/bmp";

case ".wav":

return "audio/wav";

case ".mp3":

return "audio/mpeg3";

case ".mpg":

case "mpeg":

return "video/mpeg";

case ".rtf":

return "application/rtf";

case ".asp":

return "text/asp";

case ".pdf":

return "application/pdf";

case ".fdf":

return "application/vnd.fdf";

case ".ppt":

return "application/mspowerpoint";

case ".dwg":

return "image/vnd.dwg";

case ".msg":

return "application/msoutlook";

case ".xml":

case ".sdxl":

return "application/xml";

case ".xdp":

return "application/vnd.adobe.xdp+xml";

default:

return "application/octet-stream";

}

}