Thursday, September 11, 2014

Add Excel type Filtering to Repeater or Gridview in ASP.NET

In this example we will add dropdown list filter to repeater. The filters will be created dynamically at runtime.

1) Create a repeater control
<asp:Repeater runat="server" ID="rptData" OnItemCreated="rptData_ItemCreated" EnableViewState="false">
        <HeaderTemplate>
            <table>
                <tr>
                    <td>Department</td>
                    <td>Name</td>
                    <td>Age</td>
                </tr>
                <tr>
                    <td id="Department" runat="server" ></td>
                    <td id="Name" runat="server" ></td>
                    <td id="Age" runat="server"></td>
                </tr>
        </HeaderTemplate>
        <ItemTemplate>
            <tr>
                <td><%# Eval("Department") %></td>
                <td><%# Eval("Name") %></td>
                <td><%# Eval("Age") %></td>
            </tr>
        </ItemTemplate>
        <FooterTemplate>
            </table>
        </FooterTemplate>
    </asp:Repeater>

2) Create an Employee class
[Serializable]
        public class Employee
        {
            public string Name { get; set; }
            public int Age { get; set; }
            public string Department { get; set; }
            public Employee() { }

            public static List<Employee> GetEmployees()
            {
                List<Employee> employees = new List<Employee>();
                employees.Add(new Employee() { Name = "Victor", Age = 31, Department = "Finance" });
                employees.Add(new Employee() { Name = "Jeff", Age = 35, Department = "Finance" });
                employees.Add(new Employee() { Name = "Tom", Age = 42, Department = "IT" });
                employees.Add(new Employee() { Name = "Matt", Age = 42, Department = "IT" });
                return employees;
            }
        }
3) On page load bind data to the repeater
protected void Page_Load(object sender, EventArgs e)
        {
            rptData.DataSource = Employee.GetEmployees();
            rptData.DataBind();
        }

OK now we will start the code to add filtering to the repeater.

4) Add “ItemCreated” Event
protected void rptData_ItemCreated(object sender, RepeaterItemEventArgs e)
        {
            //Check if item is header
            if (e.Item.ItemType == ListItemType.Header)
            {
                //Get employee data
                List<Employee> tempData = ViewState["filtered_data"] == null?Employee.GetEmployees():(List<Employee>)ViewState["filtered_data"] ;
                //Get properties of employee data. We will be adding drop down list filters for these properties
                foreach (PropertyInfo propertyInfo in typeof(Employee).GetProperties())
                {
                    if (e.Item.FindControl(propertyInfo.Name) != null)
                    {
                        //Create dropdownlist
                        DropDownList ddltemp = new DropDownList();
                        ddltemp.Items.Add(new ListItem("Select", string.Empty));

                        //Get value of the selected property from the list
                        foreach (string value in (from x in tempData select propertyInfo.GetValue(x, null).ToString()).Distinct().ToList())
                        {
                            ddltemp.Items.Add(new ListItem(value, value));
                        }
                        ddltemp.Attributes.Add("style", "width:70px;");
                        ddltemp.ID = "ddl" + propertyInfo.Name;
                        //Set autopost back
                        ddltemp.AutoPostBack = true;
                        //Add event handler
                        ddltemp.SelectedIndexChanged += new EventHandler(ddlTemp_SelectedIndexChanged);
                        //Add dropdownlist to the repeater header
                        e.Item.FindControl(propertyInfo.Name).Controls.Add(ddltemp);
                    }
                }
            }
        }

5) Add dropdownlist post back event to filter data

  protected void ddlTemp_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Get dropdownlist object that send the request
            DropDownList ddlTemp = (DropDownList)sender;
            List<Employee> tempData = ViewState["filtered_data"] == null ? Employee.GetEmployees() : (List<Employee>)ViewState["filtered_data"];

            //Filter the list based on the selected value of the property
            List<Employee> temp = (from x in tempData
                                           where x.GetType().GetProperties().Where(y => y.Name == ddlTemp.ID.Replace("ddl", "")
                                                                                                   && y.GetValue(x, null).ToString() == ddlTemp.SelectedItem.Text).Count() > 0
                                           select x).ToList();

            ViewState["filtered_data"] = temp;
            rptData.DataSource = temp;
            rptData.DataBind();
        }

That’s it! Run the application and you can filter records from header of the repeater!



Wednesday, August 6, 2014

Create AJAX dropdownlist (select) using jQuery



Given below is an example of AJAX drop down list which fills data by making an AJAX call and also persists data on post back.

