locked
How to create a Database with table in WebApi with Code First approach RRS feed

  • Question

  • User-1980778524 posted

    Hi, newBie here, I did the following.

    Problem:
    When I open the SQL Server Object explorer from Menu in VS2017, I didnot find my database call UserRegistryDB.

    Please help.


    1) I have installed SQLServer 2016 EXPRESS

    2)

    a) add EntityFramework to reference in the project.

    b) I have created a Class : UserDBContext.cs  in Model-Folder

    public class UserDBContext : DbContext
     {     
        public DbSet<User> Users { get; set; }

      }

    3) I have added a model class in Model-Folder

     public class User
        {
            public int Id { get; set; }
            public string emailID { get; set; }
            public string AppName { get; set; }
            public string SessionToken { get; set; }

        }


    4) I have added below : in Web.Config

      <connectionStrings>
        <add name="UserDBContext"
         connectionString="server=.; database= UserRegistryDB; integrated security = true;"
         providerName ="System.Data.SqlClient"/>

      </connectionStrings>

    5) Build and run

     no compilation error, build success.

    I need help with the Question:

    What are the differences in ConnectionString that using server =. , server=(local) ? or server=(localdb)

    a) connectionString="server=. ; database= UserRegistryDB; integrated security = true;"

    b) connectionString="Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=TestDatabase;Integrated Security=True"

    Thanks

    Tuesday, October 10, 2017 6:42 AM

All replies

  • User-1980778524 posted

    Any1 can help? I still dont know why and  how to solve this problem.

    Tuesday, October 10, 2017 1:24 PM
  • User475983607 posted

    The dot and (local) are your machine name.  (localdb) is usually SQL Express.  It depends on how you setup SQL Express and if you installed other versions of SQL.  You can figure out what to have by looking at the SQL services you have running.

    https://technet.microsoft.com/en-us/library/ms165734(v=sql.90).aspx

    Other than that, I think you should take a look at the Web API code first tutorials for step-by-step assistance. 

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/data/using-web-api-with-entity-framework/part-1

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/getting-started-with-aspnet-web-api/tutorial-your-first-web-api

    Or see the MVC code first tutorials.

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/creating-an-entity-framework-data-model-for-an-asp-net-mvc-application

    Tuesday, October 10, 2017 1:55 PM
  • User-1980778524 posted

    @ mgebhard

    I have read the links you have provided.  I still dont find the solution.  

    I did the following:

    1) in Services:

    I have turned on the SQLEXPRESS  and SQL Server Browser 

    2) In VS Menu : 

    I check on SQL server Object Explorer ---> Nothing 

    I check Server  Explorer--> Nothing 

    3) I check my System, I have the following 

    Microsoft SQL server 2016 (64-bits)

    Microsoft SQL Server 2016 LocalDB

    Microsoft SQL Server 2017 RC1

      

    What else I missed out?

    Thanks

    Tuesday, October 10, 2017 3:22 PM
  • User475983607 posted

    I'm not sure what problem you are trying to solve.   I assume you need to figure out what SQL instance to have running.  Try the follow doc which shows how to browser SQL instances.

    https://msdn.microsoft.com/en-us/library/hh272693(v=vs.103).aspx

    If you are having creating the DB using code first, then it is not clear if enabled or created migrations.  Perhaps you are not on that step yet?  Anyway, the previous docs explain how to enable migrations.

    Tuesday, October 10, 2017 6:27 PM
  • User-1980778524 posted

    I downloaded SQLServer Express 2016 and installed by default installation ( no user's own instance created). 1)I have checked the settings using Mixed Windows and QL Server authentication. 2) Turn on SQLEXPRESS and SQL Server Browser 3) Have enable NamedPipe and TCP/IP.

    So, I would like to know it is EntiryFramework Code First is not working?

    Thanks

    Wednesday, October 11, 2017 1:48 AM
  • User-271186128 posted

    Hi SkyDriving,

    You can try below connection string:

    <add name="UserDBContext" connectionString="Data Source=(localdb)\MSSQLLocalDB; Initial Catalog= UserRegistryDB; Integrated Security=True;" providerName ="System.Data.SqlClient"/>
    

    Then, rebuild and run the application, the result as below:

    If still not working, I suggest you could create a database first, then, refer to the following articles to add SQL DataBase connection from Server Explorer:

    https://msdn.microsoft.com/en-us/library/0wbea1ae.aspx

    And, when using EF code first, you could assign the database connection string. code as below:

        public partial class UserDBContext: DbContext
        {
            public UserDBContext()
                : base("name=UserDBContext") // connection string name
            {
            }
    

    Then, using Migration to generate the data tables.

    Best regards,
    Dillion

    Wednesday, October 11, 2017 9:00 AM
  • User-1980778524 posted

    @Zhi Lv

    I will try your approach soon.

    I have changed to use other method : 1) Create DB and table in SSMS .  

    BTW, Your Image show:  (X ) UserDBContext(APIEF) 

    1) If I use SSMS to view , will this UserRegistryDB as Database  appear there? If It is there, this means this DB is not residing in VS2017, right?

    2) Your Image show (X ) UserDBContext(APIEF), is this DB residing in VS2017 or in MS SQL Server?

    3) Why there is a X ? Is that means I have to  Click to connect the DB? It will Not Connect automatically?

    Thanks

    Wednesday, October 11, 2017 10:19 AM
  • User475983607 posted

    SkyDriving

    3) Why there is a X ? Is that means I have to  Click to connect the DB? It will Not Connect automatically?

    Yes, you must create a connection just like SSMS.  Or you can read my last post which explains how to search you local system for SQL instances.  Sincer you connected with SSMS, simply use the same connection in VS 2017.

    Wednesday, October 11, 2017 11:07 AM
  • User-271186128 posted

    Hi SkyDriving,

    Why there is a X ? Is that means I have to  Click to connect the DB? It will Not Connect automatically

    The "X" means the connect not open, you could right click the connection name and refresh the connection. Then, the result as below:

    After refresh the connection, if the connection still not open, you could refer to my previous reply or mgebhard's post to re-connect the database.

    Besides, I suggest you could also try to add the SQL Server DataBase from Visual Studio 2017. Please refer to the following steps:

    Right click your project--> select "Add"--> select "New Item"--> on the Data tap, select "SQL Server DataBase".

    screenshot as below:

    Then, the connection string in web.config file as below:

      <connectionStrings>
        <add name="ConnectionString" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True"
          providerName="System.Data.SqlClient" />
      </connectionStrings>

    Best regards,
    Dillion

    Thursday, October 12, 2017 7:57 AM