SQL: Select rows from Comma Separated IDs

It's not a very common scenario when IDs are present as Comma Separated data and you have to select all rows against those IDs.

For example, if Addresses is a table and its primary key is u_address_id
There is another table OrganizationObject, which would store details of Organizations. The organizations' addresses are stored in Addresses table.

Ideally the Addresses table should contain a column to store primary key of OrganizationObject. Unfortunately, if you don't have that column, the data is stored in reverse order, where all the addresses's IDs for a particular organization are stored in CSV format in OrganizationObject row itself and you don't even have the privilege of changing the structure, here's a solution.

select ad.u_country_name as Country, aid.u_Name as ProviderName, aid.u_organization_type as ProviderType  from (
SELECT 
 F1.u_Name,
F1.u_organization_type,
O.u_address_id
FROM
(
SELECT *,
cast(''+replace(F.u_addresses,';','')+'' as XML) as xmlfilter from OrganizationObject F
where F.u_organization_type in ('2', '3')
)F1
CROSS APPLY
( 
 SELECT AData.A.value('.','varchar(50)') as u_address_id 
 FROM f1.xmlfilter.nodes('X') as AData(A)) O) aid --Address IDs
join
      Addresses ad
on 
      aid.u_address_id = ad.u_address_id  
where 
      ISNULL(NULLIF(u_country_name, ''), NULL) is not null 
      and ad.u_st_address_type = 2
order by u_country_name

Sql CE & LINQ with SQL Metal

To start programming with LINQ against SQL CE 3.5, as if a table in Sql CE is a class, you can make use of SQL Metal.

This post is a good guide to start off with.


Use the below command to generate the cs and dbml file.

Generate DBML file

C:\Program Files\Microsoft Visual Studio 9.0\VC>sqlmetal /dbml:J:\Sid\WinApps\Auto-motive\Auto-motive\AutoDB.dbml J:\Sid\WinApps\Auto-motive\Auto-motive\AutoDB.sdf
Microsoft (R) Database Mapping Generator 2008 version 1.00.30729
for Microsoft (R) .NET Framework version 3.5
Copyright (C) Microsoft Corporation. All rights reserved.

Generate CS file

C:\Program Files\Microsoft Visual Studio 9.0\VC>sqlmetal /Code:J:\Sid\WinApp
s\Auto-motive\Auto-motive\AutoDB.cs J:\Sid\WinApps\Auto-motive\Auto-motive\AutoDB.sdf
Microsoft (R) Database Mapping Generator 2008 version 1.00.30729
for Microsoft (R) .NET Framework version 3.5
Copyright (C) Microsoft Corporation. All rights reserved.

Getting QueryString parameters from ReWritten URL in ASP.Net

After a URL is rewritten to merge querystring into the actual URL, to get the querystring params, you should look towards Request's RawUrl property.

Use this code to extract QueryString params from the raw url:

Uri theRealURL = new Uri(HttpContext.Current.Request.Url.Scheme + "://" + HttpContext.Current.Request.Url.Authority + HttpContext.Current.Request.RawUrl);  
string utm_source = HttpUtility.ParseQueryString(theRealURL.Query).Get("utm_source");  

Actual code from this blog

ASP.Net - Redirecting with URL Rewriter

To redirect one URL to another, use this rule inside "rewriter" section:

Adding new row to DataGridView programatically in C#

This article would be helpful if you are looking to add rows programatically to DataGridView and keeping key values invisible to user.

Heres a simple relation between ASP.Net & WinForms.

ASP.Net <--> WinForms
GridView <--> DataGridView
DataKeyNames(Considering 1 column is stored) <---> DataGridViewRow.Tag(Tag can store literally any value, but here it can solve our problem)

With the below code you have control on what you display to users while you still keep (one)important detail invisible.
Only one solution, to lengths of my knowledge, will help in this situation. Using DataGridViewRow.

