Wednesday, July 22, 2015

Exporting XML From MS SQL Server

Summary

This is going to be a short post on how to query an MS SQL Server database and produce XML output.


The Query

There are two formats that you can use when you want to convert a query result into XML, Elements format and Properties Format.  The following query will select from a table named "product" and put the result set into elements:

SELECT * FROM product FOR XML RAW ('product'), ROOT('data'), ELEMENTS
 
 
The results window will look something like this:
 

Click on the url link and the output should look like this:

<data>
  <product>
    <ProductId>1</ProductId>
    <ProductType>1</ProductType>
    <Name>Matchbox Car</Name>
    <store>1</store>
  </product>
  <product>
    <ProductId>2</ProductId>
    <ProductType>1</ProductType>
    <Name>Baby Rattle</Name>
    <store>1</store>
  </product>
  <product>
    <ProductId>3</ProductId>
    <ProductType>1</ProductType>
    <Name>Lego Bricks</Name>
    <store>1</store>
  </product>
  <product>
    <ProductId>4</ProductId>
    <ProductType>2</ProductType>
    <Name>Steak</Name>
    <store>1</store>
  </product>
</data>


If you want to flatten out your XML, you can use this syntax:

SELECT * FROM product FOR XML RAW ('product')

Which will produce this output:

<product ProductId="1" ProductType="1" Name="Matchbox Car" store="1" />
<product ProductId="2" ProductType="1" Name="Baby Rattle" store="1" />
<product ProductId="3" ProductType="1" Name="Lego Bricks" store="1" />
<product ProductId="4" ProductType="2" Name="Steak" store="1" />


As you can see by the last example, you can leave the "Root" keyword off the query if you don't want to the root data tags added.


Producing JSON

