WEB-API+.NET+CRUD+SSMS(VS2022)

标签：WEB string Parameters CRUD +. item command new public

1.使用VS2022创建一个web-api项目，根目录如下：

其中TestCode.cs写model实体类，Controller编写控制器
2.实体类Item，编写对应的属性

点击查看代码

 public class Item
 {
     [Required]
     public int Id { get; set; }
     [Required] 
     public int FieldID { get; set; }
     [Required] 
     public string Description { get; set; }
     [Required] 
     public string FuncMod { get; set; }
     [Required]
     public DateTime Date { get; set; }
     [Required]
     public string Questioner { get; set; }
     [Required]
     public string BaFollower { get; set; }
     [Required]
     public string Developer { get; set; }

     // 你可以添加一个方法来解析日期字符串
    /* public DateTime ParseDate(string date)
     {
         return DateTime.Parse(date); // 或者使用 DateTime.TryParse 来更安全地解析
     }*/
 }

3.编写控制器实现CRUD 编写函数封装数据库操作

点击查看代码

//封装数据库操作 便于使用
public class DatabaseHelper
{
    //数据库连接字符串配置文件
    private string connectionString = "Data Source=IP地址;Initial Catalog=数据库名称;User ID=用户名;Password=密码;TrustServerCertificate=true;";

    public SqlConnection Connection ;
	public DatabaseHelper()
	{

    	Connection = new SqlConnection(connectionString);
	}
	public string ConnectionStr
	{
    	get { return _ConnectionStr +"2"; }
    	set { _ConnectionStr = value +"1"; }
	}
	private string _ConnectionStr;
}

继续在class DatabaseHelper类中编写CRUD操作
查询所有条目：

点击查看代码

//执行查询 将查询结果返回dt
public DataTable ExecuteQuery(string sql)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        return dt;
    }
}

查询对应ID的条目

点击查看代码

 //根据ID查询Description
 // 根据ID查询问题描述
 public string GetQuestionDescriptionById(int id)
 {
     using (SqlConnection conn = new SqlConnection(connectionString))
     {
         conn.Open();
         SqlCommand cmd = new SqlCommand("SELECT Description FROM Task_Test_Wxy1 WHERE Id = @Id", conn);
         cmd.Parameters.AddWithValue("@Id", id);
         return (string)cmd.ExecuteScalar();
     }
 }

在class ItemController : ControllerBase类中编写查询接口

点击查看代码

[ApiController]	//控制器标识
[Route("api/[controller]/[action]")]	//路由
public class ItemController : ControllerBase
{
}

查询接口详细代码如下

点击查看代码

[HttpGet(Name ="GetDescription")]
public IEnumerable<string>GetContent()
{
    /*string connectionString = "Data Source=IP地址;Initial Catalog=数据库名称;User ID=用户名;Password=密码;TrustServerCertificate=true;";
    //using语句可以确保在操作完成后正确关闭和释放资源
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        // 在这里执行数据库操作
        //获取所有问题描述
        string strSql = "select Description from Task_Test_Wxy1 ";
        SqlDataAdapter objDataAdpter = new SqlDataAdapter();
        objDataAdpter.SelectCommand = new SqlCommand(strSql, connection);
        DataSet ds = new DataSet();
        objDataAdpter.Fill(ds, "t_position");
        dataGridView1.DataSource = ds.Tables[0];
        connection.Close();
    }*/
    //string myConn = ConfigurationManager.ConnectionStrings["MySql"].ConnectionString;
    
    DatabaseHelper db=new DatabaseHelper();
    DataTable result = db.ExecuteQuery("select Description from Task_Test_Wxy1");

    //获取所有问题描述
    //return new string[] { "content1", "content2" };

    // 将DataTable转换为JSON
    string json = JsonConvert.SerializeObject(result);

    // 打印或返回JSON字符串
    Console.WriteLine(json);
    return new[] { json };

}

[HttpGet("{id}")]
public ActionResult GetContentById(int id)
{
    //获取对应ID的问题描述
    DatabaseHelper db = new DatabaseHelper();
    string description = db.GetQuestionDescriptionById(id);

    if (string.IsNullOrEmpty(description))
    {
        return NotFound($"No question description found with ID {id}");
    }

    return Ok(new { Id = id, Description = description });


    //return "content" + id;
}

同理更新操作的数据库操作函数：

点击查看代码

public static bool UpdateItemById(int id, Item item)
{
    DatabaseHelper db = new DatabaseHelper();
    using (SqlConnection conn = db.Connection)
    {
        conn.Open();
        string sql = "UPDATE Task_Test_Wxy1 SET FieldID = @FieldID, FuncMod = @FuncMod, Date=@Date, Description=@Description, Questioner=@Questioner, BaFollower=@BaFollower, Developer=@Developer WHERE Id = @Id";
        using(SqlCommand command = new SqlCommand(sql, conn))
        {
            command.Parameters.AddWithValue("@FieldID", item.FieldID);
            command.Parameters.AddWithValue("@FuncMod", item.FuncMod);
            command.Parameters.AddWithValue("@Date", item.Date);
            command.Parameters.AddWithValue("@Description", item.Description);
            command.Parameters.AddWithValue("@Questioner", item.Questioner);
            command.Parameters.AddWithValue("@BaFollower", item.BaFollower);
            command.Parameters.AddWithValue("@Developer", item.Developer);
            command.Parameters.AddWithValue("@Id", id);
            int result = command.ExecuteNonQuery();
            return result > 0; // 如果影响的行数大于0，则更新成功
        }
    }
}

按ID更新对应条目接口代码：

点击查看代码

[HttpPut("{id}")]
public ActionResult<string> PutContent(int id, [FromBody] Item content)
{
    //修改 int id 是从 URL 路由中获取的参数，而 Item content 是从请求正文中获取的对象
    bool isUpdated = DatabaseHelper.UpdateItemById(id, content);

    if (isUpdated)
    {
        return Ok("修改成功");
    }
    else
    {
        return NotFound("未找到");
    }
}

插入数据库操作函数：

点击查看代码

//执行插入 更新&删除操作
public int ExecuteNonQuery(string sql)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        conn.Open();
        return cmd.ExecuteNonQuery();
    }
}

插入接口代码：

点击查看代码

[HttpPost]
public ActionResult PostContent([FromBody] Item content)
{
     
    /*var settings = new JsonSerializerSettings
    {
        DateParseHandling = DateParseHandling.DateTime,
        DateFormatString = "yyyy-MM-dd"
    };*/
    // 解析JSON字符串为对象（这里需要根据实际结构来解析）
    //Item item = JsonConvert.DeserializeObject<Item>(content);
    //Item item = JsonConvert.DeserializeObject<Item>(content, settings);
    Item item = content;

   /* try
    {
        item = JsonConvert.DeserializeObject<Item>(content, settings);
    }
    catch (JsonReaderException ex)
    {
        // 处理异常，例如记录错误日志
        Console.Error.WriteLine($"JSON parsing error: {ex.Message}");
    }*/

    //item.Date = item.ParseDate(item.Date); // 假设你已经将 Date 字段的类型更改为 string

    /*// 构建SQL命令
    string sql = $"INSERT INTO YourTable (FieldID, FuncMod, Date, Description, Questioner, BaFollower, Developer) VALUES ('{data.FieldID}', '{data.FuncMod}', '{data.Date}', '{data.Description}', '{data.Questioner}', '{data.BaFollower}', '{data.Developer}')";
    // 执行SQL命令
    int affectedRows = db.ExecuteNonQuery(sql);*/

    string sql = "INSERT INTO Task_Test_Wxy1 (FieldID, FuncMod, Date, Description, Questioner, BaFollower, Developer) VALUES (@FieldID, @FuncMod, @Date, @Description, @Questioner, @BaFollower, @Developer)";

    //新增
    DatabaseHelper db = new DatabaseHelper();
    db.ConnectionStr = "abc";
    var str = db.ConnectionStr;

    if (db.Connection.State != ConnectionState.Open)
    {
        db.Connection.Open(); // 尝试打开连接
    }
    using (SqlCommand command = new SqlCommand(sql, db.Connection))
    {
        // 检查连接状态，确保它是打开的
       
        command.Parameters.AddWithValue("@FieldID", item.FieldID);
        command.Parameters.AddWithValue("@FuncMod", item.FuncMod);
        command.Parameters.AddWithValue("@Date", item.Date);
        command.Parameters.AddWithValue("@Description", item.Description);
        command.Parameters.AddWithValue("@Questioner", item.Questioner);
        command.Parameters.AddWithValue("@BaFollower", item.BaFollower);
        command.Parameters.AddWithValue("@Developer", item.Developer);
        
        // 执行SQL命令
        int affectedRows = command.ExecuteNonQuery();

        // 检查是否成功插入
        if (affectedRows > 0)
        {
            return Ok(new { message = "Data inserted successfully." });
        }
        else
        {
            return BadRequest(new { message = "Insertion failed." });
        }
    }

}

