在C＃中从DataGridView更新SQL数据库 [英] Updating SQL Database from DataGridView in C#
问题描述
这里有几个教程，但我假设我必须实施一些错误，因为我从组合教程中遵循的代码不能正常工作。
这些是教程： https://youtu.be/_i4mYXSaD4w ， https://youtu.be/_sB0A6FIhUM
我正在尝试创建一个DataGridView显示一些基本数据，只有4列信息。我希望用户能够添加，更新和删除行信息。我已经在SQL数据库中手动创建了一行信息，以避免在程序加载时出现catch。
我有1行SQL数据库信息加载很好，但是当我编辑信息，并单击我的更新按钮，似乎在SQL方面工作，即使程序端工作。这是我的意思是我有一个消息框，确认它已经更新，但是当我关闭应用程序并再次运行它，它加载旧的数据，当我仔细检查数据库，它还没有更新。如果有人可以帮我调整，所以当我添加一行信息到DataGridView或编辑行，并且SQL文件实际上收到更改/更新，我会很感激。这是我的代码：
public partial class Form1：Form
{
SqlConnection con;
SqlDataAdapter sda;
DataTable dt;
SqlCommandBuilder scb;
private int rowIndex = 0;
public Form1（）
{
InitializeComponent（）;
}
private void Form1_Load（object sender，EventArgs e）
{
try
{
con = new SqlConnection （Properties.Settings.Default.SchoolConnectionString）;
con.Open（）;
sda = new SqlDataAdapter（SELECT * FROM School，con）;
dt = new DataTable（）;
sda.Fill（dt）;
dataGridView1.DataSource = dt;
}
catch（Exception ex）
{
MessageBox.Show（Error\\\
+ ex.Message，Error，MessageBoxButtons.OK，MessageBoxIcon.Error） ;
}
}
private void UpButton_Click（object sender，EventArgs e）
{
try
{
scb = new sqlCommandBuilder（sda）;
sda.Update（dt）;
MessageBox.Show（信息更新，更新，MessageBoxButtons.OK，MessageBoxIcon.Information）;
}
catch（Exception ex）
{
MessageBox.Show（ex.Message，Error，MessageBoxButtons.OK，MessageBoxIcon.Error）;
}
}
//按钮刷新数据而不需要关闭应用
private void RefButton_Click（object sender，EventArgs e）
{
尝试
{
con = new SqlConnection（Properties.Settings.Default.SchoolConnectionString）;
con.Open（）;
sda = new SqlDataAdapter（SELECT * FROM School，con）;
dt = new DataTable（）;
sda.Fill（dt）;
dataGridView1.DataSource = dt;
}
catch（Exception ex）
{
MessageBox.Show（Error\\\
+ ex.Message，Error，MessageBoxButtons.OK，MessageBoxIcon.Error） ;
}
}
private void CloseButton_Click（object sender，EventArgs e）
{
Application.Exit（）;
}
// BROKEN - 应该提出一个菜单项来删除行
private void dataGridView1_CellMouseUp（object sender，DataGridViewCellMouseEventArgs e）
{
if（e.Button == MouseButtons.Right）
{
this.dataGridView1.Rows [e.RowIndex] .Selected = true;
this.rowIndex = e.RowIndex;
this.dataGridView1.CurrentCell = this.dataGridView1.Rows [e.RowIndex] .Cells [1];
this.contextMenuStrip1.Show（this.dataGridView1，e.Location）;
contextMenuStrip1.Show（Cursor.Position）;
}
}
private void contextMenuStrip1_Click（object sender，CancelEventArgs e）
{
if（！this.dataGridView1.Rows [ this.rowIndex] .IsNewRow）
{
this.dataGridView1.Rows.RemoveAt（this.rowIndex）;
}
}
}
现在，我不太清楚的是，我从一个教程中改变了一件事，就是他们使用了一个数据集而不是一个数据表，但据我所知，除了能够容纳多个表结构的数据集之外拉扯数据或更新数据没有任何差异。因为我没有使用数据块和不同的表，所以我觉得DataTable足够我使用这个程序。
此外，在我的数据库我有一个主键是一个整数，和4列文本。我宁愿避免愚蠢的整数列，如行号，只是使我的第一个文本列成为主键，但是当我尝试这样做并更新数据库时，它会抛出错误。如果有办法做到这一点，我会感谢解释如何，或者如果有办法隐藏第一行号列拉取主键的整数值，并自动增加和/或调整此值根据编辑，更改和新行添加，将是巨大的。只是为了清除，如果我添加行2,3和4，我希望这些值是自动生成的，只是使该列不可见。就这样，我必须在那里手动输入整数。
感谢任何帮助和建议。
更新＃1：
好的，所以采取一些建议，我尝试使用DataSet的格式如下：
private void Form1_Load （object sender，EventArgs e）
{
try
{
con = new SqlConnection（Properties.Settings.Default.SchoolConnectionString）;
con.Open（）;
sda = new SqlDataAdapter（SELECT * FROM School，con）;
ds = new DataSet（）;
sda.Fill（ds，e）;
dataGridView1.DataSource = ds.Tables [e];
}
catch（Exception ex）
{
MessageBox.Show（Error\\\
+ ex.Message，Error，MessageBoxButtons.OK，MessageBoxIcon.Error） ;
}
}
private void UpButton_Click（object sender，EventArgs e）
{
try
{
scb = new SqlCommandBuilder（sda）;
sda.Update（ds，e）;
MessageBox.Show（信息更新，更新，MessageBoxButtons.OK，MessageBoxIcon.Information）;
}
catch（Exception ex）
{
MessageBox.Show（ex.Message，Error，MessageBoxButtons.OK，MessageBoxIcon.Error）;
}
}
我已经尝试以给定的格式再次使用DataTable在使用以下格式提供的第一个答案中，但有两种方式：1没有新的SqlCommandBuilder实例，一个与：
WITH：
private void Form1_Load（object sender，EventArgs e）
{
try
{
con = new SqlConnection（Properties.Settings.Default.SchoolConnectionString）;
con.Open（）;
sda = new SqlDataAdapter（SELECT * FROM School，con）;
dt = new DataTable（）;
sda.Fill（dt）;
dataGridView1.DataSource = dt;
}
catch（Exception ex）
{
MessageBox.Show（Error\\\
+ ex.Message，Error，MessageBoxButtons.OK，MessageBoxIcon.Error） ;
}
}
private void UpButton_Click（object sender，EventArgs e）
{
try
{
scb = new SqlCommandBuilder（sda）;
newDT = dt.GetChanges（）;
if（newDT！= null）
{
sda.Update（newDT）;
}
MessageBox.Show（信息更新，更新，MessageBoxButtons.OK，MessageBoxIcon.Information）;
}
catch（Exception ex）
{
MessageBox.Show（ex.Message，Error，MessageBoxButtons.OK，MessageBoxIcon.Error）;
}
}
WITHOUT：此版本产生一个错误，说：更新需要一个有效的UpdateCommand，在传递带修改行的DataRow集合时。
private void Form1_Load（object sender，EventArgs e）
{
try
{
con = new SqlConnection（Properties.Settings.Default.SchoolConnectionString）;
con.Open（）;
sda = new SqlDataAdapter（SELECT * FROM School，con）;
dt = new DataTable（）;
sda.Fill（dt）;
dataGridView1.DataSource = dt;
}
catch（Exception ex）
{
MessageBox.Show（Error\\\
+ ex.Message，Error，MessageBoxButtons.OK，MessageBoxIcon.Error） ;
}
}
private void UpButton_Click（object sender，EventArgs e）
{
try
{
newDT = dt.GetChanges（）;
if（newDT！= null）
{
sda.Update（newDT）;
}
MessageBox.Show（信息更新，更新，MessageBoxButtons.OK，MessageBoxIcon.Information）;
}
catch（Exception ex）
{
MessageBox.Show（ex.Message，Error，MessageBoxButtons.OK，MessageBoxIcon.Error）;
}
}
Sooooo，我被困了。除了没有SqlCommandBuilder之外的所有人都触发更新的消息框，但是没有一个实际上将更改保存到sql数据库。
那么，由于在这个网站上更多的谷歌搜索和梳理其他问题，我发现有人回答了一个类似的问题，让我意识到我实际上错过了一些简单的事情。所以在其他人遇到这个问题的时候，我以为我会发布我造成这个问题的可能类似的错误。
那么，什么我没有意识到，Visual Studio是在\bin\debug文件夹中制作我的数据库的临时版本。 （如果有办法关闭该功能，我很乐意听到它，因为我认为这很愚蠢）无论什么原因，如果您在Sever Explorer中打开查找.mdf文件，手动添加或删除该数据库中的信息在运行时反映在程序中，因为它查找此文件，复制该文件，然后在运行应用程序以进行测试时从那里工作。令人失望的事实是，（再次，除非我错过了哪里不要发生这种情况），它不会将您对temp版本的这些更改横向复制到您首先创建的主要.mdf文件。 p>
所以，这就是为什么我看不到任何改变，因为我正在看主要的数据库文件，其实际上是无法工作的。一旦我发现如何从Visual Studio中找到临时版本，并且我查询了该文件数据库，我实际上看到了这些更改。为了清楚起见，我原来尝试的多个版本实际上是工作。所以，我之前提到的所有解决方案除了我提到的那个之外，由于缺少SqlCommandBuilder调用，导致了一个错误，实际上更新了临时数据库。
作为一个备注，来自Visual Studio，C＃，SQL新手上的视角，我对SQL数据库和DataGridView的大量教程和信息感到惊讶，这在教程的过程中没有注明。即使是我观看的Youtube视频也没有明确地表明，当他们证明更新正在发生时，他们并没有明确地检查临时数据库，而不是主要的数据库。
There's a few tutorials out there on this, but I'm assuming I must have implemented something wrong, because the code I followed from combined tutorials is not working as it should.
These are the tutorials: https://youtu.be/_i4mYXSaD4w , https://youtu.be/_sB0A6FIhUM
I'm trying to create a DataGridView that displays some basic data, just 4 columns of information. I want the user to be able to add, update and delete rows of information. I've manually created 1 row of information in the SQL database just to avoid in 'catches' when the program is loaded.
I've got the 1 line of my SQL Database information loading just fine, but when I edit the information, and click my update button it doesn't seem to work on the SQL side, even though the program side works. By this I mean, I have a messagebox that confirms it's been updated, but when I close the App and the run it again, it loads the old data, and when I double check the database, it hasn't been updated. If someone could help me adjust this so when I add rows of information to the DataGridView or edit the rows, and have the SQL file actually receive the changes/updates, I'd appreciate it. Here is my code:
public partial class Form1 : Form
{
SqlConnection con;
SqlDataAdapter sda;
DataTable dt;
SqlCommandBuilder scb;
private int rowIndex = 0;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
con.Open();
sda = new SqlDataAdapter("SELECT * FROM School", con);
dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void UpButton_Click(object sender, EventArgs e)
{
try
{
scb = new SqlCommandBuilder(sda);
sda.Update(dt);
MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//button to refresh the data without need to close the app
private void RefButton_Click(object sender, EventArgs e)
{
try
{
con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
con.Open();
sda = new SqlDataAdapter("SELECT * FROM School", con);
dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void CloseButton_Click(object sender, EventArgs e)
{
Application.Exit();
}
//BROKEN - supposed to bring up a menu item to delete the row
private void dataGridView1_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
{
if (e.Button == MouseButtons.Right)
{
this.dataGridView1.Rows[e.RowIndex].Selected = true;
this.rowIndex = e.RowIndex;
this.dataGridView1.CurrentCell = this.dataGridView1.Rows[e.RowIndex].Cells[1];
this.contextMenuStrip1.Show(this.dataGridView1, e.Location);
contextMenuStrip1.Show(Cursor.Position);
}
}
private void contextMenuStrip1_Click(object sender, CancelEventArgs e)
{
if (!this.dataGridView1.Rows[this.rowIndex].IsNewRow)
{
this.dataGridView1.Rows.RemoveAt(this.rowIndex);
}
}
}
Now, what I'm not sure about is 1 thing I changed from one of the tutorials, was that they used a dataset rather than a datatable, but as I understand it, other than a dataset being able to hold multiple table structures, there's no differences in pulling the data or updating it. For the fact that I didn't have mounds of data and different tables to use, I felt DataTable was sufficient for my use of this program.
Additionally, in my database, I have a primary key that's an integer, and 4 columns of text. I'd prefer to avoid the stupid column of integers like line numbers and just make my first text column the primary key, but when I try to do this and update the database, it throws errors. If there's a way to do this, I'd appreciate the explanation of how, or if there's a way to hide the first line number column the pulls the integer value for the primary key, and have it automatically increment and/or adjust this value according to editing, changes and new rows being added that'd be great. Just to clear up, if I add rows 2, 3 and 4, I want these values to be autogenerated and just make that column not visible. As it stands, I have to manually type the integer in there.
Thanks for any help and advice.
Update #1:
Okay, so taking some recommendations, I have tried using DataSet in the following format:
private void Form1_Load(object sender, EventArgs e)
{
try
{
con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
con.Open();
sda = new SqlDataAdapter("SELECT * FROM School", con);
ds = new DataSet();
sda.Fill(ds, "e");
dataGridView1.DataSource = ds.Tables["e"];
}
catch (Exception ex)
{
MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void UpButton_Click(object sender, EventArgs e)
{
try
{
scb = new SqlCommandBuilder(sda);
sda.Update(ds, "e");
MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
I've tried using DataTable again in the format given in the first answer provided using the following format but in 2 ways: 1 without a new instance of the SqlCommandBuilder, and one with: WITH:
private void Form1_Load(object sender, EventArgs e)
{
try
{
con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
con.Open();
sda = new SqlDataAdapter("SELECT * FROM School", con);
dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void UpButton_Click(object sender, EventArgs e)
{
try
{
scb = new SqlCommandBuilder(sda);
newDT = dt.GetChanges();
if (newDT != null)
{
sda.Update(newDT);
}
MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
WITHOUT: This version produces an error that says: "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
private void Form1_Load(object sender, EventArgs e)
{
try
{
con = new SqlConnection(Properties.Settings.Default.SchoolConnectionString);
con.Open();
sda = new SqlDataAdapter("SELECT * FROM School", con);
dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void UpButton_Click(object sender, EventArgs e)
{
try
{
newDT = dt.GetChanges();
if (newDT != null)
{
sda.Update(newDT);
}
MessageBox.Show("Information Updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
Sooooo, I'm stumped. All except the one without SqlCommandBuilder trigger the Updated Message Box, but none of them actually save the changes to the sql database.
Well, thanks to more Googling and combing through other questions here on this site, I found someone answered a similar question that made me realize I was in fact missing something simple. So in the event someone else runs into this issue, I thought I'd post the highly possible similar mistake I was making that was resulting in this issue.
So, what I didn't realize, was that Visual Studio was making a temp version of my database in the \bin\debug folder. (If there's a way to turn that functionality off, I'd love to hear about it, because I think that's rather stupid) For whatever reason, if you open up in the Sever Explorer to find your .mdf file, manually adding or deleting information in that database is reflected in the program when run, because it looks for this file, makes a copy of it, and then works from there while you're running your application for testing purposes. The disappointing fact, is that (again, unless I missed where to keep this from happening) it doesn't transversely copy these changes you make to the temp version, over to the primary .mdf file you created in the first place.
So, this is why I saw no changes, because I was looking at the primary database file where it was in fact not working from. Once I found out how to locate the temp version from within Visual Studio, and I queried that file database, I did in fact see the changes. For clarity, multiple versions of what I tried originally were in fact working. So, all of the solutions I tried above aside from the one where I mentioned it produced an error due to a lack of the SqlCommandBuilder call, in fact do update the temporary database.
As a side note, coming from a Visual Studio, C#, SQL newbie perspective, I'm surprised with the extensive tutorials and information on SQL Databases and DataGridView, that this isn't noted in the process of tutorials. Even Youtube videos I watch didn't make this explicitly clear that when they "proved" the updates were happening, they didn't make it obvious they were checking the temp database, not the primary one.
这篇关于在C＃中从DataGridView更新SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
