ASP.NET CORE 连接 SQL SERVER2008R2 数据库

r囧r小猫 2023-10-18 23:49 111阅读 0赞

## ASP.NET CORE 连接 SQL SERVER ##

1.新建asp.net项目，我用的是VS 2017  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 2]  
2.打开如图控制台，输入命令：Scaffold-DbContext “Data Source=127.0.0.1;Initial Catalog=Spring\_MVC\_Core;User ID=sa;Password=\*\*\*\*\*\*\*;MultipleActiveResultSets=true;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models 回车，参数分别为数据库地址、名字、管理员账号、密码  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 3]  
3.会根据你的数据库名称自动生成一个Context.cs文件，右键 controllers 文件夹，然后选择控制器，选择视图控制器，在新窗口选择用户模型，添加  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 5]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 6]  
4.程序会自动创建user控制器和视图  
![在这里插入图片描述][20190813101958512.png]  
5.打开Models，Spring\_MVC\_CoreContext.cs文件，注释掉如下内容

using System;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.EntityFrameworkCore.Metadata;
    
    namespace TestCore.Models
    {
        public partial class Spring_MVC_CoreContext : DbContext
        {
            public Spring_MVC_CoreContext()
            {
            }
    
            public Spring_MVC_CoreContext(DbContextOptions<Spring_MVC_CoreContext> options)
                : base(options)
            {
            }
    
            public virtual DbSet<Users> Users { get; set; }
    
            //protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            //{
            //    if (!optionsBuilder.IsConfigured)
            //    {
            //        #warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
            //        optionsBuilder.UseSqlServer("Data Source=127.0.0.1;Initial Catalog=Spring_MVC_Core;User ID=sa;Password=654321;MultipleActiveResultSets=true;");
            //    }
            //}
    
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Users>(entity =>
                {
                    entity.ToTable("users");
    
                    entity.Property(e => e.Id)
                        .HasColumnName("id")
                        .ValueGeneratedNever();
    
                    entity.Property(e => e.Account)
                        .IsRequired()
                        .HasColumnName("account")
                        .HasMaxLength(50);
    
                    entity.Property(e => e.Email)
                        .HasColumnName("email")
                        .HasMaxLength(50);
    
                    entity.Property(e => e.Name)
                        .IsRequired()
                        .HasColumnName("name")
                        .HasMaxLength(50);
    
                    entity.Property(e => e.Password)
                        .IsRequired()
                        .HasColumnName("password")
                        .HasMaxLength(50);
    
                    entity.Property(e => e.Phone)
                        .HasColumnName("phone")
                        .HasMaxLength(50);
    
                    entity.Property(e => e.Rights)
                        .IsRequired()
                        .HasColumnName("rights")
                        .HasMaxLength(50);
                });
            }
        }
    }

6.打开appsettings.json文件，添加数据库连接，如下

{
      "Logging": {
        "LogLevel": {
          "Default": "Warning"
        }
      },
      "AllowedHosts": "*",
    
      "ConnectionStrings": {
        // 新加入的内容：数据库地址       数据库名       帐号          密码
        "DefaultConnection": "Server=localhost;Database=Spring_MVC_Core;User ID=sa;Password=******;"
      }
    }

7.打开startup.cs，加入数据库服务

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;
    using Microsoft.AspNetCore.Builder;
    using Microsoft.AspNetCore.Hosting;
    using Microsoft.AspNetCore.Http;
    using Microsoft.AspNetCore.HttpsPolicy;
    using Microsoft.AspNetCore.Mvc;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.Extensions.Configuration;
    using Microsoft.Extensions.DependencyInjection;
    using TestCore.Models;
    
    namespace TestCore
    {
        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)
            {
                services.Configure<CookiePolicyOptions>(options =>
                {
                    // This lambda determines whether user consent for non-essential cookies is needed for a given request.
                    options.CheckConsentNeeded = context => true;
                    options.MinimumSameSitePolicy = SameSiteMode.None;
                });
    			//下面为加入的数据库服务，注意Spring_MVC_CoreContext修改为自己的文件名
                services.AddDbContext<Spring_MVC_CoreContext>(options =>
                    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
    
                services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
            }
    
            // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
            public void Configure(IApplicationBuilder app, IHostingEnvironment env)
            {
                if (env.IsDevelopment())
                {
                    app.UseDeveloperExceptionPage();
                }
                else
                {
                    app.UseExceptionHandler("/Home/Error");
                    app.UseHsts();
                }
    
                app.UseHttpsRedirection();
                app.UseStaticFiles();
                app.UseCookiePolicy();
    
                app.UseMvc(routes =>
                {
                    routes.MapRoute(
                        name: "default",
                        //我将users设为默认启动了，程序打开后可以看见users数据
                        template: "{controller=Users}/{action=Index}/{id?}");
                });
            }
        }
    }

8.可以将users设为默认启动，或者在views/shared/\_layout.cshtml内加入users链接，如下

<ul class="nav navbar-nav">
        <li><a asp-area="" asp-controller="Home" asp-action="Index">Home</a></li>
        <li><a asp-area="" asp-controller="Home" asp-action="About">About</a></li>
       <li><a asp-area="" asp-controller="Home" asp-action="Contact">Contact</a></li>
       <li><a asp-area="" asp-controller="Users" asp-action="Index">Users</a></li>
    </ul>

9.最终效果如图  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 7]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190813100654971.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190813100819225.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2019081310094442.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20190813101106346.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20190813101609305.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20190813101651172.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20190813101802894.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ==,size_16,color_FFFFFF,t_70
[20190813101958512.png]: https://img-blog.csdnimg.cn/20190813101958512.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20190813103001611.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE3NjYzOQ==,size_16,color_FFFFFF,t_70