Connecting to SQL server from Restful C# service

Question

I am trying to develop a restful C# service with a connection to Microsoft SQL database. From what I understand, I am supposed to do the connection to the SQL server inside the following code:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace Webserver.Models
{
    public class WebserverContext : DbContext
    {


        public WebserverContext() : base("name=WebserverContext")
        {

        }

        public System.Data.Entity.DbSet<Webserver.Models.ApiModels.SecondlyReading> SecondlyReadings { get; set; }
    }
}

The issue that I am facing is that I am not sure how to do it. I have done db connection in C# ASP.NET before and the db connection is something like:

SqlConnection conn=new SqlConnection("Data source=localhost;"+"Initial Catalog=authors;user=myuser;password=mypassword")

I tried implementing it but it does not work. I have also referenced from the following link for db connection in C# Restful Service:

Link

Would appreciate any help.

Hi gram. It's hard to help you without some code that reproduces the problem and a decent description of what the error is. How to Ask — John Wu
Hi @JohnWu, my main issue is that I am not sure how to connect to Mircrosoft SQL server for Restful web service. — gram95
Maybe start by looking for a tutorial on using Entity Framework. It's very different than what you might be used to if you've worked with System.Data.SqlClient in ADO.NET. — Biscuits
I see, I'll try to look up further on that. Thanks @Biscuits — gram95
Btw., you can still use SqlConnection and SqlCommand in ADO.NET (or any other data access mechanism, for that matter) to build a RESTful service, if you prefer. This won't somehow make your service become less RESTful or anything. :) — Biscuits

Phil.Wheeler Phil.Wheeler · Accepted Answer · 2017-08-08T03:59:22

This may be a helpful start: https://msdn.microsoft.com/en-us/library/jj729737%28v=vs.113%29.aspx?f=255&MSPPError=-2147217396 but probably the step you are missing is having the connection string in your Web.Config.

ASP.Net Entity Framework will try to connect to a database instance whose connection string name in Web.Config matches the name of your context (e.g. WebserverContext, in your example above).

In your Web.Config, your ConnectionStrings element will have something along the following lines:

  <connectionStrings>
    <add name="WebserverContext" connectionString="Data Source=localhost;Initial Catalog=myWebserverContextDb;User ID=someuser;Password=somepassword" providerName="System.Data.SqlClient" />
  </connectionStrings>

Your DbContext class constructor needs to have that same name set so that it can look the connection string up. This tutorial might also help: https://www.tutorialspoint.com/entity_framework/entity_framework_dbcontext.htm

Connecting to SQL server from Restful C# service

1 Answers