Recently, we had to retrieve content from an external SQL database and use the content in a component, fair enough. Using Entity Framework, we can connect to the database and generate classes and models for the tables - this of course makes things a lot easier for us.
Doing this is very simple, we first start by creating a connection. Open the Server Explorer, right click on “Data Connections”, and then click on “Add Connection…”:
Change the data source to “Microsoft SQL Server (SqlClient) and then complete the form, which should look like this:
Once you have the connection setup correctly, right click in your project and choose “Add” -> “New Item”, then choose “ADO.NET Entity Data Model”. Choose the Model Contents that you want and continue. Make sure that you set the Entity Container Name to match the “name” field of your connection to the database in your ConnectionStrings.config file.
And that’s it, the wizard will setup the context and the models mapped from the SQL database for you! You can, for example, query the databases using LINQ:
using (var context = new {YourContextContructor}())
{
var result = context.{TableYouWantToQuery}
.Where(x => x.{YourSearchCriteria})
.FirstOrDefault();
}
Leave a comment if you have any questions.