Multiple databases using DAAB

Topics: Data Access Application Block
Dec 29, 2009 at 3:56 AM

Hello All,

I have housed on the same physical server, 5 databases, for 5 different States of the US. I am working with a WinForm that offers a comboBox to select one of the States. A second comboBox is being used to select a city from the preceeding State's selection.

Once I configure a [default database] connection string for one of the databases, I'm prevented from switching to one of the other connection strings to access my other databases. Is it allowed to switch connection strings enabling me to move from data store to data store?

I'm using VS 2008 Express - C#, MS Sql Server 2008 Express, .NET 3.5

What I'm attempting to do is allow the selection of a State i.e. Missouri, in the first comboBox, once this event fires, it in turn connects to the Missouri database, where a stored procedure runs a SELECT statement that retrieves all cities in Missouri and returns them to the second ComboBox. Selecting a city in the second comboBox fires another event that opens a third comboBox, which offers the user a selection of all zip codes in the respective city.

 

<?xml version="1.0" encoding="utf-8"?>

<configuration>

<configSections>

<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

</configSections>

<dataConfiguration defaultDatabase="States.Properties.Settings.AlabamaCS" />

<connectionStrings>

<add name="States.Properties.Settings.AlabamaCS" connectionString="Data Source=ADVERTISER\SQLEXPRESS;Initial Catalog=Alabama;Integrated Security=True"

providerName="System.Data.SqlClient" />

<add name="States.Properties.Settings.ConnecticutCS" connectionString="Data Source=ADVERTISER\SQLEXPRESS;Initial Catalog=Connecticut;Integrated Security=True"

providerName="System.Data.SqlClient" />

<add name="States.Properties.Settings.MinnesotaCS" connectionString="Data Source=ADVERTISER\SQLEXPRESS;Initial Catalog=Minnesota;Integrated Security=True"

providerName="System.Data.SqlClient" />

<add name="States.Properties.Settings.MissouriCS" connectionString="Data Source=ADVERTISER\SQLEXPRESS;Initial Catalog=Missouri;Integrated Security=True"

providerName="System.Data.SqlClient" />

<add name="States.Properties.Settings.WyomingCS" connectionString="Data Source=ADVERTISER\SQLEXPRESS;Initial Catalog=Wyoming;Integrated Security=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

</configuration>

 

private void cbState_SelectedIndexChanged(object sender, EventArgs e)

{

if (cbState.SelectedText!= null )

cbCity.Visible = true;

try

{

switch (cbState.Text)

{

case "Alabama":

conn = Settings.Default.AlabamaCS;

st.AddRange(TableDAL.getCitiesInAlabama(conn));

break;

case "Connecticut":

conn = Settings.Default.ConnecticutCS;

st.AddRange(TableDAL.getCitiesInConnecticut(conn));

break;

case "Minnesota":

st.AddRange(TableDAL.getCitiesInMinnesota());

break;

case "Missouri":

st.AddRange(TableDAL.getCitiesInMissouri());

break;

case "Wyoming":

st.AddRange(TableDAL.getCitiesInWyoming());

break;

}

foreach (Tables item in st)

{

input.Add(item.City.ToString());

}

output.AddRange(input);

}

catch (Exception ex)

{

Console.WriteLine("Caugth exception {0}", ex);

}

}

 

Any assistance will be greatly appreciated

Dec 29, 2009 at 5:29 AM

I can't repro this, could you post some code snippets from your TableDAL class? Or you can send a sample solution that replicates the error,

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Dec 29, 2009 at 6:59 AM
AvanadeSupport wrote:

I can't repro this, could you post some code snippets from your TableDAL class? Or you can send a sample solution that replicates the error,

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

 I tried to use a solution you offered from another posting, but for some reason I could not get one of the types to set: (see underlined text below)

IConfigurationSource entLibConfig = ConfigurationSourceFactory.Create();

ConnectionStringsSection connStringsSection = (ConnectionStringsSection)entLibConfig.GetSection("connectionStrings"); <--------

 

TableDAL.cs to follow:

using System;
using System.Data;
using System.Configuration;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Collections.Generic;
using System.Text;

namespace States
{
    public class TableDAL
    {
        public TableDAL()
        {
        }

        #region "Stored Procedure(s) that query data store and retrieve selected category table."
     
