Access Data &Update using ADO .NET

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

 

Last updated:9/18/2014 1:24:10 PM

1 Comments

Leave Comment