删除数据库操作函数：

点击查看代码

public static bool DeleteItemById(int id)
{
    DatabaseHelper db = new DatabaseHelper();
    using (SqlConnection conn = db.Connection)
    {
        conn.Open();
        string sql = "DELETE FROM Task_Test_Wxy1 WHERE Id = @Id";
        using (SqlCommand command = new SqlCommand(sql, conn))
        {
            command.Parameters.AddWithValue("@Id", id);
            int result = command.ExecuteNonQuery();
            return result > 0; // 如果影响的行数大于0，则删除成功
        }
    }

}

删除接口代码：

点击查看代码

[HttpDelete("{id}")]
public ActionResult DeleteContent(int id)
{
    //删除对应ID的信息
    bool isDeleted = DatabaseHelper.DeleteItemById(id);

    if (isDeleted)
    {
        return Ok("删除成功");
    }
    else
    {
        return NotFound("未找到");
    }
}

细节：如果在数据库操作封装函数中直接return new SqlConnection(connectionString)那么在接口函数中使用db.connection会不断地重新new一个新的对象，从而导致db.Connection.Open();仅在该行生效，随之关闭，在进行数据库操作时不断报错 Connection close，因此，直接写一个构造函数避免不断重复new新对象的问题

控制器所有代码如下：

点击查看代码

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Http.HttpResults;
using Microsoft.AspNetCore.Mvc;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;
using Newtonsoft.Json;

namespace test.Controllers
{
    //封装数据库操作 便于使用
    public class DatabaseHelper
    {
        //数据库连接字符串配置文件
        private string connectionString = "Data Source=10.211.90.25;Initial Catalog=XMES3;User ID=Biel_PRO;Password=Biel_PRO#2021;TrustServerCertificate=true;";

        public SqlConnection Connection ;

        public DatabaseHelper()
        {

            Connection = new SqlConnection(connectionString);
        }
        public string ConnectionStr
        {
            get { return _ConnectionStr +"2"; }
            set { _ConnectionStr = value +"1"; }
        }
        private string _ConnectionStr;




