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.
We have an empty database project.
Step 2: For create the tables Right-click on project and add a new item.
Step 3: Add a new table named Employee.
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.
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.
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)