How To Retrieve Data From Database In C# Windows Application

Accessing Information from a database is one of the important aspects of whatsoever programming language. It is an absolute necessity for whatsoever programming linguistic communication to have the power to work with databases. C# is no different.

Information technology tin piece of work with different types of databases. It tin work with the most common databases such as Oracle and Microsoft SQL Server.

C# and .Net can work with a majority of databases, the nearly mutual 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 volition look at working the Microsoft SQL Server every bit our database. For learning purposes, ane tin download and use the Microsoft SQL Server Limited Edition, which is a complimentary database software provided by Microsoft.

In working with databases, the following are the concepts which are common to all databases.

Connectedness – To work with the data in a database, the showtime obvious step is the connection. The connexion to a database unremarkably consists of the below-mentioned parameters.

1. Database name or Data Source – The first important parameter is the database name to which the connection needs to exist established. Each connexion can but work with one database at a time.
2. Credentials – The next of import aspect is the username and password which needs to be used to establish a connectedness to the database. It ensures that the username and countersign take the necessary privileges to connect to the database.
3. Optional parameters – For each database blazon, you can specify optional parameters to provide more information on how .net should handle the connectedness to the database. For example, i can specify a parameter for how long the connection should stay active. If no functioning is performed for a specific menstruum of time, then the parameter would determine if the connexion has to be closed.

Selecting data from the database – Once the connection has been established, the adjacent important attribute is to fetch the data from the database. C# can execute 'SQL' select command against the database. The 'SQL' statement tin be used to fetch data from a specific table in the database.

Inserting data into the database – C# tin can as well be used to insert records into the database. Values tin be specified in C# for each row that needs to be inserted into the database.

Updating data into the database – C# tin can besides be used to update existing records into the database. New values tin can exist specified in C# for each row that needs to be updated into the database.

Deleting data from a database – C# can also be used to delete records into the database. Select commands to specify which rows need to exist deleted can be specified in C#.

Ok, at present that nosotros have seen the theory of each performance, permit's jump into the further sections to wait at how we can perform database operations in C#.

SQL Command in c#

SqlCommand in C# permit the user to query and ship the commands to the database. SQL control is specified past the SQL connexion 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 all-time for the different commands.

How to connect C# to Database

Permit's now wait 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 elementary Windows forms awarding to work with databases. We will have a simple push button called "Connect" which will be used to connect to the database.

So permit's follow the below steps to achieve this

Step ane) The showtime step involves the creation of a new project in Visual Studio. Subsequently launching Visual Studio, y'all need to choose the bill of fare option New->Projection.

Step ii) The next pace is to choose the project type every bit a Windows Forms awarding. Hither, nosotros also need to mention the name and location of our project.

1. In the projection dialog box, we tin see various options for creating different types of projects in Visual Studio. Click the Windows option on the left-hand side.
2. When we click the Windows options in the previous step, we volition exist able to come across an choice for Windows Forms Application. Click this option.
3. We and so give a proper noun for the application which in our case is "DemoApplication". We also need to provide a location to store our application.
4. Finally, we click the 'OK' button to permit Visual Studio to create our project.

Step 3) Now add a push button from the toolbox to the Windows form. Put the text property of the Button as Connect. This is how information technology will look like

Step 4) At present double click the grade so that an event handler is added to the code for the push button click event. In the event handler, add together the below code.

using System; using Arrangement.Collections.Generic; using System.ComponentModel; using System.Information; using System.Data.SqlClient; using Organisation.Drawing; using Arrangement.Linq; using Arrangement.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 = @"Information Source=WIN-50GP30FGO75;Initial Catalog=Demodb;User ID=sa;Password=demol23";    cnn = new SqlConnection(connetionString);    cnn.Open();    MessageBox.Evidence("Connection Open  !");    cnn.Close();   }  } }

Code Explanation:-

