How to create a database Web Services

services|web| Create | data | database

One of the most obvious uses of XML WEB Services is universal data access. With it, you can access a company's database to many clients on the Internet, or you can dynamically import it to a Third-party Web site, or even allow your business partner's Web services to query. Let's explain how to create a simple Web services that displays your database content to Internet Explorer, Third-party Web services, and custom C # and vb.net clients.

Partners, customers, and employees already have considerable experience in using data designed for a variety of devices, regardless of how your current database is organized, and to ensure versatility, Web Services returns XML-formatted data to the client. For example: If a logistics company (your partner) is ready to ship your goods to your customers, when the delivery car arrives at the client's door, his PDA displays information about the change of address, and the van is easily transported to another location because your client changed his address in the database. This change is automatically updated automatically in your partner's system.

Below, you start writing your own ASP.net database web Services. First, check your database to see if it can easily output XML-formatted data and see if ado.net can be read out and dynamically transformed. In some cases, you may need to convert your current database to meet this need. If your database access code becomes so complex that it affects scalability, it is recommended that you convert the database.

For simplicity, here's a hypothetical example where the database has only one "products" table. Of course, your database may have many tables, or your Web services may need to access more than one database.

Now, we can start writing code. Open visual Studio. NET and create a C # asp.net WEB services project under the Databasewebservice directory, as shown in the figure:

Right click on the Service1.asmx, the Service1.asmx renamed to Databasewebservice.asmx, this file will contain the data from the database webmethods, and then, right click, select "View Code", Switch to Code view, and change to the name of the Databasewebservice class and constructor.

First reference at the beginning. NET's class library:

<xmp>using system.data.sqlclient;using System.Data.OleDb;</xmp>

Then change the name of the class to Databasewebservice:

Public <xmp>class Databasewebservice:system.web.services.webservice{public Databasewebservice () {//codegen: This call is ASP. InitializeComponent () required by the NET Web Service designer (); ...} </xmp>

Write your own method code at the end of the Hello World method, the first method sqldb to access the SQL Server database, which handles the SQL Server queries sent by the client, the query sqldb the parameters from the browser address bar, The code for all WebMethod methods has a Try/catch statement that handles the output of error messages when the query fails. If the WebMethod method has an exception at run time, the catch statement produces a dataset, which is an error table that contains incorrect information.

The Sqldb method first creates and opens the SQL database connection, and the connection string should be unique on your server, and as an example, we use Visual Studio. NET installation; Next, the Sqldb method creates a SQL data adapter that the parameter query uses to determine which data records to return, a dataset that produces the result of the query, and an XML format, with results as the result of the root node. The code is as follows:

<xmp>[WebMethod] public DataSet sqldb (string Query) {try {SqlConnection CS = new SqlConnection (' server= (local) \\NetSDK ;d Atabase=northwind; Trusted_connection=yes "); SqlDataAdapter mycommand = new SqlDataAdapter (Query, CS); DataSet myDataSet = new DataSet (); Mycommand.fill (myDataSet, "Results"); return mydataset; The catch (Exception ex) {return DataError (ex);}} </xmp>

The method used to query an Access database is basically the same as SQL, and for everyone to test it, all the code is as follows:

<xmp>[WebMethod] public DataSet accessdb (string Query) {try {string straccessconn = ' provider=microsoft.jet.oledb.4.0; Data source= "+ this. Server.MapPath ("Accesswebservices.mdb"); OleDbConnection myaccessconn = new OleDbConnection (Straccessconn); OleDbCommand Myaccesscommand = new OleDbCommand (query,myaccessconn); OleDbDataAdapter mydataadapter = new OleDbDataAdapter (Myaccesscommand); Myaccessconn.open (); DataSet myDataSet = new DataSet (); Mydataadapter.fill (myDataSet, "Results"); Myaccessconn.close (); return mydataset; The catch (Exception ex) {return DataError (ex);}} </xmp>

Finally write the method that handles the error:

Public <xmp>DataSet DataError (Exception ex) {DataSet errds = new DataSet ("Errors"); DataTable errtable = ErrDS.Tables.Add ("Error"); ERRTABLE.COLUMNS.ADD ("message"); ERRTABLE.ROWS.ADD (new object[] {ex. Message}); return errds; } </xmp>

Now you can compile the project to see if your Web services are working properly. If it works, the result will be as follows:

Then select your database type, as shown in the following illustration:

Select Accessdb (Note: Before you do this, create a database Accesswebservices.mdb and set up a table acesstabletest and place it under the Databasewebservice directory), and in query, enter the SELECT * from Acesstabletest, and then click Invoke, and you'll get an XML-formatted query that shows the following:

If a similar result is shown above, your Web services can use it.

If you cooperate with XSL, you can create an HTML page that you can browse, and you can enter it directly in the address bar: Http://localhost/DataBaseWebService/DataBaseWebService.asmx/AccessDB? Query=select+*+from+acesstabletest get the data you want.

A client application using this Web service is written in C # below. Create a new Vs.net project for a Windows application named Webservicesclient, right-click on the Solution browser, select Add Web Reference, and in the pop-up dialog box, enter:

<xmp>Http://localhost/DataBaseWebService/DataBaseWebService.asmx</xmp>

Then click "Add Reference", VS. NET will add the required files to your project. Add a menu on From1, add two menu items, get the SQL Server products list, and get access products list, and to use the Web services we just created, create an instance of Web services first, as follows:

<xmp>private void Menuitem1_click (Object Sender,system.eventargs e) {WebServicesClient.localhost.DataBaseWebService Database =</xmp> <xmp>new WebServicesClient.localhost.DataBaseWebService ();</xmp> <xmp>DataSet ds = DATABASE.SQLDB ("SELECT * FROM Products"); DataGrid1.DataSource = ds. Tables[0]; } private void Menuitem2_click (Object Sender,system.eventargs e) {WebServicesClient.localhost.DataBaseWebService Database =</xmp> <xmp>new WebServicesClient.localhost.DataBaseWebService ();</xmp> <xmp>DataSet ds = Database.accessdb ("SELECT * from Acesstabletest"); DataGrid1.DataSource = ds. Tables[0]; } </xmp>

Finally, running the newly built window application, we can get the data from the database we just lifted. As shown in the following illustration: