using SqLite in Asp.Net

问题:

I'm trying to use SqLite in my Asp.Net. I have the code below in connectionstring web.config

<add name="ConnectionStringName" connectionString="data source=MembersLastLogin3.s3db" providerName="System.Data.SQLite" />

and here is the code for running query

public  int ExecuteNonQuery(string sql)
    {
        try
        {
            string con = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString; 
            SQLiteConnection cnn = new SQLiteConnection(con);
            cnn.Open();
            SQLiteCommand mycommand = new SQLiteCommand(cnn);
            mycommand.CommandText = sql;
            int rowsUpdated = mycommand.ExecuteNonQuery();
            cnn.Close();
            return rowsUpdated;              
        }
        catch { throw; }
    }
}

and this simple query

sql = "INSERT INTO LastLogin (MemId ) VALUES ('" + res + "');";
                LocalDb.ExecuteNonQuery(sql);

but I get the error "Table LastLogin doesn't exist"

I have used SqLitein .Net already but it's the first time I'm using it in Asp.Net,I'm sure the table exists but it seems it can connect to it, where is my problem? is the setting in web.config is enough? is there any tutorial available ? thanks

回答1:

Verify if the table(s) exist in your SQLite db using the GetSchema method:

using ( var conn = new SQLiteConnection( "Data Source=<PathToDb>\\MembersLastLogin3.s3db" ) )
{
  DataTable tbl = conn.GetSchema("Tables")
  // The DataTable will list all the tables in the
  // db as well as information about each table.
}

The most likely cause is the connection string you are using is not pointing to the SQLite database which is why you'll see the error table does not exist.

As a side note regarding the sample code you posted, if you are not doing anything with the exception, then there is no need to try/catch the exception if the exception is just going to be thrown back.

回答2:

I suppose your file db is stored in App_Data directory of the asp.net website.

So just try to change your connection string with:

<add name="ConnectionStringName" connectionString="data source=|DataDirectory|MembersLastLogin3.s3db" providerName="System.Data.SQLite" />

回答3:

Edit: Looks like you need to specify an absolute path: SQLite accessed by code has no tables?