How to use C# with SQL
In this post we’ll look at how to use C# with SQL. In this tutorial, we will discuss the process for creating a very simple C# console application that connects to a SQL Server database. You will learn the most basic steps needed to use C# with SQL. You will see how we can gather some basic information from the user and use it to run T-SQL commands behind the scenes to retrieve or update data in the database.
When it comes to SQL Server databases, I would say most of the time, your end users will NOT be running queries or updating data directly within SQL Server Management Studio. They will most likely be using a user-friendly software application that ultimately accesses the database and does the data retrieval or updating behind the scenes.
If you’re new to SQL, don’t forget to check out our article to Learn SQL Server.
We will discuss the following topics in this tutorial:
Install Visual Studio 2019 Community and create an empty C# project
We will be using the latest-and-greatest free version of Microsoft Visual Studio to create our simple C# console application: Visual Studio 2019 Community.
In previous years, the free edition of Visual Studio has been called ‘Express‘. Recently, Microsoft decided to rename their free edition to ‘Community‘. So understand they are one-in-the-same.
Here is what the download page looks like:
Just click the Download Community 2019 link to start the download. This will download an .exe file starting with the name “vs_community“.
Installing Visual Studio Community 2019
Once that executable finishes downloading, double click on it to start the installation.
You’ll first see the Visual Studio Installer start up:
When it’s loaded, you’ll need to specify what “workload” you want to initially install with Visual Studio. You’ll pick .NET desktop development. The C# language , in case you didn’t know, is a .NET language:
Then, the installation process will begin!:
Notice the “Start after installation” checkbox is enabled. This will start Visual Studio Community automatically after the installation process is complete.
Setting up a C# project
Before we create our project, the first question we’re asked is the color theme we want Visual Studio to use. I like the regular Blue theme, but many people like the Dark theme. Pick the one you think you’ll like!:
Then click Start Visual Studio.
You’ll be presented with a Get Started screen, where you’ll want to select Create a new project:
On the “Create a new Project” screen, we’re going to pick the Console Application option that outlines the C# language, as shown here:
Then click Next.
We’re almost there. The next step is to name our C# project. Let’s call it SimpleSQLProject:
When you outline a Project Name, the Solution Name also updates. You can change it if you want to.
Also, the Location is given a default path. Again, you can change that if you want to. I’m just going to leave it alone.
Then, hit Next!
It will quickly ask you what version of .NET you want to use. Go ahead and pick .NET 5.0 if it’s available, then click Create.
That’s it! We have an empty project ready to use C# with SQL:
Gotta love the classic “Hello world” console message. These are every programmer’s first words spoken, you see.
Add the System.Data.SqlClient namespace to your C# project
As I subtly hinted at earlier, the key to linking a C# application to a SQL Server database is to use the System.Data.SqlClient namespace. This namespace contains all the classes and functions we need to make all our dreams come true.
Let’s add this namespace to our SimpleSQLProject project.
In Visual Studio, navigate to Tools -> NuGet Package Manager -> Manage NuGet Packages for Solution
This will open a new window where you can search for the System.Data.SqlClient namespace:
Here’s a rundown on what to do on this page:
- Make sure you are on the Browse tab, then type the name of the namespace we want, System.Data.SqlClient. It should appear as the first result.
- Enable the checkbox next to the project you want to add the namespace to (we only have one project, SimpleSQLProject).
- Click Install
Important! Don’t skip this step, or else you can use C# with SQL. The System.Data.SqlClient reference is crucial!
IF YOU DON’T GET ANY RESULTS WHEN SEARCHING FOR THE System.Data.SqlClient NAMESPACE:
You might need to update the web location where the namespaces are searched. Navigate to Tools -> NuGet Package Manager -> Package Manager Settings:
In the “Options” window that appears, look in the Package Sources option. Make sure the Source field contains the following URL: https://api.nuget.org/v3/index.json
I encountered this issue and it nearly drove me crazy. Hopefully I just saved you a couple hours’ worth of Googling and frustration. You’re welcome.
We can double-check that our namespace is part of the project. Look at the properties of the project in the Solution Explorer, under Dependencies -> Packages:
The “using” clause
The last thing we need to do is outline a using clause in our source code. Here’s the syntax:
A moment ago, we added the namespace to the project, but we didn’t actually say we wanted to use it yet. The using clause does just that:
We’re on our way.
Setting up a SQL Server connection string
To use C# with SQL, you’ll need to “connect” to the database. Our C# application needs to know the SQL Server connection details in order to work properly, obviously. Those details are outlined in something called a connection string.
The connection string contains details like:
- Name of the SQL Server
- Database name
- Login credentials
It contains everything the C# application needs to find and connect to the database.
So then the question becomes: How do I create a SQL Server connection string?
Creating a SQL Server connection string using a UDL file
We can create something called a UDL file to help us create the SQL Server connection string. Here are the simple steps:
- On the desktop (or any folder location, really) right click and choose New -> Text Document.
- The name of the document doesn’t matter, but you should change the extension of the document from .TXT to .UDL. You’ll get a prompt asking if you are sure. We are, in fact, sure.
- Right click the new .UDL file and select Open With -> OLE DB Core Services. This will open the following “Data Link Properties” window:
You’re going to fill out this form with the connection information for your SQL Server and database. Here is an example of the content of my UDL file:
My SQL Server is called “BEETLEJUICEPC\SQLEXPRESS01“, the name of the database is “Dealership“, and I’m choosing to connect to them using my Windows credentials. If you would rather connect to the SQL Server by outlining a username and password (maybe the SA account, for example), you can do that.
When you’ve outlined everything, click Test Connection to make sure it works!:
Once you’ve verified the connection is valid, click OK to close the UDL form.
Writing out the connection string to a file
Next, we can open the same UDL file within a text editor (like Notepad or Notepad++) and see the connection information there in plain text format.
Right click the UDL file and choose Open With -> Notepad. The document will open in Notepad and you will see your new connection string!:
Yours might look slightly different from mine, but you should see the name of your SQL Server and database. If you chose to login using a username and password, you would see that, too.
Awesome, so now we have the connection string.
We should copy that string to it’s own file, then put that file in the same folder location as our project. It doesn’t need to be in that folder location, but that seems like the best place for it.
Remember earlier when we created the C# project, it outlined the default location where the project would be saved to. I’ll just put the new ConnectionString.txt file there.
(I need to emphasize that this ConnectionString.txt file doesn’t need to exist, but later we’ll see why it’s a really good idea to have it)
Important: When you copy the connection string to this ConnectionString.txt file, you need to add the words ‘Data Source =‘ to the front of it.
So the file will looks like this:
We’re on the home stretch.
Performing a SELECT statement from your C# program
Ok, let’s make a simple connection to the database.
But first, let’s actually create some data in our database. For our examples, we’re going to pretend we work at a used car dealership, and need a database to keep track of the vehicles in our inventory. We’re going to start with a table called Vehicles:
Notice the CarID column uses the IDENTITY property. This is useful for automatically populating the column with a new value on inserts.
Also notice the CarYear column is a SMALLINT data type. This data type can store a number as large as about 32,000, which is of course much more than we need, but it uses less space in memory than the INT data type, for example. You should always try to use the smallest data type to suit your needs.
Let’s populate the table with a single row:
Nice, here’s what the table ought to look like:
Writing a SELECT statement in the C# application
Let’s say our C# application is the tool our employees will use to gather details about cars in our inventory.
Let’s write a simple SELECT statement to return all the details of a vehicle with a specific CarID, provided by the user.
In SQL, we know that’s a simple line of code. Let’s say we want to see all the details of CarID = 1
But let’s see if we can mimic that in our C# with SQL application. Again, the difference is that the user will provide the CarID value of the vehicle for which they want details.
Back in the C# application, let’s write the line of code where we ask the user for their input (in the Main function):
We read what the user typed with a simple Console.ReadLine(). But let’s take that input and actually use it as a parameter to a new function dedicated to the task of running a SQL SELECT statement. We’ll call our new C# function doSelectStatement. Here is the call to it:
If you are a programmer, you’ll understand the importance of functions. We want to isolate tasks into their own code blocks.
Using the SqlConnection, SqlCommand, and SqlDataReader classes
Ok, so now we need to write the doSelectStatement C# function. We’ll start slow:
The first thing we need to put in this function a using block, which outlines a new SqlConnection object:
Notice the *connection string* part. We’re going to copy and paste our connection string here, in quotes:
Remember to put the ‘Data Source =‘ at the front of the connection string.
Notice if your SQL Server name contains a backslash (‘\‘), you need to escape it with a second backslash.
(Again, I might have saved you hours’ worth of Googling. You’re welcome.)
Ok, so the newConnection object is used to connect to the database. The next step is to create a SqlCommand object, passing our newConnection object to it:
The first parameter to the SqlCommand constructor is the actual SQL statement we want to fire. It’s just a string value. Notice how we append the CarID value. The second parameter is the newConnection object we created in the using block.
Then, you see how we need to actually open the connection by saying selectCommand.Connection.Open()
(Later, we’ll make sure to close the connection by saying the following: selectCommand.Connection.Close())
The next thing we need to do is actually execute the SQL statement and read its results into a SqlDataReader object.
The way we execute our SQL statement is by calling the ExecuteReader() function on the selectCommand object. Here’s the code:
Again, notice we need to write the result of that ExecuteReader() function to a SqlDataReader object, called sqlReader in our case.
It’s a good idea to perform this ExecuteReader() function in a try…catch block. You want to make sure any errors in your code are handled gracefully.
Printing the results of our query to the screen
Ok, so we successfully executed our SQL query, and we know its results are held in the sqlReader object. The next step is to just pull the information from that object.
Let’s just show you how it’s done, then we’ll discuss it:
The first thing you should do is double-check your sqlReader object to make sure there is actually something to read. This is accomplished with the simple if (sqlReader.Read()) code. We want to make sure the query actually returned something.
It would be bad if we attempted to read information from the sqlReader object if it was empty.
So then we call several ‘Get‘ functions. These ‘Get‘ functions will read column data from the result set of our query. As a parameter to these ‘Get‘ functions, we pass a number representing the column we want to get data from. The result set columns start with an index of 0.
Which ‘Get‘ function we use depends on the data type of the column for which we need to read data.
The different ‘Get’ functions
If the column we need to read contains a string value, we use the GetString() function. If the column is an integer, we might need to use either GetInt16() or GetInt32() or even GetInt64()
Which ‘Get‘ function you use will depend on the data type of the SQL column you’re reading from.
For example, if we want the data from the first column in our result set, we need to use the GetString function since that is a string column. As a parameter to the function, we pass the number of the column we want to get data from. Since we want the first column, and column numbers start with an index of 0, the call would be GetString(0).
The CarYear of our vehicle is the fourth column in our SELECT statement. If columns start with an index of 0, this would mean the CarYear is column number 3. But to pull integer information, we need to use a ‘GetInt‘ method. Since the CarYear column is a SMALLINT data type, we use the ‘GetInt16‘ method. So the whole call is GetInt16(3).
(And also, we need everything to be in a string format for the Console.WriteLine method, so we actually need to call the ‘ToString‘ method on the whole thing to convert the integer result to a string: GetInt16(3).ToString())
Again folks, which ‘Get‘ function we use depends on the data type of the column for which we need to read data. Take a look at the official Microsoft documentation on the SqlDataReader class to understand more about the different ‘Get‘ methods.
Folks, we’ve done it. Here is everything we have so far:
All we need to do is build the solution and run it.
Building and running our C# with SQL code
To build the solution, just right-click the solution in the Solution Explorer in Visual Studio and choose Build Solution (or just hit Ctrl+Shift+B on your keyboard):
Then to run the application, just navigate to Debug -> Start Without Debugging (or just hit Ctrl+F5 on your keyboard):
It will launch your console app:
Remember, we want the user to enter the ID of the car they want details for. We only have one car in our Vehicles table, and it has a CarID of 1. So let’s enter 1 and see if our getSelectStatement function works:
Yep, it checks out.
Reading the connection string from file
Let’s think about something we did in our source code that might be considered bad practice.
We basically hard-coded our connection string information in the C# code. If that seems a bit unsafe, that’s because it is. What if it needs to change? Sure, you can change it easily, but what about the end user? They won’t have access to the source code, nor would they really know what they’re doing if they did!
For that reason, it would be better to read in the connection string information from a file. Remember when we saved our connection string to a file called ConnectionString.txt? Well, we can read from that file in our source code. It would be best to also create a separate function dedicated to getting the connection string. We’ll call it getConnectionString.
To read from a file (using the FileStream class) we need to include the System.IO namespace to our application. That is as simple as adding another ‘using‘ clause to the top of our code:
Once that is there, we can write the following getConnectionString function:
Again, notice the double backslashes in the folder path.
This function returns the string value of the connection string outlined in the ConnectionString.txt file. So now, instead of hard-coding the connection string, we can just call our new getConnectionString() function to retrieve the connection string for us:
Much cleaner. And now, if we need to change the name of the SQL Server or the database, we can change it in the ConnectionString.txt file!
Doing an UPDATE statement in your C# program
Folks, the rest of this tutorial is going to be slight variations of what you have already learned. The process for running other SQL statements (like UPDATE, INSERT or DELETE) is very similar to running a SELECT statement using C# with SQL.
Let’s think about an UPDATE statement. For this example, let’s have the user actually type out what UPDATE statement they want executed in SQL.
Here’s a new block in the Main function of our code:
Let’s talk about how this function is the same as our previous function:
- We created a using block that outlines a SqlConnection object called updateConnection. And again, we called our handy getConnectionString() function to grab the connection string from our file.
- We create a SqlCommand object called updateCommand. In the constructor, we pass the SQL statement we want to run and the connection object. In this case, the SQL statement is what the user typed out (stored in the updateStatement parameter). Then we open the connection saying updateCommand.Connection.Open().
- Then we execute the query using ExecuteNonQuery().
That last point is worth talking about. The statement we’re running isn’t exactly a query, is it? We’re not extracting information when we do an UPDATE statement.
I suppose that’s why Microsoft decided to name the method ExecuteNonQuery. If we are, in fact, querying data, we use ExecuteReader like we saw when running the SELECT statement earlier.
The other thing about ExecuteNonQuery is that it returns the number of rows effected by the SQL statement. So if our query did successfully update one row, the return value from ExecuteNonQuery would be 1. This is why it’s a good idea to put the call to ExecuteNonQuery in an ‘if‘ statement. If the command updated at least one row, we know it worked.
One final thing to note is how we don’t have a SqlDataReader object. Remember, we’re not reading anything, right?
Testing an UPDATE statement from our C# application
Let’s test it out. Remember to build the solution before running the program.
After the SELECT statement work is done, we’re asked to type the UPDATE statement we want executed:
So, let’s type an UPDATE statement. For example: UPDATE Vehicles SET Model=’Cherokee’ WHERE CarID = 1
Here is that UPDATE statement ran from our program:
Now if we check the data in SQL, we see it did, in fact, change:
INSERT data from your C# with SQL program
Again folks, we’re basically going to do similar work for INSERT statement. This time, we’ll ask the user to enter the information they would like inserted into the Vehicles table.
Here is the new block in our Main function:
We’re asking the user for everything:
- VIN
- Make
- Model
- Year
- Mileage
We are going to pass all that information to the new doInsertStatement function. Speaking of:
Again folks, it’s more of the same:
- Get a connection using SqlConnection and our handy getConnectionString function.
- Create a new SqlCommand object that passes the SQL statement we want to run as a parameter
- Call ExecuteNonQuery, which returns the number of rows effected. If the INSERT statement succeeded, that means one row was effected.
Let’s test it out:
Now let’s check the data:
DELETE data from database from your C# program
You get the idea. More of the same.
Let’s have the user enter the CarID of the car they want to delete. Here’s the new code in the Main function:
Here’s the new doDeleteStatement function:
We already know the drill:
- Get a connection using getConnectionString
- Create SqlCommand object
- Call ExecuteNonQuery
Let’s test it out:
Let’s check the data. We ought to see that CarID # 2 is gone, which is the Ford F150 we added earlier:
Final thoughts
I hope this tutorial helped you understand how to use C# with SQL by successfully connecting a SQL Server database to a C# application.
We’ve learned how to both read and write data to a database via a C# application. It’s all possible through the use of the System.Data.SqlClient namespace.
Your next steps should be to play around with this program. Write your own functions, add more error handling, think about how to reduce repeated code, make it more user friendly, etc. The changes you could make to this code are endless.
Also, take a look at the documentation for the System.Data.SqlClient class. There is a lot of great information for you to read through.
For your ultimate convenience, here is the entire C# code:
Want to learn more? Check out these related articles:
In addition, don’t forget to head on over to www.SimpleSQLTutorials.com where you’ll find more good stuff.
This site uses Akismet to reduce spam. Learn how your comment data is processed.
C# Database Connection: How to connect SQL Server (Example)
It can work with different types of databases. It can work with the most common databases such as Oracle and Microsoft SQL Server.
It also can work with new forms of databases such as MongoDB and MySQL.
Fundamentals of Database connectivity
C# and .Net can work with a majority of databases, the most common being Oracle and Microsoft SQL Server. But with every database, the logic behind working with all of them is mostly the same.
In our examples, we will look at working the Microsoft SQL Server as our database. For learning purposes, one can download and use the Microsoft SQL Server Express Edition, which is a free database software provided by Microsoft.
In working with databases, the following are the concepts which are common to all databases.
- Connection – To work with the data in a database, the first obvious step is the connection. The connection to a database normally consists of the below-mentioned parameters.
- Database name or Data Source – The first important parameter is the database name to which the connection needs to be established. Each connection can only work with one database at a time.
- Credentials – The next important aspect is the username and password which needs to be used to establish a connection to the database. It ensures that the username and password have the necessary privileges to connect to the database.
- Optional parameters – For each database type, you can specify optional parameters to provide more information on how .net should handle the connection to the database. For example, one can specify a parameter for how long the connection should stay active. If no operation is performed for a specific period of time, then the parameter would determine if the connection has to be closed.
Ok, now that we have seen the theory of each operation, let’s jump into the further sections to look at how we can perform database operations in C#.
SQL Command in c#
SqlCommand in C# allow the user to query and send the commands to the database. SQL command is specified by the SQL connection object. Two methods are used, ExecuteReader method for results of query and ExecuteNonQuery for insert, Update, and delete commands. It is the method that is best for the different commands.
How to connect C# to Database
Let’s now look at the code, which needs to be kept in place to create a connection to a database. In our example, we will connect to a database which has the name of Demodb. The credentials used to connect to the database are given below
- Username – sa
- Password – demo123
We will see a simple Windows forms application to work with databases. We will have a simple button called “Connect” which will be used to connect to the database.
So let’s follow the below steps to achieve this
Step 1) The first step involves the creation of a new project in Visual Studio. After launching Visual Studio, you need to choose the menu option New->Project.
Step 2) The next step is to choose the project type as a Windows Forms application. Here, we also need to mention the name and location of our project.
- In the project dialog box, we can see various options for creating different types of projects in Visual Studio. Click the Windows option on the left-hand side.
- When we click the Windows options in the previous step, we will be able to see an option for Windows Forms Application. Click this option.
- We then give a name for the application which in our case is “DemoApplication”. We also need to provide a location to store our application.
- Finally, we click the ‘OK’ button to let Visual Studio to create our project.
Step 3) Now add a button from the toolbox to the Windows form. Put the text property of the Button as Connect. This is how it will look like
Step 4) Now double click the form so that an event handler is added to the code for the button click event. In the event handler, add the below code.
Code Explanation:-
- The first step is to create variables, which will be used to create the connection string and the connection to the SQL Server database.
- The next step is to create the connection string. The connecting string needs to be specified correctly for C# to understand the connection string. The connection string consists of the following parts
- Data Source – This is the name of the server on which the database resides. In our case, it resides on a machine called WIN- 50GP30FGO75.
- The Initial Catalog is used to specify the name of the database
- The UserID and Password are the credentials required to connect to the database.
When the above code is set, and the project is executed using Visual Studio, you will get the below output. Once the form is displayed, click the Connect button.
Output:-
When you click on “connect” button, from the output, you can see that the database connection was established. Hence, the message box was displayed.
Access data with the SqlDataReader
To showcase how data can be accessed using C#, let us assume that we have the following artifacts in our database.
- A table called demotb. This table will be used to store the ID and names of various Tutorials.
- The table will have 2 columns, one called “TutorialID” and the other called “TutorialName.”
- For the moment, the table will have 2 rows as shown below.
Let’s change the code in our form, so that we can query for this data and display the information via a Messagebox. Note that all the code entered below is a continuation of the code written for the data connection in the previous section.
Step 1) Let’s split the code into 2 parts so that it will be easy to understand for the user.
- The first will be to construct our “select” statement, which will be used to read the data from the database.
- We will then execute the “select” statement against the database and fetch all the table rows accordingly.
Code Explanation:-
- The first step is to create the following variables
- SQLCommand – The ‘SQLCommand’ is a class defined within C#. This class is used to perform operations of reading and writing into the database. Hence, the first step is to make sure that we create a variable type of this class. This variable will then be used in subsequent steps of reading data from our database.
- The DataReader object is used to get all the data specified by the SQL query. We can then read all the table rows one by one using the data reader.
- We then define 2 string variables, one is “SQL” to hold our SQL command string. The next is the “Output” which will contain all the table values.
Step 2) In the final step, we will just display the output to the user and close all the objects related to the database operation.
Code Explanation:-
- We will continue our code by displaying the value of the Output variable using the MessageBox. The Output variable will contain all the values from the demotb table.
- We finally close all the objects related to our database operation. Remember this is always a good practice.
When the above code is set, and the project is run using Visual Studio, you will get the below output. Once the form is displayed, click the Connect button.
Output:-
From the output, you can clearly see that the program was able to get the values from the database. The data is then displayed in the message box.
C# Insert Into Database
Just like Accessing data, C# has the ability to insert records into the database as well. To showcase how to insert records into our database, let’s take the same table structure which was used above.
TutorialID TutorialName 1 C# 2 ASP.Net Let’s change the code in our form, so that we can insert the following row into the table
TutorialID TutorialName 3 VB.Net So let’s add the following code to our program. The below code snippet will be used to insert an existing record in our database.
Code Explanation:-
- The first step is to create the following variables
- SQLCommand – This data type is used to define objects which are used to perform SQL operations against a database. This object will hold the SQL command which will run against our SQL Server database.
- The DataAdapter object is used to perform specific SQL operations such as insert, delete and update commands.
- We then define a string variable, which is “SQL” to hold our SQL command string.
When the above code is set, and the project is executed using Visual Studio, you will get the below output. Once the form is displayed, click the Connect button.
Output:-
If you go to SQL Server Express and see the rows in the demotb table, you will see the row inserted as shown below
C# Update Database
Just like Accessing data, C# has the ability to update existing records from the database as well. To showcase how to update records into our database, let’s take the same table structure which was used above.
TutorialID TutorialName 1 C# 2 ASP.Net 3 VB.Net Let’s change the code in our form, so that we can update the following row. The old row value is TutorialID as “3” and Tutorial Name as “VB.Net”. Which we will update it to “VB.Net complete” while the row value for Tutorial ID will remain same.
Old row
TutorialID TutorialName 3 VB.Net New row
TutorialID TutorialName 3 VB.Net complete So let’s add the following code to our program. The below code snippet will be used to update an existing record in our database.
C# SqlCommand Example With Code Explanation:-
- The first step is to create the following variables
- SQLCommand – This data type is used to define objects which are used to perform SQL operations against a database. This object will hold the SQL command which will run against our SQL Server database.
- The dataadapter object is used to perform specific SQL operations such as insert, delete and update commands.
- We then define a string variable, which is SQL to hold our SQL command string.
When the above code is set, and the project is executed using Visual Studio, you will get the below output. Once the form is displayed, click the Connect button.
Output:-
If you actually go to SQL Server Express and see the rows in the demotb table, you will see the row was successfully updated as shown below.
Deleting Records
Just like Accessing data, C# has the ability to delete existing records from the database as well. To showcase how to delete records into our database, let’s take the same table structure which was used above.
TutorialID TutorialName 1 C# 2 ASP.Net 3 VB.Net complete Let’s change the code in our form, so that we can delete the following row
TutorialID TutorialName 3 VB.Net complete So let’s add the following code to our program. The below code snippet will be used to delete an existing record in our database.
Code Explanation:-
- The Key difference in this code is that we are now issuing the delete SQL statement. The delete statement is used to delete the row in the demotb table in which the TutorialID has a value of 3.
- In our data adapter command, we now associate the insert SQL command to our adapter. We also then issue the ExecuteNonQuery method which is used to execute the Delete statement against our database.
When the above code is set, and the project is executed using Visual Studio, you will get the below output. Once the form is displayed, click the Connect button.
Output:-
If you actually go to SQL Server Express and see the rows in the demotb table, you will see the row was successfully deleted as shown below.
Connecting Controls to Data
In the earlier sections, we have seen how to we can use C# commands such as SQLCommand and SQLReader to fetch data from a database. We also saw how we read each row of the table and use a messagebox to display the contents of a table to the user.
But obviously, users don’t want to see data sent via message boxes and would want better controls to display the data. Let’s take the below data structure in a table
TutorialID TutorialName 1 C# 2 ASP.Net 3 VB.Net complete From the above data structure, the user would ideally want to see the TutorialID and Tutorial Name displayed in a textbox. Secondly, they might want to have some sort of button control which could allow them to go to the next record or to the previous record in the table. This would require a bit of extra coding from the developer’s end.
The good news is that C# can reduce the additional coding effort by allowing binding of controls to data. What this means is that C# can automatically populate the value of the textbox as per a particular field of the table.
So, you can have 2 textboxes in a windows form. You can then link one text box to the TutorialID field and another textbox to the TutorialName field. This linking is done in the Visual Studio designer itself, and you don’t need to write extra code for this.
Visual Studio will ensure that it writes the code for you to ensure the linkage works. Then when you run your application, the textbox controls will automatically connect to the database, fetch the data and display it in the textbox controls. No coding is required from the developer’s end to achieve this.
Let’s look at a code example of how we can achieve binding of controls.
In our example, we are going to create 2 textboxes on the windows form. They are going to represent the Tutorial ID and Tutorial Name respectively. They will be bound to the Tutorial ID and TutorialName fields of the database accordingly.
Let’s follow the below-mentioned steps to achieve this.
Step 1) Construct the basic form. In the form drag and drop 2 components- labels and textboxes. Then carry out the following substeps
- Put the text value of the first label as TutorialID
- Put the text value of the second label as TutorialName
- Put the name property of the first textbox as txtID
- Put the name property of the second textbox as txtName
Below is the how the form would look like once the above-mentioned steps are performed.
Step 2) The next step is to add a binding Navigator to the form. The binding Navigator control can automatically navigate through each row of the table. To add the binding navigator, just go to the toolbox and drag it to the form.
Step 3) The next step is to add a binding to our database. This can be done by going to any of the Textbox control and clicking on the DataBindings->Text property. The Binding Navigator is used to establish a link from your application to a database.
When you perform this step, Visual Studio will automatically add the required code to the application to make sure the application is linked to the database. Normally the database in Visual Studio is referred to as a Project Data Source. So to ensure the connection is established between the application and the database, the first step is to create a project data source.
The following screen will show up. Click on the link- “Add Project Data Source”. When you click on the project data source, you will be presented with a wizard; this will allow you to define the database connection.
Step 4) Once you click on the Add Project Data Source link, you will be presented with a wizard which will be used to create a connection to the demotb database. The following steps show in detail what needs to be configured during each step of the wizard.
- In the screen which pops up , choose the Data Source type as Database and then click on next button.
- In the next screen, you need to start the creation of the connection string to the database. The connection string is required for the application to establish a connection to the database. It contains the parameters such as server name, database name, and the name of the driver.
- Click on the New connection button
- Choose the Data Source as Microsoft SQL Server
- Click the Continue button.
- Next, you need to add the credentials to connect to the database
- Choose the server name on which the SQL Server resides
- Enter the user id and password to connect to the database
- Choose the database as demotb
- Click the ‘ok’ button.
- In this screen, we will confirm all the settings which were carried on the previous screens.
- Choose the option “Yes” to include sensitive data in the connection string
- Click on the “Next” button.
- In the next screen, click on the “Next” button to confirm the creation of the connection string
- In this step,
- Choose the tables of Demotb, which will be shown in the next screen.
- This table will now become an available data source in the C# project
When you click the Finish button, Visual Studio will now ensure that the application can query all the rows in the table Demotb.
Step 5) Now that the data source is defined, we now need to connect the TutorialID and TutorialName textbox to the demotb table. When you click on the Text property of either the TutorialID or TutorialName textbox, you will now see that the binding source to Demotb is available.
For the first text box choose the Tutorial ID. Repeat this step for the second textbox and choose the field as TutorialName. The below steps shows how we can navigate to each control and change the binding accordingly.
- Click on the Tutorial ID control.
- In the Properties window, you will see the properties of the TutorialID textbox. Go to the text property and click on the down arrow button.
- When you click the down arrow button, you will see the demotbBinding Source option. And under this, you will see the options of TutorialName and TutorialID. Choose the Tutorial ID one.
Repeat the above 3 steps for the Tutorial Name text box.
- So click on the Tutorial Name text box
- Go to the properties window
- Choose the Text property
- Choose the TutorialName option under demotbBindingSource
Step 6) Next we need to change the Binding Source property of the BindingNavigator to point to our Demotb data source. The reason we do this is that the Binding Navigator also needs to know which table it needs to refer to.
The Binding Navigator is used to select the next or previous record in the table. So even though the data source is added to the project as a whole and to the text box control, we still need to ensure the Binding Navigator also has a link to our data source. In order to do this, we need to click the Binding navigator object, go to the Binding Source property and choose the one that is available
Next, we need to go to the Properties window so that we can make the change to Binding Source property.
When all of the above steps are executed successfully, you will get the below-mentioned output.
Output:-
Now when the project is launched, you can see that the textboxes automatically get the values from the table.
When you click the Next button on the Navigator, it automatically goes to the next record in the table. And the values of the next record automatically come in the text boxes
C# DataGridView
Data Grids are used to display data from a table in a grid-like format. When a user sees’s table data, they normally prefer seeing all the table rows in one shot. This can be achieved if we can display the data in a grid on the form.
C# and Visual Studio have inbuilt data grids, this can be used to display data. Let’s take a look at an example of this. In our example, we will have a data grid, which will be used to display the Tutorial ID and Tutorial Name values from the demotb table.
Step 1) Drag the DataGridView control from the toolbox to the Form in Visual Studio. The DataGridView control is used in Visual Studio to display the rows of a table in a grid-like format.
Step 2) In the next step, we need to connect our data grid to the database. In the last section, we had created a project data source. Let’s use the same data source in our example.
- First, you need to choose the grid and click on the arrow in the grid. This will bring up the grid configuration options.
- In the configuration options, just choose the data source as demotbBindingSource which was the data source created in the earlier section.
If all the above steps are executed as shown, you will get the below-mentioned output.
Output:-
From the output, you can see that the grid was populated by the values from the database.
Как сделать подключение к Microsoft SQL Server средствам Visual Studio 2019?
string connectionString = @»Data Source=Domem\Moiseev_AN;Initial Catalog=JobBase;Integrated Security=True»;
Александр Ананьев, нужно запрограммировать диалоговое окно подключения к Microsoft SQL Server на C#, чтобы подключение происходила к примеру так:
у каждого компьютера своя учетная запись, если программа запущена к примеру на другом пк с учеткой Domen\Syduakina, то строка подключения
Доменная учетная запись добавлена в Microsoft SQL Server.Как подключить sql server к visual studio
Кроме полноценного MS SQL Server в версиях Developer или Express мы также можем установить облегченную легковесную версию SQL Server Express — движок LocalDB , который предназначен специально для целей разработки. Например, LocalDB может применяться для разработки программ на различных языках программирования для тестирования их работы с базой данных MS SQL Server, когда нет необходимости в большинстве возможностей стандартного MS SQL Server. И в этих условиях естественно проще установить небольшой легковесный движок, чем полноценный MS SQL Server.
Формально SQLLocalDB представляет компонент MS SQL Server Express, тем не менее есть несколько вариантов, как можно установить LocalDB.
Установка через программу усстановки SQL Server Express
Первый вариант представляет установка LocalDB через программу усстановки SQL Server Express. Так, загрузим с адреса https://www.microsoft.com/en-us/sql-server/sql-server-downloads установщик для SQL Server Express. На стартовом экране установщика выберем пункт «Скачать носитель»:
Нам откроется окно для выбора компонентов для установки. Выберем на нем пункт LocalDB :
После нажатия на кнопку «Скачать» в указанную в окне папку будет загружен файл SqlLocalDB.msi . И после запуска этого файла будет установлен LocalDB.
Установка через мастер установки Visual Studio
Если вы вдруг работаете с Visual Studio, в частности, с платформой ASP.NET, то вы можете установить LocalDB через программу установки Visual Studio. В частности, на вкладке «Отдельные компоненты» можно выбрать соответствующий пункт (конкретная версия LocalDB может отличаться):
Подключение к LocalDB
После установки LocalDB к этой версии SQL Server также можно подключиться из SQL Server Management Studio. Для этого при запуске программы в окне подключения к серверу в поле «Server name» в качестве имени сервера необходимо указать (localdb)\MSSQLLocalDB
Если в этом поле выпадающий список не содержит «(localdb)\MSSQLLocalDB», однако LocalDB тем не менее был ранее установлен, то можно просто ввести в это поле «(localdb)\MSSQLLocalDB».
И после этого мы сможем работать с LocalDB в SQL Server Management Studio в рамках установленых для этого движка ограничений: