XML Web Services A most obvious use is universal data access. With it, you can access the company's database by many of the clients on the Internet, or dynamically import it into third-party Web sites, and even allow your business partner's Web Services to query. Let's explain how to create a simple Web Services, display your database content to Internet Explorer, third-party web services, and custom C # and VB.NET clients.
Partners, customers, employees have considerable experience when using data designed for multiple devices, whether your current database organizes how to organize, to ensure genericity, Web Services returns data to the client. For example, if a logistics company (your partner) is ready to deliver your goods to your customers, when the carriage reaches the customer's door, his PDA shows the information change information, at this time, the truck It is easy to transport it to another place, that is because your customers change his address in the database, which is automatically updated in your partner's system.
Below, you will start writing your own ASP.NET Database Web Services. First, check your database, see if it can easily output data in XML format, see if ADO.NET can read and dynamically convert. In some cases, you may need to convert the current database to meet this needs. If your database accesses the code is very complicated, it will affect the situation of scalability, it is recommended that you convert the database.
For the sake of simplicity, here the database in the example is only one "products" table. Of course, your database may have a lot of tables, or your Web Services needs to access more than one database.
Now, we can start writing code. Open Visual Studio .NET, create a C # ASP.NET Web Services project in the DatabaseWebService directory, as shown:
Right click on Service1.asmx, rename Service1.asmx to DatabaseWebService.asmx, this file will contain WebMethods that get data from the database, then click Right click, select "View Code", switch to code view, change to DatabaseWebService The name of the class and constructor.
First reference .NET's class library:
Using system.data.sqlclient; using system.data.oledb;
Then change the name of the class for DatabaseWebService:
Public class databasewebservice: system.web.services.WebService {public databaseWebService () {// codegen: This call is the initializeComponent () ();} ...}, which is the ASP.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 handles the SQL Server query sent by the client, and the SQLDB parameter transmits the query statement sent by the browser address bar, all The code of the webMethod method has a TRY / CATCH statement that outputs some error messages when the query fails. If the WebMethod method occurs during runtime, the catch statement generates a dataset, which is an error message containing the error message.
The SQLDB method first creates and opens the SQL database connection. The connection string should be unique on your server, as an example, we use the sample database that comes with the Visual Studio .NET installation; Next, SQLDB method creates a SQL data adapter The parameter query is used to determine the data record to return; finally generate the data set of the query result, and a XML format, and the result of Results as the root node. 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 mydatanet;} catch (exception ex) {return dataerror (ex);}}
The method used to query the Access database is basically the same as SQL. For your test, all 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 of handling errors:
Public DataSet DataError (Exception EX) {Dataset Errds = New Dataset ("ErrorS"); DataTable errtable = errds.tables.add ("error"); errtable.columns.add ("message"); errtable.rows.add (Errtable.Rows.Add) NEW Object [] {EX.MESSAGE}; Return Errds;
Now, you can compile the project, see if your web services can work properly. If you work properly, the results will be as follows:
Then select your database type, as shown below:
Select AccessDB (Note: Before making this, create a database AccessWebServices.mdb, and create a table acessTableTest, and put it under the DatabaseWebService directory. Enter "Select * from acessTabletest" in Query, then click "Invoke", You will get a query result in an XML format, which is shown below: If you appear similar results on the map, you will use it.
If you can use the XSL, you can generate the HTML page you can browse, you can also enter directly in the address bar: http://localhost/databaseWebService/Databasewebservice.asmx/accessdb? Query = SELECT * From AcessTableTestSt get you want The data.
Next, a client application that uses the Web Services with C # is written. Create a VS.NET project for a Windows application, named WebServicesClient, right-click on the solution browser, select Add Web reference, enter in the pop-ups:
http://localhost/databasewebservice/databasewebservice.asmx
Then click "Add Reference", and vs.net will add the required files to your project. Add a menu on the FROM1, add two menu items, "Get SQL Server Product List" and "Get Access Product List", you want to use the Web Services we just created, first create an instance of Web Services, as shown below:
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 established Window application, you can get the data in the database we just lifted. As shown below:
