I have just finished the console app tutorial on using code generated from my production database, SQLServer. Every thing worked out just fine. (Excellent!) In debug mode I see the following:

Query: SELECT [DB_Name].[DB_Owner].[DB_Table].[Field] ...

It must be the case that DB_Name is set from the catalog value of the connection string. Correct?

How about DB_Owner... where does this value come from? I assume it was generated from the database. The database used to generate the code is consistent regarding table owners, they are all owned by dbo. However on my production servers I suspect that the DB_Owner is not consistent. i.e. during the past 15 years tables were added to the systems by various administrators.

How can this be handled? Will I have to make the table owners in the production systems match those of the development database?

Barry

I found a section in help on Application configuration through .config files. But that just begs more questions.

My situation. I have a system at a datacenter that services several sites, by sites I mean production facilities. Each site has its own database. There is a master database that knows about the site databases. Each of the databases could be either SQLServer or Oracle.

The only thing that needs to be known up-front is the connection information to the MASTER database. The connection information for the individual sites will come from the Master Database.

So I have to store the main connection string somewhere and the password can't be plain text. I think this is addressed in the .config file section I mentioned above.

Then I have to figure out how to set up the connection dynamically.

Barry

OK, I'm having problems setting the connection string dynamically.

In App.config there is connection string:

<add key="Main.ConnectionString" value="data source=..."/>

In the ConsoleApplication example I want to set the the connection string there.

using (DataAccessAdapter adapter = new DataAccessAdapter())
adapter.ConnectionString = "Cut-n-paste from App.config file";
{
  adapter.FetchEntityCollection(loops, filter);
}

I get an error CS0103: The name 'adapter' does not exist in the current context

It worked correctly before I added the adapter.ConnectionString line.

So what am I doing wrong?

Barry

using (DataAccessAdapter adapter = new DataAccessAdapter("Cut-n-paste from App.config file")) { adapter.FetchEntityCollection(loops, filter); }

OK, that took care of the compile problem. I did not come across the documentation that said to set it there instead of setting the property. I would have thought that since it was a property is should have worked.

But it still doesn't work.

Below I am trying to demonstrate a data dump from identical tables from 2 databases MDP_GEP, and MDP_DPC. (GEP and DPC are chemical plants located in the US.) I find it very strange that I am specifying the connection string and yet the same database is being accessed. I am sure this situation is referenced in overwriting the catalog name, but that is done in the app.confg file, which I will not be using. Why doesn't this work and what is the work around.

Code Below,

static void Main(string[] args)
    {
      RelationPredicateBucket filter = new RelationPredicateBucket(Loops_tbFields.Unit_id == 31);
      EntityCollection<Loops_tbEntity> loops = new EntityCollection<Loops_tbEntity>();
      string CS = "data source=HOUIC-S-7039\\MDPROONLINE,1474;initial catalog=MDP_GEP;User ID==******;Password==******;persist security info=False;packet size=4096";
      using (DataAccessAdapter adapter = new DataAccessAdapter(CS))
      {
        adapter.FetchEntityCollection(loops, filter);
      }
      Console.WriteLine("Number of Entities fetched: {0}", loops.Count);
      foreach (Loops_tbEntity l in loops)
      {
        Console.WriteLine("{0} {1}", l.Loop_id, l.LoopName_txt);
      }
      string s = Console.ReadLine();

      RelationPredicateBucket filter2 = new RelationPredicateBucket(Loops_tbFields.Unit_id == 31);
      EntityCollection<Loops_tbEntity> loops2 = new EntityCollection<Loops_tbEntity>();
      string CS2 = "data source=HOUIC-S-7039\\MDPROONLINE,1474;initial catalog=MDP_DPC;User ID=******;Password==******;persist security info=False;packet size=4096";
      using (DataAccessAdapter adapter2 = new DataAccessAdapter(CS2))
      {
        adapter2.FetchEntityCollection(loops2, filter2);
      }
      Console.WriteLine("Number of Entities fetched: {0}", loops2.Count);
      foreach (Loops_tbEntity l2 in loops2)
      {
        Console.WriteLine("{0} {1}", l2.Loop_id, l2.LoopName_txt);
      }
      s = Console.ReadLine();
    }

I've edited out the username/passwords in your post.

You can specify the catalog/schema name per call on adapter. Please see this section in the DataAccessAdapter class section, the 'Multi name setting'.

You simply create a CatalogNameOverwriteHashtable instance and add name-value pairs for from-to overwrites. (if you have multiple catalogs in your project, you can specify overwrites for all of them this way). Then pass the instance to the dataaccessadapter ctor or set the CatalogNameOverwrites property of the DataAccessAdapter instance to the instance of the CatalogNameOverwriteHashtable class you created.

As you will set this regularly (with every dataaccessadapter instantiation), it might be handy to create a dedicated utility class for this which obtains the catalog names from the master db, and produces DataAccessAdapter instances for you with the proper overwrites already setup.

You say its simple. Simple would be having it just work by setting the connection string.

Actually I am probably a pretty good test for simple! I really have no idea what you are talking about. How do I make a hash table and then after I make one how do I use it.

I specify a connection string and the software opens up the incorrect database. My project manager is going to call that a bug!

OK, I saw this from another post. A recommendation from David Elizondo;

CatalogNameOverwriteHashtable overwriteCatalog = new CatalogNameOverwriteHashtable( CatalogNameUsage.Clear);
      using (DataAccessAdapter adapter2 = new DataAccessAdapter(CS2, false, overwriteCatalog, null))

My simple example now works.

Barry

The main thing is that a connection string isn't sufficient: what if you have 2 catalogs in your project? To which catalog do you want to connect to, as you can specify just 1 catalog in the connection string? That's why the catalog name is in the queries.

Admitted, this section in the manual doesn't have an example, however it's also more commong to use the .config file to do overwrites.

SqlServer connects in a two steps process The connection string first allows you to connect to the sqlserver service, and then you will connect to the initial catalog specified in the connection string. However, you can query ANY catalog from then on if you have query rights on that catalog. That's the reason why llblgen pro emits the catalog name into the persistence elements and queries, so you can define relations between entities which are mapped to tables in different catalogs (FK constraints aren't possible between catalogs).

So if I connect to 'Pubs' via the connection string, I can do: select * from [Northwind].[dbo].[Customers]

and it will work (if I have select rights on that database of course) .That's the feature we're using to support multiple catalogs in a single project.