使用数据库在 C# 中插入、删除和更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15578758/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
insert, delete and update in C# using database?
提问by Mohan
This program i wrote for insert,update
我为插入、更新而编写的这个程序
using dataset from C# database name is info can anyone check this code tharouly please help e what should i write over the syntax.
使用来自 C# 数据库名称的数据集是信息任何人都可以检查此代码 tharouly 请帮助我应该写什么语法。
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.Data.SqlClient;
namespace Insert_update_delete
{
public partial class Form1 : Form
{
SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True");
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
cmd.Connection = cn;
Loadlist();
}
private void button1_Click(object sender, EventArgs e)
{
if (txtId.Text != "" & txtName.Text != "")
{
cn.Open();
cmd.CommandText = "Insert into info (Id, Name) Values('"+txtId.Text+"' , '"+txtName.Text+"')";
cmd.ExecuteNonQuery();(it is showing error here )
cmd.Clone();
MessageBox.Show("Record Inserted!");
cn.Close();
txtId.Text = "";
txtName.Text = "";
Loadlist();
}
}
private void Loadlist()
{
listBox1.Items.Clear();
listBox2.Items.Clear();
cn.Open();
cmd.CommandText = "Select * From info";
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
listBox1.Items.Add(dr[0].ToString());
listBox2.Items.Add(dr[1].ToString());
}
}
cn.Close();
}
private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
{
ListBox l = sender as ListBox;
if(l.SelectedIndex != -1)
{
listBox1.SelectedIndex = l.SelectedIndex;
listBox2.SelectedIndex = l.SelectedIndex;
txtId.Text = listBox1.SelectedItem.ToString();
txtName.Text = listBox2.SelectedItem.ToString();
}
}
private void button2_Click(object sender, EventArgs e)
{
if (txtId.Text != "" & txtName.Text != "")
{
cn.Open();
cmd.CommandText = "Delete from info where id*'"+txtId.Text+"' and name*'"+txtName.Text+"'";
cmd.ExecuteNonQuery();
cn.Close();
MessageBox.Show("Record Deleted");
Loadlist();
txtId.Text = "";
txtName.Text = "";
}
}
private void button3_Click(object sender, EventArgs e)
{
if (txtId.Text != "" & txtName.Text != "" & listBox1.SelectedIndex != -1)
{
cn.Open();
cmd.CommandText = "Update info set id='"+txtId.Text+"', name='"+txtName.Text+"' where id='"+listBox1.SelectedItem.ToString()+"' and name= '"+listBox2.SelectedItem.ToString()+"'";
cmd.ExecuteNonQuery();
cn.Close();
MessageBox.Show("Record Updated");
Loadlist();
txtId.Text = "";
txtName.Text = "";
}
}
}
}
no doubt on this one works fine please check.
毫无疑问，这个工作正常，请检查。
回答by Chris Doggett
I'd guess it's that you're using "Insert in to info" when you should be using "INSERT INTO info".
我猜你应该使用“INSERT INTO info”时使用“Insert in to info”。
回答by Sachin
Remove space from insert statement. You have put space between in towhich should be intoso change your command Text like this.
从插入语句中删除空格。您在in to它们之间放置了空间，因此应该像into这样更改您的命令文本。
cmd.CommandText = "Insert into info (Id, Name) Values('"+txtId.Text+"' , '"+txtName.Text+"')";
one more this what is *in your delete statement. I think it will also cause a problem you when you pass over the current error
还有一个是*你的删除语句中的内容。我认为当您传递当前错误时，它也会给您带来问题
cmd.CommandText = "Delete from info where id*'"+txtId.Text+"' and name*'"+txtName.Text+"'";
So it should
所以应该
cmd.CommandText = "Delete from info where id=" + txtId.Text+"' and name='"+txtName.Text+"'";
Now Your Update Command : Here you have missed ,between multiple columns. Your update command should be like this
现在您的更新命令：在这里您错过,了多个列。你的更新命令应该是这样的
cmd.CommandText = "Update info set id='"+txtId.Text+"', name='"+txtName.Text+"' where id='"+listBox1.SelectedItem.ToString()+"' and name= '"+listBox2.SelectedItem.ToString()+"'";
回答by Oded
This SQL is not valid SQL:
此 SQL 不是有效的 SQL：
"Delete from info where id'"+txtId.Text+"' and name'"+txtName.Text+"'";
"Insert in to info (Id, Name) Values('"+txtId.Text+"' , '"+txtName.Text+"')";
"Update info set id='"+txtId.Text+"'name='"+txtName.Text+"' where id='"+listBox1.SelectedItem.ToString()+"' and name= '"+listBox2.SelectedItem.ToString()+"'";
It should:
这应该：
- Contain
=signs when making comparisons in the DELETE, instead of the*. - Use
intoand notin toin the INSERT. - Have commas between fields in the UPDATE.
- Should not be concatenating the SQL, but use parameterized queries.
=在 DELETE 中进行比较时包含符号，而不是*.- 使用
into而不是in to在 INSERT 中。 - 在 UPDATE 中的字段之间使用逗号。
- 不应连接 SQL，而是使用参数化查询。
The only valid SQL you have there is the SELECT. You should try running your queries in SQL Server Management Studio before you write them in the program.
您拥有的唯一有效 SQL 是 SELECT。在将查询写入程序之前，您应该尝试在 SQL Server Management Studio 中运行查询。
回答by Roy Ashbrook
Looks like your insert command has an typo in it:
看起来您的插入命令中有一个错字：
cmd.CommandText = "Insert in to info (Id, Name) Values('"+txtId.Text+"' , '"+txtName.Text+"')"
should be something like:
应该是这样的：
cmd.CommandText = "Insert into info (Id, Name) Values('"+txtId.Text+"' , '"+txtName.Text+"')"
回答by Jon Raynor
BEST PRACTICE
最佳实践
Don't use Select *. Here you are only looking for 2 columns of data. So, only select 2 colunms. What if the table had 20 columns? That's 18 extra column of data that were retrieved for nothing. Also, refer to column names instead of indexes. If someone in the future changes the schema of that table and inserts of column between columns 0 and 1, then your results may not be what is expected.
不要使用 Select *。在这里，您只需要查找 2 列数据。所以，只选择 2 列。如果表有 20 列怎么办？那是 18 个额外的数据列，这些数据是无偿检索的。另外，请参考列名而不是索引。如果将来有人更改了该表的架构并在 0 和 1 列之间插入了列，那么您的结果可能不是预期的。
Select col1, col2 from info
Your code:
您的代码：
cmd.CommandText = "Select * From info";
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
listBox1.Items.Add(dr[0].ToString());
listBox2.Items.Add(dr[1].ToString());
}
}
