Accessing Data from a database is one of the important aspects of any programming language. It is an absolute necessity for any programming language to have the ability to work with databases. C# is no different. Show
It can work with different types of databases. It can work with the most common databases such as Oracle and Microsoft SQL Server. 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.
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. 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
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.
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. using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace DemoApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString; SqlConnection cnn; connetionString = @"Data Source=WIN-50GP30FGO75;Initial Catalog=Demodb;User ID=sa;Password=demol23"; cnn = new SqlConnection(connetionString); cnn.Open(); MessageBox.Show("Connection Open !"); cnn.Close(); } } } Code Explanation:-
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 SqlDataReaderTo showcase how data can be accessed using C#, let us assume that we have the following artifacts in our database.
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.
Code Explanation:-
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:-
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 DatabaseJust 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.
Let’s change the code in our form, so that we can insert the following row into the table
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:-
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 DatabaseJust 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.
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
New row
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:-
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 RecordsJust 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.
Let’s change the code in our form, so that we can delete the following row
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:-
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 DataIn 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
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
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.
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.
Repeat the above 3 steps for the Tutorial Name text box.
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# DataGridViewData 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.
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. Summary
How to connect to SQL Server in C?The only caveat here is that ODBC does use an old C-style API.. Step 1: Creating your Azure SQL Database. ... . Step 2: Get connection string. ... . Step 3: Add your IP to the firewall. ... . Step 4: Connecting from a Windows C/C++ application. ... . Step 5: Connecting from a Linux C/C++ application.. How to connect to local SQL Server in C#?Steps to Establish C# SQL Server Database Connection. Step 1: Create a New Project.. Step 2: Connect.. Step 3: Add Event Handler.. Code Explanation.. Output.. Can you use SQL with C?You can code SQL statements in a C or C++ program wherever you can use executable statements. Each SQL statement in a C or C++ program must begin with EXEC SQL and end with a semicolon (;). The EXEC and SQL keywords must appear on one line, but the remainder of the statement can appear on subsequent lines.
How to connect to SQL Server using IP address in C#?Port # 1433 is default port number, so if you are running SQL server on default port number, you don't have to specify in connection string. You can see this link how to connect to database. In SQL server, make sure TCP/IP is enabled, so you can access data from different computer.
|