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

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/tianchao7c/article/details/83580918
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格式返回到微信小程序里

上一篇:智能未来,从NoSQL出发


下一篇:RHEL6基本使用方法和RHEL7的区别