1) Create an AJAX handler

public class AjaxHandler : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            if (HttpContext.Current.Request.QueryString["getDDLData"] == "1")
            {
                List<DDLData> values = new List<DDLData>();

                values.Add(new DDLData() { Text = "Option1", Value = "1" });
                values.Add(new DDLData() { Text = "Option2", Value = "2" });
                values.Add(new DDLData() { Text = "Option3", Value = "3" });

                JavaScriptSerializer serializer = new JavaScriptSerializer();
                string result = serializer.Serialize(values);

                context.Response.ContentType = "text/json";
                context.Response.Write(result.ToString());
            }
           
        }
        public class DDLData { public string Text; public string Value; }
        public bool IsReusable
        {
            get { return true; }
        }
    }

Make sure that you declare AJAX handler information in web.config. To learn about AJAX call check the AJAX call related articles this blog
  <system.webServer>
     <modules runAllManagedModulesForAllRequests="true"/>
    <handlers>
      <add name="ajaxHandler" verb="GET,POST" path="RequestHandler.aspx" type="AJAXAsptest.AjaxHandler" />
    </handlers>
  </system.webServer>

 2) Create a “Select” element in HTML

    <select name="ddlData" id="ddlData">
            <option></option>
</select> 

 3) Make AJAX call to the handler and fill the dropdown list

<script src="//code.jquery.com/jquery-1.11.0.min.js"></script>
    <script>
        var selectedValue = '<%= Request.Form["ddlData"]!=null?Request.Form["ddlData"]:""%>';
        $.ajax({ url: "RequestHandler.aspx?getDDLData=1",
            success: function (data) {//Get result object from server
                var elementRef = $("#ddlData");
                elementRef.append(new Option("Select", "Select", false, true));
                $.each(data, function () {
                    elementRef.append(new Option(this.Text, this.Value));
                });
                if (selectedValue != '') {
                    elementRef.val(selectedValue);
                }
            }
        });
    </script>

 4) On server side you can get the selected value of dropdown from the “form” data

Response.Write(Request.Form["ddlData"]);




Monday, June 30, 2014

SQL Server – Convert results from query to string and send email from DB

Given below is an example of how to convert data from SQL server into a string and send the results in an email from database. This can be used to send scheduled reports. Also there is no need to create an executable in c# or any other language to send email reports in the below example.

  
/*Create temp table to store data.*/
DECLARE @temp TABLE (emp_id int,emp_name varchar(10))

INSERT INTO @temp (emp_id,emp_name) VALUES(1,'John')
INSERT INTO @temp (emp_id,emp_name) VALUES(2,'Tom')

DECLARE @result VARCHAR(4000)

/*Get results into a string*/
SELECT @result =
       (
       SELECT convert(varchar, emp_id)+','+emp_name+'\n'
       FROM @temp
       FOR XML PATH('')
       )

SELECT @result

/*Send the report to recipients*/
EXEC msdb..sp_send_dbmail @profile_name='TEST',
@recipients='someone@somewhere.com',
@subject='DB Job',
@body_format = 'HTML',

@body= @result

Thursday, May 22, 2014

ASP.NET URL Routing


 For SEO purposes it might be better to do URL routing for public facing asp.net applications. This will make the URLs more readable and parameters can be separated by “/” instead of “?” and “&”.

If you are using ASP.NET 4.0 and IIS 7 or higher it is easy to do URL routing in asp.net application. You have to do only 2 steps to setup URL routing

       1) Create a function to register routes in global.asax

void RegisterRoutes(RouteCollection routes)
        {
            routes.Ignore("{resource}.aspx/{*pathInfo}");

            routes.MapPageRoute(
               "",      // Route name
               "Home",      // Route URL
               "~/Default.aspx" // Web page to handle route
            );
        }
2) Invoke the function from Application_Start method

 void Application_Start(object sender, EventArgs e)
        {
            // Code that runs on application startup
            RegisterRoutes(RouteTable.Routes);
        }

Now any request to http://webiste/Home will be redirected to default.aspx page

Now how to pass parameters? You can add parameters to the route URL as given below

routes.MapPageRoute(
               "About",      // Route name
               "AboutTest/{controller}/{action}/{id}"// Route URL
               "~/About.aspx", // Web page to handle route
            true,
               new RouteValueDictionary { { "controller", "food" }, { "action", "show" },{"id","myid"} });

The parameters can be accessed from the page using “Page.RouteData.Values”

