Tuesday 10 May 2016

Export data from sqlserver database to excel in asp.net c#

In this article we will learn how to export data directly from sql server database table to excel,
below is the database table which contain some data
I will export this data into excel file to excel by using C#.net coding as below:
1.Add a new file TabletoExcel.aspx in the solution explorer
Copy and paste below code in TabletoExcel.aspx page
01
02
03
04
05
06
07
08
09
10
11
12
13
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnGenaerateToExcel" runat="server" onclick="btnGenerateToExcel_Click" Text="Button" />
</div>
</form>
</body>
Copy and paste below code in TabletoExcel.cs  page:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
public partial class TableToExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnGenerateToExcel_Click(object sender, EventArgs e)
{
PopulatExcelEntry();
}
protected void PopulatExcelEntry()
{
SqlConnection Connection = new SqlConnection("Server=Munesh-PC;Database=Griddata;Uid=sa;Pwd=123");
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter("select * from Tbl_Mst_Employee", Connection);
try
{
adapter.Fill(ds);
ExportToExcel(ds);
}
catch (Exception ex)
{
Connection.Close();
}
}
public static void ExportToExcel(System.ComponentModel.MarshalByValueComponent DataSource)
{
try
{
System.IO.StringWriter objStringWriter = new System.IO.StringWriter();
System.Web.UI.WebControls.DataGrid tempDataGrid = new System.Web.UI.WebControls.DataGrid();
System.Web.UI.HtmlTextWriter objHtmlTextWriter = new System.Web.UI.HtmlTextWriter(objStringWriter);
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=TableToExcel.xls");
tempDataGrid.DataSource = DataSource;
tempDataGrid.DataBind();
tempDataGrid.HeaderStyle.Font.Bold = true;
tempDataGrid.RenderControl(objHtmlTextWriter);
DataSource.Dispose();
HttpContext.Current.Response.Write(objStringWriter.ToString());
HttpContext.Current.Response.End();
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
catch (Exception ex)
{
throw ex;
}
}
}
Run you application and click on button
Click on Ok and you will get your required output as:
at No comments:

Remove Assigned Users from Role in Asp.Net MVC


Before proceeding to this tutorial please go to  Assigned Role to user in Asp.Net MVC
In previous tutorial we learned how to assign role to user , here we will learn how to remove Role from assigned user.
For this purpose we need to add a action method in account controller class. In previous tutorial we created a model class name is “AllroleWithAllUser” . same class we will use here to pass this class in controller.
Following code is for “RemoveRoleForUser” in controller class.
  [HttpGet]
        public ActionResult RemoveRoleAddedToUser()
        {
            AssignRole objvm = new AssignRole();
            objvm.UserRolesList = GetAll_UserRoles();
            objvm.Userlist = GetAll_Users();
            return View(objvm);
        }

