Sqlite import to database with button click Winform

Question

I am in need to import an Excel file into a Sqlite Table by clicking a button.
Use open file dialog select file and insert into table. I searched the Net with
no clear answer. Can someone send me a link or code sniplet?
Thanks

Answer 1

Hi Booney-3048,
First, you can use OpenFileDialog to choose file and load data into datatable.
Then you can traverse the datatable and insert each row of data into the Sqlite table.
I made a test via sql server, and you can use Microsoft.Data.SQLite instead of my System.Data.SqlClient.
Here is my test code you can refer to.

DataTable data;  
   
public void FileSelect()  
{  
    string filePath = string.Empty;  
    string fileExt = string.Empty;  
    OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file  
    file.Filter = "Discovery Excel|*.xlsx| CSV (Coming Soon)| *.csv";  
    file.Title = "Please select a valid data file";  
  
    if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file choosen by the user    
    {  
        filePath = file.FileName; //get the path of the file    
        fileExt = Path.GetExtension(filePath); //get the file extension   
        if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0)  
        {  
            try  
            {  
                //load excle data to datatable  
                Workbook workbook = new Workbook();  
                workbook.LoadFromFile(filePath);  
                Worksheet sheet = workbook.Worksheets[0];  
                data = sheet.ExportDataTable();  
  
            }  
            catch (Exception ex)  
            {  
                MessageBox.Show(ex.Message.ToString()); ;  
            }  
        }  
        else  
        {  
            MessageBox.Show("Please choose .xlsx or .CSV file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error    
        }  
    }  
             
    if (data != null && data.Rows.Count > 0)   
    {  
        string connStr ="my connectionString";  
        using (SqlConnection conn = new SqlConnection(connStr))  
        {  
            conn.Open();  
            foreach (DataRow row in data.Rows) //iterate over all rows  
            {  
                string str = "insert into test(Id,Name) values(@Id,@Name)";  
                var com = new SqlCommand(str, conn);  
                com.Parameters.AddWithValue("@Id", Convert.ToInt32(row.Field<string>("Id")));  
                com.Parameters.AddWithValue("@Name", row.Field<string>("Name"));  
  
                com.ExecuteNonQuery();  
                MessageBox.Show("added");  
            }  
        }  
    }  
              
              
}  
  
private void button1_Click(object sender, EventArgs e)  
{  
    FileSelect();  
}  

And FrankM provided a code example via SQLite.
Beside, here are also some code example you can refer to.
Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

This solved my problem

private void btn_Upload_Click(object sender, EventArgs e)
        {
            string constring = "Data Source = Document.db; Version=3";
            using (SQLiteConnection con = new SQLiteConnection(constring))
            {
                con.Open();

                using (var transaction = con.BeginTransaction())
                using (var command = con.CreateCommand())

                {
                    foreach (DataGridViewRow row in dataGridView1.Rows)

                    {

                        using (SQLiteCommand cmd = new SQLiteCommand("INSERT INTO Import_Data VALUES(@Id,@Item,@Name" +
                        ",@Feed_Location,@Sub)", con))
                        {
                            cmd.Parameters.AddWithValue("@Id", row.Cells["Column0"].Value);
                            cmd.Parameters.AddWithValue("@Item", row.Cells["Column1"].Value);
                            cmd.Parameters.AddWithValue("@Name", row.Cells["Column2"].Value);
                            cmd.Parameters.AddWithValue("@Feed_Location", row.Cells["Column3"].Value);
                            cmd.Parameters.AddWithValue("@Sub", row.Cells["Column4"].Value);

                            cmd.ExecuteNonQuery();

                        }
                    }
                    transaction.Commit();
                }
                con.Close();
            }

            MessageBox.Show("Successful Saved", "Data Inserted", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }