ADO.NET sample (.NET)

Concepts Covered

This sample demonstrates how Remoting SDK Servers can be used as middle-tier servers for accessing databases residing on DBMS servers.

Prerequisites

To work out of the box this sample requires the MS SQL server deployed on the localhost and the sample database Northwind. Refer to the connection string in the ADOService_Impl.cs file:

mySqlConnection.ConnectionString = "user id=sa;data source=localhost;initial catalog=Northwind;Integrated Security=SSPI";

The sample is not bound to a certain database, you may use any.

Getting Started

Build and launch the ADO.NET Sample Server. The server contains only one operation that allows to get data from the defined database. Build and launch the ADO.NET Sample Client. Write a query you need, set the maximum number of returned records and click the button Query. The corresponding result set will be shown. In case of error messages, check the database connection string as described above.

Examine the code

The ADO.NET Sample Server contains a service operation with input parameters: an SQL string request and the maximum number of returned records and the result parameter; the resultset is in XML format. The server uses standard framework components to access the database: SqlConnection, SqlDataAdapter, DataSet (assemblies System.Data and System.Data.SqlClient). By default the connection is set to MSSQL Northwind database. Change it to work with other databases.

public virtual Binary QueryData(string SQL, int MaxRecords) {
  SqlConnection mySqlConnection = new System.Data.SqlClient.SqlConnection();
  mySqlConnection.ConnectionString = "user id=sa;data source=localhost;initial catalog=Northwind;Integrated Security=SSPI";

  SqlDataAdapter myAdapter = new SqlDataAdapter(SQL, mySqlConnection);
            
  DataSet myDataset = new DataSet("RemoteData");
  myDataset.Tables.Add("UserData");

  myAdapter.Fill(myDataset, 0, MaxRecords, "UserData");

  Binary myData = new Binary();
  myDataset.WriteXml(myData, XmlWriteMode.WriteSchema);
  return myData;
}

The ADO.NET Sample Client represents the Remoting SDK client that works with the server in a standard way. After clicking the button Query, the client creates a new proxy object and calls the service operation QueryData with the SQL and MaxRecords parameters set accordingly. The returned result set is suitable for loading into a DataSet instance that is used as a DataGrid data source.

private void Query_Click(object sender, System.EventArgs e)
{
   IADOService service = CoADOService.Create(clientMessage, clientChannel);
   try
   {
    using (Binary data = service.QueryData(tbSQL.Text, (int)nupMaxRecords.Value))
    {
        DataSet MyDataSet = new DataSet();

        MyDataSet.ReadXml(data, XmlReadMode.ReadSchema);
        dataGrid1.DataSource = MyDataSet.Tables[0];
    }
   }
   catch (Exception ex)
   {
    MessageBox.Show(ex.Message);
   }
}