locked
Read data from SQL server view RRS feed

  • Question

  • User-1146848290 posted

    Hi,

    I would like to get data from MS SQL server views and then pass it via REST API. I followed this tutorial to develop the web API (I just changed the server in the web.config) and the http-part works fine but I cannot connect to the server and get access to the database. 
    How does the web-program 'knows' which view to access? How do I get all the data from the views and how do I handle the Data in the program?

    I'm just getting started with ASP.NET...Undecided

    Thanks a lot for your help!  

    Wednesday, June 29, 2016 5:41 PM

All replies

  • User-595703101 posted

    Student is the table that your sample is working with

    You can replace students with your view name and all other fields according to that. On the other hand your view should be an updatable view

    Thursday, June 30, 2016 5:41 AM
  • User1559292362 posted

    Hi adfe,

    I would like to get data from MS SQL server views and then pass it via REST API. I followed this tutorial to develop the web API (I just changed the server in the web.config) and the http-part works fine but I cannot connect to the server and get access to the database. 
    How does the web-program 'knows' which view to access? How do I get all the data from the views and how do I handle the Data in the program?

    According your requirement, I create a demo as below for your reference.

    1. Create a view in your database.

    USE [T_WebApiApp]
    GO
    
    /****** Object: View [dbo].[V-Student] Script Date: 6/30/2016 1:48:38 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    CREATE VIEW [dbo].[V-Student]
    	AS SELECT Id, FirstName, LastName FROM Students
    

    2. Create a model class for the view;

    namespace WebApiWithExistingDbDemo.Models
    {
        public class VStudent 
        {
            public int Id { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
        }
    }

    3.Add the DbSet property in the DbContext class

    using System.Data.Entity;
    
    namespace WebApiWithExistingDbDemo.Models
    {
        public class ApiDbContext : DbContext
        {
            public ApiDbContext() : base("Connection")
            {
    
            }
    
            public DbSet<Student> Students { get; set; }
    
            public DbSet<VStudent> VStudents { get; set; }
    
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Configurations.Add(new StudentMap());
            }
        }
    
    }

    4.Use a StudentMap file to set a different name for the view (using ToTable("v-student"); in the constructor) or to set particular properties

    using System.Data.Entity.ModelConfiguration;
    
    namespace WebApiWithExistingDbDemo.Models
    {
        public class StudentMap : EntityTypeConfiguration<VStudent>
        {
            public StudentMap()
            {
                this.HasKey(t => t.Id);
                this.ToTable("V-Student");
            }
        }
    }

    5. Add the StudentMap file to the modelBuilder, for example ovveriding the OnModelCreating method of the Context:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Configurations.Add(new StudentMap());
            }

    6. Use Migration command init database.

     open visual studio 2015 Tools --> Nuget package manager --> package manager console.  then type the following migration command

    • enable-migrations
    • Add-Migration InitialCreate –IgnoreChanges
    • update-database

      For more information about migration, please refer to:

      https://msdn.microsoft.com/en-us/library/dn579398.aspx?f=255&MSPPError=-2147217396

    7. add the following on your controller

            public IEnumerable<VStudent> GetAllVStudent()
            {
                var list = dbContext.VStudents.ToList();
    
                return list;
            }

    Best regards,

    Cole Wu

    Thursday, June 30, 2016 5:59 AM
  • User-1146848290 posted

    Hi Cole Wu, 

    thanks for your answer. I tried to get it fixed the last days but i still get an error at GetAllStudet():

    System.ArgumentNullException {"Value cannot be null.\r\nParameter name: source"} 

    What did I do wrong?

    Thank you

    Thursday, July 7, 2016 3:40 PM
  • User-2057865890 posted

    Hi Adfe,

    I cannot connect to the server and get access to the database. 

    System.ArgumentNullException {"Value cannot be null.\r\nParameter name: source"} 

    This error often occurs when your connection string is wrong.

    You need something like this:

    <add name="Connection"
          connectionString="Data Source=Server Name;Initial Catalog=DatabaseName;uid=xxxx;password=xxxx"
          providerName="System.Data.SqlClient" />
    </connectionStrings>

    reference: SQL Server Connection Strings for ASP.NET Web Applications

    Best Regards,

    Chris

    Saturday, July 16, 2016 6:12 AM