How do i write code for excel to sql server bulk exporting in winforms c#

Question

0.00/5 (No votes)

See more:

Hello All,

I have created a code for exporting Excel data to SQL Server in C#(WinForms). I have also included a Datagridview to view the data before inserting on to the SQL table. Well, everything seems to be working, the Datagridview displays the excel data but unable to insert any records/data into the SQL table.

No error or exception thrown by debugger, i couldn't understand what went wrong.

Please, help me out with your valuable suggestion to eradicate the unknown error.

I'm pasting the whole code for better understanding.

What I have tried:

C#

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using System.Configuration;
using System.Data.SqlClient;

namespace Sample_AutoFillForBrowser_Application
{

    public partial class Form2 : Form
    {
        private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
        private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";

        public Form2()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            openFileDialog1.ShowDialog();

        }

        private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
        {
            string filePath = openFileDialog1.FileName;
            string extension = Path.GetExtension(filePath);
            string conString = "";
            string sheetName = "";
            switch (extension)
            {
                case ".xls":
                    conString = string.Format(Excel03ConString, filePath, "YES");
                    break;
                case ".xlsx":
                    conString = string.Format(Excel07ConString, filePath, "YES");
                    break;
            }
            using (OleDbConnection con = new OleDbConnection(conString))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = con;
                    con.Open();
                    DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    sheetName = dt.Rows[0]["Table_Name"].ToString();
                    con.Close();
                }
            }
            using (OleDbConnection con = new OleDbConnection(conString))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    OleDbDataAdapter oda = new OleDbDataAdapter();
                    cmd.CommandText = "SELECT Salutation, FirstNameCandidate, MiddleNameCandidate, LastNameCandidate, Gender, DateofBirth, Aadharno, Religion, TraineeAddress, TraineeState, TraineeDistrict, PINCode, FirstNameofFatherGuardian, LastNameofFatherGuardian, ContactnoofTrainee, EmailAddressofTrainee, CourseFee, Enrollmentnumber FROM [" + sheetName + "]";
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    oda.SelectCommand = cmd;
                    DataTable dt = new DataTable();
                    oda.Fill(dt);
                    con.Close();
                    dataGridView1.DataSource = dt;
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[18]
            {
            new DataColumn("Salutation", typeof(string)),
            new DataColumn("FirstNameCandidate", typeof(string)),
            new DataColumn("MiddleNameCandidate", typeof(string)),
            new DataColumn("LastNameCandidate", typeof(string)),
            new DataColumn("Gender", typeof(string)),
            new DataColumn("DateofBirth", typeof(string)),
            new DataColumn("Aadharno", typeof(string)),
            new DataColumn("Religion", typeof(string)),
            new DataColumn("TraineeAddress", typeof(string)),
            new DataColumn("TraineeState", typeof(string)),
            new DataColumn("TraineeDistrict", typeof(string)),
            new DataColumn("PINCode", typeof(string)),
            new DataColumn("FirstNameofFatherGuardian", typeof(string)),
            new DataColumn("LastNameofFatherGuardian", typeof(string)),
            new DataColumn("ContactnoofTrainee", typeof(string)),
            new DataColumn("EmailAddressofTrainee", typeof(string)),
            new DataColumn("CourseFee", typeof(string)),
            new DataColumn("Enrollmentnumber", typeof(string))

    });
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {

                string Salutation = (null != row.Cells[0].Value) ? row.Cells[0].Value.ToString() : string.Empty;
                string FirstNameCandidate = (null != row.Cells[1].Value) ? row.Cells[1].Value.ToString() : string.Empty;
                string MiddleNameCandidate = (null != row.Cells[2].Value) ? row.Cells[2].Value.ToString() : string.Empty;
                string LastNameCandidate = (null != row.Cells[3].Value) ? row.Cells[3].Value.ToString() : string.Empty;
                string Gender = (null != row.Cells[4].Value) ? row.Cells[4].Value.ToString() : string.Empty;
                string DateofBirth = (null != row.Cells[5].Value) ? row.Cells[5].Value.ToString() : string.Empty;
                string Aadharno = (null != row.Cells[6].Value) ? row.Cells[6].Value.ToString() : string.Empty;
                string Religion = (null != row.Cells[7].Value) ? row.Cells[7].Value.ToString() : string.Empty;
                string TraineeAddress = (null != row.Cells[8].Value) ? row.Cells[8].Value.ToString() : string.Empty;
                string TraineeState = (null != row.Cells[9].Value) ? row.Cells[9].Value.ToString() : string.Empty;
                string TraineeDistrict = (null != row.Cells[10].Value) ? row.Cells[10].Value.ToString() : string.Empty;
                string PINCode = (null != row.Cells[11].Value) ? row.Cells[11].Value.ToString() : string.Empty;
                string FirstNameofFatherGuardian = (null != row.Cells[12].Value) ? row.Cells[12].Value.ToString() : string.Empty;
                string LastNameofFatherGuardian = (null != row.Cells[13].Value) ? row.Cells[13].Value.ToString() : string.Empty;
                string ContactnoofTrainee = (null != row.Cells[14].Value) ? row.Cells[14].Value.ToString() : string.Empty;
                string EmailAddressofTrainee = (null != row.Cells[15].Value) ? row.Cells[15].Value.ToString() : string.Empty;
                string CourseFee = (null != row.Cells[16].Value) ? row.Cells[16].Value.ToString() : string.Empty;
                string Enrollmentnumber = (null != row.Cells[17].Value) ? row.Cells[17].Value.ToString() : string.Empty;

                dt.Rows.Add(Salutation, FirstNameCandidate, MiddleNameCandidate, LastNameCandidate, Gender, DateofBirth, Aadharno, Religion, TraineeAddress, TraineeState, TraineeDistrict, PINCode, FirstNameofFatherGuardian, LastNameofFatherGuardian, ContactnoofTrainee, EmailAddressofTrainee, CourseFee, Enrollmentnumber);

            }
            if (dt.Rows.Count > 0)
            {
                string str = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
                using (SqlConnection con = new SqlConnection(str))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        sqlBulkCopy.DestinationTableName = "dbo.AutoFill";

                        sqlBulkCopy.ColumnMappings.Add("Salutation", "Salutation");
                        sqlBulkCopy.ColumnMappings.Add("FirstNameCandidate", "FirstNameCandidate");
                        sqlBulkCopy.ColumnMappings.Add("MiddleNameCandidate", "MiddleNameCandidate");
                        sqlBulkCopy.ColumnMappings.Add("LastNameCandidate", "LastNameCandidate");
                        sqlBulkCopy.ColumnMappings.Add("Gender", "Gender");
                        sqlBulkCopy.ColumnMappings.Add("DateofBirth", "DateofBirth");
                        sqlBulkCopy.ColumnMappings.Add("Aadharno", "Aadharno");
                        sqlBulkCopy.ColumnMappings.Add("Religion", "Religion");
                        sqlBulkCopy.ColumnMappings.Add("TraineeAddress", "TraineeAddress");
                        sqlBulkCopy.ColumnMappings.Add("TraineeState", "TraineeState");
                        sqlBulkCopy.ColumnMappings.Add("TraineeDistrict", "TraineeDistrict");
                        sqlBulkCopy.ColumnMappings.Add("PINCode", "PINCode");
                        sqlBulkCopy.ColumnMappings.Add("FirstNameofFatherGuardian", "FirstNameofFatherGuardian");
                        sqlBulkCopy.ColumnMappings.Add("LastNameofFatherGuardian", "LastNameofFatherGuardian");
                        sqlBulkCopy.ColumnMappings.Add("ContactnoofTrainee", "ContactnoofTrainee");
                        sqlBulkCopy.ColumnMappings.Add("EmailAddressofTrainee", "EmailAddressofTrainee");
                        sqlBulkCopy.ColumnMappings.Add("CourseFee", "CourseFee");
                        sqlBulkCopy.ColumnMappings.Add("Enrollmentnumber", "Enrollmentnumber");


                        con.Open();
                        sqlBulkCopy.WriteToServer(dt);
                        con.Close();
                    }
                }
            }
            this.Close();
        }
    }
}

