All IT Courses 50% Off
Dotnet Tutorials

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:

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.

TutorialIDTutorialName
1C#
2ASP.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:

ASP.NET Database Connection

Insert Database Record using InsertCommand:

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

TutorialIDTutorialName
1C#
2ASP.NET

The new record to be added is:

TutorialIDTutorialName
3VB.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:

Insert, Update and Delete database records in Asp.Net

Update Database Record using UpdateCommand:

You can also update the existing records in the database. 

Let us take the same table.

TutorialIDTutorialName
1C#
2ASP.NET
3VB.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:

Asp.Net Database Connection

Delete Database Record using DeleteCommand:

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

TutorialIDTutorialName
3VB.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:

ASP.NET Database Connection

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:

TutorialIDTutorialName
1C#
2ASP.NET
3VB.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.

Asp.Net Database Connection

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

ASP.NET Database Connection

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

ASP.NET Database Connection

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

ASP.NET Database Connection

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

ASP.NET Database Connection

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.

ASP.NET Database Connection

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

Asp.Net Database Connection

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

Asp.Net Database Connection

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

ASP.NET Database Connection

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

ASP.NET Database Connection

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

ASP.NET Database Connection

Output:

Asp.Net Database Connection
Facebook Comments
Tags

Related Articles

Back to top button
Close
Close

Get Python Course
worth 499$ for FREE!

Offer valid for 1st 20 seats only, Hurry up!!

You have successfully subscribed to the newsletter

There was an error while trying to send your request. Please try again.

H2kinfosys Blog will use the information you provide on this form to be in touch with you and to provide updates and marketing.