C# - Populate ComboBox With SQLite

Submitted by razormist on Friday, April 27, 2018 - 20:32.

In this tutorial we will create a Populate ComboBox With SQLite using C#. C# is a general-purpose, object-oriented programming language. C# automatically manages inaccessible object memory using a garbage collector, which eliminates developer concerns and memory leaks. C# is faster than dynamically typed languages because things are more clearly defined. It contains several classes that support any C# platforms, like game development. It has a friendly environment for all new developers. So let's do the coding.

Getting Started

First you will have to download & install the Visual Studio. Visual Studios is an open source development feel free to create any application that you want. Here's the link for the Visual Studio https://www.visualstudio.com/. Here's the link for the SQLite Browser http://sqlitebrowser.org/.

Setting up SQLite

SQLite is very carefully tested prior to every release and relevant to use in some way. SQLite is very usable in any environments especially in embedded devices. First all you need to do is to install the components of the SQLIte database, by right clicking in the Main project title in the solution explorer then selecting the Manage NuGet Packages. tut1

Then go to the browse and search sqlite, after that install it and wait until the process is completed. tut2

Next go to the Updates and update the needed framework to make sqlite work properly. tut3

Note: Update only the framework if there is an available new update.

Application Design

We will now create the design for the application, first locate the designer file called form1.Designer.cs, this is the default name when you create a new windows form. Rename the form as Main.cs and then write these codes inside your designer file.

namespace Populate_Combox_With_SQLite
{
    partial class Main
    {
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;
 
        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }
 
        #region Windows Form Designer generated code
 
        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.label1 = new System.Windows.Forms.Label();
            this.txt_name = new System.Windows.Forms.TextBox();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.rb_female = new System.Windows.Forms.RadioButton();
            this.rb_male = new System.Windows.Forms.RadioButton();
            this.label2 = new System.Windows.Forms.Label();
            this.label3 = new System.Windows.Forms.Label();
            this.comboBox1 = new System.Windows.Forms.ComboBox();
            this.button1 = new System.Windows.Forms.Button();
            this.groupBox1.SuspendLayout();
            this.SuspendLayout();
            // 
            // label1
            // 
            this.label1.AutoSize = true;
            this.label1.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label1.Location = new System.Drawing.Point(64, 85);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(86, 27);
            this.label1.TabIndex = 0;
            this.label1.Text = "NAME:";
            // 
            // txt_name
            // 
            this.txt_name.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.txt_name.Location = new System.Drawing.Point(156, 82);
            this.txt_name.Name = "txt_name";
            this.txt_name.Size = new System.Drawing.Size(347, 35);
            this.txt_name.TabIndex = 1;
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.button1);
            this.groupBox1.Controls.Add(this.comboBox1);
            this.groupBox1.Controls.Add(this.label3);
            this.groupBox1.Controls.Add(this.rb_female);
            this.groupBox1.Controls.Add(this.rb_male);
            this.groupBox1.Controls.Add(this.label2);
            this.groupBox1.Controls.Add(this.label1);
            this.groupBox1.Controls.Add(this.txt_name);
            this.groupBox1.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.groupBox1.Location = new System.Drawing.Point(32, 12);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(588, 396);
            this.groupBox1.TabIndex = 2;
            this.groupBox1.TabStop = false;
            this.groupBox1.Text = "BASIC INFORMATION";
            // 
            // rb_female
            // 
            this.rb_female.AutoSize = true;
            this.rb_female.Location = new System.Drawing.Point(294, 160);
            this.rb_female.Name = "rb_female";
            this.rb_female.Size = new System.Drawing.Size(117, 31);
            this.rb_female.TabIndex = 4;
            this.rb_female.TabStop = true;
            this.rb_female.Text = "Female";
            this.rb_female.UseVisualStyleBackColor = true;
            // 
            // rb_male
            // 
            this.rb_male.AutoSize = true;
            this.rb_male.Location = new System.Drawing.Point(184, 160);
            this.rb_male.Name = "rb_male";
            this.rb_male.Size = new System.Drawing.Size(88, 31);
            this.rb_male.TabIndex = 3;
            this.rb_male.TabStop = true;
            this.rb_male.Text = "Male";
            this.rb_male.UseVisualStyleBackColor = true;
            // 
            // label2
            // 
            this.label2.AutoSize = true;
            this.label2.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label2.Location = new System.Drawing.Point(29, 164);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(121, 27);
            this.label2.TabIndex = 2;
            this.label2.Text = "GENDER:";
            // 
            // label3
            // 
            this.label3.AutoSize = true;
            this.label3.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label3.Location = new System.Drawing.Point(79, 240);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(71, 27);
            this.label3.TabIndex = 5;
            this.label3.Text = "CITY:";
            // 
            // comboBox1
            // 
            this.comboBox1.FormattingEnabled = true;
            this.comboBox1.Location = new System.Drawing.Point(156, 237);
            this.comboBox1.Name = "comboBox1";
            this.comboBox1.Size = new System.Drawing.Size(347, 35);
            this.comboBox1.TabIndex = 6;
            // 
            // button1
            // 
            this.button1.Location = new System.Drawing.Point(191, 327);
            this.button1.Name = "button1";
            this.button1.Size = new System.Drawing.Size(220, 41);
            this.button1.TabIndex = 7;
            this.button1.Text = "SUBMIT";
            this.button1.UseVisualStyleBackColor = true;
            this.button1.Click += new System.EventHandler(this.Submit);
            // 
            // Main
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(9F, 20F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(644, 434);
            this.Controls.Add(this.groupBox1);
            this.Name = "Main";
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Main";
            this.Load += new System.EventHandler(this.Main_Load);
            this.groupBox1.ResumeLayout(false);
            this.groupBox1.PerformLayout();
            this.ResumeLayout(false);
 
        }
 
        #endregion
 
        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.TextBox txt_name;
        private System.Windows.Forms.GroupBox groupBox1;
        private System.Windows.Forms.RadioButton rb_male;
        private System.Windows.Forms.Label label2;
        private System.Windows.Forms.RadioButton rb_female;
        private System.Windows.Forms.ComboBox comboBox1;
        private System.Windows.Forms.Label label3;
        private System.Windows.Forms.Button button1;
    }
}

or also you create the layout by dragging the proper tools to the forms.

Creating the Script

We will now create the script to make things work. To do that go to the csharp script called Main.cs then right click and select view code, this will force you to go to the text editor. Then import these important modules inside the Class of the form.

using System.Data.SQLite;
using System.IO;

Next write these variable inside the Main class:

SQLiteConnection conn;
SQLiteCommand cmd;
SQLiteDataReader dr;
String connectString;

After that we need to create the database for the application, first write these blocks of codes.

private void GenerateDatabase() {
            String path = Application.StartupPath + @"\data.db";
            if (!File.Exists(path)) {
                conn = new SQLiteConnection(connectString);
                conn.Open();
                String sql = "CREATE TABLE member (ID INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, gender TEXT, city TEXT)";
                cmd = new SQLiteCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
 
                GenerateCityTable();
            }
 
        }
 
        private void GenerateCityTable() {
            conn = new SQLiteConnection(connectString);
            conn.Open();
            String sql = "CREATE TABLE city (ID INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)";
            cmd = new SQLiteCommand(sql, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
 
            InserCity();
        }
 
        private void InserCity() {
            String[] city = CityList();
 
            try
            {
                conn = new SQLiteConnection(connectString);
                cmd = new SQLiteCommand();
                cmd.CommandText = @"INSERT INTO city (name) VALUES(@name)";
                cmd.Connection = conn;
 
                cmd.Parameters.Add(new SQLiteParameter("@name", DbType.String));
                conn.Open();
 
                foreach (String list in city) {
                    cmd.Parameters[0].Value = list;
 
                    cmd.ExecuteNonQuery();
                }
 
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
 
        private String[] CityList() {
            String[] city = {
                "New York",
                "Los Angeles",
                "Chicago",
                "Houston",
                "Philadelphia",
                "Phoenix",
                "San Antonio",
                "San Diego"
            };
 
            return city;
        }

Then initialize the methods inside the Main method.

 public Main()
        {
            InitializeComponent();
            connectString = @"Data Source=" + Application.StartupPath + @"\data.db;version=3";
            GenerateDatabase();
        }

To populate the ComboBox, double click the form to automatically create a Form Load method, And then write these codes inside of it to retrieve the data from the database.

try
            {
                conn = new SQLiteConnection(connectString);
                cmd = new SQLiteCommand();
                cmd.CommandText = @"SELECT * FROM city ORDER BY name ASC";
                cmd.Connection = conn;
                conn.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read()) {
                    comboBox1.Items.Add(dr["name"]);
                }
 
                dr.Close();
                conn.Close();
 
            }
            catch(Exception ex) {
                MessageBox.Show(ex.Message);
            }

Lastly, to save the data to the database we will create a script to make it happen. All you have to do is just write down these codes inside the Submit method from the Event of the button.

private void Submit(object sender, EventArgs e) {
 
            String gender = "";
 
            if (rb_male.Checked)
            {
                gender = "Male";
            }
            else if (rb_female.Checked)
            {
                gender = "Female";
            }
 
 
            if (txt_name.Text == "" || gender == "" || comboBox1.Text == "")
            {
                MessageBox.Show("Required Fields!");
            }
            else {
                try
                {
                    conn = new SQLiteConnection(connectString);
                    cmd = new SQLiteCommand();
                    cmd.CommandText = @"INSERT INTO member (name, gender, city) VALUES(@name, @gender, @city)";
                    cmd.Connection = conn;
                    cmd.Parameters.Add(new SQLiteParameter("@name", txt_name.Text));
                    cmd.Parameters.Add(new SQLiteParameter("@gender", gender));
                    cmd.Parameters.Add(new SQLiteParameter("@city", comboBox1.Text));
                    conn.Open();
 
                    int i = cmd.ExecuteNonQuery();
 
                    if (i == 1)
                    {
                        MessageBox.Show("Successfully Created!");
                        txt_name.Text = "";
                        gender = "";
                        comboBox1.Text = "";
                        rb_male.Checked = false;
                        rb_female.Checked = false;
                    }
 
                    conn.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

Try to run the application and see if it works. There you go we successfully created a Populate ComboBox With SQLite using C#. I hope that this tutorial help you understand on how to develop an application using C#. For more updates and tutorials just kindly visit this site. Enjoy Coding!!!

Add new comment

2145 views