如何使用C＃窗体更新SQL中的数据而不影响其他字段？ [英] How do I update data in SQL using C# windows form without affecting other fields?
问题描述
您好，
我使用C＃Windows应用程序创建了一个Windows窗体，用于添加，更新，删除和显示SQL中的用户数据。
所有查询都运行正常但是当我更新特定用户数据时，我留空的字段在SQL中也变为空白。
我有3个文本框：
用户名
密码
名称
因此，如果我将值放在用户名和密码中并将名称字段留空，那么它将更新用户名和密码，但在SQL中将名称字段留空。
我希望保持不变的字段保持不变。
请帮忙。
我尝试过：
Hello,
I have created a Windows Form using C# Windows Application to add, update, delete and show user data from SQL.
All the queries are working perfectly but when I am updating a particular user data, the fields that I am leaving empty are becoming blank in SQL as well.
I have 3 TextBoxes:
Username
Password
Name
So if I put values in Username and Password and leave the Name field blank, then it is updating the Username and Password but making the Name field blank in the SQL.
I want the unchanged fields to remain as they were.
Please help.
What I have tried:
using (SqlConnection connection = new SqlConnection("Data Source=PRIMO-CHALICE;Initial Catalog=NewsClip;Integrated Security=SSPI"))
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection; // <== lacking
command.CommandType = CommandType.Text;
command.CommandText = "UPDATE NewsClip_Login SET Username = @Username, Password = @Password, Name = @Name WHERE Username='" + this.EmployeeUsernameAdd.Text + "';";
command.Parameters.AddWithValue("@Username", EmployeeUsernameAdd.Text);
command.Parameters.AddWithValue("@Password", EmployeePasswordAdd.Text);
command.Parameters.AddWithValue("@Name", EmployeeNameAdd.Text);
try
{
connection.Open();
int recordsAffected = command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
}
推荐答案
两个主要问题：
1）永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。
连接字符串时会导致问题，因为SQL会收到如下命令：
Two major problems with that:
1) Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'哪个SQL看作三个单独的命令：
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';完全有效的SELECT
A perfectly valid SELECT
DROP TABLE MyTable;完全有效的删除表格通讯和
A perfectly valid "delete the table" command
--'其他一切都是评论。
所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。
所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。你定期做备份，不是吗？
想想你在做什么！你将用户名作为参数传递，那么为什么你最后还要修改它并使你的代码也容易受到攻击？
2）永远不要存储密码明文 - 这是一个重大的安全风险。有关如何在此处执行此操作的信息：密码存储：如何做到这一点。 [ ^ ]
为了让您了解我们对此类事物的感受，请参阅此处： Code Crime 1 [ ^ ]
然后我们遇到一些小问题：为什么你设置用户名，当你知道它已经是相同的值？因为如果它不同，它就不会匹配任何行！
最后：为什么它改变名称字段？因为你告诉它......
如果你希望名称在空白时保持不变，你需要检查你的C＃代码，并使用不同的查询，或传递当前值代替。如果你告诉SQL将它设置为这个那么这正是它将要做的......
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.
So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
Think about what you are doing! You pass the username as a parameter, so why the heck are you tacking it on at the end and making your code vulnerable as well?
2) Never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^]
To give you an idea how we feel about that kind of thing, see here: Code Crime 1[^]
Then we come to minor problems: why are you setting the username, when you know it's the same value already? Because if it was different, it wouldn't match any rows!
And finally: why is it changing the name field? Because you told it to...
If you want the name to remain untouched when it is blank, you need to check in your C# code, and either use a different query, or pass the current value instead. If you tell SQL "set it to this" then that is exactly what it will do...
进一步对OG的回应......
我假设
Further to OG's response...
I assume that
this.EmployeeUsernameAdd.Text是您要替换的旧用户名，因此您应该使用（例如） @OldUsername 参数。
如果用户将字段留空，则不更改名称，请使用类似...
is the old username that you are replacing, so you should use (for example) an @OldUsername parameter.
To not change Name if the user has left the field blank, use something like ...
... Name = CASE WHEN @Name = '' THEN Name ELSE @Name END ...
类似于用户名和密码字段，如果它们为空白意味着无变化。
这样可以省去动态创建SQL或t o有几十种变体。
将SQL（带参数化）放入存储过程。这将有助于隔离职责，例如，如果表格更改名称/列重组，您只需更改存储过程，而无需编辑/编译/重新部署源代码。
最后：我希望我误读了你的SQL，而你实际上并没有真正以纯文本形式存储密码。我希望样本只是一个提取，并且任何密码都被保存为加密文本，实际上是通过发送加密（首选）或者您在数据库本身上使用SQL Server加密。
Similarly for the Username and Password fields in case they are blank meaning 'no change'.
This saves you having to either dynamically create SQL or to have dozens of variants.
Put the SQL (with parameterisations) into a Stored Procedure. This will help segregate responsibilities so, for example, if you tables change name / columns are restructured, you can just change the Stored Procedure without having to edit / compile / redeploy your source code.
Finally: I hope that I have misread your SQL and you are not actually really storing passwords in plain text. I hope that the sample is just an extract and that any passwords are being saved as encrypted text, either by actually being sent encrypte (preferred) or you are using SQL Server encryption on the database itself.
这篇关于如何使用C＃窗体更新SQL中的数据而不影响其他字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
