Visual Studio 2012 introduced a new feature called SQL Server Object Explorer. In earlier versions of Visual Studio, we have Server Explorer, which opens up a particular connection and work with a single database data. Now with the new SQL Server Object Explorer, we are able to manipulate multiple databases from a database server.
Now from the visual studio itself, we can create tables, define various keys, and create stored procedures and functions.
Open the SQL Server Object Explorer
Select View -> SQL Server Object Explorer to open the SQL Server Object Explorer.
New SQL Server Object Explorer opens with the SQL Server node.
Add Database Server
Now we need to add our database server to the SQL Server Object Explorer window by right click and select Add SQL Server option.
This will open the Connect to Server window, same as the SQL Server Management server. Specify the Server name and authentication type.
Once connected, new server along with Databases, Security and Server Objects will be displayed in the SQL Server Object Explorer.
Expand the database; in our sample I used the NorthWind database. For creating a new table, right click on the Tables node and select “Add New Table” option.
This will open the table designer window, where we can define the table structure and specify various keys associated with the table. Visual Studio will generate the SQL script corresponding to your table definition. You can see various keys, constraints, indexes and triggers associated with the table.
Once we select the Preview database option, Visual Studio will open the Preview Database Updates window from where we can generate the scripts or can update the database immediately.
Generate Script option will generate the scripts shown in the below screen.
Same way we can create various types of functions by right clicking the Functions node in the tree and selecting the corresponding option.
Open Existing Table
Following screen shows the OrderDetails table, various database objects like keys, constraints, indexes and triggers associated with the OrderDetails table. You can see the SQL script for creating the table and associated constraints below the table definition.
Server Object Explorer is one of the handy tool added in Visual Studio 2012, which will allow the developers to remotely connect to a database and perform all database operations from Visual Studio itself. This avoids the need for installing the SQL Server in developer system.