Web Service ?

[kcwallace]

I have the following VB.NET class. Its function is to simplify database interaction. I want to create a web service to do the same thing for my web application, and to avoid multiple copies of the code residing on the intranet. My problem is, how do I pass constants from the web service to ASP.NET project?

 

Imports System.Data
Imports System.Data.SqlClient

Public Class ADONET
   Public Const CnnStrDosimSQL As String = "Data Source=process;Initial Catalog=dosimetrysqldatabases;Integrated Security=SSPI;"
   Public Const CnnStrIT As String = "Data Source=process;Initial Catalog=IT;Integrated Security=SSPI;"
   Public Const CnnStrSQA As String = "Data Source=process;Initial Catalog=SQA;Integrated Security=SSPI;"
   Public Const CnnStrHR As String = "Data Source=process;Initial Catalog=HR;Integrated Security=SSPI;"
   Public Const CnnStrDosimSQLWeb As String = "server=PROCESS;database=dosimetrysqldatabases;UID=sa;PWD=sa;"
   Public Const CnnStrHRWeb As String = "server=PROCESS;database=HR;UID=sa;PWD=sa;"
   Public Const CnnStrSQAWeb As String = "server=PROCESS;database=SQA;UID=sa;PWD=sa;"
   Public Const CnnStrITWeb As String = "server=PROCESS;database=IT;UID=sa;PWD=sa;"
   
   Public Function SetCnn(ByVal CnnStr As String) As SqlConnection
       SetCnn = New SqlConnection(CnnStr)
       SetCnn.Open()
   End Function

   Public Function GetDR(ByVal SQL As String, ByVal Cnn As SqlConnection) As SqlDataReader
       If Cnn.State = ConnectionState.Open Then
           Cnn.Close()
           Cnn.Open()
       End If
       Dim Cmd As SqlCommand = GetCmd(SQL, Cnn)

       GetDR = Cmd.ExecuteReader
   End Function

   Public Function GetDA(ByVal Cmd As SqlCommand, ByRef DS As DataSet) As SqlDataAdapter
       GetDA = New SqlDataAdapter
       GetDA.SelectCommand = Cmd
       DS = New DataSet("X")
       GetDA.Fill(DS)
   End Function

   Public Function GetCmd(ByVal SQL As String, ByVal Cnn As SqlConnection) As SqlCommand
       GetCmd = New SqlCommand(SQL, Cnn)
   End Function

   Public Sub ActionSQL(ByVal SQL As String, ByVal Cnn As SqlConnection)
       Dim Cmd As SqlCommand = GetCmd(SQL, Cnn)
       Cmd.CommandText = SQL
       Cmd.ExecuteNonQuery()
   End Sub
end class

[PlausiblyDamp]

You could just return them from a web method.

Not sure why you would need to return constants from the web service though - if you are trying to encapsulate your data access behind a web service there should be no need for the client to know connection strings etc.

It might help if you gave a bit more detail about how the system is architectured.

[kcwallace]

Thank you

 

I want the client to pick the database to access, and not give them all the passwords for some of the more sensitive databases. I left off quite a few of the of the connectionstring constants that do required passwords.

[bri189a]

But again, why would you pass them the connection and not just retrieve them the data that they want?

 

You would be opening a heck of security hole passing an open ended connection string for the user...whomever it be...to use as they wish...for example:

 

Say I have Office 2003 (which supports web services)...I'm an idiot user who thinks Excel is the best thing since sliced bread. I don't know crap about programming or connection resource management. I take that connection string and form several connection to a database and leave them open for hours at a time because I don't know any better. I distribute this application to every person in my departement, they in turn share it between other departments. The dba is asking you (the person who developed it) why every connection available to the database is locked and all are using the same connection string which ties back to you. Nobody else can access the other resources on the machine because of this and the entire infrastructure is at a halt. He's also asking why several tables, and even databases have disappeared. You're co-workers are wondering why several rows of data that are static and couldn't possibily be deleted by an enterprise application are suddenly missing.

 

What do you tell them?

 

Get my point????????

[kcwallace]

Good point. I was overthinking it.

[bri189a]

It's okay, that's why we're here...to kick each other in the butt when we need it...PD can tell you about all the times he's kicked me in the butt! :)