List custList = GetAllCustomers();
            dataGridView1.Rows.Clear();

            foreach (Customer cust in custList)
            {
                //First add the row, cos this is how it works! Dont know why!
                DataGridViewRow R = dataGridView1.Rows[dataGridView1.Rows.Add()];
                //Then edit it
                R.Cells["Name"].Value = cust.Name;
                R.Cells["Address"].Value = cust.Address;
                R.Cells["Phone"].Value = cust.PhoneNo;
                //Customer Id is invisible but still usable, like, 
                //when double clicked to show full details
                R.Tag = cust.IntCustomerId;
            }

Add Site-Search functionality with JRank & RESTful web service - ASP.Net

If you are looking to add Site-Search functionality to your web site, and if you dont want Google ads in your search results page, JRank comes to rescue.
Starting off with JRank is quite an easy task. Just 3 easy steps to complete.

• Join JRank
• Create a context. Contexts are a way to tell JRank where to search in our site, the crawl frequency and for us to know how many pages are crawled/indexed, last crawled date and other stuff.
• Integrate search into your site. Explained below

Complete 1st and 2nd steps. The 3rd step has 3 tasks in it again.

• Add a search box
• Post Search Query & Get results from JRank
• Parse & Display

Adding a search box

I suppose, search button should exist on each page that a user can access. For that purpose, I have put search box on master page. So when user searches, search data will be passed to SiteSearch.aspx.

Add SiteSearch.aspx page to your web site. This page will receive search strings from other pages in your site. Also add a Literal control to this page to display the results.
Use plain old way of passing data from one page to other.

You can also use GET or POST methods but to keep it simple, I've used post back.
In the code behind, add this code.

protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        if (string.IsNullOrEmpty(sb1.Text.Trim()))
        {
            return;
        }
        Response.Redirect("searchresults.aspx?q=" + sb1.Text);
    }

Post Search Query & Get results from JRank

In SiteSearch.aspx page, get the search key words from Query string and pass it to JRank to do the search.

protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            if (string.IsNullOrEmpty(Request.QueryString["q"].ToString().Trim()))
            {
                Response.Redirect("index.aspx");
            }
            else
            {
                search(Request.QueryString["q"].ToString().Trim());
            }
        }
    }

Parse & Display
Once JRank is called to search, it will return an XML of result that contains page details, content preview and other stuff.
I have used RESTful API as I will have more control over end result than the HTML response of JRank.
Parse it and display what you want.

