question

Booney-3048 avatar image
0 Votes"
Booney-3048 asked Booney-3048 published

Sqlite import to database with button click Winform

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

windows-formsdotnet-sqlite
5 |1600 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.

DanielZhang-MSFT avatar image
0 Votes"
DanielZhang-MSFT answered Booney-3048 commented

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.


· 3
5 |1600 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.

Daniel Zhang

I am getting 2 errors in the load excel data to database;

//load excle data to datatable
Workbook workbook = new Workbook();
workbook.LoadFromFile(filePath); workbook does not contain a definition for LoadFromFile
Worksheet sheet = workbook.Worksheets[0];
data = sheet.ExportDataTable(); worksheet does not contain a difinition for ExportDataTable

0 Votes 0 ·

Hi @Booney-3048,
You need to install Spire.Xls into your project.
86618-412.png

 using Spire.Xls;

Best Regards,
Daniel Zhang



0 Votes 0 ·
412.png (12.9 KiB)

Daniel

works great how do I copy more then 200 rows? I have 10000 rows.

0 Votes 0 ·
Booney-3048 avatar image
0 Votes"
Booney-3048 answered Booney-3048 published

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);
         }
5 |1600 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.