C＃将excel文件导入dataGridView，然后保存到数据库问题 [英] C# Import excel file to dataGridView then save to database problem

问题描述

你好。一切正常，但是当我将我的excel文件输入到我的数据网格视图并将其更新到我的数据库时，它只保存一行到我的数据库而不是另一行2.为什么不呢？

< br $>
图片：

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）
{
}

< span class =code-keyword> 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 = < span class =code-keyword> 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 grid
cmd = new SqlCommand("SELECT * FROM Rota"); //replace with actual query of yours
cmd.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，然后保存到数据库问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！