ASP.NET Programmer

Accessing Session Variables from a Class

I’ve got a vb.net class file that needed to access the Session object to determine if the logged in user qualified for special product pricing. Session(“user_group”) spits back a nice nasty little message saying “Name ‘Session’ is not declared.” The compiler is right. Its not declared. Since we are working within the bounds of the class, there is no System.Web space by default like the Page object enjoys so to pull back the Session content we simply need to fully qualify where the compiler can find that Session object:

System.Web.HttpContext.Current.Session(“networkname”)

Tags: class, session, VB.NET

Create Excerpt from Description Blurb

So the task at hand is taking a verbose piece of description text and breaking it down to the first 300 characters. Not only are we going to trim this to 300 characters, but we are going to drop off all the text that follows the last period. This will serve as teaser copy on our store page to get our fair reader to click over to read the full description if interested. So our first step will be taking that large block and cutting it down to 300 characters:

SELECT LEFT(Description, 300) AS Excerpt FROM Products

This SQL query basically says we want the left 300 characters of the description field, and we are going to store it in a variable we call excerpt. Now comes the tricky part of dealing with the text after the period. I’ve created a console application in Visual Studio to help whittle down the results. The following sets up our initial query:

int i = 0; SqlConnection cn = new SqlConnection(@”Server=DBServer;DataBase=myDB;uid=[uid];pwd=[pwd];”); SqlConnection cn2 = new SqlConnection(@”Server=DBServer;DataBase=myDB;uid=[uid];pwd=[pwd];”); SqlDataReader dr; cn.Open(); SqlCommand cmd = new SqlCommand(“SELECT LEFT(Description, 300) AS Excert FROM Products”, cn); dr = cmd.ExecuteReader();

Now we need to loop through that results set so we can pair down this data.

while (dr.Read()) { string sExcerpt = dr["Excerpt"].ToString(); string sID = dr["ID"].ToString();

The program doesn’t need to worry about any descriptions that already end in a period or those ending as a url string so let’s skip them.

if (sExcerpt.EndsWith(“.”) | sExcerpt.EndsWith(“.com”) | sExcerpt.EndsWith(“.html”) | sExcerpt.EndsWith(“.htm”)) { Console.Write(i + “\n\n”); i++; }

Here’s where we identify where the last period in our content block resides.

else { int position = sExcerpt.LastIndexOf(‘.’); if (position != -1) { int forwardParenthesis = sExcerpt.LastIndexOf(‘(‘); int backwardParenthesis = sExcerpt.LastIndexOf(‘)’); if (sExcerpt.Substring(position + 1).StartsWith(“)”)) sExcerpt = Regex.Replace(sExcerpt, sExcerpt.Substring(position + 2), “”); else if ((forwardParenthesis > backwardParenthesis) | (forwardParenthesis > 1 & backwardParenthesis == -1)) sExcerpt = Regex.Replace(sExcerpt, sExcerpt.Substring(position + 1) + “)”, “”); else if (sExcerpt.Substring(position + 1).Contains(“)”) & !sExcerpt.Substring(position + 1).Contains(“(“)) sExcerpt = Regex.Replace(sExcerpt, “(” + sExcerpt.Substring(position + 1), “”); else sExcerpt = Regex.Replace(sExcerpt, sExcerpt.Substring(position + 1), “”); } else bAttention = true; Console.Write(sExcerpt + “\n\n”); } string sSQL = “INSERT INTO tbl_Excerpt (ID,Excerpt, Attention) VALUES (” + sID + “,’” + Regex.Replace(sExcerpt,”‘”,”””) + “‘,’” + bAttention + “‘)”; cn2.Open(); cmd = new SqlCommand(sSQL, cn2); cmd.ExecuteNonQuery(); cn2.Close(); } cn.Close(); Console.WriteLine(“Press ‘Enter’ to continue…”); Console.Read(); Tags: Add new tag, c#, SQL

Check Web Page Updates Through Screen Scrape

I’ve got a fair share of websites that draw data from a event promoter’s website. I’d originally done research on utilizing their web services model, but was severely disappointed with their implementation. That left me with two essential problems I needed to solve:

