Insert, Update, Delete: ASP.NET Database Connection Tutorial

Details

Accessing Data from a database is an important aspect of any programming language. It is necessary for any programming language to have the ability to work with databases.

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 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.

  1. 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.
    1. Database name or Data Source – The first important parameter is the database name. Each connection can only work with one database at a time.
    2. Credentials – The next important aspect is the 'username' and 'password'. This is used to establish a connection to the database.
    3. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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

Let's work with our current web application created in the earlier sections.

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.

Insert, Update and Delete database records in Asp.Net

Step 2) Add the below code which will be used to establish a connection to the database.

Insert, Update and Delete database records in Asp.Net 

namespace DemoApplication
{  
	public partial class Demo  System.Web.UI.Page  
    {  
	  protected void Page_Load(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();  
			
		Response.Write("Connection MAde");    
		conn.Close();  
			
	  }
	}
}

Code Explanation:-

  1. The first step is to create variables. It will be used to create the connection string and the connection to the SQL Server database.
  2. The next step is to actually create the connection string. The connection string consists of the following parts
  1. Next, we assign the connecting string to the variable 'cnn'.
  1. 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:-

Insert, Update and Delete database records in Asp.Net

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.

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

TutorialID

TutorialName

1

C#

2

ASP.Net

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,

Insert, Update and Delete database records in Asp.Net

Code Explanation:-

  1. The first step is to create the following variables
  1. 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.
  2. 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.
  3. Next, we will execute the data reader command, which will fetch all the rows from the demotb table.
  4. Now that we have all the rows of the table with us, we need a mechanism to access the row one by one.

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.

Insert, Update and Delete database records in Asp.Net 

namespace DemoApplication
{  
	public partial class Demo System.Web.UI.Page  
    {  
	  protected void Page_Load(object sender, EventArgs e)  
	  {  
		SqlCommand command;
		SqlDataReader dataReader;
		String sql, Output =" ";
		sql = "Select TutorialID,TutorialName from demotb";
		
		command = new SqlCommand(sql, cnn);
		
		dataReader = sqlquery.ExecuteReader();
		while (dataReader.Read())
		{
		  Output = Output + dataReader.GetValue(0) + "-" + dataReader.GetValue(1) + "</br>";
		}
		
		Response.Write(Output); 
		dataReader.Close();
		command.dispose();
		conn.Close(); 
			
	  }
	}
}

Code Explanation:-

  1. We will continue our code by displaying the value of the Output variable. This is done using the Response.Write method.
  2. 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:-

Insert, Update and Delete database records in Asp.Net

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.

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

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.

Insert, Update and Delete database records in Asp.Net 

namespace DemoApplication
{  
	public partial class Demo System.Web.UI.Page  
    {  
	  protected void Page_Load(object sender, EventArgs e)  
	  {  
		SqlCommand command;
		SqlDataAdapter adapter = new SqlDataAdapter(); 
		String sql="";
		
		sql = "Insert into demotb(TutorialID,TutorialName) value(3, '" + "VB.Net +"')";
		
		command = new SqlCommand(sql,cnn);
		adapter.InsertCommand = new SqlCommand(sql,cnn); 
		adapter.InsertCommand.ExecuteNonQuery();

		command.Dispose(): 
		cnn.Close();
				
	  }
	}
}

Code Explanation:-

  1. The first step is to create the following variables
    1. 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.
    2. The DataAdapter object is used to perform insert, delete and update SQL commands
    3. We then define a string variable, which is "SQL" to hold our SQL command string.
  2. 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
  3. 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
  4. In our data adapter command,
  1. 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.

Insert, Update and Delete database records in Asp.Net 

namespace DemoApplication
{  
	public partial class Demo System.Web.UI.Page  
    {  
	  protected void Page_Load(object sender, EventArgs e)  
	  {  
		SqlCommand sqlquery;
		SqlDataReader dataReader;
		String Output =" ";
		sql = "Select TutorialID,TutorialName from demotb";
		
		sqlquery = new SqlCommand(sql, cnn);
		
		dataReader = command.ExecuteReader();
		while (dataReader.Read())
		{
		  Output = Output + dataReader.GetValue(0) + "-" + dataReader.GetValue(1) + "</br>";
		}
		
		Response.Write(Output); 
		dataReader.Close();
		command.dispose();
		conn.Close(); 		
	  }
	}
}

When the above code is set, and the project is executed in Visual Studio, you will get the below output.

Output:-

Insert, Update and Delete database records in Asp.Net

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.

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

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.

Insert, Update and Delete database records in Asp.Net 

namespace DemoApplication
{  
	public partial class Demo System.Web.UI.Page  
    {  
	  protected void Page_Load(object sender, EventArgs e)  
	  {  
		SqlCommand command;
		SqlDataAdapter adapter = new SqlDataAdapter(); 
		String sql="";
		
		sql = "Update demotb set TutorialName='"VB.Net Complete"+"' where TutorialID=3";
		
		command = new SqlCommand(sql,cnn);
		
		adapter.InsertCommand = new SqlCommand(sql,cnn); 
		adapter.InsertCommand.ExecuteNonQuery;
		
		command.Dispose(): 
		cnn.Close();
				
	  }
	}
}

