ASP.Net

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.

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

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

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

Wednesday, October 9, 2019

ASP.Net

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

Fundamentals of Database connectivity

ASP.NET Database Connections