HTML help/info control

Given below is code to show help information next to controls(like textbox, dropdownlist etc). This will show help info next to the control “onfocus” event. This feature can be attached to other events also like “onmouseover”,”onclick” etc.

The control will look like this - 

Set the script and style in html head

<script>

    var prevmsgControlName;

    var timeoutVar;

    function showControlMessage(control, msg) {

        var msgControlName = 'message_' + control.id;

        if (prevmsgControlName) {

            document.getElementById(prevmsgControlName).style.display = "none";

        }

        if (document.getElementById(msgControlName) == null) {

            var tempDiv = document.createElement("div");

            tempDiv.id = msgControlName;

            var rect = control.getBoundingClientRect();

            tempDiv.innerHTML = msg;

            tempDiv.className = "client_message";

            tempDiv.style.left = Number(rect.left + control.offsetWidth + 5) + "px";

            tempDiv.style.top = rect.top + "px";

            control.parentNode.appendChild(tempDiv);

            clearTimeout(timeoutVar);

            timeoutVar = setTimeout(hideControlMessage, 8000);

            prevmsgControlName = msgControlName;

        }

        else {

            clearTimeout(timeoutVar);

            document.getElementById(msgControlName).style.display = "block";

            timeoutVar = setTimeout(hideControlMessage, 8000);

            prevmsgControlName = msgControlName;

        }

    }

    function hideControlMessage() {

        clearTimeout(timeoutVar);

        document.getElementById(prevmsgControlName).style.display = "none";

    }

</script>

<style>

.client_message {

    border:1px solid black;

    background-color:rgb(250, 250, 188);

    padding:5px;

    position:absolute;

    font-size:90%;

    width: 300px;

    border-radius:2px;

}

</style>

Once the script and style are set in head, you can add help info to control as given below.

<input id="control1" type="text" onfocus="showControlMessage(this,'Replace with help info you want to show for the control')" />

How to use SOLR.NET to add/search documents from SOLR

In this article we will look into how to use SOLR to add and query data using  SOLR.NET

Install Apache Tomcat and  SOLR

1. Install Java version 6 or above

2.Download Apache Tomcat -Core: 32-bit/64-bit Windows Service Installer (pgp, md5) http://tomcat.apache.org/download-70.cgi

After you install apache tomcat you can check if service is running by checking the location -http://localhost:8080/

3.Download solr (zip) file http://www.gtlib.gatech.edu/pub/apache/lucene/solr/4.10.3/

Details on how to install and run SOLR is clearly stated in the below awesome articles

http://www.lawruk.com/blog/55/how-to-install-solr-on-windows-8

http://blog.navigationarts.com/installing-solr-4-6-on-windows/

Use SOLR.NET to connect to SOLR

So now that we have Apache and SOLR running let’s look at how to use this in ASP.NET(C#)

1.Download SOLR.NET - https://github.com/mausch/SolrNet

2.Compile the project and get the dlls from SOLRNET project (there will be SolrNet.dll, Microsoft.Practices.ServiceLocation.dll, HttpWebAdapters.dll and the pdb and xml files)

3.Create an empty ASP.NET project

4.Create a new folder in this project and copy the dlls from SOLRNET project (step 2) to that folder.

5.Add references to the 3 dlls

6.Add Global.asax and initialize SOLR.NET connection in application start

  protected void Application_Start(object sender, EventArgs e)

        {

            Startup.Init<SOLRPOST.PostData.SolrSearchResult>("http://localhost:8080/solr");

        }

7.Create a class which has the same fields as the schema in SOLR. For testing purpose I added only 2 fields from schema

public class SolrSearchResult

        {

            [SolrField("id")]

            public string id { get; set; }

            [SolrField("title")]

            public string title { get; set; }

        }

8.Now we are ready to add documents to SOLR! Given below is the function to add documents to SOLR

public void AddData()

        {

            ISolrOperations<SolrSearchResult> solr = ServiceLocator.Current.GetInstance<ISolrOperations<SolrSearchResult>>();

            SolrSearchResult test = new SolrSearchResult() { id = "changeme2", title = "changeme2" };

            solr.Add(test);

            solr.Commit();

        }

9.To search data use the below function

public void SearchData()

        {

            ISolrOperations<SolrSearchResult> solr = ServiceLocator.Current.GetInstance<ISolrOperations<SolrSearchResult>>();

            SolrQueryResults<SolrSearchResult> results = solr.Query(new SolrQuery("id:\"changeme2\""));

            foreach (SolrSearchResult result in results)

            {

                Response.Write(result.title);

            }

        }

Group and pivot data in SQL

Given below is an example of how to group and pivot data in SQL .This example shows you how to group data in CSV format and pivot it.

--Create temp table

CREATE TABLE  #car_data (company varchar(10), model varchar(20))

--Insert data

INSERT INTO #car_data (company,model) values ('Ford','Escape')

INSERT INTO #car_data (company,model) values ('Ford','Explorer')

INSERT INTO #car_data (company,model) values ('Ford','Expedition')

INSERT INTO #car_data (company,model) values ('Chevy','Cruze')

INSERT INTO #car_data (company,model) values ('Chevy','Impala')

INSERT INTO #car_data (company,model) values ('Chevy','Silverado')

-- Group data in CSV format

SELECT  t.company

    ,STUFF((SELECT ', ' + model

        FROM #car_data with(nolock)

        WHERE company = t.company

        FOR XML PATH(''), TYPE)

    .value('.','VARCHAR(400)'),1,2,' ') List_Output

FROM #car_data t with(nolock)

GROUP BY t.company 

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!

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"]);

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

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"]);

            }