C#

How to Setup a Connection with SQL Server Database in Visual Studio

Programmingempire

In this post on How to Setup a Connection with SQL Server Database in Visual Studio, I will demonstrate a step by step procedure for creating an SQL Server Database and set up a connection. Also, I will describe how to retrieve data using a C# application.

Let us start with a console application in C# and all the steps are given below. Specifically, I will use Visual Studio 2019 for creating this application. However, the same procedure is also applicable with earlier versions of Visual Studio.

Set up a Connection with SQL Server Database in Visual Studio

  1. Start Visual Studio and Create a New Project
  2. Now, select a Console App and provide a suitable name.
  3. Once, the project opens, select Server Explorer from the View menu.
  4. Further, click on the server explorer and then right-click on Data Connection and select Add Connection.
  5. With this, you will see a dialog box for choosing the database server. Therefore, you can select SQL Server and name the database. It will ask you for creating a new database. Hence, select yes.
  6. Now, again click on server explorer and right-click on the newly created database, and then select Add Table.
  7. Now, specify a suitable name for the table and add columns. Finally, click on Update to update the database.
  8. Now, refresh the database in server explorer. It will show you the newly created table.
  9. Right-click on the table name and select Show Table Data.
  10. Now you can enter the data in the table. Once done, save the table and close it.
  11. For connecting this database with our C# application, right-click on the database name and select Properties. Copy the value of the ConnectionString property that we will use later in the program.

Example of Creating a Database Application in Visual Studio 2019

Create a New Project
Create a New Project

Establish the Data Connection

Right-Click on Data Connection
Right-Click on Data Connection
Select the Add Connection Option
Select the Add Connection Option

Ensure that the Data Source is Microsoft SQL Server Database File (SqlClient) and enter a new database file name.

Specify Data Source and Database File Name
Specify Data Source and Database File Name

You will get following Dialog Box. Click on Yes and view the newly created database in the Server Explorer.

Create New Database File
Confirmation Dialog Box
Database Created
Database Created

Now expand the MyDb.mdf and right-click on the Tables tab as shown below. Also, select the Add New Table option.

Add a Table to the Database
Add a Table to the Database

Next, change the table name and add columns in the table and save the table. Finally, click on Update as shown below.

Create a Table in the Database
Create a Table in the Database

Click on the Update Database Button.

Click on Update Database
Click on Update Database

Now refresh the Tables tab in the Server Explorer and right-click on the table Student. Further, select the option Show Table Data.

Show Table Data
Show Table Data

Now, enter some data in the table, save the table, and close it.

Enter Data in the Student Table
Enter Data in the Student Table

Now, right click on the database again in Server Explorer and select the Properties option.

View Properties of the Database
View Properties of the Database

Search the ConnectionString property, copy its value and keep it for later use in the application.

Connection String Property
Connection String Property

Now, database connection is complete and we can write the code to access it.

Summary

In this article on How to Setup a Connection with SQL Server Database in Visual Studio, you have learned creating a SQL Server database and connecting it with our application for the purpose of accessing data. Now, we can perform more operations like insert, update, and delete using the same database.


programmingempire

You may also like...