C#

Examples of Connected and Disconnected Approach in ADO.NET

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.

Structure of the Book table
Structure of the Book table

Examples of Connected and Disconnected Approach in ADO.NET

There are two architectures in ADO.NET for database access – Connected Architecture and the 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 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 an 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:\Users\KAVITA\Documents\db1.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

Fetching Data using Connected Approach
Fetching Data using Connected Approach

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:\Users\KAVITA\Documents\db1.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

Fetching Data using Disconnected Approach
Fetching Data using Disconnected Approach

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:\Users\KAVITA\Documents\db1.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

Select Query using Connected Approach
Select Query using Connected Approach
Insert Query Using Connected Approach
Insert Query Using Connected Approach
Update Query using Connected Approach
Update Query using Connected Approach
Delete Query using Connected Approach
Delete Query using Connected Approach

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:\Users\KAVITA\Documents\db1.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.


Related Topics

A Beginner’s Tutorial on WPF in C#

Everything about Tuples in C# and When to Use?

Linear Search and Binary Search in C#

Creating Jagged Arrays in C#

Learning Indexers in C#

Understanding Method Parameter Modifiers in C#

Object Initializers in C#

Examples of Static Constructors in C#

When should We Use Private Constructors?

C# Practice Questions

C# Basic Examples

Private and Static Constructors in C#

Constructors in C#

C# Arrays

C# Examples

How to Create a C# Console Application

Creating Navigation Window Application Using WPF in C#

LINQ To SQL Examples

Understanding the Concept of Nested Classes in C#

How to Setup a Connection with SQL Server Database in Visual Studio

Examples of Extension Methods in C#

Learning All Class Members in C#

Access Modifiers in C#

C# Root Class – Object

KeyValuePair and its Applications

IEnumerable and IEnumerator Interfaces

IEqualityComparer Interface

New Features in C# 9

Generic IList Interface and its Implementation in C#

Leave a Reply

Your email address will not be published. Required fields are marked *