if (Page.RouteData.Values["controller"] != null)
            {
                Response.Write(Page.RouteData.Values["controller"]);
            }
            if (Page.RouteData.Values["action"] != null)
            {
                Response.Write(Page.RouteData.Values["action"]);
            }


Wednesday, May 21, 2014

C# Sort generic list by column name


Data in DataTable can be sorted using string column name

DataView dv = new DataView(ds.Tables[0], "","SortExpression" + " " + "SortDirection", DataViewRowState.CurrentRows);

For list we cannot pass sort expression and direction in string format. We have to find the column using reflection and then sort the column as given below

PropertyInfo pi = typeof(Employee).GetProperty("[ColumnName]");
lstData.OrderBy(i => pi.GetValue(i, null)).ToList();


Given below is an example in ASP.NET
-------------------------------------------------------------
HTML (Add inside ‘form’ tags)
    <asp:DropDownList runat="server" ID="ddlSortColumn"></asp:DropDownList>
    <asp:DropDownList runat="server" ID="ddlSortDirection">
        <asp:ListItem Text="asc"></asp:ListItem>
        <asp:ListItem Text="desc"></asp:ListItem>
    </asp:DropDownList>
    <asp:Button runat="server" ID="btnReport" Text="Report"
        onclick="btnReport_Click" />
    <br />
    <asp:GridView runat="server" ID="grvData"></asp:GridView>

C# (CodeBehind)
protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                IList<PropertyInfo> props = new List<PropertyInfo>(typeof(Employee).GetProperties());
                ddlSortColumn.DataSource = props;
                ddlSortColumn.DataTextField = "Name";
                ddlSortColumn.DataValueField = "Name";
                ddlSortColumn.DataBind();

                grvData.DataSource = GetData();
                grvData.DataBind();
            }
        }
        protected void btnReport_Click(object sender, EventArgs e)
        {
            PropertyInfo pi = typeof(Employee).GetProperty(ddlSortColumn.SelectedValue);
            if (ddlSortDirection.SelectedItem.Text == "asc")
            {
                grvData.DataSource = GetData().OrderBy(i => pi.GetValue(i, null)).ToList();
            }
            else
            {
                grvData.DataSource = GetData().OrderByDescending(i => pi.GetValue(i, null)).ToList();
            }
            grvData.DataBind();
        }
        public List<Employee> GetData()
        {
            List<Employee> employees = new List<Employee>();
            employees.Add(new Employee() { Id = 1, Name = "Employee 1", Age = 25 });
            employees.Add(new Employee() { Id = 2, Name = "Employee 2", Age = 35 });
            employees.Add(new Employee() { Id = 3, Name = "Employee 3", Age = 39 });
            return employees;
        }
        public class Employee
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public int Age { get; set; }
        }


Wednesday, February 26, 2014

How to return error messages in AJAX calls

In AJAX calls exception can occur mainly due to 1) communication error 2) server error. We will look at how to handle these exceptions

Given below is a simple AJAX application – both client code and server handler

JS Code
  <script>

        $.ajax({ url: "NewRequestHandler.aspx?val1=10&val2=11",
            success: function (data) {//Get result object from server
                alert(data.Result);
                if (data.Error!= null) {
                    alert(data.Error); //Get error returned by server
                }
            },
error: function (xhr, errorType, exception) { //Triggered by communication error, like http 404  etc
                alert(xhr.responseText);
                return false;
            }
        });

    </script>

Server Code
public class NewRequestHandler : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            //this object will be returned in the response
            NewData data = new NewData();
            try
            {
                int val1 = Int32.Parse(context.Request.QueryString["val1"]);
                int val2 = Int32.Parse(context.Request.QueryString["val2"]);
                data.Result = val1 + val2;
            }
            catch (Exception ex)
            {
                //capture any exceptions and add it to the response object
                data.Error = ex.Message;
            }
            //convert the object to JSON and return
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            context.Response.ContentType = "text/json";
            context.Response.Write(serializer.Serialize(data));
        }
        public bool IsReusable
        {
            get { return true; }
        }
        public class NewData
        {
            public int Result;
            public string Error;
        }
    }

Any error in communicating with the server will be captured by the below line of code in JS

error: function (xhr, errorType, exception) { //Triggered by communication error, like http 404  etc

Errors occurring in server is captured by the C# and returned in the JSON object. JS code can check for this ‘error’ property to display exceptions from server

if (data.Error!= null) {
                    alert(data.Error); //Get error returned by server
                }