Well, this should be something that you could do quite easily, though I don't completely understand the benefit of what you're trying to do - unless you actually did want to use a different database.  

All you should need to do is add a new <code>connectionString</code> to the <code>connectionStrings</code> section in your main web(dot)config.  Then, you can access it any way you wish.  That includes DAL2 (don't use the others ones at this point), raw DAL code, and any other common method (e.g., Entity Framework, Dapper, etc.).  

Just be sure that the <code>connectionString</code> is using the values you configured in SQL Server to achieve your desired result. 

Posted By Will Strohl on 1/19/2024 11:58 AM

Well, this should be something that you could do quite easily, though I don't completely understand the benefit of what you're trying to do - unless you actually did want to use a different database.  

All you should need to do is add a new <code>connectionString</code> to the <code>connectionStrings</code> section in your main web(dot)config.  Then, you can access it any way you wish.  That includes DAL2 (don't use the others ones at this point), raw DAL code, and any other common method (e.g., Entity Framework, Dapper, etc.).  

Just be sure that the <code>connectionString</code> is using the values you configured in SQL Server to achieve your desired result. 

Hi Will,

Thanx for taking the time to read my question and respond - much appreciated.

I have added a new connectionString - but would I need to create a new SQL user that only has access to my schema in order to accomplish this?
This might be an issue in terms of the web-hosting - I'd need to check what I can achieve in this regard.

In the model class (Invoice.cs) - would I specify the schema as part of the TableName attribute when using DAL2: [TableName("orders.Invoice")] ?

The purpose is simply to group the tables for my module into something that is easily identifiable and distinct from the rest of DNN:
like: orders.Invoices and orders.InvoiceItems instead of just dbo.Invoices and dbo.InvoiceItems.
the rest of DNN live in the 'dbo' schema and would be inter-mingled with my tables.

Thanx,
Alon

You're going to be putting this together a bit custom compared to how most folks would approach this.  I wouldn't be able to tell you exactly what to do, but the links in the resource below will give you all of the details you need to know on how to implement DAL2.  You'll need to read through some of it to find the part where you customize the query string and whatnot.  

https://www.dnnsoftware.com/wiki/dal-2

The reason for using a different schema in the DotNetNuke database (not dbo) is simply to ensure that my tables will not conflict with any other modules, that they are easily located and grouped together when browsing the schema etc.

With DAL-2 I found that when I specify the TableName attribute in my Model class, I can specify this as schema.table:
[TableName("mySchema.MyTable")]

The rest of the DAL-2 functionality works the same - no other changes are required.

David's suggestion is what most of us do.  It's generally easier, but it's always a good idea to do either one to protect the integrity of your data.  Some people even go as far as hosting application data in another data source entirely.  It depends on the various requirements of the project.  

Just for your convenience (and for others that may read this in the future), here are the main options, in the order of both complexity and isolation of the data.  The order is the same.  

1. Prefix database objects with a unique prefix (e.g., <code>acme_</code> if your company name is Acme).  << most common, in my personal experience
2. Put the application data into another schema in the same database (e.g., <code>&#91;myorg&#93;</code> instead of <code>&#91;dbo&#93;</code>).  << least common, in my personal experience
3. Have DNN running in its own database, and the data you use for your custom modules might live in another database (or any other data source(s), like an API or something else).  

I would use a second connection string and a second SqlDataProvider in the web(dot)config file, e.g.

<code>{add connectionstring="Data Source=mySqlServer;Initial Catalog=dnnDatabase;Integrated Security=True" name="FeesSqlServer" providername="System.Data.SqlClient" /}</code>

and

<code>{add connectionstringname="FeesSqlServer" databaseowner="fees" name="FeesSqlDataProvider" objectqualifier="" providerpath="~\Providers\DataProviders\SqlDataProvider\" type="DotNetNuke.Data.SqlDataProvider, DotNetNuke" upgradeconnectionstring="" /}</code>

(Replace { with < and } with >)

I never tried this aginst the same database, but it always worked fine with different ones, also completely different systems like Oracle or mySQL. So it should work.

Happy DNNing! Michael