执行在C＃中的SQL语句？ [英] Executing an SQL statement in C#?

问题描述

嘿家伙，我要执行我的SQL语句，但即时通讯有synatx麻烦，有人可以帮助我理解我干什么错吗？

谢谢，灰分

 公共无效AddToDatabase（字符串[] WordArray，诠释好，诠释坏，诠释删除）
 {
 
。对于（INT WORDCOUNT = 0; WORDCOUNT< WordArray.Length; WORDCOUNT ++）
 {
串sSQL =INSERT INTO WordDef（文字，好，坏，删除）VALUES（+ WordArray [ WORDCOUNT] +，+良好+，+为+，+删除+）; 
 
 Debug.Print（sSQL）; 
 
 //私人m_recordset作为ADODB.Recordset 
 //私人m_connection作为ADODB.Connection 
 ADODB.Recordset RS; 
 ADODB.Connection CN; 
 
 
 CN =新ADODB.Connection（）; 
 RS =新ADODB.Recordset（）; 
 
 CN.CursorLocation = ADODB.CursorLocationEnum.adUseClient; 
 
 CN.ConnectionString =供应商= Microsoft.Jet.OLEDB.4.0;数据源= doom_calc_dict.mdb;喷气OLEDB：数据库; 
 CN.Open（CN.ConnectionString，，，0）; 
 
对象哑= Type.Missing; 
 
 CN.Execute（sSQL，出虚，0）; 
 
 RS.Close（）; 
 CN.Close（）; 
 
 //字符串sSQL =SELECT字从WordDef WHERE字='+ WordArray [WORDCOUNT] +'; 
 DatabaseTools.LoadDataFromDatabase（sSQL）; 
 //DatabaseTools.LoadDataFromDatabase(sSQL）; 
 
} 
} 
  

解决方案

您需要解决的最重要的事情是使用查询参数而不是动态构建字符串。这将提高性能，维护和安全性。

此外，要使用较新的强类型的ADO.Net对象。确保使用指令来添加对 System.Data.OleDb 。

注意此代码使用语句。他们会做出的确定的，当你用它完成您的连接被关闭。因为数据库连接是有限的和非托管资源，这是很重要的。

最后，你没有真正使用在代码中的数组。所有你真正关心的是遍历一组单词的能力，所以要接受的IEnumerable<的不是数组，字符串过夜。不要担心：如果这是你需要将它传递了这个函数将接受一个数组作为参数

 公共无效AddToDatabase （IEnumerable的<串>换句话说，int好，诠释坏，诠释删除）
 {
字符串SQL =INSERT INTO WordDef（文字，好，坏，删除）VALUES（@Word，@Good，@坏，@Remove）;使用（OleDbConnection的CN =新的OleDbConnection（连接字符串这里））
使用
 
（OleDbCommand的CMD =新的OleDbCommand（SQL，CN））
 {
 CMD .Parameters.Add（@道，OleDbType.VarChar）; 
 cmd.Parameters.Add（@好，OleDbType.Integer）.value的=好; 
 cmd.Parameters.Add（@坏，OleDbType.Integer）.value的坏=; 
 cmd.Parameters.Add（@删除，OleDbType.Integer.Value =删除; 
 
 cn.Open（）; 
 
的foreach（字符串字字）
 {
 cmd.Parameters [0] .value的=字; 
 cmd.ExecuteNonQuery（）; 
} 
} 
} 
  

一件事：在OleDb的使用查询参数时，重要的是要确保你的顺序来添加。

更新：固定在VS 2005 / .NET 2.0（已在VS 2008的功能依赖）

工作。

Hey guys i want to execute my SQL statement but im having synatx trouble, can someone help me understand what i doin wrong please?

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);

    }
}

解决方案

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.

Notice the using statements in this code. They will make sure your connection is closed when you finish with it. This is important because database connections are a limited and unmanaged resource.

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.

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.

Update: Fixed to work on VS 2005 / .Net 2.0 (had relied on VS 2008 features).

这篇关于执行在C＃中的SQL语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！