none
How to access remote database server from RESTful WEB API in C# ? RRS feed

  • Question

  • Hi,

    I am trying to design RESTful WEB API that can access database (e.g. SQL server) so that I can get data from the database and do some calculations locally and then put the results back to the database.

    I am working in C# VS 2013.

    I found some examples of ASP.NET API at

    http://www.asp.net/web-api/overview/getting-started-with-aspnet-web-api/tutorial-your-first-web-api

    http://www.visualstudio.com/integrate/get-started/rest/basics

    I need to access database (that is located in remote server) from ApiController.

    But, in the exmaples, the database is built locally.

    Also, I need to access the remote database server from a GUI.

    The GUI users should not see any information about login to the database server, such as password and username.

    They can only see the server name and database names.

    Any help would be appreciated.



    Sunday, March 1, 2015 12:16 AM

Answers

  • Hello,

    As far as I know, the WebAPI uses the Entity Framework as its data access layer, so for accessing the remote database in a webapi project, you could check this link:

    https://social.msdn.microsoft.com/Forums/en-US/e80a1707-4977-4bc2-bae4-f51b9fa3fedb/connect-entity-framework-to-remote-server-database-?forum=adodotnetentityframework

    This link above shows you could configure it in the config file.

    Or adding an additional constructor which accepts a string parameter, you then construct the connection string at runtime as:

    string _connString = "metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string="data source=localhost;initial catalog=DATABASE;persist security info=True;user id=sa;password=YourPassword;multipleactiveresultsets=True;App=EntityFramework"";
    
    EntityConnectionStringBuilder ecsb = new EntityConnectionStringBuilder(_connString);
    
    ctx = new Entities(_connString);
    

    By the way, for issues related with WebAPI, it is recommended to post to:

    http://forums.asp.net/1246.aspx/1?Web+API

    The current forum is for .NET Basic Libraries.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 2, 2015 2:23 AM
    Moderator

All replies

  • Hello,

    As far as I know, the WebAPI uses the Entity Framework as its data access layer, so for accessing the remote database in a webapi project, you could check this link:

    https://social.msdn.microsoft.com/Forums/en-US/e80a1707-4977-4bc2-bae4-f51b9fa3fedb/connect-entity-framework-to-remote-server-database-?forum=adodotnetentityframework

    This link above shows you could configure it in the config file.

    Or adding an additional constructor which accepts a string parameter, you then construct the connection string at runtime as:

    string _connString = "metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string="data source=localhost;initial catalog=DATABASE;persist security info=True;user id=sa;password=YourPassword;multipleactiveresultsets=True;App=EntityFramework"";
    
    EntityConnectionStringBuilder ecsb = new EntityConnectionStringBuilder(_connString);
    
    ctx = new Entities(_connString);
    

    By the way, for issues related with WebAPI, it is recommended to post to:

    http://forums.asp.net/1246.aspx/1?Web+API

    The current forum is for .NET Basic Libraries.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 2, 2015 2:23 AM
    Moderator
  • Hi, Fred,

    I am trying to follow the exmaple of building Restful services using asp.net WebAPI 2 and Entity Framework Code first approach.

    http://www.codeproject.com/Articles/821439/Getting-started-with-ASP-Net-Web-API-using-CodeF

    But, I got a problem (my latest post) at :

    https://social.msdn.microsoft.com/Forums/en-US/27127bf6-08e8-4790-98cb-8d78f53e8e0d/error-of-mdf-file-not-able-to-attach-at-runtime-in-visual-studio-2013-win-7?forum=adodotnetentityframework#395a3848-1eae-4002-bec0-6687658f5891

    Any help would be appreciated.

    Thanks


    • Edited by DavidinUSA Monday, March 2, 2015 3:43 PM
    Monday, March 2, 2015 3:26 PM