保存的DataGridView数据到SQL Server数据库的Winform

问题描述：

这里是UI的样本图像：保存的DataGridView数据到SQL Server数据库的Winform

我工作的一个项目，将导入Excel文件并通过显示数据到一个DataGridView Windows窗体。导入Excel文件并在DataGridView中显示它工作正常，我遇到的问题是将数据保存在DataGridView中作为批量插入，当我单击Save按钮时，它显示一个

错误的屏幕截图：

An unhandled exception of type 'System.ArgumentException' occured in System.Data.dll

当我查看详细信息它显示：

No mapping exists from object type System.Windows.Forms.DataGridViewTextBoxColumn to a known managed provider native type.

代码：

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.Data.OleDb; 
using System.Data.SqlClient;

SAVE按钮代码

 private void btn_Save_Click(object sender, EventArgs e) 
     { 

      foreach (DataGridViewRow row in dataGridView1.Rows) 
      { 
       string constring = @"Data Source=databasename;Initial Catalog=Rookies;Integrated Security=True"; 

       using(SqlConnection con = new SqlConnection(constring)) 
       { 
        using (SqlCommand cmd = new SqlCommand("INSERT INTO tbl_prospects ([prospectid], [firstname], [lastname], [height], [weight], [age], [college])VALUES(@prospectid, @firstname, @lastname, @height, @weight, @age, @college)", con)) 
        { 
         cmd.Parameters.AddWithValue("@prospectid", prospectidDataGridViewTextBoxColumn); 
         cmd.Parameters.AddWithValue("@firstname", firstnameDataGridViewTextBoxColumn); 
         cmd.Parameters.AddWithValue("@lastname", lastnameDataGridViewTextBoxColumn); 
         cmd.Parameters.AddWithValue("@height", heightDataGridViewTextBoxColumn); 
         cmd.Parameters.AddWithValue("@weight", weightDataGridViewTextBoxColumn); 
         cmd.Parameters.AddWithValue("@age", ageDataGridViewTextBoxColumn); 
         cmd.Parameters.AddWithValue("@college", collegeDataGridViewTextBoxColumn); 

         con.Open(); 
         cmd.ExecuteNonQuery(); 
         con.Close(); 
        } 
       } 
      } 
      MessageBox.Show("Successfully Saved!"); 
     } 



    } 
}

我还包括以下

浏览按钮CODE

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

     if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
     { 
      this.txt_Path.Text = openFileDialog1.FileName; 
     } 
    }

LOAD按钮代码

private void btn_Load_Click(object sender, EventArgs e) 
    { 
     string PathConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txt_Path.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes;\";"; 
     OleDbConnection conn = new OleDbConnection(PathConn); 

     OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from [" + txt_Sheet.Text + "$]", conn); 
     DataTable DT = new DataTable(); 

     myDataAdapter.Fill(DT); 

     dataGridView1.DataSource = DT; 
    }

其他代码

我是C＃编程新手，想要学习它，如果有人能提前帮助我做我要做的事情，这将是我的第一个应用程序。

答

你可以从每一行的值这样

foreach (DataGridViewRow row in dataGridView.Rows) 
{ 
    // your code 

    cmd.Parameters.AddWithValue("@prospectid",row.Cells["ColumnName"].Value.ToString()); 
}

这不是批量插入，你可以找到很好的答案批量插入链接 - http://stackoverflow.com/questions/5022531/best-way-to-bulk-insert-from-ac-sharp-datatable/ 5022716＃5022716 –

谢谢！我试过这个，当我运行该文件时得到了这个错误。 {“无法找到名为prospectid的列。\ r \ nParameter name：columnName”} – jeff

检查row.Cells [“ColumnName”]中的DataGridView列的名称。通常，这与Excel文件中的列名相同 –

答

我的第一句话：只能使用1次对象SqlConnextion并且最好是现在添加的SqlTransaction对象，以免在情况下数据的部分记录DataGridView的一行错误。的答案，你需要指定每列

private void btn_Save_Click(object sender, EventArgs e) 
{ 
    string constring = @"Data Source=databasename;Initial Catalog=Rookies;Integrated Security=True"; 
    SqlConnection con = new SqlConnection(constring); 
    SqlTransaction transaction = con.BeginTransaction(); 
    try 
    { 
     con.Open(); 
     foreach (DataGridViewRow row in dataGridView1.Rows) 
     { 
      using (SqlCommand cmd = new SqlCommand("INSERT INTO tbl_prospects ([prospectid], [firstname], [lastname], [height], [weight], [age], [college])VALUES(@prospectid, @firstname, @lastname, @height, @weight, @age, @college)", con)) 
      { 
       cmd.Parameters.AddWithValue("@prospectid", row.Cells["prospectid"].Value); 
       cmd.Parameters.AddWithValue("@firstname", row.Cells["firstname"].Value); 
       cmd.Parameters.AddWithValue("@lastname", row.Cells["lastname"].Value); 
       cmd.Parameters.AddWithValue("@height", row.Cells["height"].Value); 
       cmd.Parameters.AddWithValue("@weight", row.Cells["weight"].Value); 
       cmd.Parameters.AddWithValue("@age", row.Cells["age"].Value); 
       cmd.Parameters.AddWithValue("@college", row.Cells["college"].Value); 
       cmd.Transaction = transaction; 
       cmd.ExecuteNonQuery(); 
      } 
     } 
     transaction.Commit(); 
     con.Close(); 
     MessageBox.Show("Successfully Saved!"); 
    } 
    catch (Exception ex) 
    { 
     transaction.Rollback(); 
     con.Close(); 
     MessageBox.Show(ex.Message); 
    } 
}

检查row.Cells [“ColumnName”]中的DataGridView列的名称值 –

通常，这与Excel文件中的列名相同 –

答

您可以直接使用SqlBulkCopy的写数据表到SQL Server，而不是做一行一行的单元格的值。

string constring = @"Data Source=databasename;Initial Catalog=Rookies;Integrated Security=True"; 

using (var bulkCopy = new SqlBulkCopy(constring)) 
{ 
     bulkCopy.BatchSize = 500; 
     bulkCopy.NotifyAfter = 1000; 

     bulkCopy.DestinationTableName = "TableName"; 
     bulkCopy.WriteToServer(dataTable); 
}

还有各种SqlBulkCopy构造函数可以传递SqlConnection和SqlTransaction。

保存的DataGridView数据到SQL Server数据库的Winform

相关推荐