Создаем проект базы данных в Visual Studio 2019
В настоящее время базы данных (БД) являются проблемой для разработчиков, и это одна из самых сложных задач в любом проекте ПО для управления изменениями БД и синхронизации этих изменений. Для решения этой проблемы в Visual Studio имеются проекты баз данных SQL. Можно с легкостью разрабатывать, управлять, сравнивать и развертывать изменения базы данных с помощью Visual Studio.
А также можем отслеживать изменения всех объектов БД через систему контроля версий. Visual Studio Database Project предоставляет гибкие возможности для создания нового проекта базы данных из существующей БД с помощью нажатия на кнопку или возможность создания проекта базы данных с нуля.
Итак, поехали
Запускаем Visual Studio 2019, выбираем Create a new project, потом выбираем SQL Server Database Project.
Введем название базы данных и выберем место расположения файла.
После чего щелкаем по кнопке Create. А теперь правой кнопкой мыши щелкаем по проекту SampleDB и из появившейся меню выбираем Add > Table.
Присваиваем имя таблице – “Student” и щелкаем по кнопке Add.
После того, как добавили таблицу, появится окно конструктора таблицы. Здесь мы можем добавлять столбцы с типом данных.
Добавим несколько столбцов в эту таблицу. Мы увидим, что сгенерируется скрипт в нижней части окна и сохраняется схема таблицы.
Теперь пора опубликовать базу данных в SQL Server
Щелкаем правой кнопкой мыши в окне Solution Explorer по проекту базы данных. В появившемся меню выбираем Properties. В параметрах проекта выбираем версию SQL Server.
Кроме того, мы можем изменять подключение по умолчанию на вкладке Debug Щелкаем по кнопке Edit.
Изменяем свойства соединения в соответствии с параметрами ПК. Введем имя сервера, имя пользователя, пароль и выберем базу данных. Потом щелкаем по кнопке OK. Если есть проверка подлинности Windows, то не нужно указывать имя пользователя и пароль. Нам нужно только имя сервера и база данных.
Не забываем сохранять все изменения. А теперь щелкаем правой кнопкой мыши на проекте БД и выбираем Publish… Редактируем настройки Target Database.
Введем свойства подключения, такие как – если это проверка подлинности SQL Server, то указываем имя сервера, имя пользователя, пароль и выбираем базу; а если это аутентификация Windows, то указываем имя сервера и базу данных.
После чего мы жмем на кнопку Publish. Немного подождем, пока БД не будет опубликована.
Теперь запускаем SQL Server Management Studio и проверяем, есть ли база данных SampleDB или нет.
Вывод
В этой статье мы узнали, как создать проект базы данных в Visual Studio 2019 и публиковать ту же БД в SQL Server 2017.
How to start creating a C# application with SQL Server Database [PART 1]
Applications 
In this post i will demonstrate how to start creating a C# application with SQL Server Database. For this you will need Visual Studio and SQL Server. For both programs there are free editions available on the Microsoft websites:
- Visual Studio Community 2015: https://www.visualstudio.com/
- SQL Server Express Edition: https://www.microsoft.com/en-us/server-cloud/products/sql-server-editions/sql-server-express.aspx
For learning purposes we will create a very simple application: the users will input Name, no. of Extra-Hours worked and Date into a Form. The Hours field will accept only integer values which are not greater than 8. Then, they will press the Save button and the data will be inserted into a SQL Server Database Table.
Step 1: Create the SQL Server Database and the Table:
- Open and login to SQL Server, then from the left menu, right click on the “Databases” and click “Create Database”. Then name it: “Test_app”
- Create a new Table named “ExtraHours” into the newly created database with the following query:
Step 2: Create in Visual Studio a new Windows Form Application and a Form with 4 controls:
- Open Visual Studio, select File–> New Project–> Windows Forms Application:
- In the Form1.cs [Design] file create into the Form the 4 controls: A) Textbox “txtName” for name input, B) Textbox “txtHours” for hours input, C) DateTimePicker “dateOverTime” for selecting the date when the extra-hours occured, D) button1 labeled “Save Record” for saving the record to the database:
Step 3: Create C# Code for Connection to SQL Server + checking and adding the data into the database:
- In the Form1.cs file insert the following C# code:
Step 4: Run the Windows Forms application and check if the data is correctly inserted into the SQL Server Database:
- If the user tries to insert into “Hours” not integer values or integer values which are greater than 8, the data is not inserted to the database:
- If else, the data is inserted to database after the user presses the “Save Record” button:
- Check in SQL Server the inserted data:
This was a very simple example of how to combine Microsoft Visual Studio, C# and SQL Server for application development.
–> In PART 2 of this post I present how we can visualize the records from the SQL Database in the Windows Forms interface.
Create Your First Visual Studio Database Project
Databases should be under source control and have the ability for CI/CD just like application code. Visual Studio gives you this capability. Here, we will show you how to get your databases into a Visual Studio Database Project.
Download Visual Studio
If you don’t have Visual Studio, download it from here.
During the installation, you will be asked to choose your Workload(s). You need to check Data storage and processing. You can also check others if you want to give them a try or add them later.
Create a New Project
Launch Visual Studio and select Create a new project.
Search for database and select SQL Server Database Project. Click Next.
Enter the name of the project. The Solution Name defaults to Project Name. For this example, we will use Adventure Works and make the Solution Name generic for when we add additional databases down the road. Set the location to your preference.
When Visual Studio opens, you will see the Solution Explorer window. The Solution will have one project, Adventure Works. Right-click on the project and select Import, then Database.
Enter the connection information and uncheck Import referenced logins. Logins will have different permissions in UAT and Production and we don’t want to increase or decrease permissions unintentionally.
After the Import is complete, you will see folders for each schema and a few others for administration. Example:
Managing Development
You can make changes within Visual Studio or in the database and sync the changes. If you are working with tables or making global searches or changes, I would start with Visual Studio. For code development, you may prefer to work in the database and sync views, stored procedures, etc., when you have working code.
Here is an example of editing a table in Visual Studio. It has a Design pane and a T-SQL pane. Updates in one pane are automatically reflected in the other pane. So you work where it is more comfortable.
After you do some development in the database, you must pull your changes into Visual Studio. I created a stored procedure template to illustrate the process.
To pull the changes into Visual Studio, go to Tools, SQL Server, New Schema Comparison.
Click on the Select Source dropdown and enter your database connection. After you create a new connection it will be saved for future use.
For the target, we will select the project.
After the source and targets are entered, click Compare.
When the comparison is done, and we go into the Results pane, we will see a list of Add, Changed, and Deleted objects between the two. The Object Definitions pane will show the source code differences. If you split the window, you can see both and scroll through the objects that will be updated.
If you do not want to push any object to the target, you can uncheck the box next to the plus sign. Group highlighting and excluding works as expected.
We can now click Update to push the changes to our project. Confirm the update and the changes are applied to your project.
Publish Changes to a Database
The Publish process will do a Build of the project to validate and create a dacpac (a self-contained Data-Tier Application package used for deployments) file for processing.
The Build process will highlight warnings and errors. You can also suppress warnings. Most warnings are based on Best Practices as determined by Microsoft. Errors must be fixed because they will not execute. To show a warning, I changed the case of a column in a view. Let’s see the Build process.
Right-click on the Project and select Build.
The output shows the build succeeded, but there were warnings.
If you want to clean up the code, go to the error pane and double-click the warning. The source file will open in the editor and highlight the location of the warning.
You can suppress these warnings under Project Properties. Enter the warning number without the SQL prefix in the Suppression box.
To push the changes to a database, right-click the project and select publish. For this example, we will publish to a QA database that was created on the server and does not contain any object yet.
After we enter in the connection, we can generate the script to preview what will be happening. This is also helpful in debugging when this gets complicated and you may need pre and post-deployment scripts. For a simple change, we can click publish.
Name already in use
visualstudio-docs / docs / data-tools / create-a-sql-database-by-using-a-designer.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
Create a database and add tables in Visual Studio
You can use Visual Studio to create and update a local database file in SQL Server Express LocalDB. You can also create a database by executing Transact-SQL statements in the SQL Server Object Explorer tool window in Visual Studio. In this topic, you create an .mdf file and add tables and keys by using the Table Designer.
To complete this walkthrough, you need the .NET desktop development and Data storage and processing workloads installed in Visual Studio. To install them, open Visual Studio Installer and choose Modify (or More > Modify) next to the version of Visual Studio you want to modify.
[!NOTE] The procedures in this article apply only to .NET Framework Windows Forms projects, not to .NET Core Windows Forms projects.
Create a project and a local database file
Create a new Windows Forms App (.NET Framework) project and name it SampleDatabaseWalkthrough.
On the menu bar, select Project > Add New Item. If you see a small dialog box with a box for a filename, choose Show All Templates.
In the list of item templates, scroll down and select Service-based Database.
. moniker range=»>=vs-2022″ 
. moniker-end . moniker range=»<=vs-2019″ 
. moniker-end
Name the database SampleDatabase.mdf, and then click Add.
Add a data source
If the Data Sources window isn’t open, open it by pressing Shift+Alt+D or selecting View > Other Windows > Data Sources on the menu bar.
In the Data Sources window, select Add New Data Source.
. moniker range=»>=vs-2022″ . moniker-end . moniker range=»<=vs-2019″ alt=»Add new data source in Visual Studio» width=»» /> . moniker-end
The Data Source Configuration Wizard opens.
On the Choose a Data Source Type page, choose Database and then choose Next.
On the Choose a Database Model page, choose Next to accept the default (Dataset).
On the Choose Your Data Connection page, select the SampleDatabase.mdf file in the drop-down list, and then choose Next.
On the Save the Connection String to the Application Configuration File page, choose Next.
On the Choose your Database Objects page, you see a message that says the database doesn’t contain any objects. Choose Finish.
View properties of the data connection
You can view the connection string for the SampleDatabase.mdf file by opening the Properties window of the data connection:
Select View > SQL Server Object Explorer to open the SQL Server Object Explorer window. Expand (localdb)\MSSQLLocalDB > Databases, and then right-click on SampleDatabase.mdf (it might be listed as a full path) and select Properties.
Alternatively, you can select View > Server Explorer, if that window isn’t already open. Open the Properties window by expanding the Data Connections node, right-clicking on SampleDatabase.mdf, and then selecting Properties.
[!TIP] If you can’t expand the Data Connections node, or the SampleDatabase.mdf connection is not listed, select the Connect to Database button in the Server Explorer toolbar. In the Add Connection dialog box, make sure that Microsoft SQL Server Database File is selected under Data source, and then browse to and select the SampleDatabase.mdf file. Finish adding the connection by selecting OK.
Create tables and keys by using Table Designer
In this section, you create two tables, a primary key in each table, and a few rows of sample data. you also create a foreign key to specify how records in one table correspond to records in the other table.
Create the Customers table
In Server Explorer or SQL Server Object Browser, expand the Data Connections node, and then expand the SampleDatabase.mdf node.
Right-click on Tables and select Add New Table.
The Table Designer opens and shows a grid with one default row, which represents a single column in the table that you’re creating. By adding rows to the grid, you add columns in the table.
In the grid, add a row for each of the following entries:
Right-click on the CustomerID row, and then select Set Primary Key.
Right-click on the default row ( Id ), and then select Delete.
Name the Customers table by updating the first line in the script pane to match the following sample:
Add an index constraint to the Customers table. Add a comma at the end of the Phone line, then add the following sample before the closing parenthesis:
You should see something like this:
. moniker range=»>=vs-2022″ . moniker-end . moniker range=»<=vs-2019″ alt=»Table Designer with Customers table» width=»» /> . moniker-end
In the upper-left corner of Table Designer, select Update, or press Shift+Alt+U.
In the Preview Database Updates dialog box, select Update Database.
The Customers table is created in the local database file.
Create the Orders table
Add another table, and then add a row for each entry in the following table:
Set OrderID as the primary key, and then delete the default row.
Name the Orders table by updating the first line in the script pane to match the following sample:
Add an index constraint to the Customers table. Add a comma at the end of the OrderQuantity line, then add the following sample before the closing parenthesis:
In the upper-left corner of the Table Designer, select Update, or press Shift+Alt+U..
In the Preview Database Updates dialog box, select Update Database.
The Orders table is created in the local database file. If you expand the Tables node in Server Explorer, you see the two tables:
. moniker range=»>=vs-2022″ . moniker-end . moniker range=»<=vs-2019″ alt=»Tables node expanded in Server Explorer» width=»» /> . moniker-end
If you don’t see it, hit the Refresh toolbar button.
Create a foreign key
In the context pane on the right side of the Table Designer grid for the Orders table, right-click on Foreign Keys and select Add New Foreign Key.
. moniker range=»>=vs-2022″ . moniker-end . moniker range=»<=vs-2019″ alt=»Add a foreign key in Table Designer in Visual Studio» width=»» /> . moniker-end
In the text box that appears, replace the text ToTable with Customers.
In the T-SQL pane, update the last line to match the following sample:
In the upper-left corner of the Table Designer, select Update (Shift+Alt+U).
In the Preview Database Updates dialog box, select Update Database.
The foreign key is created.
Populate the tables with data
In Server Explorer or SQL Server Object Explorer, expand the node for the sample database.
Open the shortcut menu for the Tables node, select Refresh, and then expand the Tables node.
Open the shortcut menu for the Customers table, and then select Show Table Data or View Data.
Add whatever data you want for some customers.
You can specify any five characters you want as the customer IDs, but choose at least one that you can remember for use later in this procedure.
Open the shortcut menu for the Orders table, and then select Show Table Data or View Data.
Add data for some orders. As you enter each row, it’s saved in the database.
[!IMPORTANT] Make sure that all order IDs and order quantities are integers and that each customer ID matches a value that you specified in the CustomerID column of the Customers table.
Congratulations! You now know how to create tables, link them with a foreign key, and add data.