ASP.NET Database Connection

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:

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

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.

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.

The new record to be added is:

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();
  }
}
}

Initial Steps in Configuring a DataAdapter

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 first step in configuring a DataAdapter is to select the appropriate command objects that the DataAdapter will use to interact with the data source. This selection process involves specifying the SQL statements or stored procedures that define how data is retrieved, inserted, updated, and deleted within the database.

To begin, you must create and configure the necessary command objects. Typically, these are the SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand. The SelectCommand is the most crucial, as it defines the query or stored procedure used to fetch data from the database. This command object must be set with a valid SQL query or stored procedure call that retrieves the desired data set.

Once the SelectCommand is configured, the DataAdapter can use it to fill a DataSet or DataTable with data from the database. Configuring the other command objects (InsertCommand, UpdateCommand, and DeleteCommand) ensures that changes made to the DataSet or DataTable can be accurately reflected back in the database, maintaining data integrity and consistency.

By carefully selecting and configuring these command objects, you ensure that the DataAdapter operates efficiently and accurately within your application’s data management processes.

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.

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:

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:

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.

Step 7: Now, you will see the Demotable table. You only need to click on the Next button to accept the default setting.

Step 8: Click on the Test Query button to check if can get the values from the table. Click the Finish button.

Step 9: Click the OK button to bind the TutorialID listbox to the TutorialID field name in the demotable table.

Step 10: Click on the Tutorial Name Listbox and choose Data Source in the dialog box.

Step 11: Choose the DemoData Source and click on the OK button.

Output: