In previous article, we have discussed about ADO.NET, the namespaces like System.Data.OleDb, System.Data.SqlClient, System.Data.OracleClient, and System.Data.Odbc, what are the Classes available in namespaces like Dataset, DataTable, DataColumn, DataRow, DataView, DataRelation etc, what are the common classes available in namespaces like Connection, Command, DataReader, DataAdapter, CommandBuilder, Parameter, how to Establishing a Connection, how to Send request as a Statement, how to Capture the results given by Data Source, what is the role of constructors of Connection, what is Connection String and its syntax, what are the attributes of connection string like Providers, Data Source, User Id and Password, Database (or) Initial Catalog, Trusted_Connection = True, and DSN, what is the Connection String for Oracle, what is the Connection String for SQL Server, what are the Methods and Properties of Connection Class like Open (), Close (), State and ConnectionString, what is Command class, what are the Properties of Command Class like Connection and CommandText, what are the Methods of Command Class like ExecuteReader (), ExecuteScalar () and ExecuteNonQuery (), how to access Data from DataReader along with some examples.
Please find below the link for accessing the article
CSharp.NETTutorial - Day 21
Now, in this article we will discuss about ADO.NET, What is DataReader, what are features of DataReader, what are drawbacks of DataReader, what is disconnected Architecture, difference between connected-oriented architecture and disconnected-oriented architecture, what is DatSet, what are features of DataSet, how to work with DataSet, what are Constructors of DataAdapter, Methods of Adapter, Fill method, how to access Data from DataSet, what is DataTable, how to refer to a cell under DataTable,
ADO.NET
DataReader
DataReader is a class which can hold the data on client machine in the form of Rows and Columns.
Features of DataReader:
· DataReader is faster in access to get data from the data source because it is connection-oriented.
· DataReader can hold multiple tables at a time. We can load multiple tables in DataReader by passing multiple selection statements as arguments to command separated by a ';'
Please see below:
Command cmd=new Command (Select*From Statements; Select*From Teacher; on);
DataReader dr=cmd. ExecuteReader ();
Note: Use NextResult () method on DataReader object to navigate from current table to next table.
Eg: dr. NextResult ();
Drawbacks of DataReader
· As it is connection-oriented, it requires a permanent connection with Data Source all the time in order to access the data so that obviously performance will get degreased when there are number of clients accessing the same data at the same time.
· DataReader gives forward only access to the data i.e. it allows to go either to next Record (or) table but not to previous record (or) table.
· DataReader is a ReadOnly object which does not allow any changes to data present in it.
Disconnected Architecture
ADO.NET provides two different architecture for data source communication.
1) Connection-oriented Architecture
2) Disconnected-oriented Architecture
In connected-oriented architecture, we need to maintain a continuous connection with the data source in order to access the data in it. Here we can use 'DataReader' class to hold the data on client machines, whereas in disconnected-oriented architecture, we no need to maintain a continuous connection with the data source to access the data. Here we can use 'DataSet' class to hold the data on client machines.
DatSet
DataSet is a class which is under 'System.Data' namespace and it can be used to hold and manage the data on client machines apart from DataReader.
Features of DataSet
· DataSet can hold multiple tables in it.
· DataSet is designed in disconnected architecture and it doesn't require any permanent connection with the source to hold the data.
· DataSet provides scrollable navigation to the data which can allow us to navigate in any direction that is either from top to bottom or from bottom to top.
· DataSet can be updatable that is changes can be performed to data present in it and also we send them back to the database as well.
Working with DataSet
In case of DataReader, we can load the data into DataReader from the DataSource using the class called 'Command' whereas in case of DataSet, we can use 'DataAdapter' class to communicate between DataSource and 'DataSet'.
DataReader <------- Command -------> DataSource
DataSet <-------> DataAdapter <------> DataSource
Constructors
DataAdapter(string stmt, Connection con)
DataAdapter(Command cmd)
Eg:
DataAdapter da=new DataAdapter("<sql stmt>",con);
Methods of Adapter
Fill (DataSet ds, string tname)
Update (DataSet ds, string tname)
'Fill' is used to load the data from DataSource into DataSet.
'Update' is used to transfer the data from DataSet to DataSource.
Internally, DataAdapter is a collection of four commands which are listed below:
1) Select Command
2) Insert Command
3) Update Command
4) Delete Command
When we call 'Fill' method of DataAdapter, below things will take place:
1) Open connection with the DataSource
2) Execute the Select Command on the DataSource and load data from the table to DataSet.
3) Close the Connection.
As we know DataSet can be updatable, we can make changes to the data which is loaded into it like add, modify or delete.
After making changes to the data if we want to send those changes back to the data source, we should call 'Update' method on Adapter and it will perform the below things.
1) Re-open the connection with the DataSource
2) Changes which are made in DataSet will be back to the table, wherein this process it will use Insert, Update or Delete Commands of Adapter.
3) Close the Connection.
Accessing Data from DataSet
'DataReader' provides pointer-based access to the data so that we can get the data only in a sequential order, whereas 'DataSet' provides index-based access to the data so that we can get the data from any location ReadOnly.
'DataSet' is a collection of tables, where each table is represented as a class named 'DataTable' and can be identified by its index position or name.
Syntax of DataSet
-Collection of Tables (DataTable) <dataset>.Tables [Index]
(or)
-ds.Tables [0] (or) ds.Tables ["Employee"]
Again, every DataTable is a collection of rows and columns, where each row is represented as a class called 'DataRow' and identified by its index position and each column is represented as a class called 'DataColumn' and identified by its Index position or name.
Syntax of DataTable
-Collection of Rows (DataRow)
< datatable >.Rows[Index]
ds.Tables [0].Rows [0]
-Collection of Columns (DataColumns)
<datatable>.Column[Index] (or) Column[name]
ds.Tables[0].Column[0] (or) ds.Tables[0].Columns["Eno"]
How to refer to a cell under DataTable
<datatable>.Rows[row][col]
ds.Tables [0].Rows [0] [0]
Or
ds.Tables [0].Rows[0]["Eno"]
Add reference of 'Microsoft.VisualBasic' assembly from .NET tab of add reference window and write the following code.
using System.Data.SqlClient;
using Microsoft.VisualBasic;
Declarations
SqlConnection con;
SqlDataAdapter da;
SqlCommandBuilder cb;
DataSet ds;
int rno = 0;
Under Form Load:
con = new SqlConnection("User Id=sa;Password=123;Database=mydb");
da = new SqlDataAdapter("Select Empno,Ename,Job,Sal from Emp", con);
ds = new DataSet();
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds, "Emp");
ShowData();
private void ShowData()
{
textBox1.Text = ds.Tables[0].Rows[rno][0].ToString();
textBox2.Text = ds.Tables[0].Rows[rno][1].ToString();
textBox3.Text = ds.Tables[0].Rows[rno][2].ToString();
textBox4.Text = ds.Tables[0].Rows[rno][3].ToString();
}
Under First button
rno = 0;
ShowData();
Under Prev button
if (rno > 0)
{
rno -= 1;
if (ds.Tables[0].Rows[rno].RowState == DataRowState.Deleted)
{
MessageBox.Show("Deleted Row Cannot be Accessed");
return;
}
ShowData();
}
else
MessageBox.Show("First Record of the Table");
Under Next button
if (rno < ds.Tables[0].Rows.Count - 1)
{
rno += 1;
if (ds.Tables[0].Rows[rno].RowState == DataRowState.Deleted)
{
MessageBox.Show("Deleted Row Cannot be Acceessed");
return;
}
ShowData();
}
else
MessageBox.Show("Last Record of the Table");
Under Last Button
rno = ds.Tables[0].Rows.Count - 1;
ShowData();
Under New button
textBox1.Text = textBox2.Text = textBox3.Text = textBox4.Text = "";
int index = ds.Tables[0].Rows.Count - 1;
textBox1.Text = (Convert.ToInt32(ds.Tables[0].Rows[index][0])+1.ToString());
textBox2.Focus();
Adding a new record under data table
To add new records into DataTable of DataSet, follow the below process:
1) Create an empty row by calling the method NewRow() on DataTable
2) Assign values to the new row by treating it as a single dimensional array
3) Call the Add method and add the row to DataRowCollection
Under Insert Button
DataRow dr = ds.Tables[0].NewRow();
dr[0] = textBox1.Text;
dr[1] = textBox2.Text;
dr[2] = textBox3.Text;
dr[3] = textBox4.Text;
ds.Tables[0].Rows.Add(dr);
MessageBox.Show("Record Added to Table");
rno = ds.Tables[0].Rows.Count - 1;
Updating an existing record of DataTable
To update an existing record in a data table reassign modified values back to the row under data table so that old values get changed to new ones.
Under Update Button
ds.Tables[0].Rows[rno][1] = textBox2.Text;
ds.Tables[0].Rows[rno][2] = textBox3.Text;
ds.Tables[0].Rows[rno][3] = textBox4.Text;
MessageBox.Show("Record modified under data table");
Deleting an existing record of DataTable
In order to delete a record under data table, call Delete method on DataRowCollection pointing to the row that has to be deleted.
Under Delete Button
ds.Tables[0].Rows[rno].Delete();
MessageBox.Show("Record deleted from data table");
button1.PerformClick();
How to Save changes made on DataTable of DataSet to DataBase
If we want to save the changes which were made on DataTable of DataSet to DataBase, we need to call the Update method on DataAdapter by passing the DatSet which contains modified values in it. In order to work the Update method of DataAdapter, it should contain three commands under it which are Insert, Update or Delete and these three commands have to be written by the programmers manually or generate them with the help of CommandBuilder class. If we have given CommandBuilder with the DataAdapter that contains a Select Command in it, it will generate remaining three commands which are required.
Consturctor:
CommandBuilder (DataAdapter da)
Note: CommandBuilder can generate UPDATE and DELETE commands for us for a given SELECT command only if the table contains Primary Key Constraints on it.
To add a Primary key constraint on our Employee table
· Open SqlServer Management Studio
· Click on "New Query"
· Choose our CSharpDB database and write the following statements in the query window and execute.
ALTER TABLE EMPLOYEE ALTER COLUMN INT NOT NULL
ALTER TABLE EMPLOYEE ADD PRIMARY KEY (ENO)
Under SaveToDatabase Button
cb = new SqlCommandBuilder(da);
da.Update(ds, "Employee");
MessageBox.Show("Data Saved to DataBase");
Searching a record of DataTable
To search a record of datatable, call Find method on DataRowCollection which can search the data on Primary Key Column(s) of table and returns a Row.
Find (Object key) -> DataRow
Note: In order to work Find method, we need to load first the Primary Key information of table into DatSet by setting the property values as "AddWithKey" for MissingSchemaAction of DataAdapter.
Under Search Button
string value = Interaction.InputBox("Enter Employee No.", "Employee Search", "", 150, 150);
if (value.Trim().Length > 0)
{
int eno = int.Parse(value);
DataRow dr = ds.Tables[0].Rows.Find(eno);
if (dr != null)
{
textBox1.Text = dr[0].ToString();
textBox2.Text = dr[1].ToString();
textBox3.Text = dr[2].ToString();
textBox4.Text = dr[3].ToString();
}
else
MessageBox.Show("Invalid Employee No.");
}
Under Close Button
this.Close();
Happy Learning….!!!!!
This post first appeared on Dot Net Programming (C#, Asp.Net, ADO.Net, WCF, WPF, Ajax, LINQ), please read the originial post: here
