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:
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();
}
}
}