Displying data from Sqlite database in a listview in a winform app using C#

Bernardt 1 Reputation point
2021-03-08T14:56:34.793+00:00

Hi, I have a Forms app connected to a sqlite database and I'm trying to display the data form a table in the database in a listview upon initial start up of the app.

Any suggestions and code examples of how to:

1: Display data from sqlite database table in a listview.

2: Do the ubove upon initial start up of the app.

Would be much appreciated.
Thanks

Windows Forms
Windows Forms
A set of .NET Framework managed libraries for developing graphical user interfaces.
1,811 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,488 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,099 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Mattias Asplund 236 Reputation points
    2021-03-08T17:21:06.343+00:00

    Since I can not upload a Visual Studio solution here, I created a GitHub repository for you.

    You can view the solution at: SqliteListViewSolution

    0 comments No comments

  2. Kyle Wang 5,526 Reputation points
    2021-03-09T02:06:24.2+00:00

    Assume that you have add a ".db" file under path ProjectPath\bin\Debug, make sure you have installed Nuget package "System.Data.SQLite".

    First, configure the connection string in App.config

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
      ...
      <connectionStrings>
         <add name="SQLiteDbContext" connectionString="Data Source=MyDatabase.db" providerName="System.Data.SQLite.EF6" />
      </connectionStrings>
    </configuration>
    

    Second, set listview's "View" property and add columns.

    public Form1()
    {
        InitializeComponent();
    
        // set view and add new columns
        listView1.View = View.Details;
        listView1.Columns.Add("ID", 120, HorizontalAlignment.Left);
        listView1.Columns.Add("Name", 120, HorizontalAlignment.Left);
        listView1.Columns.Add("Score", 120, HorizontalAlignment.Left);
    }
    

    Third, call "SQLiteDataAdapter.Fill()" to get table from database, use foreach to traverse the table and add new "ListViewItem" to listview1.

    private void LoadDataFromDB()
    {
        SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.db");
        m_dbConnection.Open();
    
        using (SQLiteConnection conn = new SQLiteConnection(m_dbConnection))
        {
            SQLiteDataAdapter sda = new SQLiteDataAdapter("Select * From MyTable", conn); // MyTable (ID varchar(20), Name varchar(50) ,Score int)
            DataSet ds = new DataSet();
            sda.Fill(ds);
    
            listView1.BeginUpdate();
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                ListViewItem lvi = new ListViewItem();
                lvi.Text = row["ID"].ToString();
                lvi.SubItems.Add(row["Name"].ToString());
                lvi.SubItems.Add(row["Score"].ToString());
                listView1.Items.Add(lvi);
            }
            this.listView1.EndUpdate();
        }
    
        m_dbConnection.Close();
    }
    

    As to "initial start up of the app", you can subscribe to "Form1.Load" Event and call LoadDataFromDB() in Method "Form1_Load()".

    public Form1()
    {
        InitializeComponent();
        this.Load += Form1_Load;
        // set view and add new columns
        //...
    }
    
    private void Form1_Load(object sender, EventArgs e)
    {
        LoadDataFromDB();
    }