Insert data from excel sheet to SQL CE 3.5 database

Question

0.00/5 (No votes)

See more:

I want a simple and organized way to migrate my data from multiple excel sheets to one single SQL compact 3.5 database file
What I did to work this thing out was trying to load the excel sheet into a datagridview and then save it to the sql CE database, but it always gives error and duplicates cells in the database

EDIT:
This is the working code as per Maciej Los Insert data from excel sheet to SQL CE 3.5 database[^] answer guidance

C#

try
            {
                String sheetN = "Sheet1";
                String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                "E:\\test.xls" +
                                ";Extended Properties='Excel 8.0;HDR=YES;';";

                OleDbConnection EXcon = new OleDbConnection(constr);
                OleDbCommand oconn = new OleDbCommand("Select * From [" + sheetN + "$]", EXcon);
                EXcon.Open();

                OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
                DataTable data = new DataTable();
                sda.Fill(data);
                dataGridView1.DataSource = data;

                if (data.Rows.Count >= 1)
                {
                    MessageBox.Show("We have Rows");
                }


                string conString = (@"Data Source=E:\\test.sdf;Persist Security Info=True;");
                
                try
                {
                    using (SqlCeConnection con = new SqlCeConnection(conString))
                    {
                        con.Open();


                        SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions();
                        using (SqlCeBulkCopy bc = new SqlCeBulkCopy(con, options))
                        {
                            bc.DestinationTableName = "Niaba";
                            bc.WriteToServer(data);
                        }
                        con.Close();
                    }
                    MessageBox.Show("All data copied to database successfully");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

Posted 1-Oct-15 16:43pm

Sherif Kamel

Updated 2-Oct-15 1:57am

v2

Add a Solution

Comments

Maciej Los 2-Oct-15 2:20am

What's error message? Which line?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-10-01T20:38:00

I'd suggest to use SqlCeBulkCopy class[^] (from codeplex), which provides several options to copy data between databases in a safe way.

How to use it?
1) download and install this nuget packet[^],
2) create new project,
3) create new OleDbConnection[^] to load data from Excel into DataTable[^] object (using OleDbCommand[^] + OleDbDataReader[^]),
4) close OleDbConnection (do NOT delete DataTable object, you'll use it in the next step!),
5) create new SqlCeConnection[^],
6) create new SqlCeBulkCopy class and use WriteToServer method. Pass DataTable object as an input parameter,
7) close SqlCeConnection.

That's all!

For further details, please download the documentation[^].