![myDataAdapter]( "myDataAdapter")
本文介绍了C＃将excel文件导入dataGridView，然后保存到数据库问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 限时删除！！ 你好。一切正常，但是当我将我的excel文件输入到我的数据网格视图并将其更新到我的数据库时，它只保存一行到我的数据库而不是另一行2.为什么不呢？ 图片： http://postimg.org/image/oj2zqbeyb/ [ ^ ] http://postimg.org/image/4jy6mv6at/ [ ^ ] 使用系统; 使用 System.Collections.Generic; 使用 System.ComponentModel; 使用 System.Data; 使用 System.Drawing; 使用 System.Linq; 使用 System.Text; 使用 System.Threading.Tasks; 使用 System.Windows.Forms; 使用 System.IO; 使用 System.Data.OleDb; 使用 System.Data.SqlClient; 命名空间登录 { public partial class EmployeeRota：表格 { string con = 数据源= dqq5ndqef2.database.windows.net;初始目录=登录;集成安全= False;用户ID = richardjacobs97;密码= *******; Connect Timeout = 15; Encrypt = False; TrustServerCertificate = False; ApplicationIntent = ReadWrite; MultiSubnetFailover = False; SqlCommandBuilder scb; DataTable dt; public EmployeeRota（） { InitializeComponent（）; } private void btnSelect_Click（ object sender，EventArgs e） { OpenFileDialog openFileDialog1 = new OpenFileDialog（）; if （openFileDialog1.ShowDialog（）== System.Windows.Forms.DialogResult.OK） { this .textBox1.Text = openFileDialog1.FileName; } } private void button1_Click（ object sender，EventArgs e） { string PathCpnn = Provider = Microsoft.Jet.OLEDB.4.0; Data Source = + textBox1.Text + ;扩展属性= \Excel 8.0; HDR =是; \;; OleDbConnection conn = new OleDbConnection（PathCpnn）; OleDbDataAdapter myDataAdapter = new OleDbDataAdapter（ 从[ + textBox2.Text + $]中选择*， conn）;在 DataTable dt = new DataTable（）; myDataAdapter.Fill（dt）; dataGridView1.DataSource = dt; myDataAdapter.Update（dt）; } private void EmployeeRota_Load（ object sender，EventArgs e） {} private void button2_Click（ object sender，EventArgs e） { string connectionString = con; 使用（SqlConnection connection = new SqlConnection（connectionString）） { SqlCommand cmd = new SqlCommand（ INSERT INTO Rota （Id，Name，DateWorking）值（@ Id，@ Name，@ Date））; cmd.CommandType = CommandType.Text; cmd.Connection = connection; SqlDataAdapter sda = new SqlDataAdapter（cmd）; for （ int i = 0 ; i < dataGridView1.Rows.Count; i ++） { cmd.Parameters.AddWithValue（ @ Id，dataGridView1.Rows [i] .Cells [ Id]。Value）; cmd.Parameters.AddWithValue（ @ Name，dataGridView1.Rows [i]。单元格[ 名称]。值）; cmd.Parameters.AddWithValue（ @ Date，dataGridView1.Rows [i]。单元格[ DateWorking]。Value）; dt = new DataTable（）; sda.Fill（dt）; dataGridView1.DataSource = dt; } } } } } 解决方案 ，conn）; DataTable dt = new DataTable（）; myDataAdapter.Fill（dt）; dataGridView1 .DataSource = dt; myDataAdapter.Update（dt）; } private void EmployeeRota_Load（ object sender，EventArgs e） {} private void button2_Click（ object sender，EventArgs e ） { string connectionString = con; 使用（SqlConnection） connection = new SqlConnection（connectionString）） { SqlCommand cmd = new SqlCommand（ INSERT INTO Rota（Id，Name，DateWorking）值（@ Id，@ Name，@ Date））; cmd.CommandType = CommandType.Text; cmd.Connection = connection; SqlDataAdapter sda = new SqlDataAdapter（cmd）; for （ int i = 0 ; i < dataGridView1.Rows.Count; i ++） { cmd.Parameters.AddWithValue（ @ Id，dataGridView1.Rows [i] .Cells [ Id]。Value）; cmd.Parameters.AddWithValue（ @ Name，dataGridView1.Rows [i]。单元格[ 名称]。值）; cmd.Parameters.AddWithValue（ @ Date，dataGridView1.Rows [i]。单元格[ DateWorking]。Value）; dt = new DataTable（）; sda.Fill（dt）; dataGridView1.DataSource = dt; } } } } } 只需将以下部分移至外面循环。在第一次保存操作后刷新网格，因此只插入第一行。 dt = new DataTable （）; sda.Fill（dt）; dataGridView1.DataSource = dt; 类似于 - SqlCommand cmd = new SqlCommand（ INSERT INTO Rota（Id，Name，DateWorking）值（@Id，@ Name，@ Date），连接）; cmd.CommandType = CommandType.Text; connection.Open（）; for （ int i = 0 ; i < dataGridView1.Rows.Count; i ++） { // 插入数据 cmd.Parameters.AddWithValue（ @ Id，dataGridView1.Rows [i] .Cells [ Id]值）。 cmd.Parameters.AddWithValue（ @ Name，dataGridView1.Rows [i]。单元格[ 名称]。值）; cmd.Parameters.AddWithValue（ @ Date，dataGridView1.Rows [i]。单元格[ DateWorking]。Value）; cmd.ExecuteNonQuery（）; cmd.Parameters.Clear（）} cmd.Dispose（）; connection.Close（）; // 将更新数据绑定到网格 cmd = new SqlCommand（ SELECT * FROM Rota ）; // 替换为您的实际查询 cmd.CommandType = CommandType.Text; cmd.Connection = connection; SqlDataAdapter sda = new SqlDataAdapter（cmd）; dt = new DataTable（）; sda.Fill（dt）; dataGridView1.DataSource = dt; 希望，它有帮助：） 使用此循环插入数据将有助于 foreach （DataGridViewRow di in dataGridView1.Rows） {在此处编写插入查询或传递Sp的参数..... } Hi guys. Everything is working fine but when i input my excel file to my datagrid view and i update it to my database, it is only saving one row to my database and not the other 2. Why not?images:http://postimg.org/image/oj2zqbeyb/[^]http://postimg.org/image/4jy6mv6at/[^]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.Data.SqlClient;namespace Login{ public partial class EmployeeRota : Form { string con = "Data Source=dqq5ndqef2.database.windows.net;Initial Catalog=Login;Integrated Security=False;User ID=richardjacobs97;Password=*******;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"; SqlCommandBuilder scb; DataTable dt; public EmployeeRota() { InitializeComponent(); } private void btnSelect_Click(object sender, EventArgs e) { OpenFileDialog openFileDialog1 = new OpenFileDialog(); if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK) { this.textBox1.Text = openFileDialog1.FileName; } } private void button1_Click(object sender, EventArgs e) { string PathCpnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes;\";"; OleDbConnection conn = new OleDbConnection(PathCpnn); OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from [" + textBox2.Text + "$]", conn); DataTable dt = new DataTable(); myDataAdapter.Fill(dt); dataGridView1.DataSource = dt; myDataAdapter.Update(dt); } private void EmployeeRota_Load(object sender, EventArgs e) { } private void button2_Click(object sender, EventArgs e) { string connectionString = con; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("INSERT INTO Rota (Id, Name, DateWorking) Values (@Id, @Name, @Date)"); cmd.CommandType = CommandType.Text; cmd.Connection = connection; SqlDataAdapter sda = new SqlDataAdapter(cmd); for (int i = 0; i < dataGridView1.Rows.Count; i++) { cmd.Parameters.AddWithValue("@Id", dataGridView1.Rows[i].Cells["Id"].Value); cmd.Parameters.AddWithValue("@Name", dataGridView1.Rows[i].Cells["Name"].Value); cmd.Parameters.AddWithValue("@Date", dataGridView1.Rows[i].Cells["DateWorking"].Value); dt = new DataTable(); sda.Fill(dt); dataGridView1.DataSource = dt; } } } } } 解决方案 ", conn); DataTable dt = new DataTable(); myDataAdapter.Fill(dt); dataGridView1.DataSource = dt; myDataAdapter.Update(dt); } private void EmployeeRota_Load(object sender, EventArgs e) { } private void button2_Click(object sender, EventArgs e) { string connectionString = con; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("INSERT INTO Rota (Id, Name, DateWorking) Values (@Id, @Name, @Date)"); cmd.CommandType = CommandType.Text; cmd.Connection = connection; SqlDataAdapter sda = new SqlDataAdapter(cmd); for (int i = 0; i < dataGridView1.Rows.Count; i++) { cmd.Parameters.AddWithValue("@Id", dataGridView1.Rows[i].Cells["Id"].Value); cmd.Parameters.AddWithValue("@Name", dataGridView1.Rows[i].Cells["Name"].Value); cmd.Parameters.AddWithValue("@Date", dataGridView1.Rows[i].Cells["DateWorking"].Value); dt = new DataTable(); sda.Fill(dt); dataGridView1.DataSource = dt; } } } } }Just move following section to the outside of the loop. It is refreshing the grid after 1st save operation and thus inserting only 1st row.dt = new DataTable();sda.Fill(dt);dataGridView1.DataSource = dt;Something like-SqlCommand cmd = new SqlCommand("INSERT INTO Rota (Id, Name, DateWorking) Values (@Id, @Name, @Date)",connection);cmd.CommandType = CommandType.Text;connection.Open();for (int i = 0; i < dataGridView1.Rows.Count; i++) { //Insert data cmd.Parameters.AddWithValue("@Id", dataGridView1.Rows[i].Cells["Id"].Value); cmd.Parameters.AddWithValue("@Name", dataGridView1.Rows[i].Cells["Name"].Value); cmd.Parameters.AddWithValue("@Date", dataGridView1.Rows[i].Cells["DateWorking"].Value); cmd.ExecuteNonQuery(); cmd.Parameters.Clear() }cmd.Dispose();connection.Close();//bind update data to gridcmd = new SqlCommand("SELECT * FROM Rota"); //replace with actual query of yourscmd.CommandType = CommandType.Text;cmd.Connection = connection;SqlDataAdapter sda = new SqlDataAdapter(cmd);dt = new DataTable();sda.Fill(dt);dataGridView1.DataSource = dt;Hope, it helps :)Use this loop for insert data it will help foreach (DataGridViewRow di in dataGridView1.Rows){ write your insert query here or pass parameters of Sp.....} 这篇关于C＃将excel文件导入dataGridView，然后保存到数据库问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 1403页，肝出来的.. 09-06 21:37