private void search(string q)
    {
        string jrankURL = string.Format("http://www.jrank.org/api/search/v2.xml?key={0}&q={1}", "your-jrank-api-key", q);
        System.Net.WebClient serviceRequest = new System.Net.WebClient();
        string response = serviceRequest.DownloadString(new Uri(jrankURL));
        System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
        doc.LoadXml(response);
        XmlNode root = doc.DocumentElement;
        XmlNodeList nodes = root.SelectNodes("/serp/entries/entry");

        StringBuilder sb = new StringBuilder();
        sb.Append("Your search for ").Append(q).Append(" has ").Append(nodes.Count.ToString()).Append(" results.

");
        sb.Append("
");         foreach (XmlNode node in nodes)         {             sb.Append("
");

string url = node["url"].InnerText;

string title = node["title"].InnerText; 

string content = node["content"].InnerText;

sb.Append("").Append(title).Append("").Append(content).Append("");

sb.Append("
");          }          sb.Append("
");
        ltSearchResults.Text = sb.ToString();
    }

Try understanding the code and tweak some to get the results you need.
Happy coding!

Problem repeating a background image on y axis in HTML?

Often we come across a situation where a div height will change across pages and a lot divs in those pages use the same CSS rule.

If you want to repeat the background image on y-axis and you want to change the height of the div dynamically where the background image has to repeat itself, you can do it using JavaScript. And now that you came here, it is a real simple job.

Theoretically what you are going to do is,
1.Assign the div, whose height you are looking to change dynamically, an ID.
2.When the document is loaded, get the height occupied by that div
3.Assign that height to the CSS rule where background repeat is mentioned.
As simple as that! Heres the working code.

<head>
    <style type="text/css">
        #content
        {
            width: 450px;
            background-image: url(images/line_v.gif);
            background-repeat: repeat-y;
            background-position: right center;
            min-height: 10px;
            float: left;
        }
    </style>
</head>
<body>
    <div id="content>
        Lorem ipsum dolor sit amet, consectetur adipiscing elit. Aliquam semper turpis in
        tortor interdum sed mattis lectus mollis. Pellentesque laoreet quam et mauris feugiat
        sit amet eleifend turpis imperdiet. Praesent condimentum libero vitae massa imperdiet
        pellentesque. Vestibulum et risus dolor, ac tincidunt velit. Maecenas lobortis diam
        tellus. Suspendisse auctor, velit ut imperdiet semper, erat orci pharetra tortor,
        venenatis vulputate metus eros ut felis. Cras ullamcorper odio sed felis ornare
        ultricies. Phasellus porta adipiscing aliquet. Aenean ligula tellus, blandit a pulvinar
        in, fermentum a erat. Sed arcu nisl, porta eu vestibulum at, luctus sed tellus.
        Aenean eget quam justo, quis ultricies ante. Vivamus tempor sollicitudin augue ut
        rhoncus. Sed odio mauris, imperdiet ut elementum vitae, dapibus at quam. Nullam
        in aliquam sem. Fusce consectetur laoreet elit, non consequat nunc tempus nec. Cras
        at enim ipsum. Praesent ipsum purus, tempus non sagittis volutpat, fringilla eget
        mi. Maecenas sed elit est. Morbi facilisis purus eget nunc tincidunt iaculis mattis
        quam pellentesque. Sed tortor leo, laoreet sit amet mattis a, fermentum in ligula.
        Sed tempor lacus a lorem fermentum fermentum semper nunc sagittis. Vivamus et est
        porta purus mollis cursus eget in ante. Sed lacinia, diam in tempor suscipit, turpis
        enim posuere turpis, sit amet vestibulum quam nulla id elit. Vivamus ultricies tempus
        mi, non consequat velit mollis id. Curabitur vehicula, turpis id tempor condimentum,
        sem nulla molestie enim, vel commodo metus ipsum non nisi. Aliquam erat volutpat.

    </div>

    <script type="text/javascript">

        var divHeight;
        var textobj = document.getElementById("content");

        if (textobj.offsetHeight) { divHeight = textobj.offsetHeight;}
        else if (textobj.style.pixelHeight) { divHeight = textobj.offsetHeight; }

        changecss('#content', 'min-height', divHeight + "px");

        function changecss(theClass, element, value) {
            var cssRules;

            var added = false;
            for (var S = 0; S < document.styleSheets.length; S++) {

                if (document.styleSheets[S]['rules']) {
                    cssRules = 'rules';
                } else if (document.styleSheets[S]['cssRules']) {
                    cssRules = 'cssRules';
                } else {
                    //no rules found... browser unknown
                }

                for (var R = 0; R < document.styleSheets[S][cssRules].length; R++) {
                    if (document.styleSheets[S][cssRules][R].selectorText == theClass) {
                        if (document.styleSheets[S][cssRules][R].style[element]) {
                            document.styleSheets[S][cssRules][R].style[element] = value;
                            added = true;
                            break;
                        }
                    }
                }
                if (!added) {
                    try {
                        document.styleSheets[S].insertRule(theClass + ' { ' + element + ': ' + value + '; }', document.styleSheets[S][cssRules].length);

                    } catch (err) {
                        try { document.styleSheets[S].addRule(theClass, element + ': ' + value + ';'); } catch (err) { }

                    }
                }
            }
        }
    </script>

</body>
</html>

Here's the line_v.gif, . Place it in images folder and give it a go to see how it works.

Make sure the javascript is at the end of document. Else it would would throw "object required" error.

Happy coding!