1. The outset step is to create variables, which will be used to create the connection cord and the connectedness to the SQL Server database.
2. The next step is to create the connection cord. The connecting string needs to be specified correctly for C# to sympathize the connectedness cord. The connection string consists of the post-obit parts
   1. Information Source – This is the name of the server on which the database resides. In our instance, it resides on a machine chosen WIN- 50GP30FGO75.
   2. The Initial Itemize is used to specify the name of the database
   3. The UserID and Password are the credentials required to connect to the database.
3. Next, nosotros assign the connecting string to the variable cnn. The variable cnn, which is of type SqlConnection is used to found the connection to the database.
4. Side by side, we use the Open method of the cnn variable to open a connectedness to the database. Nosotros and then simply display a message to the user that the connectedness is established.
5. One time the functioning is completed successfully, we then close the connection to the database. It is always a practiced practice to close the connection to the database if nothing else is required to be washed on the database.

When the above code is set, and the project is executed using Visual Studio, y'all will become the below output. Once the form is displayed, click the Connect button.

Output:-

When you lot click on "connect" button, from the output, you can see that the database connectedness was established. Hence, the message box was displayed.

Access data with the SqlDataReader

To showcase how information tin can be accessed using C#, let united states of america assume that we have the following artifacts in our database.

1. A table called demotb. This table will be used to shop the ID and names of various Tutorials.
2. The tabular array will accept 2 columns, one called "TutorialID" and the other called "TutorialName."
3. For the moment, the table volition have 2 rows as shown below.

TutorialID	TutorialName
1	C#
2	ASP.Net

Permit'south 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 lawmaking entered beneath is a continuation of the code written for the data connection in the previous section.

Step ane) Let's split the code into 2 parts so that it will exist easy to understand for the user.

• The start volition be to construct our "select" statement, which will be used to read the information from the database.
• We will then execute the "select" statement against the database and fetch all the table rows accordingly.

Code Explanation:-

1. The kickoff step is to create the post-obit variables
   1. SQLCommand – The 'SQLCommand' is a form defined within C#. This class is used to perform operations of reading and writing into the database. Hence, the beginning step is to make certain that nosotros create a variable type of this course. This variable will then be used in subsequent steps of reading data from our database.
   2. The DataReader object is used to get all the information specified by the SQL query. We can then read all the tabular array rows one by i using the data reader.
   3. Nosotros then define 2 string variables, one is "SQL" to concord our SQL command string. The adjacent is the "Output" which will contain all the tabular array values.
2. The next stride is to define the SQL statement, which will exist used against our database. In our instance, it is "Select TutorialID, TutorialName from demotb". This volition fetch all the rows from the table demotb.
3. Adjacent, we create the command object which is used to execute the SQL statement against the database. In the SQL command, yous have to pass the connection object and the SQL string.
4. Next, we will execute the information reader control, which will fetch all the rows from the demotb table.
5. Now that we have all the rows of the table with usa, we need a machinery to access the row ane by one. For this, nosotros will use the while statement. The while statement will be used to access the rows from the information reader i at a time. Nosotros and so use the GetValue method to get the value of TutorialID and TutorialName.

Step two) In the terminal step, we will simply brandish the output to the user and shut all the objects related to the database operation.

Code Explanation:-

1. 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.
2. Nosotros finally close all the objects related to our database performance. Think this is always a good practice.

When the above lawmaking is fix, and the project is run using Visual Studio, you volition get the beneath output. One time the grade is displayed, click the Connect button.

Output:-

From the output, you lot can clearly see that the program was able to become the values from the database. The data is and so displayed in the bulletin box.

C# Insert Into Database

Just similar Accessing information, C# has the power to insert records into the database every bit well. To showcase how to insert records into our database, let's take the aforementioned table construction which was used above.

TutorialID	TutorialName
i	C#
2	ASP.Internet

Permit's change the code in our grade, so that we tin insert the following row into the table

TutorialID	TutorialName
3	VB.Net

So let'south add the following code to our program. The below code snippet will be used to insert an existing tape in our database.

Code Explanation:-