        public static Tables getTablesById(int id)
        {
            Tables table = new Tables();
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("TableByID");
            db.AddInParameter(dbCommand, "mdaID", System.Data.DbType.Int32, id);
            IDataReader idr = db.ExecuteReader(dbCommand);
            if (idr.Read())
            {
                EntityUtils.PopulateEntity<Tables>(table, idr);
            }
            return table;
        }

        public static List<Tables> getCitiesInAlabama(string conn)
        {
            IConfigurationSource entLib = ConfigurationSourceFactory.Create();	// Was unable to access your solution "ConnectionStringsSection = ... " for some reason System.Configuration.dll would expose second part of solution??
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Alabama");
            IDataReader idr = db.ExecuteReader(dbCommand);
            return (EntityUtils.PopulateEntities<Tables>(idr));
        }

        public static List<Tables> getCitiesInConnecticut(string conn)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Connecticut");
            IDataReader idr = db.ExecuteReader(dbCommand);
            return (EntityUtils.PopulateEntities<Tables>(idr));
        }

        public static List<Tables> getCitiesInMinnesota()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Minnesota");
            IDataReader idr = db.ExecuteReader(dbCommand);
            return (EntityUtils.PopulateEntities<Tables>(idr));
        }

        public static List<Tables> getCitiesInMissouri()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Missouri");
            IDataReader idr = db.ExecuteReader(dbCommand);
            return (EntityUtils.PopulateEntities<Tables>(idr));
        }


        public static List<Tables> getCitiesInWyoming()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Wyoming");
            IDataReader idr = db.ExecuteReader(dbCommand);
            return (EntityUtils.PopulateEntities<Tables>(idr));
        }
        #endregion
    }

    
}

Exception error to follow:

 

Microsoft.Practices.ObjectBuilder2.BuildFailedException was caught
  Message="The current build operation (build key Build Key[Microsoft.Practices.EnterpriseLibrary.Data.Database, null]) failed: The value can not be null or an empty string. (Strategy type ConfiguredObjectStrategy, index 2)"
  Source="Microsoft.Practices.ObjectBuilder2"
  BuildKey="Build Key[Microsoft.Practices.EnterpriseLibrary.Data.Database, null]"
  ExecutingStrategyIndex=2
  ExecutingStrategyTypeName="ConfiguredObjectStrategy"
  StackTrace:
       at Microsoft.Practices.ObjectBuilder2.StrategyChain.ExecuteBuildUp(IBuilderContext context)
       at Microsoft.Practices.ObjectBuilder2.Builder.BuildUp(IReadWriteLocator locator, ILifetimeContainer lifetime, IPolicyList policies, IStrategyChain strategies, Object buildKey, Object existing)
       at Microsoft.Practices.ObjectBuilder2.Builder.BuildUp[TTypeToBuild](IReadWriteLocator locator, ILifetimeContainer lifetime, IPolicyList policies, IStrategyChain strategies, Object buildKey, Object existing)
       at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.EnterpriseLibraryFactory.BuildUp[T](IReadWriteLocator locator, ILifetimeContainer lifetimeContainer, IConfigurationSource configurationSource)
       at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.EnterpriseLibraryFactory.BuildUp[T](IConfigurationSource configurationSource)
       at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.NameTypeFactoryBase`1.CreateDefault()
       at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase()
       at States.TableDAL.getCitiesInAlabama(String conn) in C:\Documents and Settings\Gary\My Documents\Visual Studio 2008\Projects\States\States\TableDAL.cs:line 37
       at States.States.cbState_SelectedIndexChanged(Object sender, EventArgs e) in C:\Documents and Settings\Gary\My Documents\Visual Studio 2008\Projects\States\States\States.cs:line 36
  InnerException: System.ArgumentException
       Message="The value can not be null or an empty string."
       Source="Microsoft.Practices.EnterpriseLibrary.Data"
       StackTrace:
            at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseConfigurationView.ValidateInstanceName(String name)
            at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseConfigurationView.GetConnectionStringSettings(String name)
            at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseCustomFactory.CreateObject(IBuilderContext context, String name, IConfigurationSource configurationSource, ConfigurationReflectionCache reflectionCache)
            at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.ConfiguredObjectStrategy.PreBuildUp(IBuilderContext context)
            at Microsoft.Practices.ObjectBuilder2.StrategyChain.ExecuteBuildUp(IBuilderContext context)
       InnerException: 

 

 

Jan 5, 2010 at 2:12 AM

Yes, the ConnectionStringSection is in the System.Configuration namespace.  Regarding the exception you're encountering, do you have a default database configured in your config?  Make sure that your config is in the executable project and not in a class library.

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com