HighOnCoding: Get high on ASP.NET articles, videos, podcasts and more!

How to Access SQL Server 2005 from the Windows Mobile Devices

RRaveen
Published Date: 4/20/2009 11:26:05 AM
Views: 1665

Abstract:
In this article I’m going to access SQL server from a windows mobile device (please note that we’re not talking about SQL CE Database). We’re going to connect to SQL server directly from the windows mobile device and implement CRUD and more operations in the device. It will also connect to the main SQL Server directly.
Introduction:

SQL server is one of the most popular databases in the world and SQL CE is database subset of the SQL server that can be used in windows CE operating system. Here I will talk about how to access the SQL server directly in windows mobile device rather than SQL CE database. Because both databases are handled within .Net framework in different ways we must understand those differences.
Now let’s move to the concepts.

Prerequisites:

1. Windows Mobile 6 SDK
2. Windows Mobile 5/6 Professional Emulator.
3. Sql server 2005(for testing purpose Express edition)
4. Visual Studio 2008

Overview:

In this exercise you will develop a .NET Compact Framework application using Visual Studio 2008. The application will allow you to see real time data stored in SQL Server 2005. Since the application is directly accessing and updating SQL Server, it does not require the use of SQL Server Compact Edition nor do you need to configure replication. It does require that the device have reliable connectivity with the backend server while working on this application.
From the Device you can connect to SQL Server using TCP/IP connection. Before we start to write code we want to configure SQL Server to accept remote connections over TCP/IP.

Configuration:

First you need to allow remote connections access to your SQL Server 2005 instance. To allow remote connections, follow the steps below.
1. First click on start -> All Programs then go to the -> Microsoft SQL Server 2005
After that select -> Configuration Tools finally select SQL Server Surface Area configuration. Under menu.

• On the surface area configuration dialog click on "Surface Area Configuration for Services and Connections" option.
• Click on Remote Connections in Database engine node.
• Enable Remote connections by clicking on Local and remote connections.

Finally select sub option "Using both TCP/IP and name pipes". Like below

Then we want to move to firewall setup for this SQL server application.
Configure Windows Firewall:

If your system has the Windows Firewall configured, it must be configured to allow the remote connection to communicate with the SQL Server executable. Follow the steps given below to ensure connectivity through the firewall.
1. Start the Control Panel on the computer running SQL Server 2005.
2. Double Click on Windows Firewall.
3. Click on the Exceptions tab.
4. Click on the Add Program button.
5. Add the path of sqlsrvr.exe. By default the sqlsrvr.exe is installed at <Drive>\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe

Then say OK for all.

Implementation:

For this I had to design a simple user interface that has two tabs, one is to add records to the database and second is to retrieve records from the SQL server.

Now we will move to very simple level data access code.

First I’m going initialize my connectionstring

string connectionstring = @"Server=Server IP/Name;Database=Your DB; Uid=user id; pwd=your sql passwrod;";

Then I create an SQlConnection and access the server with the above and a connection string like the following:

private void SelectEmployee()
        {
            try
            {
                DataTable data = null;
                using (SqlConnection connection = new SqlConnection(connectionstring))
                {
                    if (connection.State != ConnectionState.Open)
                    {
                        connection.Open();
                    }

                    using (SqlCommand cmdSelect = connection.CreateCommand())
                    {
                        cmdSelect.CommandText = "SELECT * FROM Employee;";
                        cmdSelect.CommandType = CommandType.Text;
                        data = new DataTable();
                        using (SqlDataReader reader = cmdSelect.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                data.Load(reader);
                            }
                        }
                    }
                }
                if (data!=null)
                {
                    this.datagrdviewEmp.DataSource = data;
                }
               
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

I hope you can understand the above code because it is basic level data acess code with sqlserver datbase.

Then run and see the results.

Final Touch:

This article talked about how to access sql server from a windows mobile device, note that It’s not talking about SQl server Compact edition. Here we used a very simple level connection object, command and reader object to access the data in SQL server. I also attached source code with this article and I did some insert operations too. Download the code and add more functionality as you wish.

References:

1. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx
2. http://msdn.microsoft.com/en-us/library/ms173748(SQL.90).aspx

Author:
My name is RRaveen, currently living and working in Singapore. Ii am highly focusing on to provide better Solutions to business problems which are commonly an organization facing, retrieve information on time using information technologies.Since i loves windows mobile technology design and implementation business applications which is based on C#.And my other specializations are Asp.net,WPF and WCF .
i have been working on many back ends which are Mysql,Sqlserver and Oracle/PL/SQL/TOAD hands on experience more than 3 and a half year.

[Download Sample]

Did you like this article?

Submit

Enter Comment/Feedback

Name:

Subject:

Email:

2+2=

Comments:

Enter Comment/Feedback

Comments/Feedbacks