ASP.NET Database Connection

StevenSeptember 10, 2020

30,556 6 minutes read

ASP.NET Database Connection can connect with most databases, including Oracle, Microsoft SQL Server, MongoDB, and MySQL.

Here we are going to use Microsoft SQL Server as our database. Microsoft SQL Server is free database software, and one can easily download and install Microsoft SQL Server Express Edition.

The below concepts are common in all databases:

Connection: While working with a database, the first step is to establish a connection. To establish a connection, the following parameters are required:

Database name or data source: The name of the database to which the connection can be made. You can work with only one database at a time.
Credentials: It includes the username and password that are required to establish an ASP.NET Database Connection to the database. Credential is required for SQL Server Authentication and it is optional for Windows Authentication.
Optional Parameters: You can also specify optional parameters to provide more information on how .NET should be connected to the database to handle the data.

Selecting data from the database: Once the connection is established, data can be fetched from the database by executing SQL select command against the database. The SQL statement is used to fetch data from a specific table in the database.
Inserting data into the database: You can insert new records into the database and the values for each row that are to be inserted in the database should be specified.
Updating data into the database: Existing records can also be updated into the database. New values should be specified for each row that needs to be updated into the database.
Deleting data from the database: Records can also be deleted from the database.

ASP.NET Database Connection

We will connect to a database that has the name of Sampledb. The credentials that are needed to connect to the database are:

Username- dbtest
Password- sample123

To establish a connection, open your web application project and write the below SQL Server Connection String in Web.config file:

<connectionStrings>
    <add name="ConString" connectionString=" Data Source=WIN-50GP30FGO75;Initial Catalog=Demodb ;User ID=dbtest;Password=samplel23"/>
</connectionStrings>

Then, add System.Configuration Reference by right clicking on the project and click Add Reference option from the Context Menu.

From the dialog box Add Reference, click on .Net Tab and look for System.Configuration assembly. Select the component and click OK.

Once you have added the reference, it will appear in the References folder of the Solution Explorer.

Now, write the behind code to get the connection string from web.config file:

using System;
using System.Configuration;
namespace DemoApplication
{  
public partial class Demo  System.Web.UI.Page  
    {  
  protected void PageLoad(object sender, EventArgs e)  
  {  
string connetionString;
SqlConnection cnn;       
connetionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
cnn = new SqlConnection(connetionString);
cnn.Open();  
Response.Write("Connection Made");    
conn.Close();  
}
}
}

Here, the Data Source is the name of the server on which the database resides. Initial Catalog denotes the name of the database. User ID and Password are the credentials that are required.

Output:

Insert, Update and Delete database records in Asp.Net

Read Database using SqlDataReader:

Suppose you have a table named demotable used to store the ID and names of various Tutorials. The table contains two columns, one is TutorialID, and the other is TutorialName.

TutorialID	TutorialName
1	C#
2	ASP.NET

For this, write the below code to read the data using a select statement.

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

Here SQLCommand is a predefined class used to perform operations of reading and writing into the database. The DataReader object is used to retrieve all the data specified by the SQL query and we can read all the table rows one by one with the help of the data reader.

Output:

Insert Database Record using InsertCommand:

Suppose in the existing demotable; you want to insert a new record.

TutorialID	TutorialName
1	C#
2	ASP.NET

The new record to be added is:

TutorialID	TutorialName
3	VB.NET

Write the below code to insert a new record in the existing table:

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

Here, SQLCommand is used to define objects that perform SQL operations against a database. This object will hold the SQL command that will run against our SQL Server database.

The DataAdapter object is used to perform the insert, delete, and update SQL commands.

Now we will write the below code to display the new record in the browser:

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

Output:

Update Database Record using UpdateCommand:

You can also update the existing records in the database.

Let us take the same table.

TutorialID	TutorialName
1	C#
2	ASP.NET
3	VB.NET

Suppose we want to update the TutorialName VB.NET to VB.NET complete.

For this, we need to add the below code:

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

Now, to see the above changes in the browser, you need to add the below code:

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

Output:

Delete Database Record using DeleteCommand:

Suppose you want to delete the below record from the demotable:

TutorialID	TutorialName
3	VB.NET complete

For this, you need to add the below code:

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

Now to see the recent changes in the browser write the below code:

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

Output:

Connecting Asp.net Controls to Data:

There are some methods available in ASP.NET to link controls directly to different fields in the table.

CheckboxList
RadioButtonList
DropDownlist
Listbox

Let us see how we can use control binding in ASP.Net. We are taking an example of a listbox.

Suppose we have the following table:

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

Step 1: Create a basic web form. Using the toolbox in Visual Studio, drag and drop two components: labels and Listboxes. Name the text value of the first label as TutorialID and second label as TutorialName.

Step 2: Click on the Listbox for Tutorial ID and then click on Choose Data source from the dialog box.

Step 3: Create a new data source by choosing the option of New data source.

Step 4: Choose the database option and give a name to our data source such as DemoDataSource. Click the OK button.

Step 5: Create a connection to our database by clicking on the New Connection button.

Step 6: Choose the server name on which the SQL Server resides. Enter the credentials that are needed to connect to the database. Choose the database as demotable and click on OK.