009.Working with SQL Server LocalDB --【在sql server localdb 上操作数据】

淡淡的烟草味﹌ 2021-09-29 23:08 231阅读 0赞

**索引：**

[目录索引][Link 1]

**Working with SQL Server LocalDB**

在sql server localdb 上操作数据

2017-3-7 2 分钟阅读时长

本文内容

**1.[SQL Server Express LocalDB][]**

[SQL Server Express LocalDB][]  sql server 的一个简化免费版本

**2.[Seed the database][]**

初始化数据库的初始表数据

By [Rick Anderson][]

The MvcMovieContext object handles the task of connecting to the database and mapping Movie objects to database records.

MvcMovieContext 对象处理了链接数据库与映射Movie 对象到表记录的任务.

The database context is registered with the [Dependency Injection][] container in the ConfigureServices method in the *Startup.cs* file:

DB上下文在*Startup.cs* 文件的ConfigureServices 方法中被注册到了DI容器中:

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

1 public void ConfigureServices(IServiceCollection services)
     2 
     3 {
     4 
     5     // Add framework services.
     6 
     7     services.AddMvc();
     8 
     9  
    10 
    11     services.AddDbContext<MvcMovieContext>(options =>
    12 
    13             options.UseSqlServer(Configuration.GetConnectionString("MvcMovieContext")));
    14 
    15 }

C\# code

The ASP.NET Core [Configuration][] system reads the ConnectionString.

Asp.net core 的配置系统读取了ConnectionString 的配置的值。

For local development, it gets the connection string from the *appsettings.json* file:

对于本地开发，它从*appsettings.json* 文件获取链接字符串的值：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

1 "ConnectionStrings": {
    2 
    3   "MvcMovieContext": "Server=(localdb)\\mssqllocaldb;Database=MvcMovieContext-20613a4b-deb5-4145-b6cc-a5fd19afda13;Trusted_Connection=True;MultipleActiveResultSets=true"
    4 
    5 }

JSON Code

When you deploy the app to a test or production server, you can use an environment variable or another approach to set the connection string to a real SQL Server.

当你将应用部署到一个测试或生产服务器，你可以使用环境变量或其它的方法来设置一个真实的db链接字符串的值。

See [Configuration][] for more information.

查看[Configuration][] 获取更多信息。

**SQL Server Express LocalDB**

（一个简化的免费的轻量的sql server 版本）

LocalDB is a lightweight version of the SQL Server Express Database Engine that is targeted for program development.

LocalDB 是 SQL Server Express Database Engine 的一个轻量版本，目的是本地的程序开发。

LocalDB starts on demand and runs in user mode, so there is no complex configuration.

LocalDB 以用户模式直接开始查询即可，因此没有复杂的配置。

By default, LocalDB database creates "\*.mdf" files in the *C:/Users/<user>* directory.

默认情况下，LocalDB 会在*C:/Users/<user>* 文件夹下创建"\*.mdf" 数据库文件。

*  From the **View** menu, open **SQL Server Object Explorer** (SSOX).

从 **View**  菜单，打开 **SQL Server Object Explorer** ：

![525873-20170814150835287-633386401.png][]

*  Right click on the Movie table **> View Designer**

右击 Movie  表，点击  **> View Designer** 菜单

![525873-20170814151032100-896209336.png][]

![525873-20170814151100537-614738882.png][]

Note the key icon next to ID. By default, EF will make a property named ID the primary key.

注意PK图标在ID 字段旁边，EF默认会使用ID做为一个PK。

*  Right click on the Movie table **> View Data**

右击 Movie 表，选择 **> View Data** 菜单

![525873-20170814151256584-1483901556.png][]

![525873-20170814151335100-1761930772.png][]

**Seed the database**

向数据库初始化值

Create a new class named SeedData in the *Models* folder. Replace the generated code with the following:

在 *Models* 文件夹下新建一个名为SeedData 类，用下面的代码替换掉自动生成的代码：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

1 using Microsoft.EntityFrameworkCore;
      2 
      3 using Microsoft.Extensions.DependencyInjection;
      4 
      5 using System;
      6 
      7 using System.Linq;
      8 
      9  
     10 
     11 namespace MvcMovie.Models
     12 
     13 {
     14 
     15     public static class SeedData
     16 
     17     {
     18 
     19         public static void Initialize(IServiceProvider serviceProvider)
     20 
     21         {
     22 
     23             using (var context = new MvcMovieContext(
     24 
     25                 serviceProvider.GetRequiredService<DbContextOptions<MvcMovieContext>>()))
     26 
     27             {
     28 
     29                 // Look for any movies.
     30 
     31                 if (context.Movie.Any())
     32 
     33                 {
     34 
     35                     return;   // DB has been seeded
     36 
     37                 }
     38 
     39  
     40 
     41                 context.Movie.AddRange(
     42 
     43                      new Movie
     44 
     45                      {
     46 
     47                          Title = "When Harry Met Sally",
     48 
     49                          ReleaseDate = DateTime.Parse("1989-1-11"),
     50 
     51                          Genre = "Romantic Comedy",
     52 
     53                          Price = 7.99M
     54 
     55                      },
     56 
     57  
     58 
     59                      new Movie
     60 
     61                      {
     62 
     63                          Title = "Ghostbusters ",
     64 
     65                          ReleaseDate = DateTime.Parse("1984-3-13"),
     66 
     67                          Genre = "Comedy",
     68 
     69                          Price = 8.99M
     70 
     71                      },
     72 
     73  
     74 
     75                      new Movie
     76 
     77                      {
     78 
     79                          Title = "Ghostbusters 2",
     80 
     81                          ReleaseDate = DateTime.Parse("1986-2-23"),
     82 
     83                          Genre = "Comedy",
     84 
     85                          Price = 9.99M
     86 
     87                      },
     88 
     89  
     90 
     91                    new Movie
     92 
     93                    {
     94 
     95                        Title = "Rio Bravo",
     96 
     97                        ReleaseDate = DateTime.Parse("1959-4-15"),
     98 
     99                        Genre = "Western",
    100 
    101                        Price = 3.99M
    102 
    103                    }
    104 
    105                 );
    106 
    107                 context.SaveChanges();
    108 
    109             }
    110 
    111         }
    112 
    113     }
    114 
    115 }