1) I needed to know when certain events were updating so I could keep my websites up to date.

2) I needed to be able to parse through the raw html and parse out the relevant data to generate an XML file that my GridView can pull to display the relevant data to my vistors.

The first task was to build a Console App that I could setup as a Scheduled Task to run nightly to tell me which pages had updated. So up Visual Studio goes. I’ve chosen not to hard code the page values so I can easily add additional pages to check in the future. I’ve stored these values in a text file called parsePages.txt as follows:

yahoo,http://www.yahoo.com google,http://www.google.com

These values could be just as easily stored in a database or an xml document. Next, I go about pulling this data into the program so I know which pages I need to parse through. I setup the regular expression to split up the values into an ArrayList from parsePages.txt so I can start making my checks.

Regex splitRx = new Regex(@”,\s*”, RegexOptions.Compiled); ArrayList al = new ArrayList(); using (StreamReader sr = new StreamReader(@”c:\scrape\parsePages.txt”)) { string line = null; int ln = 0; while ((line = sr.ReadLine()) != null) { string[] fields = splitRx.Split(line); if (fields.Length != 2) { Console.WriteLine(“Invalid Input on line:” + ln); continue; } ln++; al.Add(fields); } } Console.WriteLine(“\nI processed {0} lines:”, al.Count);

Now that I have my list of files to parse through, I’m going to loop through each — checking to see if the file exists then executing the parse routine.

//for loop to cycle through the pages and pass them to ScreenScrape foreach (string[] sa in al) { VerifyFileExists(sa[0]); ScreenScrape(sa[0],sa[1]); Console.WriteLine(“[{0}] [{1}]“, sa[0], sa[1]); }

Here is where we do our file verification. We are using two text files for each value — one to house our current content and one for the previous days content. This way we can see what changes, if any, have been made since the previous day. If for some reason either the main file or the alt file doesn’t exist, we want to create it. This is handy for new events we’ve just added to the list.

public static void VerifyFileExists(string reference) { //We need to verify that the main & alt file exist before we start comparing them //If they don’t, create them FileInfo ioMainFile = new FileInfo(@”c:\scrape\” + reference + “.txt”); if (!ioMainFile.Exists) { FileStream fs = ioMainFile.Create(); Console.WriteLine(“File Successfully Created : ” + reference + “.txt”); fs.Close(); } FileInfo ioAltFile = new FileInfo(@”c:\scrape\” + reference + “_alt.txt”); if (!ioAltFile.Exists) { FileStream fs = ioAltFile.Create(); Console.WriteLine(“File Successfully Created : ” + reference + “_alt.txt”); fs.Close(); } }

So far we’ve done a lot of work, but we still haven’t gotten to the meat of what we’re trying to do here. Well never fear because here comes the parse routine. So we pass in the reference and the url into the ScreenScrape method. We use the System.Net.WebRequest object to create our url and the WebResponse to instantiate it.

public static void ScreenScrape(string reference, string url) { string sCurrentPage = “”; System.Net.WebRequest request = System.Net.WebRequest.Create(url); // set properties of the request using (System.Net.WebResponse response = request.GetResponse()) { using (System.IO.StreamReader reader = new System.IO.StreamReader(response.GetResponseStream())) { sCurrentPage = reader.ReadToEnd();

We spit the html contents of the page into a StreamReader then do a little cleanup to strip off the header and footer html that we aren’t concerned with.

//this cleans out the header content of the file sCurrentPage = Regex.Replace(sCurrentPage,”<!DOCTYPE(.|\n)*?<td class=\”ttcc\”>”,”"); sCurrentPage = Regex.Replace(sCurrentPage,”<!DOCTYPE(.|\n)*?<div id=\”PLCenterBodyCSp\”>”,”"); //this cleans up the footer content of the file sCurrentPage = Regex.Replace(sCurrentPage, “<!–end PLCenterBodyCSp(.|\n)*?</html>”, “”);</pre></span>

Next, we need to compare the main text file with the alt text file to identify which one was written to most recently so we can overwrite the alternate file with the html we were chewing on in the StreamReader.

//find the older of the two files and write the new changes to it FileInfo ioMainFile = new FileInfo(@"c:\scrape\" + reference + ".txt"); FileInfo ioAltFile = new FileInfo(@"c:\scrape\" + reference + "_alt.txt"); if(ioMainFile.LastWriteTime < ioAltFile.LastWriteTime){ TextWriter tw = new StreamWriter(@"c:\scrape\" + reference + ".txt"); tw.WriteLine(sCurrentPage); tw.Close(); //Console.WriteLine("Processed Main File"); } else{ TextWriter tw = new StreamWriter(@"c:\scrape\" + reference + "_alt.txt"); tw.WriteLine(sCurrentPage); tw.Close(); //Console.WriteLine("Processed Alt File"); }

Now if we found any change between the old and the new file, we need to know about it. If the file lengths don't match, we write a line out to the console saying an update was found.

//if different print it out to the screen if (ioMainFile.Length != ioAltFile.Length) { Console.WriteLine("Update found : " + reference); //write out the xml from sCurrent Page for those who have updates and file size is larger than previous } Tags: ASP.NET, c#, HTML, page parsing, visual studio, XML

SQL IF…ELSE: CASE Statement

I was looking for a way to approach my SQL query that would work similar to a traditional if – then conditional in c#. So I came across SQL CASE statements, and they are exactly what I was looking for. I can do a test conditional based on one of the values passed back from the query to conditionally set a value. An example with multiple conditions of this follows:

SELECT ID, Status, Title, CASE WHEN Title=’ASP.NET Programming’ AND CurrentYearPricing IS NULL THEN LastYearPricing ELSE CurrentYearPricing END AS Pricing FROM db_Programming

Basically, we are saying that when the title of our book is equal to ‘ASP.NET Programming’ and there is no set price for the current year, use last year’s price. For everything else, just use this year’s price. We define this new custom variable as ‘Pricing’.

Reference Article: http://www.databasejournal.com/features/mssql/article.php/3288921/T-SQL-Programming-Part-5—Using-the-CASE-Function.htm

Tags: case statements, if else, SQL

Looking for NULL Values in SQL

After spending much too long trying to figure out why a SQL CASE statement wasn’t correctly evaluating the second condition in this statement:

CASE WHEN [column_name1]=’ASP.NET Programming’ AND [column_name] = NULL

I realize rather foolishly it is the way I am comparing the value to NULL. SQL is always IS NULL and not = NULL.

[column_name] IS NULL Tags: case statements, null, SQL

Storing State in AJAX/Javascript: Cookie Based

In searching for the best way to persist state in my AJAX application, I found writing to a cookie to be the best alternative. The following is an example on how to implement cookie based state management in javascript/AJAX:

var lndbid = getCookie(‘lndbid’); //setup these cookie values so we can store the lndbid selected when we go to find out why they are dropping the database function setCookie(c_name,value,expiredays) { var exdate=new Date(); exdate.setDate(exdate.getDate()+expiredays); document.cookie=c_name+ “=” +escape(value)+ ((expiredays==null) ? “” : “;expires=”+exdate.toGMTString()); } function getCookie(c_name) { if (document.cookie.length>0) { c_start=document.cookie.indexOf(c_name + “=”); if (c_start!=-1) { c_start=c_start + c_name.length+1; c_end=document.cookie.indexOf(“;”,c_start); if (c_end==-1) c_end=document.cookie.length; return unescape(document.cookie.substring(c_start,c_end)); } } return “”; }

Reference Article: http://www.w3schools.com/JS/js_cookies.asp

Tags: AJAX, cookies, javascript

Introduction to ASP.NET Programmer

ASP.NET Programmer is a site built to store programming code samples for daily challenges we come across that will no doubt surface over and over again as well as an avenue to feature our projects we’ve built and systems we manage to highlight our consulting services. Regardless, we hope to provide much value in an overall webspace that seems loaded down with fluff. Enjoy!