Implementation of Web Service database access middleware

Web|web Services | access | data | database

Absrtact: This paper analyzes the current situation of database access middleware, points out the existing problems, and draws the necessity of applying new technology. A database access middleware WSDBM based on Web service technology is developed, and the effectiveness of the middleware is validated by an application example.
Keywords: Web services; database access middleware;. Net

1 Introduction
With the rapid development of Intranet/internet network, the network-oriented distributed database becomes the key to support Internet service, and the traditional database access technology has been unable to meet the needs of distributed application integration.
"1" The research and development of new database access middleware is one of the main directions in the field of database research by using new technology.
Web Service is a new distributed computing model, based on a series of open technical standards, its loose coupling, language neutrality, platform independence and openness make it become the next generation of e-business architecture, become the next generation of www.
"2" Microsoft.NET was provided based on ". NET Framework, which provides a separate, unified programming model involving clients, servers, and services.
“. NET Framework, together with Visual Studio.NET, provides us with a complete platform for application development.
The main work of this paper is to use Web services to encapsulate the existing database access mode into middleware on the Visual Studio.NET platform, so that the encapsulated middleware can meet the needs of distributed applications.

2 Definition of Web Service middleware interface
The existing common database access method, its main operations are: Set up the database connection, open and close the database, execute queries and execute other SQL statements, implementation of transaction management and buffer pool management. The more advanced database access methods also support real-time database connections. Therefore, in order to encapsulate traditional database access, we first define a Web service that should at least have the Web methods listed above (the Public function member with the property WebMethod). These methods form the interface of this Web service.
. NET, the implementation of WEB services is encapsulated in the System.Web.Services.WebService class. In order to encapsulate a Web service that accesses the database, we must define a new class that inherits from WebService or its subclasses. For relatively simple web services, we simply let this class inherit from WebService. This generated class becomes a specific Web service.
. NET, the new relational data management classes are based on a series of namespaces in the class library, often called System.Data as ado.net. System. Data contains the underlying objects that are used to access and store relational data. Visual Studio.NET directly provides us with the System.Data.SqlClient and oledbclient two namespaces that contain the basic objects needed to access the SQL Server relational database and other relational databases. The classes that end with transaction provide transaction management functionality, and classes that end with connection are used to connect to specific databases. A class that ends with command defines the operation of a database table.

