Disconnected Mode in ADO.NET using C# with Example

C# Disconnected Mode in ADO.NET with Example

In Disconnected Mode in ADO.NET using C# architecture, there is no need to open connection to access data from database and no need to make connection alive while we perform insert, update, delete and search operation and after retrieve data from database no need connection close.

In Disconnected Mode ADO.NET, DataAdapter is used as a Dataprovider that provides communication between DataSet and database . This can be done using fill method to fill Data into DataSet and after modification in dataset data, We use DataAdapter update method to update data into Database. In Disconnected Mode in ADO.NET, DataSet can hold multiple table data.So DataSet is also called as virtual Database Because in connected mode when you constantly trips to the database for any (I U D S operation) insert, update, delete and search operation you wish to do. This creates more traffic to the database. So the limitation of Connected Mode ADO.NET is removed under Disconnected Mode in Ado.Net.

Also Read:- ConnectedMode in ADO.NET using C#

Example:-

Create a Table in Database having following fields as in screenshot:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace WindowsFormsApplication1

{

    public partial class Disconnected_Mode : Form

    {

        public Disconnected_Mode()

        {

            InitializeComponent();

        }

// Save button coding to Save data into Database Table.

        private void btnsave_Click(object sender, EventArgs e)

        {

            SqlConnection con=new SqlConnection(@"Data Source=MALIK\MALIK;Initial Catalog=smalik;Integrated Security=True");

            SqlDataAdapter da = new SqlDataAdapter("Select * from tb_test", con);

//Coding to Fill Data into Dataset using DataAdapter

                     DataSetds = new DataSet();

            da.Fill(ds);

//Coding to Save Data into Dataset

            DataTable dt = ds.Tables[0];

            DataRow dr = dt.NewRow();

            dr[0] = txtrollno.Text;

            dr[1] = txtfirstname.Text;

            dr[2] = txtlastname.Text;

            dt.Rows.Add(dr);

//Coding to Save or Update DataSet Data into Database using DataAdapter.

            SqlCommandBuilder scb = new SqlCommandBuilder(da);

            da.Update(ds);

            MessageBox.Show("Data Saved");

        

           

          

        }

// Search button coding to Search data From Database Table.

        private void btnsearch_Click(object sender, EventArgs e)

        {

            SqlConnection con=new SqlConnection(@"Data Source=MALIK\MALIK;Initial Catalog=smalik;Integrated Security=True");

            SqlDataAdapter da = new SqlDataAdapter("Select * from tb_test", con);

//Coding to Fill Data into Dataset using DataAdapter

            DataSet ds = new DataSet();

            da.Fill(ds);

//Coding to Search Data From Dataset.

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                if (dr.RowState.ToString() != "Deleted")

                    if (dr[0].ToString() == txtrollno.Text)

                    {

                        txtfirstname.Text = dr[1].ToString();

                        txtlastname.Text = dr[2].ToString();

                        break;

                    }

            }

        }

// Delete button coding to delete data From Database Table.

        private void btndelete_Click(object sender, EventArgs e)

        {

            SqlConnection con = new SqlConnection(@"Data Source=MALIK\MALIK;Initial Catalog=smalik;Integrated Security=True");

            SqlDataAdapter da = new SqlDataAdapter("Select * from tb_test", con);

//Coding to Fill Data into Dataset using DataAdapter

            DataSet ds = new DataSet();

            da.Fill(ds);

//Coding to Delete Data From Dataset.

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                if (dr.RowState.ToString() != "Deleted")

                    if (txtrollno.Text == dr[0].ToString())

                    {

                        dr.Delete();

                        MessageBox.Show("Data Deleted from DataSet");

                        break;

                    }

            }

//Coding to Save all non-deleted DataSet Data into Database using DataAdapter.

            SqlCommandBuilder scb = new SqlCommandBuilder(da);

            da.Update(ds);

            MessageBox.Show("Data Deleted from Database");

        }

// Update button coding to update modified data into Database Table.

        private void btnupdate_Click(object sender, EventArgs e)

        {

            SqlConnection con = new SqlConnection(@"Data Source=MALIK\MALIK;Initial Catalog=smalik;Integrated Security=True");

            SqlDataAdapter da = new SqlDataAdapter("Select * from tb_test", con);

//Coding to Fill Data into Dataset using DataAdapter

            DataSet ds = new DataSet();

            da.Fill(ds);

//Coding to Save modified Data into Dataset.

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                if (txtrollno.Text == dr[0].ToString())

                {

                    dr[1] = txtfirstname.Text;

                    dr[2] = txtlastname.Text;

                 MessageBox.Show("Data Update in DataSet");

                 break;

                            

                }

            }

