Hi,

how could I support access to more than one database?

In my program I need to connect other databases. LLBLGen creates this DbUtils class where static methods to get the connectionstring exist.

So I guess I have to change these methods. But since I don't have a context when calling this methods, how do I decide for which database the connection string should be returned?

Christoph

The current template set uses DAO objects internally, which means, the DAO object is called from inside the entity classes/collection classes. This can be limiting. The template set that is released in November will solve this: it will have separate DAO objects which accept a connection string among other data, like an entity to save or fill, or f.e. a datareader object to use to fill an entity collection. But this doesn't help you at the moment. So I have a question:

Are the databases identical (A) or does your project reference more than 1 database (B)?

In the case of (A), for example when the user can select the database he connects to from a list in the gui, there is a problem, and I have to look into that how to solve that quickly for you.

In the case of (B), you create a project for each database you reference. In the project properties, you can specify the name of the tag where the connection string is stored in. Compile each project separately and reference them in your code as normal assemblies. Add each connection string tag found in the generated app.config files to the app.config file you're working with. Each assembly will now load its own connection string and connect to the correct database.

When you run into conflicts when types are defined multiple times, alias the namespaces in your codefile using 'using' (C#) or 'Imports' (VB.NET)

So I guess I have to change these methods. But since I don't have a context when calling this methods, how do I decide for which database the connection string should be returned?

This is the problem with the current templates. The code they generate works ok, but when other scenarios are required, it can be limiting, because there is no way you can pass down a connection string when saving an entity for example (which would be odd anyway, because you're working with entities, not data-related material).

I hope you're working with multiple databases which are not the same so you can solve it yourself.

I found a solution for (A) too

Change DbUtils.cs like this: - Add a static public member variable, called 'ActualConnectionString', and set it to "" (or String.Empty, what you prefer), - In 'CreateConnection' alter the code such that you first test if ActualConnectionString == "" (or String.Empty if you've set it to String.Empty): like this:

if(ActualConnectionString=="")
{
    // read the connection string from the *.config file.
    AppSettingsReader configReader = new AppSettingsReader();
    ActualConnectionString = configReader.GetValue(connectionKeyString, typeof(string)).ToString();
}

return CreateConnection(ActualConnectionString);

• Then at the start of your program (or somewhere else) you simply do: DbUtils.ActialConnectionString = "yourconnectionstring".

Note: this can be insecure when you work in a large team, the static var can be set to another value easily, however most of the time this is not a problem, since all DB activity is placed in the BL anyway.

Note 2: when you use COM+, you also have to alter DbUtilsComPlus with the same code

I how this answers your question

The databases are identical.

Sorry for that

Let me explain a little bit.

I use your tool mainly as persistence tool for my classes. During runtime a copy of the database is made (single access) and with this copied database a user can generate an html export. After the export finished the database copy will be deleted.

I know, I could change the connection string statically and wait for the export to finish and then reset the connection string to the original value. But this means that the user cannot work during the export, because for this he has to access the original database.

Any idea how I can work around this?

Christoph

ChBaeumer wrote:

During runtime a copy of the database is made (single access) and with this copied database a user can generate an html export. After the export finished the database copy will be deleted.

I know, I could change the connection string statically and wait for the export to finish and then reset the connection string to the original value. But this means that the user cannot work during the export, because for this he has to access the original database.

Ah. Then the trick with the static var is not a solution indeed...

With the current set of templates, there is no other way than that when you want to work with just 1 assembly.

I don't know if the HTML export functionality is a separate piece of code. If that's the case, you can use option (B), generate the code in another project using another connection string tag name and root namespace and use that assembly with the HTML export functionality.

It's a little bit of code bloat and I'm not happy about it, but I can't think of another way around it, until I have written that new template set.

I am having a caching problem using this solution. For instance. If one user on one computer changes the db conneciton string all users are affected by the change. So two different people can not be looking at two databases at the same time. What is worse is that they don't even know what database they are looking at (the session var for the their connection string is not aware of the chage). So they end up getting unexpected/undesireable results when more than one user is using the system at the same time. Any ideas?

Yes, the trick for selfservicing is global for every call in the application. If you want per-call access to different databases, use adapter.

Do you have any examples/best practices on how this is accomplished using the adapter method?

Sam wrote:

Do you have any examples/best practices on how this is accomplished using the adapter method?

follow these steps: - using Adapter template group. You then get 2 vs.net projects per llblgen pro project - make 2 llblgen pro projects, one per database (so 1 for sqlserver and 1 for oracle) - keep the tables in sync with each other. This means: an entity in sqlserver should have the same fields as in Oracle, in the same order. Keep the entities named the same as well. - avoid types like GUID on sqlserver, as these are not supported on oracle. - generate code using the two llblgen pro projects in two different folders. Then use 1 database generic project (pick either one, they're the same anyway as you kept names the same) and use per database the database generic project, so your .NET solution will have 3 generated projects: one db specific for each database and one generic with the entities. - use IDataAccessAdapter interface to program your application, instead of using DataAccessAdapter. This way, you can create a single method in your application which produces a new DataAccessAdapter for the right database to access. You can pass the connection string to the DataAccessAdapter constructor.

After this, you can fully target the two databases in your application transparently. You simply instantiate an entity using the generic project and persist it (or load it) using the DataAccessAdapter instance of choice (sqlserver or oracle). This for example also means that you can pull an entity from sqlserver and save it into oracle if you'd like that.

So you can then do: CustomerEntity customer = new CustomerEntity(); // fill it with data

IDataAccessAdapter adapter = MyUtils.CreateAdapter(DatabasesSupported.SqlServer); adapter.SaveEntity(customer);

where DatabasesSupported is a simple enum you define: public enum DatabasesSupported:int { SqlServer, Oracle }

and CreateAdapter is a method which looks something like:

public static IDataAccessAdapter CreateAdapter(DatabasesSupported dbType)
{
    IDataAccessAdapter toReturn = null;
    
    switch(dbType)
    {
        case DatabasesSupported.SqlServer:
            toReturn = new SqlServerNamespace.DatabaseSpecific.DataAccessAdapter();
            break;
        
        case DatabasesSupported.Oracle:
            toReturn = new OracleNamespace.DatabaseSpecific.DataAccessAdapter();
            break;
    }
    
    return toReturn;
}

Very important: - fields in the tables have to be in the same order in both databases - use only types which are supported in both databases. This means that you should avoid GUID's and oracle native types like timestamp with local zone stuff. - specify precision and scale definitions on NUMBER fields in oracle.

Thank you for the response. I don't know if it matters but I am not dealing with an oracle/sql situation. I am actually dealing with a sql/sql situation. Before I started working here my company decided to create a different database for each time zone. Therefore the tables and everything are the same but the data is different. Corporate employees need utils that search and reports on all databases. Will this still require the adaper method?

Sam wrote:

Thank you for the response. I don't know if it matters but I am not dealing with an oracle/sql situation. I am actually dealing with a sql/sql situation. Before I started working here my company decided to create a different database for each time zone. Therefore the tables and everything are the same but the data is different. Corporate employees need utils that search and reports on all databases. Will this still require the adaper method?

Yes it still requires adapter, but it makes life more easier for you

Simply generate code using adapter templates, from a project created with one catalog (doesn't matter which one). The catalog name is embedded in the persistence logic. Per DataAccessAdapter instance you can now control which catalog to address by specifying the catalog name in the DataAccessAdapter constructor. This gives you fine grained control per DataAccessAdapter instance, which database to target.

Another way is to use the overwrite method of the catalog to clear the catalog name. This makes it possible to control which catalog to connect to via the connection string. You then specify the connection string with the DataAccessAdapter constructor.

Please see: "Catalog specific persistence info (SqlServer specific)" in the documentation (Using the generated code\Adapter\DataAccessAdapter functionality)

I am prefacing this post with a disclaimer that this is a stupid question but I would not be posting it I hadn’t already exhausted all my (limited albeit) resources. As per your instructions earlier I have generated the project using the adapter method. Here is the problem. I have a method (using self-servicing) that takes a search string and returns a DataTable of customers that meet their search criteria. Here is one method I wrote using the self servicing code the a cannot replicate using the adapter:

        public DataTable SearchCustomers (string search)
        {
            DataTable CustomersTable = new DataTable();
            search = "%" + search + "%";
            PredicateExpression Filter = new PredicateExpression();
            SortExpression Sort = new SortExpression();
            Filter.Add(DAL.FactoryClasses.PredicateFactory.Like(DAL.INSFieldIndex.NAME, search));
            Filter.AddWithOr(DAL.FactoryClasses.PredicateFactory.Like(DAL.INSFieldIndex.ATTN, search));
            Sort.Add(DAL.FactoryClasses.SortClauseFactory.Create(DAL.INSFieldIndex.NAME, SortOperator.Ascending));
            CustomersTable = DAL.CollectionClasses.INSCollection.GetMultiAsDataTable(Filter, 100, Sort);

            DataColumn FullAddressColumn = new DataColumn("FULL_ADDRESS", typeof(string));
            FullAddressColumn.Expression = "CITY + ', ' + ST + ' ' + ZIP";
            CustomersTable.Columns.Add(FullAddressColumn);
            return CustomersTable;
        }

I cannot replicate this in the data adapter method. I know it is stupid but the examples all seem to deal with related table (not the case here).

In adapter, you use the FetchTypedList for retrieving entity collections as a datatable. I've rewritten your method to use this routine.

public DataTable SearchCustomers (string search)
{
    DataTable CustomersTable = new DataTable();
    search = "%" + search + "%";
    IRelationPredicateBucket filter = new RelationPredicateBucket();
    SortExpression Sort = new SortExpression();
    filter.PredicateExpression.Add(DAL.FactoryClasses.PredicateFactory.Like(DAL.INSFieldIndex.NAME, search));
    filter.PredicateExpression.AddWithOr(DAL.FactoryClasses.PredicateFactory.Like(DAL.INSFieldIndex.ATTN, search));
    Sort.Add(DAL.FactoryClasses.SortClauseFactory.Create(DAL.INSFieldIndex.NAME, SortOperator.Ascending));
    DataAccessAdapter adapter = new DataAccessAdapter();
    CustomerEntity customerDummy = new CustomerEntity();
    adapter.FetchTypedList(customerDummy.Fields, CustomersTable, filter, 100, Sort, false);
    DataColumn FullAddressColumn = new DataColumn("FULL_ADDRESS", typeof(string));
    FullAddressColumn.Expression = "CITY + ', ' + ST + ' ' + ZIP";
    CustomersTable.Columns.Add(FullAddressColumn);
    return CustomersTable;
}

I can have missed a detail here and there, but this is the idea.