1. The first step is to create the following variables
1. SQLCommand – This data type is used to define objects which are used to perform SQL operations against a database. This object will concur the SQL command which volition run against our SQL Server database.
2. The DataAdapter object is used to perform specific SQL operations such as insert, delete and update commands.
3. We then ascertain a string variable, which is "SQL" to hold our SQL command string.
2. The next stride is to actually define the SQL argument which will exist used against our database. In our case, we are issuing an insert statement, which volition insert the tape of TutorialID=one and TutorialName=VB.Net
3. Side by side, nosotros create the command object which is used to execute the SQL statement against the database. In the SQL command, y'all have to laissez passer the connexion object and the SQL string
4. In our data adapter command, nosotros now associate the insert SQL command to our adapter. We also and so issue the ExecuteNonQuery method which is used to execute the Insert statement against our database. The 'ExecuteNonQuery' method is used in C# to issue whatever DML statements against the database. Past DML statements, we mean the insert, delete, and update operation. In C# , if you lot want to issue whatever of these statements against a tabular array, you need to employ the ExecuteNonQuery method.
5. We finally close all the objects related to our database functioning. Remember this is always a practiced practise.

When the above lawmaking is set, and the project is executed using Visual Studio, you lot will get the below output. Once the grade is displayed, click the Connect push button.

Output:-

If you become to SQL Server Express and see the rows in the demotb table, you volition see the row inserted as shown below

C# Update Database

But like Accessing data, C# has the power to update existing records from the database as well. To showcase how to update records into our database, permit'southward accept the same table structure which was used above.

TutorialID	TutorialName
one	C#
two	ASP.Cyberspace
3	VB.Internet

Permit's change the lawmaking in our form, so that we tin can update the post-obit row. The old row value is TutorialID every bit "iii" and Tutorial Proper name as "VB.Net". Which we will update information technology to "VB.Net complete" while the row value for Tutorial ID will remain same.

One-time row

TutorialID	TutorialName
3	VB.Net

New row

TutorialID	TutorialName
3	VB.Net complete

So permit'due south add the following lawmaking to our program. The beneath lawmaking snippet will exist used to update an existing record in our database.

C# SqlCommand Example With Code Explanation:-

1. The first step is to create the post-obit variables
1. SQLCommand – This data blazon 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.
2. The dataadapter object is used to perform specific SQL operations such every bit insert, delete and update commands.
3. Nosotros and then ascertain a cord variable, which is SQL to concur our SQL control string.
2. The side by side pace is to define the SQL argument which volition be used confronting our database. In our case we are issuing an update statement, this will update the Tutorial proper name to "VB.Internet Complete" while the TutorialID is unchanged and kept equally 3.
3. Adjacent, we will create the command object, which is used to execute the SQL statement confronting the database. In the SQL command, you lot have passed the connection object and the SQL cord.
4. In our data adapter command, we now acquaintance the insert SQL command to our adapter. We also then issue the ExecuteNonQuery method which is used to execute the Update statement confronting our database.
5. We finally close all the objects related to our database operation. Retrieve this is always a good do.

When the above code is prepare, and the project is executed using Visual Studio, yous volition get the below output. Once the form is displayed, click the Connect button.

Output:-

If you actually go to SQL Server Limited and come across the rows in the demotb table, yous volition see the row was successfully updated as shown below.

Deleting Records

Just like Accessing data, C# has the power to delete existing records from the database as well. To showcase how to delete records into our database, allow's accept the aforementioned table structure which was used above.

TutorialID	TutorialName
1	C#
2	ASP.Cyberspace
3	VB.Internet complete

Let's modify the code in our grade, so that we tin delete the post-obit row

TutorialID	TutorialName
three	VB.Cyberspace complete

So let's add the following lawmaking to our program. The beneath code snippet volition exist used to delete an existing tape in our database.

Code Explanation:-

1. The Central 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 tabular array in which the TutorialID has a value of 3.
2. In our data adapter command, we now associate the insert SQL control to our adapter. Nosotros also and so issue the ExecuteNonQuery method which is used to execute the Delete statement against our database.

When the above lawmaking is ready, and the projection is executed using Visual Studio, you volition get the below output. Once the course is displayed, click the Connect button.

Output:-

If you actually go to SQL Server Limited and see the rows in the demotb table, you lot volition run into the row was successfully deleted as shown below.

Connecting Controls to Information

In the earlier sections, we have seen how to we can apply C# commands such every bit SQLCommand and SQLReader to fetch information from a database. Nosotros as well saw how we read each row of the table and apply a messagebox to brandish the contents of a table to the user.

Just plainly, users don't want to run across data sent via message boxes and would desire better controls to brandish the data. Permit'southward have the beneath data construction in a table

TutorialID	TutorialName
1	C#
2	ASP.Internet
3	VB.Internet complete

From the above data construction, the user would ideally desire to see the TutorialID and Tutorial Name displayed in a textbox. Secondly, they might desire to have some sort of push button control which could allow them to go to the next record or to the previous record in the tabular array. This would require a bit of extra coding from the programmer's stop.

The skilful news is that C# tin reduce the boosted coding attempt by allowing binding of controls to information. What this ways 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 tin so 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 volition ensure that information technology writes the code for you to ensure the linkage works. Then when you lot run your awarding, the textbox controls volition automatically connect to the database, fetch the data and display it in the textbox controls. No coding is required from the programmer'south end to achieve this.

Let'southward await at a code instance of how we can accomplish bounden of controls.

In our case, nosotros are going to create ii textboxes on the windows grade. They are going to represent the Tutorial ID and Tutorial Name respectively. They volition be bound to the Tutorial ID and TutorialName fields of the database accordingly.

Let'due south follow the below-mentioned steps to reach this.

Footstep 1) Construct the basic grade. In the form drag and driblet 2 components- labels and textboxes. So carry out the post-obit substeps

1. Put the text value of the first label as TutorialID
2. Put the text value of the 2d label as TutorialName
3. Put the proper name property of the first textbox as txtID
4. Put the name holding of the second textbox as txtName

Below is the how the course would look like once the above-mentioned steps are performed.

Stride 2) The next pace is to add a binding Navigator to the form. The bounden Navigator command can automatically navigate through each row of the table. To add the binding navigator, merely get to the toolbox and drag it to the class.

Step 3) The adjacent step is to add a bounden to our database. This can be done by going to any of the Textbox command and clicking on the DataBindings->Text property. The Binding Navigator is used to found a link from your awarding to a database.

When you perform this step, Visual Studio will automatically add together the required code to the application to brand sure the awarding is linked to the database. Normally the database in Visual Studio is referred to as a Projection Information Source. So to ensure the connectedness is established between the awarding and the database, the first step is to create a project data source.

The post-obit screen volition testify up. Click on the link- "Add together Project Data Source". When you click on the projection data source, you will be presented with a wizard; this will let yous to define the database connection.

Step four) One time you click on the Add Project Data Source link, you lot will be presented with a wizard which volition be used to create a connectedness to the demotb database. The following steps show in item what needs to be configured during each step of the wizard.

1. In the screen which pops up , choose the Information Source blazon as Database and and then click on next button.

2. In the adjacent screen, you need to kickoff the creation of the connectedness string to the database. The connexion string is required for the application to constitute a connection to the database. It contains the parameters such as server name, database name, and the name of the commuter.
1. Click on the New connection button
2. Choose the Data Source as Microsoft SQL Server
3. Click the Go along button.

3. Next, y'all need to add the credentials to connect to the database
1. Choose the server name on which the SQL Server resides
2. Enter the user id and password to connect to the database
3. Choose the database as demotb
4. Click the 'ok' push.

4. In this screen, we volition confirm all the settings which were carried on the previous screens.
1. Choose the choice "Yes" to include sensitive data in the connection cord
2. Click on the "Adjacent" push.

5. In the next screen, click on the "Next" push button to confirm the cosmos of the connection string

6. In this step,

