SQLite Table Problem

DerWaldFried

New member
Joined
Dec 9, 2019
Messages
2
Programming Experience
Beginner
Hello dear, I am currently building a program to learn where I want to display my SQLite data in a table in my windows program.

For this I use a ListBox but also like tips, when a other Solution ist better. My problem is now, after I wrote everything as it should be right. I miss the point where I insert the DataTable data in the table. And I do not know how to do that. Bevor when I needed only small things I have do it like in this example
C#:
klist.ValueMember = "DataID"

But how can I get it all now from first name to phone is displayed in the list and so that it is taboo clear?
C#:
namespace BaldwinVS
{
    public partial class BaldwinVS : Form
    {
        public BaldwinVS()
        {
            InitializeComponent();
        }
 
        private void BaldwinVS_Load(object sender, EventArgs e)
        {
            if (!Directory.Exists("Daten"))
            {
                System.IO.Directory.CreateDirectory("Daten");
                SQLiteConnection.CreateFile("Daten/vsdaten.sqlite");
 
                SQLiteConnection dbConnection = new SQLiteConnection("Data Source = Daten/vsdaten.sqlite; Version = 3;");
                dbConnection.Open();
                string sql = "CREATE TABLE personen(vorname TEXT, nachname TEXT, ausweissnummer TEXT, telefon NUMERIC)";
                SQLiteCommand Command = new SQLiteCommand(sql, dbConnection);
                Command.ExecuteNonQuery();
 
            }
            else
            {
                if (!File.Exists("Daten/vsdaten.sqlite"))
                {
                    SQLiteConnection.CreateFile("Daten/vsdaten.sqlite");
 
                    SQLiteConnection dbConnection = new SQLiteConnection("Data Source = Daten/vsdaten.sqlite; Version = 3;");
                    dbConnection.Open();
                    string sql = "CREATE TABLE personen(vorname TEXT, nachname TEXT, ausweissnummer TEXT, telefon NUMERIC)";
                    SQLiteCommand Command = new SQLiteCommand(sql, dbConnection);
                    Command.ExecuteNonQuery();
                }
                else
                {
                    SQLiteConnection dbConnection = new SQLiteConnection("Data Source = Daten/vsdaten.sqlite; Version = 3;");
                    dbConnection.Open();
                    list_load();
                }
            }
        }
 
        private ArrayList list_load()
        {
            string sSql = "SELECT * FROM personen";
            SQLiteConnection dbConnection = new SQLiteConnection("Data Source = Daten/vsdaten.sqlite; Version = 3;");
            SQLiteCommand Command = new SQLiteCommand(sSql, dbConnection);
 
            ArrayList list = new ArrayList();
            try
            {
 
                DataTable table = GetDataTable(sSql);
 
                // Return all table names in the ArrayList
 
                foreach (DataRow row in table.Rows)
                {
                    list.Add(row.ItemArray[0].ToString());
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return list;
 
        }
 
        private DataTable GetDataTable(string sSql)
        {
            SQLiteConnection dbConnection = new SQLiteConnection("Data Source = Daten/vsdaten.sqlite; Version = 3;");
 
            try
            {
                DataTable dt = new DataTable();
                using (var c = new SQLiteConnection(dbConnection))
                {
                    c.Open();
                    using (SQLiteCommand cmd = new SQLiteCommand(sSql, c))
                    {
                        using (SQLiteDataReader rdr = cmd.ExecuteReader())
                        {
                            dt.Load(rdr);
                            return dt;
 
                            SQLiteDataAdapter sql_adapt = new SQLiteDataAdapter(sSql, dbConnection);
                          
                          
 
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }
        }
    }
}
This is my Code. I hope anything will help me with a Solution that i can learn more. Thanks a lot and i hope too that my english is okay, i am from germany. My English is not the best ^^
 
Busy right now, but I suggest using a more modern book or tutorial to learn C#. Things that make your current code dated:
- Use of ArrayList instead of the recommend List<T>.
- The old Unix and/or DOS style of having to run a program twice to get it to initialize its files -- in your case your form load event needs to be called twice. The first time to create the database, and the second time to read from the database.
- Use of WinForms of WPF or Xamarin.

In general though the correct modern approach is to create a class that holds a row's worth of data (name, phone number, etc.) and then make a list of that class. You then only load enough of what you need into the list and display that into a list view or data view grid. A slightly older approach from the early days of WinForms is to load data into a DataTable and then just assign that table as the data source for a data grid, list view, data grid view, list box, or combo box.
 
Are you saying that you want to display just the data from one column or the data from all columns? If it's all columns then a ListBox is a poor choice. Use a DataGridView instead. Simply assign a DataTable to the DataSource property of the grid and it will automatically create columns and rows.
 
SkyDiver i have a old Galileo Programming Book. I will show to newer ^^

jmcilhinney okay, thanks for this informations. I will try it.

Are you saying that you want to display just the data from one column or the data from all columns? If it's all columns then a ListBox is a poor choice. Use a DataGridView instead. Simply assign a DataTable to the DataSource property of the grid and it will automatically create columns and rows.
One Question to this. I have show to the DataGridView and the other Points. But in the Setup i dont can do anythingh with SQLite or with normal mysql. Must i do anything for this options ? I mean the only options that i have is azur sql and microsoft sql :S
 
By "setup" I assume you are talking about the Windows Forms Designer. Not everything needs to be dragged and dropped or configured in the designer. You can simply write some code.
 
Back
Top Bottom