SQLite Connection not working in C#
Here is what I did:
private void button2_Click(object sender, EventArgs e){ string dbPath = Path.Combine(Environment.CurrentDirectory, "UrduDictionary"); string connString = string.Format("Data Source={0}", dbPath); using (SQLiteConnection conn = new SQLiteConnection(connString)) { StringBuilder query = new StringBuilder(); query.Append("SELECT * "); query.Append("FROM CATIGORY_TABLE "); using (SQLiteCommand cmd = new SQLiteCommand(query.ToString(), conn)) { conn.Open(); using (SQLiteDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { Console.WriteLine("{0} {1} {2}", dr.GetValue(0), dr.GetValue(1), dr.GetValue(2)); } } } }}
just in case...
var pathDB = System.IO.Path.Combine(Environment.CurrentDirectory, "Test.sqlite"); if (!System.IO.File.Exists(pathDB)) throw new Exception(); var connection_string = String.Format("Data Source={0};Version=3;", pathDB);
This is what i did for sqlite connection with C#:
- I have downloaded Sqlite binaries from here
- then here is the code to connect with Sqlite:
string dbConnectionString = @"Data Source=Sample.s3db;Version=3;";try{ SQLiteConnection sqlite_con = new SQLiteConnection(dbConnectionString); sqlite_con.Open(); string query = "select * from test;"; SQLiteCommand sqlite_cmd = new SQLiteCommand(query, sqlite_con); SQLiteDataReader dr = sqlite_cmd.ExecuteReader(); while (dr.Read()) { MessageBox.Show(dr.GetString(1)); } sqlite_con.Close();}catch (Exception ex){ MessageBox.Show(ex.ToString());}
In case anyone wants to use it with Entity Framework below is the code (tested with EntityFramework version 4.1.0.0
)
Model and DataContext
namespace WpfApplication1.Model{ public class Movie { public Int64 Id { get; set; } public string name { get; set; } } public class MySqliteContext : DbContext { public DbSet<Movie> Movies { get; set; } }}List<Model.Movie> movies = new List<Model.Movie>();using (var context = new MySqliteContext()) movies = context.Movies.ToList();foreach (var movie in movies) MessageBox.Show(movie.name.ToString());
and here is the app.config file containing DbProviderFactories
and DefaultConnectionFactory
<?xml version="1.0"?><configuration> <configSections> <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 --> <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.1.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="true" /> </configSections> <startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0,Profile=Client" /> </startup> <system.data> <DbProviderFactories> <remove invariant="System.Data.SQLite" /> <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.84.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" /> </DbProviderFactories> </system.data> <connectionStrings> <add name="MySqliteContext" connectionString="Data Source=|DataDirectory|Sample.s3db" providerName="System.Data.SQLite" /> </connectionStrings> <entityFramework> <defaultConnectionFactory type="System.Data.SQLite.SQLiteFactory, EntityFramework"> <parameters> <parameter value="v11.0" /> </parameters> </defaultConnectionFactory> </entityFramework></configuration>