Flat file Database with LINQ and ASP.NET jQuery Page Methods

How to create a flat file delimited database of quotations and implement LINQ plus jQuery access to ASP.NET PageMethods.

I've been keeping a database of famous (and not so famous) quotations for the last five years or so. I always add to it whenever I can. Currently I"ve got over 44,000 quotations in it. I also use it as a sample database for various articles that I've written.

This time around, I decided to take a different tack. Instead of having a "normal" database with Author, Subject and Quotation tables, I decided to export everything into a flat file with AuthorFirstName, AuthorLastName, AuthorInfo, Subject and QuotationText fields. The idea was to load and parse this file into a List of Quotation objects and be able to use LINQ to query it from memory.

In this sample I'll provide you with a flat file containing the first 3,000 quotations. A typical line looks like this:

17|Clare  Boothe|Luce|1903-1987, American Author|But if God had wanted us to think just with our wombs, why did He give us a brain?|Women

Each line has an ID, Author first and last name, author info, the quotation text, and a subject, in that order. As opposed to a normalized schema which would have an Author, a Subject, and a Quotation table with foreign key fields from Author and Subject pointing to Quotation, everything here is denormalized and flattened out. This makes the text file a little bigger, but in the Quotations library code I'll show shortly, it only ever needs to be loaded one time.

Now I need representative classes to model my domain, because I'm going to be doing all my querying via LINQ:

public   class Author :IEquatable<Author>
    {

     public string FirstName { get; set; }
     public string LastName { get; set; }
     public string AuthorInfo { get; set; }
     public string FullName { get; set; }

     public Author( string firstName, string lastName, string authorInfo)
     {
         this.FirstName = firstName;
         this.LastName = lastName;
         this.FullName = firstName + " " + lastName;
         this.AuthorInfo = authorInfo;
     }

     public Author()
     {
     }


     public bool Equals(Author other)
     {
         if (FirstName == other.FirstName && LastName == other.LastName) return true;
         return false;
     }

     public override int GetHashCode()
     {
         int hashFirstName = FirstName == null ? 0 : FirstName.GetHashCode();
         int hashLastName = LastName == null ? 0 : LastName.GetHashCode();
         return hashFirstName ^ hashLastName;
     }
    }

Note that my Author class implements IEquatable, with it's Equals method, and overriding the GetHashCode method. This is done so that comparisons for query operators such as Distinct will get correct results. The LINQ Distinct operator by itself is just not that smart - you need to help it out.

   public class Subject : IEquatable<Subject>
    {
     public string SubjectText { get; set; }


     public bool Equals(Subject other)
     {
         if (SubjectText == other.SubjectText ) return true;
         return false;
     }

     public override int GetHashCode()
     {
         int hash = SubjectText == null ? 0 : SubjectText.GetHashCode();
         return hash;
        
     }
    }

   Finally, the Quotation class, which is a little more complex:

    [Serializable]
   public class Quotation
    {
         public int Id { get; set;}
        public string AuthorFirstName { get; set;}
         public string AuthorLastName { get; set;}
         public string AuthorInfo { get; set;}
         public string QuotationText { get; set;}
         public string Subject { get; set; }

         public Quotation()
        {
         }

         public Quotation( int id, string authorFirstName, string authorLastName, string authorInfo, string quotation, string subject)
         {
             this.Id = id;
             this.AuthorFirstName = authorFirstName;
             this.AuthorLastName = authorLastName;
             this.AuthorInfo = authorInfo;
             this.QuotationText = quotation;
             this.Subject = subject;


         }

        public bool AddQuotation( string authorFirstName, string authorLastName, string authorInfo, string quotation, string subject )
        {
            bool OK = true;
            Quotation q = new Quotation
                              {
                                  AuthorFirstName = authorFirstName,
                                  AuthorLastName = authorLastName,
                                  AuthorInfo = authorInfo,
                                  QuotationText = quotation,
                                  Subject = subject
                              };
            int maxId = Quotations.MaxId;
            q.Id = maxId + 1;
            Quotations.MaxId = q.Id;
             System.IO.File.AppendAllText(ConfigurationSettings.AppSettings["quotesFilePath"], q.ToString());

             return OK;

        }

        public override string ToString()
        {
            return string.Format(CultureInfo.InvariantCulture,
                 "{0}|{1}|{2}|{3}|{4}|{5}",
                Id,
                AuthorFirstName ?? string.Empty,
                AuthorLastName ?? string.Empty,
                AuthorInfo?? String.Empty,QuotationText, Subject?? String.Empty );
        }

    }
}

This is all tied together with a class called Quotations (plural) that contains all the LINQ logic:

