Inline AJAX DropDown and Text Editing with Asp.Net MVC and jQuery

including how to use a database to populate the dropdown

First thing is first, you will need to download jQuery and the Jeditable plugin (I prefer to refer to it as the Jedi-Table!). Be sure to put these references in your View (or Masterpage). Next, you have to set up a view on which to use an inline edit. I find that I often want to use this approach on tables of information. For this View, I will set it to use an IEnumerable of an Item I have called 'ItemOwner' (this is arbitrary and does not really matter). It will be a simple table that lists the Name and the Country of the owner, both of which will be editable inline. Here is the Index in my ExampleController.cs:
myDataContext db = new myDataContext();
public ActionResult Index()
{
    // get the info for the 'Countries' dropdown:
    ViewData["countries"] = db.Countries
        .Select(x => new SelectListItem() 
        { 
            Text = x.Name, 
            Value = x.Id.ToString() 
        }).ToJson();

    // get the 'ItemOwners' I am interested in:
    var owners = db.ItemOwners.Take(3);

    return View(owners);
}

As you can see there, I am also pulling the countries from the database and throwing them into the ViewState - we will get to this later. Since the Country is actually a foreign key relation, the value is set to an integer which is the identity field in the database. It is also using a .ToJson() extension which takes a IEnumerable<SelectListItem> and puts it into a simple JSON string that I use which is here:
public static string 
    ToJson(this IEnumerable<SelectListItem> slis)
{
    string output = "{";
    if (slis != null)
    {
        for (int i = 0; i < slis.Count(); i++)
        {    
            output += " '" + slis.Skip(i)
            .First().Value + "': '" + 
            slis.Skip(i).First().Text + "'" + 
            (i == slis.Count() - 1 ? " " : ",");
        }
    }
    return output += "}";
}

There is probably a better way to do that... but I don't know it?!

I am also pulling 3 ItemOwners from the database, I know this is silly, but it just an example. Here is how I am displaying them in the view:
<table>
    <thead>
        <tr>
            <th>Name</th>
            <th>Country</th>
        </tr>
    </thead>
    <tbody>
        <% foreach(var owner in Model) { %>
        <tr>
            <td><%= owner.Name %></td>
            <td><%= owner.Country.Abbreviation %></td>
        </tr>
        <% } %>
    </tbody>
</table>

Now that there is a simple table we want to make it a bit more interactive. Since we aregoing to make all of these fields editable, we need to add in a way to distinguish exactly what they are. To do that, we will need two things: the id of the item they are editing, and the type of inline editing we will be doing (i.e. dropdown or text input). So to do that, let's add in a few css classes and an identifieng ID:
<td id="name<%= owner.Id %>" class="editable text">
    <%= owner.Name %></td>
<td id="ctry<%= owner.Id %>" class="editable dropdown">
    <%= owner.Country.Abbreviation %></td>

And now add a little css to make them appear to be clickable:
td.editable:hover 
{ cursor:pointer; background-color:Orange; }

Now they all look like you can click on them, so we can move on to making the click actually do something.

This is where the jQuery comes in, and it is very simple. I have made these 'helper' methods in Javascript to make all of my inline calls centrally controllable, I keep this in my sites script folder so if I change one inline edit, I change them all; it also makes for more readable Javascript on each page.
function InlineDropdown(collectionToDropDown, ajaxAddress, dropDownDataSet) {
    collectionToDropDown.editable(ajaxAddress,
    {
        data: dropDownDataSet,
        type: 'select',
        indicator: 'saving...',
        tooltip: 'click to edit...',
        submit: 'Save',
        style: 'inherit',
        placeholder: 'click to edit'
    });
}

function InlineTextbox(collectionToInline, ajaxAddress) {
    collectionToInline.editable(ajaxAddress, 
    {
        indicator: 'saving...',
        tooltip: 'click to edit...',
        style: 'inherit',
        placeholder: 'click to edit'
    });
}

function InlineTextarea(collectionToInline, ajaxAddress) {
    collectionToInline.editable(ajaxAddress, 
    {
        type        : 'textarea',
        rows        : 4,
        indicator   : 'saving...',
        tooltip     : 'click to edit...',
        style       : 'inherit',
        submit      : 'Save',
        onblur      : 'ignore',
        placeholder : 'click to edit'
    });
}

Obviously you can read all about the options on the Jeditable page, but this is how I set them. Also notice I have a InineTextarea included as well for a textarea which is not covered here but works the exact same.

