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

JacekWW
JacekWW used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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;

 

Author

Commented:
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
Commented:
I think you declared a string variable named 'DB01Connection' and calling CreateConnection() and CreateDataAdapter() methods with that variable.

You can call these methods only with object of the class DbProviderFactory  and not with string variable.

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial