System.Data.SqlClient.SqlException:'.'附近的语法不正确. [英] System.Data.SqlClient.SqlException: 'Incorrect syntax near '.'.'

问题描述

我尝试一些有关带SQL的.Net Core Wep API的知识.我在'.''附近弄错了语法.

I try to something about .Net Core Wep API with SQL. I got the incorrect syntax near '.''.'

我将邮递员用于api，并尝试使用json来实现.

ı use postman for api and try whether it came or not with json.

在appsettings.json代码中；

in appsettings.json code;

{
  "ConnectionStrings": {
    "EmployeeAppCon": "Data Source=.;Initial Catalog=EmployeeDB; Integrated Security=true"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

然后我尝试了get，post方法，但是在这里我遇到了Uptade方法的错误；

And ı tried get, post method but ı got an error in here with Uptade method ;

[HttpPut]
        public JsonResult Put(Department dep)
        {
            string query = @"
                       Uptade dbo.Department set 
                       DepartmentName='"+dep.DepartmentName+@"'
                       where DepartmentId="+dep.DepartmentId+@"
                       ";
            DataTable table = new DataTable();
            string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");
            SqlDataReader myReader;
            using (SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader); ;
                    myReader.Close();
                    myCon.Close();
                }

            }
            return new JsonResult("Uptade Successfull");

        }

我的错在哪里，请多多帮助我

Where is my fault please help me thanks a lot?

推荐答案

这很可能是SQL中串联的问题.长话短说:永远不会将输入连接到SQL； correct 操作更像是:

This is most likely a problem with concatenation in the SQL; long story short: never ever concatenate input into SQL; the correct operation is more like:

update dbo.Department
set DepartmentName=@name
where DepartmentId=@id

其中 @name 和 @id 是参数.

然后，您将使用 myCommand.Parameters.Add(...)包含这两个参数及其名称/值，并使用 ExecuteNonQuery (不是 ExecuteReader ).

Then you would use myCommand.Parameters.Add(...) to include those two parameters and their names/values, and use ExecuteNonQuery (not ExecuteReader).

但是！让 Dapper (免费等)来完成所有艰苦的工作会容易得多.我们，那么我们就可以这样做:

However! It would be much simpler to get Dapper (free etc) to do all the hard work for us, then we can just do:

using var myCon = new SqlConnection(sqlDataSource); // don't even need to open it
myCon.Execute(@"
update dbo.Department
set DepartmentName=@name
where DepartmentId=@id",
    new { name = dep.DepartmentName, id = dep.DepartmentId });

此处的 new {...} 定义了我们的带有值的命名参数.

where the new {...} here defines our named parameters with values.

这篇关于System.Data.SqlClient.SqlException:'.'附近的语法不正确.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！