Working with Databases through Visual Studio

As a software developer, we are focused on writing quality code that can easily be passed to another developer. We’ve read and signed the manifesto for software craftsmanship and are proud to be a polyglot programmer. But the one thing that most developers do is shy away from anything relating to databases. That is the database administrators job right? While you could argue either way, it comes in handy to know several integration points with SQL Server and Visual Studio that could make life easier for you and your DBA colleagues. In this article, we will discuss the tooling that Visual Studio provides for databases and several key features that you should be aware of.

Gathering the Required Tools

Make sure you have the following installed before proceeding:

Declarative Database Development with SQL Server Data Tools

Once you have installed SQL Server and Visual Studio, you can install SQL Server Data Tools which integrates nicely inside of Visual Studio. Using this tool, uou can begin working with SQL Server without ever opening the SQL Server Management Studio which is typically located on a separate server.

Launch Visual Studio and select File-> New File-> SQL File and begin typing in a SQL Query similar to the one shown in Listing 1.

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Listing 1: Creating a Simple Customers Table

You may notice at the bottom of the Visual Studio window that it says “Disconnected”. We need to connect to an instance of our database by clicking on the “Execute” toolbar icon. This will open the “Connect to Server” dialog as shown in Figure 1.

Figure 1: The Microsoft SQL Server 2014 Connection Screen.

Figure 1: The Microsoft SQL Server 2014 Connection Screen.

From here you will need to make sure that the Server name and Authentication method are correct and press the “Connect” button.

Now we are inside our instance of the Database Engine and if you look at the Message log displayed in Visual Studio, it will show the results of our query. In this case, I simply created a table called “CUSTOMERS”.

Still inside of Visual Studio, if we select “View” and click on “SQL Server Object Explorer” then we can connect to our SQL server instance and view the Database structure. This will allow us to access the database. I’ve shown a comparison between Visual Studio and SQL Server Management Studio side-by-side in Figure 2.

Figure 2: Visual Studio and SQL Server Management Studio side-by-side.

Figure 2: Visual Studio and SQL Server Management Studio side-by-side.

If we select the AdventureWorks2012 database and then click “Create New Project,” we can import the database (as shown in Figure 3) into an off-line project.

Figure 3: Importing AdventureWorks2012 database into a Visual Studio project.

Figure 3: Importing AdventureWorks2012 database into a Visual Studio project.

Now that our project has been imported into Visual Studio, we can simply select a .SQL file from our solution explorer and the individual rows and T-SQL will be displayed as shown in Figure 4.

Figure 4 Selecting the EmailAddress.sql file brings up the properties for that table which we can manipulate inside of Visual Studio.

Figure 4 Selecting the EmailAddress.sql file brings up the properties for that table which we can manipulate inside of Visual Studio.

From here, we can place a check mark on the “Allow Nulls” field for ModifiedDate, but no changes will be made to the server until it is ready for deployment. Let’s save the file by pressing Ctrl-S and “Build the Solution” by clicking on Build from the context menu and Build Solution.

If we switch over to our SQL Server Object Explorer and right click our AdventureWorks2012 project, we can compare our local copy against the production copy as shown in Figure 5.

Figure 5: we can see that the only difference is the schema is the ModifiedDate from our local copy to our production copy.

Figure 5: we can see that the only difference is the schema is the ModifiedDate from our local copy to our production copy.

With a simple click of the “Update Target Button” shown beside the Compare button, we can send our changes back to the server. The Data Tools Operation dialog will report back any type of errors or if it was successful or not. You can press “Compare” again to see the changes being reflected on the server. It should now report that “No Differences were detected”.

Code-First Functionality with Entity Framework 6

Developers using Entity Framework in the past have relied on creating core models from existing database schemas. With the introduction of Code-First (which was first introduced in EF 4.3) you can define your model using C# or VB.NET classes to generate a database schema or to map to an existing database. With the recent release of Entity Framework 6.1 you can specify a default connection factory that Code First will use to locate a database for the context. Let’s stop and take a look at a simple example of this integration.

From within Visual Studio, create a New Console Application and gave it the name EntityFrameworkCF. After your project has loaded, then
you can install the Entity Framework package by right-clicking on the References folder of your project and selecting ” Manage NuGet Packages” as shown in Figure 6. Select the Install button and the necessary references should be added to your project.

Figure 6: Installing Entity Framework 6 from the NuGet repository.

Figure 6: Installing Entity Framework 6 from the NuGet repository.

Let’s start by examining the App.config file located in the project root directory.

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
    <parameters>
      <parameter value="v12.0" />
    </parameters>
  </defaultConnectionFactory>
  <providers>
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  </providers>
</entityFramework>