public static class Quotations
    {
         private static string quotesFilePath = ConfigurationSettings.AppSettings["quotesFilePath"];
        private static readonly List<Quotation> Quotes = new List<Quotation>();
        public static int MaxId;
        private static readonly object _lock = new object();

        static Quotations()
        {
             LoadQuotes();
        }

         private static void LoadQuotes()
        {
             if (quotesFilePath == null && System.Web.HttpContext.Current == null)
                quotesFilePath = System.Environment.CurrentDirectory + @"\quotes.txt";
            else if (quotesFilePath == null && System.Web.HttpContext.Current != null)
                quotesFilePath = System.Web.HttpContext.Current.Server.MapPath("~/quotes.txt");

            string[] lines = File.ReadAllLines(quotesFilePath);
            foreach (string line in lines)
            {
                string[] sp = line.Split('|');
                var q = new Quotation
                            {
                                Id = int.Parse(sp[0]),
                                AuthorFirstName = sp[1],
                                AuthorLastName = sp[2],
                                AuthorInfo = sp[3],
                                QuotationText = sp[4],
                                Subject = sp[5]
                              };
                 Quotes.Add(q);
            }
            MaxId = lines.Length;
        }


        /// <summary>
        /// Gets quotes by subject search string
        /// </summary>
        /// <param name="subjectLike">The Search Phrase</param>
        /// <returns>List of Quotation</returns>
        public static List<Quotation> GetQuotesBySubject(string subjectLike)
        {
           List<Quotation> foundQuotes = null;
            foundQuotes = Quotes.Where(x => x.Subject.ToLower().Contains(subjectLike.ToLower())).ToList();
             return foundQuotes;
        }


        /// <summary>
        /// Gets quotes by search text in quotation text.
        /// </summary>
        /// <param name="like">The search term.</param>
        /// <returns>List of Quotation</returns>
        public static List<Quotation> GetQuotesByTextInQuote(string like)
        {
           List<Quotation> foundQuotes = null;
            foundQuotes = Quotes.Where(   x => x.QuotationText.ToLower().Contains(like.ToLower() )  ).ToList();
            return foundQuotes;
        }

        /// <summary>
        /// Gets quotes matching either subject or text in quote.
        /// </summary>
        /// <param name="like">The search term</param>
        /// <returns>List of Quotation</returns>
        public static List<Quotation> GetQuotesBySubjectOrTextInQuote(string like)
        {
            var foundQuotes=    Quotes.Where(x => x.QuotationText.ToLower().Contains(like.ToLower()))
                 .Union(Quotes.Where(x => x.Subject.ToLower().Contains(like.ToLower())).ToList());
             return foundQuotes.ToList();
        }


        /// <summary>
        /// Gets quotes by author last name search term.
        /// </summary>
        /// <param name="authorLike">The author search term</param>
        /// <returns>List of Quotation</returns>
        public static List<Quotation> GetQuotesByAuthor(string authorLike)
        {
           List<Quotation> foundQuotes = null;
            foundQuotes = Quotes.Where(x => x.AuthorLastName.ToLower().Contains(authorLike.ToLower())).ToList();
            return foundQuotes;
        }

        /// <summary>
        /// Adds a quotation, resaves the quotes.txt file, and reloads it with the new quotation included.
        /// </summary>
        /// <param name="q">A new Quotation Object</param>
        /// <returns>boolean success</returns>
        /// <remarks>this method insures that the Quotation Text is not a duplicate.</remarks>
        public static bool AddQuotation(Quotation q)
        {
            q.AuthorLastName = q.AuthorLastName.Trim();
            q.AuthorFirstName = q.AuthorFirstName.Trim();
            q.AuthorInfo = q.AuthorInfo.Trim();
            q.QuotationText = q.QuotationText.Trim();
            lock (_lock)
            {
                bool ok = true;
                Quotation q1 =
                     Quotes.Where(x => x.QuotationText.ToLower() == q.QuotationText.ToLower()).FirstOrDefault();
                 if (q1 == null)
                {
                    q.Id = MaxId + 1;
                    MaxId++;
                    string x = q.ToString();

                     if (quotesFilePath == null && System.Web.HttpContext.Current == null)
                        quotesFilePath = System.Environment.CurrentDirectory + @"\quotes.txt";
                     else if (quotesFilePath == null && System.Web.HttpContext.Current != null)
                        quotesFilePath = System.Web.HttpContext.Current.Server.MapPath("~/quotes.txt");
                        File.AppendAllText(quotesFilePath, x + "\r\n");
                     Quotes.Clear();
                     LoadQuotes();
                  }
                 else
                    ok = false;
                 return ok;
            }
        }

         /// <summary>
        /// Gets a distict List of Authors.
        /// </summary>
        /// <returns>List of Author</returns>
        public static List<Author> GetAuthors()
        {
           List<Author> Authors = (
                                       from q in Quotes
                                       select
                                           new Author
                                               {
                                                   FirstName = q.AuthorFirstName,
                                                    LastName = q.AuthorLastName,
                                                   AuthorInfo = q.AuthorInfo,
                                                   FullName = q.AuthorLastName +" " +q.AuthorFirstName
                                               }).Distinct().OrderBy(x=>x.LastName).ToList();
             return Authors;
        }

         /// <summary>
        /// Gets a distinct list of Subjects.
        /// </summary>
        /// <returns>List of Subject</returns>
        public static List <Subject> GetSubjects()
        {
           List<Subject> subjects = (from q in Quotes
                                      select new Subject {SubjectText = q.Subject})
                                         .Distinct().OrderBy(x=>x.SubjectText).ToList();
             return subjects;
        }

        /// <summary>
        ///  Returns a random Quotation
        /// </summary>
        /// <returns>Quotation</returns>
        public static Quotation GetRandomQuote()
        {
            var gen = new Random((int) DateTime.UtcNow.Ticks);
             return Quotes.Skip(gen.Next(0, Quotes.Count() - 1) - 1).Take(1).SingleOrDefault();
        }
    }


    The LoadQuotes method checks for the file location, then parses the file, adding to the List<Quotation> which is static. Everything else is done via LINQ queries.

    For example, GetQuotesBySubject:    
    
    foundQuotes = Quotes.Where(x => x.Subject.ToLower().Contains(subjectLike.ToLower())).ToList();

    Or, GetQuotesBySubjectOrTextInQuote, which uses the Union operator to perform two WHERE conditions:

     var foundQuotes=     Quotes.Where(x => x.QuotationText.ToLower().Contains(like.ToLower()))
                 .Union(Quotes.Where(x => x.Subject.ToLower().Contains(like.ToLower())).ToList());

