- Accessing SQL Server Express from the emulator (or PDA)

This post talks about data access from the emulator (or actual device) to your PC. If you're just getting started on the .NET Compact Framework 2.0, you can read this to install the tools and this to write your first app.

In this tutorial, you learn how to let the emulator access a SQL Server Express database in your PC.

SQL Server 2005 Express edition is free and is installed together with most editions of Visual Studio 2005. As the successor of the very useful MSDE, SQL Server Express offers the equivalent, if not better, feature set of other free database servers (MySQL in particular). Compared to MSDE, SQL Server Express does not have the workload throttle restriction, thus giving better scalability, and allows a higher maximum database size of 4GB (an increase of 2GB). However, the new kid on the block always uses only a single CPU (even if the machine has multiple), and supports only replication subscriptions (it can't be a publisher). Still, it is extremely useful for basic development and exploration, in my opinion. It even comes with a management console (currently in Community Technology Preview, downloadable from a link on this page), something that its predecessor lacks.



Now, let's dive in! This step-by-step tutorial requires a sample database such as Northwind.mdf (which can be extracted from a download here). Although Northwind was created for SQL Server 2000, it can also be used in SQL Server 2005.

1. You should have downloaded and extracted the Northwind sample database (download from here). Place both Northwind.mdf and Northwind.ldf files in a folder of your choice.
2. Download and install the Management Studio Express from a link on this page. Start the Management Studio and login using Windows Authentication. Right-click on the first node in the treeview and click "Properties". In the dialog window, click "Security" on the left and select "SQL Server and Windows Authentication". This will allow connections to be made using SQL Server authentication.
3. Next, still in Management Studio, expand the Security node in treeview, then expand Logins. In the main panel, right-click on "sa" and click "Properties". Change the password. Still within Properties dialog, click "Status" in the leftside panel, and enable the "sa" account. Click OK.
4. Right-click on Databases, and attach the Northwind database.
5. Next, we need to enable TCP/IP connections. Click Start-> All Programs-> SQL Server 2005-> Configuration Tools-> SQL Server Configuration Manager.
6. Under "Protocols for SQLEXPRESS", ensure that "TCP/IP" is enabled. Double-click on TCP/IP, set Enabled to Yes, and under the "IP Addresses" tab, clear all the "TCP Dynamic Ports" entries (set them blank), and set TCP Port (under IP All) to 1433. We are fixing the port, which means SQL Browser service can be stopped (in fact, this is a security best practice). You may need to use another port number if you have other versions/instances of SQL Server running.
7. Restart the SQL Express service to activate the new configurations.
8. Start Visual Studio and create a new smart device project.
9. Add a ComboBox called cboCountries to the form. Add the reference System.Data.SqlClient to your project. In code view, add the line "using System.Data.SqlClient;" near the top.
10. In the Form_Load event handler, add the following code (replacing "192.1.2.3" with your IP address, and "yourPassword" with the password for your sa account:



string sConnection = "Data Source=192.1.2.3,1433;Initial Catalog=Northwind;User ID=sa;Password=yourPassword;";
string sSQL = "SELECT DISTINCT Country FROM Customers ORDER BY Country";
SqlCommand comm = new SqlCommand(sSQL, new SqlConnection(sConnection));
SqlDataReader dr = null;
try
{
  comm.Connection.Open();
  dr = comm.ExecuteReader();
  while (dr.Read())
    cboCountries.Items.Add(dr[0]);
}
catch (Exception e)
{
  MessageBox.Show(e.Message);
  return;
}
dr.Close();
comm.Connection.Close();




11. Start the emulator and cradle it (see this post on cradling the emulator). Make sure that it can access the network.
12. Run your program. You should see the combo box populated with a list of countries extracted from the Northwind sample database.

What next? Learn how to create a SQL Server Mobile database here.

Categories: [Data Access_] [Networking_] Tags: [netcf2] [Compact Framework] [SQL Server]