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.
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.
.jpg)
