CODEWITHSUNDEEP
c#asp.netsqlclass
frans
frans

Reputation: 181

Passing a SqlConnection to class library file (dll)

I am busy developing a class library project in C# to be reused and attached to different projects in future. It will mainly be used for Table Valued Parameters. My question is, how do I pass a SQL connection to it? The connection will be instantiated in another (main project) that the .dll gets attached to.

I currently have a Class Library Project, and have a Console Application Project created in the same solution for testing purposed.

One last requirement is that I don't want to use ConfigurationManager as the connection string will not be stored in app.config or web.config and by default the queries must be passed back to the calling application.

I've come accross a couple of links like the one below, but nothing I can really use:

Sharing a connection string

Please excuse the noobness, I am 7 weeks into professional programming.

Upvotes: 1

Views: 3991

Answers (3)

Ondrej Svejdar
Ondrej Svejdar

Reputation: 22084

This is excellent example for dependency injection. I would recommend using enterprise library unity for this kind of stuff. In your data access layer library I would define interface:

public interface IConnectionProvider {
    string ConnectionString { get; }
}

public interface IAccountProvider {
   Account GetAccountById(int accountID);
}

internal class AccountProvider : IAccountProvider {
    private IConnectionProvider _connectionProvider;
    public AccountProvider(IConnectionProvider connectionProvider) {
        if (connectionProvider == null) {
          throw new ArgumentNullException("connectionProvider");
        }
        _connectionProvider = connectionProvider;
    }
   public Account GetAccountById(int accountID) {
       Account result;
       using(var conn = new SqlConnection(connectionProvider)) {
         // retrieve result here
       }
       return result;
   }
}

public static class Bootstrapper {
    public static void Init() {
        ServiceLocator.AddSingleton<IAccountProvider, AccountProvider>();
    }
}

Then in any assembly using your data access library you can define implementation for IConnectionProvider, like this:

internal class WebConnectionProvider : IConnectionProvider {
    public string ConnectionString { get { return "Server=..."; } }
}

internal static class WebBootstrapper {
    public static void Init() {
        Bootstrapper.Init();
        ServiceLocator.AddSingleton<IConnectionProvider, WebConnectionProvider>();
    }
}

And anywhere after you call WebBootstrapper.Init() in your assembly you can use:

var accountProvider = ServiceLocator.Resolve<IAccountProvider>();
accountProvider.GetAccountById(1);

Service locator:

using System;
using Microsoft.Practices.Unity;


public class ServiceLocator {
  private IUnityContainer m_Container = new UnityContainer();

  public void Add<TFrom, TTo>() where TTo : TFrom {
    m_Container.RegisterType<TFrom, TTo>();
  }

  public void BuildUp<T>(T instance) {
    m_Container.BuildUp<T>(instance);
  }

  public void BuildUp(Type type, object instance) {
    m_Container.BuildUp(type, instance);
  }

  public void AddSingleton<TFrom, TTo>() where TTo : TFrom {
    m_Container.RegisterType<TFrom, TTo>(new ContainerControlledLifetimeManager());
  }

  public void AddInstance<T>(T instance) {
    m_Container.RegisterInstance<T>(instance);
  }

  public T Resolve<T>() {
    return m_Container.Resolve<T>();
  }

  private static ServiceLocator m_Instance;

  public static ServiceLocator Instance {
    get { return m_Instance; }
  }

  static ServiceLocator() {
    m_Instance = new ServiceLocator();
  }
}

Upvotes: 0

Trevor Pilley
Trevor Pilley

Reputation: 16413

In your dll, simply require an IDbConnection or IDbCommand. All the method is then properly abstracted against the interfaces for the data access.

For example:

In your shared dll

public static int LookUpIntForSomething(IDbConnection connection)
{
    using (var command = connection.CreateCommand())
    {
        // use command.
    }
}

In your calling app

using (var connection = new SqlConnection("ConnectionString"))
{
    var int = DbQueries.LookupIntForSomething(connection);
}

Upvotes: 3

RoughPlace
RoughPlace

Reputation: 1121

if i understand your requirements correctly,I'm not sure that i do, i would setup a static struct as such 

public static struct ConnectionString
{
    public int ID;
    public string Connection;
    public override string ToString()
    {
        return Connection;
    }

   public static ConnectionString DataBase1 = new ConnectionString{ ID = 1 , Connection = "YourConnectionStringhere"};
   public static ConnectionString DataBase2 = new ConnectionString{ ID = 2 , Connection = "YourConnectionString2here"};

}

and then use it as such 

public void SomeMethod()
{
    var I = ReferencedDll.DoSomething(ConnectionString.DataBase1.ToString());
 }

or

public void SomeMethod()
{
    var ClassFromDll = new ReferencedDll.SomeClass(ConnectionString.DataBase1.ToString());

    ClassFromDll.DoSomething();
 }

of course this leaves your connection strings hard coded which is not ideal

Upvotes: -1

Related Questions