使用C＃使用CSV文件填充DataGridView，并使用结果更新Access数据库 [英] Using C# to populate a DataGridView with CSV file, and update Access database with results
本文介绍了使用C＃使用CSV文件填充DataGridView，并使用结果更新Access数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有另一种形式（见下图），允许用户单击按钮（按钮1 ）打开CSV文件，使用openFileDialog，并在formGridView中显示所选文件的内容，格式为（示例如下）。
我的目标：我想要一个按钮（按钮3），在同一个表单上，将dataGridView的显示结果提交到前面提到的Access数据库中。 strong>
似乎我拥有我需要的所有组件。感觉我没有太远，但在我的代码中似乎还有一些错误和/或缺失。我一直在努力完成这个几个星期。请帮助!!!
以下是窗体的屏幕截图以及窗体的完整代码。所有的帮助是非常感谢！
使用系统;
使用System.Collections.Generic;
使用System.ComponentModel;
使用System.Data;
使用System.Drawing;
使用System.Linq;
使用System.Text;
使用System.Windows.Forms;
使用System.IO;
使用System.Globalization;
使用System.Configuration;
使用System.Data.OleDb;
命名空间csvToGrid
{
public partial class Import：Form
{
public Import（）
{
InitializeComponent（） ;
}
public void button1_Click（object sender，EventArgs e）
{
string delimiter =，;
string tablename =medTable;
DataSet dataset = new DataSet（）;
OpenFileDialog openFileDialog1 = new OpenFileDialog（）;
openFileDialog1.Filter =CSV文件（* .csv）| * .csv |所有文件（*。*）| *。*;
openFileDialog1.FilterIndex = 1;
if（openFileDialog1.ShowDialog（）== DialogResult.OK）
{
if（MessageBox.Show（你确定要从\\\
+ openFileDialog1导入数据。 FileName +？，Are you sure？，MessageBoxButtons.YesNo）== DialogResult.Yes）
{
filename = openFileDialog1.FileName;
StreamReader sr = new StreamReader（filename）;
string csv = File.ReadAllText（openFileDialog1.FileName）;
dataset.Tables.Add（tablename）;
dataset.Tables [tablename] .Columns.Add（Prescription）;
dataset.Tables [tablename] .Columns.Add（客户名称）;
dataset.Tables [tablename] .Columns.Add（Medication）;
dataset.Tables [tablename] .Columns.Add（Quantity）;
dataset.Tables [tablename] .Columns.Add（Date Filled）;
string allData = sr.ReadToEnd（）;
string [] rows = allData.Split（\r.ToCharArray（））;
foreach（行中的字符串r）
{
string [] items = r.Split（delimiter.ToCharArray（））;
dataset.Tables [tablename] .Rows.Add（items）;
}
this.dataGridView1.DataSource = dataset.Tables [0] .DefaultView;
MessageBox.Show（文件名+已成功导入。\\\
请在发送到数据库之前查看所有数据。，成功！，MessageBoxButtons.OK）;
}
else
{
this.Close（）;
}
}
}
public string filename {get;组; }
private void openFileDialog1_FileOk（object sender，CancelEventArgs e）
{
}
private void Import_Load（object发件人，EventArgs e）
{
}
private void button4_Click（object sender，EventArgs e）
{
Application.Exit ）;
}
private void button2_Click（object sender，EventArgs e）
{
this.Close（）;
}
private void button3_Click（object sender，EventArgs e）
//删除分号，并在行
{$ b之后添加括号$ b //创建连接字符串
string connString =Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C：\\Search\\\Database.accdb;
//创建数据库查询
string query =SELECT * FROM script_Orders;
//创建一个OleDbDataAdapter来执行查询
OleDbDataAdapter dAdapter = new OleDbDataAdapter（query，connString）;
//创建一个命令构建器
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder（dAdapter）;
//创建一个DataTable来保存查询结果
DataTable dTable = new DataTable（）;
//填写DataTable
dAdapter.Fill（dTable）;
// DataGridView
DataGridView dataGridView1 = new DataGridView（）;
// BindingSource同步DataTable和DataGridView
BindingSource bSource = new BindingSource（）;
//设置BindingSource DataSource
bSource.DataSource = dTable;
//设置DataGridView DataSource
dataGridView1.DataSource = bSource;
//将更改返回数据库的更新函数。
dAdapter.Update（dTable）;
}
}
}
示例超过欢迎！
解决方案
假设你想做的就是拿CSV的内容并将其插入到表格中你可以通过你的数据集循环，并调用一个insert命令（当然是一个参数化的查询）
var AccessCnn = string.Format （Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {0}; Persist Security Info = False;，@C：\YOURDBNAME.accdb）;
使用（OleDbConnection accessCnn = new OleDbConnection（AccessCnn））
{
//创建命令
var accessCmd = new OleDbCommand （@INSERT INTO script_Orders
（处方，[客户名称]，药物，数量，[填写日期]）
VALUES（？，？，？，？，？），accessCnn）
foreach（var row in dataset.Tables [medTable]。Rows）
{
accessCmd.Parameters.Clear（）;
accessCmd.Parameters.AddWithValue（？，row [Prescription]）;
accessCmd.Parameters.AddWithValue（？，row [Customer Name]）;
accessCmd.Parameters.AddWithValue（？，row [Medication]）;
accessCmd.Parameters.AddWithValue（？，row [Quantity]）;
accessCmd.Parameters.AddWithValue（？，row [Date Filled]）;
ccessCmd.ExecuteNonQuery（）;
}
}
您将需要将您的DataSet移动到类级别的变量
public partial class Import：Form
{
DataSet数据集
然后在button1_Click中稍后分配而不是声明和分配它
string tablename =medTable;
dataset = new DataSet（）;
I have a C# Windows Forms project that interacts with an Access database (accdb). I have one form that reads the database just fine, and displays it into a DataGridView. I have another form that submits textbox information into the database just as fine.
I have another form (see image below) that allows the user to click a button (button 1) to open a CSV file, using "openFileDialog", and display the selected file's contents in the dataGridView on the form (example shown below).
MY GOAL: I want a button (button 3), on that same form, to submit the dataGridView's displayed results into the previously mentioned Access database.
It seems like I have all of the components I need. It feels like I'm not too far off, but there still seems to be something wrong and/or missing in my code. I've been trying to accomplish this for weeks. PLEASE HELP!!!
Here is both, a screen shot of the form, and the full code for the form. ALL help is GREATLY appreciated!
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Globalization;
using System.Configuration;
using System.Data.OleDb;
namespace csvToGrid
{
public partial class Import : Form
{
public Import()
{
InitializeComponent();
}
public void button1_Click(object sender, EventArgs e)
{
string delimiter = ",";
string tablename = "medTable";
DataSet dataset = new DataSet();
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.Filter = "CSV Files (*.csv)|*.csv|All Files (*.*)|*.*";
openFileDialog1.FilterIndex = 1;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
if (MessageBox.Show("Are you sure you want to import the data from \n " + openFileDialog1.FileName + "?", "Are you sure?", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
filename = openFileDialog1.FileName;
StreamReader sr = new StreamReader(filename);
string csv = File.ReadAllText(openFileDialog1.FileName);
dataset.Tables.Add(tablename);
dataset.Tables[tablename].Columns.Add("Prescription");
dataset.Tables[tablename].Columns.Add("Customer Name");
dataset.Tables[tablename].Columns.Add("Medication");
dataset.Tables[tablename].Columns.Add("Quantity");
dataset.Tables[tablename].Columns.Add("Date Filled");
string allData = sr.ReadToEnd();
string[] rows = allData.Split("\r".ToCharArray());
foreach (string r in rows)
{
string[] items = r.Split(delimiter.ToCharArray());
dataset.Tables[tablename].Rows.Add(items);
}
this.dataGridView1.DataSource = dataset.Tables[0].DefaultView;
MessageBox.Show(filename + " was successfully imported. \n Please review all data before sending it to the database.", "Success!", MessageBoxButtons.OK);
}
else
{
this.Close();
}
}
}
public string filename { get; set; }
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
}
private void Import_Load(object sender, EventArgs e)
{
}
private void button4_Click(object sender, EventArgs e)
{
Application.Exit();
}
private void button2_Click(object sender, EventArgs e)
{
this.Close();
}
private void button3_Click(object sender, EventArgs e)
//remove the semicolon, and add brackets below after line
{
//create the connection string
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Search\\Database.accdb";
//create the database query
string query = "SELECT * FROM script_Orders";
//create an OleDbDataAdapter to execute the query
OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
//create a command builder
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
//the DataGridView
DataGridView dataGridView1 = new DataGridView();
//BindingSource to sync DataTable and DataGridView
BindingSource bSource = new BindingSource();
//set the BindingSource DataSource
bSource.DataSource = dTable;
//set the DataGridView DataSource
dataGridView1.DataSource = bSource;
// An update function to get the changes back into the database.
dAdapter.Update(dTable);
}
}
}
Examples are more than welcome!
解决方案
Assuming all you want to do is take the contents of the CSV and insert them into your table you can just loop though your dataset and call an insert command (with a parameterized query of course)
var AccessCnn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;", @"C:\YOURDBNAME.accdb");
using (OleDbConnection accessCnn = new OleDbConnection(AccessCnn))
{
//Create The Command
var accessCmd = new OleDbCommand(@"INSERT INTO script_Orders
(Prescription, [Customer Name], Medication, Quantity, [Date Filled])
VALUES (?,?,?,?,?)", accessCnn);
foreach(var row in dataset.Tables["medTable"].Rows)
{
accessCmd.Parameters.Clear();
accessCmd.Parameters.AddWithValue("?", row["Prescription"]);
accessCmd.Parameters.AddWithValue("?", row["Customer Name"]);
accessCmd.Parameters.AddWithValue("?", row["Medication"]);
accessCmd.Parameters.AddWithValue("?", row["Quantity"]);
accessCmd.Parameters.AddWithValue("?", row["Date Filled"]);
ccessCmd.ExecuteNonQuery();
}
}
You will need to move your DataSet to be a class level variable
public partial class Import : Form
{
DataSet dataset;
and then later in button1_Click assign it instead of declaring and assigning it
string tablename = "medTable";
dataset = new DataSet();
这篇关于使用C＃使用CSV文件填充DataGridView，并使用结果更新Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
