Use the Web Service Connection Database in the Pocket PC
Foreword
Microsoft's mobile developer conference was held in Beijing, and the domestic mobile application software has just started, but the broad field of this prospect has received more and more software vendors. Business applications on mobile devices Although it has just started, it has shown great development potential.
In the Microsoft Mobile Development Challenge, my work "The First Year Wireless Catering Management System" won the third prize of commercial applications. In order to achieve the connection of the Pocket PC and the background database server, I use the .NET CompactFramework and Web Service technology in my work, I will share the implementation principle of the core part to share. In this paper, there is no code "饕餮 饕餮年" code, I use another program "SQL Query Analyzer" as an example of a demo.
In the field of embedded database, all major database vendors have their own mobile solutions, such as Microsoft's SQL Server CE, Sybase's IAnywhere, IBM DB2 Everyplace, etc. Although various manufacturers provide data synchronization solutions, it is realized by different principles, however, each data synchronization scheme is targeted for their own database, and cannot be compatible with other database products.
So there is no way, can access multiple databases through a mechanism? Microsoft's Mobile 2003 has fully supported .NET Compact Framework development, and .NET provides good support for Web Service, developers can easily develop Web Service's service programs and client applications.
This article discusses solutions to multiple databases through Web Service on the Windows Mobile2003 platform. This will definitely be the optimal solution to the mobile device to access a variety of remote databases, but I hope this is a relatively excellent solution to the existing technology.
System requirements: Visual Studio .NET 2003
Pocket PC 2003 Simulator
IIS 5.0
First Web Service
At present, Web Service has received extensive attention, and mainstream development tools provide good support for Web Service development. We use Visual Studio. Net 2003 here to make a simple Web Service instance, let the programmers developed by Web Service better into the discussion of follow-up.
Let's create an ASP.NET Web Service Project: Open the File menu, select New, then Project. In the New Project dialog box, Project Types select "Visual C # Projects", Select "ASP.NET Web Service" in Templates. Select your Web Service location and name in Location. Since IIS service is configured on my machine, I will deploy Web Service directly on the unit. Please see Figure 1.
ASP.NET Web Service Engineering and ASP.NET are very similar, just the extension of the page files as ASMX, not ASPX. Next, we have to create a WebMethod for Web Service. Open the service1.asmx.cs file, add two webmethod: YourName and Welcom, the code is as follows (the code for bold words):
Namespace WebService1
{
Public class service1: system.web.services.Webservice
{
......
[WebMethod]
Public String YourName ()
{
Return "my name is wolf! I am the old wolf!"
[WebMethod]
Public String Welcom (String Yourname)
{
String str = youRname ", welcome to Wolf Web Service";
Return Str;
}
}
}
Here you need to remind everyone that every webmethod must be public, and you need to function before the function.
Plus [WebMethod] statement. The YourName function returns a String object containing English and Chinese, mainly to test the normal reality of the web service's string to support Unicode's Windows Cerace. The Welcom function adds a parameter that is also to test whether the Web Service and the WindowsCE platform exist in a string that cannot be displayed normally.
Then we select the start command (or press F5) in the debug menu to run this web service, appear as follows: (see Figure 2)
It can be seen that the name of the two functions we have just prepared is displayed. You can click on the function name to view information such as WebMethod's SOAP and HTTP POST, you can also click the Invoke button directly to see if the result returned by WebMethod is correct.
If your Web Service is working properly, then we create a Smart Device application that invasively invasive Web Service in the Pocket PC 2003 environment. If you don't know how to create a Smart Device application under Visual Studio .NET 2003, I hope you will refer to the instructions below.
Open a new VS.NET IDE environment, open the File menu, select New, then Project. In the New Project dialog box, Project Types select "Visual C # Projects", Select "Smart Device Application" in Templates, then click OK.
In the Smart Device Application Wizard dialog, there are two list boxes. The target platform for the list box selection program will display different options depending on the Different options you have in developing the SDK installed on the machine. The Pocket PC includes Pocket PC 2002 and Pocket PC 2003; Windows CEs include other WindowsCe platforms that support .NET Compact Framework. If you have the SMARTPHONE SDK, you will also display the SMARTPHONE option.
The following dialog boxes are the created application type, including Windows applications, class libraries, non-graphical applications, and empty projects. The options here will also be slightly different depending on your selection platform. One thing to explain is that if you have a Mobile Internet Toolkit, you can also create an ASP.NET web program for the mobile device through this wizard.
We have selected "Pocket PC" and "Windows Application" here, click OK. We can start moving the development.
Let's take a look at the panoramic of the IDE environment: Actually, the development environment of the WinForm program is almost. But please pay attention to the drop-down menu in the picture, and friends who have been developed by Windowsce must be very familiar. Yes, this is the menu for selecting the application output device. We have selected "Chs Pocket PC 2003 - SDK Emulator", we can select the Connect to Device button next to the drop-down menu to start the simulator, or wait until the program is running to start the simulator. Note: Visual Studio .NET 2003 will default to the SDK of Pocket PC 2002, you need to install the Pocket PC 2003 SDK and Chinese images to see this. However, there is no problem with the application you undermine under the Pocket PC 2002 simulator.
Below, let's add Web References, select "Add Web References" under the Project menu, or right click on Web References in Solution Explorer, and select "Add Web References" in the pop-up menu. We will see the following dialog:
In the URL to fill in the web service URL, do you remember the URL in the IE address bar when we run Web Service? http://localhost/webservice1/service1.asmx. right? If it is a WinForm program, this is correct, but in the Smart Device application, it is wrong to write. Please note that the topic discussed below is important: the Simulator of the Windows CE device is actually connected to your PC as a remote device. So, if you write "localhost" or "127.0.0.1", the program access will be the simulator, not the PC you used to develop. So, here you have to fill in your PC's actual machine name or IP address in the network. So here should fill in http: //yourname/webservice1/service1.asmx (YourName means your actual machine name). Here if you fill in the localhost, the development environment will also find Web References, but will appear before calling the Web Service. Ok, fill in the URL, press the Go button, if the URL is correct, then the information on the image is displayed, then click the Add References button. This way we can use this Web Service in the project.
We open the interface editor, add two button and a textbox to call two webmethod. code show as below:
Private void Button1_Click (Object Sender, System.Eventargs E)
{
YourName.Service1 myservice = new yourname.service1 ();
Label1.text = myservice.Yourname ();
Messagebox.show (MyService.Yourname ());
}
Private void button2_click (Object Sender, System.Eventargs E)
{
YourName.Service1 myservice = new yourname.service1 ();
Messagebox.show (MyService.welcom (TextBox1.text));
}
YourName is the name of the Web Service, which is equivalent to namespace, so we must create an object of the service1 class in your YourName, then call the method in Service1. You can see that the process of calling the web server in the C # program and the process of creating a local class and calling its method is very similar. It is also necessary to say that due to the influence of the network factor, the calling web service is not necessarily successful, so it is necessary to add an exception handling code in the development of commercial software, which is omitted in order to make a conciseness of the program. The program operation results are as follows: SQL query analyzer
After understanding the development and call of the basic Web Service, we can discuss the core content. Web service is based on XML language, which is this feature that makes web services to connect a bridge between various heterogeneous systems. So is there a simple way to pass data across different databases? ASP.NET Web Service is support to returning a DataSet object, while DataSet can be supported by various data representing controls, such as DataGrid, Dataset is the bridge we have to find.
In this section, I will create a SQL query analyzer. Unlike other query analyzers, I can specify the connected database type when calling, so that you can implement the connection different databases. We first create the Web Service section of the SQL query analyzer:
Let's take a look at the UML map of SQL Service. We used the service1 class in SQL Service, and added five WebMethod to it, see the following table:
name
Features
SetDatabaseType Sets the Type of Connection Database SetDbConnectionstring Settings Database Connection Strings Create Creating Database Operation Objects EXECUTENONQUERY EXECUTEDASET executing the SQL statement ExecuteTaset executes the SQL statement and returns a DataSet
In the service1 class of Web Service, we first declare that three variables are used to store database operation objects, database connection strings, and database types. Note that we declare the variables as static, and to allow the client to perform different webmethod, you can access the same database operation object and string object. DbopeReter is the base class of the database operation class, and we have created an instance of DBopeReter derived class.
Private static dboperater m_dboperater;
Private static string m_databaseType;
Private static string m_dbconnectionstring;
SetDatabaseType and SetDbConnectionstring Two WebMethod are the ways to set up database types and database connection strings. Here, it is necessary to specifically remind the connection string of the database as a web service parameter, and it is a very dangerous approach to the network in a clear text. Here is just the simplicity of the sample program. It is highly recommended that readers protect their database connection strings when implementing their own Web Service.
[WebMethod]
Public Bool setDatabaseType (String DatabaseType)
{
IF (dataBaseType == "")
Return False;
M_DatabaseType = DatabaseType;
Return True;
}
[WebMethod]
Public Bool SetDbconnectionstring (String Dbstring) {
IF (dbstring == "")
Return False;
m_dbconnectionstring = dbstring;
Return True;
}
The following CREATE method is used to create the object of the corresponding DBOPERATER derived class according to M_DatabaseType. What is needed here is that setting the database type (setDatabaseType) is String as a parameter, without using an ENUM type or other type, is to add a client source code when adding to the new database support.
[WebMethod]
Public Bool Create ()
{
IF (m_databaseType == "SQL Server")
{
m_dboperater = new dbsqlserveroperater (m_dbconnectionstring);
Return True;
}
IF (m_databaseType == "access")
{
m_dboperater = new dbaccessoperater (m_dbconnectionstring);
Return True;
}
Return False;
}
The remaining two WebMethod simply invoke the corresponding method of DBOPERATER derived class objects, while the actual database operation is implemented in the DBopeRer derived class. This makes it better to achieve the separation of the database operation and the client operation.
[WebMethod]
Public Bool ExecutenonQuery (String SQL)
{
Return m_dboperater.executenonquery (SQL);
}
[WebMethod]
Public DataSet ExecuteTASet (String SQL)
{
Return m_dboperaater.executedata (SQL);
}
We will package the specific method of the database to the DbopeRater class and derived the DBOPERATER Class with DBSQLSERVEROPERATER and DBACCESSOPERATER classes, which are used to perform operations for SQL Server databases and Access databases. DBOPERATER classes have exposed two virtual methods: ExecutenonQuery () and ExecuteDataSet (), which are responsible for overloading by derived classes. The derived class also exposes its own constructor to obtain connection database strings.
Below is the implementation code of the DBOPERATER class, you can see that two methods in the DBOPERATER class use virtual keywords, which is declared as virtual functions.
// Database Operation Base Class
Public Class Dboperater
{
Public Virtual Bool ExecuteNonQuery (String SQL)
{
Return True;
}
Public Virtual DataSet ExecuteDataSet (String SQL)
{
Return NULL;
}
}
In the Architecture of Web Service, only DBOPERATER's derived classes that actually perform database operations, we see the DBSQLServerOperaater class, using the database operation class in System.Data.sqlClient namespace, and store the SQL Server database. The DBAccessopeRater class operates on the OLEDB data, using the class in the System.Data.OleDb namespace. // SQL Server action class
Public Class DbsqlServerOperater: DbopeReater
{
Public dbsqlserveroperater (String Dbconnectionstring)
{
M_SqlConnection = New SqlConnection (dbconnectionstring);
}
Public Override Bool ExecuteNonQuery (String SQL)
{
IF (SQL == "")
Return False;
m_sqlconnection.open ();
Sqlcommand command = m_sqlconnection.createCommand ();
Command.commandtext = SQL;
Command.executenonQuery ();
m_sqlconnection.close ();
Return True;
}
Public Override Dataset ExecuteDataSet (String SQL)
{
IF (SQL == "")
Return NULL;
m_sqlconnection.open ();
SqlDataAdapter Da = New SqlDataAdapter (SQL, M_SQLCONNECTION);
DataSet DS = New DataSet ();
Da.fill (DS);
m_sqlconnection.close ();
Return DS;
}
Private SqlConnection M_SqlConnection;
}
// OLEDB operation class
......
The ACCESS operation class code that is basically the same as the SQL Server operation class is omitted. For implementations and precautions for heavy load and inheritance in C #, see related books.
Since the operation of the database has been packaged in the Web Service, the client's code is relatively simple. Smartsqlclient contains two property pages, the first property page is setting, setting the database type and database connection string, after setting, click the Setting button, and the program saves the database type and the connection string and automatically switches. To the second property page.
The second property page Query, including a TextBox, used to write SQL query statements, a DataGrid, used to display the database from the Web Service, and the two Button execute the SQL query and the not return result set. Operation.
Now let's take a look at the response function of the ExecdataSet button, which first creates an instance of the Web Service access object, then set the database type and database connection string according to the settings in Setting, then create an object instance of the corresponding database operation class, and Call the Web Service's ExecuteDataSet method, passed a valid SQL query statement to the Web Service. Web Service will return a DataSet object, and our client program displays the first table in the DataSet into the DataGrid control. Private Void Execdsbtn_Click (Object Sender, System.EventArgs E)
{
Wolf.service1 service = new sqlsmartclient.wolf.service1 ();
Service.setDatabaseType (M_DatabaseType);
Service.setdbconnectionstring (m_connectionstring);
Service.create ();
DataSet DS = Service.executedataTaset (TextBox1.text);
DataGrid1.datasource = DS.TABLES [0];
}
Ok, here, we have implemented a SQL query analyzer that can operate remote SQL Server and Access databases in PDA end. With the increasingness of the .NET database operating components, you can fully support the support of the popular relational databases such as Oracle, DB2, and implement more mobile devices-based applications on this basis.
Interact with SQL Server CE
The SQL Query Analyzer we implemented above is not only to easily retrieve different types of databases on mobile devices, but also this web service is more realistic.
It is well known that the mainstream database on the Windows CE platform is SQL Server CE, SQL Server CE and server exchange data mainly have two: Remote Data Access (RDA) and Replication. These two data synchronization methods need not only need to build a special IIS environment and can only synchronize data with the SQL Server database. This greatly increases the difficulty of using other types of database system integrators to develop mobile device applications, and is also not conducive to the Windows CE platform being accepted by a broader field.
The following time, I will demonstrate how to use the Web Service to synchronize data between SQL Server CE and Access by Web Service. Of course, my implementation can not reach the RDA to synchronize the table structure.
In order to complete this part of the code, we add a property page to SQLSMARTCLIENT, named SQLServerce. The top TEXTBOX and CREATEDB buttons are prepared to create a table in the SQL Server CE database and the database, will later introduce their code implementation. The following Fill button completed tasks include submitting the SQL statement in the second TextBox to the Web Service, which is responsible for providing DataSets that contain the appropriate data. Then parse the DataSet, insert into the corresponding SQL statement, insert into the SQL Server CE database.
Let's first introduce the operation of the SQL Server CE in the .NET Compact Framework. All operations for SQL Server CE in .NET CF are all in the system.data.sqlserverce namespace, where the names are similar to the mode of operation and the .NET Framework, System.Data.sqlClient. Let's take a look at it, how to create a SQL Server CE database: // Create a SQL Server CE database
Private Void CreatedBBTN_Click (Object Sender, System.Eventargs E)
{
IF (file.exists (dbnametextbox.text)
File.delete (DBNAMETEXTBOX.TEXT);
String connString = "data source =" DBNAMETEXTBOX.TEXT;
SQLCEENGINE = New SQLCEENGINE (ConnString);
Engine.createdTabase ();
// CREATE TABLE
Sqlceconnection conn = new sqlceconnection (connString);
Cn.open ();
SqlceCommand command = conn.createcommand ();
Command.commandtext = "CREATE TABLE HUMAN (ID Int Primary Key, NAME NTEXT, AGE INT, ADDRESS NTEXT";
Command.executenonQuery ();
CONN.CLOSE ();
Messagebox.show ("Database Creation Success");
}
In this code, we first check if the SDF file exists, if there is, delete the existing database file (extension SDF). Then construct the database connection string based on the given SDF file name, create a SQLCEENGINE object, and call the SQLCEENGINE's CREATEDATABASE method to create a database. The last part of the code created a table called Human using SQLCECONNECTION and SQLCECMMAND.
Ok, create a good SQL Server CE database, which will take the data on the Web Service. Let's see the implementation code of the Fill button.
The first step is to create a Web Service access object and request a DataSet database to the Web Service. Not much explanation.
Private Void Filldsbtn_Click (Object Sender, System.Eventargs E)
{
// Get Web Service DataSet
Wolf.service1 service = new sqlsmartclient.wolf.service1 ();
Service.setDatabaseType (M_DatabaseType);
Service.setdbconnectionstring (m_connectionstring);
Service.create ();
DataSet DS = Service.executedataTaset (SqlTextBox.text);
In the second step, we set up a database connection string according to the above, and create a SQLCECONNECTION and SQLCECMMAND objects, it looks very familiar, isn't it?
// Get SQL Server CE Database Dataset
String connString = "data source =" dbnametextbox.text; sqlceconnection conn = new SQLCECONNECTION (ConnString);
Cn.open ();
SqlceCommand command = conn.createcommand ();
In the third step, we get a DataRow array from the first DataTable object of DataSet. If the number of DATAROW is not less than 1, there is data in the DATAROW array. We loop to the value of each field through the Foreach keyword of the C # language, then store it into the corresponding String object, and then consist of these String objects into SQL statements. Finally, these SQL statements are performed with SQLCECMMAND objects.
// build SQL Command
String SQL, ID, Name, AGE, Address;
DataRow [] currrows = ds.tables [0] .select (null, null, dataviewrowstate.currentrows);
Currrows.Length <1)
Return;
Foreach (DataRow Myrow IN Currrow)
{
ID = Myrow ["id"]. TOSTRING ();
Name = myrow ["name"]. TOSTRING ();
AGE = Myrow ["age"]. TOSTRING ();
Address = Myrow ["address"]. TOSTRING ();
SQL = "INSERT INTO HUMAN (ID, Name, Age, Address) VALUES (" ID ", '" Name "" " AGE ",' " Address " ";
// Execute SQL Command
Command.commandtext = SQL;
Command.executenonQuery ();
}
CONN.CLOSE ();
Messagebox.show ("Conversion Finish");
}
Finally close the connection of SqlceConnection, the entire data synchronization process is completed. We look through the query analyzer comes with SQL Server CE to see the synchronized data, and the same in the Access table. Of course, we can also synchronize the data in the SQL Server CE to the remote database, and it will not be repeated here.
Written in the last
Ok, we have completed some long web service experience. From the implementation of the first Web Service, to the SQL query analyzer connected to different databases, we have finally found that our SQL Server CE can connect the Access database, of course, can also be any database you want.
It is only a short code that brings us these wonderful experiences. It seems that the most popular Web Service and DataSet are not difficult in your program, right?
So try the Web Service to your next mobile app.
About author
