.net core ADO.net 操作数据库---查询

第一步：数据库链接字符串

项目根目录 appsettings.json

添加一行

"ConStr": "Data Source=.;database=ceshi;uid=sa;pwd=sa",

完整代码

{
  "ConStr": "Data Source=.;database=ceshi;uid=sa;pwd=sa",

  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

第二步：帮助类

项目根目录新建Utility文件夹

SqlHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace swaggertest.Utility
{
    public class SqlHelper
    {
        public static string ConStr { set; get; }

        public static DataTable ExecuteTable(string comText, params SqlParameter[] sqlParameters)
        {
            using (SqlConnection conn=new SqlConnection(ConStr)) 
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(comText,conn);
                cmd.Parameters.AddRange(sqlParameters);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                return ds.Tables[0];
            }
        }
    }
}

ToModel.cs

用于反射实体类赋值

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace swaggertest.Utility
{
    public static class ToModel
    {
        public static T DtToModel<T>(this DataRow dr) 
        {
            //获取类型
            Type t = typeof(T);
            //实例化对象
            T md = (T)Activator.CreateInstance(t);

            var props = t.GetProperties();
            foreach (var prop in props)
            {
                if (dr.Table.Columns.Contains(prop.Name))
                {
                    prop.SetValue(md,dr[prop.Name]);
                }
            }
            return md;
        }
    }
}

第三步：配置

项目根目录 Startup.cs文件

在ConfigureServices()方法里，添加如下代码

SqlHelper.ConStr = Configuration["ConStr"].Trim();

完整代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.OpenApi.Models;
using swaggertest.Utility;

namespace swaggertest
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            SqlHelper.ConStr = Configuration["ConStr"].Trim();

            //跨域服务注册
            services.AddCors(m=>m.AddPolicy("any",a=>a.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader()));

            //Swagger
            services.AddSwaggerGen(m=>{
                m.SwaggerDoc("v1", new OpenApiInfo { Title="swaggerTest",Version="v1" });
            });
            services.AddControllers();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseRouting();

            app.UseAuthorization();

            //Swagger
            app.UseSwagger();
            app.UseSwaggerUI(s=> {
                //下面路径里的v1必须和SwaggerDoc（）第一个参数一致
                s.SwaggerEndpoint("/swagger/v1/swagger.json", "swaggerTast");
            });

            //跨域
            app.UseCors();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

第四步：模型

项目根目录新建Models文件夹

Products.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using swaggertest.Utility;

namespace swaggertest.Models
{
    public class Products
    {
        public int Id { set; get; }

        public String Name { set; get; }

        public static List<Products> ListAll() 
        {
            List<Products> products = new List<Products>();
            DataTable dt = SqlHelper.ExecuteTable("select * from Products");
            foreach (DataRow dr in dt.Rows)
            {
                products.Add(dr.DtToModel<Products>());
            }
            return products;
        }

    }
}

第五步：控制器

Controllers》ProductsController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Cors;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using swaggertest.Models;

namespace swaggertest.Controllers
{
    [EnableCors("any")]
    [Route("api/[controller]/[Action]")]
    //[ApiController]
    public class ProductsController : ControllerBase
    {
        [HttpGet]
        public IActionResult GetProduct()
        {
          List<Products> products= Products.ListAll();
            return new JsonResult(products);
        }
    }
}

总结

其实，和平常使用asp.net写代码一样，无非是配置不太一样。