If you need to produce JSON, you will need to do it using another tool.  Use the For XML RAW command above, including the root tag (the output must be well-formed.  Then go to this site (or any other free xml to json converter website): thomasfrank.se XML to JSON – a converter

Paste your xml into the window and hit the convert button.

Sunday, July 19, 2015

MVC - List of Check Boxes

Summary

In this post I'm going to show how to handle a list of check boxes in an MVC application.


The Interface

I've kept this example as simple as possible to point out the tricky spots when attempting to deal with check boxes.  For those of you who have worked with HTML and the POST form submit, you'll know that check boxes are annoying.  The basic problem with a check box is that only the check boxes that are checked will appear in the list of submitted objects.  If you have thousands of check boxes on your screen, then that would be a more efficient method of submitting your data.  Leaving the server code to sort out what changed since the page was rendered.  However, if you have 100 or fewer check boxes, it makes more sense to just submit all the check boxes back with a flag to indicate if it was checked or not.

The following is a screenshot of the screen I'm going to use.  The check box list is dynamic and is determined by a data source.  In this example, the list of names on the screen will be populated manually (i.e. hard-coded).  In a real application you'll need to determine what data will be used in your check list and query the data using an ORM or ADO, etc.


The Model

 Let's start with the model.  First I created an object to represent one person record, or in this case, one check box of information:

public class PersonModel
{
    public string Name { get; set; }
    public bool Checked { get; set; }
}


This object is pretty easy to figure out.  There is a name that forms the title next to the check box.  Your data might consist of a first and last name, in which you can concatenate them using Razor on your view.  The checked boolean value is used to represent the fact that the check box is checked or not.

Then I created another object to represent the entire view consisting of a list of personnel, or a list of check boxes:

public class HomeViewModel
{
    public List<PersonModel> Persons { get; set; }
}


That concludes the model itself.


The Default Controller Method

The controller consists of two methods.  The first method sets up the data and sends it to the view:

public ActionResult Index()
{
    var myHomeViewModel = new HomeViewModel();

    myHomeViewModel.Persons = new List<PersonModel>();
    myHomeViewModel.Persons.Add(new PersonModel { Name = "Frank", Checked = false });
    myHomeViewModel.Persons.Add(new PersonModel { Name = "Joe", Checked = false });
    myHomeViewModel.Persons.Add(new PersonModel { Name = "Sue", Checked = false });
    myHomeViewModel.Persons.Add(new PersonModel { Name = "Mike", Checked = false });

    return View(myHomeViewModel);
}


This is all very basic stuff.  Just initialize a list of personnel (called myHomeViewModel) and populate it with 4 items.  Then make sure you pass the list to the view.


I'll show how the submit controller works after I describe the view.


The View

In order to use your model data, you need to declare the model at the top of the view:

@model MVCMultiCheckBox.Models.HomeViewModel

Then the real work occurs inside the form tags:

@using (Html.BeginForm("Submit", "Home"))
{
    for (int i = 0; i < Model.Persons.Count; i++)
    {
        <div>
            @Html.CheckBoxFor(m => m.Persons[i].Checked, new { id = "cbPerson_" + i })
            @Html.DisplayFor(m => m.Persons[i].Name)
        </div><br />
        @Html.HiddenFor(m => m.Persons[i].Name);

    }
    <div><input type="submit" value="Submit" name="Submit" class="btn btn-default" /></div>
}


The controller's name is "Home" and the controller post method will be named "Submit".  Keep that in mind when you're trying to figure out why your submit doesn't do anything.  Next, is the for loop.  That will iterate through the list of check boxes defined in the model.  Inside the loop are three (3) very important items: The check box, the displayed name and a hidden field.  The check box and name are obvious, but the hidden field is used to make sure that something is submitted when the check box is not checked.  The name of the hidden field will be the same name as the check box.  If a check box is set, then it will be returned, if not, then the hidden field will be returned which will be set to false (all hidden fields have a value of false).  Run the program, right-click on the web page and view the source to see what is rendered to the browser.


The Http POST Controller

Finally, the post controller looks like this:

[HttpPost]
public ActionResult Submit(HomeViewModel result)
{
    return View("Index", result);
}


Which does nothing with the result set.  You'll need to put a break-point on the "return view()" line and submit some data.  Then look at the values in side of "result".  You should see your model updated with the correct true/false representing the check boxes that you checked on the interface.  Here's the result variable in the watch window after checking joe and sue (with everything expanded):


The POST controller is where you'll need to call a save method in your personnel business class and save the changed results back to the database.  If you're attempting to reduce the amount of chatter to your database, you can setup a "PreviouslyChecked" flag for each record and then only save back records where the "Checked" is not equal to "PreviouslyChecked".  Of course, there are many other possible ways to handle the updating of your database from the results returned from your view, but that is not for this blog post.


How to Get The Code

You can download the sample source from my GitHub account by clicking here.


Friday, July 3, 2015

The Case for MVC (Part 5)

Summary

This will be the last post on this series.  I'll be demonstrating a very simple view model to show how the MVC pattern makes a basic web form easy to create and maintain.  I'm using Visual Studio 2013 for this sample and MVC5, but it should work the same with VS 2012 and MVC4.


The Basic Form

This example will incorporate the simplest code possible to demonstrate how to display text boxes in a form to allow a user to edit an address record.  I'll leave the bootstrap framework files in place (some js and css file), but I'm not going to touch any bootstrap code.  Basically, this will be an empty MVC5 application with some C# code and some Razor.

First, let's add a class file to the "Models" directory.  I don't typically use this method when I design website front-ends because I use a lot of AJAX, JQuery and Web API's.  But for a quick-and-dirty website or if your web application doesn't have large data, this is a good way to start.

Right-click on the Models directory and add a class.  Name it "AddressViewModel".  Now paste this code inside:

public class AddressViewModel
{
    public int Id { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Zip { get; set; }

    public void Save()
    {
        // save this record
    }
}


You can change the attributes to turn this record into an Entity Framework Code-First implementation.  I'm not going to do that part here.  I'm just going to show how you can get your raw data between this model and your view.

Now you'll need to create an MVC controller.  Right-click on the "Controllers" directory and "Add - Controller".  Then select "MVC 5 Controller - Empty".  Then name it "HomeController".  Right-click on the "View()" method and add a view (leave it the default Index.cshtml name).  Now you can modify your code to match this:

// GET: Home
public ActionResult Index()
{
    var addressRecord = new AddressViewModel
    {
        Id = 5,
        Address = "123 Main St.",
        City = "Rockville",
        State = "MD",
        Zip = "12345"
    };

  return View(addressRecord);
}

[HttpPost]
public ActionResult Save(AddressViewModel addressRecord)
{
    addressRecord.Save();
    return View("Index");
}


Now open your Index.cshtml.  Modify the code to look like this:

@model MVCModelDemo.Models.AddressViewModel

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

@using (Html.BeginForm("Save","Home"))
{
    <div>@Html.EditorFor(model => model.Address)</div><br />
    <div>@Html.EditorFor(model => model.City)</div><br />
    <div>@Html.EditorFor(model => model.State)</div><br />
    <div>@Html.EditorFor(model => model.Zip)</div><br />
    @Html.HiddenFor(model => model.Id)
    <input type="submit" name="Save" value="Save" />
}


That's it for the code.  One of the things to note is the use of the "Html.HiddenFor()" method that is used to temporarily hold the id.  This will render in html as a standard hidden field.  Any fields from your model object that you want save for the next submit will need to go into a visible control (like text box or radio or drop-down) or it'll need to be saved aside in a hidden field.  When the user clicks on the submit button named "Save", then everything in the model will be submitted back to the controller that is setup the handle the save post operation.

Refactoring this code becomes almost trivial since you don't have to keep track of a bunch of Query or Form values that the user submitted.  You can add a field to the model and then take care of it in the view.  Everything will be posted as expected.

One of the details that I didn't cover in this example is the data context portion.  Typically the object that is used as a view model is also used as the record definition of an ORM such as Entity Framework.  You can also handle this in your addressRecord.Save() method using ADO or any other method you desire.  It's typically best to use an ORM since this pattern is designed around the way that ORMs persist their data.  The Save() method is nothing more than an update to the database, without the need to form a query and populate the fields.


Conclusion

Theoretically, I could continue this series forever, but I'm going to end it for now.  If you're familiar with the previous .Net pattern of using code-behind and <% %> tags, you should instantly recognize a few advantages to MVC:

1. Razor is structured like C.
2. Using the view model pattern is easier than tracking Form and Query parameters.
3. Using controllers instead of "events" makes more sense in a web application.
4. Using a pattern that clearly isolates the front-end code from middle-tier and back-end code makes the project easier to maintain.
5. Unit testing is easier to accomplish due to the distinct separation of concerns.

I have more subjects to cover regarding MVC and I'll be creating future posts on this subject.

How to Get the Sample Code

Go to my GitHub account by clicking here and download a zipped copy to your PC.