Learn more about the. Net Core Web API Development Series "8": Accessing the Database (basic functionality)

Series Catalogue

Learn more about the. Net Core WEB API Development Series Catalog

The source code involved in this series: Https://github.com/seabluescn/Blog_WebApi

I. Overview

This article discusses how to connect to a database, including connecting to SQL Server and connecting to MySQL, and then doing some basic data manipulation.

Second, connect SQL Server

First add the relevant packages through NuGet:

Create a new entity class:

   Public classProduct {[Key] Public stringCode {Get;Set; }  Public stringName {Get;Set; }  Public stringdescript {Get;Set; }  Public intNumbers {Get;Set; } }

The [key] attribute identifier indicates that the code is the primary key.

Create a new DbContext class

 Public class Salescontext:dbcontext    {        publicgetset;}         protected Override void onconfiguring (dbcontextoptionsbuilder builder)        {            "server=localhost;database=sales; User Id=sales; password=sales2018; " ;            Builder. Usesqlserver (CONNSTR);        }    }

and register the service in startup

 Public class Startup    {               // This method gets called by the runtime. Use this method to add services to the container.         Public void configureservices (iservicecollection services)        {            services. Adddbcontext<SalesContext>();        }    }

Create a new controller, you can do the operation of the database

[Produces ("Application/json")] [Route ("api/products")]     Public classProductscontroller:controller {Private ReadOnlySalescontext _context;  PublicProductsController (Salescontext context) {_context=context; }        /// <summary>        ///Get Product List/// </summary>        /// <returns>Product List</returns>[HttpGet] PublicList<product>getallproducts () {List<Product> products = _context. Products.tolist<product>(); returnProducts ; }      }

The following database and tables are built, for SQL Server, after logging in with Windows Authentication, build the database sales and user sales, with the appropriate permissions set, ensure that SQL Server Management Studio can be Server authentication to log in to the database and can perform data operations.

Database Build table product, field name, type and entity class field name, type consistent:

The properties of all entity classes must have corresponding fields in the database, but database tables can have extra fields.

You should now be able to query the data through http://localhost:5000/api/products.

There are two more questions to take care of:

1. The name of the entity Dbset we want to define in the context is products rather than Product, and we want the corresponding table name in the database to be: Base_product, which is more in line with the custom.

 Public class Salescontext:dbcontext    {        publicgetset;}                protected Override void onmodelcreating (ModelBuilder ModelBuilder)        {            modelbuilder.entity<Product> (). ToTable ("Base_Product");        }    }

2, our data class connection string is written in the code, we need to move it to the configuration file.

The first way to clear or delete DbContext class onconfiguring

protected Override void onconfiguring (dbcontextoptionsbuilder builder)        {                   }

Read the configuration file in startup instead

         Public void configureservices (iservicecollection services)        {             = configuration.getconnectionstring ("sqlserverconnection");            Services. Adddbcontext<SalesContext> (builder=> Builder. Usesqlserver (CONNSTR));        }  

The configuration file Applicatios.json content is as follows:

{  "ConnectionStrings": {    "sqlserverconnection  ""server=localhost;database=sales; User Id=sales; password=sales2018; "  }
}

Third, connect MySQL

1. Add the Package reference:

2. Modify the service registration code as follows

 Public void configureservices (iservicecollection services)        {            = configuration.getconnectionstring ("mysqlconnection");            Services. Adddbcontext<SalesContext> (builder=> Builder. Usemysql (CONNSTR));          }

The configuration file information is as follows:

{"mysqlconnection":"server=58.220.197.198;port=3317;database= Sales;uid=sales;pwd=sales; Sslmode=none; " }

Because SSL connection mode is used by default, if the database is not provided, you need to increase sslmode=none

Iv. some basic operations

    /// <summary>    ///Product Information Interface/// </summary>[Produces ("Application/json")] [Route ("api/products")]     Public classProductscontroller:controller {Private ReadOnlySalescontext _context;  PublicProductsController (Salescontext context) {_context=context; }        /// <summary>        ///Get Product List/// </summary>        /// <returns>Product List</returns>[HttpGet] PublicList<product>getallproducts () {List<Product> products = _context. Products.tolist<product>(); returnProducts ; }        /// <summary>        ///Search product Information by product number (non-fuzzy query)/// </summary>        /// <param name= "code" >Product Code</param>        /// <returns>Product Information</returns>[HttpGet ("{code}")]           PublicProduct Getproductbycode (String code) {Console.WriteLine ($"Getproductbycode:code={code}"); Product Product=_context.            Products.find (code); returnproduct; }           /// <summary>        ///New Products/// </summary>        /// <param name= "Product" >Product Information</param>[HttpPost] Public stringaddproduct ([frombody]product Product) {if(product==NULL) {Console.WriteLine ("ADD Product:null"); return NULL; } Console.WriteLine ($"Add Product: {product. Name}"); _context.            Products.add (product); _context.            SaveChanges (); return "Success"; }                /// <summary>        ///Delete a product/// </summary>        /// <param name= "code" >Coding</param>[Httpdelete ("{code}")]         Public voidDelete (stringcode) {Console.WriteLine ($"Delete Product:code={code}"); Product Product=_context.             Products.find (code); if(Product! =NULL) {_context.                Products.remove (product); _context.            SaveChanges (); }                       return; }        /// <summary>        ///Update product Information/// </summary>        /// <param name= "code" >Product Code</param>        /// <param name= "Newproduct" >Product Information</param>[Httpput ("{code}")]         Public voidUpdate (String code, [Frombody]product newproduct) {Console.WriteLine ($"Change product ({code}): Name={newproduct. Name}"); Product Product=_context.            Products.find (code); Product. Name=newproduct.            Name; _context.            SaveChanges (); return; }    }

V. About Codefirst and Dbfirst

Many tutorials refer to the operation of Codefirst and Dbfirst, but I do not encounter the actual use of the application scenario, normal project development is generally built some tables to write some code, and then build some tables to write some code, and so on, and so on, very few projects are really finished building the table and then write code.

Learn more about the. Net Core Web API Development Series "8": Accessing the Database (basic functionality)