MS SQL Server Query to get Date difference of two dates in Days hours minutes and seconds

Hi in this post i will show how to get the date difference of two dates in DD:HH:MM:SS:MS format in MS Sql Server.

DD: Days
HH: Hours
MM: Minutes
SS: Seconds
MS: Milli Seconds

Sql Query :

SELECT *
,Days = datediff(dd, 0, DateDif)
,Hours = datepart(hour, DateDif)
,Minutes = datepart(minute, DateDif)
,Seconds = datepart(second, DateDif)
,MS = datepart(ms, DateDif)
FROM (
SELECT DateDif = EndDate - StartDate
,a.*
FROM (
SELECT StartDate = convert(DATETIME, '05-02-2013 01:10:00.000')
,EndDate = getdate()
) a
) Result

Output:

Generate PDF from a html string using ItextSharp in asp.net (c#)

In this post i will show how to create a pdf file from a html string using itextsharp in asp.net.

Below is the code to do this (There may be redundant code in the given sample below. So you can edit the below code as per your needs)

//creating html:

        public void ViewReportPDF(Int32 EmpID)
        {
            SqlCommand cmd = new SqlCommand("USP_GET_EMP_DATA");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@empid", EMPID);
            DataSet ds = GetDataSet(cmd);

            int count = 0;
            string s1 = " <table border='1' width='100%'>";

            string s2 = "";

            for (int j = 0; j <= ds.Tables.Count - 1; j++)
            {
                for (int i = 0; i <= ds.Tables[j].Rows.Count - 1; i++)
                {
                    for (int k = 0; k <= ds.Tables[j].Columns.Count - 1; k++)
                    {
                        if (count == 2)
                        {
                            count = 0;
                        }

                        if (count == 0)
                        {
                            s2 = s2 + "<tr>";
                        }

                        s2 = s2 +
                        "<td>" + ds.Tables[j].Columns[k].ColumnName + "</td>" +
                        "<td>" + ds.Tables[j].Rows[i][k] + "</td>";

                        count++;

                        if (count == 2)
                        {
                            s2 = s2 + "</tr>";
                        }
                    }
                }
            }

            string s3 = "</table>";

            string htmlText1 = s1 + s2 + s3;
            HTMLToPdf(htmlText1);
        }

//creating pdf using itextsharp and saving it in the application folder:

        public void HTMLToPdf(string HTML)
        {
            Document document = new Document(PageSize.A4);
            PdfWriter.GetInstance(document, new FileStream(Request.PhysicalApplicationPath + "\\EMP.pdf", FileMode.Create));
            document.Open();
            iTextSharp.text.html.simpleparser.StyleSheet styles = new iTextSharp.text.html.simpleparser.StyleSheet();
            iTextSharp.text.html.simpleparser.HTMLWorker hw = new iTextSharp.text.html.simpleparser.HTMLWorker(document);
            hw.Parse(new StringReader(HTML));
            document.Close();
            string ss = Request.PhysicalApplicationPath.ToString() + "\\EMP.pdf";
            ShowPdf(ss);
        }

//showing pdf from folder where it was saved.

        private void ShowPdf(string s)
        {
            string fileName = Path.GetFileNameWithoutExtension(s);
            Response.ContentType = "application/pdf";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + "EMP" + ".pdf");
            Response.TransmitFile(Request.PhysicalApplicationPath.ToString() + "\\" + fileName + ".pdf");
            HttpContext.Current.ApplicationInstance.CompleteRequest();

        }

Output:

The output of pdf would be in below structure as given below:

Applying join on two datatable in asp.net (c#) using LINQ | using join, where clause in LINQ

Applying join on two datatable in asp.net (c#) using LINQ.

Below is the Query :

1.LINQ Join Query:

DataTable result = (from t1 in SomeDatatable1.AsEnumerable()
                    join t2 in SomeDatatable2.AsEnumerable() on t1.Field<string>("EmpId") equals                                                                                       t2.Field<string>("EmpId")
                    select t1).CopyToDataTable();


2.LINQ Join with Where clause Query:

DataTable result = (from t1 in SomeDatatable1.AsEnumerable()
                    join t2 in SomeDatatable2.AsEnumerable() on t1.Field<string>("EmpId") equals                                                                                       t2.Field<string>("EmpId")
                    where t2.Field<string>("EmpId") == "100"
                    select t1).CopyToDataTable();