After creating action method for [HttpGet], now we need to add another action method for the [httpPost] .  So the following is code for the “RemoveRoleForUser” action method for[httpPost] method.
      [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult RemoveRoleAddedToUser(AssignRole _assignRole)
        {
            if (_assignRole.UserRoleName == "0")
            {
                ModelState.AddModelError("RoleName", "select proper RoleName");
            }
            if (_assignRole.UserID == "0")
            {
                ModelState.AddModelError("UserName", "select proper Username");
            }
            if (ModelState.IsValid)
            {
                int currentUserId = CheckUserWithUserRole(Convert.ToInt32(_assignRole.UserID));
                if (currentUserId == Convert.ToInt16(_assignRole.UserRoleName))
                {
                    var UserName = GetUserName_BY_UserID(Convert.ToInt32(_assignRole.UserID));
                    var UserRoleName = GetRoleNameByRoleID(Convert.ToInt32(_assignRole.UserRoleName));
                    Roles.RemoveUserFromRole(UserName, UserRoleName);
                    ViewBag.ResultMessage = "User Role is removed successfully !";
                }
                else
                {
                    ViewBag.ResultMessage = "This current user doesn't belong to selected user role.";
                }
                _assignRole.UserRolesList = GetAll_UserRoles();
                _assignRole.Userlist = GetAll_Users();
            }
            else
            {
                _assignRole.UserRolesList = GetAll_UserRoles();
                _assignRole.Userlist = GetAll_Users();
            }
            return View(_assignRole);
        }

Following is the code for the remove the role for the respective user.
Following is the code for checking that selected user is belong to the respective RolId.
  public int CheckUserWithUserRole(int UserId)
        {
            using (UsersRoleContext context = new UsersRoleContext())
            {
                int RoleId = context.webpages_UsersInRole.Where(c => c.UserId == UserId).Select(c => c.RoleId).SingleOrDefault();
                return RoleId;

            }
        }

Now right click on the” RemoveRoleAddedToUser” controller and add the view to this controller ,this view contain the scaffold template as “Create” model class and model class as “assignRole”. 

After click add write the following code to this view
@model MvcMembershipProvider.Models.AssignRole

@{
    ViewBag.Title = "RemoveRoleAddedToUser";
}

<h2>RemoveRoleAddedToUser</h2>
<link href="~/bootstrap/css/bootstrap.min.css" rel="stylesheet" />
<script src="~/bootstrap/js/bootstrap.min.js"></script>
@using (Html.BeginForm()) {
    @Html.AntiForgeryToken()
    @Html.ValidationSummary(true)

    <fieldset>
        <legend>AssignRole</legend>

        <div class="editor-label">
            @Html.LabelFor(model => model.UserRoleName)
        </div>
        <div class="editor-field">
            @*@Html.EditorFor(model => model.UserRoleName)*@
            @Html.DropDownListFor(m => m.UserRoleName, new SelectList(Model.UserRolesList, "Value", "Text"),
new { style = "width:200px", @class = "form-control" })
            @Html.ValidationMessageFor(model => model.UserRoleName)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.UserID)
        </div>
        <div class="editor-field">
            @*@Html.EditorFor(model => model.UserID)*@
            @Html.DropDownListFor(m => m.UserID, new SelectList(Model.Userlist, "Value", "Text"),
new { style = "width:200px", @class = "form-control" })
            @Html.ValidationMessageFor(model => model.UserID)
        </div>

        <p>
            <input type="submit" value="Remove User Role" />
        </p>
    </fieldset>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

Now run your application and go to the following URL
http://localhost:50526/Account/RemoveRoleAddedToUser

Now select user and role type from dropdown list and then check this work.


Authorize attribute in controller
We can authorize or we can give permission to the controller using attribute.
There is a attribute  [Authorize] to authorize. Following is the code for the give the permission code to the controller.
If you want to give permission to the “admin” to full controller , if user have the permission for admin then it will redirect to this page other wise it will redirect to login page
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcMembershipProvider.Controllers
{
[Authorize(Roles="Admin")]
public class Authonticate1Controller : Controller
{
public ActionResult Index()
{
return View();
}
}
}

If you want to give permission to particular user then use the following code. Here only admin can access then action method.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcMembershipProvider.Controllers
{
public class Authonticate1Controller : Controller
{
[Authorize(Roles="Admin")]

public ActionResult Index()
{
return View();
}
}
}

And the following code for the particular action method for the particular user
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcMembershipProvider.Controllers
{
public class Authonticate1Controller : Controller
{
[Authorize(Roles = "Admin", Users = "munesh")]
public ActionResult Index()
{
return View();
}
}
}

Download this project from this link Downlaod

at No comments:
Subscribe to: Posts (Atom)

C# program Selection Sorting

Selection sort is a straightforward sorting algorithm. This algorithm search for the smallest number in the elements array and then swap i...

  • SQL server database connection in Window Form
                                                                                                                                              ...
  • Interview Question on Asp.Net
                                                                                                                                             ...
  • Important SQL Query
                                                                                                                                             ...