Coolstorage query error

211 Views Asked by At

I have been looking for an open-source alternative to Linq-To-SQL and came across Vici CoolStorage which is a perfect fit for my needs (light-weight, open source, supports Access and SQL Server).

However, I am having some trouble getting it to retrieve data (although it adds data fine), and I've replicated the same issue in 2 different environments using 2 different databases, so clearly it is something I'm doing wrong, and hopefully someone can point out what that is.

I have created a new Access mdb with 2 tables - AccountStatus and Account. AccountStatus contains AccountStatusID, AccountStatusName, while Account contains AccountID, AccountName, AccountStatusID.

I have added Vici.CoolStorage and Vici.Core references to my project (using NuGet), created a Domain folder, and added the 2 following classes to map to my tables:

AccountStatus:

using Vici.CoolStorage;

namespace ViciAccount.Domain
{
    [MapTo("AccountStatus")]
    public abstract partial class AccountStatus : CSObject<AccountStatus, int>
    {
        public abstract int AccountStatusID { get; set; }
        [ToString]
        public abstract string AccountStatusName { get; set; }

        [OneToMany(LocalKey = "AccountStatusID", ForeignKey = "AccountStatusID")]
        public abstract CSList<Account> Accounts { get; }
    }
}

Account:

using Vici.CoolStorage;

namespace ViciAccount.Domain
{
    [MapTo("Account")]
    public abstract partial class Account : CSObject<Account, int>
    {
        public abstract int AccountID { get; set; }
        [ToString]
        public abstract string AccountName { get; set; }
        public abstract int AccountStatusID { get; set; }

        [ManyToOne(LocalKey = "AccountStatusID", ForeignKey = "AccountStatusID")]
        public abstract AccountStatus AccountStatus { get; set; }
    }
}

I have then added the following code to the Load event of a Form to test:

if (!CSConfig.HasDB())
{
    CSConfig.SetDB(new CSDataProviderAccess(@"G:\FilePath\Test Project\ViciTest.mdb"));
    CSConfig.UseTransactionScope = false;
}

CSConfig.Logging = true;
CSConfig.LogFileName = @"G:\FilePath\Test Project\vici.log";

AccountStatus accountStatus = AccountStatus.New();
accountStatus.AccountStatusName = "Live";
accountStatus.Save();

accountStatus = AccountStatus.New();
accountStatus.AccountStatusName = "Closed";
accountStatus.Save();

CSList<AccountStatus> accountStatuses = AccountStatus.List();

MessageBox.Show(accountStatuses.Count.ToString());

It successfully adds the "Live" and "Closed" records, but fails when I try to query the Count of the CSList with "Error executing query. Possible syntax error", InnerException of "Object reference not set to an instance of an object.".

Does anybody have any ideas what I'm getting wrong here?

EDIT: I have swapped the Vici.CoolStorage dlls for Activa.CoolStorage dlls (found here at CodePlex and dating back to 2008) and now everything works fine, so it is definitely something to do with latest version (Vici is 1.5, Activa is 1.2). SQL Logging doesn't seem be supported in the older version though

0

There are 0 best solutions below