Entry

How can access database using ADO.NET in C#?

Aug 19th, 2004 02:31
Ajesh Babu,

This tutorial will teach you Database concepts and ADO.NET in a very 
simple and easy-to-understand manner with many code snippets and 
samples. This is primarily meant for beginners and if you are looking 
for any advanced ADO.NET topics, this may not be the right page for 
you. 
Database Concepts 
Database is the media to store data. If you have an application that 
has to store and retrieve data, your application must be using a 
database. 
A 'File' is the simplest form of saving the data in the disk, but is 
not the most 'efficient way' of managing application data. A database 
is basically a collection of one or more files, but in a custom format, 
and data is organised in a specific format such a way that it can be 
retrieved and stored very efficiently. 
Some examples for databases are : 
MS Access 
SQL Server 
Oracle 
MS Access is a very light weight database provided by Microsoft for 
applications with less number of users and relatively small quantity of 
data. MS Access saves data into database files with the extension .mdb. 
Usually, MS Access comes along with MS Office package. If you already 
have the .mdb database file, you can freely use it with your 
application and you do not need MS Access software. The MS Access 
software is required only if you want to directly open the database and 
manipulate the data or change the database schema. 
SQL Server (Microsoft product) and Oracle (Oracle Corp.) are more 
complex, advanced, relational databases and they are much more 
expensive. It can support large number of users and very high quantity 
of data. If you are developing a software, which might be accessed 
simulatenously by 100s of users or if you expect your data may grow 
100s of MBs, you might consider one of these. (We are learning 
Microsoft .NET.. so you might want to consider the SQL Server than 
Oracle, for which Microsoft provides special data access components!!) 
ADO.NET 
ADO.NET is the data access model that comes with the .NET Framework. 
ADO.NET provides the classes required to communicate with any database 
source (including Oracle, Sybase, Microsoft Access, Xml, and even text 
files). 
DataAccess Providers in .NET 
ADO.NET comes with few providers providers, including: 
OleDb 
SqlClient 
Microsoft made the SQL Server. So they gave a separate provider, 
specifically made for SQL Server. We can use the OleDb provider for all 
other database sources including MS Access, Oracle, Sybase etc. There 
is a separate provider available for Oracle. 
A DATA PROVIDER is a set of classes that can be used to access, 
retrieve and manipulate data from the databases. 
Both OleDb and SqlClient has it's own set of classes, but they have the 
same concepts. We would like to classify the classes into two broad 
categories (this is not a microsoft classification, anyway!) 
Classes for communicate with database 
Classes for holding/manipulating data 
The job of first category of classes is to communicate with database 
and send or retrieve data from the database. The second category of the 
classes will be used as a carrier of data. 
Classes for holding data 
The following are the main classes used to hold data in Ado.NET: 
DataSet 
DataTable 
DataRow 
A DataSet is an in-memory representation of the database. 
DataSet contains DataTables (and more...) 
DataTable represents a database table 
DataTable contains DataRows (and more...) 
A DataRow represents a record in a database table. 
DataRow is a collection of all fields in a record. 
We can use the DataAdapter or DataReader to populate data in DataSet. 
Once we populate data from database, we can loop through all Tables in 
the DataSet and through each record in each Table. 
On the first look, this may look bit confusing, but once you understand 
the concept and get familiar with the Ado.NET classes, you will 
appreciate the power and flexibility of Ado.NET. 
//The dataBase Connection Codes Below
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data 
Source=C:\\Samples\\Employee.mdb";
OleDbConnection myConnection = new OleDbConnection( connectionString );
myConnection.Open();
string query = "insert into EMPLOYEE_TABLE (EmployeeID, Name, Address) 
VALUES (101, 'John', '3960 CliffValley Way')";
OleDbCommand myCommand = new OleDbCommand();
myCommand.CommandText = query;
myCommand.Connection = myConnection;
myCommand.ExecuteNonQuery();
myConnection.Close();
//The following code demonstrates using OleDbDataAdapter Object and 
DataSet to retrieve data from databbase. 
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data 
Source=C:\\Samples\\Employee.mdb";
OleDbConnection myConnection = new OleDbConnection( connectionString );
string query = "select * from EMPLOYEE_TABLE";
OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, 
myConnection );
DataSet employeeData = new DataSet();
myAdapter.Fill ( employeeData );