.NET Core中的原始SQL [英] Raw SQL in .NET Core

问题描述

我有这个问题：我需要从.NET Core应用程序执行原始SQL。所以我有这段代码

I have this problem: I need to execute raw SQL from my .NET Core app. So I have this code

var sqlConnection1 = new SqlConnection("Server=(localdb)\\mssqllocaldb;Database=MyDB;Trusted_Connection=True;MultipleActiveResultSets=true");
var cmd = new SqlCommand
{
    CommandText = "SELECT * FROM dbo.Candidates WHERE id = " + model.CandidateId,
    CommandType = CommandType.Text,
    Connection = sqlConnection1
};

sqlConnection1.Open();
var wantedRow = cmd.ExecuteReader();
sqlConnection1.Close();

我无法访问wantedRow中的数据...
（当我使用实体时框架，此查询有效，但是我不能使用实体框架）。

I can't access the data in wantedRow... (When I use Entity Framework this query works, but I can't use Entity Framework). Is it possible in .NET Core?

推荐答案

首先，您的代码是< href = https://en.wikipedia.org/wiki/SQL_injection rel = nofollow noreferrer> sql注入攻击。使用参数化查询而不是串联字符串。

First, your code is an open door for sql injection attacks. Use parameterized queries instead of concatenating strings.

第二，对实现IDisposable接口的所有内容使用 using 语句。在这种情况下-连接，命令和阅读器。

Second, use the using statement for everything that implements the IDisposable interface. In this case - connection, command and reader.

第三，吸引读者只是工作的一部分。您仍然需要使用 reader.Read（）并获取值。

Third, getting the reader is just a part of the job. You still need to use reader.Read() and get the values.

using(var sqlConnection1 = new SqlConnection("Server=(localdb)\\mssqllocaldb;Database=MyDB;Trusted_Connection=True;MultipleActiveResultSets=true"))
{ 
    using(var cmd = new SqlCommand()
    {
        CommandText = "SELECT * FROM dbo.Candidates WHERE id = @id",
        CommandType = CommandType.Text,
        Connection = sqlConnection1
    })
    {
        cmd.Parameters.Add("@id", SqlDbType.Int).Value = model.CandidateId
        sqlConnection1.Open();

        using(var reader = cmd.ExecuteReader())
        {
            if(reader.Read())
            {
                var id = reader[0];
                var whatEver = reader[1];
                // get the rest of the columns you need the same way
            }
        }
    }
}

这篇关于.NET Core中的原始SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！