Now the jQuery calls are almost trivial:
InlineTextbox(
    $('td.editable.text'), 
    "<%= Url.Content("~/Ajax/ItemOwner.ashx") %>"
);

InlineDropdown(
    $('td.editable.dropdown'), 
    "<%= Url.Content("~/Ajax/ItemOwner.ashx") %>", 
    <%= ViewData["countries"].ToString() %>
);

What that is doing is sending the POST requests to the specified address. The POST contains a few things:
  • id - the id of the element that sent the request
  • value - the new value passed by the element
We are also passing more information there - remember that we passed both the type of field to edit and the id of the ItemOwner to edit, ie [name837] which emans we want to edit the Name field of ItemOwner 837. So we simply set up an ashx handler (which we specified above) to do the dirty work:
public void ProcessRequest(HttpContext context)
{
    string newValue;
    try
    {
        myDataContext db = new myDataContext();
        string elementId = context.Request.Form["id"];

        // since we made the first 4 of the id the 'field' whic to edit
        // we can just pull the first 4 letters for use in our switch:
        string fieldToEdit = elementId.Substring(0, 4);

        //now take anything after those 4 and it is the Id:
        int idToEdit = Convert.ToInt32(elementId.Remove(0, 4));

        // the value is simply a string:
        newValue = context.Request.Form["value"].Trim();

        // now that we have the id, get the ItemOwner from the db
        ItemOwner owner = db.ItemOwners.FirstOrDefault(x => x.Id == idToEdit);

        // after all is said and done, we will return newValue to the user so the field
        // looks as if the change has taken place (which it has)

        // using the field we pulled above, decide what to do:
        switch (fieldToEdit)
        {
            // name is easy
            case "name": owner.Name = newValue; break;

            // since the country is an integer foreign key, we need to Convert.ToInt32:
            case "ctry":
                owner.CountryId = Convert.ToInt32(newValue);
                // now that we have recorded the value, we want to return the text to
                // the user and not the id value which would make no sense
                newValue = db.Countries.FirstOrDefault(x => x.Id == owner.CountryId).Abbreviation;
                break;
            // if it wasn't caught, something is wrong:
            default: throw new Exception("invalid fieldToEdit passed");
        }

        db.SubmitChanges(); // save it
    }
    // now if an exceptions were reported, the user can see what happened
    // this also inform the user nothing was saved
    // you could easily make this not reported to the user and logged elsewhere
    catch (Exception ex) 
    { newValue = "Error: " + ex.Message + " [nothing written to db]"; }

    //now return what you want in the element:
    context.Response.Write(newValue);       
}

And that is all it takes.

A relationship is being added or deleted from an AssociationSet ...

this error may be cause by a Foreign Key changed that gets missed by Linq-to-Entities

My old Linq-to-Entities project came back to haunt me. I had to make some changes to the DB to allow a couple things, and with that, I changed some INT NOT NULL REFERENCES change to INT REFERENCES - the obvious difference being that they now allowed null values. No big deal really, I went into my .edmx file and did 'Update Model from Database' and everything seemed to be working fine, until I tried a certain operation that kicked out this doozy:

A relationship is being added or deleted from an AssociationSet 'FK__Gizmo__CategoryI__0425A276'. With cardinality constraints, a corresponding 'Gizmo' must also be added or deleted.


Now those arent the actual values, but you get the idea. This is telling me that my Foreign Key relationship is being violated, which confused me. I had changed that FK to be nullable, so this should not be happenening. I then tried the same operation in SQL Server Management Studio, just to be sure it was legal on the SQL side, and it worked fine. So I figured, like so many times before, the problem lies with Linq-to-Entities. I opened my .edmx and saw something like this:
As you can see, it clearly shows a one-to-many relationship from Category->Gizmo. This was no longer the case, but L2E failed to pick up on it.

The bottom line is that 'Update Model from Database' did not catch the Foreign Key change


Once I figured this out, it is a simple to fix. Simply right-click on the relationship (in the box above) and click 'Properties'; that will bring up the Properties dialogue. Once this is open, change the End of the referenced table from '1 (One)' to '0...1 (Zero or One)' and save your .edmx.



This should not be necessary as you would assume 'Update Model from Database' would catch things like this, but like so many other things in L2E, it just doesn't work like you want it to. I can't wait for .Net 4.0, supposedly most of the problems with L2E are getting fixed; we'll just have to wait and see.

string.ToNullable<T>() Extension for Converting a string into a Nullable Object of Type T

save time with a simple conversion that works for all nullable Types