        //执行查询 将查询结果返回dt
        public DataTable ExecuteQuery(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }
        //根据ID查询Description
        // 根据ID查询问题描述
        public string GetQuestionDescriptionById(int id)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT Description FROM Task_Test_Wxy1 WHERE Id = @Id", conn);
                cmd.Parameters.AddWithValue("@Id", id);
                return (string)cmd.ExecuteScalar();
            }
        }

        public static bool DeleteItemById(int id)
        {
            DatabaseHelper db = new DatabaseHelper();
            using (SqlConnection conn = db.Connection)
            {
                conn.Open();
                string sql = "DELETE FROM Task_Test_Wxy1 WHERE Id = @Id";
                using (SqlCommand command = new SqlCommand(sql, conn))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    int result = command.ExecuteNonQuery();
                    return result > 0; // 如果影响的行数大于0，则删除成功
                }
            }

        }

        public static bool UpdateItemById(int id, Item item)
        {
            DatabaseHelper db = new DatabaseHelper();
            using (SqlConnection conn = db.Connection)
            {
                conn.Open();
                string sql = "UPDATE Task_Test_Wxy1 SET FieldID = @FieldID, FuncMod = @FuncMod, Date=@Date, Description=@Description, Questioner=@Questioner, BaFollower=@BaFollower, Developer=@Developer WHERE Id = @Id";
                using(SqlCommand command = new SqlCommand(sql, conn))
                {
                    command.Parameters.AddWithValue("@FieldID", item.FieldID);
                    command.Parameters.AddWithValue("@FuncMod", item.FuncMod);
                    command.Parameters.AddWithValue("@Date", item.Date);
                    command.Parameters.AddWithValue("@Description", item.Description);
                    command.Parameters.AddWithValue("@Questioner", item.Questioner);
                    command.Parameters.AddWithValue("@BaFollower", item.BaFollower);
                    command.Parameters.AddWithValue("@Developer", item.Developer);
                    command.Parameters.AddWithValue("@Id", id);
                    int result = command.ExecuteNonQuery();
                    return result > 0; // 如果影响的行数大于0，则更新成功
                }
            }
        }

        //执行插入 更新&删除操作
        public int ExecuteNonQuery(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }
    }



    [ApiController]
    [Route("api/[controller]/[action]")]
    public class ItemController : ControllerBase
    {

        [HttpGet(Name = "GetTest2")]
        [Description("测试")]
        public ActionResult<IEnumerable<TestCode>> Get()
        {
            var forecast = new List<TestCode>
            {
                new TestCode{Code=0,Msg="Hello Biel"},
                  new TestCode{Code=0,Msg="Hello Biel"}
            };

            return Ok(forecast);
        }

        [HttpGet(Name ="GetDescription")]
        public IEnumerable<string>GetContent()
        {
            /*string connectionString = "Data Source=10.211.90.25;Initial Catalog=XMES3;User ID=Biel_PRO;Password=Biel_PRO#2021;TrustServerCertificate=true;";
            //using语句可以确保在操作完成后正确关闭和释放资源
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                // 在这里执行数据库操作
                //获取所有问题描述
                string strSql = "select Description from Task_Test_Wxy1 ";
                SqlDataAdapter objDataAdpter = new SqlDataAdapter();
                objDataAdpter.SelectCommand = new SqlCommand(strSql, connection);
                DataSet ds = new DataSet();
                objDataAdpter.Fill(ds, "t_position");
                dataGridView1.DataSource = ds.Tables[0];
                connection.Close();
            }*/
            //string myConn = ConfigurationManager.ConnectionStrings["MySql"].ConnectionString;
            
            DatabaseHelper db=new DatabaseHelper();
            DataTable result = db.ExecuteQuery("select Description from Task_Test_Wxy1");

            //获取所有问题描述
            //return new string[] { "content1", "content2" };

            // 将DataTable转换为JSON
            string json = JsonConvert.SerializeObject(result);

            // 打印或返回JSON字符串
            Console.WriteLine(json);
            return new[] { json };

        }

        [HttpGet("{id}")]
        public ActionResult GetContentById(int id)
        {
            //获取对应ID的问题描述
            DatabaseHelper db = new DatabaseHelper();
            string description = db.GetQuestionDescriptionById(id);

            if (string.IsNullOrEmpty(description))
            {
                return NotFound($"No question description found with ID {id}");
            }

            return Ok(new { Id = id, Description = description });


            //return "content" + id;
        }
        

        [HttpPost]
        public ActionResult PostContent([FromBody] Item content)
        {
             
            /*var settings = new JsonSerializerSettings
            {
                DateParseHandling = DateParseHandling.DateTime,
                DateFormatString = "yyyy-MM-dd"
            };*/
            // 解析JSON字符串为对象（这里需要根据实际结构来解析）
            //Item item = JsonConvert.DeserializeObject<Item>(content);
            //Item item = JsonConvert.DeserializeObject<Item>(content, settings);
            Item item = content;

           /* try
            {
                item = JsonConvert.DeserializeObject<Item>(content, settings);
            }
            catch (JsonReaderException ex)
            {
                // 处理异常，例如记录错误日志
                Console.Error.WriteLine($"JSON parsing error: {ex.Message}");
            }*/

            //item.Date = item.ParseDate(item.Date); // 假设你已经将 Date 字段的类型更改为 string

            /*// 构建SQL命令
            string sql = $"INSERT INTO YourTable (FieldID, FuncMod, Date, Description, Questioner, BaFollower, Developer) VALUES ('{data.FieldID}', '{data.FuncMod}', '{data.Date}', '{data.Description}', '{data.Questioner}', '{data.BaFollower}', '{data.Developer}')";
            // 执行SQL命令
            int affectedRows = db.ExecuteNonQuery(sql);*/

            string sql = "INSERT INTO Task_Test_Wxy1 (FieldID, FuncMod, Date, Description, Questioner, BaFollower, Developer) VALUES (@FieldID, @FuncMod, @Date, @Description, @Questioner, @BaFollower, @Developer)";

            //新增
            DatabaseHelper db = new DatabaseHelper();
            db.ConnectionStr = "abc";
            var str = db.ConnectionStr;

            if (db.Connection.State != ConnectionState.Open)
            {
                db.Connection.Open(); // 尝试打开连接
            }
            using (SqlCommand command = new SqlCommand(sql, db.Connection))
            {
                // 检查连接状态，确保它是打开的
               
                command.Parameters.AddWithValue("@FieldID", item.FieldID);
                command.Parameters.AddWithValue("@FuncMod", item.FuncMod);
                command.Parameters.AddWithValue("@Date", item.Date);
                command.Parameters.AddWithValue("@Description", item.Description);
                command.Parameters.AddWithValue("@Questioner", item.Questioner);
                command.Parameters.AddWithValue("@BaFollower", item.BaFollower);
                command.Parameters.AddWithValue("@Developer", item.Developer);
                
                // 执行SQL命令
                int affectedRows = command.ExecuteNonQuery();

                // 检查是否成功插入
                if (affectedRows > 0)
                {
                    return Ok(new { message = "Data inserted successfully." });
                }
                else
                {
                    return BadRequest(new { message = "Insertion failed." });
                }
            }

        }


        /*[HttpPost]
        public ActionResult PostContent1([FromBody] string content)
        {

            var settings = new JsonSerializerSettings
            {
                DateParseHandling = DateParseHandling.DateTime,
                DateFormatString = "yyyy-MM-dd"
            };
            // 解析JSON字符串为对象（这里需要根据实际结构来解析）
            //Item item = JsonConvert.DeserializeObject<Item>(content);
            //Item item = JsonConvert.DeserializeObject<Item>(content, settings);
            Item item = null;

            try
            {
                item = JsonConvert.DeserializeObject<Item>(content, settings);
            }
            catch (JsonReaderException ex)
            {
                // 处理异常，例如记录错误日志
                Console.Error.WriteLine($"JSON parsing error: {ex.Message}");
            }

            //item.Date = item.ParseDate(item.Date); // 假设你已经将 Date 字段的类型更改为 string

            *//*// 构建SQL命令
            string sql = $"INSERT INTO YourTable (FieldID, FuncMod, Date, Description, Questioner, BaFollower, Developer) VALUES ('{data.FieldID}', '{data.FuncMod}', '{data.Date}', '{data.Description}', '{data.Questioner}', '{data.BaFollower}', '{data.Developer}')";
            // 执行SQL命令
            int affectedRows = db.ExecuteNonQuery(sql);*//*

            string sql = "INSERT INTO Task_Test_Wxy1 (FieldID, FuncMod, Date, Description, Questioner, BaFollower, Developer) VALUES (@FieldID, @FuncMod, @Date, @Description, @Questioner, @BaFollower, @Developer)";

            //新增
            DatabaseHelper db = new DatabaseHelper();
            SqlConnection conn = db.Connection;
            if (conn.State != ConnectionState.Open)
            {
                conn.Open(); // 尝试打开连接
            }
            using (SqlCommand command = new SqlCommand(sql, conn))
            {
                // 检查连接状态，确保它是打开的

                command.Parameters.AddWithValue("@FieldID", item.FieldID);
                command.Parameters.AddWithValue("@FuncMod", item.FuncMod);
                command.Parameters.AddWithValue("@Date", item.Date);
                command.Parameters.AddWithValue("@Description", item.Description);
                command.Parameters.AddWithValue("@Questioner", item.Questioner);
                command.Parameters.AddWithValue("@BaFollower", item.BaFollower);
                command.Parameters.AddWithValue("@Developer", item.Developer);

                // 执行SQL命令
                int affectedRows = command.ExecuteNonQuery();

                // 检查是否成功插入
                if (affectedRows > 0)
                {
                    return Ok(new { message = "Data inserted successfully." });
                }
                else
                {
                    return BadRequest(new { message = "Insertion failed." });
                }
            }

        }*/



        [HttpPut("{id}")]
        public ActionResult<string> PutContent(int id, [FromBody] Item content)
        {
            //修改
            bool isUpdated = DatabaseHelper.UpdateItemById(id, content);

            if (isUpdated)
            {
                return Ok("修改成功");
            }
            else
            {
                return NotFound("未找到");
            }
        }

        [HttpDelete("{id}")]
        public ActionResult DeleteContent(int id)
        {
            //删除对应ID的信息
            bool isDeleted = DatabaseHelper.DeleteItemById(id);

            if (isDeleted)
            {
                return Ok("删除成功");
            }
            else
            {
                return NotFound("未找到");
            }
        }

        private static readonly string[] Summaries = new[]
        {
            "Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
        };

        private readonly ILogger<ItemController> _logger;

        public ItemController(ILogger<ItemController> logger)
        {
            _logger = logger;
        }

        [HttpGet(Name = "GetWeatherForecast")]
        public IEnumerable<TestCode> Get1()
        {
            return Enumerable.Range(1, 5).Select(index => new TestCode
            {
                Code = 0,
                Msg = "Hello Biel"
            })
            .ToArray();
        }
    }
}

标签：WEB,string,Parameters,CRUD,+.,item,command,new,public
From： https://www.cnblogs.com/pbnote/p/18410744

WEB-API+.NET+CRUD+SSMS(VS2022)

相关文章

赞助商

阅读排行