Code Explanation:-

  1. The first step is to create the following variables
    1. 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.
    2. The dataadapter object is used to perform insert, delete and update SQL commands
    3. We then define a string variable, which is SQL to hold our SQL command string.
  2. 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.
  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
  4. 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.
  5. 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

Insert, Update and Delete database records in Asp.Net 

namespace DemoApplication
{  
	public partial class Demo System.Web.UI.Page  
    {  
	  protected void Page_Load(object sender, EventArgs e)  
	  {  
		SqlCommand sqlquery;
		SqlDataReader dataReader;
		String Output =" ";
		sql = "Select TutorialID,TutorialName from demotb";
		
		sqlquery = new SqlCommand(sql, cnn);
		
		dataReader = command.ExecuteReader();
		
		while (dataReader.Read())
		{
		  Output = Output + dataReader.GetValue(0) + "-" + dataReader.GetValue(1) + "</br>";
		}
		
		Response.Write(Output); 
		dataReader.Close();
		command.dispose();
		conn.Close(); 		
	  }
	}
}

When the above code is set, and the project is executed using Visual Studio, you will get the below output.

Output:-

Insert, Update and Delete database records in Asp.Net

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.

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.

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.

Insert, Update and Delete database records in Asp.Net 

namespace DemoApplication
{  
	public partial class Demo System.Web.UI.Page  
    {  
	  protected void Page_Load(object sender, EventArgs e)  
	  {  
		SqlCommand command;
		SqlDataAdapter adapter = new SqlDataAdapter(); 
		String sql="";
		
		sql = "Delete demotb where TutorialID=3";
		
		command = new SqlCommand(sql,cnn);
		
		adapter.DeleteCommand = new SqlCommand(sql,cnn); 
		adapter.DeleteCommand.ExecuteNonQuery;
		
		command.Dispose(): 
		cnn.Close();
				
	  }
	}
}

Code Explanation:-

  1. 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.
  2. 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.

Insert, Update and Delete database records in Asp.Net 

namespace DemoApplication
{  
	public partial class Demo System.Web.UI.Page  
    {  
	  protected void Page_Load(object sender, EventArgs e)  
	  {  
		SqlCommand sqlquery;
		SqlDataReader dataReader;
		String Output ="";
		sql = "Select TutorialID,TutorialName from demotb";
		
		sqlquery = new SqlCommand(sql, cnn);
		
		dataReader = command.ExecuteReader();
		
		while(dataReader.Read())
		{
		  Output = Output + dataReader.GetValue(0) + "-" + dataReader.GetValue(1) + "</br>";
		}
		
		Response.Write(Output); 
		dataReader.Close();
		command.dispose();
		conn.Close(); 		
	  }
	}
}

When the above code is set, and the project is executed using Visual Studio, you will get the below output.

Output:-

Insert, Update and Delete database records in Asp.Net

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

  1. CheckboxList
  2. RadioButtonList
  3. DropDownlist
  4. 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.

TutorialID

TutorialName

1

C#

2

ASP.Net

3

VB.Net complete

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;

  1. Put the text value of the first label as TutorialID
  2. 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.

Insert, Update and Delete database records in Asp.Net

Step 2) The next step is to start connecting each listbox to the database table.

  1. First, click on the Listbox for Tutorial ID. This will bring up another dialog box to the side of the control.
  2. From the dialog box, we need to click on the option of Choose Data source.

Insert, Update and Delete database records in Asp.Net

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'.

Insert, Update and Delete database records in Asp.Net

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.

  1. Choose the database option to work with an SQL Server database.
  2. Now we need to give a name to our data source. Here we are giving it a name of DemoDataSource.
  3. Finally, we click the 'OK' button to proceed to the next screen.

Insert, Update and Delete database records in Asp.Net

Step 5) Now we need to create a connection to our database. In the next screen, click on the New Connection button

Insert, Update and Delete database records in Asp.Net

Step 6) Next you 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' button.

Insert, Update and Delete database records in Asp.Net

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.

Insert, Update and Delete database records in Asp.Net

Step 8) You will now be able to test the connection on the next screen.

  1. Click on the Test Query button to just see if you are able to get the values from the table
  2. Click the Finish button to complete the wizard.

Insert, Update and Delete database records in Asp.Net

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.

Insert, Update and Delete database records in Asp.Net

Step 10) Now it's time to bind the Tutorial Name listbox to the Tutorial Name field.

  1. First, click on the Tutorial Name Listbox.
  2. Next, Choose from Data Source in the dialog box which appears at the side of the Listbox.

Insert, Update and Delete database records in Asp.Net

Step 11) You will already see the DemoDataSource when choosing the Data Source in the next screen.

  1. Choose the DemoData Source
  2. Click on the OK button.

Insert, Update and Delete database records in Asp.Net

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

Output:-

Insert, Update and Delete database records in Asp.Net

From the output, you can see that the listboxes display the Tutorial ID and Tutorial Names respectively

Summary

 

YOU MIGHT LIKE: