MonoTouch Tutorial: Display Tabular Data in iPhone and iPad Apps

What a long strange trip it's been over the late spring and early summer in 2011. Thankfully, everything is up and going in the world of MonoTouch. This is the latest article in what is a series of articles on iPhone development for .NET/C# developers using MonoTouch. To get up to speed on MonoTouch, check out my first article from the April 2011 issue of DevProConnections, "Guide to Building iOS Applications with MonoTouch for the .NET/C# Developer."

Article Overview

Data is what makes applications go. It could be a Twitter search, a running game score where you are playing against your friends, sales data, or any other type of data that users want to base decisions on. In this article, we're going to look at presenting tabular data to users in a UITableView. The UITableView has a number of visually attractive default styles that you can use. After we're done looking at these, we'll look at creating a custom UITableView layout. Along the journey, we'll look at some optimizations we can do that will give the user an improved experience. After we're done with this, we'll look at some strategies to get at various data sources, such as Representational State Transfer (REST), Windows Communication Foundation (WCF), SQL Server, and the on-board SQLite database.

Limitations with Data Apps in iOS

I would be remiss if I didn't mention some of the problems you may run into when using data in the iPhone. These data limitations have very little to do with MonoTouch and much more to do with the general limitations of mobile as well as security issues that you need to be aware of.

First off, the iPhone has a watchdog timer. The timer is "watching" your application. If the timer thinks that your application has locked up in any way, such as locking the UI thread for too long, iOS will kill your application. What does this mean? If your application makes a network request that takes a long time, your application has a good possibility of being killed by the system. This can happen if you make this web request on the UI thread. You want to make requests on a separate thread.

Second, if you make even small requests on the UI thread, you are making a request that can result in "jerky" responsiveness to the user. Your users won't like this. Third, using WCF services in MonoTouch is different from just adding a reference in MonoDevelop to a service and calling methods on the proxy. iOS limits an application's ability to dynamically create code at runtime. As a result, calling WCF is slightly different in iOS. We'll walk through what you have to do there. Finally, when we get to the section on working with SQL Server, I am in no way, shape, or form suggesting that you open up your database server and its associated ports to the public Internet.

Figure 1: Displaying data using UITableView

I view using SQL Server as merely an option for an internal company application to be used on its private network -- say an iPad tablet application used to show inventory levels. Even then, you will want to work with your IT group to discuss the risks.

UITableView

The UITableView is the basis for presenting data to users. This data can come from any source. As long as the data is readable, we can display it to the user, as the example in Figure 1 shows. First off, let's do the basics. We'll open up the .xib file in a basic project. Then we'll drag a UISearchBar and a UITableView into our window design surface. In my example, I've created a couple of outlets. The UITableView is searchTable, and the UISearchBar is searchTerm. Now that you have created these, save and close XCode.

At a high level, let's look at my code for wiring this up in my controller class, shown in Figure 2.

