Create Database Web Services

The most obvious use of XML Web Services is universal data access. With it, you can access your company's database by many clients on the Internet, or dynamically import it to a third-party Web site, you can even allow your business partner's Web Services to query. The following explains how to create a simple Web service to display your database content to Internet Explorer, third-party Web Services, and custom C # and VB. NET clients.

Partners, customers, and employees have a wealth of experience in using data designed for multiple devices. No matter how your current database is organized, to ensure universality, web Services returns XML data to the client. For example, if a Logistics Company (your partner) wants to deliver your goods to your customers, when the delivery arrives at the customer's door, his PDA displays the information about the change of the departure address. At this time, the freight car easily delivers the information to other places, because your customer has changed his address in the database, this change is automatically updated in your partner's system.

Next, write your own ASP. NET database Web Services. First, check your database to see if it can easily output data in XML format and whether ADO. NET can be read and dynamically converted. In some cases, you may need to convert the current database to meet this requirement. If your database access code becomes complex and affects scalability, we recommend that you convert the database.

For simplicity, we assume that the database in the example 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,

Right-click Service1.asmx and rename Service1.asmx to DataBaseWebService. asmx. This file will contain WebMethods that obtain data from the database. Then, right-click and select "view code" to switch to the Code view and change it to the DataBaseWebService class and constructor name.

Reference the. NET class library at the beginning:

using System.Data.SqlClient;using System.Data.OleDb;

Then change the class name to DataBaseWebService:

Public class DataBaseWebService: System. web. services. webService {public DataBaseWebService () {// CODEGEN: This call is ASP. initializeComponent ();} //...} required by the NET Web service designer ();}//...}

Write your own method code at the end of the Hello World method. The first method SQLDB is used to access the SQL Server database, which processes the SQL Server Query sent by the client, SQLDB parameters are the query statements sent from the address bar of the browser. All the WebMethod code has a try/catch Statement, which is used to handle some error messages when the query fails. If the WebMethod method has exceptions at runtime, the catch statement generates a dataset, which is an Error table containing Error information.
The SQLDB method first creates and opens the sqldatabase connection. The connection string must be unique on your server. For example, we use Visual Studio. the example database that comes with the installation. Next, create an SQL data adapter using the SQLDB method. The parameter QUERY is used to determine the data records to be returned. The dataset that generates the QUERY results is in XML format, and use Results as the root node. The Code is as follows:
[WebMethod]public DataSet SQLDB(string Query){    try    {        SqlConnection CS =             new SqlConnection("server=(local)//NetSDK;database=Northwind;Trusted_Connection=yes");        SqlDataAdapter myCommand = new SqlDataAdapter(Query, CS);        DataSet myDataSet = new DataSet();        myCommand.Fill(myDataSet, "Results");        return myDataSet;    }    catch (Exception ex)    {        return DataError(ex);    }}

The method used to query the ACCESS database is basically the same as that of SQL. For the convenience of testing, all the code is as follows:
[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;    }    catch (Exception ex)    {        return DataError(ex);    }}

Finally, write the method to handle the error:
public 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;}

Now you can compile the project to see if your Web Services work properly. The result is as follows:
Then select your database type, as shown in:
Select AccessDB. (Note: Before performing this operation, create the database AccessWebServices. mdb, create the table AcessTableTest, and put it under the DataBaseWebService directory), input "select * from AcessTableTest" in the Query, and then click "Invoke ", you will get a query result in XML format, as shown below:
If similar results are displayed, your Web Services can be used.
If you use XSL, you can generate HTML pages that can be viewed. You can also enter http: // localhost/DataBaseWebService. asmx/AccessDB in the address bar? Query = select + * + from + AcessTableTest to get the desired data.
Use C # To write a client application that uses the Web Services. Create a VS. NET project for a Windows application named WebServicesClient. Right-click the solution browser and choose add Web reference. In the displayed dialog box, enter:
Http: // localhost/DataBaseWebService. asmx
Then click "add reference". VS. NET will add the required files to your project. Add a menu on From1 and add two menu items, "Get SQL Server product list" and "get Access product list". Use the Web Services we just created, create an instance of Web Services as follows:
 
private void menuItem1_Click(object sender, System.EventArgs e){    WebServicesClient.localhost.DataBaseWebService Database         = new WebServicesClient.localhost.DataBaseWebService();    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        = new WebServicesClient.localhost.DataBaseWebService();    DataSet ds = Database.AccessDB("select * from AcessTableTest");    dataGrid1.DataSource = ds.Tables[0];}

Finally, run the newly created Window application to obtain the data in the database we just mentioned. As shown in: