communicating to 2 databases

[Guest]

I am new to using .net. I am told that I need to setup a config file that
will be accessed by a VB.net dll. This vb.net dll will be setup to run every
5 to 10 minutes by a timer that is coded into the vb.net application.
A user will submit a request to obtain data from a HP mainframe.

This web config file needs to be able to do the following:

1. The user will submit the request to a sql server 2000 database (that
resides on a different that where the vb.net application will be running).
2.. The vb.net code will that submit the request for data to the hp
mainframe by using http and xml.

Thus my questions are:

1. How do you setup a connection and retrieve data from the sql server 2000
database that resides with the company's intranet? This will somehow need to
include a connection object and statements oh how to connect to the SQL
server 2000 database. The user name and password are suppose to be passed to
the sql server as part of the connection string.
2. How would I request to the HP mainframe using soap, http, and xml to wrap
up the request?

Thanks!

 

[Guest]

Connecting to the SQL Server is a simple matter. As you are using SOAP to
transmit the request to another server, you will probably find it easier to
work with DataSets, as they are XML based. Basic method:

'Best to put the connection string in a configuration file
Dim connString As String = ConfigurationSettings.AppSettings("connString")

'SQL is the name of the stored procedure or sql statement you are using
'to pull data from SQL Server
Dim sql As String = "{Your string or sproc name here}"

'Set up main objects
Dim conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(sql, conn)

'Need dataset
Dim ds As New DataSet("YourNameForDataSet")

'Need adapter to fill DataSet
Dim da As New SqlDataAdapter(cmd)

'May desire table mappings for better XML naming
da.TableMappings.Add("Table", "YourNameForTable")

Try
conn.Open()
da.Fill(ds)
Finally
If (conn.State==ConnectionState.Open) Then
conn.Close()
End If

conn.Dispose()
End Try

How you work with this data to get it to HP is the trickiest part. If HP has
the ability to get SOAP through a specific web address, the method is simply
transforming the DataSet to the proper XML for HP (or setting HP to accept
the DataSet XML as input).

If you have a specific address on HP, you then have to either find a way to
get WSDL for the address (check the HP documentation) or create WSDL to call
that particular address. The easiest way to do this is

1. set up a separate .NET project with an ASMX web service.
2. Make the service look just like the HP box (expecting the same XML)
3. Add that project, as a web project, to your VB.NET app
4. Open the actual files for the web service and change the address of the
service

NOTE: You might be able to contact the HP box for WSDL and set up the web
reference directly to the HP box. I am not familiar with HP mainframes, so
this is a guess.

You may have to alter the XML. Study the XML transformation class and set up
XSLT to transform to the XML expected. This is only applicable if you cannot
have the HP box accept DataSet XML.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************