//Disconnected Mode in Ado.Net using C# Coding to Save Updated or Modified DataSet Data into Database using DataAdapter.

            SqlCommandBuilder scb = new SqlCommandBuilder(da);

            da.Update(ds);

            MessageBox.Show("Data Update in Database");

        }

    }

}

//End of Disconnected Mode in Ado.Net using C# Coding with Example.

Read more »

Connected Mode in ADO.Net using C# with Example

Connected Mode in ADO.Net using C# with example

In Connected mode in ADO.NET architecture connection must be open using open method to access data from database and make connection alive while we perform insert, update, delete and search operation and after retrieve data from database connection must be close using close method.

In Connected Mode ADO.NET, DataReader is used to retrieve data from database and it can hold only single table data while in Disconnected ModeADO.NET, Dataset is used to hold multiple table data.So this is the limitation of Connected Mode ADO.NET. Because when you constantly trips to the database for any insert, update, delete and search operation you wish to do. This creates more traffic to the database.

Also Read:- Disconnected Mode ADO.NET using C#

Example:-

Create a Table in Database and windows form in visual studio having following fields as in screenshot:

usingSystem;

usingSystem.Collections.Generic;

usingSystem.ComponentModel;

usingSystem.Data;

usingSystem.Drawing;

usingSystem.Linq;

usingSystem.Text;

usingSystem.Threading.Tasks;

usingSystem.Windows.Forms;

//use below namespace to connect with ADO.Net

usingSystem.Data.SqlClient;

namespaceWindowsFormsApplication1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        // Connected Mode ADO.Net Save button coding to Save data into Database Table.

        private void btnsave_Click(object sender, EventArgs e)

        {

            // Create object of sqlconnection class.

            SqlConnection con=new SqlConnection();

            // Set connection string property of connection object.

            con.ConnectionString = @"Data Source=MALIK\MALIK;Initial Catalog=smalik;Integrated Security=True";

            // Open connection using open method.

            con.Open();

            // Create object of sqlcommand class then pass connection string and connection object.

            SqlCommand cmd=new SqlCommand("insert into tb_test values("+Convert.ToInt32(txtrollno.Text)+",'"+txtfirstname.Text+"','"+txtlastname.Text+"')",con);

           // Execute Command using ExecuteNonQuery method for modification into table.

            cmd.ExecuteNonQuery();

            // Close connection using close method.

            con.Close();

        }

        private void btnsearch_Click(object sender, EventArgs e)

        {

            SqlConnection con = new SqlConnection();

            con.ConnectionString = @"Data Source=MALIK\MALIK;Initial Catalog=smalik;Integrated Security=True";

            SqlCommand cmd=new SqlCommand("Select * from tb_test where Rollno="+Convert.ToInt32(txtrollno.Text)+"",con);

            con.Open();

            SqlDataReader dr =cmd.ExecuteReader();

           

            if (dr.Read())

            {

                txtfirstname.Text = dr["Firstname"].ToString();

                txtlastname.Text = dr["Lastname"].ToString();

            }

            else

            {

                MessageBox.Show("Record not Found");

            }

            con.Close();

        }

        private void btndelete_Click(object sender, EventArgs e)

        {

            SqlConnection con = new SqlConnection();

            con.ConnectionString = @"Data Source=MALIK\MALIK;Initial Catalog=smalik;Integrated Security=True";

           

            SqlCommand cmd = new SqlCommand("Delete from tb_test where Rollno=" + Convert.ToInt32(txtrollno.Text) + "", con);

            con.Open();

            int temp=cmd.ExecuteNonQuery();

            if (temp > 0)

            {

                MessageBox.Show("Record Deleted");

            }

            else

                MessageBox.Show("No Record found");

          

            con.Close();

        }

        private void btnupdate_Click(object sender, EventArgs e)

        {

            SqlConnection con = new SqlConnection();

            con.ConnectionString = @"Data Source=MALIK\MALIK;Initial Catalog=smalik;Integrated Security=True";

            SqlCommand cmd = new SqlCommand("Update tb_test set Firstname='" + txtfirstname.Text + "',Lastname='" + txtlastname.Text + "'", con);

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

        }

    }

}

If you like this post please share this to your Friends.

Read more »