c# MVC框架对mysql数据库的查询

最新推荐文章于 2025-08-01 18:12:05 发布

转载最新推荐文章于 2025-08-01 18:12:05 发布 · 611 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/674050

文章标签：

#数据库 #测试 #json

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using demo2.Models;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;

namespace demo2.Controllers
{
    public class GetFilesController : Controller
    {
        // GET: GetFiles
        [HttpGet]
        public void GetFiles(GetFiles Parameter)
        {
            string sn = Parameter.Sn_code;
            string t = DateTime.Now.ToString("yyyyMMdd");
            string name = Parameter.File_name;
            MySqlConnection sqlCnn = new MySqlConnection();
            string connetStr = "Server = 127.0.0.1; Port = 3306; Database = Database; Uid = root; Pwd = root;SslMode = none;";
            MySqlConnection conn = new MySqlConnection(connetStr);
            //string sql1 = string.Format("select * from device_files where sn_code='{0}' order by file_time,fid DESC limit 0,1", sn);
            //string sql1 = string.Format("select * from device_files where sn_code='{0}' and file_time='{1}'", sn, t); //查询当前日期包含的数据
            string sql1 = string.Format("select * from device_files where sn_code='{0}' and file_name like '%{1}%'", sn, name); //模糊查询文件名

            MySqlCommand cmd = new MySqlCommand(sql1, conn);

            try
            {
                conn.Open();
                //执行查询，并将结果返回给读取器
                MySqlDataReader reader = cmd.ExecuteReader();

                List<GetFiles> proList = new List<GetFiles>();

                while (reader.Read())
                {
                    proList.Add(new GetFiles
                    {
                        Fid = reader.GetString(reader.GetOrdinal("fid")),
                        Sn_code = reader.GetString(reader.GetOrdinal("sn_code")),
                        File_src = reader.GetString(reader.GetOrdinal("file_src")),
                        File_time = reader.GetString(reader.GetOrdinal("file_time")),
                        File_name = reader.GetString(reader.GetOrdinal("file_name"))
                    });
                }

                var obj = new
                {
                    Sn_code = reader.GetString(reader.GetOrdinal("sn_code")),
                    data = proList,
                    message = true,
                    state = 200
                };
                Formatting microsoftDataFormatSettings = default(Formatting);
                string result = JsonConvert.SerializeObject(obj, microsoftDataFormatSettings);
                Response.Write(result);
            }
            catch (MySqlException ex)
            {
                Response.Write(ex);
            }
            finally
            {
                conn.Close();
                Console.WriteLine("关闭数据库");
            }
        }
    }
}

查询结果生成json格式返回到微信小程序里