ADO .NET Data Access

In this blog we will discuss how to Access data using ADO .NET and make changes to it.

Coding

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace UpdatingData
{
public partial class Form1 : Form
 {
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(
@"Data Source=yourServerName;user id=UserName;password=Password;" +
"Initial Catalog=DatabaseName");

SqlDataAdapter thisAdapter = new SqlDataAdapter(

"SELECT EMPNO,ENAME FROM EMP", conn);

SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

DataSet ds = new DataSet();

thisAdapter.Fill(ds, "Employee");

Console.WriteLine("name before change: {0}",
ds.Tables["Employee"].Rows[5]["ENAME"]);

ds.Tables["Employee"].Rows[5]["ENAME"] = "Johnson";

thisAdapter.Update(ds, "Employee");

Console.WriteLine("name after change: {0}",
ds.Tables["Employee"].Rows[5]["ENAME"]);
}
}
}

Here,

SqlConnection conn = new SqlConnection(

@"Data Source=yourServerName;user id=UserName;password=Password;" +

"Initial Catalog=DatabaseName");

The above block of code is SQL – Server specific connection String to the database

SqlDataAdapter thisAdapter = new SqlDataAdapter(

"SELECT EMPNO,ENAME FROM EMP", conn);

Here we create a DataAdapter object to Operations such as Update

SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

The SqlCommandBuilder is used to build SQL statements

DataSet ds = new DataSet();

Here, we create a DataSet object to hold data.

thisAdapter.Fill(ds, "Employee");

In the above statement we will the DataSet with the query we have previously defined for the DataAdapter.

Console.WriteLine("Name before change: {0}",ds.Tables["Employee"].Rows[5]["ENAME"]);

Displaying the data before change

ds.Tables["Employee"].Rows[5]["ENAME"] = "Johnson";

In the above line, we change the data in Employee table, row 5 with the column name ENAME

thisAdapter.Update(ds, "Employee");

Here we make a call to the Update command to make the changes permanent to the database Table.

 Console.WriteLine("Name after change: {0}",ds.Tables["Employee"].Rows[5]["ENAME"]);

Thanks for reading