question

Bernardt-2043 avatar image
0 Votes"
Bernardt-2043 asked ·

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

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

dotnet-csharpwindows-formsdotnet-sqlite
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The Listview control is old technology. Microsoft developed the DataGridView for C#; the DataGridView is more flexible and powerful. Do you want an example of that?

0 Votes 0 ·
MattiasAsplund-3483 avatar image
0 Votes"
MattiasAsplund-3483 answered ·

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

You can view the solution at: SqliteListViewSolution


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KyleWang-MSFT avatar image
0 Votes"
KyleWang-MSFT answered ·

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();
 }


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.