Forum

Search Home

1
2
3
4
5

Home

Premium

Development

DAL2 - Custom Database

10/3/2013 4:33 PM

Andy

Joined: 8/8/2013

Posts: 73

DAL2 - Custom Database

I watched the video series about Data Access and found it helpful.

I would like to use the DAL2 technique but have the need to map my objects to a database other than the default DNN database. Is it possible to use an external database and how do I do this?

Second, while trying to find the answer myself I have had a hard time finding information about DAL2. Is there documentation that describes the attributes, options, or anything about DAL2. I found this page http://www.dnnsoftware.com/wiki/page/... that seems like it hopes to be the future source of information but for now it doesn't contain much at all, even the links to additional articles at the bottom are broken. Where should I go to get more info?

10/4/2013 5:37 AM

Scott Wilkinson

Joined: 5/17/2013

Posts: 118

Re: DAL2 - Custom Database Modified By Aderson Oliveira on 10/4/2013 3:26:36 PM

Andy,

I am not sure about the best reference for DAL2, but if you do a google search on "Charles Nurse" and DAL2, you will get some good info.

As for the alternate database connection in DAL2, the answer is 'YES'.

Add a new connection string to your web.config for your other database. Example:

Then in your DAL2 Repository class, pass the name of the connection string to the DataContext instance:

public class ItemRepository : IItemRepository
{
    private const string DB_CONNECTSTRING = "AltSqlServer";
 
    public void CreateItem(IItemModel t)
    {
            using (IDataContext ctx = DataContext.Instance(DB_CONNECTSTRING))
            {
                var rep = ctx.GetRepository();
                rep.Insert((Item)t);
            }
    }   
}

10/4/2013 10:33 AM

Andy

Joined: 8/8/2013

Posts: 73

Re: DAL2 - Custom Database

Thanks, that looks like it will work just fine.

One difference I have with my data is that it is product data that is shared across multiple modules, the whole site, and multiple systems. My data is not connected to a particular module or even the portal for that matter. Although I am setting things up with DAL2, at this time I do not expect data to be altered with my modules. I will be doing a lot of retrievals but not updates and deletes.

I want to make sure that I understand the "Scope" option correctly. From what I gathered in your videos there are 2 options, "ModuleId" and "PortalId" which relate to the cache and not the database field values (There doesn't need to be a field called PortalId in my database, correct?). Because my data is shared across multiple modules is the appropriate scope "PortalId"?

10/7/2013 2:57 PM

Andy

Joined: 8/8/2013

Posts: 73

Re: DAL2 - Custom Database

Hi Scott,

I took your suggestion but am having a little trouble getting things going. I can connect to the database successfully but having problems getting it to pull a record out of the database.

My code includes a ProductRepository class that contains the CRUD. I created 2 methods to select the products from the database, getItem() and getItemByISBN(). These are very similar to your GetItem() and GetItemByName functions in the DataAccess sample code. I used SQL Profiler to look at what was being asked of the database. This is what I see from the database side of things with the two function calls:

exec sp_executesql N'SELECT NULL FROM [Product] WHERE [productCode]=@0',N'@0 nvarchar(4000)',@0=N'ANDY'

and

exec sp_executesql N'SELECT NULL FROM [Product] WHERE ISBN LIKE @0',N'@0 nvarchar(4000)',@0=N'1234567890123'

I am confused why the SQL statement is "SELECT NULL" instead of "SELECT *" or "SELECT productCode, title...". My guess is that there is something isn't quite configured right with the DAL2 mapping and it isn't connecting the field names from the Product class. The productCode field is correctly used as the Primary Key in the SQL produced by getItem() but that is the only thing I see correctly mapped.

I am not sure where to look at this point. Why is the SQL selecting null instead of my fields?

10/8/2013 9:04 AM

Andy

Joined: 8/8/2013

Posts: 73

Re: DAL2 - Custom Database

I have done some testing with the Scope attribute while running an SQL Profiler.

My table has a primary key of 'ProductCode'. If I do not include a scope option, when I execute my getProduct()/getItem() method I see the following SQL:

SELECT NULL FROM [Product]

I added the following line to my Product.cs file:

[Scope("productCode")]

After adding this line the SQL Profiler showed that this query was being attempted:

exec sp_executesql N'SELECT NULL FROM [Product] WHERE [productCode]=@0',N'@0 nvarchar(4000)',@0=N'ANDY'

I tested by modifying the string I put in the Scope attribute to "PortalId" and "Nothing". Both cases resulted in the same query as "productCode". If anyone knows why this is happening, please let me know.

I am not certain but it does not appear Scope is connected to the "SELECT NULL" problem because it is happening with or without the Scope attribute.

Does anyone have any ideas about how I can get my object connected to the database with DAL2 or why I am seeing "SELECT NULL" instead of the fields?

Page 1 of 2

1

DAL2 - Custom Database