1

I want to connect to a SQLite database. Please show me example code which WORKS. Also I want to link datagridview with the database.I use this code but it doesn't work:

private DataTable dt;
public Form1()
{
    InitializeComponent();
    this.dt = new DataTable();
}
private SQLiteConnection SQLiteConnection;
private void DataClass()
{
    SQLiteConnection = new SQLiteConnection("Data Source=PasswordManager.s3db;Version=3;");
}
private void GetData()
{
    SQLiteDataAdapter DataAdapter;
    try
    {
    SQLiteCommand cmd;
    SQLiteConnection.Open();  //Initiate connection to the db
    cmd = SQLiteConnection.CreateCommand();
    cmd.CommandText = "select*from PasswordManager;";  //set the passed query
    DataAdapter = new SQLiteDataAdapter(cmd);
    DataAdapter.Fill(dt); //fill the datasource
    dataGridView1.DataSource = dt;
}
catch(SQLiteException ex)
{
    //Add your exception code here.
}
SQLiteConnection.Close();
wingerse
  • 3,670
  • 1
  • 29
  • 61
Nikalas1111
  • 61
  • 1
  • 5
  • What have you tried so far? Also, see [this question](http://stackoverflow.com/questions/26020/what-is-the-best-way-to-connect-and-use-a-sqlite-database-from-c-sharp) – k.m Jan 15 '12 at 13:47
  • 2
    Have you [googled](https://www.google.com/?#q=c%23%20sqlite%20example) this first? – Jon Jan 15 '12 at 13:48

2 Answers2

4

You could use the System.Data.SQLite ADO.NET provider. Once you download and reference the assembly, it's pretty straightforward ADO.NET code:

using (var conn = new SQLiteConnection(@"Data Source=test.db3"))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "SELECT id FROM foo";
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            int id = reader.GetInt32(reader.GetOrdinal("id"));
            ...
        }
    }
}
Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
  • The link provided is dead. It appears that there are at least two places to get this package. https://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki and the other is the nuget site at https://www.nuget.org/packages/System.Data.SQLite – Richard Chambers May 05 '17 at 17:43
2

In addition to the answer provided by Darin, there is no "create database" command in SQLite (from what I remember). When you initiate a "SQLiteConnection", if the given database (.db3) does not exist, it automatically creates it... from there, you can then create your tables.

DRapp
  • 47,638
  • 12
  • 72
  • 142
  • i know how to do this so it is not problem for me – Nikalas1111 Jan 15 '12 at 18:05
  • 1
    @Nikalas1111, no problem, but for others who may be new to SQLite and don't know about the engine and creating connection could otherwise help them on that one missing piece that I don't think was well documented when I originally started with it :) – DRapp Jan 15 '12 at 21:33