1

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

Niall
  • 1,551
  • 4
  • 23
  • 40
  • Just to be sure I understand your problem correctly: the exception is thrown on the last line with "accountStatuses.Count.ToString()"? – Niels R. Dec 14 '12 at 08:21
  • Yes, exactly. It also throws the same error if I try to run any ad hoc SQL against the database, or any other type of query. But in all these situations the same code works fine using the Activa library – Niall Dec 14 '12 at 08:48
  • I would like to help you out here, so can you send me the code and MDB file by e-mail? I presume this is just a test project without any sensitive data. Check my profile for my e-mail address. – Niels R. Dec 18 '12 at 09:10
  • Thanks for the offer of help, however your email address isn't displayed when I view your profile? – Niall Dec 21 '12 at 13:53

0 Answers0