For a while, I have used a ToNullable method (that I found somewhere on the intertubes) that required the input of a TryParse delegate like this:
int? eight = "8".ToNullable<T>(int.TryParse);
int? nInt = "".ToNullable<T>(int.TryParse);//null

Which wasn't bad in any way, but I realized that every time I was using this method, I would have to type in the TryParse of the Type I was trying to get; clearly there is a better way, and I found it using TypeConverter. Now I can use my new ToNullable method in a cleaner, less repetitive way:
int? eight = "8".ToNullable<T>();
int? nInt = "".ToNullable<T>();//null

Here is the code:
public static Nullable<T> 
  ToNullable<T>(this string s) where T : struct
{
  T? result = null; 
  if (!string.IsNullOrEmpty(s.Trim())) 
  {
    TypeConverter converter = TypeDescriptor
      .GetConverter(typeof(T?)); 
    result = (T?)converter.ConvertFrom(s); 
  }
  return result;
}

This has been added to my Utilities Library on CodePlex. convert integer to nullable ?Int32 convert int to nullable ?int convert double to nullable ?double convert bool to nullable ?bool convert decimal to nullable ?decimal convert long to nullable ?long

Universal Get<>() accessor for any Linq-to-SQL Table

never write a Linq-to-SQL Get accessor again

I don't even want to know how many times I have written something like this:
var p = db.Products.FirstOrDefault(x => x.Id == someId);

Then 4 lines down, do it again almost exactly the same, over and over, at least once for each table. Well... no more! Now that I am able to get the Primary Key of any Linq-to-SQL talbe, it is trival to be able to write a universal get statement so I can simply do this when I want to grab an object by it's Primary Key:
Product p = db.Get<Product>(someId);

What if I want to get an item that has a Guid as a primary key? Same thing, it doesn't matter:
Guid gId = 
  new Guid("4fcc0b82-b137-4e4b-935e-872ed662ba53");
Gizmo g = db.Get<Gizmo>(gId);

If you you give the wrong Type of Key, it will tell you in a nice ArgumentException:
Gizmo g = db.Get<Gizmo>(5);

Error:

Primary Key of Table and primaryKey argument are not of the same Type; Primary Key of Table is of Type: System.Guid, primaryKey argument supplied is of Type: System.Int32



Here is the code without any error handling:
public static T Get<T>(this DataContext dataContext,
  object primaryKey) 
    where T : class, INotifyPropertyChanged
{
  return dataContext.GetTable(typeof(T))
    .Cast<T>()
    .Where(GetPrimaryKey<T>()
    .Name + ".Equals(@0)", primaryKey)
    .FirstOrDefault();
}

The full code is available in my Utilities class on CodePlex. This requires System.Linq.Dynamic.

Get the Primary Key PropertyInfo of any Linq-to-SQL Table

Easily find any table's Primary Key property

In my search for a universal generic Get() accessor for Linq-to-SQL DataContexts, I figured I would have to dynamically find the primary key of a table. Using Reflection and the Attributes applied to L2S tables, it is not difficult at all:
//get the primary key PropertyInfo table 'Product'
PropertyInfo info = GetPrimaryKey<Product>();

It's just that easy, it will throw a NotSupportedException if there is no primary key, or the primary key allows NULL. It uses the Linq-to-SQL ColumnAttribute properties to determine what the primary key is. If there is more than one primary key, it will just use the first one it comes across.

The complete source code can be browsed at my new Utilities Library along with full documentation on CodePlex, just figured it would be easy to keep all of my utilities in one place. Otherwise, here is the meat of the code:
public static PropertyInfo GetPrimaryKey<T>()
{
  PropertyInfo[] infos = typeof(T).GetProperties();
  PropertyInfo PKProperty = null;
  foreach (PropertyInfo info in infos)
  {
    var column = info.GetCustomAttributes(false)
     .Where(x => x.GetType() == typeof(ColumnAttribute))
     .FirstOrDefault(x => 
      ((ColumnAttribute)x).IsPrimaryKey && 
      ((ColumnAttribute)x).DbType.Contains("NOT NULL"));
  if (column != null)
  {
    PKProperty = info;
    break;
  }
  if (PKProperty == null) 
  {
    throw new NotSupportedException(
      typeof(T).ToString() + " has no Primary Key");
  }
  return PKProperty;
}

And yes, I did come up with a universal generic Get() accessor for Linq-to-SQL DataContexts, that's next post... or the code is already posted in my Utilities Library.

FileStream Save() Extension - Easily Save Your FileStreams with the Option of No-Overwrite

an extension to allow fast simple saving without worrying about overwriting a file

With these extensions, you can save a FileStream as simple as this:
fs.Save(@"C;/file.txt");

Now if you run it again, it will save another file named file[1].txt, then file[2].txt and so on. If you want to overwrite a file, simply state true for the overwrite flag:
fs.Save(@"C;/file.txt", true);

Here is the code:
public static string Save(this FileStream file, 
  string path)
{ return file.Save(path, false); }
public static string Save(this FileStream file, 
  string path, bool overwrite)
{
  int count = 1;
  string folder = Path.GetDirectoryName(path);
  if (!Directory.Exists(folder)) 
    Directory.CreateDirectory(folder);
  int fileSize = Convert.ToInt32(file.Length);
  string fileName = Path.GetFileName(file.Name);
  Byte[] bytes = new Byte[fileSize];
  file.Read(bytes, 0, fileSize);
  string root = Path.GetDirectoryName(path) + 
    "\\" + Path.GetFileNameWithoutExtension(path);
    
  while (!overwrite && File.Exists(path))
  {
    path = root + "[" + count++.ToString() + 
      "]" + Path.GetExtension(path);
  }
    
  File.WriteAllBytes(path, bytes);
  return Path.GetFileName(path);
}

Parsing Strings to Enums with a Simple Univeral Extension

getting an enum to a string is easy, but switching back can be a pain

If I have an enum:
public enum WhatToShow { All, Courses, Seminars };

and I want to turn a string "Courses" back into that enum Type, there are a few ways I could do it. The most basic way would be to use a switch statement; that is a pain, especially for large enums, plus it has to be re-written for each enum. Here is a simple extension you can use to convert strings back into enums:
public static T ToEnum<T>(this string strOfEnum)
{
    return (T)Enum.Parse(typeof(T), strOfEnum);
}

Now if I have a simple string, it is simple to turn it back to an enum:
string str = "Courses";
WhatToShow en = str.ToEnum<WhatToShow>();

Forganizer - unobtrusive network file organizer - new open source project

An unobtrusive intranet ASP.NET MVC application for logically tagging, organizing and searching network files in one centralized location with a fast and simple interface

Me and some friends have a network set up where we share movies and music. This is a Windows domain network and we all simply share our folders as 'shares' across the network. The problem is that there are so many different files and they are all spread out, there was no way to browse them all at once in any sort of convenient way. That is where the idea for forganizer came up, it is simply a tagging and search interface for multiple shared drives (or just your local ones if you want). Since I hadn't really made any MVC projects yet, I decided to do this one in ASP.NET MVC, a great new technology. Also, this was my first attempt at many new programming techniques I hadn't really used in the past such as Unit Testing, Inversion of Control (Dependency Injection) and Mocking, all very useful!

I want to say right up front, this is for a Windows network, and will work best with... err... IE7+, because it works best with your file explorer - some features like folder opening and downloads may not work with other browsers.

You can always check out my source code if you want to see how something was done, here I am just going to explain what the program does from a user point of view.

clean install

Once forganizer is installed (just run Content/setup.sql and publish the rest to a website, making sure you have MVC installed and all that good stuff) you will see this:

Now you need to add some files to the system, here is the directory we are going to add for the demo. Notice that there are some files in the top folder, and also some folders that go down (quite a few nested folders actually).

adding some files

To add new files in, simply click on the 'manage' tab on the upper right. The default page for manage is 'add files' so you simply enter the network path in the 'from folder' field and click 'run it'.
Notice that it only uploaded 8 files, those are the files in the root. If you check the 'recursive' checkbox, if will go ahead and run through all of the files regardless of depth:
The system will never add a file more than once, so feel free to run the same folders over and over, in fact, that is how you enter new files that have been put on the shares - I think later I will implement and auto-updater. Now if we were to will in more of the textboxes the system would be more selective. Say I wanted to exclude all of the .txt files I have, I would just fill '.txt' in the 'exclude extensions' blank. Similarly, if I am only interested in video files, I would just put '.avi .mpg .mpeg' in the 'include only these extensions' field - it's really pretty self-explanatory.

now you are up and running

Click on the 'search' tab or on 'forganizer' and you will see that you now have a tidy list of files. Also, you will notice that on the right side, all of the file extensions that were in your folders are now represented in the extensions cloud, sized by the relation of file count - in my example, there are many more .cs files than anything else:
Each file has download, open folder and delete links next to it. They all do what you think they would, except delete does not actually delete a file, just the reference in forganizer. If you delete a reference, then later restore it, it will still hold all the tags you previously assigned to it.

tag some stuff

As of right now, this doesn't do us much good, they are searchable by file extension (just click on the file extension on the right), but you could do that in Explorer. We want to add some tags, so click on 'manage' again, and then on 'mass tag adding' on the right side menu.
In this example, I am adding the tag 'Testing' to all the files in the directory \\network_drive\share\folder\forganizer\Forganizer.Tests. Now go back to the search, and you can see that many of the files have been tagged:
The 'mass tag delete' and 'mass tag edit' work just the same as above (actually runs the same exact code). Notice that I also manually added the 'wallpaper' tags to a couple .jpg files - that was done simply by typing 'wallpaper' in the small textbox by each file and clicking '+tags' or pressing enter. You will also see that now there is a cloud tag on the right showing the tags that are in the present search.

create categories

Now you can make some categories to make searching easier. Categories are just groups of file extensions, I am going to make a 'programming' category that includes .js and .cs files:

the manual tagging interface

Now going back to the main search, you will see that a 'programming' category is now in the sidebar. In the following picture, you can see that I have clicked on the 'WebUI' tag and the '.js' file extension. Now all I see is the .js files that are tagged with 'WebUI', it's just that simple! Also take note of the intuitive urls achieved with MVC.
I also typed MS in some of the .js file blanks as I am going to tag those as MS javascript files by pressing enter:
Those MS tags are now inserted.

Now if you want to delete a tag, simply hover over it and a [delete] link will show up:
Click it and the tag is removed:
Pretty basic stuff and simple to figure out and use. This same add/delete interface is used for categories as well.

file cleanup

Now what if someone went and deleted some files, since forganizer does not actually constantly monitor the files, it will be out of date. That is why I built a cleanup feature - say I delete these files:

I can just go to manage -> file cleanup and click the big button:
And you are all cleaned up. One great feature on this is if any file is deleted, either manually or by the cleanup tool, its tags will be preserved; if that file is ever re-added, or restored, it will still have all the meta-data that it always had.

some more stuff

I went ahead and added the 'WebUI' tag to all the files in the \\network_drive\share\folder\forganizer\forganizer.WebUI folder, and 'DomainModel' to all files in the \\network_drive\share\folder\forganizer\forganizer.Tests folder, and finally 'forganizer' to all the files in the \\\network_drive\share\folder\forganizer\forganizer folder, so I can easily discern all of those files. I also added a 'visual_studio' category that include .csproj, .sln and .suo extensions, as well as a documents and images category.

So now I want to find all the Visual Studio related files in forganizer; all I need to do is click on 'visual_studio' in the categories, and 'forganizer' in the tags section and I get the files I am interested in:
Think of it now as "showing all the .csproj, .sln and .suo files with the forganizer tag" Notice that in the upper right, 'and' has a box around it, you can also choose 'or' and it will be a broader search. Say I clicked the 'or' link and then clicked the 'wallpaper' tag and 'images' as well, now forganizer would be showing "showing all the .csproj, .sln, .suo, .png, .jpg and .jpeg files with the forganizer tag OR the wallpaper tag"
You will also see that each cloud (tags/extensions/categories) will be broken into 2 parts, the active (green) links and the inactive (grey) links. The active links are files that are showing in the search right now, the inactive ones are things you can add to the search that will broaden it; if you have 'and' picked in the tag section, you will not see and inactive tags, as that wouldn't make sense.

remember, this is all unobtrusive

This never actually does anything with the files, deleting only deleted the reference, the files will still be there! This is just a layer on top to help sort/search.

I hope this is interesting to someone out there, if not, it was great to help learn MVC and some other great technologies.

Getting the Sheet Name(s) from an Excel Document with OleDb

users name their sheets all sorts of crazy things, but sometimes, I want to be able to get the sheet names regardless of what they are named

Considering that I already have my ConnectionString strC set up to access my Excel file, I can just use the GetOleDbSchemaTable method like this:
DataTable dtS;
using (OleDbConnection c = new OleDbConnection(strC))
{
  c.Open();
  dtS = c.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
    new object[] { null, null, null, "TABLE" });
  c.Close();
}

Now dtSchema holds the sheet names (in alphabetical order). To get them out:
foreach(DataRow row in dtS.Rows)
  Console.WriteLine(row.Field<string>("TABLE_NAME"));