Programmingempire
Today I will give some examples of the Connected and Disconnected Approach in ADO.NET. Basically, the ADO.NET library in .NET Framework provides the functionality for database access. This library offers lots of classes and methods for fetching and manipulating data from any data source.
The following examples demonstrate how to perform database operations using these two approaches. As an illustration, the database we use in all of these examples is db1.mdf. In case, you need to know how to create a database in Visual Studio, follow this link. Also, we will use the same table named Book in these examples. The structure of the Book table is shown below.
Examples of Connected and Disconnected Approach in ADO.NET
There are two architectures in ADO.NET for database access – Connected Architecture and Disconnected Architecture. While the connected approach requires the connection with the database to remain established throughout, the disconnected approach closes the connection once the data is fetched. After that, all computations are done offline, and later the database is updated.
Connected Approach
To explain, the connected approach, a simple example of fetching data and displaying it on console is shown below. As can be seen, first we create an object of SqlConnection class with the ConnectionString property of the database and open the connection. After that, create an object of SqlCommand class and set its properties. Finally, call the ExecuteReader() method of the SqlCommand class and retrieve the data in a SqlDataReader object. Further, use the Read() method to visit each row and get the value of each field of a row.
using System;
using System.Data;
using System.Data.SqlClient;
namespace DatabaseExample1
{
class Program
{
// Connected Approach for Database Access in ADO.NET
static void Main(string[] args)
{
SqlConnection con1 = new SqlConnection(@"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=C:UsersKAVITADocumentsdb1.mdf;Integrated Security=True;Connect Timeout=30");
con1.Open();
SqlCommand cmd = new SqlCommand();
string str = "select * from Book";
cmd.CommandText = str;
cmd.Connection = con1;
SqlDataReader dr= cmd.ExecuteReader();
Console.WriteLine("Book Records...");
while(dr.Read())
{
int id = Int32.Parse(dr[0].ToString());
string title = dr[1].ToString();
Console.WriteLine($"Book ID: {id}, Book Title: {title}");
}
}
}
}
Output
Disconnected Approach
In like manner, we will use the disconnected approach to fetch and display the data from the Book table. But this time, we don’t need any command object. Instead, we use an object of SqlDataAdapter class and call its Fill() method to fetch the data in a Dataset object. Finally, use a foreach loop to visit each row and display the value of each field.
using System;
using System.Data;
using System.Data.SqlClient;
namespace DatabaseExample2
{
// Disconnected Approach for Database Access in ADO.NET
class Program
{
static void Main(string[] args)
{
SqlConnection con1 = new SqlConnection(@"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=C:UsersKAVITADocumentsdb1.mdf;Integrated Security=True;Connect Timeout=30");
con1.Open();
SqlDataAdapter da = new SqlDataAdapter("select * from Book",con1);
DataSet ds = new DataSet();
da.Fill(ds, "Book");
foreach(DataRow dr in ds.Tables["Book"].Rows)
{
int x = Int32.Parse(dr[0].ToString());
string title = dr[1].ToString();
Console.WriteLine($"Book ID: {x}, Book Title: {title}");
}
}
}
}
Output
Complete Example of Data Access and manipulation using Connected Approach
The following example shows how to perform insert, update, delete, and select operations using the connected approach. As shown below, fetching data in a Data Reader requires calling ExecuteReader() method of the SqlCommand class. Similarly, for insert, update, and delete operations we use the ExecuteNonQuery() method.
using System;
using System.Data;
using System.Data.SqlClient;
namespace DatabaseExample3
{
namespace ConnectedApproachDemo1
{
class Program
{
SqlConnection con;
SqlCommand cmd;
SqlDataReader dr;
int n;
static void Main(string[] args)
{
Program ob = new Program();
int ch;
while (true)
{
Console.WriteLine("Select a Database Operation: ");
Console.WriteLine(@"1. Show Records
2. Insert Record
3. Update Record
4. Delete Record
5. Exit");
Console.WriteLine("Enter Your Choice: ");
ch = Int32.Parse(Console.ReadLine());
switch (ch)
{
case 1: //Display Records
ob.ShowRecords();
break;
case 2:
Console.WriteLine("Insert a Record...");
//Insert a Record
ob.InsertRecord();
break;
case 3: //Update Record
Console.WriteLine("Update Record...");
ob.UpdateRecord();
break;
case 4: //Delete Record
Console.WriteLine("Delete Record...");
ob.DeleteRecord();
break;
break;
case 5:
Environment.Exit(0);
break;
default:
Console.WriteLine("Invalid Choice Entered!");
break;
}
}
}
public void Connect()
{
con = new SqlConnection(@"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=C:UsersKAVITADocumentsdb1.mdf;Integrated Security=True;Connect Timeout=30");
con.Open();
}
public void ShowRecords()
{
Connect();
cmd = new SqlCommand("select * from Book");
cmd.Connection = con;
dr = cmd.ExecuteReader();
string str = "";
while (dr.Read())
{
int x = Int32.Parse(dr[0].ToString());
string s1 = dr[1].ToString();
str = "Book ID: " + x + ", Book Title: " + s1;
Console.WriteLine(str);
}
dr.Close();
con.Close();
}
public void InsertRecord()
{
Connect();
Console.WriteLine("Enter Book ID: ");
int x = Int32.Parse(Console.ReadLine());
Console.WriteLine("Enter Book Title: ");
string s1 = Console.ReadLine();
string cmdtext = "insert into Book values('" + x + "', '" + s1+"')";
cmd = new SqlCommand(cmdtext);
cmd.Connection = con;
n = cmd.ExecuteNonQuery();
if (n > 0)
{
Console.WriteLine("Record Inserted Successfully!");
}
else
{
Console.WriteLine("Record not inserted!");
}
con.Close();
//Display all records
ShowRecords();
}
public void UpdateRecord()
{
Connect();
Console.WriteLine("Enter Book Id in which you want an updated value:");
int x = Int32.Parse(Console.ReadLine());
Console.WriteLine("Enter New Value of Book Title: ");
string y = Console.ReadLine();
string cmdtext = "update Book set book_title = '" + y + "' where book_id = " + x;
cmd = new SqlCommand(cmdtext);
cmd.Connection = con;
n = cmd.ExecuteNonQuery();
if (n > 0)
{
Console.WriteLine("Record Updated Successfully!");
}
else
{
Console.WriteLine("Record not Updated!");
}
con.Close();
//Display all records
ShowRecords();
}
public void DeleteRecord()
{
Connect();
Console.WriteLine("Enter Book Id which you want to delete:");
int x = Int32.Parse(Console.ReadLine());
string cmdtext = "delete Book where book_id = " + x;
cmd = new SqlCommand(cmdtext);
cmd.Connection = con;
n = cmd.ExecuteNonQuery();
if (n > 0)
{
Console.WriteLine("Record Deleted Successfully!");
}
else
{
Console.WriteLine("Record not Deleted!");
}
con.Close();
//Display all records
ShowRecords();
}
}
}
}
Output
Complete Example of Disconnected Approach
To demonstrate the disconnected approach, we will perform all the above operations on the Book table. Further, we use the objects of SqlDataAdaper, and DataSet along with an object of SqlConnection class. In similar way, the Connection object uses the ConnectionString property to create a connection with the database.
After that, we call the Open() method to open the connection and the Data Adapter will now use this connection. Finally, we fetch the data in an object of DataSet as given in the FetchData() method. Regardless of the database operation (such as insert, update, delete, or select), the manner in which data is retrieved remains same, that is, by calling the Fill() method.
Moreover, in the case of insert, update, and delete, the way in which data is updated in the physical database is also the same, that is, by calling the Update() method of Data Adapter. Accordingly, the Insert operation requires that we first call the NewRow() method to create a blank row and assign the values to each field. Then call the Add() method from the Rows collection in the DataTable object. Finally, call the Update() method to update the database.
Similarly, the Update operation also requires first to search for the appropriate row in the table and make necessary changes. Likewise, the Delete operation also searches for the appropriate row, and then the Delete() method is called for that row. Finally, the Update() method of the DataAdapter is called to reflect the changes in the database.
using System;
using System.Data;
using System.Data.SqlClient;
namespace DatabaseExample4
{
//Disconnected approach for database access
class Program
{
SqlConnection con;
SqlDataAdapter da;
DataSet ds;
int n;
static void Main(string[] args)
{
Program ob = new Program();
int ch;
while (true)
{
Console.WriteLine("Select a Database Operation: ");
Console.WriteLine(@"1. Show Records
2. Insert Record
3. Update Record
4. Delete Record
5. Exit");
Console.WriteLine("Enter Your Choice: ");
ch = Int32.Parse(Console.ReadLine());
switch (ch)
{
case 1: //Display Records
ob.ShowRecords();
break;
case 2:
Console.WriteLine("Insert a Record...");
//Insert a Record
ob.InsertRecord();
break;
case 3: //Update Record
Console.WriteLine("Update Record...");
ob.UpdateRecord();
break;
case 4: //Delete Record
Console.WriteLine("Delete Record...");
ob.DeleteRecord();
break;
case 5:
Environment.Exit(0);
break;
default:
Console.WriteLine("Invalid Choice Entered!");
break;
}
}
}
public void Connect()
{
con = new SqlConnection(@"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=C:UsersKAVITADocumentsdb1.mdf;Integrated Security=True;Connect Timeout=30");
con.Open();
}
public void FetchData()
{
Connect();
da = new SqlDataAdapter("select * from Book", con);
ds = new DataSet();
da.Fill(ds, "Book");
}
public void ShowRecords()
{
FetchData();
string str = "";
foreach(DataRow dr in ds.Tables["Book"].Rows)
{
int x = Int32.Parse(dr[0].ToString());
string s1 = dr[1].ToString();
str = "Book ID: " + x + ", Book Title: " + s1;
Console.WriteLine(str);
}
con.Close();
}
public void InsertRecord()
{
FetchData();
SqlCommandBuilder c = new SqlCommandBuilder(da);
Console.WriteLine("Enter Book ID: ");
int x = Int32.Parse(Console.ReadLine());
Console.WriteLine("Enter Book Title: ");
string s1 = Console.ReadLine();
DataRow dr = ds.Tables["Book"].NewRow();
dr[0] = x;
dr[1] = s1;
ds.Tables["Book"].Rows.Add(dr);
da.Update(ds, "Book");
con.Close();
//Display all records
ShowRecords();
}
public void UpdateRecord()
{
FetchData();
SqlCommandBuilder c = new SqlCommandBuilder(da);
Console.WriteLine("Enter Book Id in which you want an updated value:");
int x = Int32.Parse(Console.ReadLine());
Console.WriteLine("Enter New Value of Book Title: ");
string y = Console.ReadLine();
foreach(DataRow dr in ds.Tables["Book"].Rows)
{
int x1 = Int32.Parse(dr[0].ToString());
if (x == x1)
dr[1] = y;
}
da.Update(ds, "Book");
con.Close();
//Display all records
ShowRecords();
}
public void DeleteRecord()
{
FetchData();
SqlCommandBuilder c = new SqlCommandBuilder(da);
Console.WriteLine("Enter Book Id which you want to delete:");
int x = Int32.Parse(Console.ReadLine());
foreach (DataRow dr in ds.Tables["Book"].Rows)
{
int x1 = Int32.Parse(dr[0].ToString());
if (x == x1)
{
dr.Delete();
break;
}
}
da.Update(ds, "Book");
con.Close();
//Display all records
ShowRecords();
}
}
}
Summary
In this article on Examples of Connected and Disconnected Approach in ADO.NET, I have explained the Connected and Disconnected approaches of database access and manipulation. While the connected approach uses the objects of connection, command, and data reader, the disconnected approach makes use of the connection, data adapter, and DataSet objects. Additionally, an object of CommandBuilder class is also required to perform insert, update, and delete operations in the disconnected approach.
- Angular
- ASP.NET
- C
- C#
- C++
- CSS
- Dot Net Framework
- HTML
- IoT
- Java
- JavaScript
- Kotlin
- PHP
- Power Bi
- Python
- Scratch 3.0
- TypeScript
- VB.NET