Posted 4-Mar-16 9:02am

Member 10376341

Updated 4-Mar-16 20:04pm

v2

Add a Solution

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)

OriginalGriff · Answer 1 · 2016-03-04T20:04:00

Solution 1

We can't tell, because we don't have access to your data, and that's going to be the important bit here.
So start with the debugger: put a breakpoint on the line:

C#

if (dt.Rows.Count > 0)

in your button2_Click handler, and run your app until it hits it.
Then start looking at exactly what you have to play with: Are there any rows? How many? What do they contain?
When you are sure you have data, start stepping through and looking at exactly what happens at each step.
That should give you an idea as to what is happening, or at least better information to try and explain it to us.
But we can't do any of that for you!

And why on earth are you using the DGV cell content, instead of the DataTable you loaded it from?

Posted 4-Mar-16 20:04pm

OriginalGriff

Comments

Maciej Los 5-Mar-16 7:00am

The last statement is my favorite!

Member 10376341 5-Mar-16 14:52pm

Thank you for the reply.
I guess the last line gives me the answer. I seriously think i made a mistake taking data from the DataGridView. I shall recode and take the data from DataTable instead.

Thanks for your time.

OriginalGriff 5-Mar-16 15:36pm

You're welcome!

Member 10376341 7-Mar-16 2:18am

