Problem about SQLite and Linq to Entities

I have a web project developed in VS2008, which use System.Data.SQLite for .Net 2.0 and Linq to Entities to access data.

Here is the source layout:

image

image

image

You can see that i have a SQLite database Test_DB.db3 in SQLite folder. In my develop machine, i use connectiongstring created by Linq to Entities named “Test_DBEntities”, and don’t have the DBProviderFactories section, it run well. The Default.aspx page display data and the pages in Admin folder manage the data.

I publish the website in my product machine, it can not run, the error is “Could not load the assembly System.Data.SQLite”,

I search a solution, which ask me add the DBProviderFactories to the web.config file and add new reference to System.Data.SQLite.Linq.ddl. After do this, i can view the Default.aspx which display data. But i can not manage my data through the pages in Admin folder. I get error “Can not open the database file”, some people said the database can not in App_Data, but my file is not in App_Data, i still get the error.

I feel strange when i view the default.aspx, Test.edmx get data successfully, but when i view pages in Admin, for example, Manage.aspx, it will get the error “Can not open the database file”, The Manage.aspx get data only, no else. Following is my code to access data:

using (Test_DBEntities osDataContext = new Test_DBEntities(Common.GetConnString()))
{                    
     OS_Module module = osDataContext.OS_Module.First<OS_Module>(m => m.Status == "Y" && m.ModuleValue == this.ModuleValue);
       this.modelTitle.InnerHtml = module.ModuleTitle;
       this.modelDetail.InnerHtml = Server.HtmlDecode(module.ModuleDetail);
}

I can use Default.aspx display different data, which get data successfully each time, but when display data use Manage.aspx in Admin folder, i will get the error “Can not open the database file”.

Any one who gets a solution, please comment here. Thanks very much!

11 Comments

  • You are serching the db in the directory d:\work station\sqlite\test_db.db3.
    You must verify that this path exists in poduction

  • Yes,
    I am sure "d:\work station\sqlite\test_db.db3" exists.


    "i suggest you to change your data source in

    data source=|DataDirectory|\test_db.sqlite'"

    and then set AppDomain.CurrentDomain.SetData("DataDirectory",..."
    difference?

  • What is the result of Common.GetConnString()

  • What is the result of Common.GetConnString()

  • What is the result of Common.GetConnString()

  • What is the result of Common.GetConnString()

  • Common.GetConnString()

    public static string GetConnString()
    {
    string connString = "metadata=res://*/OutsourcingSpot.csdl|"
    +"res://*/OutsourcingSpot.ssdl|res://*/OutsourcingSpot.msl;"
    + "provider=System.Data.SQLite;provider connection string='data source=\""
    +HttpContext.Current.Server.MapPath("SQLite/Test_DB.db3")
    +"\"'";
    return connString;
    }

    Because on product server, i don't know the physical path, so i use the Common.GetConnString() instead.

  • MapPath("SQLite/Test_DB.db3") this return the path relative to the aspx

    so MapPath("SQLite/Test_DB.db3") called from default.aspx return .\sqlite\test_db.db3
    while MapPath("SQLite/Test_DB.db3") called from admin\manage.aspx return admin\sqlite\test_db.db3

    i think you must use MapPath("~/SQLite/Test_DB.db3")

  • Yes, I feel shamed.
    Thanks for your patience!

    I must use Server.MapPath("~/SQLite/Test_DB.db3") to get the path which related to root path of the virtual directory.
    And Server.MapPath("SQLite/Test_DB.db3") get the path related to the current directory.

  • Ok it works, but i think that the better solutions is change your connectionstirng (in web.config) with data source=|DataDirectory|\test_db.db3'" and move test_db.d3 in app_data so you avoid to use Common.GetConfig() and you website works on all environment (development,testing, production)

  • It must be the error information which confused me "Can not open the database file", I think it should be "Can not find the database file".
    Thanks!

Comments have been disabled for this content.