时间戳

首页 > 解决方案 > SQL 存储过程和 ASP.NET API 控制器

asp.net - SQL 存储过程和 ASP.NET API 控制器

问题描述

我正在尝试为我的删除方法编写一个存储过程。它适用于我创建的字符串查询，但我正在努力使其更安全。这是我改变之前的情况。

存储过程 控制器之前

[HttpDelete]
        public JsonResult Delete(int ID)
        {
            string query = @"DELETE FROM dbo.WeatherForecast WHERE ID =" + ID;

            DataTable table = new DataTable();
            string sqlDataSource = _configuration.GetConnectionString("WeatherAppCon");
            SqlDataReader myReader;
            using (SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (System.Data.SqlClient.SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    
                   
                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader);

                    myReader.Close();
                    myCon.Close();
                }
            }


            return new JsonResult("Row Deleted Successfully");
        }

存储过程 控制器之后

    [HttpDelete]
    public JsonResult Delete(int ID)
    {
        string query = "dbo.p_WeatherForecastDelete";

        DataTable table = new DataTable();
        string sqlDataSource = _configuration.GetConnectionString("WeatherAppCon");
        SqlDataReader myReader;
        using (SqlConnection myCon = new SqlConnection(sqlDataSource))
        {
            myCon.Open();
            using (System.Data.SqlClient.SqlCommand myCommand = new SqlCommand(query, myCon))
            {
                
               
                myReader = myCommand.ExecuteReader();
                table.Load(myReader);

                myReader.Close();
                myCon.Close();
            }
        }


        return new JsonResult("Row Deleted Successfully");
    }

SQL 存储过程（不起作用）

USE [WeatherTemplate]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[p_WeatherForecastDelete]
AS
BEGIN
    DELETE FROM dbo.WeatherForecast WHERE ID = + ID;
END

标签： asp.netstored-procedureshttp-delete

解决方案

有几点需要纠正：

  1. 指定 SqlCommand 类型

    myCommand.CommandType = CommandType.StoredProcedure;

  2. 添加参数

    myCommand.Parameters.Add(new SqlParameter("@ID", ID));

  3. 修复存储过程以接收参数并正确使用它

    ALTER PROCEDURE [dbo].[p_WeatherForecastDelete]
        @ID INT
    AS
    BEGIN
        DELETE FROM dbo.WeatherForecast WHERE ID = @ID;
    END

您似乎也没有阅读执行结果，因此您可以安全地将 myCommand.ExecuteReader() 替换为 myCommand.ExecuteNonQuery()

推荐阅读