Implementation of 3 Web service middleware
3.1 Implementation Framework
The following is a simple Web services middleware implementation (c #语言来编写, recommended by Microsoft). In order to realize the scalability of middleware, we decided not to directly implement the operation of the database in the middleware WebMethod (Web method). We first define a virtual class Dboperator, which defines the interface to the database operation in this virtual class, including the opening (open) and closing (close) of the database, the start of the transaction (BeginTrans), A transaction's submission (CommitTrans) and rollback (RollbackTrans), execution of an SQL statement that returns no value (void execsql), and execution of an SQL statement that returns a dataset (a DataSet Execsql). In this way, each WebMethod of our web services middleware simply invokes the corresponding member function of the Dboperator object. For example, the open method in the Web services middleware is directly calling the Dboperator object's Open method.
3.2 implementation of specific database operations
In order to implement the operation of the database, we need to define the subclass of the Dboperator class, we take sqldboperator as an example, obviously sqldboperator implement the operation of the SQL Server database.

First, you define a Sqldboperator class that inherits from Dboperator and imports the namespace system. Data. SqlClient.
Using System.Data.SqlClient;
Class Sqldboperator:dboperator

Second, add private data members Conn,trans,intransaction,comm
Private SqlConnection Conn; Database connection
Private SqlTransaction Trans; Transaction Processing Class
private bool Intransaction=false; Indicates whether the current is in a transaction
Private SqlCommand comm; Database Operations Command Handler class

Then we start writing the implementation statement of the open operation inherited from Dboperator.
public override void Open (string connstr)
{
If the connection is empty, construct a connection through CONNSTR first
if (conn==null)
Conn=sqlconnection (CONNSTR);
If this connection is not turned on, open the connection
IF (Conn. State.tostring (). ToUpper ()!= "OPEN")
This.conn.Open ();
}

After the open (open database) operation was written, we started writing close (closing the database) operation.
public override void Close ()
{
If the connection is empty, do not close
if (conn==null)
Return
If this connection is turned on, close this connection
IF (Conn. State.tostring (). ToUpper () = = "OPEN")
This.conn.Close ();
}

Implementation of transaction processing: the first is the implementation of the BeginTrans (Start transaction) operation.
public override void BeginTrans ()
{
If the connection is empty, then no transaction can begin
if (conn==null)
Return
/* Start conn the transaction to which it belongs and save the transaction, setting the Intransaction (in transaction) flag to True (true). The transaction is being processed.
Trans=conn. BeginTransaction ();
Intransaction=true;
}

The following is the implementation of the CommitTrans (Submit transaction) operation:
public override void CommitTrans ()
{
Trans.commit (); Transaction submission
intransaction=false;//the flag in the transaction turns false;
}

Similarly, the implementation of the RollbackTrans (ROLLBACK TRANSACTION) operation is as follows:
public override void RollbackTrans ()
{
Trans. Rollback (); Transaction rollback
intransaction=false;//the flag in the transaction turns false;
}

Finally, we write the implementation of the EXECSQL (Execute SQL statement) operation:
public override void Exesql (string strsql,string[] strparams,object[] strvalues)
{
If Comm (the object that handles the SQL statement) is empty, the object is generated
if (comm==null)
Comm=new SqlCommand ();
Sets the connection for this object conn
Comm. Connection=this.conn;
To determine whether in a transaction, set the Comm Transaction object property
if (intransaction)
Comm. Transaction=trans;
To determine whether the number of parameters and the number of parameter values are equal, can not execute this SQL statement
if ((strparams!=null) && (strparams.length!=strvalues.length)
throw new Exception ("Query parameters and values do not correspond!");
Set the command text for this Comm object
Comm.commandtext=strsql;
Save the parameter name and corresponding parameter values in the Comm parameter array
if (strparams!=null)
{
for (int i=0;i<strparams.length;comm. Parameters.Add (Strparams[i],strvalues[i]));
}
Execute this query with no return value
Comm. ExecuteNonQuery ();
}

The following is an implementation of the Execsql operation with the return value:
public override DataSet Exesqlfordataset (string querystring)
{
If Comm (the object that handles the SQL statement) is empty, the object is generated
if (comm==null)
Comm=new SqlCommand ();
Sets the connection for this object conn
Comm. Connection=this.conn;
To determine whether in a transaction, is to set the Comm Transaction object property
if (intransaction)
Comm. Transaction=trans;
Generates a DataSet object (DS) that is used to save the returned query results
DataSet ds = new DataSet ();
Defines an object ad for a Sqldataadpater class.
SqlDataAdapter ad = new SqlDataAdapter ();
Set the command text for this Comm object
comm.commandtext=querystring;
Set the SelectCommand property for AD to Comm.
SelectCommand is a property of a DataAdapter object that represents a Transact-SQL statement or stored procedure that is used to select records in the data source.
Ad. SelectCommand =comm;
The ad performs a fill operation and the result is saved to the DS
Ad. Fill (DS);
DS return
return DS;
}

4 use of defined Web services middleware to Access database usage
Let's assume a specific scenario for this middleware use:
Assuming that there is a private, relatively large bookstore, the owner of the bookstore is a distance from the bookstore, the bookstore and the owner of the network between the establishment of the LAN is not realistic, and the boss needs at home can use the bookstore management system, even the day-to-day management. That is to say, the boss needs to realize his home office. To this end, the bookstore owner decided to let an IT company to develop the system.
An IT company after receiving the development intention of the bookstore owner, after conducting preliminary system research, decided to take this it order, because this bookstore owner did not propose the concrete solution (he also does not understand), therefore the IT company personnel thought the following several development plans:
In the traditional solution, we can adopt the Dynamic Web page programming method, that is, to build a Web site, so that anywhere on the Internet, I can access through this site, this approach is very good, many existing corporate portals are using this technology to achieve. But one drawback of this technique is that bookstores need to have their own web services, which increases the cost of implementation. At the same time, the implementation of this solution also abandoned the existing solution (the bookstore in the early days of the establishment of a special IT company to develop a dedicated bookstore management system), abandoned this legacy (Legacy), redesign, bookstore owners do not want to.
Another traditional solution is to use CORBA or DCOM for programming. The original system is programmed with Visual C + +. So you can choose to program DCOM, which is also very reasonable. However, this scheme limits the implementation of the platform, while the development of higher costs, after all, the use of DCOM programming and implementation is a more complex work.
Finally, the company decided to use Web services technology to encapsulate the original database access layer, so that our clients only need to make database access layer changes. In this way, the original interface of the system is inconvenient, the business layer (business layer) unchanged, the change is only the implementation of the client's database access layer. The above is the specific occasion for the use of this Web service.
Below we use the C # Windows application Programming project on the Visual Studio.NET platform to illustrate the use of this Web service.
Start by creating a new C # Windows Application project, adding controls to the form, a DataGrid, and a button.
Then add a Web reference that adds the ASMX file address of the Web service middleware to the Web reference. So we can use the service directly.
Double-click button 1 to program as follows:
Create a new instance of a Web service
WEBREFERENCE.SERVICEWSDBM the=new WindowsApplication1.WebReference.ServiceWSDBM ();
Open the database to which the connection string above can be connected, using the database connection string as a parameter
The. Open ("Provider=SQLOLEDB.1; Persist Security Info=false; User id=sa;initial Catalog=northwind; Use Procedure for prepare=1; Auto translate=true; Packet size=4096; Use encryption for Data=false; Tag with column collation when Possible=false ");
The. Open ();
Returns the recordset by querying the database that is already open.
System.Data.DataSet ds=the.exesqlfordataset ("SELECT * FROM Products");
Display a query's recordset in a DataGrid
Datagrid1.datasource=ds;
Datagrid1.datamember=ds. Tables[0]. TableName;
Close this connection
The. Close ();
Execution results, and the contents of the Products table in the SQL Server library on the connection are displayed in the DataGrid.
5 concluding remarks
According to the above introduction, the database access middleware based on Web service can extend the application scope of database system, this kind of middleware not only adapts to LAN, but also adapts to the future wide area network application. Compared with traditional middleware, this middleware has the following advantages: 1 cross-platform. 2) Easy to use, can be used as a local component of the Web services can be applied. 3 compatibility, expansion convenience, for example, we want to increase support for Oracle, only need to download and Oracle-related. NET plug-ins (download address: Can be downloaded to http://msdn.microsoft.com/downloads/. Net Framework Data Provider for Oracle), and then replace the SQL in the above program with Oracle. To sum up, the implementation of database access middleware based on Web services will be a more effective solution.