.NET Core中的原始SQL

本文介绍了.NET Core中的原始SQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这个问题：我需要从.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?

推荐答案

首先，您的代码是 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 } } } }