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