Accessing Data from a Database - XML

A Web service needs to access the exchange rate of currencies against the U.S. dollar for a specified country. This data is stored in the Rates table, as shown in Figure.

The Rates Table.

The Rates Table.

After creating the Rates table, you can connect your Web service to this table, as discussed in the following section.

Accessing Data by Using the SQL Server .NET Data Provider
To connect to a database, Visual Studio .NET provides you with data providers, such as the SQL Server .NET data providers and OLEDB .NET data providers. In addition to connecting to a database, you can use these data providers to execute commands on a database. These commands include accessing data from a database, adding data to a database, deleting data from a database, and modifying data in a database.

When you try to access data from a database by using a data provider, the data is first stored in a DataSet object, which acts as an interface between the Web service and the database. Similarly, when you need to modify the data in the database, the changed data is first stored in the DataSet object, and the changes are then reflected to the underlying database. You learned about the data providers and the DataSet object "Building Web Applications on the .NET Platform.

SQL Server .NET Data Providers

The SQL Server .NET data provider is a lightweight data provider that connects to an SQL database. To connect to an SQL database, you should use the SQL Server .NET data provider. While connecting to an SQL database, the SQL Server .NET data provider does not use the ODBC or OLE-DB calls layer; therefore, the connection is direct and fast.

Connecting to a Database

To connect a Web service to a database, perform the following steps:

  1. Add the following declarations to the beginning of your Visual Basic file, Webservice1.asmx.vb:
  2. Imports System.Data
  3. Imports System.Data.SqlClient
  4. The preceding statement includes the System.Data and System.Data.SqlClient namespaces in your application.

    The System.Data namespace is included in the .NET base class library that constitutes the classes that are used by the ADO.NET technology. This technology connects a .NET application to a database, such as an SQL database.

  5. Create an object of the SqlClient.SqlConnection class.
  6. To the constructor of the class, pass the name and details of your SQL Server connection.
  7. For example, to connect to the ABCFinance database that is present at SQL Server, Server1, when the mode of authentication is Windows Security, use the following command:

  8. Pass the name of the connection object as a parameter to the constructor of the SqlClient.SqlConnection class. The code to pass the connection object as a parameter is shown here:
  9. Declare a SqlDataReader object as shown in the following code statement:
  10. Execute the command to read the data from a database table. The command is shown here:
  11. The preceding statement calls the ExecuteReader() method that returns the rows from the database table in the form of an SqlReader object.

  12. Move to the first record of the dataset to the SqlReader object and extract the required data by using the column name in the table.
  13. For example, in the following code, Denomination and ExRate are columns in the Rates table in the ABCFinance database. The Read() method reads the data in the Rates table.

  14. Close the reader and the connection objects.

The following is the entire code listing for the implementation of the GetCountryData() method:

Similarly, you can add code for the GetRateByCountry() method. The entire code for the GetRateByCountry() method is as follows:

After you add the code for the GetCountryData() and GetRateByCountry() methods, you need to recompile the Webservice1.asmx.vb file. Because you are using the classes and methods in the data access assemblies of the .NET Framework, the command line for compiling the DLL file changes is as follows:

After you compile the DLL file, copy the file to the bin folder. This creates the Web service, which you can now test.Testing the Web service is discussed in the following section.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

XML Topics