I’ve been struggling for a while to find a decent example of how dropdown lists work in ASP.Net MVC. It seems like a pretty simple thing to do – show a dropdown list with a selected item, allow the user to select a different item then save the new selection back on the server when the user submits the form containing the dropdown. However, it took me a few hours of searching and experimenting to get my dropdown working.

Here’s how I got it working …

1. In the model used to pass data to the view, ensure there are two fields – one containing the list of items to show in the list on the web page, the other field containing the value of the selected item. Here’s a cut down version of my model class:

public class CustomerModelData
{
    // The list of payment terms. This is a list of strings
    // converted into the SelectList type for use by the
    // Html.DropDownList HTML helper.
    //
    public SelectList PaymentTerms { get; set; }

    // The value of the selected item. Note that this is
    // a string, i.e. the same type as the list members.
    //
    // Note that the name of this field is used by the
    // Html.DropDownList helper later in the example.
    //
    public String PaymentTermID { get; set; }
}

2. In the controller, when the view is initially loaded, populate the model fields above with the list of items for the dropdown and the value of the selected item.

// Load the data from the database.
//
CustomerModelData Model = new CustomerModelData();

List<PaymentTermData> DBTerms = DatabaseManager.PaymentTerms;
List<String> DBTermsText = new List<string>();

foreach (PaymentTermData PT in DBTerms)
{
    DBTermsText.Add(PT.ToString());
}

PaymentTermData SelectedTerm = DatabaseManager.SelectedTerm(); 

// Set the list of items for display converting from a
// list of strings to a SelectList.
//
Model.PaymentTerms = new SelectList(DBTermsText);

// Set the selected item for the drop down.
//
Model.PaymentTermID = SelectedTerm.ToString();

3. In the view, plase the Html.DropDownList helper:

@Html.DropDownList("PaymentTermID", Model.PaymentTerms)

Note that there are two parameters to the DropDownList call:

a. “PaymentTermID” – This is the name of the field in the model that contains the selected item.

b.  Model.PaymentTerms – This is the list of items for the drop down.

4. When the user makes a selection on the drop down and then submits the form containing the drop down, e.g. by pressing the Save or Submit button on the form, the same model class (CustomerModelData) can be used to pass data back to the controller:

[HttpPost]
public ActionResult CustomerDetails(CustomerModelData Data)
{
    // The value chosen by the user is now held in the model.
    //
    String TheSelectedValue = Data.PaymentTermID;

    // The list of values is NOT passed back to the controller.
    // This field is now null.
    //
    SelectList TheValues = Data.PaymentTerms;

Note that the value selected by the user has been updated in the model BUT that the list of values is now null. If the view is reloaded at the end of the controller action, the list will need to be repopulated in the model.

There may be other, more efficient ways of handling dropdown lists in ASP.Net MVC but this example works for me and took a surprising amount of time and trials to get to a working state. Initially, I had tried to think of the DropDownList helper as though it was a Winforms combo box where I could pass a list of objects, e.g. the actual PaymentTermData class, together with an instance of the class as the selected item. However this didn’t work and in the end I reverted to a simple type (String) for both the list and selected item in the drop down.

Also check out this post http://www.sliqtools.co.uk/blog/net/asp-net-mvc-disable-cache-to-keep-ajax-working-ok/ for tips on disabling the cache with Ajax requests as I have come across situations where the dropdownlist (and other stuff) appears not to work with the caching enabled.

While working with SQL Server and Entity Framework the following error was raised after adding a new table into the SQL Database and updating the model in my project from the DB:

"Unable to update the EntitySet 'Customers' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation."

The error was raised as an exception on calling the SaveChanges method on the context after adding a new record into a list/ table on the database.

The solution to the problem was simple – although my new SQL table had an ID column, I hadn’t set the column as the primary key. Setting the column as a primary key in SQL Server Management Studio then going back to my Visual Studio project and rebuilding the entity model from the database made the error go away and let me successfully save new records to the table.

As part of developing a new application using the SQLite database I need to perform some standard database checks when the application starts. Firstly I need to check that the application’s database exists and then either create the database if it doesn’t exist, or, if the database does exist, check that the tables in the SQLite database contain the columns expected by the app.

Checking whether the Database Exists

Checking that the SQLite database exists is easy, I can simply use code like:

if (!File.Exists(mPathName))
{

i.e. I can use the normal System.IO methods in .Net to check whether the database is present.

Creating a SQLite Database

Creating a SQLite database is also straightforward in SQLite. I can create the database file as follows:

// Create the database file.
SQLiteConnection.CreateFile(mPathName);

// Open a connection to the database.
using (SQLiteConnection Conn = new SQLiteConnection("Data Source = " + mPathName))
{
    Conn.Open();

    // Create the required tables. In this example, I'm creating a Customers table with 3 fields - ID, UNIQUEID and DATAFIELD.
    String SQL = "CREATE TABLE Customers (ID INTEGER PRIMARY KEY, UNIQUEID VARCHAR(30), DATAFIELD VARCHAR(100))";

    using (SQLiteCommand Command = new SQLiteCommand(SQL, Conn))
    {
       Command.ExecuteNonQuery();
    }

    Conn.Close();
}

This is all pretty standard SQL and ADO.Net.

Checking if Columns Exist in the SQLite Database

Checking whether columns exist was a little harder to work out for SQLite. Some other databases I’ve used has special methods to get the tables, columns and their attributes. SQLite didn’t have a class to enable me to do this quite so obviously but after some research and experimentation, I found the following code worked:

// Open a connection to the database.
using (SQLiteConnection Conn = new SQLiteConnection("Data Source = " + mPathName))
{
    Conn.Open();

    // Get the schema for the columns in the database.
    DataTable ColsTable = Conn.GetSchema("Columns");

    // Query the columns schema using SQL statements to work out if the required columns exist.
    bool IDExists       = ColsTable.Select("COLUMN_NAME='ID' AND TABLE_NAME='Customers'").Length != 0;
    bool UNIQUEIDExists = ColsTable.Select("COLUMN_NAME='UNIQUEID' AND TABLE_NAME='Customers'").Length != 0;
    bool ElephantExists = ColsTable.Select("COLUMN_NAME='ELEPHANT' AND TABLE_NAME='Customers'").Length != 0;

    Conn.Close();
}

A statement like

ColsTable.Select("COLUMN_NAME='ID' AND TABLE_NAME='Customers'")

returns an array of DataRows. If the column doesn’t exist an array of length 0 will be returned, hence the .Length != 0 check.

Adding a Column if it doesn’t Exist in the SQLite Database

Adding a column to a SQLite database can be performed using standard SQL statements.

String SQL = "ALTER TABLE Customers ADD COLUMN ELEPHANT VARCHAR(100)";

using (SQLiteCommand Command = new SQLiteCommand(SQL, Conn))
{
    Command.ExecuteNonQuery();
}

It turns out that performing these basic operations on a SQLite database is pretty straightforward when you know how. One thing I haven’t bothered to find out yet is to determine whether a column has the correct attributes, e.g. my example ELEPHANT column may change from a 100 character to a 200 character wide column between different versions of the application. However, I’ve never had a good reason for doing such a database update in the past. I’m also relying on the SQLite feature where the database will store any sort of data of almost any length in any column.

For more SQLite tips, see:

http://www.sliqtools.co.uk/blog/technical/sqlite-how-to-get-the-id-when-inserting-a-row-into-a-table/