public override void ViewDidLoad (){base.ViewDidLoad ();ts = new TwitterSearch();searchTerm.SearchButtonClicked += HandleSearchTermSearchButtonClicked;//any additional setup after loading the view, typically from a nib.}void HandleSearchTermSearchButtonClicked (object sender, EventArgs e){var TermToSearchOn = searchTerm.Text;searchTerm.ResignFirstResponder();ts.StartSearch(TermToSearchOn, new AsyncCallback(ProcessResult));}public override bool ShouldAutorotateToInterfaceOrientation (UIInterfaceOrientation toInterfaceOrientation){// Return true for supported orientationsreturn (toInterfaceOrientation != UIInterfaceOrientation.PortraitUpsideDown);}void ProcessResult(IAsyncResult iar){List twtL = ts.ProcessRestXmlLINQHttpResponse(iar);var td = new TweetListData(twtL);InvokeOnMainThread(delegate{searchTable.DataSource = td;searchTable.ReloadData();} );}

With this code, we've created a new TwitterSearch object and will be using it to perform searches against Twitter. The search object will perform the calls asynchronously.

When the user clicks on the search bar to perform an actual search, the StartSearch method is called. Inside the StartSearch method, the call to the Twitter Search API is made asynchronously. The code inside my search class looks like that in Figure 3.

public class TwitterSearch{private string TwitterUrl = "http://search.twitter.com/search.atom?q={0}&rpp=100";public TwitterSearch (){}public void StartSearch(string term, AsyncCallback iac)    {        string Url = String.Format(TwitterUrl, term);        try        {        // Create the web request        HttpWebRequest request = WebRequest.Create(Url) as HttpWebRequest;        // Set type to POST        request.Method = "GET";        request.ContentType = "application/xml";        request.BeginGetResponse(iac, request);        }        catch        {          //do somethingthrow;        }    } public List ProcessRestXmlLINQHttpResponse(IAsyncResult iar){List twt;    try    {    HttpWebRequest request = (HttpWebRequest)iar.AsyncState;    HttpWebResponse response;    response = (HttpWebResponse)request.EndGetResponse(iar);    System.IO.StreamReader strm = new System.IO.StreamReader(        response.GetResponseStream());    //string responseString = strm.ReadToEnd();    System.Xml.Linq.XDocument xd = XDocument.Load(strm);    XNamespace atomNS = "http://www.w3.org/2005/Atom";    twt = (from tweet in xd.Descendants(atomNS + "entry")            where tweet != null            select new Tweet            {StatusDate = tweet.Element(atomNS + "updated").Value,            Status = tweet.Element(atomNS + "title").Value,          ProfileImage = tweet.Elements(atomNS + "link").ElementAt(1).Attribute("href").Value,            UserName = tweet.Element(atomNS + "author").Element(atomNS + "name").Value            }).ToList();}    catch    {    //do somethingthrow;}return(twt);}}

Once the data is returned asynchronously, we then call ProcessRestXMLLinqHttpResponse, which will process it using LINQ to XML to create the necessary objects. Both of these methods are used to make the call and process the results and should look familiar to .NET/C# developers.

The next high-level step in the process is to create a data source object. On my searchTable object, I will set the DataSource property and then call the .ReloadData() method. For ASP.NET Web Forms developers, this is very familiar. We do the same thing when binding data to a GridView, where we set the .DataSource property and call .BindData() on the GridView.

Now, let's jump into the iPhone-isms. The first obvious one is InvokeOnMainThread(delegate). We have to remember that we are running on a non-UI thread when we are making asynchronous calls. If we want to write to a UI element, we need to do it from the UI thread. This is done via the InvokeOnMainThread method. If we don't use InvokeOnMainThread to write to the UI, we will either get an error, or we won't get anything to happen. Note: If you are using threading in your MonoTouch application and nothing happens, you probably need an InvokeOnMainThread method call somewhere in your code.

Let's dig into our data object that we are going to bind against. Figure 4 shows the object that I've created.

public class TweetListData : UITableViewDataSource{private List _data;#region implemented abstract members of MonoTouch.UIKit.UITableViewDataSourcepublic override int RowsInSection (UITableView tableView, int section){return(_data.Count);}public override UITableViewCell GetCell (UITableView tableView, MonoTouch.Foundation.NSIndexPath indexPath){// TODO: Implement - see: http://go-mono.com/docs/index.aspx?link=T%3aMonoTouch.Foundation.ModelAttributestring cellid = "cellid";UITableViewCell cell = tableView.DequeueReusableCell(cellid);if ( cell == null ){cell = new UITableViewCell(UITableViewCellStyle.Subtitle, cellid);}cell.TextLabel.Text = _data[indexPath.Row].Status;}return(cell);}#endregionpublic TweetListData (List data){_data = data;}}

The result of running this code looks something like the screen shown in Figure 5.

Figure 5: Twitter search results

Congratulations, you've now got a query running against the Twitter Search API. You are pulling the data back, binding it to the UITableView, and displaying some data to the user. This is a good first step, but it's just a first step. Let's look at some of the more interesting things that we can do.

Built-in Styles

There are four built-in styles available through iOS:

Let's look at some new code for the GetCell method, shown in Figure 6.

public override UITableViewCell GetCell (UITableView tableView, MonoTouch.Foundation.NSIndexPath indexPath){string cellid = "cellid";UITableViewCell cell = tableView.DequeueReusableCell(cellid);if ( cell == null ){cell = new UITableViewCell(UITableViewCellStyle.Subtitle, cellid);}cell.TextLabel.Text = _data[indexPath.Row].Status;cell.DetailTextLabel.Text = _data[indexPath.Row].UserName;var img = Convert.ToString(_data[indexPath.Row].ProfileImage);        NSUrl nsUrl = new NSUrl(img);        NSData data = NSData.FromUrl(nsUrl);        if ( data != null )         {    cell.ImageView.Image = new UIImage(data);      cell.ImageView.SizeToFit();        }  return(cell);}

When you run the application, you get the Twitter profile image, the Tweet, and the Twitter user ID. (Click the Download button at the top of this article to download the complete code for the Twitter search iOS application discussed in this article.) As you scroll through the data, you will probably notice that the scrolling is jerky. Requests to load the image are continuing in the background. Ultimately, it's not really a smooth experience for the users. These requests are happening on the main UI thread. Here are a few ways that I have thought of to improve the user's experience of scrolling through the data.

Custom Cells

All of this sounds so good. Unfortunately, what happens when the defaults just don't fit what you want to do? Thankfully, you can create your own custom look. Let's look at the steps to do this.

First, let's create a new iPhone View with a controller. Create a new file by navigating to MonoDevelop, iPhone, iPhone View and Controller. Give this a file name and select New. In our example, the file is called MyCustomCellWithController, as shown in Figure 7.

Figure 7: Creating a new iPhone View with a controller

Once you click the New button, you should see something similar to the image shown in Figure 8.

Figure 8: New view MyCustomCellWithController

You now have an .xib file that contains the UI layout, the .cs file that contains the location where we can implement some custom code to modify the UI elements, and finally, the .xib.designer.cs file.

The next step is to open our .xib file by double-clicking it, which opens the file in Xcode. Once in Xcode, you will want to drag the UITableViewCell from the library to the window titled View, as shown in Figure 9.

Figure 9: Adding UITableViewCell to the View window in Xcode

I suggest you make the window area an appropriate height, so that we can easily add controls to our view. In our example, we'll add a UIImageView and UILabel. Finally, we'll create and set up outlets for the cell, the UIImageView, and the UILabel. This is performed by creating a custom UITableViewCell and manually creating your own getters and setters.

Your UITableView probably looks something like the image in Figure 10.

Figure 10: UITableView display

Now that you have this, you can see that we're only getting one line of content and notice that the scrolling is a little bit jerky. The problem is that we're currently downloading an image, which can be fairly large for a mobile Internet connection, on the main thread. Let's look at how we can resolve these issues.

The display of the content can be resolved via modifying our getters and setters, as shown in Figure 11.

public string TwitterImage{set { ThreadPool.QueueUserWorkItem(new WaitCallback(SetTwitterImage), value);}}private void SetTwitterImage(object state){var img = Convert.ToString(state);NSUrl nsUrl = new NSUrl(img);NSData data = NSData.FromUrl(nsUrl);InvokeOnMainThread(delegate{uiiv.Image = new UIImage(data);} );}public string TwitterStatus {set {output.Text = value;output.Lines = 0;output.LineBreakMode = UILineBreakMode.WordWrap;output.Font = UIFont.FromName("Helvetica", 12);output.SizeToFit();}}public UITableViewCell Cell{get { return(cell); }}

The two things to note in this code are:

The values are set via getters and setters within our partial class, shown in Figure 12.

public partial class MyCustomCellWithController : UIViewController{public MyCustomCellWithController () : base ("MyCustomCellWithController", null){}public override void DidReceiveMemoryWarning (){// Releases the view if it doesn't have a superview.base.DidReceiveMemoryWarning ();// Release any cached data, images, etc that aren't in use.}public override void ViewDidLoad (){base.ViewDidLoad ();//any additional setup after loading the view, typically from a nib.}public override void ViewDidUnload (){base.ViewDidUnload ();// Release any retained subviews of the main view.// e.g. myOutlet = null;}public override bool ShouldAutorotateToInterfaceOrientation (UIInterfaceOrientation toInterfaceOrientation){// Return true for supported orientationsreturn (toInterfaceOrientation != UIInterfaceOrientation.PortraitUpsideDown);}public string TwitterImage{set { ThreadPool.QueueUserWorkItem(new WaitCallback(SetTwitterImage), value);}}private void SetTwitterImage(object state){var img = Convert.ToString(state);NSUrl nsUrl = new NSUrl(img);NSData data = NSData.FromUrl(nsUrl);InvokeOnMainThread(delegate{uiiv.Image = new UIImage(data);} );}public string TwitterStatus {set {output.Font = UIFont.FromName("Helvetica", 12);output.Text = value;output.Lines = 0;output.LineBreakMode = UILineBreakMode.WordWrap;output.SizeToFit();}}public UITableViewCell Cell{get { return(cell); }}}

This partial class allows us to access the elements within the MyCustomCellWithController in a safe way. The .xib.cs file contains our getters and setters for the class as well as initialization routines.

Delegates

Often, there is a need to change the default behavior of an object in iOS. In this situation, we need to change the default behavior of the UITableView. Specifically we'll want to change the height of the row so that the content fits within it, and we'll want to do something when a cell is selected. To do this, we'll create a delegate object, as shown in the code in Figure 13.

public class TweetViewTableController : UITableViewDelegate{private UIFont font = null;private List _twts = null;public List TweetList {get{  return(_twts); }set{  _twts = value; }}public TweetViewTableController(){if (font == null)font = UIFont.FromName("Helvetica", 12.0f);}public override void RowSelected (UITableView tableView, NSIndexPath indexPath){// TODO: Implement - see: http://go-mono.com/docs/index.aspx?link=T%3aMonoTouch.Foundation.ModelAttributeConsole.WriteLine(String.Format("Row clicked: {0}", indexPath.Row));}public override float GetHeightForRow(UITableView tableView, NSIndexPath indexPath){var TotSize = (TweetListData._data[indexPath.Row].Status.Length / 18 + 1) * font.LineHeight;var maxHeight = 120f;if ( TotSize < maxHeight ) {TotSize = maxHeight;}return(TotSize);}}

In this case, we'll call it TweetViewTableDelegate. Our object will inherit from the UITableViewDelegate, and then we will override the RowSelected and GetHeightForRow methods. These methods will be used when a user clicks on a cell (RowSelected) and when the height of the row needs to be determined (GetHeightForRow).

Other methods could be overridden as well. You can determine what types of functionality you want to override by digging into the available methods -- simply search on "public override" to see some of the available methods.

Retrieving Data

Now that we've seen what we can do with data, the next logical question to answer is where to get data. In .NET on the desktop, accomplishing this is fairly easy. We would go and fire up ADO.NET and get some data via LINQ, Entity Framework, DataTables, NHibernate, or any other type of tool that allows us to query data and return it to the client in some way.

Unfortunately, the mobile world has some constraints that don't exist in the desktop world. Mobile connections are slower than a typical wired connection. In my house, my wired connection via a cable modem is multiple times faster (10 times) than a mobile 3G connection. Even when I am in a location that supports the Sprint 4G network, my cable modem in my house is still about three times faster.

Mobile networks are typically less reliable than wired networks. When I walk through the locker room in my gym, I get zero bars of connectivity. When I walk out of the locker room, the connection immediately goes up to four to five bars of connectivity. Finally, the latency of a wireless network is higher. Latency is the amount of time that is required from the initial request of the device until the initial response is returned back to the device. For a wired connection, a good amount of latency is on the order of 50 ms. For a mobile device, latency could be on the order of thousands of milliseconds. In your mind, this may not sound like much, but in reality this can be significant. It can seem like your TCP/IP packets have to travel from your device, to the moon, off to their destination, and then back.

Another important constraint is the watchdog timer. iOS implements a watchdog timer. If the main UI thread is locked waiting for something to happen, and the watchdog timer hits its timeout point, the watchdog will kill your app.

As a result of these issues, we will want to use asynchronous requests whenever we go off the device as much as possible. Note: Since our web service calls will be going over the public Internet, you should look at using HTTPS for as many of your web service calls as possible.

Using WCF

Now that we understand the constraints that we have with mobile devices, how do we get at data using WCF? .NET developers are familiar with the concepts and how to use WCF in a .NET 4 application running on Windows.

Unfortunately, iOS doesn't allow us to use WCF web services as we would expect. In a Visual Studio-based application, we can create a reference to a WCF web service and Visual Studio will handle setting up the necessary proxies. So, the question becomes how do we get the proxies necessary to make these calls? Thankfully, we can create these on our own. Let's look at the steps to use a WCF service with MonoTouch:

SlSvcUtil.exe /noConfig http://yourwebsite/pathto/wcfservice?wsdl

This will create a .cs file that can then be used inside of a MonoTouch application.

Now that you have completed this, you'll be able to program against the service. To program against the service, your code will look something like that in Figure 14.

voidHandleBtnWCFAsyncTouchUpInside (objectsender, EventArgs e){AddNumberServiceClient asc = newAddNumberServiceClient(   newBasicHttpBinding (),     newEndpointAddress ("http://10.1.10.92/webservices/AddNumberService.svc") );asc.AddNumbersCompleted += HandleAscAddNumbersCompleted;asc.AddNumbersAsync(3, 4);}voidHandleAscAddNumbersCompleted (objectsender, AddNumbersCompletedEventArgs e){InvokeOnMainThread( delegate{lblOutput.Text = "Result: "+ e.Result.TotalNum.ToString();} );}

As you can probably guess from looking at my code, I have a web service that will take two numeric inputs, add them together, and return the result. As you look at the code, everything makes some amount of sense. A method is assigned for the .AddNumbersCompleted event, and then the method AddNumbersAsync is called with the two numbers. When the value is returned, the one thing that looks a little bit strange is the InvokeOnMainThread. When we make an asynchronous query, the result is returned on a non-UI thread. We can only write on a UI control from the UI thread, so we use InvokeOnMainThread to have some code execute on the main thread.

Great, now we know how to use WCF. Unfortunately, the support for WCF in MonoTouch is listed as experimental. If your WCF calls are used in conjunction with HTTPS, you're probably fine. However, don't expect all features of WCF to work perfectly in every possible configuration. WCF is just too complicated to be able to easily implement support in a non-Windows mobile environment without access to a lot of the source code for WCF and rather involved understanding of the internals of WCF.

REST

REST is another way to handle communication with web services. A discussion of what REST is, and its concepts, is beyond the scope of an article about handling data in mobile apps. If you are looking to get up to speed on REST, check out the Representational state transfer Wikipedia page, which is a good starting point to learn more about REST. For us as developers, we'll need to understand a few concepts to get started with.

First, REST is based on using the HTTP verbs for its action. For example, querying data is typically associated with GET. Adding data typically is associated with a POST. Deleting an object is typically associated with DELETE. And so on. I will deviate from this in my examples. I'll use a POST for most of my operations.

With REST, there is no proxy support, so developers will have to know all of the data that goes in and all of the data that comes out.

There are several data formats that you'll want to be familiar with. These are eXtensible Markup Language (XML) and JavaScript Object Notation (JSON). As a .NET developer, you are probably familiar with XML, but most likely less familiar with JSON. For more info about JSON, you can start at the JSON Wikipedia page.

Now, let's look at some code to call some REST-based web services. This first web service is a call to the Twitter API to receive some data. In the example in Figure 15, we'll make a call to get some JSON data back. As you look at the code, you can see that this code is exactly the same code that you would see if you were calling from a .NET 4 desktop application.

void HandleBtnRESTJSONAsyncTouchUpInside (objectsender, EventArgs e){       string Url = "http://api.twitter.com/1/statuses/user_timeline.json?screen_name=wbm";        try        {        // Create the web request        HttpWebRequest request = WebRequest.Create(Url) asHttpWebRequest;        // Set type to POST        request.Method = "GET";        request.ContentType = "application/xml";        request.BeginGetResponse(newAsyncCallback(ProcessRestJsonLINQHttpResponse), request);        }        catch (WebException we)        {        Console.WriteLine(String.Format("Web exception: {0}", we.Message));        }}void ProcessRestJsonLINQHttpResponse(IAsyncResult iar)    {        HttpWebRequest request = (HttpWebRequest)iar.AsyncState;        HttpWebResponse response;        response = (HttpWebResponse)request.EndGetResponse(iar);        System.IO.StreamReader strm = newSystem.IO.StreamReader(        response.GetResponseStream());System.Json.JsonArray ja = (JsonArray)JsonArray.Load(strm);        var twt = (fromx inja             select newTweet            {                StatusId = x["id"].ToString(),                UserName = x["user"]["screen_name"].ToString(),                ProfileImage = x["user"]["profile_image_url"].ToString(),                Status = x["text"].ToString(),                StatusDate = x["created_at"].ToString()            }).ToList();InvokeOnMainThread(delegate{lblOutput.Text = String.Format("Records Returned: {0}", twt.Count);} );}

When you look at the callback, you are probably seeing something that looks a little bit different. The System.Json namespace is something that is actually not in the .NET 4 client profile. Thankfully, Microsoft has included this in the Silverlight profile, therefore it's an entirely valid namespace that .NET developers can use. In our code, we're using a JSON array to hold the contents that are returned to us.

The next step is to create a set of objects. This is done using a LINQ query. Given the popularity of JSON and the ability to use LINQ, this is a great thing. As an fyi, we could just as easily have iterated through the JSON array, but LINQ is so much cooler and more fun to work with. Yes, Virginia, we have LINQ in MonoTouch.

Now that we have looked at JSON support, let's take a look at using XML. You can see in Figure 16 that once again, we are calling a REST API at Twitter, and we're pulling data back.

void HandleBtnRESTAsyncTouchUpInside (objectsender, EventArgs e){        string Url = "http://api.twitter.com/1/statuses/user_timeline.xml?screen_name=wbm";        try        {        // Create the web request        HttpWebRequest request = WebRequest.Create(Url) asHttpWebRequest;        // Set type to POST        request.Method = "GET";        request.ContentType = "application/xml";        request.BeginGetResponse(newAsyncCallback(ProcessRestXmlLINQHttpResponse), request);        }        catch (WebException we)        {        Console.WriteLine(String.Format("Web exception: {0}", we.Message));}}void ProcessRestXmlLINQHttpResponse(IAsyncResult iar)    {        HttpWebRequest request = (HttpWebRequest)iar.AsyncState;        HttpWebResponse response;        response = (HttpWebResponse)request.EndGetResponse(iar);        System.IO.StreamReader strm = newSystem.IO.StreamReader(        response.GetResponseStream());        System.Xml.Linq.XDocument xd = XDocument.Load(strm);        var twt = (fromx inxd.Root.Descendants("status")             where x != null            select newTweet            {                StatusId = x.Element("id").Value,                UserName = x.Element("user").Element("screen_name").Value,                ProfileImage = x.Element("user").Element("profile_image_url").Value,                Status = x.Element("text").Value,                StatusDate = x.Element("created_at").Value            }).ToList();InvokeOnMainThread(delegate{lblOutput.Text = String.Format("Records Returned: {0}", twt.Count);} );}

In the callback, you can see that we're using LINQ to XML and are hydrating our objects. The last thing to note is that I am going ahead and forcing the query to run via the call to .ToList. This isn't strictly required, but done merely for my benefit.

SQLite

Handling data over a web service to a cloud data store is a great thing. Unfortunately, it isn't always the best thing when dealing with data in a mobile device. Doing too much communication over an antenna will hit the battery on a device too much. Also, what happens if you have a lot of storms in your area and lose the cellular network for hours or days? We need a way to store data on the device, and an application will upload that data at another time.

Thankfully in this regard, Apple has included the SQLite relational database with iOS. SQLite is an embedded relational database. It allows a program to store data locally and then send that data to the great data store in the sky. Since we're all familiar with ADO.NET, the API story for SQLite is basically the same as using any other database with .NET. There is a Mono.Data.Sqlite namespace that contains all the ADO.NET objects that we're familiar with, such as a Connection, Command, Parameter, DataAdapter, and similar objects. Let's take a look at some workflow used in the code example in Figure 17 to connect to create and use SQLite.

string dir = Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);string dbFile = "Test.db3";string db = Path.Combine(dir, dbFile);string dbConn = String.Format("Data Source={0}", db);SqliteConnection conn = newSqliteConnection();SqliteCommand cmd = newSqliteCommand();if ( !File.Exists(db) ){SqliteConnection.CreateFile(db);}conn.ConnectionString = dbConn;cmd.Connection = conn;conn.Open();string[] sql = newstring[]{ "CREATE TABLE IF NOT EXISTS PEOPLETABLE(PID INTEGER PRIMARY KEY, FIRSTNAME VARCHAR(25), LASTNAME VARCHAR(25) )",String.Format("INSERT INTO PEOPLETABLE (FIRSTNAME, LASTNAME) VALUES ('{0}', '{1}')", "WALLY", "MCCLURE") }; foreach(strings insql){cmd.CommandText = s;cmd.ExecuteNonQuery();}SqliteCommand sqlCm = newSqliteCommand(conn);string sSql = "select * from PEOPLETABLE";sqlCm.CommandText = sSql;sqlCm.CommandType = CommandType.Text;SqliteDataAdapter sda = newSqliteDataAdapter(sqlCm);DataSet ds = newDataSet();sda.Fill(ds, "PEOPLETABLE");lblOutput.Text = String.Format("Records returned: {0}", ds.Tables["PEOPLETABLE"].Rows.Count);if ( conn.State != ConnectionState.Closed ){conn.Close();}sqlCm.Dispose();sda.Dispose();conn.Dispose();

First, we need to get the directory to the folder that we'll use to store our database file. To do so, combine the directory name and filename to get the full path to the database. We'll use that full path as the parameter to the database-connection string.

Next, we'll check whether the file exists. If not, we'll create it using a static method on the SqliteConnection object. Finally, we'll pass some commands into our connection to set up our database. We could also send the database with our application, but since the application will need to send update commands to the database, I have shown how to set up the database.

Alhough this example only pulls the data from the table, you can use parameters to customize your statements. You can also do all the rest of your CRUD operations. A word of warning: Don't pull down 500KB records to your local database. Although you can do so, your application probably won't perform well because of this, and your users will probably not like you.

A few final thoughts on this. Your database is joined at the hip with your application. If you delete your application, iOS will delete your database as well. Updates don't seem to delete your database, but a delete and a reinstall of an application will. If you are looking for LINQ and Entity Framework, you won't find it. There are some third-party object-relational mapping (ORM) tools that work, but your mileage may vary.

SQL Server

.NET developers are very familiar with using SQL Server as their back-end database. There's nothing special about the code to connect to a SQL Server database in a MonoTouch iOS app. The code to do so is exactly the same as you would use in a .NET server-side or client-side application. However, there are several things that you as a developer need to understand before you open up your SQL Server database and allow a user to directly access it over the Internet.

Figure 18: Using iPhone's internationalization support

The code sample in Figure 19 is performing a SQL Server connection. Note how it looks exactly like what we see in .NET.

string strCn = "Data Source=xxxxx;user id=yyyyy;password=zzzzzzz;Initial Catalog=aaaaaaa";string strSql = "select count(*) from Session";SqlConnection sqlcn = newSqlConnection(strCn);SqlCommand sqlCm = newSqlCommand(strSql, sqlcn);SqlDataReader sdr;sqlcn.Open();sdr = sqlCm.ExecuteReader();if ( sdr.HasRows ) {sdr.Read();count = Convert.ToInt32(sdr[0]);}if ( sqlcn.State != ConnectionState.Closed ){sqlcn.Close();}sqlcn.Dispose();sqlCm.Dispose();lblOutput.Text = String.Format("Records Returned: {0}", count);

Get the Data!

I hope that you have enjoyed this article on the UITableView and data in MonoTouch. In it, we've looked at various options for using the UITableView and images and the challenges that lie therein. We also looked at various options to go get data to fill our UITableView. These samples should work with iOS 5, MonoDevelop 2.8, and MonoTouch 5.x. Now go and get that data!

Wallace B. "Wally" McClure ([email protected]) is an ASPInsider, member of the national INETA Speakers Bureau, author of seven programming books, and a partner in Scalable Development. He blogs at www.morewally.com and co-hosts the ASP.NET Podcast (www.aspnetpodcast.com). Find Wally on twitter as @wbm.