asked on

How do I declare web.config based database connection in a web service assembly?

My question regards deployment of an AIR project which relies on a connection to SQL Server 2005 through .NET web service. During build of web service assembly VS asks for a declaration of the database connection: “The name 'DB01Connection' does not exist in the current context”. How should I extract connection parameters from web.config for the assembly to compile properly? Where should I place the assembly in the release version of the AIR application? Should I include .pdb file along with .dll?
Are there any other steps I should perform for the release version to work?

Part of web service:

using System;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Web.Services;
using System.Configuration;
using System.Web.Configuration;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

public class MyWebService : System.Web.Services.WebService
{
    public MyWebService()
    {
     }

    [WebMethod]
    public DataTable executeSQLXML(string SQLstring)
    {
        try
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("EXECUTE ");
            sql.Append(SQLstring);

            using (DbConnection cnn = DB01Connection.CreateConnection())
            using (DbCommand command = cnn.CreateCommand())
            using (DbDataAdapter adapter = DB01Connection.Factory.CreateDataAdapter())
            {
                command.CommandText = sql.ToString();
                DataTable result = new DataTable("Table0");
                adapter.SelectCommand = command;
                adapter.Fill(result);
                return result;
            }
        }
        catch (Exception ex)
        {
            throw CreateSafeException(ex);
        }
    }
}

Web.config:

<connectionStrings>
<add connectionString="server=localhost;database=MyDatabase;uid=user;pwd=password" name=" DB01Connection" providerName="System.Data.SqlClient"/>
</connectionStrings>

Open in new window

Obadiah Christopher

http://www.codeproject.com/KB/cpp/WebServiceCall.aspx

RameshS

Have you tried ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString in the System.Configuration namespace?
For example, the following statement will return the connection details for the connection string DB01Connection.

String conString = ConfigurationManager.ConnectionStrings["DB01Connection"].ConnectionString;

JacekWW

ASKER

Hi RameshS,
yes, I have tried. It returns 2 errors for the 'using' scope:
1: 'string' does not contain a definition for 'CreateConnection' and no extension method 'CreateConnection' accepting a first argument of type 'string' could be found (are you missing a using directive or an assembly reference?)
2: 'string' does not contain a definition for 'Factory' and no extension method 'Factory' accepting a first argument of type 'string' could be found (are you missing a using directive or an assembly reference?)

There has to be an explicit conversion I am not aware of. Do you have some ideas?
Big thanks

ASKER CERTIFIED SOLUTION

RameshS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JacekWW

ASKER

Hi Ramesh,

I have added the following code (as per code snippet) and modified 'using' scope in the [WebMethod]:

from
using (DbConnection cnn = DB01Connection.CreateConnection())
using (DbCommand command = cnn.CreateCommand())
using (DbDataAdapter adapter = DB01Connection.CreateDataAdapter())
to
DbConnection cnn = DB01Connection.CreateConnection();
cnn.ConnectionString = connectionString;
DbCommand command = cnn.CreateCommand();
DbDataAdapter adapter = DB01Connection.CreateDataAdapter();

It compiled without a problem, however I don't know if construction of this data adapter is correct. I will check how the assembly works in the release version of AIR application.
Thank you

...
    private static DbProviderFactory DB01Connection;
    private static String connectionString;

    public MyWebService()
    {
        // Get the ConnectionStrings collection.
        ConnectionStringSettingsCollection connections =
            ConfigurationManager.ConnectionStrings;
        String providerName = connections["DB01Connection"].ProviderName;
        connectionString = connections["DB01Connection"].ConnectionString;

        DB01Connection = DbProviderFactories.GetFactory(providerName);
    }
...

Open in new window