By default when you installed the NuGet package a default connection factory was registered that points to either SQL Express or a LocalDB, depending on which one you have installed. Since we want to add our own SQL Server 2014 database, we will make a change to the parameters as shown below:

<parameters>
  <parameter value="v12.0" />
    <parameter value="Data Source=THINKPAD-WIN8; Integrated Security=True; MultipleActiveResultSets=True" />
</parameters>

This will now allow us to tie directly into our SQL Server database engine with just one line of code.

Now that we have the proper app configuration, let’s create a class called Model.cs and copy and paste the following code snippet:

using System.Data.Entity;
using System.ComponentModel.DataAnnotations;

namespace EntityFrameworkCF
{
    class CustomerContext : DbContext
    {
        public DbSet Customers { get; set; }
    }

    public class Customer
    {
        [Key]
        public int Id { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

        public string Address { get; set; }

        public string City { get; set; }

        public string State { get; set; }

        public int Zip { get; set; }
    }
}

This class sets the stage for our Code-First approach. We inherit from DbContext and create a public property called Customers which makes use of DbSet. DbSet represents the collection of all the entities in the context, or that can be queried from the database, of a given type. We then create several common properties found for a typical customer record.

Switch back over to Program.cs and enter the following code:

static void Main(string[] args)
{
    using (var db = new CustomerContext())
    {
        db.Customers.Add(new Customer { FirstName = "Michael", LastName = "Crump", Address = "111 Street", City = "Birmingham", State = "AL", Zip = 35555 });
        db.SaveChanges();

        foreach (var customer in db.Customers)
        {
            Console.WriteLine(customer.FirstName);
            Console.WriteLine(customer.LastName);
            Console.WriteLine(customer.Address);
            Console.WriteLine(customer.City);
            Console.WriteLine(customer.State);
            Console.WriteLine(customer.Zip);
            Console.ReadLine();
        }
    }
}

This simply adds one record and displays it inside of our console application as shown in Figure 7.

Figure 7: our console application just created the table and one row of data using Entity Framework and SQL Server 2014.

Figure 7: our console application just created the table and one row of data using Entity Framework and SQL Server 2014.

Again, without leaving Visual Studio, open the SQL Server Object Explorer and find that it created a Database called “EntityFrameworkCF.CustomerContext” that matches our project name and Class name. If we expand the tables then we can see that our Customers table has been added and our row of data is included as shown in Figure 8.

Figure 8: viewing the database and tableour Code-First console application created in SQL Server 2014.

Figure 8: viewing the database and tableour Code-First console application created in SQL Server 2014.

I’ve barely scratched the surface with the sorts of things you can do with Entity Framework. There is lots more to be discovered like Migrations, Data Motion, etc. You can read more about Entity Framework here or if you prefer you can use Telerik’s own Data Access which support SQL Server 2014 amongst other SQL versions.

Use Backend Services in the Telerik Platform in SQL Server

Since we are already working with SQL Server, you will be pleased to find out that the Telerik Platform has built-in support for MS SQL Server through Data Connectors. This will allow us to use our backend services to talk directly to an existing table or view of our existing database. If we log into the Telerik Platform and select Backend Services -> Content Types, then we will see the option to “Create Type from a Data Connector” as shown in Figure 9.

But before we do that, we must setup a Data Link Server that is hosted in IIS and has network access to your local database.

Figure 9: an example of the "Create Data Connector" screen.

Figure 9: an example of the “Create Data Connector” screen.

From this screen, we simply give it a name, set the type to MS SQL and provide the Data Link Server URL and connection string. We can test our connection before saving the data connector. We can then switch back to Content Types and see the type name, number of items and storage type as shown in Figure 10. It is worth noting that this data is not stored on our backend services, but on your database instances.

Figure 10: an example of creating types with the Telerik Platform from a SQL Server 2014 database.

Figure 10: an example of creating types with the Telerik Platform from a SQL Server 2014 database.

Conclusion

We have looked at several integration points with SQL Server, Visual Studio and the Telerik Platform today. To recap each section:

  • The support provided by SQL Server Data Tools should help developers to take a quick peek or modify data in a database without logging onto another server and using SQL Server Management Studio.
  • If you aren’t using Code-First development with Entity Framework 6, then you are definitely missing out on a new way to define your “model first” then use it to generate your database schema.
  • Fully utilize the power of data connectors with backend services, which is part of the Telerik Platform. With this capability the data is stored on your local machine and we provide a way to access it through the backend services API. Not only do we support SQL Server, but we support MySQL, Oracle, PostgreSQL as well. You may also use transport-level security or server-to-server authentication to secure your app.

See databases aren’t that scary after all! With the support and tooling from Microsoft and Telerik, it makes us thankful that we aren’t doing this anymore.

As always, if you have any questions or comments, then please leave them below.

Comments