1. Choose the tables of Demotb, which will be shown in the next screen.
2. This table volition at present get an available information source in the C# projection

When you click the Finish button, Visual Studio will now ensure that the application can query all the rows in the table Demotb.

Footstep 5) Now that the data source is defined, we at present need to connect the TutorialID and TutorialName textbox to the demotb tabular array. When you click on the Text property of either the TutorialID or TutorialName textbox, you will at present see that the bounden source to Demotb is available.

For the first text box choose the Tutorial ID. Echo this pace for the 2d textbox and choose the field equally TutorialName. The below steps shows how we can navigate to each command and change the binding accordingly.

1. Click on the Tutorial ID control.

2. In the Properties window, you will run across the backdrop of the TutorialID textbox. Get to the text property and click on the downward arrow push button.

3. When you click the down arrow push button, you will run into the demotbBinding Source pick. And under this, you will come across the options of TutorialName and TutorialID. Cull the Tutorial ID 1.

Repeat the above three steps for the Tutorial Name text box.

1. So click on the Tutorial Name text box
2. Become to the properties window
3. Choose the Text property
4. Choose the TutorialName choice under demotbBindingSource

Step six) Side by side we need to change the Bounden Source holding of the BindingNavigator to bespeak to our Demotb information source. The reason we practice this is that the Binding Navigator also needs to know which table information technology needs to refer to.

The Binding Navigator is used to select the adjacent or previous record in the table. And so fifty-fifty though the data source is added to the project as a whole and to the text box command, we still need to ensure the Bounden Navigator as well has a link to our information source. In order to practise this, nosotros need to click the Bounden navigator object, go to the Binding Source property and choose the one that is bachelor

Next, nosotros demand to go to the Properties window so that we can make the modify to Binding Source property.

When all of the above steps are executed successfully, you will become the below-mentioned output.

Output:-

Now when the project is launched, you can see that the textboxes automatically get the values from the tabular array.

When you click the Next push button on the Navigator, information technology automatically goes to the next tape in the table. And the values of the next record automatically come in the text boxes

C# DataGridView

Information Grids are used to brandish information from a tabular array in a grid-like format. When a user sees's table information, they normally prefer seeing all the table rows in one shot. This can exist achieved if nosotros can display the data in a grid on the form.

C# and Visual Studio take inbuilt information grids, this can be used to display information. Let'southward take a wait at an case 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 tabular array.

Step 1) Drag the DataGridView control from the toolbox to the Course in Visual Studio. The DataGridView command is used in Visual Studio to display the rows of a table in a grid-like format.

Step two) In the adjacent footstep, nosotros need to connect our data grid to the database. In the concluding section, we had created a project information source. Let'southward utilize the same data source in our instance.

1. First, you need to choose the filigree and click on the arrow in the grid. This will bring upward the grid configuration options.
2. In the configuration options, only choose the data source as demotbBindingSource which was the data source created in the earlier section.

If all the above steps are executed equally shown, you volition get the below-mentioned output.

Output:-

From the output, you lot can encounter that the grid was populated by the values from the database.

Summary

• C# SQL can work with databases such equally Oracle and Microsoft SQL Server.
• This C# database tutorial has all the commands which are required to piece of work with databases. This involves establishing a connection to the database. You tin can perform operations such every bit select, update, insert and delete using the commands in C#.
• The DataReader object in C# is used to hold all the information returned by the database. The While loop in C# can be used to read the data rows one at a time.
• The information adapter object is used to perform SQL operations such every bit insert, delete, and update against the database.
• C# can bind controls to the diverse fields in a table. They are bound by defining a data source in C#. The data source is used to pull the information from the database and populate them in the controls.
• The binding navigator is used to automatically navigate through the rows in a tabular array.
• The data grid in C# can connect to the database and brandish all the values from the table in a grid-like format.

How To Retrieve Data From Database In C# Windows Application,

Source: https://www.guru99.com/c-sharp-access-database.html

Posted by: martincouseed1937.blogspot.com

Share this post