web| Connection database using Web service connection database in Pocket PC
Objective
Microsoft's Mobile Developers Conference was held in Beijing in June, and while domestic mobile apps are just getting started, the broad prospects are being watched by more and more software vendors. Commercial applications on mobile devices, although just beginning, have shown great potential for growth.
In the Microsoft Mobile Development Challenge, my work, "gluttonous year of the Year Wireless catering management system" has won the third prize in commercial applications. In order to connect the Pocket PC to the back-end database server, my work uses the. Net compactframework and Web service technology, and I'll share the core principles of implementation. In this article, there is no code for "The Year of Gluttony", I use another program "SQL Query Analyzer" as a demo example.
In the field of embedded database, the world's major database vendors have provided their own mobile solutions, such as Microsoft's SQL Server CE, Sybase ianywhere, IBM's DB2 Everyplace and so on. Although each vendor provides a solution for data synchronization, and the principle of implementation is similar, each data synchronization scheme is aimed at its own database and cannot be compatible with other database products.
So is there any way that you can access a variety of databases through a mechanism? Microsoft's Mobile 2003 has fully supported the. NET Compact framework development, while. NET provides good support for Web services, and developers can easily develop service programs and client applications for Web service.
This article discusses a solution that accesses multiple databases through a Web service on a Windows Mobile2003 platform. This is certainly not the best solution for mobile devices to access multiple remote databases, but I hope this is a better solution with existing technologies.
System requirements: Visual Studio. NET 2003
Pocket PC 2003 Simulator
IIS 5.0
First Web Service
At present, the Web service has been widely concerned, the mainstream development tools for Web service development provides a good support. We use visual Studio here. NET 2003来 make a simple Web service instance, so that programmers who do not understand the development of Web service to better enter the discussion of the following topics.
Let's start by creating a asp.net Web Service project: Open the File menu, select New, and then project. In the New Project dialog box, Project types selects Visual C # Projects, and templates selects ASP.net Web Service. Location Select the location and name of your Web service, because I have the IIS service configured on my machine, I deploy the Web service directly on this computer. Please see figure I.
asp.net Web service engineering is very similar to ASP.net, except that the paging file extension is asmx, not aspx. Next, we'll create a WebMethod for the web Service. Open the Service1.asmx.cs file and add two Webmethod:yourname and welcom to class Service1, as follows (bold code added):
Namespace WebService1
{
public class Service1:System.Web.Services.WebService
{
......
[WebMethod]
public string YourName ()
{
Return ' My Name is wolf! I'm the old wolf! ";
}
[WebMethod]
public string welcom (string yourname)
{
String str = yourname+ ", Welcome to Wolf's Web service";
return str;
}
}
}
It's important to note that each WebMethod must be public, and it needs to be in front of the function
Add the [WebMethod] statement. The Yourname function returns a string object that contains English and Chinese, primarily to test whether the string of Web service can be realistic on a windowsce platform that supports Unicode. The welcom function adds a parameter to test whether a string does not display correctly between the Web service and the WindowsCE platform.
Then we select the Start command in the Debug menu (or press F5) to run the Web Service, which appears as follows: (see figure II)
As you can see, the names of the two functions we have just written are displayed. You can click on the function name to view WebMethod soap and HTTP post and so on, you can also click the Invoke button directly to see if the WebMethod returned the correct results.
If your Web service works properly, next, we create a smart device application that invokes our Web service under the Pocket PC 2003 environment. If you are not familiar with how to create a smart device application under Visual Studio. NET 2003, you will want to refer to the following instructions.
Open a new Vs.net IDE environment, open the File menu, select New, and then project. In the New Project dialog box, Project types selects Visual C # Projects, select "Smart Device Application" in the templates, and then click OK.
In the Smart Device Application Wizard dialog box, there are two list boxes. Above a list box to select the target platform for the program, depending on how you develop the SDK installed on the machine can display different options. The Pocket PC includes the Pocket PC 2002 and Pocket PC 2003;windows CE including other WINDOWSCE platforms that support the. NET Compact Framework. If you have a Smartphone SDK installed, the smartphone option is also shown here.
The following dialog box selects the type of application you are creating, including Windows applications, class libraries, non-graphical applications, and empty projects. The options here vary slightly depending on the platform you choose. Another point to note is that if you have a mobile Internet Toolkit installed, you can also use this wizard to create ASP.net Web programs that target platforms for mobile devices.
We are here to select "Pocket PC" and "Windows application" and click OK. We can start a mobile development tour.
Let's take a look at the panorama of the IDE's environment: in fact, the development environment of the WinForm program is similar. But please pay attention to the picture in the Drop-down menu, have done windowsce development of friends must be very familiar with it. By the way, this is the menu that selects the application output device. We choose "CHS Pocket PC 2003–sdk Emulator" Here, we can select the Connect to Device button next to the Pull-down menu to start the simulator, or wait until the program is run to start the simulator.
Note that Visual Studio. NET 2003 installs the Pocket PC 2002 SDK By default, and you need to install a Pocket PC 2003 SDK and a Chinese image to see this. However, it is no problem to debug the following application under the Pocket PC 2002 emulator.
Next, we'll add the Web References, select "Add Web References" Under the Project menu, or right-click the Web References in Solution Explorer and select "Add Web" from the pop-up menu References ". We will see the following dialog box:
In the URL to fill out the Web service URL, remember when we run the Web service in the IE Address bar URL? Http://localhost/WebService1/Service1.asmx. Is that right? This is true if it is a WinForm program, but in the smart device application it is wrong to write. Please note that the topic discussed below is important: the emulator for the Windows CE device, while running on your PC, is actually connected to your PC as a remote device. So, if you write "localhost" or "127.0.0.1", then the program will access the simulator, not the PC you use to develop it. So, here you have to fill in the actual machine name or IP address of your PC on the network. So here you should fill in the Http://yourname/WebService1/Service1.asmx (yourname indicates your actual machine name). Here, if you fill out the localhost, the development environment will find the Web References for you, but there will be an exception when calling the Web service. OK, fill in the URL, press the Go button, if the URL is correct, then the information on the above image will be displayed, then click the Add References button. This will allow us to use this Web service in our project.
We open the Interface Editor, add two button and a textbox to call two WebMethod. The code is as follows:
Yourname is the name of the Web service, which is the equivalent of a namespace, so we have to create an object for the Service1 class in Yourname, and then call the method in Service1. As you can see, the procedure for calling Web server in a C # program is very similar to the process of creating a local class and calling its methods. In addition, due to the impact of network factors, the call Web service is not necessarily successful, so in the development of commercial software need to add exception handling code, here for the simplicity of the program omitted. The results of the program operation are as follows:
SQL Query Analyzer
After understanding the development and invocation of basic Web service, we can go into the discussion of the core content. Web service is based on an XML language, and it is this feature that makes Web service a bridge between disparate systems. So is there a simple way to pass data across different databases? The ASP.net Web service supports returning dataset objects, which can be supported by a variety of data representation controls, such as the DataGrid, which is the bridge we are looking for.
In this section, I will create a SQL Query Analyzer, unlike other query parsers, I can specify the type of database to connect to when I call, so that I can connect to different databases. Let's start by creating the Web Service section of SQL Query Analyzer:
Let's take a look at the UML diagram for SQL service. We followed the Service1 class in the SQL service above and added five WebMethod to it, whose name and function are described in the following table:
Name
Function
Setdatabasetype
Set the type of connection database
Setdbconnectionstring
To set the connection string for a database
Create
Create a Database Action object
ExecuteNonQuery
Execute an SQL statement that does not return results
ExecuteDataset
Executes the SQL statement and returns a dataset
In the Service1 class for Web service, we first declare that three variables are used to store database operation objects, database connection strings, and database types separately. Note that we declare the variable to be static in order for the client to perform a different WebMethod, access to the same database Operation object and string object. Dboperater is the base class for database operations classes, and wait a moment we create an instance of the Dboperater derived class.
private static Dboperater M_dboperater;
private static string M_databasetype;
private static string m_dbconnectionstring;
The Setdatabasetype and setdbconnectionstring two WebMethod are methods provided to set the database type and the database connection string. The special reminder here is that it is a dangerous practice to pass a database connection string as a Web service parameter to the network in plaintext, and this is done only for the simplicity of the sample program. It is strongly recommended that readers protect their database connection strings in a better way 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 creation method is used to create an object of the corresponding Dboperater derived class based on M_databasetype. It should be explained here that the database type (Setdatabasetype) is set with a string as a parameter instead of an enum type or other type, so that you do not have to modify the existing client source code later to add support for the new database.
[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 the Dboperater derived class object, while the actual database operations are implemented in Dboperater derived classes. This enables better separation of database operations from client operations.
[WebMethod]
public bool ExecuteNonQuery (String sql)
{
return m_dboperater.executenonquery (SQL);
}
[WebMethod]
Public DataSet ExecuteDataset (String sql)
{
return m_dboperater.executedataset (SQL);
}
We encapsulate the concrete methods of database operations into the Dboperater class and derive the Dbsqlserveroperater and Dbaccessoperater classes from the Dboperater class, which are used for SQL The operation of the server database and the Access database. The Dboperater class exposes two virtual methods: ExecuteNonQuery () and ExecuteDataset (), which are responsible for overloading implementations by derived classes. The derived classes also expose their own constructors, which are used to get the connection database strings.
The following is the implementation code for the Dboperater class, and you can see that the two methods in the Dboperater class use the virtual keyword and declare it as a virtual function.
Database Operations 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 the Web service, the only dboperater derived classes that really do database operations are the Dbsqlserveroperater classes we see below, Use the database operations class in the System.Data.SqlClient namespace to store operations on the SQL Server database. The Dbaccessoperater class operates on OLE DB data, using classes in the System.Data.Oledb namespace.
SQL Server Action Class
public class Dbsqlserveroperater:dboperater
{
Public Dbsqlserveroperater (String dbconnectionstring)
{
M_sqlconnection = new SqlConnection (dbconnectionstring);
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;
}
OLE DB Operations Class
......
This omits the access Operation class code that is essentially the same as the SQL Server action class. See related books for implementation and considerations of overloading and inheritance in C #.
Because operations on the database have been encapsulated on the Web service side, the client code is relatively simple to implement. Smartsqlclient contains two property pages, the first property page is setting, set the database type to query and the database connection string, after setting, click the Setting button, the program will save the database type and connection string, and automatically switch to the second property page.
The Second property page query, which includes a textbox, is used to write SQL query statements, a DataGrid to display the database from the Web service, and two button to perform the SQL query that returns the dataset and the operation that does not return the result set.
Now let's take a look at the response function of the Execdataset button, which first creates a Web service to access an instance of the object, then sets the database type and database connection string according to the settings in setting, and then creates an object instance of the corresponding database action class. and call the Web service's ExecuteDataset method, passing a valid SQL query statement to the Web Service,web service returns a DataSet object, Our client program displays the first table in the dataset to the DataGrid control.
Well, here we have a SQL Query Analyzer that can operate remote SQL Server and Access databases on the PDA side. With. NET database operations components, you can fully implement the Oracle, DB2 and other popular relational database support, and on this basis, to implement more mobile device based applications.
Interacting with SQL SERVER CE
The SQL Query Analyzer we implemented above is not only about the convenience of retrieving different types of databases on mobile devices, but also the way the Web service is more realistic.
As we all know, the database that is mainstream on the Windows CE platform is the SQL Server Ce,sql server CE Exchange data with the server in two main ways: Remote data Access (RDA) and replication. These two methods of data synchronization require not only a dedicated IIS environment, but also data synchronization with the SQL Server database. This greatly increases the difficulty of system integrators using other types of databases to develop mobile device applications and is not conducive to the wider acceptance of the Windows CE platform.
In the following time, I will demonstrate how to use Web service to implement data synchronization between SQL Server CE and access based on the Web service above. Of course, my implementation is not yet up to the capability of RDA to replicate the table structure synchronously.
To complete this part of the code, we add a property page in Sqlsmartclient, named SqlServerCe. The top textbox and Createdb buttons are intended to create the SQL Server CE database and the tables in the database, and later we'll introduce their code implementations. The following Fill button accomplishes tasks such as submitting the SQL statement in the second textbox to the Web service, which is responsible for providing a dataset containing the corresponding data. The dataset is then parsed and inserted into the SQL Server CE database, grouped into the appropriate SQL statements.
Let's begin by introducing the operation of SQL Server CE in the. Net Compact Framework. The action classes for SQL Server CE in the. NET CF are all in the System.Data.SqlServerCe namespace, where the names of the classes are similar to the System.Data.SqlClient in the. NET Framework. Let's take a look at how to create a SQL Server CE database:
SqlCeEngine engine = new SqlCeEngine (connstring);
Engine. CreateDatabase ();
Create Table
SqlCeConnection conn = new SqlCeConnection (connstring);
Conn. 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 succeeded");
}
In this code, we first check that the SDF file exists and, if it exists, delete the existing database file (the extension SDF). It then constructs the database connection string based on the SDF filename given, creates the SqlCeEngine object, and invokes the SqlCeEngine CreateDatabase method to create the database. The last part of the code uses SqlCeConnection and SqlCeCommand to create a table named human.
Well, create a SQL Server CE database and take the data off the Web service. Let's look at the implementation code for the Fill button.
The first step is to create the Access object for the Web service and request the dataset database to the Web service. No more explanations.
The second step is to make a database connection string based on the SQL Server CE database created above, and create SqlCeConnection and SqlCeCommand objects that look familiar, don't they?
SqlCeConnection conn = new SqlCeConnection (connstring);
Conn. Open ();
SqlCeCommand command = conn. CreateCommand ();
In the third step, we get the DataRow array from the first DataTable object in the dataset, and if the number of DataRow is not less than 1, it means that there is data in the DataRow array. We loop through the C # language's foreach keyword to get the value of each field and then store it in the corresponding string object, which is then composed of the string objects in the SQL statement. Finally, execute the SQL statements with the SqlCeCommand object.
Build SQL Command
String sql,id,name,age,address;
datarow[] Currrows = ds. Tables[0]. Select (null, NULL, dataviewrowstate.currentrows);
if (Currrows.length < 1)
Return
foreach (DataRow myrow in Currrows)
{
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 complete");
}
Finally, the SqlCeConnection connection is closed and the entire data synchronization process is completed. We looked at the synchronized data from the SQL Server CE Query Analyzer, exactly the same as in the Access table. Of course, we can also synchronize the data in SQL Server CE to a remote database, which is no longer repeated here.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
