What is ADO.NET - Part 1

What is ADO.NET?
ADO.NET is not a different technology. In simple terms, you can think of ADO.NET, as a set of classes (Framework), that can be used to interact with data sources like Databases and XML files. This data can, then be consumed in any .NET application. ADO stands for Microsoft ActiveX Data Objects.

The following are, a few of the different types of .NET applications that use ADO.NET to connect to a database, execute commands, and retrieve data.

ASP.NET Web Applications
Windows Applications
Console Applications

What are .NET Data Providers?
Databases only understand SQL. If a .NET application (Web, Windows, Console etc..) has to retrieve data, then the application needs to
1. Connect to the Database
2. Prepare an SQL Command
3. Execute the Command
4. Retrieve the results and display in the application



Sample ADO.NET code to connect to SQL Server Database and retrieve data. Notice that we are using SQLConnection, SQLCommand and SQLDataReader classes . All the objects are prefixed with the word SQL. All these classes are present in System.Data.SqlClient namespace. So, we can say that the .NET data provider for SQL Server is System.Data.SqlClient.
SqlConnection con = new SqlConnection("data source=.; database=Sample; integrated security=SSPI");
SqlCommand cmd = new SqlCommand("Select * from tblProduct", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
GridView1.DataSource = rdr;
GridView1.DataBind();
con.Close();

Sample ADO.NET code to connect to Oracle Database and retrieve data. Notice that we are using OracleConnection, OracleCommand and OracleDataReader classes . All the objects are prefixed with the word Oracle. All these classes are present in System.Data.OracleClient namespace. So, we can say that the .NET data provider for Oracle is System.Data.OracleClient.
OracleConnection con = new OracleConnection("Oracle Database Connection String");
OracleCommand cmd = new OracleCommand("Select * from tblProduct", con);
con.Open();
OracleDataReader rdr = cmd.ExecuteReader();
GridView1.DataSource = rdr;
GridView1.DataBind();
con.Close();



If we want to connect to OLEDB datasources like Excel, Access etc, we can use OleDbConnection, OleDbCommand and OleDbDataReader classes. So, .NET data provider for OLEDB is System.Data.OleDb. 

Different .NET Data Providers
Data Provider for SQL Server - System.Data.SqlClient

Data Provider for Oracle - System.Data.OracleClient
Data Provider for OLEDB - System.Data.OleDb
Data Provider for ODBC - System.Data.Odbc



Please note that, depending on the provider, the following ADO.NET objects have a different prefix
1. Connection - SQLConnection, OracleConnection, OleDbConnection, OdbcConnection etc
2. Command - SQLCommand, OracleCommand, OleDbCommand, OdbcCommand etc
3. DataReader - SQLDataReader, OracleDataReader, OleDbDataReader, OdbcDataReader etc
4. DataAdapter - SQLDataAdapter, OracleDataAdapter, OleDbDataAdapter, OdbcDataAdapter etc

The DataSet object is not provider specific. Once we connect to a Database, execute command, and retrieve data into .NET application. The data can then be stored in a DataSet and work independently of the database.

25 comments:

  1. the following code generates an error in connection with the database says con.Open() cannot connect to Sql database. Can you post how to solve this problem?

    ReplyDelete
    Replies
    1. Please Verify the Connection string in web.config file,you get that error when the connection string is not correct or

      if you mention the datasource(server) name as (local) or dot also gives you the error sometimes.So its better to give the name of the server instead of "." or (local)

      Delete
  2. hi venkat
    could u tell me the difference between windows application and console application
    in the same way diff between website and webapplication
    pls upload video regarding these differences...can u explain practically...

    ReplyDelete
    Replies
    1. Console Applications don't have user interfaces and are run in the Command Prompt. Windows Forms applications do have user interfaces.

      Delete
  3. Hi ..

    Windows Application Is The Application That
    Interacts With The Data Source Of
    Windows Based Objects , Classes And Programs , Software Like
    MS Office , Ms Word , Notepad etc . Which Are Of Software Application And Windows Itself A System Application
    You Can Understand The Concept Of This By Reading Application Programming Interface (API) And System Calls etc

    Whereas Console Application Is The Application That Interacts With Console Application i.e -> Command Line Based Applications etc
    There Are Lots Of Programming Language That Helps It To Interact To The Object Oriented Levels etc Like Perl , Ruby And Python etc
    Are The Console Applications That Interacts With The Command Line Applications i.e -> Console Application . The Terms Signifies The Following Meaning Behind It

    Whereas Website Is Basically A Terminology Of Web Pages In Which The Pages Are Dynamically As Well Statically Connected To The Web Interface System And It's The Part Of Either Local Area Network (LAN) Or Remote Wide Area Network (WAN) . If You Know Client And Server Architecture Model Then The Concept Is Easily Understandable That Client Sends Request To The Web Server And The Web Server Connects And Gets And Stores To The Request And Make Information Available To You . Also There Are Many Levels Of Requests , Methods And Properties In What Way It Works etc . Basically It's Registered To Web Hosting Domains via URL etc.
    It's a document, typically written in plain text interspersed with formatting instructions of Hypertext Markup Language (HTML, XHTML). A webpage may incorporate elements from other websites with suitable markup anchors.

    Web Applications Are The Applications That Interacts With Web Interface System etc . eg -> MSQLI , HTML , XML , XHML etc :)

    Thanks For Reading

    Hope You Understand

    https://www.facebook.com/ChuckaKhatri

    ReplyDelete
  4. Hello Sir your tutorial videos are best to understand the beginners to learn and adapt the best techniques. Just want to ask you is you can do the tutorials for creating .exe in ADO.NET with Software Copy Protection for windows application.

    ReplyDelete
  5. You Are the Best Instructor Ever.You Have No Idea What You Have Done For Me.Love From Pakistan.

    ReplyDelete
  6. can anyone give example for ODBC

    ReplyDelete
  7. Hi Venkat,

    what basic settings have do be done in SqlServer to let your examples run? I always get a SqlException on Method Connection.Open independent on the used Web server (Build in or IIS)

    Regards

    ReplyDelete
  8. Hi Sir,

    I have watched many videos of yours,i Really really appreciate your work, its very easy to understand very helpful,i am just speech less. thanks a lot and wish me luck for the interview.

    ReplyDelete
  9. World class tutorials!! thanks alot. God bless you!!

    ReplyDelete
  10. Hi Venkat
    I am using Visual studio 2012 and SQL server 2014.
    Above code generates an error while connecting to the database at con.Open();
    Please provide the detailed explanation to fix this issue?

    ReplyDelete
  11. the way you teach is indeed appreciable.thanks a lot sir

    ReplyDelete
  12. when retriving data from database with asp.net some error pls solve it


    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'persons'.

    Source Error:


    Line 17: SqlCommand cmd = new SqlCommand("select * from persons", con);
    Line 18: con.Open();
    Line 19: SqlDataReader rdr = cmd.ExecuteReader();
    Line 20: GridView1.DataSource = rdr;
    Line 21: GridView1.DataBind();

    Source File: c:\users\fit\documents\visual studio 2010\Projects\WebApplication5\WebApplication5\Default.aspx.cs Line: 19

    Stack Trace:

    ReplyDelete
    Replies
    1. Mohammed Sheik please do this code
      //First Verified Person is correct that you given your table name after that write that code

      SqlCommand cmd = new SqlCommand("select * from persons", con);
      con.open();
      SqlDataReader rdr = cmd.ExecuteReader();
      DataTable dt=new Datatable();
      rdr.fill(dt);
      GridView1.DataSource=dt;
      GridView1.DataBind();
      Con.Close();

      Delete
    2. The table name persons doesn't exist in the database that you are pointing to .. so u r getting this error.. first create the table , then execute..

      Delete
    3. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

      What is the problem hier ???

      Delete
    4. You should check your database name correctly mention in Web.config file, also check your server name.
      for example


      just like that

      Delete
  13. Thank you so much ! You help me a lot with this tutorial . Yua are the te best to learn C# (Sharp).
    Aurora from Albania.

    ReplyDelete
  14. You are the best teacher venkat sir.....i have ever seen...
    one day i will come to banglore ....to thank you..

    thank you very much venkat sir.....from deep of my heart.

    ReplyDelete
  15. Visual Studio 2017 community version with latest updates does it have any issues with con.open(), since the program just freezes. This checked with Asp.net vb.net code

    ReplyDelete
  16. Hi Venkat,
    Thank you for the great tutorial.
    In part2, you did not user sqlDataReader. Could you please explain?

    ReplyDelete
  17. hi sir,
    i am facing problem for connecting to database, please tell that should i need to run my sql server first, please guide me little bit more as i am new to learning here..

    ReplyDelete
  18. Sir can you please tell which application we have to use.I didn't find aspx.cs page.

    ReplyDelete

It would be great if you can help share these free resources