在 C# 中执行 SQL 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/783294/
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
Executing an SQL statement in C#?
提问by Ash
Hey guys i want to execute my SQL statement but im having synatx trouble, can someone help me understand what i doin wrong please?
嘿伙计们，我想执行我的 SQL 语句，但我遇到了 Synatx 问题，有人可以帮我理解我做错了什么吗？
Thanks, Ash.
谢谢，阿什。
public void AddToDatabase(string[] WordArray, int Good, int Bad, int Remove)
{
for (int WordCount = 0; WordCount < WordArray.Length; WordCount++)
{
string sSQL = "INSERT INTO WordDef (Word, Good, Bad, Remove) VALUES (" + WordArray[WordCount] + ", " + Good + ", " + Bad + ", " + Remove + ")";
Debug.Print(sSQL);
//Private m_recordset As ADODB.Recordset
//Private m_connection As ADODB.Connection
ADODB.Recordset RS;
ADODB.Connection CN ;
CN = new ADODB.Connection();
RS = new ADODB.Recordset();
CN.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=doom_calc_dict.mdb;jet OLEDB:database";
CN.Open(CN.ConnectionString,"","",0);
object dummy = Type.Missing;
CN.Execute(sSQL,out dummy,0);
RS.Close();
CN.Close();
//string sSQL = "SELECT Word FROM WordDef WHERE Word='" + WordArray[WordCount] + "'";
DatabaseTools.LoadDataFromDatabase(sSQL);
//DatabaseTools.LoadDataFromDatabase(sSQL);
}
}
采纳答案by Joel Coehoorn
The most important thing you need to fix is to use query parameters rather than building the string dynamically. This will improve performance, maintenance, and security.
您需要修复的最重要的事情是使用查询参数而不是动态构建字符串。这将提高性能、维护和安全性。
Additionally, you want to use the newer strongly-typed ADO.Net objects. Make sure to add using directives for System.Data.OleDb.
此外，您希望使用较新的强类型 ADO.Net 对象。确保为System.Data.OleDb.
Notice the usingstatements in this code. They will make sureyour connection is closed when you finish with it. This is important because database connections are a limited and unmanaged resource.
注意using这段代码中的语句。当您完成连接时，他们将确保您的连接已关闭。这很重要，因为数据库连接是一种有限且不受管理的资源。
Finally, you're not really using an array in your code. All you really care about is the ability to iterate over a collection of words, and so you want to accept an IEnumerable<string>instead of an array. Don't worry: this function will accept an array as an argument if that's what you need to pass it.
最后，您并没有真正在代码中使用数组。您真正关心的是迭代一组单词的能力，因此您希望接受一个IEnumerable<string>而不是数组。别担心：这个函数会接受一个数组作为参数，如果你需要传递它的话。
public void AddToDatabase(IEnumerable<string> Words, int Good, int Bad, int Remove)
{
string sql = "INSERT INTO WordDef (Word, Good, Bad, Remove) VALUES (@Word, @Good, @Bad, @Remove)";
using (OleDbConnection cn = new OleDbConnection("connection string here") )
using (OleDbCommand cmd = new OleDbCommand(sql, cn))
{
cmd.Parameters.Add("@Word", OleDbType.VarChar);
cmd.Parameters.Add("@Good", OleDbType.Integer).Value = Good;
cmd.Parameters.Add("@Bad", OleDbType.Integer).Value = Bad;
cmd.Parameters.Add("@Remove", OleDbType.Integer.Value = Remove;
cn.Open();
foreach (string word in Words)
{
cmd.Parameters[0].Value = word;
cmd.ExecuteNonQuery();
}
}
}
One more thing: when using query parameters in OleDb it's important to make sure you add them in order.
还有一件事：在 OleDb 中使用查询参数时，确保按顺序添加它们很重要。
Update:Fixed to work on VS 2005 / .Net 2.0 (had relied on VS 2008 features).
更新：已修复可在 VS 2005 / .Net 2.0 上运行（依赖于 VS 2008 功能）。
回答by Elie
Try this (and you should try running the SQL from outside your application):
试试这个（你应该尝试从你的应用程序外部运行 SQL）：
string sSQL = "INSERT INTO WordDef (Word, Good, Bad, Remove) VALUES ('" + WordArray[WordCount] + "', " + Good + ", " + Bad + ", " + Remove + ");";
回答by Jeremy
You need to add single quotes around the first argument in the SQL Statement.
您需要在 SQL 语句中的第一个参数周围添加单引号。
string sSQL = "INSERT INTO WordDef (Word, Good, Bad, Remove) VALUES ('" + WordArray[WordCount] + "', " + Good + ", " + Bad + ", " + Remove + ")";
Character and Date fields require values to be surrounded with 'single quotes'
字符和日期字段需要用“单引号”括起来的值
回答by JP Alioto
It will appear at first that I am not being helpful. But in truth, I am trying to help you, so please take it that way. You need to read thisand thisSTAT! Once you've done that, here are some good, clean ADO.NET examples.
乍一看，我似乎没有帮助。但事实上，我正在努力帮助你，所以请就这样吧。你需要阅读这个和这个STAT！完成后，这里有一些好的、干净的ADO.NET 示例。
回答by Joel Coehoorn
No you need it like this:
不，你需要这样：
string query = "INSERT INTO Table_PersonInfo
(PersonID,Surname
,[Family Name]
,AddsOnName
,Street,Number
,PostalCode
,[City of Birth]
,[Year of Birth]
,[Phone Number])
VALUES
('"+@personID+"'
, '"+ @surname + "'
, '"+@familyname+"'
, '"+@nameExtension+"'
, '"+@street+"'
, '"+@houseNumber+"'
, '"+ @postalCode+"'
, '"+@yearofbirth+"'
, '"+@placeofbirth+"'
, '"+@phoneNumber+"')";
