Exploring Visual Studio 2. Database Tools. Visual Studio has long been recognized as the premier tool for developing SQL Server database applications. However, Microsoft has been steadily improving the database tools that make. Visual Studio an effective development tool for DBAs and database developers alike. I’ll take you on a quick tour of the most important new database tools found in the Visual Studio 2. Premium and Ultimate editions. These tools let you navigate databases, create database projects, compare database schema and data, design tables, design queries, and write T- SQL scripts. Related: Top New Features in Visual Studio 2. Navigating Databases Like SQL Server Management Studio (SSMS), Visual Studio 2. Server Explorer lets you browse and navigate through different databases and database objects. You launch Server Explorer by selecting the Server Explorer option on the View menu, right- clicking Data Connections, and selecting Add Connection. In the Add Connection dialog box, you fill in your SQL Server system’s name, add the required authentication information and database name, then click OK. Using Visual Studio 2010 to debug SQL Server stored procedures is a feature well worth exploring if you need to have more insight about how a stored procedure is. Visual Studio has long been recognized as the premier tool for developing SQL Server database applications. However, Microsoft has been steadily improving the. Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of.
This will add a database node to Server Explorer, which allows you to work with diagrams, tables, views, stored procedures, functions, synonyms, types, and assemblies. You can see Server Explorer in the left pane in Figure 1. If you want to browse multiple databases, you need to add multiple connections. To navigate through the hierarchy of SQL Server database objects, simply click the arrow in front of the desired object to drill down into the specific objects. Right- clicking an object in the Server Explorer tree displays a context menu that enables you to act on the selected object. The items in the context menu vary, depending on the object selected. For example, right- clicking a table object displays a context menu that has the following options: Add New Table, Compare Data, Add New Trigger, New Query, Open Table Definition, Copy, Delete, Refresh, and Properties. To create a new SQL Server database project, select New on the File menu and choose the Project option. This will display the New Project dialog box that you see in Figure 2. Expand the node and select SQL Server. You can build database projects for SQL Server 2. R2, SQL Server 2. SQL Server 2. 00. As Figure 2 shows, you can create the following types of database projects with Visual Studio 2. Database projects. Database projects enable you to define the schema of the objects that comprise a given database. This includes database objects such as tables, views, stored procedures, functions, triggers, and indexes. Wizard projects. Wizard projects help you create database projects by stepping you through a set of screens that assist in defining and importing the schema for a specified database. Server projects. Server projects let you specify server- level configuration objects, such as your master database settings, linked servers, and logins. Data- tier applications (DACs). Introduced in SQL Server 2. R2, DACs let you more easily deploy databases and move a database between servers by creating the schema, logins, and server- level objects that compose a database. Visual Studio 2. 01. DAC packages or import the specifications from a SQL script. You can extract and deploy DAC packages from SSMS. SQLCLR database projects. Supported since SQL Server 2. SQLCLR database projects enable you to create SQLCLR objects. SQLCLR must be enabled on the server in order to create this type of project. You can choose to use either Visual Basic (VB) or C# for a project. They also enable you to deploy these objects to a target SQL Server instance. These features were carried over from the discontinued Visual Studio 2. Team Edition for Database Professionals. Comparing database schema. To compare two databases’ schema, right- click the database connection node in Server Explorer and select the Compare Schema option. This brings up the Compare Schema dialog box shown in Figure 3. On the right side, select the database that you want to use as the target for the comparison and any subsequent changes. On the left side, select the source database that you want to compare the target database to. How long the schema comparison takes to run depends on the size of the database. For small databases such as Adventure. Works, it takes less than a minute. The top pane lists and compares the objects found in each database, noting each object’s comparison status. Table 1 shows the possible statuses. You can click the arrow in front of each status to display the fine- grained details about any differences found. However, you can sometimes change the default action, as Table 1 shows. When the update action is set to Skip, a change script isn’t generated. Differences between the source object and the target object are highlighted. Clicking the Refresh Update Script button (the toolbar button with the two green arrows) generates a synchronization script, which you can see in the Schema Update Script pane at the bottom of the window. Clicking Write Updates in the toolbar executes the script. If you want to edit the script before you run it, you can click Export To Editor in the toolbar, which opens the script in Visual Studio 2. T- SQL editor. Comparing database data. Using the Compare Data option is very similar to using the Compare Schema option. To compare the data between two databases, right- click the database connection node in Server Explorer and select the Compare Data option. This brings up the New Data Comparison dialog box shown in Figure 5. You can choose to compare different records, data from the source database only, data from the target database only, or identical records. By default, all of these options are selected. Clicking Next prompts you to select which tables and views to compare. By default, the data comparison selects only tables, so if you want to include views, you must explicitly check the Views option. From there, clicking Finish runs the data comparison and displays the data comparison results, which you can see in Figure 6. For each table or view, the results include a summary of how many rows contain different data, are unique to the source database, are unique to the target database, and contain identical data. Selecting a table or view that has different rows displays the data in the Different Records pane. The key columns are listed, then the columns that differ between the source and target table or view. Data differences are highlighted in bold. Clicking the Refresh Update Script button generates a synchronization script, which you can see in the Data Update Script pane. The comments in the generated script tell you about the order of the actions that will be performed in the data synchronization operation. In this case, the script will first disable all foreign keys, after which it will perform all the deletes. The script will then perform all the updates followed by all of the inserts. Clicking Write Updates in the toolbar executes the script. Much like SSMS’s table designer, Visual Studio 2. Table Designer provides a visual editor in which you enter the table’s column names, data types, and their nullability, as well as define keys. The Table Designer is aware of all the SQL Server 2. ID, varbinary(max), and the new geographic and geometry data types. The Table Designer will open in the Visual Studio editing pane. When you exit the Table Designer, you’ll be prompted to save the table definition, which will result in the creation of the new table in the database defined by your data connection. Visual Studio 2. 01. Query Designer is almost identical to SSMS’s Query Designer. You can run Visual Studio 2. Query Designer by opening Server Explorer, right- clicking the database connection node, and selecting New Query from the context menu. The Query Designer will be displayed in the Visual Studio editing pane. As you visually design the query, the corresponding T- SQL code is interactively displayed in the code pane. You can run the query by right- clicking the designer and selecting Execute SQL. This editor provides T- SQL Intelli. Sense, but it’s more limited than what you might be used to in SSMS. In addition, it has different capabilities, depending on whether you’re in online or offline mode. If you need to connect to a remote SQL Server system during debugging, you need to make sure your firewall has TCP/IP port 1. If you’ve changed the default SQL Server port, you need to use your updated value. In addition, remember that debugging is something that’s best done in the development environment—you shouldn’t make it a practice to debug scripts on your production servers. For example, if you want to use it to create a T- SQL script for a database solution, you can right- click the database solution’s name in Solution Explorer and select Add, Script. The script’s results will be displayed in a pane in the lower portion of the window. In addition to the Execute SQL option, the right- click context menu has options that let you connect to and disconnect from the target database, validate T- SQL code, and display estimated execution plans. In particular, I’ve found the new schema and data comparison features to be a valuable addition to my SQL Server developer tools. They make it easy to find the differences in the different versions of the database that you’ve been developing, testing, and deploying. Microsoft Visual Studio 2. Express - Free downloads and reviews. Microsoft Visual Studio 2. Professional. Write better- quality code, reduce security- related issues, and avoid bugs later in the development lifecycle.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
August 2017
Categories |