VS in SQL Server application for Database Project (ii)

In the previous article "VS in SQL Server application for Database project (a)" We've created our project and structure, and now we're going to start coding. In general, when you create a database, you specify a single user for access and action for this data, and we start by creating the user.

To create a user step:

1. Create a SQL Server server-level login account

2. Add a role to the login account

3. Create database-level operations users

4. Add a role to the action user

The specific implementation is as follows:

We have created two new files Demo.login.sql and Demo.user.sql such as.

Seemingly simple operation (not just a new two files), but there is still a need to pay attention to the place. Although our new files are basically script files with the suffix. sql, the file types are actually different.

The file type of the two files we added above is Script (build), so be careful not to choose not in build. What is the difference between a script (build) and a script (not in build)? Literally, it can be seen that a file of type build executes when the deployment is compiled, whereas a file of the not-in build type is not executed when the project is compiled for deployment. We chose the Script (build) type because we wanted to perform the action of creating the user when the database was deployed.

Note: The script file created by the shortcut is by default the type of scripts (not in build).

The DEMO.LOGIN.SQL code is implemented as follows:

/*= = Create login user = = user name: IDemo password: IDemo Default library: Demodb default language: English mandatory password policy: off*/CREATELOGIN IDemo withPASSWORD='IDemo', Default_database=Demodb, Default_language=Us_english, Check_policy= OFFGO/*= = Role Add = = User: IDemo role: SysAdmin*/EXECSp_addsrvrolemember'IDemo','sysadmin'GO

The DEMO.USER.SQL code is implemented as follows:

/*= = Create DATABASE user = = User name: IDemo the user-mapped server login account is: IDemo Default schema: dbo*/CREATE USERIDemo forLOGIN IDemo withDefault_schema=dboGO/*= = Add role = = User: IDemo role: Db_onwer*/EXECsp_addrolemember'db_owner','IDemo'GO

OK, the user created this part of the code we've done, and then we'll create a table. In the Table Directory folder: Right-click Add, enter the name of the tables. Then come out of the interface such as:

The interface of a table is composed of two parts: Design and T-SQL, and the design is divided into tabular field information and other information related to the table (constraints, indexes, triggers, etc.). The new table will only generate a primary key field ID by default. The table in is the way I finished it. You can choose to use only the design interface or the T-SQL interface, I am more accustomed to using the T-SQL interface to operate.

Similarly, you can try new stored procedures, functions, and so on in the corresponding structure directory, of course, stored procedures, functions, etc. are not designed interface, only T-SQL interface. But the system will create a good template code for us, such as when the stored procedure is created, the system automatically generated template code as follows

CREATE PROCEDURE [dbo]. [proc_demotest]    @param1 int = 0 ,     @param2 int  as    SELECT @param1 @param2 RETURN 0

VS SQL Server application in Database Project (ii)