我如何编写excel代码到winforms c＃sql server批量导出＃ [英] How do i write code for excel to sql server bulk exporting in winforms c#
本文介绍了我如何编写excel代码到winforms c＃sql server批量导出＃的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
Hello All，
我创建了一个代码，用于在C＃（WinForms）中将Excel数据导出到SQL Server。我还包括一个Datagridview来在插入SQL表之前查看数据。好吧，一切似乎都在工作，Datagridview显示excel数据，但无法将任何记录/数据插入SQL表。
调试器没有抛出任何错误或异常，我无法理解出了什么问题。
请帮助我提出你消除未知错误的宝贵建议。
我正在粘贴整个代码以便更好地理解。
我尝试了什么：
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();
}
}
}
推荐答案
我们无法分辨，因为我们没有可以访问你的数据，这将是重要的一点。
所以从调试器开始：在线上放一个断点：
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:
if (dt.Rows.Count > 0)在你的button2_Click处理程序中，并运行你的应用程序，直到它命中它。
然后开始看你到底要玩什么：有没有行？多少？它们包含什么？
当你确定你有数据时，开始逐步完成并仔细查看每一步发生的事情。
那可以让你知道发生了什么，或者至少有更好的信息可以尝试向我们解释。
但是我们不能为你做任何事情！
为什么你在使用DGV单元格内容而不是加载它的DataTable吗？
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?
这篇关于我如何编写excel代码到winforms c＃sql server批量导出＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