Hello!

I have deleted the DataGridView and now a new exception raised: "The Column from the data source doesn't match". I have inspected thoroughly and there was no different column names in source and destination tables.

What could be the problem?

Member 10376341 12-Mar-16 5:57am

What is wrong in my code. Unable to import data from Excel to SQL Server.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;

namespace SimpleExcelLoader
{
public partial class Form1 : Form
{
string filename="";

public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();

openFileDialog1.InitialDirectory = "c:\\";
openFileDialog1.Filter = "Excel 2007 files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
openFileDialog1.FilterIndex = 2;
openFileDialog1.RestoreDirectory = true;

if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
filename = openFileDialog1.FileName;
textBox1.Text = filename;
button2.Enabled = true;
toolStripStatusLabel1.Text = "File Selected. Enter DB Info and click Upload Data";

}
}

private void button2_Click(object sender, EventArgs e)
{

//step 1
// Load Excel data into DataTable
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=YES;'";

string strSQL = "SELECT * FROM [Sheet1$]";
OleDbConnection excelConnection = new OleDbConnection(connectionString);
excelConnection.Open(); // This code will open excel file.

OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);

// create data table
DataTable dTable = new DataTable();
//fill table with echel data
dataAdapter.Fill(dTable);

//step 2
//connect to server

string str = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (SqlConnection con = new SqlConnection(str))
{

con.Open();

using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(con))
{
//Destination Table must match columns in Excel sheet
bulkCopy.DestinationTableName = "dbo.Test";

try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(dTable);
toolStripStatusLabel1.Text="Data Uploaded";
}
catch (Exception ex)
{

toolStripStatusLabel1.Text=ex.Message;
}

con.Close();

}

}

// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();

excelConnection.Close();
excelConnection.Dispose();
}

}

}

OriginalGriff 12-Mar-16 6:02am

Read what I said:
"We can't tell, because we don't have access to your data, and that's going to be the important bit here.
So start with the debugger..."
I can't run your code to get the same results, because I don't have access to your Excel data, or your SQL tables. So use the debugger, and start looking at what you get and what happens to it. I can't do that!

Member 10376341 12-Mar-16 7:53am

Yes, you told me to put breakpoint and see the results. I was googling around and came across the code which is apt for me, so i adapted it, still am unable to insert data into the database.

OriginalGriff 12-Mar-16 8:04am

Development isn't about "finding code" - it's about understand how the code works, and more importantly why it works, and why it doesn't work.
Just finding code and trying to "hammer it into shape" is like "finding an engine" and trying to fit it to your motorcycle! It may fit, it may not. It may fit but be so thirsty that the whole fuel tank empties in 100 yards! :laugh:
Use the debugger, and find out what exactly is going on. Until you do, nothing is going to work for you...

Member 10376341 12-Mar-16 15:25pm

Thank you for showing me a right path. I shall now definitely find out the issue using the debugger.

Thanks
Saikrishna