Creating a Visual Studio Database Project for an existing SQL Server Database

By: Rick Krueger

Learn how SQL Server’s extended events help prevent performance challenges

Problem

Continuous Integration has become standard practice for many development projects. Every time a developer checks in a piece of code, the entire project or solution is built and deployed to an environment to make sure it didn't 'break the build'. Developers have been using source control for a long time, but there just wasn't a good way to get database objects under source control. As a result, there are many applications out there with the 'application code' in source control, but the database code is unmanaged. The preferred route for getting the database into source control at many shops will be Database Projects. If you are in this position, and your shop is using Visual Studio, there is a relatively easy path to get your databases into Database Projects, and ultimately source control. 'Reverse engineering' your database into a Database Project will help you start leveraging the features of Visual Studio Database Projects available in VS Premium and VS Ultimate (including source control, continuous integration, and code analysis).

Solution

We can use Visual Studio to run the Import Database Wizard and populate an empty Database Project.

Download the sample AdventureWorks database (AdventureWorks2008_Database.zip was used for this example) fromCodePlex, and attach the AdventureWorks2008 database to follow along with this tip.

NOTE: The screenshots are from Visual Studio 2010, but the same general process will work with Database Projects in Visual Studio 2005/08. Visual Studio 2005/08/10 all use VSDBCMD.exe for deploying Database Projects, while 2012 went to a new method for deployment.

Create an Empty Database Project

1. Open Visual Studio 2010.
2. From the File Menu, select New > Project...
3. In the Installed Templates tab, expand Database > SQL Server > Advanced
4. Select the SQL Server 2008 Database Project, and enter the Name of your database
   
   
   
5. Click OK and an empty Database Project will be created.

Run the Import Database Wizard

1. From the Project Menu, select Import Database Objects and Settings...
2. Click New Connection...
3. Select your local Server name, credentials, and database name. TIP: Click Test Connection here before clickingOK.
   
   
   
4. Click Start. I recommend keeping the provided defaults, unless you understand the implications of not accepting the defaults.
5. You will see a progress bar as Visual Studio inspects the selected database, and populates the Database Project with all of the database objects.
6. Click Finish.
   
   
   

Next Steps

Congratulations! Your database has been imported into a Database Project, and you should now start managing your database from Visual Studio and make sure you get source control set up. You will also want to learn how to deploythose changes from Visual Studio to SQL Server. I have been using Database Projects for a long time now, and I haven't come across many limitations. There are times I still prefer to write my code in SQL Server Management Studio, and then import the code into Visual Studio, but that is mostly a personal preference.

• Learn more about Visual Studio Database Projects
• Check out Adding Tables To A Database Project
• Visit Adding Additional Objects To A Database Project
• Use the MergeScripts Extension to organize and consolidate table scripts

select, insert, update, delete statements using Store Procedure in SQLServer

Here, we will see how to create select, insert, update, delete statements using stored procedure. Let's take a look at a practical example. We create a table.

Creating Table

CREATE TABLE employee(

    id          INTEGER NOT NULL PRIMARY KEY,

    first_name  VARCHAR(10),

    last_name   VARCHAR(10),

    salary      DECIMAL(10,2),

    city        VARCHAR(20),   

 )

Now insert some values in the table and using select statement to select a table.

 INSERT INTO employee VALUES (2, 'Monu',  'Rathor',4789,'Agra');

 GO

 INSERT INTO employee VALUES (4, 'Rahul' ,  'Saxena',   5567,'London');

 GO

 INSERT INTO employee VALUES (5, 'prabhat',  'kumar',  4467,'Bombay');

 go

 INSERT INTO employee VALUES (6, 'ramu',  'kksingh',  3456, 'jk');

 go

 select * from employee

Table looks like this.

Figure 1

Stored procedure for Select, insert, update, delete

Here, we create a stored procedure for select,insert,update,delete statements to select the data from the table.

Alter PROCEDURE MasterInsertUpdateDelete

(

    @id         INTEGER,

    @first_name  VARCHAR(10),

    @last_name   VARCHAR(10),

    @salary      DECIMAL(10,2),

    @city        VARCHAR(20), 

    @StatementType nvarchar(20) = ''

)

AS

BEGIN

IF @StatementType = 'Insert'

BEGIN

insert into employee (id,first_name,last_name,salary,city) values( @id, @first_name,  @last_name,  @salary, @city)   

END

IF @StatementType = 'Select'

BEGIN

select * from employee

END 

IF @StatementType = 'Update'

BEGIN

UPDATE employee SET

            First_name =  @first_name, last_name = @last_name, salary = @salary,

            city = @city

      WHERE id = @id

END

else IF @StatementType = 'Delete'

BEGIN

DELETE FROM employee WHERE id = @id

END

end

Now press F5 to execute the stored procedure.

Now open object explorer and select storeprocedure MasterInsertUpdateDelete.

Stored Procedure to Check Insert

StatementType = 'Insert'

MasterInsertUpdateDelete -> right click select execute stored procedure...

Figure2

Execute procedure window will be open.

Figure3

Now for insert we fill the data in required field.

StatementType=insert

Figure4

Click on the ok Button. and check in the employee table with following inserted data.

Figure5

Stored Procedure to Check update

MasterInsertUpdateDelete -> right click select execute stored procedure...

Execute procedure window will be open.

StatementType = 'Update'

Figure6

Click on the ok Button. and check in the employee table with following updated data where id is 7.

Figure7

Stored Procedure to Check Delete

MasterInsertUpdateDelete -> right click select execute stored procedure...

Execute procedure window will be open.

StatementType = 'Delete'

Figure8

we delete record from table which has id=2

Click on the ok Button. and check in the employee table with following deleted data where id is 2.

SQL Server and Oracle datatypes Comparison

The following summarizes the datatype of SQL Server and Oracle :

SQL Server	Oracle
Exact Numerics
TINYINT	NUMBER(3)
SMALLINT	NUMBER(5)
INTEGER	NUMBER(10)
BIGINT	NUMBER(19)
DECIMAL(p,s)	NUMBER(p,s)
NUMERIC(p,s)	NUMBER(p,s)
SMALLMONEY	NUMBER(10,4)
MONEY	NUMBER(19,4)
Approximate Numerics
REAL	BINARY_FLOAT
FLOAT	BINARY_DOUBLE
Date Time
SMALLDATETIME	TIMESTAMP(3)
DATETIME	TIMESTAMP(3)
DATETIME2(fs)	TIMESTAMP(fs)
DATETIMEOFFSET(fs)	TIMESTAMP (fs) WITH TIME ZONE
DATETIMEOFFSET(fs)	TIMESTAMP (fs) WITH LOCAL TIME ZONE
Character strings
CHAR(x)	CHAR(x)
VARCHAR(x)	ARCHAR2(x)
VARCHAR(MAX)	CLOB
TEXT	LONG
Binary strings
BINARY(n)	RAW(n)
VARBINARY(n)	LONG RAW
VARBINARY(MAX)	LONG RAW or BLOB
IMAGE	LONG RAW
Binary strings
XML	XMLTYPE
BIT	NUMBER(1)
TIMESTAMP	ORA_ROWSCN pseudo column
UNIQUEIDENTIFIER	RAW(16)
N/A	BFILE