Create and Insert data in database using Visual Studio 2013

We will create a database with some pre-filled data. We developed application using LocalDB with either Visual Studio 2013 or Visual Studio Express 2013 for Web.

Step1: Open Visual Studio open Other Language the select SQL Server and create a SQL Server Database Project. Name the project OrganizationEmployeeData.

cp1

We have an empty database project.

cp2

Step 2: For create the tables Right-click on project and add a new item.

cp3

Step 3: Add a new table named Employee.

cp4

Step 4: In the table file, replace the T-SQL command with the following code to create the employee table.

CREATE TABLE [dbo].[Employee] (
    [EmployeeID]      INT           IDENTITY (1, 1) NOT NULL,
    [LastName]       NVARCHAR (50) NULL,
    [FirstName]      NVARCHAR (50) NULL,	
    [JoiningDate] DATETIME      NULL,
    PRIMARY KEY CLUSTERED ([EmployeeID] ASC))

Now you can see that design window automatically synchronizes with the code. We can use either the code or designer.

cp5

cp6

We will add another table Department and use the following T-SQL command.

CREATE TABLE [dbo].[Department] (
    [DepartmentID] INT           IDENTITY (1, 1) NOT NULL,
    [Title]    NVARCHAR (50) NULL,
    [Credits]  INT           NULL,
    PRIMARY KEY CLUSTERED ([DepartmentID] ASC)
)

And, create one more table Enrollment.

CREATE TABLE [dbo].[Enrollment] (
    [EnrollmentID] INT IDENTITY (1, 1) NOT NULL,
    [Band]        DECIMAL(3, 2) NULL,
    [DepartmentID]     INT NOT NULL,
    [EmployeeID]    INT NOT NULL,
    PRIMARY KEY CLUSTERED ([EnrollmentID] ASC),
    CONSTRAINT [FK_dbo.Enrollment_dbo.Department_DepartmentID] FOREIGN KEY ([DepartmentID]) 
        REFERENCES [dbo].[Department] ([DepartmentID]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.Enrollment_dbo.Employee_EmployeeID] FOREIGN KEY ([EmployeeID]) 
        REFERENCES [dbo].[Employee] ([EmployeeID]) ON DELETE CASCADE

We will populate database with data through a script that is run after the database is deployed. Add a Post-Deployment Script to the project. We can use the default name.

cp7

Now add the following T-SQL code to the post-deployment script. This script simply adds data to the database when no matching record is found. It does not overwrite or delete any data you may have entered into the database.

MERGE INTO Department AS Target 
USING (VALUES 
        (1, 'Microsoft', 3), 
        (2, 'Java', 3), 
        (3, 'Php', 4)
) 
AS Source (DepartmentID, Title, Credits) 
ON Target.DepartmentID = Source.DepartmentID 
WHEN NOT MATCHED BY TARGET THEN 
INSERT (Title, Credits) 
VALUES (Title, Credits);


MERGE INTO Employee AS Target
USING (VALUES 
        (1, 'Ark', 'Roop', '2013-09-01'), 
        (2, 'Akash', 'Gupta', '2012-01-13'), 
	(3, 'Saurabh', 'Gupta', '2011-09-03')
)
AS Source (EmployeeID, LastName, FirstName, JoiningDate)
ON Target.EmployeeID = Source.EmployeeID
WHEN NOT MATCHED BY TARGET THEN
INSERT (LastName, FirstName, JoiningDate)
VALUES (LastName, FirstName, JoiningDate);


MERGE INTO Enrollment AS Target
USING (VALUES 
	(1, 2.00, 1, 1),
	(2, 3.50, 1, 2),
	(3, 4.00, 2, 3),
	(4, 1.80, 2, 1),
	(5, 3.20, 3, 1),
	(6, 4.00, 3, 2)
)
AS Source (EnrollmentID, Band, DepartmentID, EmployeeID)
ON Target.EnrollmentID = Source.EnrollmentID
WHEN NOT MATCHED BY TARGET THEN
INSERT (Band, DepartmentID, EmployeeID)
VALUES (Band, DepartmentID, EmployeeID);

It is important to note that the post-deployment script is run every time you deploy your database project.
We have 4 SQL script files but no actual tables. In Visual Studio, click the Start button (or F5) to build and deploy your database project to localdb.
To see that the new database has been created, open SQL Server Object Explorer and look for the name of the project in the correct local database server (in this case (localdb)\Projects)

cp8

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>