How to export List Data to EXCEL,Word,PDF..

Export List data to Excel, Word, Pdf is shown below.

In Controller:
Getting data :

public ActionResult jQGRID()

        {

            Sample sample = new Sample();

            List<Sample> lst = new List<Sample>();

            lst = sample.GetList();

            Session["Export"] = lst;

            return View(lst);

        }

in view:

<input type="button" id="Exporttopdf" value="Exporttopdf" />

<input type="button" id="ExporttoExcel" value="Export to Excel" />

<input type="button" id="ExporttoWord" value="Export to Word" />

onclick functions using Jquery:

<script>

    $(document).ready(function () {

        $("#Exporttopdf").click(function () {

            window.location = '@Url.Action("ExportGridToPDF","JqGrid")';

        });

        $("#ExporttoExcel").click(function () {

            window.location = '@Url.Action("ExportToExcel","JqGrid")';

        });

        $("#ExporttoWord").click(function () {

            window.location = '@Url.Action("ExportToWord","JqGrid")';

        });

        

    });

</script>

Methods when click on buttons to export in controller:

public void ExportToExcel()

        {

            if (Session["Export"] != null)

            {

                GridView gv = new GridView();

                gv.DataSource = Session["Export"];

                gv.DataBind();

                Response.ClearContent();

                Response.Buffer = true;

                Response.AddHeader("content-disposition", "attachment; filename=Kavitha.xls");

                Response.ContentType = "application/ms-excel";

                Response.ContentEncoding = System.Text.Encoding.UTF8;

                Response.BinaryWrite(System.Text.Encoding.UTF8.GetPreamble());

                Response.Charset = "";

                StringWriter sw = new StringWriter();

                HtmlTextWriter htw = new HtmlTextWriter(sw);

                gv.RenderControl(htw);

                Response.Output.Write(sw.ToString());

                Response.Flush();

                Response.End();

            }

        }

        public void ExportGridToPDF()

        {

            if (Session["Export"] != null)

            {

                GridView gv = new GridView();

                gv.DataSource = Session["Export"];

                gv.DataBind();

                Response.ContentType = "application/pdf";

                Response.AddHeader("content-disposition", "attachment;filename=Kavitha.pdf");

                Response.Cache.SetCacheability(HttpCacheability.NoCache);

                StringWriter sw = new StringWriter();

                HtmlTextWriter hw = new HtmlTextWriter(sw);

                gv.RenderControl(hw);

                StringReader sr = new StringReader(sw.ToString());

                Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);

                HTMLWorker htmlparser = new HTMLWorker(pdfDoc);

                PdfWriter.GetInstance(pdfDoc, Response.OutputStream);

                pdfDoc.Open();

                htmlparser.Parse(sr);

                pdfDoc.Close();

                Response.Write(pdfDoc);

                Response.End();

            }

public void ExportToWord()

        {

            if (Session["Export"] != null)

            {

                GridView gv = new GridView();

                gv.DataSource = Session["Export"];

                gv.DataBind();

                Response.Clear();

                Response.Buffer = true;

                Response.AddHeader("content-disposition",

                "attachment;filename=Kavitha.doc");

                Response.Charset = "";

                Response.ContentType = "application/vnd.ms-word ";

                StringWriter sw = new StringWriter();

                HtmlTextWriter hw = new HtmlTextWriter(sw);

                gv.RenderControl(hw);

                Response.Output.Write(sw.ToString());

                Response.Flush();

                Response.End();

            }

        }

Check Login session before every action hitting in MVC

Here is checking the session values every time when before hitting to every action.

  protected override void OnActionExecuting(ActionExecutingContext filterContext)

        {

            if (Session["User"] != null)

            {

                base.OnActionExecuting(filterContext);

            }

            else

            {

                TempData["TimeOut"] = "Please login again.";

                filterContext.Result = RedirectToAction("Index", "Home", new { area = "" });

            }

        }

Ajax and getJSon functions to call a method in MVC

Ajax and getJSON functions to call a method in mvc


Ajax function:

<script>

    $(document).ready(function () {

            $.ajax({

                url: '@Url.Action("test","JqGrid")', type: "POST", datatype: "JSON", data: { id: $("#txtMobile").val(), Name: $("#txtName").val() }, success: function (data) {

                    if (data != null) {

                        alert("success");

                        //$.each(data, function (sample, i) {

                        //});

                    }

                }

            });

           

        });

</script>

getJSON function

<script>

    $(document).ready(function () {

           

            $.getJSON('@Url.Action("test","JqGrid")', { id: $("#txtMobile").val(), Name: $("#txtName").val() }, function (data) {

                if (data != null) {

                    alert("success");

                }

            });

        });

</script>

Some Important Queries in SQL Server

Below are some difficult queries in SQL Server

SQL Main Queries using SQL Operators

Between Operation

Get records using between dates

SELECT *

FROM TableName

where   CAST(Date as Date) between '2013-01-22' and '2014-05-22' order by Date desc

Get Records using between Id

SELECT *

FROM TableName

where   ID between 24 and 55 order by id desc

IN Operator

Get records which are having that list of Ids

SELECT *

FROM TableName

where ID in(1,11,22,33,44)  order by id desc

Functions:

Count()

To get Count of Records in a table

SELECT COUNT(*) FROM table_name;

First Record from a table

SELECT TOP 1 CustomerName FROM Customers

ORDER BY CustomerID ASC;

Last record from A table

SELECT TOP 1 CustomerName FROM Customers

ORDER BY CustomerID DESC;

Duplicates are having Or not

select COUNT(no)as count,no from TableName group by No having COUNT(No)>1

Select Top N Records:

SELECT * FROM (

  SELECT

    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,

    columns

  FROM tablename

) AS foo

WHERE rownumber <= n

another

WITH RequireRows AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader
)

SELECT *
FROM RequireRows
WHERE RowNumber <= n;

Select nth Record

SELECT * FROM (

  SELECT

    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,

    columns

  FROM tablename

) AS foo

WHERE rownumber = n


another

WITH RequireRows AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
)  

SELECT * 
FROM RequireRows

WHERE RowNumber = 5;

Insert records into one table to another existing table:

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

Insert records from one table to another new table:

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

Function to search in function from C#


CREATE FUNCTION [dbo].[iter$simple_intlist_to_tbl] (@list nvarchar(MAX))  
   RETURNS @tbl TABLE (number varchar(50) NOT NULL) AS  
BEGIN  
   DECLARE @pos        int,  
           @nextpos    int,  
           @valuelen   int  
  
   SELECT @pos = 0, @nextpos = 1  
  
   WHILE @nextpos > 0  
   BEGIN  
      SELECT @nextpos = charindex(',', @list, @pos + 1)  
      SELECT @valuelen = CASE WHEN @nextpos > 0  
                              THEN @nextpos  
                              ELSE len(@list) + 1  
                         END - @pos - 1  
      INSERT @tbl (number)  
         VALUES (convert(varchar(50), substring(@list, @pos + 1, @valuelen)))  
      SELECT @pos = @nextpos  
   END  
  RETURN  
END

Important URLS

Some Important Site URLS


Table Sorter:

http://mottie.github.io/tablesorter/docs/index.html#Widget-options 


Captcha Generation in MVC

http://www.c-sharpcorner.com/UploadFile/bd8b0b/how-to-implement-captcha-in-Asp-Net-mvc/

Cascading Dropdowns List In MVC

http://www.c-sharpcorner.com/UploadFile/4d9083/creating-simple-cascading-dropdownlist-in-mvc-4-using-razor/

Crud operations using Dailog

http://www.dotnetcodesg.com/Article/UploadFile/2/286/CRUD%20operation%20using%20Modal%20Popup%20in%20ASP.NET%20MVC%204.aspx

WCF Service Example:

http://www.aspdotnet-suresh.com/2011/06/introduction-to-wcf-wcf-tutorial-wcf.html

Insert CSV type file to Sql server database

http://www.c-sharpcorner.com/uploadfile/0c1bb2/inserting-csv-file-records-into-sql-server-database-using-as/


Check Regular Expressions

http://regexr.com/

How to Disable Mouse Events and KeyBoard cliks functions using Jquery

Disable  Mouse Events and Button Clicks

<script src="@Url.Content("~/Scripts/jquery-1.7.1.min.js")" type="text/javascript"></script>

<script type='text/javascript'>

    $(function () {

        $(document).on({

            "contextmenu": function (e) {

                console.log("ctx menu button:", e.which);

                // Stop the context menu

                e.preventDefault();

            },

            "mousedown": function (e) {

                console.log("normal mouse down:", e.which);

            },

            "mouseup": function (e) {

                console.log("normal mouse up:", e.which);

            }

        });

        // Disable all keypress events for all elements

        $(document).keypress(function (e) { e.preventDefault(); return false; });

        $(document).keyup(function (e) { e.preventDefault(); return false; });

        $(document).keydown(function (e) { e.preventDefault(); return false; });

    });

</script>