Administrate SQL Server2000 from Pocket PC

• Download source files - 37.2 Kb

Introduction

I am a little crazy about writing mobile applications using .NET CE. This article describes about administrating SQL Server 2000 from Pocket PC with the help of Web Services. Using web service I can administrate SQL Server 2000 on the fly.

Client Requirements.

1. Pocket PC with Microsoft Windows CE powered.
2. Microsoft .NET Compact Framework 1.0.

Server Requirements.

1. Microsoft .NET framework 1.1.
2. MS SQL server 2000 with SP3 or above.
3. IIS 5.0.

Background

Sometime I might away from my SQL Server or I might have be traveling somewhere. In that time, I thought about how could I control my SQL Server from there. Then I got an idea why not to try with a Pocket PC. When I stated doing the application, I faced problem in connecting to my remote SQL Server from Pocket PC. Then I look around and thought web service might be the right solution. I used web service as middle tire to interconnect my Pocket PC and SQL server. Finally, I am able to achieve that.

This is a beginning. In this, we can connect to SQL Server and manipulate query processes like how we do in Query analyzer.

In future version, I am planning to do like Desktop Enterprise Manager. If you can help me to achieve this, then you are most welcome.

Using the code

Download the source code as ZIP file. Unzip it, and in that you can find two folders named WSSQLsrv2000 and ControlSQLsrv2000.

WSSQLsrv2000 folder contains Web services, which will be consumed by the Pocket PC. Open Internet Information Services Manager (IIS) create a virtual folder under the Default Web Site directory. Set the physical file location as WSSQLsrv2000 folder path. Give write permission to the Virtual folder.

Find creating virtual folder details over here.

ControlSQLsrv2000 folder contains Windows CE application. You can compile and execute it in Emulator or deploy it in your Pocket PC.

When you execute the Pocket PC application you will be prompt for SQL server details to connect.

When you click on connect, the following code will be executed.

try
{
    UpdateStatus("Connecting to the database...");
    oLoginInfo.sServer = txtServerName.Text;
    oLoginInfo.sLoginName = txtLoginName.Text;
    oLoginInfo.sPassword = txtPassword.Text;
    oLoginInfo.sDatabase = "";
    ConnectDB oConDb = new ConnectDB();
    if (oConDb.fnConnect(txtServerName.Text ,txtLoginName.Text ,txtPassword.Text))
    {
       UpdateStatus("Connected...");
       frmMain ofrmMain = new frmMain();
       ofrmMain.FillTreeView(oLoginInfo);
       ofrmMain.Show();
       this.Hide();
    }
    else
    {
       UpdateStatus("Not Connected..."); 
       .....

The following code in Web service will be executed.

[WebMethod] public bool fnConnect(string sDataSource, string sUserid, 
                                                     string sPassword)
{
   //To check the connection status with given details
   oLoginInfo.sServer = sDataSource;
   oLoginInfo.sLoginName = sUserid;
   oLoginInfo.sPassword = sPassword;
   oLoginInfo.sDatabase = "";
   if (oCon.fnConnectStatus(oLoginInfo))
   {
     //Save the details if the connection status is true for further manupulation
     SetDBConfigSettings(sDataSource,sUserid,sPassword);
     return true;
   }
   else
   { 
     //Connection failed in various reasons
     return false;
   }
 }

ConnectDB is a class written in web service. Here we are creating the object to that class and consuming its Web method fnConnect(). When this function is called, the web services receive the request and connects to the SQL server. Once the connection was success then the application will show the Server details.

We can populate the databases in two ways.

1. Select Databases, tap and hold for few seconds and a pop up menu will appear with two options Refresh and Query Analyser. In that Select Refresh.

   

2. Select Database and select File -> Refresh From the main menu.

   

Once you select the refresh option, the entire databases will be listed. Select the Database which you want to manipulate and choose the Query Analyser option based on the above two methods. Once you select it you will be redirected to another screen.

Main menu details

1. Refresh – Reload all the databases under the server.
2. Query Analyser – Select particular database under Databases Directory and do query manipulation by selecting this option.
3. Re-Login – Exit from current server and login into different server.
4. Exit – Exit and close the application.

In that text box enter the query you want to execute. For example, type

Select * from authors

Select the statement to execute. And tap on to execute the statement. The results will be populated in the grid.

The results will be populated like this:

Points of interest

When you deploy the web service in your local PC, you have to change the URL from http://localhost to http://<ipaddress> in reference.cs under WSSQLsrv2000 folder. Otherwise the Pocket PC can’t consume the service.

You can find some of my articles over here.

History

Initial version 0.1.0