ASP.Net has the ability to work with different types of databases. It can work with the most common databases such as Oracle and Microsoft SQL Server. It also has the ability to work with new forms of databases such as MongoDB and MySQL. In this tutorial, you will learn-
Fundamentals of Database connectivity ASP.NET Database Connection ASP.NET Read Database using SqlDataReader Insert Database Record using InsertCommand Update Database Record using UpdateCommand Delete Database Record using DeleteCommand Connecting Asp.net Controls to Data
Fundamentals of Database connectivity
ASP.Net has the ability to 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 with the Microsoft SQL Server as our database. For learning purposes, one can download and use the Microsoft SQL Server Express Edition. This is a free database software provided by Microsoft. While working with databases, the following concepts which are common across 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. Each connection can only work with one database at a time. Credentials – The next important aspect is the ‘username’ and ‘password’. This is used to establish a connection to the database. Optional parameters – You can specify optional parameters 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.
Selecting data from the database – Once the connection is established, data is fetched from the database. ASP.Net has the ability to execute ‘sql’ select command against the database. The ‘sql’ statement can be used to fetch data from a specific table in the database. Inserting data into the database – ASP.Net is used to insert records into the database. Values for each row that needs to be inserted in the database are specified in ASP.Net. Updating data into the database – ASP.Net can also be used to update existing records into the database. New values can be specified in ASP.Net for each row that needs to be updated into the database. Deleting data from a database – ASP.Net can also be used to delete records from the database. The code is written to delete a particular row from the database.
Ok, now that we have seen the theory part of each operation. Now, let’s see how to perform database operations in ASP.Net.
ASP.NET Database Connections
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
Let’s work with our current web application created in the earlier sections.
Start adding database operations to it. Our example look’s at establishing a simple connection. This connection is made to the Demodb database. This is done when the page is first launched. When the connection is established, a message will be sent to the user. The message will indicate that the connection has been established.
Let’s follow the below-mentioned steps to achieve this. Step 1) Let’s first ensure that you have your web application (DemoApplication) opened in Visual Studio. Double click the ‘demo.aspx.cs’ file to enter the code for the database connection.
Step 2) Add the below code which will be used to establish a connection to the database.
Code Explanation:-
The first step is to create variables. It will be used to create the connection string and the connection to the SQL Server database. The next step is to actually create 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.
Next, we assign the connecting string to the variable ‘cnn’.
The variable cnn is of type SqlConnection. This is used to establish a connection to the database. SqlConnection is a class in ASP.Net, which is used to create a connection to a database. To use this class, you have to first create an object of this class. Hence, here we create a variable called ‘cnn’ which is of the type SqlConnection.
Next, we use the open method of the cnn variable to open a connection to the database. We display a message to the user that the connection is established. This is done via the ‘response.write’ method. We then close the connection 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:-
The output message displayed in the browser will show that the connection to the database is made.
ASP.NET Read Database using SqlDataReader
To show data accessed using Asp.Net, let us assume 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 two columns, one called “TutorialID” and the other called “TutorialName.” For the moment, the table will have two rows as shown below.
Let’s change the code so that we can query for this data and display the information on the web page itself. Note that the code entered is in continuation to that written for the data connection module. Step 1) Let’s split the code into two parts,
The first part will be to construct our “select” statement. It will be used to read the data from the database. We will then execute the “select” statement against the database. This will 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 two string variables. One is “SQL” to hold our SQL command string. The next is the “Output” which will contain all the table values.
The next step is to actually define the SQL statement. This will be used against our database. In our case, it is “Select TutorialID, TutorialName from demotb”. This will fetch all the rows from the table demotb. Next, we create the command object which is used to execute the SQL statement against the database. In the SQL command, you have to pass the connection object and the SQL string. Next, we will execute the data reader command, which will fetch all the rows from the demotb table. Now that we have all the rows of the table with us, we need a mechanism to access the row one by one.
For this, we will use the ‘while’ statement. The ‘while’ statement will be used to access the rows from the data reader one at a time. We then use the ‘GetValue’ method to get the value of TutorialID and TutorialName.
Step 2) In the final step, we will just display the output to the user. Then we will close all the objects related to the database operation.
Code Explanation:-
We will continue our code by displaying the value of the Output variable. This is done using the Response.Write method. 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. 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 browser to the user.
Insert Database Record using InsertCommand
Just like Accessing data, ASP.Net has the ability to insert records into the database as well. Let’s take the same table structure used for inserting records. Let’s change the code in our form, so that we can insert the following row into the table Step 1) As the first step 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. These objects 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 insert, delete and update SQL commands We then define a string variable, which is “SQL” to hold our SQL command string.
The next step is actually to define the SQL statement, which will be used against our database. In our case, we are issuing an insert statement. This will insert the record of TutorialID=3 and TutorialName=VB.Net Next, we create the command object which is used to execute the SQL statement against the database. In the SQL command, you have to pass the connection object and the SQL string In our data adapter command,
Associate the insert SQL command to the adapter. Then issue the ‘ExecuteNonQuery’ method. This is used to execute the Insert statement against our database. The ‘ExecuteNonQuery’ method is used in C# to issue any DML statements (insert, delete and update operation) against the database. To issue any table statements in ASP.Net, one need’s to use the ‘ExecuteNonQuery’ method.
We finally close all the objects related to our database operation. Remember this is always a good practice.
Step 2) As a second step, let’s add the same code as in the Accessing data section. This is to display the recent table data in the browser. For that, we will add the below code to the demo.aspx.cs file.
When the above code is set, and the project is executed in Visual Studio, you will get the below output. Output:-
In the browser window, you will see that the rows was successfully inserted in the database.
Update Database Record using UpdateCommand
ASP.Net has the ability to update existing records from a database. Let’s take the same table structure which was used above for the example to insert records. 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 Step 1) As the first step let’s add the following code to our program. The below code snippet will be used to update an existing record in our database.
Code Explanation:-
The first step is to create the following variables
SQLCommand – his data type is used to define objects 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 insert, delete and update SQL commands We then define a string variable, which is SQL to hold our SQL command string.
The next step is actually to define the SQL statement which will be used against our database. In our case, we are issuing an ‘update’ statement. This will update the Tutorial name to “VB.Net Complete”. The TutorialID will remain unchanged, and the value will be 3. Next, we will create the command object. This is used to execute the SQL statement against the database. In the SQL command, you have passed the connection object and the SQL string In our data adapter command, we now associate the insert SQL command to our adapter. We then issue the ExecuteNonQuery method. This is used to execute the Update statement against our database. We finally close all the objects related to our database operation. Remember this is always a good practice.
Step 2) As a second step, let’s add the same code as in the Accessing data section. This is to display the recent table data in the browser. For that, we will add the below code
When the above code is set, and the project is executed using Visual Studio, you will get the below output. Output:-
In the browser window, you will see that the rows were successfully updated in the database.
Delete Database Record using DeleteCommand
ASP.Net can delete existing records from a database. Let’s take the same table structure which was used above for the example to delete records. 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. Step 1) As the first step 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 issue the ‘ExecuteNonQuery’ method which is used to execute the delete statement against our database.
Step 2) As a second step, let’s add the same code as in the Accessing data section. This is to display the recent table data in the browser. For that, we will add the below code.
When the above code is set, and the project is executed using Visual Studio, you will get the below output. Output:-
Connecting Asp.net Controls to Data
We have seen how we can use ASP.Net commands such as SQLCommand and SQLReader to fetch data from a database. We also saw how we can read each row of the table display it on the web page. There are methods available to link controls directly to different fields in the table. At the moment, only the below controls can be bound to an ASP.Net application
CheckboxList RadioButtonList DropDownlist Listbox
So let’s see an example of how we can use control binding in ASP.Net. Here we will take a listbox example. Let’s say we have the following data in our database. Let’s use the Listbox control and see how it can automatically pick up the data from our Demotb table. Let’s follow the below-mentioned steps to achieve this. Step 1) Construct the basic web form. From the toolbox in Visual Studio, drag and drop 2 components- labels and Listboxes. 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
Below is how the form would look like once the above-mentioned steps are performed.
Step 2) The next step is to start connecting each listbox to the database table.
First, click on the Listbox for Tutorial ID. This will bring up another dialog box to the side of the control. From the dialog box, we need to click on the option of Choose Data source.
Step 3) You will then be presented with a dialog box. This can be used to create a new data source. The data source will represent a connection to the database. Choose the option of ‘New data source’.
Step 4) The below screen will be prompted after choosing the new data source in the last step. Here we need to mention the type of data source we want to create.
Choose the database option to work with an SQL Server database. Now we need to give a name to our data source. Here we are giving it a name of DemoDataSource. Finally, we click the ‘OK’ button to proceed to the next screen.
Step 5) Now we need to create a connection to our database. In the next screen, click on the New Connection button
Step 6) 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.
Step 7) On the next screen, you will be able to see the Demotb table. Just click on the Next button to accept the default setting.
Step 8) You will now be able to test the connection on the next screen.
Click on the Test Query button to just see if you are able to get the values from the table Click the Finish button to complete the wizard.
Step 9) Now on the final screen, you can click the ‘OK’ button. This will now bind the TutorialID listbox to the TutorialID field name in the ‘demotb’ table.
Step 10) Now it’s time to bind the Tutorial Name listbox to the Tutorial Name field.
First, click on the Tutorial Name Listbox. Next, Choose from Data Source in the dialog box which appears at the side of the Listbox.
Step 11) You will already see the DemoDataSource when choosing the Data Source in the next screen.
Choose the DemoData Source Click on the OK button.
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 listboxes display the Tutorial ID and Tutorial Names respectively Summary
ASP.Net can work with databases such as Oracle and Microsoft SQL Server. ASP.Net has all the commands which are required to work with databases. This involves establishing a connection to the database. You can perform operations such as select, update, insert and delete. The datareader object in ASP.Net is used to hold all the data returned by the database. The While loop in ASP.net can be used to read the data rows one at a time. The data adapter object is used to perform SQL operations such as insert, delete, and update. ASP.Net can bind controls to the various fields in a table. They are bound by defining a data source in ASP.Net. The data source is used to pull the data from the database and populate them in the controls.
