Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "how Entity Framework uses Code First mode to manage stored procedures". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "how Entity Framework uses Code First mode to manage stored procedures" can help you solve the problem.

Using stored procedures in EF is very similar to using views, generally using two methods on Database objects: SqlQuery and ExecuteSqlCommand. In order to read many rows of data from the stored procedure, we only need to define a class, and we will materialize all the retrieved rows into the collection of instances of that class. For example, read data from the following stored procedure:

CREATE PROCEDURE [dbo]. [SelectBooks] @ BookTypeName AS NVARCHAR (10) ASBEGINselect B.NameMagol B.AuthorMagneB.PublicationDaterecovery.BookTypeName from Books as Bjoin BookTypes as T on B.BookTypeId=T.BookTypeId where T.BookTypeName=@BookTypeNameEND1, define entity class

The Book entity class is defined as follows:

Using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace CodeFirstProcedureApp.Model {public class Book {public int Id {get; set;} public string Name {get; set;} public string Author {get; set;} public DateTime PublicationDate {get; set;} public virtual BookType BookType {get; set;}

The BookType entity class is defined as follows:

Using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace CodeFirstProcedureApp.Model {public class BookType {public BookType () {Books = new HashSet ();} public int BookTypeId {get; set;} public string BookTypeName {get; set;} public virtual ICollection Books {get; set 2. Define the entity class using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace CodeFirstProcedureApp.Model {public class BookFromProcedure {get; set;} public string Author {get; set;} public DateTime PublicationDate {get; set;} public string BookTypeName {get; set;}} that matches the result of the stored procedure

Note: the property name of the class must be the same as the column name defined in the stored procedure.

3. Create the seed initializer class using CodeFirstProcedureApp.Model;using System;using System.Collections.Generic;using System.Data.Entity;using System.Linq;using System.Text;using System.Threading.Tasks Namespace CodeFirstProcedureApp.EF {public class Initializer: DropCreateDatabaseIfModelChanges {protected override void Seed (EFDbContext context) {/ / create initialization data BookType bookType = new BookType () {BookTypeName = "literary novel", Books = new List {new Book () {Name= "human disqualification" Author= "Tai Jai Zhi", PublicationDate=DateTime.Parse ("2015-08-01")}, new Book () {Name= "worry-relieving grocery store", Author= "Toano Guiwo", PublicationDate=DateTime.Parse ("2014-05-01")}, new Book () {Name= "Kite Runner", Author= "Khaled Hosseni", PublicationDate=DateTime.Parse ("2006-08-01")} New Book () {Name= "A hundred years of Solitude", Author= "Garcia M á rquez", PublicationDate=DateTime.Parse ("2011-06-01")}, new Book () {Name= "Cholera Love", Author= "Garcia M á rquez", PublicationDate=DateTime.Parse ("2015-06-01")} BookType bookType2 = new BookType () {BookTypeName = "Science", Books = new List {new Book () {Name= "A brief History of humanity", Author= "Uval Herrari", PublicationDate=DateTime.Parse ("2017-01-01")} Context.BookTypes.Add (bookType); context.BookTypes.Add (bookType2); base.Seed (context);} 4, define the data context class using CodeFirstProcedureApp.Model;using System;using System.Collections.Generic;using System.Data.Entity;using System.Linq;using System.Text;using System.Threading.Tasks Namespace CodeFirstProcedureApp.EF {public class EFDbContext: DbContext {public EFDbContext (): base ("name=AppConnection") {Database.SetInitializer (new Initializer ());} / / add public DbSet Books {get; set;} public DbSet BookTypes {get; set to the data context } protected override void OnModelCreating (DbModelBuilder modelBuilder) {/ / configure table name and primary key modelBuilder.Entity (). ToTable ("Books") .HasKey (p = > p.Id); modelBuilder.Entity () .ToTable ("BookTypes") .HasKey (p = > p.BookTypeId) / / set entity relations / / BookType and Books one-to-many foreign keys: BookTypeId modelBuilder.Entity (). HasMany (p = > p.Books) .WithRequired (t = > t.BookType) .Map (m = > {m.MapKey ("BookTypeId");}); base.OnModelCreating (modelBuilder) 5. Run the program

Use SQL statements to create stored procedures:

Var createSql = @ "CREATE PROCEDURE [dbo]. [SelectBooks] @ BookTypeName AS NVARCHAR (10) AS BEGIN select B.NameMagery B.AuthorMagneB.PublicationDate T.BookTypeName from Books as B join BookTypes as T on B.BookTypeId=T.BookTypeId where T.BookTypeName=@BookTypeName END " Context.Database.ExecuteSqlCommand (createSql)

View the database:

Call stored procedure to query data

Note: before using a stored procedure, execute the stored procedure in the stored procedure or use the program above to generate the stored procedure.

Var sql = "SelectBooks {0}"; var books = context.Database.SqlQuery (sql, "literary novel"); books.ToList () .ForEach (p = > {Console.WriteLine ("BookName:" + p.Name + "Author:" + p.Author + "BookTypeName:" + p.BookTypeName + "PublicationDate:" + p.PublicationDate);})

In the above code, we specify which class to use to read the results of the query, and when we create the SQL statement, we also provide a formatting placeholder for the parameter of the stored procedure, and when we call SqlQuery, we provide a value for that parameter. If multiple parameters are to be provided, multiple formatting placeholders must be separated by commas, and an array of values must be provided to the SqlQuery. We can also use table-valued functions instead of stored procedures. The result of the stored procedure execution is as follows:

6. Execute stored procedures with no return value

Another use case is if the stored procedure does not have any return value and only executes a command against one or more tables in the database. It doesn't matter how much a stored procedure does, what matters is that it does not return any data. For example, the following stored procedure just updates some data:

CREATE PROCEDURE UpdateBooks@name AS NVARCHAR (60), @ id as intAS BEGINUPDATE Books SET Name=@nameWHERE Id=@idEND

To execute the stored procedure in the database and then call it, we use the ExecuteSqlCommand () method. This method returns the number of rows affected by the stored procedure or any other SQL statement. If you are not interested in this return value, you can ignore the return value.

Var sql = "UpdateBooks @ name,@id"; SqlParameter [] para = new SqlParameter [] {new SqlParameter ("@ id", 1D), new SqlParameter ("@ name", "human failure"),}; var book = context.Books.Where (p = > p.Id = = 1); Console.WriteLine ("data before executing stored procedures is:") Foreach (var item in book) {Console.WriteLine (item.Name + "\ t" + item.Author + "\ t" + item.PublicationDate);} var rowsAffected = context.Database.ExecuteSqlCommand (sql, para); Console.WriteLine ("number of rows affected is {0}", rowsAffected); Console.WriteLine ("data after the stored procedure is:"); var books = context.Books.Where (p = > p.Id = = 1) Foreach (var item in books) {Console.WriteLine (item.Name + "\ t" + item.Author + "\ t" + item.PublicationDate);}

The above code provides two parameters for the stored procedure: Name and Id. One thing to note here is that we must pass in the corresponding values exactly in the order they are defined in the stored procedure, and they pass in ExecuteSqlCommand as an array of parameters. The implementation results are as follows:

To a large extent, EF reduces the need for stored procedures. However, there are still many reasons to use them. These include security standards, legacy databases, or efficiency issues. For example, if you need to update thousands of pieces of data in a single operation and then retrieve them through EF; if you update a row each time, and then save those instances, it is inefficient. Finally, the data can be updated even if the stored procedure is called using the SqlQuery () method.

Note: developers can execute any SQL statement by changing the name of the stored procedure in the above SqlQuery or ExecuteSqlCommand method to the SQL statement to be executed.

This is the end of the introduction to "how Entity Framework uses Code First schema to manage stored procedures". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.