C\# Code

If there are any movies in the DB, the seed initializer returns and no movies are added.

如果数据库中有数据记录，就会直接返回，如果没有就会添加这些初始数据。

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

1 if (context.Movie.Any())
    2 
    3 {
    4 
    5     return;   // DB has been seeded.
    6 
    7 }

C\# Code

Add the seed initializer to the end of the Configure method in the *Startup.cs* file:

把数据初始化类添加到*Startup.cs* 文件的Configure  方法的最后一行：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

1             app.UseStaticFiles();
     2 
     3             app.UseMvc(routes =>
     4 
     5             {
     6 
     7                 routes.MapRoute(
     8 
     9                     name: "default",
    10 
    11                     template: "{controller=Home}/{action=Index}/{id?}");
    12 
    13             });
    14 
    15  
    16 
    17             SeedData.Initialize(app.ApplicationServices);
    18 
    19         }
    20 
    21     }
    22 
    23 }

C\# Code

**Test the app**

测试应用

*  Delete all the records in the DB. You can do this with the delete links in the browser or from SSOX.

要删除db中的所有数据记录，你可以在 SSOX 中点击删除链接即可。

*  Force the app to initialize (call the methods in the Startup class) so the seed method runs.

强制应用初始化，在 Startup  类中seed 方法会被执行。

To force initialization, IIS Express must be stopped and restarted. You can do this with any of the following approaches:

为了强制初始化，iis express 必须重启一下。你可以使用以下方法的任一种来做到：

*  Right click the IIS Express system tray icon in the notification area and tap **Exit** or **Stop Site**

右击系统托盘上通知区域的iis图标，并点击 **Exit** or **Stop Site** 。

![525873-20170814151918881-862547130.png][]

![525873-20170814151943146-413233137.png][]

*  If you were running VS in non-debug mode, press F5 to run in debug mode

如果你运行vs在非调试模式，按F5运行进入调试模式

*  If you were running VS in debug mode, stop the debugger and press F5

如果你运行vs在调试模式，停止调试并按下F5

The app shows the seeded data.

应用显示出初始化后的数据：

![525873-20170814152054537-712545295.png][]

蒙

2017-08-14 15:22 周一

转载于:https://www.cnblogs.com/Meng-NET/p/7358049.html

[Link 1]: http://www.cnblogs.com/Meng-NET/p/8963476.html
[SQL Server Express LocalDB]: https://docs.microsoft.com/zh-cn/aspnet/core/tutorials/first-mvc-app/working-with-sql#sql-server-express-localdb
[Seed the database]: https://docs.microsoft.com/zh-cn/aspnet/core/tutorials/first-mvc-app/working-with-sql#seed-the-database
[Rick Anderson]: https://twitter.com/RickAndMSFT
[Dependency Injection]: https://docs.microsoft.com/zh-cn/aspnet/core/fundamentals/dependency-injection
[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20210725/1a47ede45fa94065898e1cdc710c7850.png
[Configuration]: https://docs.microsoft.com/zh-cn/aspnet/core/fundamentals/configuration
[525873-20170814150835287-633386401.png]: /images/20210725/aeea27414e5542798bcade0a3bd5e498.png
[525873-20170814151032100-896209336.png]: /images/20210725/cf3871a3cb694441a2484ce1e1bc52a8.png
[525873-20170814151100537-614738882.png]: /images/20210725/204d804855054761a3d794e36a369f42.png
[525873-20170814151256584-1483901556.png]: /images/20210725/1aa69d40e6b749ff8947cb0bf913c033.png
[525873-20170814151335100-1761930772.png]: /images/20210725/b15d60570c2e4b2ab23ad83459a54da1.png
[525873-20170814151918881-862547130.png]: /images/20210725/aae93027e9494a75823250ba908aacfd.png
[525873-20170814151943146-413233137.png]: /images/20210725/3e651c4bb2c2486d9dacaa3cdd601a5e.png
[525873-20170814152054537-712545295.png]: /images/20210725/d21b926802ef451fb167e7ef9d8db419.png