C#列出数据库SQL的项目 [英] C# list items to database SQL

问题描述

您好，我有一个简单的问题，涉及将数据插入MS MySql数据库2012表.我拥有的表称为COMPLETED，并且具有3个字段.

Hello I have a simple question that regards inserting data into a MS MySql Database 2012 table. The table that I have is called COMPLETED and has 3 fields.

• student_ID(int，不允许为空)
• 已完成(布尔型，不允许为空)
• random_code(字符串，允许为空)

在C#中，我有一个列表，其中填充了唯一的随机代码.我希望将所有代码插入数据库，因此，如果我有20条记录，我希望将20个唯一代码插入random_code字段.因此，第一个记录获取第一个代码，第二个记录获取第二个代码，依此类推.我认为最好的方法是使用foreach，然后针对代码列表中的每个代码，将该代码插入数据库中的random_code字段中.问题是我不知道该怎么做.我有以下代码，给我VALUE错误:

In c# I have a list filled with unique random codes. I want all codes inserted into the database, so if I have 20 records I want 20 unique codes inserted into the random_code field. So the first records gets the first code, the seconds records gets the second code and so on. I think the best way to do this is using a foreach and, for each code in the list of codes insert that code into the random_code field in my database. The problem is I don't know how to do this. I have the following code that give's me an error at VALUE:

"VALUE"附近的语法不正确.

Incorrect syntax near 'VALUE'.

        foreach (string unRaCo in codes)
        {
            //insert database
            SqlCommand toDB = new SqlCommand("INSERT INTO COMPLETED (random_code) VALUE ( '"+ unRaCo +"' ) ", conn);
            SqlDataReader toDBR;
            toDBR = toDB.ExecuteReader();
        }

有人可以在这里给我一个方向吗?预先感谢.

Could anyone give me a dircetion here? Thanks in advance.

好吧，我完全改变了查询，因为我发现它还没有执行我想要的操作.我现在想更新我的记录，而不是插入记录.我使用以下代码做到了这一点:

Okay I totally changed my query as I figured out it did not yet do what I wanted it to do. I now want to update my records instead of inserting records. I did that with the following code:

        foreach (string unRaCo in codes)
        {
            //insert database
            SqlCommand naarDB = new SqlCommand("UPDATE VOLTOOID SET random_code = '"+ unRaCo +"'  ", connectie);
            SqlDataReader naarDBR;
            naarDBR = naarDB.ExecuteReader();
            naarDBR.Close();
        }

这次的问题是更新查询使用第一个代码更新所有记录，因此第一个记录具有例如代码12345，但是所有其他记录也具有该代码.我想将12345更新为记录1和54321(例如，数字2)，该怎么做?

The problem this time is that the update query updates ALL records with the first code, so the first record has the code 12345 for example but all other records also have that code. I want to update 12345 into record 1 and 54321 for example in number 2, how do I do that?

推荐答案

正确的是Values而不是Value，即使您只提供一列.

The correct is Values not Value, even if you only provide one column.

关于您的编辑.首先要当心SQL注入.您最好使用SQLParameter类.检查配置参数和参数数据类型以获取更多信息.

About your edit. First of all beware of SQL Injection. You better use SQLParameter class. Check Configuring Parameters and Parameter Data Types for further info.

如果要更新特定的ID，请使用where子句，例如(在普通SQL中):

If you want to update a specific id then use a where clause like (in plain SQL):

UPDATE VOLTOOID SET random_code = @NewValue WHERE random_code = @OldValue

现在，如果您只想在特定行中添加随机数，则必须使用一些更高级的SQL函数.同样，在普通SQL中，您将:

Now if you just want to add the random number in a specific row, then you would have to use some more advanced SQL functions. Again in plain SQL you would have:

;WITH MyCTE AS
(
    SELECT random_code,
           ROW_NUMBER() OVER(ORDER BY random_code) AS ROWSEQ -- This will give a unique row number to each row of your table
    FROM   VOLTOOID _code
)
UPDATE MyCTE 
SET    random_code = @NewValue 
WHERE  ROWSEQ = @YourRandomRow

由于上述查询是针对SQL脚本执行的，因此您需要定义所使用的变量.

As the above queries are for SQL script execution you will need to define the variable used.

这篇关于C#列出数据库SQL的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！