The final method of note is when you add a new quotation. This gets the correct ID, checks to see if the quotation text already exists, then saves the new quote inside a lock statement to prevent thread contention while writing the the quotes.txt file. Finally, it reloads the Quotes so the newly added Quotation is present.

The downloadable solution has a complete ASP.NET Web project that puts everything to use. You can search for quotes, and even add a quotation.  In addition, I've put in a PageMethod page that uses jQuery to get a list of quotations by using the jQuery Ajax function to directly call a PageMethod (much easier than WCF) and return the quotes:

<script type="text/javascript"  src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js">  
</script>  

    <script runat="server">
        // these are our PageMethods. Only 2 requirements: 1) must be static, 2) adorn with [WebMethod()] attribute.
        [WebMethod()]
         public static Quotation GetRandomQuote()
        {
            Quotation q = null;
            q = Quotations.GetRandomQuote();
             return q;
        }

         [WebMethod()]
         public static List<Quotation> GetQuotes(string searchTerm)
        {
            var q = Quotations.GetQuotesBySubjectOrTextInQuote(searchTerm);
             return q;
        }  
      
</script>

  <script type="text/javascript">
      $(document).ready(function() {
          $.ajax({
              type: "POST",
              url: "pagemethod.aspx/GetQuotes",
              contentType: "application/json; charset=utf-8",
              data: "{searchTerm: 'Peace'}",
              dataType: "json",
              success: AjaxSucceeded,
              error: AjaxFailed
          });
    });

      function AjaxSucceeded(result) {          
          BuildTable(result.d);
      }
      function AjaxFailed(result) {
          alert(result.status + ' ' + result.statusText);
      }

      function BuildTable(msg) {
          var table = '<table cellspacing="2" cellpadding="2" border="0" width="80%" align="center" >';              
               for (var i =0;i<msg.length ;i++)
               {
                  var row = '<tr style="background-color:#FFCC66">';
                  row += '<td>Author: ' + msg[i].AuthorFirstName + ' ' + msg[i].AuthorLastName + '</td></tr>';
                  row += '<tr><td>Author Info: ' + msg[i].AuthorInfo + '</td></tr>';
                  row += '<tr><td>Subject: ' + msg[i].Subject + '</td></tr>';
                  row += '<tr><td class="Yellowborder"><b>' + msg[i].QuotationText + '</b></td>';
                  row += '</tr>';
                  table += row;  
              }
          table += '</table>';
          $('#container').html(table);
      }       
  </script>  


I could also have used the new jQuery template feature to bind my table.


It was fun  putting this together. I think it shows that there is more than one way come up with a good quotation. As Yogi Berra was fond of saying, "The future ain't what it used to be".

Download The QuotationLib Solutiion.

By Peter Bromberg   Popularity  (3718 Views)