Related Posts with Thumbnails

Accessing database using ADO.NET in C# or VB.NET

Monday, May 3, 2010

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 organized 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!!)

    In this tutorial, we will be using only MS Access for simplicity. Most of the samples provided in this site uses MS Access database for simplicity and easy download.



    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, including:







  • OleDb







  • SqlClient

    There are other providers available, but we are not including them here as this tutorial is meant for beginners! When you want them, search for ADO.NET providers in Google or MSDN

    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 its 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 communicating with database


    The ConnectionCommandDataReader, and DataAdapter
    objects are the core elements of the ADO.NET provider model.



    ObjectDescriptionSqlClient ObjectsOleDb Objects
    ConnectionEstablishes a connection to a specific data source.SqlConnectionOleDbConnection
    CommandExecutes a command against a data source.SqlCommandOleDbCommand
    DataReaderReads a forward-only, read-only stream of data from a data source.SqlDataReaderOleDbDataReader
    DataAdapterPopulates a DataSet and resolves updates with the data source.SqlDataAdapterOleDbDataAdapter


    Each provider may have classes equivalent to above objects. The name of the classes vary slightly to represent the provider type appropriately.

    Depending on the type of database you work on, you will have to choose either OleDb or SqlClient (or, some other provider) objects. Since all our samples use MS Access database, we will be using OleDb objects in all the samples. If you like to use SqlServer, you just need to replace the OleDb objects with the equivalent SqlClient objects.



    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.

    Soon, we will publish several ADO.NET samples here. Please check back soon. 
  • 0 comments:

    Post a Comment

    Site Rate