I am trying to connect an Angularjs script with a PostgreSQL database (the resulting HTML has to be able to show the data extracted from the PostgreSQL database). The PostgreSQL database was created locally with the default settings (during installation). I am working in the visual studio environment.
I tried using a similar approach to this (albeit this is meant to connect with a SQL Server rather than to a PostgreSQL database): http://cybarlab.com/crud-operations-in-angularjs-and-web-api
Essentially you define a web.config file with a connection string, create an object class for your database table rows, use update-database command in the Package Manager Console to create the appropriate table in the PostgreSQL database (which worked fine), create a WEB API 2 controller based upon the previously created object class (to get the CRUD operations) and get the data using $http.get function in the angular.js script (also invoking the controller in the html file).
The problem is, I have no idea what to write in the html.get(url) url field. All of my attempts have not been successful, which together with the lack of information on Google for this problem, leads me to believe this approach for PostgreSQL simply doesn't work. So is there any way to make it work? Or is there another way to establish the connection? While the description of the task does not note that the $http.get command should be used, it does note that this should be done using the WEB API 2 controller for the CRUD operations.
The connection string (the update-database command worked so it should be correct):
<connectionStrings>
<add name="TestProjectContext" providerName="Npgsql" connectionString="Server=127.0.0.1;Port=5432;Database=TestProject;User Id=postgres;Password=NotTelling;" />
</connectionStrings>
The class object (a table with these attributes was generated in the PostgreSQL database after using the update-table command):
public class DataDestination
{
[Key]
public Guid Id { get; set; }
public string Server { get; set; }
public string Port { get; set; }
public string Database { get; set; }
public string Username { get; set; }
public string Password { get; set; }
public string SqlType { get; set; }
public string Owner { get; set; }
}
The output table in HTML:
<body data-ng-app="app" data-ng-controller="TestController">
<table style="width:100%" , border="1">
<tr>
<td>Id</td>
<td>Server</td>
<td>Port</td>
<td>Database</td>
<td>Username</td>
<td>Password</td>
<td>SqlType</td>
<td>Owner</td>
</tr>
<tr data-ng-repeat="std in DataDestinations">
<td>
{{std.Id}}
</td>
<td>
{{std.Server}}
</td>
<td>
{{std.Port}}
</td>
<td>
{{std.Database}}
</td>
<td>
{{std.Username}}
</td>
<td>
{{std.Password}}
</td>
<td>
{{std.SqlType}}
</td>
<td>
{{std.Owner}}
</td>
</tr>
</body>
{{error}}
Finally, the angular.js script statement I tried to use (the rest of the script seems to be running fine and the error message is also thrown successfully):
$http.get('http://localhost:5432/TestProject').success(function (data) {
$scope.DataDestinations = data;
})
.error(function () {
$scope.error = "An Error has occured while loading posts!";
});
EDIT: Thank you for your responses. the WEB API 2 controller was created using the Add -> Controller -> WEB API 2 Controller with actions, using Entity Framework -> Choose the previously created class and context. So basically it was generated based on the class. Here is the code:
public class TestController : ApiController
{
private TestProjectContext db = new TestProjectContext();
// GET: api/Test
public IQueryable<DataDestination> GetDataDestinations()
{
return db.DataDestinations;
}
// GET: api/Test/5
[ResponseType(typeof(DataDestination))]
public IHttpActionResult GetDataDestination(Guid id)
{
DataDestination dataDestination = db.DataDestinations.Find(id);
if (dataDestination == null)
{
return NotFound();
}
return Ok(dataDestination);
}
// PUT: api/Test/5
[ResponseType(typeof(void))]
public IHttpActionResult PutDataDestination(Guid id, DataDestination dataDestination)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
if (id != dataDestination.Id)
{
return BadRequest();
}
db.Entry(dataDestination).State = EntityState.Modified;
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
if (!DataDestinationExists(id))
{
return NotFound();
}
else
{
throw;
}
}
return StatusCode(HttpStatusCode.NoContent);
}
// POST: api/Test
[ResponseType(typeof(DataDestination))]
public IHttpActionResult PostDataDestination(DataDestination dataDestination)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
db.DataDestinations.Add(dataDestination);
try
{
db.SaveChanges();
}
catch (DbUpdateException)
{
if (DataDestinationExists(dataDestination.Id))
{
return Conflict();
}
else
{
throw;
}
}
return CreatedAtRoute("DefaultApi", new { id = dataDestination.Id }, dataDestination);
}
// DELETE: api/Test/5
[ResponseType(typeof(DataDestination))]
public IHttpActionResult DeleteDataDestination(Guid id)
{
DataDestination dataDestination = db.DataDestinations.Find(id);
if (dataDestination == null)
{
return NotFound();
}
db.DataDestinations.Remove(dataDestination);
db.SaveChanges();
return Ok(dataDestination);
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
private bool DataDestinationExists(Guid id)
{
return db.DataDestinations.Count(e => e.Id == id) > 0;
}
}
}
I will also post the context for the object just in case that is wrong:
namespace TestProject.Models
{
public class TestProjectContext : DbContext
{
public TestProjectContext()
{
Database.SetInitializer<DbContext>(null);
}
public DbSet<DataDestination> DataDestinations { get; set; }
}
}
EDIT2: Added the CORS package to the project and added it to the beginning of the already defined WEB API controller:
using TestProject.Models;
using System.Web.Http.Cors;
namespace TestProject.Controllers
{
[EnableCors(origins: "http://localhost", headers: "*", methods: "*")]
public class TestController : ApiController
{
private TestProjectContext db = new TestProjectContext();
code is the same as in previous WEB API
Didn't fix the problem in and of itself. The same self defined error is being output - "An Error has occured while loading posts!".
